Problems Creating XML Schema Collection

Ever created an XML Schema collection before? Our developers work with a lot of XML so I wasn’t surprised when eventually a request came in about permissions with XML SCHEMA COLLECTION.

Surprised that they had a permissions issue, yes, but not surprised that they were working with XML.

Why is it “an XML” and not “a XML”?

For information purposes, I’d normally provide a brief description of what an XML SCHEMA COLLECTION is but, being completely honest, I’m still not sure I can vocalize it in an understandable way. It’s kind of like explaining the colour purple without using other colours (and yes, that’s colour with a ‘u’).

I know what it is, I just can’t explain it properly…yet

So what I’m going to do is point you to the link for Microsoft docs for XML Schema Collection (done) and just gloss right over it (nothing to see here).

Permissions Shane, you mentioned permissions.

Right, sorry.

Investigation first. This was on the Development server and they had emailed me the creation code along with the error message they had received, which was this guy:

CREATE XML SCHEMA COLLECTION name AS
N'RANDOM XML ALERT...'

Msg 2797, Level 16, State 2, Line 20
The default schema does not exist.

However, when I ran the code, I got a different error message, mainly this guy:

CREATE XML SCHEMA COLLECTION name AS
N'RANDOM XML ALERT...'

Msg 9459, Level 16, State 1, Line 3
XML parsing: line 2, character 34, undeclared prefix

Which meant I had to go back and tell them to fix their darn XML.

Now I’m pretty sure we have a problems though:

That error message did not fill me with confidence. Yeah, sure they had bad XML but I was now fairly sure that there was also a permissions problem. Mainly because if there’s one thing that I’ve learned so far, it’s this:

No good can come from two different people getting two different errors from the same code!

Proper XML:

Proper XML was provided and ran by the developers but the same error message came back…

CREATE XML SCHEMA COLLECTION name AS
N'random xml alert...'

Msg 2797, Level 16, State 2, Line 20
The default schema does not exist.

The difference this time was, when I ran the code, I received the following message

Command(s) completed successfully.

…That’s not good.

Developers happy. DBAs not.

At this stage, I’m nearly convinced that it’s a permissions issue.

Checking the permissions required to create an XML Schema Collection doesn’t help, since the Devs were part of the db_dlladmin database role, so that should have been covered.

In my head I’m thinking of all the things that I can do to try and troubleshoot this problem.

  1. Extended Events my session,
  2. Ask my Senior DBA,
  3. Cry

Then I realize that I’m jumping the gun again and I slow down, and check the first error message again. This time without the developers shouting in my ear, about permissions.

The DEFAULT schema

That says “schema”, not “permission”. Maybe the difference between the DBAs and the Devs was to do with default schema and not permissions this time. Let’s check it out!

SELECT
    IIF(principal_id = 1, 'DBA', 'Dev') AS DBPrincipal,
    default_schema_name
FROM sys.database_principals
WHERE principal_id IN (1, 14);
DiffSchema
Devs don’t even have a default schema!

Wait, so it was a SCHEMA issue?

Have you checked the Examples section of Microsoft Docs? Normally, they are a great source of material for examples but if you check out the examples for XML Schema Collection , not one of them shows the schema name in the examples.

So, I walk over to the original developer and his machine, change his code to…

 
CREATE XML SCHEMA COLLECTION dbo.name_test AS 
N'RANDOM XML ALERT...' 

And it works!

Apparently what had happened was the Senior Dev had gotten sick of developers not specifying the schema when creating objects and had asked the Senior DBA to remove the default schema for Developers. That seems to work (by that I mean, everything error-ed out correctly), they were happy that developers now had to specify the schema, and life moved on.

Yet, later on, when the developer read the docs for XML Schema Collection, and saw that there was no schema in the examples, it didn’t cross their mind that a schema was required. So they didn’t specify it and that, in combination with no default schema, caused this whole mess.

The (fast food) takeaways:

  1. Slow down! Don’t jump the gun,
  2. Developers don’t know everything,
  3. It’s not always permissions,
  4. Schemas are important(!),
  5. Having checklists for investigations are highly useful, and
  6. Documentation, especially on past decisions, are even more useful!

Apologies for the blurb of a blog post but I have to go.
Apparently, there’s a permissions issue with a Stored Procedure now…

 

 

Keeping New Lines in SQL Server.

Where I compare scripts to BBQ because of course I would 😐

I have this personal opinion that one sign of a good DBA is their ability to automate things and, before the DBA world found PowerShell, the way to do this was with T-SQL.

For example, a T-SQL script to get permissions assigned to a database principal could also include a column to REVOKE those permissions. This could be “automated” with some dynamic SQL.

SELECT dprin.name AS DatabasePrincipalName,
       OBJECT_NAME(dperm.major_id) AS ObjectName,
       dperm.permission_name AS PermissionName,
       N'REVOKE '
         + dperm.permission_name
         + N' ON OBJECT::'
         + OBJECT_NAME(dperm.major_id)
         + N' FROM '
         + dprin.name COLLATE Latin1_General_CI_AS AS RevokeMe
FROM sys.database_permissions AS dperm
INNER JOIN sys.database_principals AS dprin
  ON dperm.grantee_principal_id = dprin.principal_id
