Tag: Script

T-SQL Tuesday 157 – End of Year Activity

Words: 544

Time to read: ~ 2 minutes

Read-Clipboard

Welcome to T-SQL Tuesday, the monthly blog part where we are given a topic and asked to blog about it. This month we have Garry Bargsley, asking us to discuss end-of-year activities.

Here

My current workplace has a change freeze in place for the end of the year. Usually, I would say that this is risk-averse.
But seeing as the nature of the business (payments) means that the Thanksgiving/Black Friday/Christmas time is the busiest time of the year, I’m willing to cut them some slack.

So, what to do when we cannot deploy to production? Oh, we’ll still be busy! There are always management-approved fixes that get through, annual processes to complete, and project planning that has to be…well, planned.

But, my priority for this end-of-year is documentation.

Docs

We have a few different tools for documentation. Examples are Confluence, Google Sheets, Google Docs, etc.

But most of the time, documentation takes the form of scripts saved to source control.

These scripts are multiprocess and cross-team dependent and can quickly end up like me doing DIY. One hand trying to steady the nail, the other wielding the hammer, and the whole situation collapsing into swear words and tears.

We can’t currently have a “hit-Enter-and-leave-it” bunch of scripts because we have to stop midway for another team’s work or to check the results of what we’ve just run.

Notebooks

If we used Notebooks, this would be so much easier. I could create the code, save a snippet of the results to the notebooks, and then future executors could see what to expect.

No-books

We don’t use Notebooks.

Plain .sql files for me, it is! 

To ease the documentation burden and have some semblance of tidiness, I created a PowerShell tool that splits it out “all pretty like”.

Format-TextTable

Now, with a combination of Read-Clipboard from the ImportExcel module, I can grab results and turn them into a text table that I can add back into the script.

Simple example: we want to save the database name, create date, and compatibility level of the databases on an instance.

… I said simple example – not good or useful example.

SELECT 
	database_name = name,
	create_date,
	compatibility_level
FROM sys.databases;
Picture shows an SSMS screen selecting name, create_date, and compatibility level from sys.databases. Returning 5 rows

Now, let’s copy and paste the results into a comment on that script as a form of “documentation”.

Picture shows an SSMS screen selecting name, create_date, and compatibility level from sys.databases. Returning 5 rows. Underneath the query in a comment are the pasted results that all have different spacing and are not aligned

Now, this time I’m going to copy those results, and run the following code in my PowerShell Core window, before pasting into the comment block.

Read-Clipboard | Format-TextTable | Set-Clipboard
Picture shows an SSMS screen selecting name, create_date, and compatibility level from sys.databases. Returning 5 rows. Underneath the query in a comment are the pasted results that are wrapped in a text table format and aligned under their header name

Given the option, I know which one I’d choose.

Code

You can pick up the source-code here.

Feel free to check it out and add/remove from it.

Set-Clipboard

I am aware that I could spend minutes of my time to evenly align the spaces. And, yes, I know that SSMS can do find-and-replace using simple Regex.
But if I manually structure the output every time, for all different types of columns & data types, with different spacings…

I’d be better off arguing with the company to start using Notebooks.

Until then, this works for me. Hopefully, it will work for you.

And hey, maybe it will help improve your documentation in the New Year. That has a higher chance of happening than my one to improve my DIY skills.

Table Column Differences Part 03 – Compare-SqlTableColumns

Words: 470

Time to read: ~ 2 minutes

Don’t talk to me about it!

Four years ago (I know, where did the time go?), I wrote about Table Column Differences with T-SQL and PowerShell.

A Michal commented on the post, asking how to get a specific output from his search.

Hi,

Thanks for your sharing. What if I also want to compare case sensitively columns and the order of them (syncwindows). How can I presented it on powershell.

I mean that in the final table I want to show also something like: column_a, column_A –> case sensitive

AND

column_a, column_a –> different order in the table

Thanks in advance

Michal

I confess that I never got around to answering Michal until a few weeks ago when I found myself with some rare free time.

Since then, I’ve written a script, slapped it into a function, and threw it up on Github.

Here’s hoping that it does what you want this time Michal, thanks for waiting.

Shall I Compare Thee to Another Table?

The first thing that we need to do is have a couple of SQL tables to compare.

So, I threw up a Docker container and created a couple of tables with nearly the same layout.

(Get-DbaDbTable -SqlInstance localhost -Table 'dbo.DifferenceTable01', 'dbo.DifferenceTable02').Columns |
        Select-Object -Property Parent, Name, ID, DataType |
        Format-Table -GroupBy Parent
I’m liking the new PowerShell formatting

