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

 

SQL Server Configuration Manager: Where has it gone?

Why this blog post?

Now this is a short one.

A while back I was testing attempting to access SQL Server using a defined port number.

To do that, you have to access SQL Server Configuration Manager to specify the port number, or at least I think you do (If you don’t have to do it this way, please, oh please, let me know!).

So, since my laptop is running on Windows10, I open up the Start menu, type in “config” and…nothing!
No SQL Server Configuration Manager! The closest I got was the configuration manager for Reporting Server.

I’m shocked by this especially because when I type it into the Start menu now, I get…

sqlserverconfigurationmanager
Windows 10 making a liar out of me…

Ignoring the fact that it shows up in the result pane now, I had to go to MSDN and figure out where it’s default path is.

But WHY this blog post?

Well it turns out that, for me, the default path is now in…

C:\Windows\sysWOW64\SQLServerManager”<nn>”.msc

I say “<nn>” because it’s a number dependant on what version of SQL Server that you are running.
For example, I have a 2012, a 2014 and a 2016 version on my laptop so I have 3 versions of SQL Server Configuration Manager.

sqlserverconfigurationmanager_we
I may have been msc-taken, get it? 🙂

Seriously, is that it?

Nope, that ain’t it.

Opening up Windows Explorer, going all the way down to that level just to get the config manager? Ain’t nobody got time for that!

They say “imitation is the greatest form of flattery” so taking ideas garnered from dbatools and the fact that I’m just finished a pre-con for SQL Saturday Boston in PowerShell from Mike Fal ( b | t ), thank you Andy Mallon ( b | t ) for pushing me out of my comfort zone even if it was only to just sign up!, I’ve created a little PowerShell script to let me choose and open a SQL Server Configuration Manager.

Check it out! And let me know what you think.

Script me!

Get-ChildItem -Path C:\Windows\sysWOW64\ -Filter "*SQLServerManager*" |
Out-GridView -PassThru |
Invoke-Item

 

scriptme
Seriously “OutGridView -PassThru” should get an award!

Now, I only have 2014 service up on this time so when Out-GridView pops up, I’m going to choose SQLServerManager12.msc and click “Ok”

out-gridview
This doesn’t count as a GUI!

Which will open up our “missing” configuration manager!

SQLServerConfigurationManagerEnd.PNG
I may have just been doing something stupid though…

Exit:

PowerShell! Helping make my life easier since…whenever I actually figure it out. 😐

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 🙂

Turning off Passive Voice spell-check in Word 2016

First, non SQL Server/PowerShell post…not sure I like this…

tl;dr : File | Options | Proofing | Settings | Passive Sentences

Words: 470

Reading Time: ~2.5 minutes

Warning: This is barely SQL Server/PowerShell related. It is Microsoft product related if that helps?

In the beginning…

One thing that I am starting to appreciate more and more as time goes by is Documentation.

If you’ve ever worked on a system with no documentation in place then you know of the frustration, heartbreak and rage that this instills when you’re trying to get to know the system, or even trying to troubleshoot something on that sytem.

But even the act of writing documentation can have some benefits, I’d argue that you would be hard-pressed to find something that forces you to learn about a topic more than trying to write clear and concise documentation for someone else.

What’s your problem?…

In a strange side-effect of trying to become more responsible as a DBA, I’ve actually inherited a slight case of obsessiveness about certain things.

Words need to be spelled correctly, uppercase words need to be in uppercase, and with regard to this post…

I DON’T WANT A SPELL-CHECK ERROR FOR USING A PASSIVE VOICE!!!

It’s documentation. Now I can understand that, depending on your work environment,  you can write in a more upbeat and active way. But for documentation, I don’t see anything wrong with this sentence:

Provision one (1) domain account, with no privileges, per service that will be run.

passive-voice-check
More like Passive Aggressive…

I’m fully expecting the paper clip guy to appear any second and start spouting “Looks like you’re writing in the Passive Voice…”

Calm yourself Word…

When this blue squiggly line (it is blue, right? I’m slightly colourblind) started to pop up everywhere on my document, and after checking things three times, I figured enough is enough and went about turning this off.

Here’s how I did it so that you can to!

Now I’m lucky enough that work have MS Word 2016, so if you have an older version YMMV. If you don’t have any version of Word, then your mileage will vary!

  • Go to “File | Options | Proofing” and scroll down to the section marked “When correcting spelling and grammar in Word”.
msword-proofing
I’m probably not going to touch all these…
  • Click the “Settings…” button beside the ” Writing Style:” and “Grammer and Style” dropdown box. This should open up the following window.
msword-settings
Passive Sentences!!! Grr!!!
  • Uncheck the “Passive sentences” checkbox and click “OK”. Click “OK” on the “Proofing” window as well and you should get back to your main Word screen.

And here is where the magic happens. Our blue (purple?) squiggly lines have disappeared!

passive-voice-check-noerror

Wrap it up…

You could just click ignore when spell-checking but I tried that. It ignores it once and then the moment that it spell-checks again, it picks up these “errors” again. If there is one thing worse then errors, it’s repeating errors.

Plus isn’t that part of we, as DBAs strive for?

We’re not content with just the quick fix, but want to identify and correct the underlying problem.

Makes it easier in the long run…

T-SQL Tuesday #86: SQL Server Bugs & Enhancement Requests

The Good, the Bad, and the Bug-ly.

The Good, the Bad, and the Bug-ly.

This month’s T-SQL Tuesday is brought to us by Brent Ozar ( work | personal | tweets ) and has to do with SQL Server Bugs and Enhancement requests on Microsoft Connect.