WHERE dprin.name = 'public';
RevokePermissions
This can be improved A WHOLE LOT…

What about if we want to improve this?

This is nice but what about if we are paranoid forward-thinking enough to realize that this could cause us problems?

“How?” You ask. Well what happens if there existed another database, say [NeedsAllPermissions], with the same table name and the same login has permissions on it.

Are you going to revoke permissions from that database? It needs ALL of them! It says so in the name!

So in an effort to not shoot ourselves in the foot, we add in the database name to our revoke script.

SELECT dprin.name AS DatabasePrincipalName,
       OBJECT_NAME(dperm.major_id) AS ObjectName,
       dperm.permission_name AS PermissionName,
       N'USE '
         + DB_NAME()
         + 'GO'
         + N'REVOKE '
         + dperm.permission_name
         + N' ON OBJECT::'
         + OBJECT_NAME(dperm.major_id)
         + N' FROM '
         + dprin.name COLLATE Latin1_General_CI_AS AS RevokeMe
FROM sys.database_permissions AS dperm
INNER JOIN sys.database_principals AS dprin
  ON dperm.grantee_principal_id = dprin.principal_id
WHERE dprin.name = 'public';

WithDBNoChar10

Yes, we’re only using our database now!

So all is well with the world…

Until the day comes when you actually want to revoke permissions to that user. So you run the above code, copy the RevokeMe column and paste it into the management window. and you get…

NoNewLine
No GO my friend…

GO is a special little guy. It’s not exactly T-SQL. It’s a way of telling the SQL Server Management Studio (SSMS) to send everything before it, from the beginning of the script or the preceding GO, to the SQL Server instance.

If you read the documents, the main point to take away is…

A Transact-SQL statement cannot occupy the same line as a GO command. However, the line can contain comments.

GO is a special little snowflake and needs to be on his own line then. Simple enough if you know that SQL Server converts CHAR(10) into a New Line.

If you didn’t know that, well you know that now….P.S. CHAR(13) is a carriage return 😉

So let’s update our script with some CHAR(10) and see what happens then.

SQL & BBQ, both work well with CHAR

SELECT dprin.name AS DatabasePrincipalName,
       OBJECT_NAME(dperm.major_id) AS ObjectName,
       dperm.permission_name AS PermissionName,
       N'USE '
         + DB_NAME()
         + CHAR(10)
         + 'GO'
         + CHAR(10)
         + N'REVOKE '
         + dperm.permission_name
         + N' ON OBJECT::'
         + OBJECT_NAME(dperm.major_id)
         + N' FROM '
         + dprin.name COLLATE Latin1_General_CI_AS AS RevokeMe
FROM sys.database_permissions AS dperm
INNER JOIN sys.database_principals AS dprin
  ON dperm.grantee_principal_id = dprin.principal_id
WHERE dprin.name = 'public';

 

WithDBAndChar10
That smokey, wood-fire CHAR

Now, when we paste the RevokeMe column to a new window, we get…

StillNoNewLine
Oh look, it’s a wild, rare nothing…I love them!

…absolutely no difference. 🙂

Why am I smiling?

Here, around 500 words in, we get to the meat of our post. How do we keep new lines when copying in SQL Server?

Tools | Options | Query Results | Results to Grid | Retain CR/LF on copy or save

Two things need to be done here.

  1. This checkbox needs to be enabled.

    KeepNewLines
    CHECK!
  2. A new window needs to be opened and used.

New window open, we run our script again, and this time, when we copy and paste the results, we get…

FinallyWorks
Winner, Winner, BBQ Chicken Dinner

Dessert:

So if you are using T-SQL to create scripts, and you’re having this problem with GO or just new lines in general, make sure that the “retain CR/LF on copy and save” checkbox is ticked.

Now, improve that script more, throw it in a stored procedure, and you never know, it may be semi-useful. 🙂

Why You May Need More Than FOR XML PATH(”)

XML – both easy, easy, lemon easy and difficult, difficult, lemon difficult…

Working on a blog post and I came up against a problem that I had heard of before but did not spend much brain-CPU power against.

know I’m going to run into this again so let’s document this for future me. Oh he’s going to appreciate this so much!

Commas are all the rage nowadays:

There are a fair number of questions nowadays about returning data from a database in a comma separated string. Sure the application should probably do that but hey, database servers are expensive, why not get some bang for your bucks!

Done!-ish…

SQL Server 2017 has this lovely function called STRING_AGG()

Pop in your column and your separator and it takes care of it for you!

…wait not everyone has SQL Server 2017 yet?

…wait don’t have SQL Server 2017 yet? Oh, I should really fix that…

Pre-SQL Server 2017:

So what can we do if we are not on SQL Server 2017? Take the advice that I was given for most of my life and STUFF it!

The STUFFing:

Our playground:

USE tempdb;

-- Test table
SELECT dt.comments
FROM ( VALUES ( 'XML is the bomb!'),
              ( '& JSON is cool too...')
     ) AS dt (comments);
TestTable
testing stuff…

 

Plain Old STUFFing:

I’m not the biggest fan of stuffing if I’m honest…tastes like dirt to me but hey, it works in 99% of situations…