You can see that there are around three differences here

  1. Column orders, e.g. col9 has id 6 in dbo.DifferenceTable01 but id 5 in dbo.DifferenceTable02.
  2. Column case sensitivity, e.g. col7 does not match COL7.
  3. Column presence, e.g. col3 doesn’t exist in dbo.DifferenceTable01 at all.

While Compare-Object has the -CaseSensitive switch, I don’t think that it would be helpful in all these cases. Or else I didn’t want to use that command this time around.

So, I wrote a function to get the output we wanted, and yes, I now include myself among that list of people wishing for that output.

I’m allowed to be biased towards the things that I write 🙂

Compare-SqlTableColumns

Compare-SqlTableColumns -SqlInstance localhost -Table1 'dbo.DifferenceTable01' -Table2 'dbo.DifferenceTable02' |
        Format-Table

I’ve tried to include everything you could want in the function output, i.e. column names, column ids, and statuses.

Something I’ve started to do lately is wrapping a [Diagnostics.StopWatch] in my verbose statement to see where potential slow parts of the function are.

I’d like to think that 0.2 seconds for this example aren’t too bad.

$x = Compare-SqlTableColumns -SqlInstance localhost -Table1 'dbo.DifferenceTable01' -Table2 'dbo.DifferenceTable02' -Verbose

$x | Format-Table

Thou hast less columns than thine brother…

Feel free to use and abuse this function to your hearts content. I know that there are a few things that I’d add to it. Comparing across different instances being an obvious one that I’d like to put in.

Hopefully though, someone out there will find it helpful.

Here’s looking at you, Michal.

TSQL Tuesday 106 – Trigger Headaches or Happiness: Capturing Database Creations.

I see your hidden database table trigger and raise you a more hidden server one!

Words: 306

Time to read: ~ 1.5 minutes

Continue reading “TSQL Tuesday 106 – Trigger Headaches or Happiness: Capturing Database Creations.”

Automating Conference Speaker Notifications

In case I, as a volunteer, fall asleep from lack of coffee 🙂

Words: 367

Time to read: ~ 2 minutes

Link to script: https://github.com/shaneis/RandomScripts/commit/9d6967fa56a75342f00e5ddbcd066adc387f2618

Continue reading “Automating Conference Speaker Notifications”

Importing Excel into SQL Server using PowerShell

Ah T-SQL Tuesday, is it that time again? And the 94th one at that! Wow!

Words: 797
Time to read: ~4 minutes

Update: 2017-09-20 Thanks to Rick Fraser for pointing out I showed a $ServerConnection but hadn’t defined it in the function or separately or at all! Thanks Rick!

Continue reading “Importing Excel into SQL Server using PowerShell”

Exporting Special Characters out of SQL Server using PowerShell.

PowerShell is ußer-useful!

So I’ve talked before about keeping new lines when copying results to a different window in SQL Server and about copying new lines out of SQL Server into reports.

These topics have come about as they are both issues that I’ve had to deal with. Well, another of those issues is dealing with exporting special characters out of SQL Server using PowerShell.

The Lay-out.

We already have our table called “dbo.NewLineNotes” from before when we were trying to copy new lines out of SQL Server so we’re going to add another row.
Now personal experience for me centered around the German Eszett (“ß”) but you may encounter this with other characters.

-- Insert some special characters...
INSERT INTO dbo.NewLineNotes (Notes)
VALUES (N'This is a ß')

Now if you were to use the code from keeping new lines post…


$NewLineQuery = 'SELECT Notes FROM dbo.NewLineNotes'
$InvSQLParams = @{
ServerInstance = '0738-5CG140G7T\SQLSERVER2K16'
Database = 'Pantheon'
Query = $NewLineQuery
}
Invoke-Sqlcmd @InvSQLParams |
Select-Object –Property Notes |
Export-Csv –Path 'C:\Users\soneill\Desktop\In Progress\Test\NewLines.csv' –NoTypeInformation

view raw

NewLines_FileOutBasic.ps1

hosted with ❤ by GitHub

and open up the csv file we would get…

EncodingIssue
My german is non-existant but I know that’s wrong!

“What do we do when we fall down?”…

Well with SQL Server, I normally break things down into the smallest parts and slowly build it up until it breaks. For this, it breaks when we get to Export-CSV as everything before it works!

Troubleshooting
What we want…

PowerShell is even easier for troubleshooting methodology as , and we’ve talked about it before, Get-Member and Get-Help are there to help us!

We know that it’s Export-CSV that is somehow screwing up our special character so the obvious next step…

help Export-CSV -Full;

And we can see a parameter just shine at us!

Encoding
Looks like ASCII is not for me!

So we have to define an “Encoding” do we? I used “UTF8” and modified my query…


