Words: 423
Time to read: ~ 2 minutes
Continue reading “When Stored Procedures say they Alter Schemas of the Target Table”
When Stored Procedures say they Alter Schemas of the Target Table
…even when they shouldn’t!
Tag: errors
What’s in your $Profile?
Don’t be afraid.
Show me what you got!
Words: 332
Time to read: ~ 1.5 minutes
Transact-SQL to Powershell: Substring
In my ongoing attempt to learn Powershell to help automate my workloads, I’ve come across the need to use the Transact-SQL SUBSTRING() function but, in using it, I got the following error:
Now if you are like me, that is very hard to read but the error is saying
StartIndex cannot be larger than length of string
Compare-Object ‘SQLServer’ ‘PowerShell’
The main difference that I can see when using SUBSTRING() in SQL Server versus in PowerShell is that SQL Server is very forgiving.
If you have a string that is 20 characters longs and you ask for everything from the 5th character to the 100th character, SQL Server is going to look at this, see that the string does not go to the 100th character, and just give you everything that it can.
PowerShell on the other hand, while being amazingly forgiving with some things….
Examples:
-
"a" + 2= a2 "a" * 2= aa2 + 2= 4"2" + 2= 22
…is surprisingly less forgiving than SQL Server here.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #… get some results to work with… | |
| $sqlcmdParams = @{ | |
| ServerInstance = 'localhost\SQLSERVER2K16' | |
| Database = 'master' | |
| Query = @' | |
| SELECT TOP(10) | |
| name | |
| FROM dbo.spt_values | |
| WHERE name IS NOT NULL | |
| AND LEN(name) != 0 | |
| ORDER BY name; | |
| '@ | |
| }; | |
| $dbResults = Invoke-Sqlcmd @sqlcmdParams; | |
| #…now check the substring function… | |
| foreach ($row in ($dbResults.name)) { | |
| [PSCustomObject]@{ | |
| RowName = $row | |
| RowSubString = $row.Substring(5, 100) | |
| }; | |
| }; | |
| <# | |
| # Error message: | |
| # Exception calling "Substring" with "2" argument(s): "startIndex cannot be larger than length of string" | |
| #> |
If we checked the length of the results we can see the length of each individual row:
foreach ($row in ($dbResults.name)) {
[PSCustomObject]@{
RowName = $row
RowLength = $row.Length
}
}
So PowerShell goes to find the 5th to the 100th character, sees that the 100th character is outside the length of the string, and freaks out!
The PowerShell Hammer…
…can also be a PowerShell Scalpel as well. You can get as precise as you need to and in this case, with the error complaining about the length, we should probably be more specific about the length we want.
So let’s get more specific about the length! Now we could go and input all the different values for substring function but let’s get a bit more dynamic about it.
It is PowerShell after all…
#...now check the substring function...
#...with proper values...
foreach ($row in ($dbResults.name)) {
[PSCustomObject]@{
RowName = $row
RowSubString = $row.Substring(5, ($row.Length) - 5)
}
}
So there we go, SQL Server substring and PowerShell substring are basically the same. We just have to be concise about it!
Update: 2017-08-15
Thanks to Michael Villegas ( blog | twitter ) for pointing out in the comments that PowerShell has a simpler syntax to deal with this.
While SQL Server requires 3 arguments for the substring function (expression, start, length); PowerShell has the same thing but it also has a simpler syntax for getting the characters from a starting point all the way to the end.
#...simpler syntax...
foreach ($row in ($dbResults.name)) {
[PSCustomObject]@{
RowName = $row
RowSubString = $row.Substring(5)
};
};
The more you know… 🙂
ARIGHTABORT-ing & Anti-ANSI_WARNINGS
I recently ran into a problem with the QUOTED_IDENTIFIERS option in SQL Server, and it got me to thinking about these SET options.
I mean the fact that, on tables where there are filtered indexes or computed columns with indexes, QUOTED_IDENTIFIER is required to be on to create any other indexes is just not intuitive. But if you can’t create indexes because of it then I’d argue that it’s pretty damn important! I also found out that this problem is not just limited to QUOTED_IDENTIFIER but to ARITHABORT and ANSI_WARNINGS as well.
Just check out the Microsoft Docs and what it has to say about it:
SET ARITHABORT must be ON when you are creating or changing indexes on computed columns or indexed views. If SET ARITHABORT is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail.
And for ANSI_WARNINGS it says:
SET ANSI_WARNINGS must be ON when you are creating or manipulating indexes on computed columns or indexed views. If SET ANSI_WARNINGS is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail.
It’s not just Indexes
So, like a dog when it sees a squirrel, when I found out about the problems with ARITHABORT and ANSI_WARNINGS I got distracted and started checking out what else I could break with it. Reading through the docs, because I found that it does help even if I have to force myself to do it sometimes, I found a little gem that I wanted to try and replicate. So here’s a reason why you should care about setting ARITHABORT and ANSI_WARNINGS on.
Default to on
At one stage or another if you’re working with SQL Server, you’ve probably encountered the dreaded “Divide By 0” error:
Msg 8134, Level 16, State 1, Line 4 Divide by zero error encountered.
If you want to check this out, then here’s the code below for our table:
USE Pantheon;
-- Create our test table...
CREATE TABLE dbo.ArithAborting (
id tinyint NULL
);
GO
And our attempt at inserting that value into the table:
SET ARITHABORT ON; GO SET ANSI_WARNINGS ON; GO -- Check can we insert a "divide by 0"... BEGIN TRY INSERT INTO dbo.ArithAborting (id) SELECT 1/0; END TRY BEGIN CATCH PRINT 'NOPE!'; THROW; END CATCH;
And we get our good, old, dreaded friend:
We check our ArithAborting table and nothing is there, like we expected!
SELECT * FROM dbo.ArithAborting;
What about if we were to turn our ARITHABORT and ANSI_WARNINGS off though, what happens then? Well that’s a simple thing to test, we just turn them off and run the script again:
--Turn ARITHABORT off; SET ARITHABORT OFF; GO SET ANSI_WARNINGS OFF; GO -- ...insert into our table... BEGIN TRY INSERT INTO dbo.ArithAborting (id) SELECT 1/0; END TRY BEGIN CATCH PRINT 'NOPE!'; THROW; END CATCH;
Now before I freak out and start thinking that I’ve finally divided by zero, let’s check the table:
What’s going on here? Checking the docs…
During expression evaluation when SET ARITHABORT is OFF, if an INSERT, DELETE or UPDATE statement encounters an arithmetic error, overflow, divide-by-zero, or a domain error, SQL Server inserts or updates a NULL value. If the target column is not nullable, the insert or update action fails and the user receives an error.
Do I like this?
Nope!
If I have a terminating error in my script, I quite like the fact that SQL Server is looking out for me and won’t let me put in bad data, but if you have these options turned off, even if you wrap your code in an TRY...CATCH block, it’s going to bypass it.
Plus if you are trying to divide by 0, please stop trying to break the universe. Thank you.
My Function Won’t Accept Parameters? Get-Help!
Getting Get-Help Help
The following is a recounting of an issue that I had and how I went about resolving it. No computers were harmed in the making of this post.
Ask me for one PowerShell command that everyone should know and I can answer you: Get-Help.
Fairly descriptive name if you ask me. Today I’m focusing on using Get-Help selectively to help me figure out why my custom function just won’t accept parameters!
You say Test Case. I say Basket Case.
We are going to need a custom test function for the audience to play along with at home, luckily Shane’s got you covered.
This is a Tactical Estimation of Shane’s Test function – aka T.E.S.T. function; very simple but all the important parts are there.
Function Test-FunctionByParameter {
[cmdletbinding()]
Param(
[Parameter(Mandatory = $true,
ValueFromPipelineByPropertyName = $true)]
[string]$Parameter
)
process {
"Success, I'm [$Parameter]"
}
}
If I’ve done my maths right, and I always do my maths right (as far as you know), then this function should take input from the pipeline and output it in the string “Success, I’m …”
Do I do my maths right?
Get-Service -Name *sql* | Select Name -first 1 | Test-FunctionByParameter
Huh, parameter problem.
I thought this was supposed to work like this. You pipe in information, magic happens, and functions work, no?
Well, when in doubt, Get-Help.
Get-Help:
Before I go any further though, just so that everyone knows how to use Get-Help, I’m going to show you one of the secret techniques for using Get-Help.
Get-Help *help*
Yup, I use dbatools
Why does help exist?
When you think about it, why is there even a function called help?
As far as I’m aware it’s basically the same as Get-Help except it automatically pipes the output to | more so we get pages rather than a wall of text.
Is there more that we can do with Get-Help though? Is there a way that we can return the examples only? Syntax only? Parameters only?
Is there not a way that we can do such things?!
Yessum, the Possums cousin
Okay I cheated on the first one; examples are pretty easy. PowerShell actually already takes care of that for you.
Get-Help Get-Help -examples
The other two, while not laid out for you as pretty as that, are not that difficult to do. What needs to be remembered about Get-Help is that it is a cmdlet. And what do cmdlets normally output?…
What?! No! Objects!
They normally output Objects! Wow…next time just pipe it to Get-Member if you don’t know.
I Object!
Now I first saw this done in a blog post by Adam Bertram ( blog | twitter ) but I do believe that it warrants further highlighting.
If you did pipe Get-Help to | Get-Member you would have seen a NoteProperty called syntax, so if we want the syntax for a cmdlet, we can specify that using:
(Get-Help Get-Help).syntax
So for parameters we need…yup .parameters.
(Get-Help Get-Help).parameters
Hmm, not as handy as I thought it would be. What happens if we pipe that to Get-Member (Alias gm as I’m getting lazy here)?
(Get-Help Get-Help).parameters | gm
Let’s try that and see what we get, shall we?
(Get-Help Get-Help).parameters.parameter
It’s always brightest before the dawn
And the answer comes always before you smash your screen in rage.
If we pipe the above information to Get-Member again, we get more useful information this time (I’m not going to show it, you know how to pipe to gm by now).
I’m from a database background so can we make this pretty, all I care about is the name and the pipeline input.
(Get-Help Get-Help).parameters.parameter |
Select-Object -Property name,pipelineinput
By Odin’s Beard! I mean PropertyName
You know one of these days I should really read this help file (you should too) because half way down the results of the following code is some interesting info…
help about_pipelines
METHODS OF ACCEPTING PIPELINE INPUT
Cmdlets parameters can accept pipeline input in one of two different ways:
— ByValue: Parameters that accept input “by value” can accept piped objects
that have the same .NET type as their parameter value or objects that can be
converted to that type.For example, the Name parameter of Start-Service accepts pipeline input
by value. It can accept string objects or objects that can be converted to
strings.— ByPropertyName: Parameters that accept input “by property name” can accept piped
objects only when a property of the object has the same name as the parameter.For example, the Name parameter of Start-Service can accept objects that have
a Name property.(To list the properties of an object, pipe it to Get-Member.)
Some parameters can accept objects by value or by property name. These parameters are
designed to take input from the pipeline easily.
So that’s the problem?! The names need to match up! I can do that with Select-Object!
All I need to do is add a custom label using @{Label='<custom label>';Expression={'<custom expression>'}}
Try{}Catch{}Finally{}
Get-Service -Name *sql* |
Select-Object -First 1 -Property @{l='Parameter';e={$_.Name}} |
Test-FunctionByParameter
So now when I run a command and get the crazy…
The input object cannot be bound to any parameters for the command either because the command does not take pipeline input or the input and its properties do not match any of the parameters that take pipeline input.
I can just run:
(Get-Help <cmdlet name>).parameters.parameter | Select-Object Name,pipelineInput
And know exactly where to fix! 🙂
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.
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]
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.
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.
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
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.
![[PowerShell] Getting More From Generic Error Messages.](https://i0.wp.com/nocolumnname.blog/wp-content/uploads/2017/03/initialerrormessage.png?fit=872%2C109&ssl=1)
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.
(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
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')
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?;'
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:
Now let us run this against our database and see what happens.
# Execute with results...kinda like it says... $Database.ExecuteWithResults($sql).Tables
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…
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 *
Bingo, that is a lot more helpful! Especially when we scan the results and we see this guy (highlighted)
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!
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”…