SELECT STUFF((SELECT ', ' + dt.comments
              FROM ( VALUES ( 'XML is the bomb!'),
                            ( '& JSON is cool too...')
                   ) AS dt (comments)
              FOR XML PATH('')
             ), 1, 1, '') AS CommentsEnXML;

 

StandardWay
…that’s…that’s not what I said!

Bacon Sausage STUFFing however:

So…SQL Server is trying to be too helpful. What do we do? I normally turn to the SQL community and people like Rob Farley ( blog | twitter ), who has a lovely post about this.

So let’s try it out.

SELECT STUFF((SELECT ', ' + dt.comments
              FROM ( VALUES ( 'XML is the bomb!'),
                            ( '& JSON is cool too...')
                   ) AS dt (comments)
              FOR XML PATH(''),
              TYPE).value('.', 'varchar(max)'
             ), 1, 1, '') AS CommentsEnXML;

 

BetterWay
That exclamation mark is annoying me 😡

I just eat the turkey around the STUFFing:

Little hiccup in preparing for my next post. Thankfully I learn from my mistakes and failures (there’d be no help for me otherwise!).

I’ll leave this post with a quote from the blog of the main man himself:

It’s a habit I need to use more often.

Yeah, me too Rob, me too…

SQL Prompt: For Your Group By Problems

I’m going to point people to this that have “My Group By isn’t working” questions…

The Joys of SQL:

Did you know that the SQL language allows you to do amazing analysis of data such as aggregate functions?

SELECT t.session_id,
       t.request_id,
       SUM(t.user_objects_alloc_page_count) AS UserObjectAllocated,
       SUM(t.user_objects_dealloc_page_count) AS UserObjectDeallocated
FROM sys.dm_db_task_space_usage AS t
GROUP BY t.session_id,
         t.request_id;
Works Written
0’s! Amazing!

The Pains of SQL:

But…if you forget to put in the GROUP BY clause, as a ski instructor once said, you’re going to have a bad time!

Need Group By
Pizza…French Fries…Pizza

The Repetitiveness of Questioners:

So some eager yet lost scholar ventures into this land of aggregate functions, reads the error message and adds in a GROUP BY clause.

SELECT t.session_id,
       t.request_id,
       SUM(t.user_objects_alloc_page_count) AS UserObjectAllocated,
       SUM(t.user_objects_dealloc_page_count) AS UserObjectDeallocated
FROM sys.dm_db_task_space_usage AS t
GROUP BY t.session_id;
Needs second column
French Fries….

Now don’t scoff, this happens. I mean the error message is still red, looks nearly identical to the original one encountered, and can cause a rage-inducing damnation of SQL Server error messages.

The Enlightenment of Questioners:

Trawling the bulletin boards, question sites, and forums – okay maybe a quick question online, it’s called poetic exaggeration people! – they eventually learn the folly of their ways and correct their mistake.

SELECT t.session_id,
       t.request_id,
       SUM(t.user_objects_alloc_page_count) AS UserObjectAllocated,
       SUM(t.user_objects_dealloc_page_count) AS UserObjectDeallocated
FROM sys.dm_db_task_space_usage AS t
GROUP BY t.session_id,
         t.request_id;
Works Written
PIZZA!

The Euphoria of SQL Prompt:

Now I consider myself lucky that work has invested in the RedGate tools, and right now, especially SQL Prompt.

I’m not going to talk about “Save and Recover Lost Tabs” – saved my ass many times.
I’m not going to talk about “Code Formatting” – saved my sanity many times.
I’m going to talk about “Autocomplete”.

A well-known secret with SQL Prompt’s autocomplete is the snippets feature. With this, you can increase your productivity by 75% from typing out G R O U P [space] B Y and instead use gb and hit tab.

gb shortcut
Wait? I can order Pizza?

The Ecstasy of SQL Prompt:

Do not get me wrong, a 75% increase in productivity? I’ll take that!

That is a well-known secret though, and it’s slightly hard to get excited about a well-known secret.

However, what if I told you that SQL Prompt had another lesser-known secret that can increase your productivity and ensure that you do not forgot to add the necessary columns to your GROUP BY clause?

Interested? Ah c’mon!
You sure you’re not interested?…. That’s better!

So first of all, let us increase the number of non-aggregated columns in our SELECT to include database_id, is_remote_work, and exec_context_id. Including our session_id and request_id these are all columns that we are going to need to add to our GROUP BY clause, because…well…business logic.

Only problem is ain’t nobody got time for that.
SQL Prompt knows this and adds the following little snippet after a GROUP BY autocomplete.

Shortcut shortcut
Whoa! Whoa! You can deliver Pizza to me?

Hitting tab on that includes everything in the SELECT that is not part of an aggregate function, leaving us to concern ourselves with loftier things…

Final Works
Like whatever happened to Pizza in 30 mins or free?

Pizza:

Now I don’t work for pizza RedGate, I’m not affiliated with them, and I don’t get any money off of them. In fact, I’d say that they’d happily pay me not to write about them but when I found this autocomplete feature, I got too happy not to share it!

So save yourself the trouble of typing everything out and spare yourself the pain of error messages.

