[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”…

 

Gotta Love That LIKE

LIKE a function… now the song is stuck in your head!
… I’m not sorry…

Start: (‘abc%’)

Ever heard of “Osmosis”? You know, the…

process of gradual or unconscious assimilation of ideas, knowledge, etc.

For the longest time, that was how I thought people learned in SQL Server. You hang around a technology for long enough and the information about it slowly trickles into your brain.

I would hazard that the vast majority of people learn like this. They work with something long enough and slowly they develop, if not a mastery of the subject, then a familiarity with it.

That’s how I learned Transact-SQL anyway.
Working in a help desk, trouble-shooting stored procedures and ad hoc statements; cranking out reports left, right and center, slowly absorbing the differences between INNERLEFT, RIGHT, and FULL joins. Realizing that there is a vast difference between excluding results with a WHERE clause and with a HAVING clause.

Ahh good times!

However, now I’m in the mindset that if you really want to learn something then study it; purposefully and deliberately.

And with all the new features being released for SQL Server 2016, you would be amazed at what I can learn about features that were released in SQL Server 2008.

So here’s some little known facts I learned about LIKE

Middle: (‘%lmnop%’)

Safe to say, that we’ve all used LIKE, we’ve all seen LIKE, and we’re probably all going to continue to use LIKE.
But are we using it to the best of our ability?
I wasn’t.

So let’s test out this bad boy using the WideWorldImporters database, see if we can find everyone with the first name of Leyla.

Simple right? And because [Sales].[Customers] uses the full name, we have to use LIKE.

SELECT CustomerName
, CustomerID
FROM Sales.Customers
WHERE CustomerName LIKE 'Leyla%';
GO
leyla
LEYYYYla!!!!

Now a developer comes along and says “Wait a second, my sister is Leila”. So we try to cheat and add a wildcard in there.

SELECT CustomerName
, CustomerID
FROM Sales.Customers
WHERE CustomerName LIKE 'le%a%';
GO
leiyla
…you got me on my knees…

Leonardo!? Well I suppose he does count in this situation, but there’s 2 characters between the ‘e’ and the ‘a’ and I only wanted one.

Well you can specify only 1 wildcard with the LIKE function by using the underscore (‘_’), so let’s try that.

SELECT CustomerName
, CustomerID
FROM Sales.Customers
WHERE CustomerName LIKE 'Le_la%';
GO
lejla
…singing darlin’ please…

Yes, I cheated and inserted that extra name ‘Lejla’.

Call it Poetic Licence but I only used it to show that we still have options if this is not the results that we want. We are only interested in ‘Leyla’ and ‘Leila’.

‘Lejla’, while a lovely name I’m sure, is not what we require right this second. So what are we to do?

Well, did you know that LIKE has the range function as well? What’s range got to do with it? Well, what happens if we only put in a range of 2 characters?

SELECT CustomerName
, CustomerID
FROM Sales.Customers
WHERE CustomerName LIKE 'Le[iy]la%';
GO
better
….darlin’ won’t you hear my foolish cry!

There we go! Only the results that we want and none of that Lejla, Leonardo business.

Now you could argue with me (I encourage it actually. How else am I to learn?) and say that you would never do it this way. That it is much easier to do something along the lines of this:

SELECT CustomerName
, CustomerID
FROM Sales.Customers
WHERE CustomerName LIKE 'Leyla%'
OR CustomerName LIKE 'Leila%';
GO

I’ll admit that the above reads a lot easier, but it doesn’t scale very well though. What happens if we want to include the Leala, Lebla, Lecla,….all the way to Lenla’s? Are you going to write out 15 different clauses? 1 for each different character?

SELECT CustomerName
, CustomerID
FROM Sales.Customers
WHERE CustomerName LIKE 'Leyla%'
OR CustomerName LIKE 'Leila%'
OR ....
OR ...
OR ..
OR .
GO

Or are you going to go back to ranges and do a clean, efficient, single range?

SELECT CustomerName
, CustomerID
FROM Sales.Customers
WHERE CustomerName LIKE 'Le[a-ny]la%';
GO

Now I’d argue that that is a lot more readable than an endless list of OR clauses tacked on to the end of a script.

lejla
Oh wait, it’s Layla isn’t it? Not Leyla!

End: (‘%xyz’)

There is a lot more you can do with the LIKE function. Just because you may encounter this little guy every day does not mean that you know it.

Check out the documentation on MSDN. There’s information there like Pattern Matching with the ESCAPE Clause and different wildcard characters.

Don’t shy away from the fundamentals. Every little bit that you learn can and more than likely will be used to improve your skills and make you better.

Hopefully these little tidbits of knowledge will sink in…just like osmosis 🙂

Understanding “Scan Counts 0, Logical Reads N”

So yesterday, the 30th of November, was Arun Sirpal’s (b | t) birthday and as a birthday present, he challenged me to write a blog post.

Now I’ll admit that I didn’t have any plan to write a blog post before I got the challenge as I’ve been steadily getting busier with work/study/other commitments, so apologies if it’s a bit long, rambling, and not thought through fully.

Anyway, happy birthday Arun, here’s your blog post:

Understanding “Scan Counts 0, Logical Reads N”

Have you ever run SET STATISTICS IO ON; ?

I’ll confess that I do it a lot, especially when I am performance tuning. Yet, like most things in SQL Server, I don’t fully understand it…yet!

Now don’t get me wrong, the little that I understand is extremely helpful, but recently I had a case where I didn’t understand the output of STATISTICS IO , and asking my Senior DBA got me the look from him that I come to think of as ‘The You_Are_Either_Joking_Or_You_Are_Being_Stupid_Again Look’.

So to document my stupidity, here’s the question for that look.

How come the Logical Reads from STATISTICS IO are so high when it says Scan count is 0?

tl;dr – They are related but not exactly a 1:1 relationship. Plus a scan count of 0 does not mean that the object wasn’t used at all.

Logical Reads:

Let us just get this definition out of the way as it’s very short, sweet, and to the point. Logical Reads are the…

Number of pages read from the data cache.

Right, great, got’cha. Logical reads, 8kb pages, read from the data cache. If your STATISTICS IO reports logical reads 112 then you’ve read 112 pages from the cache. Simples!

Scan Count:

This is the blighter that got me the look…well more like ‘my misunderstanding of what Scan Count means’ got me the look but it still holds my contempt at this moment in time.

My previous intuitions about this guy was…

“Scan count is the number of hits the table/index received”
(THIS IS NOT CORRECT! …and why it isn’t down as a full quote).

Let’s check out the definition again and see what it has to say for itself.
Scan count is the…

Number of seeks/scans started after reaching the leaf level in any direction to retrieve all the values to construct the final dataset for the output.

That’s a very specific definition isn’t it? It’s not all of the definition though, there’s more! And it is this “more” that I want to focus on.

Testing The Defintions

First, things first, let us set up our query environment.

USE [tempdb];
GO

SET STATISTICS IO ON;
GO

Next paragraph…

Scan count is 0 if the index used is a unique index or clustered index on a primary key and you are seeking for only one value. For example WHERE Primary_Key_Column = <value>.

Well let’s see about that!


CREATE TABLE dbo.Unique_DefinedUnique
(
 col1 INT NOT NULL PRIMARY KEY
);
INSERT INTO dbo.Unique_DefinedUnique (col1)
SELECT x.n FROM (VALUES (1), (2), (3), (4), (5)) AS x(n);

CREATE UNIQUE NONCLUSTERED INDEX uci_Unique_DefinedUnique_col1
ON dbo.Unique_DefinedUnique ( col1 )
GO

SELECT col1 FROM dbo.Unique_DefinedUnique WHERE col1 = 1;

scancount0
okay…maybe it does…

Never mind, the next paragraph please!

Scant count is 1 when you are searching for one value using a non-unique clustered index which is defined on a non-primary key column. This is done to check for duplicate values for the key value that you are searching for. For example WHERE Clustered_Index_Key_Column = <value>.

I don’t believe you!


CREATE TABLE dbo.Unique_NotDefinedUnique
(
col2 INT NOT NULL
);
GO
INSERT INTO dbo.Unique_NotDefinedUnique (col2) VALUES (1), (2), (3), (4), (5);

CREATE NONCLUSTERED INDEX nci_Unique_NotDefinedUnique_col2
ON dbo.Unique_NotDefinedUnique ( col2 )
GO

SELECT col2 FROM dbo.Unique_NotDefinedUnique WHERE col2 = 1;

scancount1
Fine…you got me there too…

Final bit!

Scan count is N when N is the number of different seek/scan started towards the left or right side at the leaf level after locating a key value using the index key.

Hmmm, so if we have duplicate values, then this will happen?…
Nah, ridiculous!


CREATE TABLE dbo.NotUnique
(
col3 CHAR(1) NOT NULL
);
GO

INSERT INTO dbo.NotUnique (col3)
VALUES
('A'), ('A'), ('B'), ('B'), ('C'), ('C'), ('D'), ('D'), ('E'), ('E'),
('F'), ('F'), ('G'), ('G'), ('H'), ('H'), ('I'), ('I'), ('J'), ('J');
GO 2

CREATE NONCLUSTERED INDEX nci_NotUnique_col3
ON dbo.NotUnique ( col3 )
GO

--Let's try it with 2 and then 3!!!

SELECT col3 FROM dbo.NotUnique
WHERE col3 = 'A' OR col3 = 'B'

SELECT col3 FROM dbo.NotUnique
WHERE col3 = 'A' OR col3 = 'B' OR col3 = 'C'

scancountn
Alright, alright! I was an idiot!

Putting away my toys…

DROP TABLE dbo.Unique_DefinedUnique, dbo.Unique_NotDefinedUnique, dbo.NotUnique;

Round Up:

I was confused about the Scan count being 0 but logical reads not being 0. How can the scan count not actually scan/seek anything?

But it is scanning/seeking!

Read the Scan Count definition again…I’ll capitalize the words that I glossed over

Number of seeks/scans STARTED AFTER REACHING THE LEAF LEVEL in any direction to retrieve all the values to construct the final dataset for the output.

Scan count of 0 occurs when there is a unique index or clustered index on a primary key and you are seeking for only one value. The word of the day is…”Unique”.

So because the unique index looking for a unique value in a column guaranteed to be unique, it’s not so much that the query isn’t looking for a value, it’s more that  once the query reaches the leaf level it already knows that it’s on the value it needs!
Since it doesn’t look any more “after reaching the leaf level”, the scan count is allowed to be 0.

This explains why, if the value is unique but not guaranteed to be so (either the index, value, or column is not guaranteed unique) the query has to do 1 scan/seek to check that the next value isn’t what it wants.
Therefore, Scan Count will be 1…

And I’ll leave it as an exercise to figure out why Scan Count N is Scan Count N.
(Hint: it’s because Scan Count N)

Take Away:

I sometimes find the documentation dry and not terribly interesting but I don’t like the “You_Are_Either_Joking_Or_You_Are_Being_Stupid_Again” look more.
So read the documentation, study what you are doing, know why things do what they do…

It’ll save you some funny looks off of people. 🙂

sp_rename to change schema?

words: 519

Reading time: ~2.5 minutes

The Set Up:

Recently I was asked by a developer whether they could use sp_rename to change the schema of a table.

I said no but I realised that I don’t know for sure as I’ve never tried it this way.

Granted I have never needed to when we have such a descriptive command like ALTER SCHEMA.

So I tested to see if sp_rename could change the schema of a table and thought I would share my results.

Here they are:


SP_RENAME:

Script 1:


SELECT [Schema Name] = SCHEMA_NAME([schema_id]),
 [Table Name] = [name]
 FROM sys.tables
 WHERE [name] = N'Alphanumeric';

original_table

Now taking a look at the documentation for “sp_rename”, turns out all we need is

  1. the current name,
  2. the new name we want to call it, and
  3. an optional object type (which I’ll include because I like typing).

So with that, it seems simple to run the following…

Script 2:


 EXEC sp_rename
 @objname = N'dbo.Alphanumeric',
 @newname = N'deleteable.Alphanumeric',
 @objtype = 'OBJECT';

sp_rename
That error message! I enjoy that error message 🙂

So now all there is left to do is check if it worked, so we run our first script again and we get???:

original_table_changed
eh…what?

I repeat the above: eh…what???

Where did my table go???

Please tell me I didn’t delete the table? It’s a test system and I took a backup before starting but it’s a whole lot of hassle to recreate the table.

However, on a whim, I changed my first query to use a LIKE:

Script 3:

sp_rename_found
ehh..WHAT??

So I haven’t changed the schema? I’ve renamed it to be dbo.deletable.Alphanumeric?

Is that even query-able?


SELECT * FROM dbo.deleteable.Alphanumeric; -- Fails!

SELECT * FROM [dbo].[deleteable.Alphanumeric]; -- Works!

Okay, let’s just change it back quickly and pretend it never happened:

Script 4:


EXEC sp_rename
@objname = N'deletable.Alphanumeric',
@newname = N'Alphanumeric',
@objtype = 'OBJECT';

error_message_02
You’re killing me!!

Okay, okay maybe it’s like the SELECT statement and I need to wrap it in square brackets?

Script 5:


EXEC sp_rename
@objname = N'[deletable.Alphanumeric]',
@newname = N'[Alphanumeric]',
@objtype = 'OBJECT';

error_message_03
I count that as a metaphorical middle finger to me…

Maybe we’re being too specific?

Script 6:

EXEC sp_rename '[deleteable.Alphanumeric]', 'Alphanumeric';
finally_works
Hopes are up…

A quick run of our first script to confirm?

works_again
…and we’re back to normal!!!

Now, as to why that syntax works but the others don’t…I have no idea.

I will try and figure that out (fodder for another blog post 🙂 ) but I’m going to need a few more coffees before I go touch that again.


ALTER SCHEMA:

It’s a bit sad though… all that hassle for something that didn’t even work in the end?

Now, lets check out the documentation of “ALTER SCHEMA”.

  1. where we are changing it to, and
  2. what we’re changing.

Seems simple, but then so did sp_rename and that burnt me.

Script 7:

alter_schema
Give my “deleteable” the “dbo.Alphanumeric” object!

A quick check to see if it actually worked as I’m not swayed anymore just by a lack of warnings:

actually_works
Yes!

Sum it up:

If I didn’t know the answer at the start, I definitely do now.

Can you change the schema of an object by using “sp_rename”?

Hell no.

Save yourself the hassle and just stick to ALTER SCHEMA. It’s easier, believe me.

 

Create View Permissions

CREATE VIEW Permission

I’m mainly writing this as documentation for myself as, in the end, this is the original purpose of this blog, to document SQL Server and new aspects of it that I learn and try.

Personal Template

I’ve always had a little block with regard to this as, for database permissions, I always followed a template in my head:

USE <database>
<Give/Take away> <what permission> <On What> <To Whom>

It’s The Little Things That Trip You

With CREATE permissions this isn’t the case; there is a piece of the above template that isn’t needed, and it’s quite easy to see why when I sat down and thought about it.

Specifically, it’s this bit:

<On What>

I’m granting CREATE permissions; since I haven’t created anything, I can’t grant the permission on anything.
So for CREATE permission, I have to modify my template a bit:

USE <database>
<Give/Take away> <what permission> <To Whom>

If I use this now as a template to a GRANT CREATE VIEW, it will work:

USE [localTesting];
GRANT CREATE VIEW TO [testUser];

And it works!

[SQL Server] How Well Do You know Your sys Tables? Test yourself

I may have been too reliant on Intellisense…

Intro:

Reading Time: ~1 minutes

Recently I had to check on the nature of my check constraints and foreign keys; whether they were trusted or not.

select name, is_not_trusted from sys.check_constraints;
select name, is_not_trusted from sys.foreign_keys;

In case you are wondering, this has some Query Optimiser (QO) benefits so it’s a definitely a bonus to ensure that they are trusted.

However, something that should have taken 5 seconds and be a no-brainer, took me 30 seconds and required a bit of memory power on my part.

All because I had a problem with my SQL Server Intellisense, and said SQL Server Intellisense stopped working.
This forced me to drudge up these names out of my memory from whatever blog post or BOL entry I read them in.

This, in turn, got me wondering; how well do you really know your sys tables?

If your intellisense broke tomorrow, would you know your Dynamic Management Objects (DMO)?

Now before you dismiss this notion as simple, remember that it is not just sys tables that you have to know. This will test your knowledge on your application tables, your columns, stored procedures and functions.

Do you know all their names? Which table has the column “ID”, which has “<table_name>ID” and which has “<table_name>_ID”?

Test yourself:

The fix for my problem with SQL Server Intellisense not working is going to be reversed to allow you to test your knowledge.

In the dialog box, Tools -> Options -> Text Editor -> General, there are two checkboxes under the “Statement completion” section:

  • Auto list members
  • Parameter information

If you uncheck these two checkboxes, your intellisense is gone!

TransactSQLGeneral

Try it out, even for 5-10 minutes.

Hopefully, with your Intellisense gone, your sense will remain.