$NewLineQuery = 'SELECT Notes FROM dbo.NewLineNotes'
$InvSQLParams = @{
ServerInstance = '0738-5CG140G7T\SQLSERVER2K16'
Database = 'Pantheon'
Query = $NewLineQuery
}
Invoke-Sqlcmd @InvSQLParams |
Select-Object –Property Notes |
Export-Csv –Path 'C:\Users\soneill\Desktop\In Progress\Test\NewLines.csv' –NoTypeInformation –Encoding UTF8;

view raw

NewLines_FileOutBasicSpecialChars.ps1

hosted with ❤ by GitHub

EncodingIssueResolved
Eszett? More like EZ-zett!

And special characters are no longer an issue for us 🙂

 

Copying New Line Data out of SQL Server

 

A lot of the time, DBAs are asked to run adhoc reports for various business people and, more often than not, the expected medium for these reports is Excel.

Now for the most part this seems simple enough…

  • Run the T-SQL report
  • Highlight the results
  • Copy the results
  • Paste into an Excel worksheet

Simples!…right?

How do you deal with carriage returns though? New line feeds? Tabs? Commas when you’re trying to comma delimit?

Try and copy them into an Excel worksheet and what you’re going to get is confusion, alarm, and vexation.

Not exactly the clear reporting that the business people are hoping for.

So what can we do? Panic? Grab another coffee? Roll your “r’s”?

Yes, yes, and not yet…

Karaoke…

I have mentioned before that we can use CHAR(10) and CHAR(13) for new lines and carriage returns in SQL Server so I’ll leave it up to an exercise to the reader to create a table with these “troublesome” bits of information in them (plus if you came here from Google, I assume you already have a table with them in it).

For me, I’ve just created a single table dbo.NewLineNotes that has a single entry with a new line in it.

CopyingNewLineTwoLines
SQL Server is left, Report is right

So a straight-up copy and paste isn’t going to cut it here. If we have more than 1 row, we’re not going to get a 1 entry to 1 row in the report that we are looking for. How do people deal with this?

1 Way:

Well, depending on what tool you have, the answer could be as simple as a right-click and selecting “Open in Excel”

RedGate_OpenInExcel
Intact but on 1 line 🙂

Or Another:

Let’s proceed with the impression that you do not have RedGate tools (cough free trial cough) and cannot avail of the right-click righteousness, what do you do then.

Well…have you thought about PowerShell?

Hear me out on this but you probably already have your query but found the new lines are screwing up the report. So let’s throw that query into a variable

$NewLineQuery = 'SELECT Notes FROM dbo.NewLineNotes'

Then what we have to do is somehow connect to the SQL Server instance and database.

Let’s go with the very basics here as that’s all we really need. Invoke-SqlCmd, and yes I know it has problems. I’ve linked and talked about them before. It works for us in this situation though.


$NewLineQuery = 'SELECT Notes FROM dbo.NewLineNotes'
$InvSQLParams = @{
ServerInstance = 'localhost\SQLSERVER2K16'
Database = 'Pantheon'
Query = $NewLineQuery
}
Invoke-Sqlcmd @InvSQLParams |
Select-Object –ExpandProperty Notes

view raw

NewLines_CopyOutBasic.ps1

hosted with ❤ by GitHub

FirstResults
Yup, that’s good old VS code!

Now the more code-centered readers among you may have spotted and asked why I used -ExpandProperty and not just -Property , or even why I included it at all.
Well, apart from the thought that code online should be like code in scripts (legible with no aliases), we’re dealing with new lines here!
If we don’t specify ​-expand then what we actually get is…

SecondResults
comma delimited or ellipses delimited?

How does that help us with Reports?

If you work with PowerShell for the smallest amount of time, then I hope you’ve run into the command Export-CSV. See help for details…

help Export-Csv -Full

This will output a delimited file (defaults to comma but we can change that if we want) to wherever we want. We can then open it up in Excel or whatever other tool you use.

Let’s see if that splits our information into a new line!


$NewLineQuery = 'SELECT Notes FROM dbo.NewLineNotes'
$InvSQLParams = @{
ServerInstance = '0738-5CG140G7T\SQLSERVER2K16'
Database = 'Pantheon'
Query = $NewLineQuery
}
Invoke-Sqlcmd @InvSQLParams |
Select-Object –Property Notes |
Export-Csv –Path 'C:\Users\soneill\Desktop\In Progress\Test\NewLines.csv' –NoTypeInformation

view raw

NewLines_FileOutBasic.ps1

hosted with ❤ by GitHub

ThirdResult
IT’S ALIVE!!! ugh I mean…IT WORKS!!!

Another another…

There are tons of different ways to do this but this is what I used.

Quick, dirty, and effective.

In the short term, I’m okay with that!