Use this lesser-known secret and have more time for pizza.

Table Column Differences with T-SQL and PowerShell

Where I start off with one idea, than veer widely towards another…much like me normally

As part of my post for the latest T-SQL Tuesday, I talked about community scripts. One of these scripts was by Jana Sattainathan ( blog | twitter ) and it was to do with finding tables with similar table structure. This is a great script for initially finding the tables and something that I am not sure that I would have thought of!

However, do me a favour and in your favourite search engine, do a search for “stackoverflow sql server wide tables” (Google search included for your benefit).

For some reason, people have a slight fascination with wwwwwiiiiiiiiddddeeeee tables!

So thanks to the excellent work done by Jana, you have now identified the tables with similar structure, but what about if we want to know which column names match exactly?

I recently had this problem with consolidating copies of tables of medium to wide length in a database and needed to know. So I created a little snippet of code to help me out and I present it to you, in all of it’s unashamed vulnerability, in the hope that it can help you out.

Be warned: Thar be PIVOTs ahead!

Ahoy, matey!

So we’ve got two tables that are slightly different, not completely different (hey, they could be) but different enough to be similar without matching… (differently similar?)

Our task is to find out which columns are in both tables, which columns are only in 1 table, and which columns are only in the other.

Now this isn’t so bad manually, especially if you only need to do this once, maybe twice. What happens though if you need to do it with multiple pairs of tables? Or multiple pairs of wide tables like our search engines showed us above?

So let us do what all DBAs should do when they have a repetitive, manual task stopping them from doing more fun important things: Automate it away!

Avast Ye!

Our two tables are as follows:

CREATE TABLE dbo.DifferenceTable01
(
    col1 int,
    col2 int,
    col4 int,
    col6 int
);
GO

CREATE TABLE dbo.DifferenceTable02
(
    col2 int,
    col3 int,
    col4 int,
    col5 int
);
GO

Now we can use the sys.columns table to check out the different columns in the table but the results are, well, meh

SELECT 
    OBJECT_NAME([object_id]) AS TableName,
    [name] AS ColumnName
FROM sys.columns 
WHERE [object_id] IN
(
    OBJECT_ID(N'dbo.DifferenceTable01', N'U'),
    OBJECT_ID(N'dbo.DifferenceTable02', N'U')
);
GO
sys_columns_results.PNG
Even if I ordered it, it would still be “meh”…

That is not really going to work out for us…
So I’m not liking the look of this, and going through the results, it seems to me that these results are just not useful. This isn’t the computers fault – it’s done exactly what I’ve told it to do – but a more useful result would be a list of columns and then either a simple ‘Yes’, or a ‘No’.

There’s syntax for this…PIVOT

Thar She Blows!

As anyone who has seen me dance can attest to, I can neither shake, rattle, nor roll. And I definitely do not normally PIVOT. However, as I’m trying to know my tools, I do know that this is the syntax that I need.

PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output.

So after looking up the syntax for this once (ok, 5 times!) I managed to come out with a script that I’m reasonably happy with.

And the results are a lot easier to read 🙂

human_readable_format
Can also be extended to more than 2 tables!

So much better! This way, no matter how long the tables, I can easily figure out what columns are in what table(s) based on their names.

Shiver Me Timbers!

Isn’t it always the way after you’ve done something, you realise a much easier way to do it?

The Old Seadog!

I’ve talked about automation in this post and I have yet to mention PowerShell. I bow my head in shame.

Especially when it has a native command like Compare-Object.

help Compare-Object -ShowWindow

Synopsis
Compares two sets of objects.

Description
The Compare-Object cmdlet compares two sets of objects. One set of objects is the “reference set,” and the other set is the “difference set.”

The result of the comparison indicates whether a property value appeared only in the object from the reference set (indicated by the <= symbol), only in the object from the difference set (indicated by the => symbol) or, if the IncludeEqual parameter is specified, in both objects (indicated by the == symbol).

If the reference set or the difference set is null ($null), this cmdlet generates a terminating error.

So the question we have to ask ourselves now is “Can we do what we did with the PIVOTs easier?”

The Old Salt.

I’ve recently found out about splatting so, of course, I’ve started to use it EVERYWHERE!

Let’s “splat” our two parameters

$Table01Param = @{
ServerInstance = 'localhost\SQLDEV2K14'
Database = 'master'
Query = "SELECT OBJECT_NAME([object_id]) AS TableName, [name] AS ColumnName FROM sys.columns WHERE [object_id] = OBJECT_ID(N'dbo.DifferenceTable01', N'U');"
}

$Table02Param = @{
ServerInstance = 'localhost\SQLDEV2K14'
Database = 'master'
Query = "SELECT OBJECT_NAME([object_id]) AS TableName, [name] AS ColumnName FROM sys.columns WHERE [object_id] = OBJECT_ID(N'dbo.DifferenceTable02', N'U');"
}

And we now save ourselves the trouble of writing the parameters to the functions.

Invoke-Sqlcmd @Table01Param
Invoke-Sqlcmd @Table02Param
splat-attack
SPLAT-ATTACK!!

Since everything is now set up, we can just pass those results into 2 different variable holders and use our Compare-Object.

