Tag: Script

T-SQL Tuesday #182 – Integrity

Time to read: ~ 3 minutes
Words: 531 words

Welcome to T-SQL Tuesday, the monthly blogging party where we are given a topic and have to talk about it. Today, we have Rob Farley ( blog | bluesky ), talking about integrity.

I’ll admit that it’s been a while since I’ve written a blog post. It’s been a combination of either burnout or busyness, but let’s see if I still have the old chops. Plus, I’m currently sick and resting in bed, so I have nothing better to do.

I’m one of the few who haven’t had experiences with corruption in our database. Apart from Steve Stedman’s ( blog ) Database Corruption Challenge, that is.

With that being said, what do I have to say about this topic then? Well, let’s talk about the first version of corruption checking automation that we introduced in work.

Now, this is just the bare bones and many different iterations since then, but the essence is here.

Overview

Like many shops out there, we can’t run corruption checking on our main production database instance. So, then, what do we do? We take the backups and restore them to a test instances, and then run corruption checking on those restored databases.

At least this way we can test the backups we take can be restored, as well.

But, I don’t want to spend every day manually restoring and corruption checking these databases, so let’s automate this bit…

Limitations

A quick scour of the interwebs brought back something extremely close to what I want by Madeira Data Solutions. It had some extras that I didn’t want, though.

More importantly, it used some functions that our dreaded antivirus software still screams false positives about. So, they would stop our script from running if we even tried.

So, a script re-write was required.

Script

Here’s the link to the script in GitHub if you want to check it out.
Use at your own peril. Also, default gist embedding on this platform is visible off-putting.

General Rundown

Here’s a general rundown of the parts of the code, just to make it easier to grok. It’s not to bump the word count on this post.

Cave Canum

This is an old version of the script. The first version, if my flu-riddled brain can remember correctly, so there are small bugs here.

Off the top of my head, I think it still:

  • only works if the backup file is called FULL
  • only works if there is a single FULL backup file per database ( _that got fixed real fast!_ )
  • A.N.Other

Grab the Full Backup

Does it expect FULL in the file name? Yes. Should it? Arguably not.

Restore Everything on the Path

We did a database per database basis. “RBAR” is fine outside a DB, right?

Faire le Corruption Check

Sacre Bleu! No corruption for you!

Report on the results

Because if you don’t tell people, have you actually done it?

Ar aon nós

Like I said, that script is the first iteration.

Use at your own peril.

Here be dragons and krakens and DBCC WRITEPAGE.

My main point is have the INTEGRITY to say “Yes, I have regular corruption checking of my databases”.

See what I did there?

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 🙂