Now the reason that this is the topic of this month’s T-SQL Tuesday is nicely spelled out by the host himself:

Now, more than ever, Microsoft has started to respond to Connect requests and get ’em fixed not just in upcoming versions of SQL Server, but even in cumulative updates for existing versions.

If you’ve kept an ear to the ground and an eye out on the social media, (seriously get on Twitter if you haven’t already; with #sqlhelp and #sqlfamily, you’ll never be alone, stuck, and/or bored again), you’d realise that this is true. Microsoft has engaged more than ever with it’s audiences, from hiring a PowerShell developer to actively engaging in various Trello Channels. So much so that a twitterbot was created to keep track of Connect items closed as fixed (Closed As Fixed) by MVP Chrissy LeMaire ( blog | tweets ).

Shameless Plug:

Now, I happen to have a Connect item and I’m going to link to it here. I can do this as this blog shares a commonality with Lesley Gore’s birthday party song. (It’s mine and I can cry if I want to)

The Good…

First of all, lets see what we’re running on here…

SELECT @@VERSION

 

 

version
I really should be doing these against SQL Server 2016… 😦

First contender: Inserting to an indexed view can fail

What would happen if I told you that, with regards to a view, sometimes inserting into the table could fail? Well that’s what this Connect item from Dave_Ballantyne found, along with the reason.

CREATE TABLE myTable
(
    Id integer not null,
    InView char(1) not null,
    SomeData varchar(255) not null
)
GO

CREATE VIEW vwIxView
WITH SCHEMABINDING
AS
SELECT 
    ID, 
    Somedata, 
    LEFT(SomeData, CHARINDEX('x', SomeData) - 1) AS leftfromx
FROM 
    dbo.myTable
WHERE 
    InView ='Y'
GO

CREATE UNIQUE CLUSTERED INDEX pkvwIxView ON vwIxView(Id)
GO

 

 

Now that we have the groundwork laid out, it’s time for our insert attempt:

DECLARE @id integer,
@inview char(1),
@Somedata char(50)

SELECT @id = 1,
@inview = 'N',
@Somedata = 'a'

INSERT INTO myTable(Id, InView, SomeData)
SELECT @id, @inview, @Somedata

 

daveballantyneerror
Do you see a LEFT() or SUBSTRING() ANYWHERE there???

He’s even gone so far as to give an explanation as to why this happens!

This is due to the compute scalar being executed prior to the filter.

Taking a look at the estimated execution plan, we can see that he’s right!

daveballantyneerrorplan
Let’s do it first, then see if we should have!

Imagine trying to troubleshoot what was causing the inserts to fail? Horrible! I can imagine tearing my hair out at that one!

I have this done as “The Good” just for the fact that, not content to just report the bug, Dave_Ballantyne even went so far as to find the possible cause. Now it’s just up to Microsoft to get to the fixing…

…The Bad…

Next up, we have moody31415 with “Can’t create Materialized View that references same table twice

Groundwork:

CREATE TABLE test (A int)
GO
CREATE VIEW vtest WITH SCHEMABINDING
AS
SELECT t1.A, t2.A as A2
FROM dbo.test T1
JOIN dbo.test T2 ON T1.A=T2.A
GO

The problem occurs when we try to add a unique clustered index on this bad boy:

CREATE UNIQUE CLUSTERED INDEX UCL_Test
on dbo.vTest (A, A2)

 

maddog_selfjoinviewserror
Ever get those times where you just can’t stand yourself?

Now, I originally put this down as “The Bad” because I thought that the issue could be down to trying essentially index the same column twice but that’s not the case…

CREATE TABLE dbo.B (col1 tinyint, col2 tinyint)
GO
CREATE VIEW dbo.SecondOne
WITH SCHEMABINDING
AS
SELECT t1.col1, t2.col2
FROM dbo.B AS t1
JOIN dbo.B AS t2 ON t1.col1 = t2.col1
GO
CREATE UNIQUE CLUSTERED INDEX UCL_test
ON dbo.SecondOne (col1, col2)

 

maddog_selfjoinviewserror_02
…I mean After-A-Massive-Meal-Cant-Stand-Yourself

In the end, the reason that I have this in “The Bad” section is that I went to the documentation and read this part…

The SELECT statement in the view definition must not contain the following Transact-SQL elements: […] self-joins […]

Now it’s unknown whether this was there when this Connect item was created, but it’s there now (and I didn’t have enough time to re-plan this blog post)

…and The Bug-ly.

In my quest through the magical lands of Connect I stumbled across this little beauty of a bug by Anatoly V. Popov that I had to mention as “The Bug-ly”.

Altering indexed view silently removes all indexes

Yes, I know it’s the same as my one but dammit if I don’t think it’s a bug! That’s why it’s getting my “The Bug-ly” title.
This leaves me with a bit of a conundrum though…Do I close mine and comment on this one to get re-opened? Or do I try and continue to push on my one?

To be honest I don’t know which option to choose but, for anyone new to SQL Server, filing a Connect Item is an exciting but nerve-wracking time.
Exciting because you’ve worked with SQL Server enough to think you know how something should be done.
However, it’s nerve-wracking because you haven’t worked with SQL Server for long enough to know if you are just whining or have an actual item to be taken seriously.

Finding this Connect item was a small little shot of validation for me. Just a little something to say “Hey, I thought this too and I’m with you”.

Summary

It’s a great thing that Microsoft have started to respond to the Connect Items again and going through them you realise how different people are using SQL Server in ways that you didn’t even think to imagine.

So check out a few, leave your comment and up-vote/down-vote as you see fit.

Just…just don’t be this guy please

dt_ofdbas