$Table01 = Invoke-Sqlcmd @Table01Param
$Table02 = Invoke-Sqlcmd @Table02Param

Compare-Object -ReferenceObject $Table01 -DifferenceObject $Table02 -Property ColumnName -IncludeEqual
Non-splat-attack
It’s annoyingly wide without splatting…

And for anyone saying “Yeah, but who knows what ‘SideIndicator’ means!”,  I advise you to read the help with PowerShell. It helps a-lot!

The result of the comparison indicates whether a property value appeared only in the object from the reference set (indicated by the <= symbol), only in the object from the difference set (indicated by the => symbol) or, if the IncludeEqual parameter is specified, in both objects (indicated by the == symbol).

If you are still complaining about it – “You can’t have the HumanReadableFormat column like you did in T-SQL” – then please stop. There are experts out there who can make you go “wow” with what they do. I’m still learning but even I have an answer to that.

Compare-Object -ReferenceObject $Table01 -DifferenceObject $Table02 -Property ColumnName -IncludeEqual |
Select-Object -Property *,@{N='HRF';e={switch ($_.SideIndicator)
{'==' {'Both'}
'=>' {'Table 2 only'}
'<=' {'Table 1 only'}
}
}}
hrf
Go on! Compare this with T-SQL

Land-ho!

I’m not trying to argue with who would win between T-SQL and PowerShell, that was definitely not my intention with this post. T-SQL is my first language love, PowerShell is very quickly becoming my second (and that’s not just because it’s the only other langauge I know).

They both accompolish the same thing!

It’s just a matter of preference, that’s all, not a matter of competition. You want to work in SSMS, PIVOT it up! You like PowerShell, hammer that Compare-Object nail!
Whatever works for you.

My first idea for this was T-SQL but it turns out for me that PowerShell is the easier option. Don’t rule out one, just because the other was the first thing to pop into your head.

Now I’m going to go before I make a joke about Pirates, SQL and the R language…

 

Dealing with System.Data.DataRow.

Words: 1018

Time to read: ~ 5 minutes

Tl;Dr: Make sure you’re calling the property, not just the variable i.e. $Var.ColumnName, not just $Var

Expert Opinion.

I had being sitting on this blog post for a while but then came a recent blog post by Mike Fal ( b | t ) that defended the use of  Invoke-Sqlcmd. Well, it turns out that Mike’s post was in response to Drew Furgiuele’s ( b | t ) blog post condeming it!

If that wasn’t bad enough, I then came across an article by Steven Swenson ( b | t ) that was in response to Mike’s article. Guess what? Another condemnation of  Invoke-Sqlcmd!

It seems that Invoke-Sqlcmd is the Marmite of the PowerShell/SQL Server world. That’s the equivalent of the Crunchy Peanut Butter versus Smooth Peanut Butter debate for my American readers. (Hi Aunt Kate and Uncle Tom!)

Now if you want some real concise, knowledgeable, and professional opinions on the pros and cons of this command, I encourage you to check out those blog posts. I’ve linked to them and I’ve read them all, each with a blend of “oh yeah” and “huh, good point” comments thrown in.

Let’s Get Personal.

The reason that I wanted to throw in my thoughts in this debate is because, as much as I love Mike’s article, it doesn’t deal with the biggest problem that I had with Invoke-Sqlcmd.

Dealing with those stupid, annoying System.Data.DataRow

system-data-datarow
Look at them there…taunting us!

I eventually  figured out how to deal with these and wanted to pass the information on.

The Set Up.

For all those playing along at home, I’ve got a SQL Server 2016 Developer Edition with a copy of WideWorldImporters, as well as PowerShell version 5.

Let’s see how many customesr we have…

SELECT COUNT(*)
FROM Sales.Customers;
CustomerCount
I am not adding 2 more customers, no matter what!

Now I don’t know about you but when I query stuff in a SQL database, it’s to do something to/with the results. They could be a list of servers that I monitor, they could be a list of databases that I want to check the recovery model of, or it could be a list of tables that I want to see how much space they are using. The main point is that I want to do something with the results.

But for this simple case, I just want to list out the customer name from this table. Simple? Yes, but this is just a test case to prove a point.

So let’s PowerShell this!

And so our problems begin.

Now, the basic premise is this:

For each customer name, I just want to output the line “Currently working on” & the customer name.

Now this is based on a real world example where it was a list of servers and I wanted to include this in Write-Debug.

Pain 1.

Invoke-Sqlcmd -ServerInstance localhost -Database WideWorldImporters -Query @"
SELECT CustomerName AS Name
FROM Sales.Customers;
"@ | ForEach-Object {
  "Currently working on $_"
}

Nice and simple PowerShell command, what I would call a “Ronseal” but when we run it…

system-data-datarow
grr!

I’m just going to follow this up with code and pictures of what I tried to do to get this to work…Hopefully you’ll get some amusement out of this…

Pain 2.

In this case I figured maybe I should put the results into a variable first and then see if it could work.

$Employees = Invoke-Sqlcmd -ServerInstance localhost -Database WideWorldImporters -Query @"
SELECT CustomerName AS Name
FROM Sales.Customers;
"@

foreach ($employee in $Employees) {
    "Currently working on $employee"
}
system-data-datarow
Nope!

Pain 3.

Well I know that PowerShell arrays start at 0, and I know that I can get the count of elements in an array by using <variable>.count so maybe that will work?

0..$Employees.Count |
    ForEach-Object {
        [int]$i = $_

        $employeeRange = $Employees[$i]

        "Currently working on $employeeRange"
    }

 

 

0basedArrayNotHighlighted
Nope!

Pain 4.

A quick check on Google points me to using ItemArray with my loops so I try that.

0..$Employees.Count |
    ForEach-Object {
        [int]$i = $_

        $employeeRange = $Employees[$i].ItemArray

        "Currently working on $employeeRange"
    }
0basedArray
YES!!! Wait…what the?

Ahhh! I know that PowerShell is 0 based but I didn’t realize that means the count is going to give me 1 extra row! Plus that’s a bit too much lines for my liking. All that just to output a customer name? Nah let me try again.

Pain 5.

for ($i = 0; $i -lt ($Employees.Count);, $i++) {

    $EmployeeFor = $Employees[$i].ItemArray

    "Currently working on $EmployeeFor"
}
forgood
FORtunately FOR gets me the FORenames (get it?)

The Real Solution.

If only I had run this…

$Employees | Get-Member

You know, there’s a reason that they say the 3 best commands are Get-Help, Get-Command, and Get-Member.

It’s because they save so much time if you just look at them.

$Employees | Get-Member
e_gm
If I may direct your attention to the MemberType of “Property”…

As it turns out there is such an easier way to get the data values back from Invoke-Sqlcmd,

if you want the data, just change $_ to $_.<property>

Let’s see if it works for us.

Pleasure 1.

If we “correct” our original code…

Invoke-Sqlcmd -ServerInstance localhost -Database WideWorldImporters -Query @"
SELECT CustomerName AS Name
FROM Sales.Customers;
"@ | ForEach-Object {
    "Currently working on $($_.Name)"
}

 

forgood
Oh…that’s lovely!

Pleasure 2.

And what about with variables?

foreach ($employee in $Employees.Name) {
"Currently working on $employee"
}
forgood
Brings a tear to my eye, it does 🙂

It looks like we finally have a proper Ronseal moment.

Final Thoughts.

I have absolutely no problem with Invoke-Sqlcmd, so I suppose I fall into Mike’s side of the camp.

Do I use it all the time though? Not really.

The SMO objects have an amazing amount of information that is just too difficult to get with Invoke-Sqlcmd so I’ve started to use the SMO more and more.

But Invoke-Sqlcmd is a tool, just like everything else. There’s no point in throwing away a tool just because it isn’t the most optimal anymore, especially when it is so useful in adhoc situations.

There are some cases where a small handheld screwdriver is more useful than an electric one, just like there are some cases where Invoke-Sqlcmd is more useful than the SMO objects.

Just know your tools…

DBA Fundamentals Social Media

This is going to be a short message but since it counts as my first SQL Family post, rather than a SQL or PowerShell one, I felt it still deserved a little post on its own.

DBA Fundamentals.

I’m helping out with the DBA Fundamentals Virtual Group’s social media presence (I told you it was going to be short 🙂 ).


What’s the Full Story Smartass?

What happened?

‘”What Happened” with Query Store’, actually?

At the start of February, I was watching a YouTube recording of that webinar by the DBA Fundamentals Virtual Group on their YouTube channel. Since my normal commute times and the normal times that webinars are on clash so often, it’s how I normally have to watch them.
During the webinar Steve Cantrell ( t ), the host and Group Leader, mentioned that they were looking for someone to volunteer with helping them out with their social media.

Why?

Well, I had steadily become more and more…addicted, for want of a better word, to the various social media outlets out there, like Slack, Twitter, YouTube, and LinkedIn. From these, I have gained so much knowledge, insight and joy from the different ways to connect to the SQL Family.

How?

I’ve been wanting a way to give back for all that I have learned so far so I took a chance and emailed Steve about the volunteer work, fully realising the very real possibility that someone who had watched the webinar in real time may have already offered and been accepted.

As you’ll find out with nearly all members of the SQL Community, there was no instant rejection. We emailed back and forth, Steve detailing who they are, what they currently do, and what they think they need to do better.

I replied with who I am, what I currently do, and what I thought I could help out with.
I think my suggestions were as long as that last line.

February 10th, expecting an apologetic yet negative reply to my last email, I got an email from Steve. He said he had talked it over with the Co-Group Leaders Mike Brumley ( t ) and Niraj Mehta, and then proceded to write to most eloquent piece of literature I have so far read.

You are in.

Why now?

I’m writing this now because I’ve had a month to try my hand in that arena, to give it a go, and see what it’s like.

I like it!

The Group Leaders have put a tremendous amount of effort into the webinars and secured well known names like Kimberly Tripp ( b | t ), Paul Randal ( b | t ), and more giving their expertise free of charge to all that attend.

Plus the cat was already out of the bag at this stage since it’s been mentioned in the pre-webinar slides.

What now?

From yourselves?

Enjoy the great content, hit us up with any questions or suggestions, and please forgive me if I ever come across as annoying on social media. I promise to try and not do that.

As for ourselves, it’s going to be pretty damn busy.

The Group Leaders are continuing to procure great talents for the webinars (believe me I’ve seen a sneak of what’s to come), we’re going to be updating the Virtual Group page to go with PASS’ new branding, and also try and get all avenues of social media to a standard where anyone can take pride in what they see.

How now brown cow?

Yeah so…a longer post than I expected to write…my bad.

 

T-SQL Tuesday #88 – The daily (database related) WTF! The Biggest Danger to your Database: Me.

That is more of a blurb than a title…and this is more an apology than a blog post…

Kennie Nybo Pontoppidan ( blog | twitter ) has the honour of hosting this month’s T-SQL Tuesday and has decided to base this month’s topic on ‘The Daily (database related) WTF‘.

Now I have great time for Kennie and T-SQL Tuesday since my very first blog post was in reply to a T-SQL Tuesday and it happened to be a topic where Kennie blogged about the exact same thing!

Now, truth be told, I wasn’t planning on participating in this one and this wasn’t because of not having a WTF moment, but rather having too many of them. However, reading through most of the entries, I see a vast majority of them are about moments well in the past and caused by other parties.

This is not the case for me. My WTF moment happened recently and the culprit was … myself.

Sorry Kennie 😦

Friday:

A request came in from our Developers about a slow performing query and my Senior DBA identifies an index that can be safely modified to improve this ones performance.
So a Maintenance Window was set and it fell to me, in my role of Junior DBA, to create a SQL Agent Job to create this index.

No worries so far right?

I create a once-off SQL Agent Job to create this index, scheduled it appropriately, and I’m off on my merry way for the weekend.

Monday:

I come in on Monday morning,  check my email, and I see an alert in my inbox about my job as well as an email from my Senior DBA; He’s not angry…WTF?

My whole job had failed!

Unable to connect to SQL Server ‘(local)’. The step failed.

01. SQLAgentError
WTF!

He is not angry as he has seen this error message before, has dealt with it before, and sees it as a case of “well you’ve seen it now, investigate it and you won’t fall for it again”.

A quick investigation later pointed to this in the Error Log the moment before the SQL Agent Job Step was supposed to run:

[165] ODBC Error: 0, Connecting to a mirrored SQL Server instance using the MultiSubnetFailover connection option is not supported. [SQLSTATE IMH01]

04. ErrorLogMessage
WTF?

Long sub-story short (i.e. Google-fu was involved), the main reason that this failed is that the SQL Agent Job Step has been configured to use a Database that is currently a mirrored one.
And SQL Agent does not like when you try to start off a step in a database that is mirrored.

02. WrongDBSetUp
WTF is wrong with this?

So the solution for me was to set the Job Step property ‘Database’ to a non-mirrored database (preferred: [master]), then include a “USE [<mirrored database>]” in the ‘Command’ property.

03. RightDBSetUp
WTF!

Knowing what to do now, and having identified another maintenance window for the next morning, I make the required changes to the job step and continue on with my day.

Tuesday:

I come in on Tuesday morning,  check my email, and I see an alert in my inbox about my job as well as an email from my Senior DBA; He’s angry…WTF?

My final job step had failed!

CREATE INDEX failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. [SQLSTATE 42000] (Error 1934).  The step failed

05. SecondSQLAgentError
WTF!

Now I’m angry too since I count these failures as personal and I don’t like failing, so I get cracking on the investigation.
Straight away, that error message doesn’t help my mood.
I’m not indexing a view!
I’m not including computed columns!
It’s not a filtered index!
The columns are not xml data types, or spatial operations!
And nowhere, nowhere am I using double quotes to justify needing to set QUOTED_IDENTIFIER on!

SO WTF SQL SERVER, WHY ARE YOU GIVING ME THESE ERRORS???

SET QUOTED_IDENTIFIER must be ON when you are creating or changing indexes on computed columns or indexed views. If SET QUOTED_IDENTIFIER is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail.

I’ve talked about stupid error message before… but in my current mood I wail, beat my breast, and stamp my feet!
The error message above was not complaining about the index I was creating, it was complaining about indexes already on the table!
In my case, we had filtered indexes already created on the table and, as such, every single index on this table from then on requires SET QUOTED_IDENTIFIER ON.

USE [TEST];
SET QUOTED_IDENTIFIER ON;

CREATE ...

Third Time’s the Charm?

No, not this time.

Luckily the Senior DBA had come in while the maintenance window was still running and manually ran the create index script.

He wasn’t angry that my job step failed. He was angry that my first job step succeeded!

Are you going “WTF? Why is he angry about that?” Let me enlighten you…

Remember at the start of this blog post I said that he had identified an index that could be safely modified?
Well, on Monday, in my haste to fix my broken job I had focused too much and thought too granular.
My second job step that created the index had failed, but my first job step, the one that dropped the original index had succeeded.

There’s not really much more to say on this. In my rush to fix a broken job, I created a stupid scenario that was luckily caught by the Senior DBA.

Wrap Up:

Yeah…so thought it would be a nice, little counter-example to the other posts out there about third parties coming along and wrecking havoc, and the DBAs swooping in to save the day.

I could make up excuses and say that, as a Junior DBA, I’m expected to make mistakes but I’m not going to.

It should be the aspiration of every Junior DBA to strive to improve and move upwards, and one of the key aspects of this is responsibility.

You should be responsible for looking after the data, looking after the jobs, and looking after the business.
And if all else fails, you should be responsible for your actions.

I have been properly chastised by my Senior and am still chastising myself for this. It’s been a long week so far…

… and it’s only Tuesday…wtf?

[PowerShell] Getting More From Generic Error Messages.

There’s more to $error than meets the eye.

What we know already:

SQL Server has some really stupid, generic error messages.
Case in point…

String or binary data would be truncated.

Yes, we know what it means but what column would be truncated? What value would be the offender here?
I am okay with not having the exact answer but it would be nice to have more!

What I learned:

PowerShell actually has some pretty generic error messages as well.
Since I am using PowerShell mainly for interacting with multiple SQL instances, my PowerShell errors mainly revolve around SQL Server.
So this error message is not helpful.

initialerrormessage

(I’m slightly colour-blind so I can barely read red on blue, I find this green (yellow?) easier)

Can we get more?

Sure we can but let’s set up an example so you can play-along at home too.

First of all, what PowerShell version are we using?

$PSVersionTable.PSVersion
psversion
Latest as of…when I updated it

Great! So let us add in our assemblies that will allow us to connect to SQL Server using SMO.

# Load the assembly since we probably do not have it loaded
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo')
assembly_loading
This is technically depreciated but I’m not going to remember that whole location…

Now I like the results showing up but if you don’t want them, just throw a $null =  before the [System.Re... bit.

# SILENTLY load the assembly since we probably do not have it loaded
$null = [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo')

Now let us connect to mine (or your) database to run some scripts against it.

# Connect to the instance and database
$SQLInstance = New-Object Microsoft.SqlServer.Management.Smo.Server 'localhost'
$Database = New-Object Microsoft.SqlServer.Management.Smo.Database
$Database = $SQLInstance.Databases.Item('Pantheon')

Everything is getting thrown into a variable/object here so there is going to be no output. Just change the ‘localhost’ bit to your server and ‘Pantheon’ to your test database.

Now, let’s get our T-SQL on!

# Create our T-SQL statement.
$sql = 'SELECT SERVERPROPERTY('ProductLevel') AS What?, SERVERPROPERTY('ProductVersion') AS Huh?;'
initialsqlerror
PowerShell & SQL…

You can see the first problem we run into here. The single quotation marks are breaking up our statement.
There are 2 fixes for this; we can double quotation mark the start and end of the string e.g. "SELECT ..." or we can do what we normally do in SQL Server and double up the single quotation marks e.g. (''ProductLevel'').
I’ve gone with the latter but hey, you choose, go crazy, whatever you want!

So now we have this:

initialsqlfix
You can probably already spot the error here from a T-SQL viewpoint…

Now let us run this against our database and see what happens.

# Execute with results...kinda like it says...
$Database.ExecuteWithResults($sql).Tables
initialerrormessage
GENERIC MESSAGE ALERT!

The whole reason for this blog post i.e. stupid, generic error message.

Now ignoring the fact that you already know what is wrong, this tells me that there is either something wrong with the $Database variable, the $sql variable or the syntax statement. Maybe even something else though!
This is not helpful and I’m going to have a bad time.

I encountered this lately and thanks to Chrissy LeMaire ( b | t ), I was introduced to the $error variable.
You can look up what this guy does by running the following on PowerShell,

help about_automatic_variables -showwindow

but the main point is that $error …

Contains an array of error objects that represent the most
recent errors. The most recent error is the first error object in the
array ($Error[0]).

So we want more information about our error message so we go…

$Error[0]

And we get…

initialerrorvariable
…well at least I can read it easier…

the same…
This…this is not what I wanted.
Thankfully, the defintion states that it is an error object and we know that objects can have more properties than what is shown be default.

So we try again, making sure that we return everything

# More than Generic
$Error[0] | Select-Object *
initialerrorvariableall
Great, now “More than a Feeling” is stuck in my head…

Bingo, that is a lot more helpful! Especially when we scan the results and we see this guy (highlighted)

initialerrorvariableallhighlighted
You saw that that was going to be it, right?

We may be working with PowerShell but we still have to obey SQL Server’s rules. So if we want to have a column with a question mark, we’re going to need to wrap it in square brackets.
So let’s fix up our $sql variable and try again.

# fix me!
$sql = 'SELECT SERVERPROPERTY(''ProductLevel'') AS [What?], SERVERPROPERTY(''ProductVersion'') AS [Huh?];'

We re-run out execute…

#Execute with results...kinda like it says...
$Database.ExecuteWithResults($sql).Tables

Lo-and-behold!

results
Those are stupid columns names, to be fair…

Like a sheepdog, let’s round it up:

I’m liking PowerShell more and more as I use it.

That is mainly outside of work but I’ve already turned my gathering of daily checks data from a half hour long process to a 2 minute one.

So it’s nice to know that, while it may have stupid, generic error messages, it also has the tools to help you with them.

Now if we could only get the tools to deal with “String or binary data would be truncated”…