September 26, 2013 at 5:18 am
Nice question. I can't tell you how many times I got hit by that before it was driven into my head! 🙂
September 26, 2013 at 5:28 am
The fact that all of the options were error conditions made it a bit easier. If one of the options had been "Query will return a list of objects", I would have got it wrong. Didn't realise that this needed Unicode.
September 26, 2013 at 6:00 am
Toreador (9/26/2013)
I knew it was going to fail due to the data type, but any of the options was feasible so it came down to a guess as to which one the MS developers decided to return. Luckily I guessed right 🙂
I didn't, and I'm surprised more people didn't pick the fourth option simply by chance, as these seem arbitrarily similar to me:
[font="Courier New"]Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
Parameter or variable '@str' has an invalid data type.[/font]
Like you said, unless you run it or have hit it before, it seems like a coin toss to me. I'm just curious how such a significant percentage of people knew it was the second option rather the fourth.
ron
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
September 26, 2013 at 6:44 am
ronmoses (9/26/2013)
Toreador (9/26/2013)
I knew it was going to fail due to the data type, but any of the options was feasible so it came down to a guess as to which one the MS developers decided to return. Luckily I guessed right 🙂I didn't, and I'm surprised more people didn't pick the fourth option simply by chance, as these seem arbitrarily similar to me:
[font="Courier New"]Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
Parameter or variable '@str' has an invalid data type.[/font]
Like you said, unless you run it or have hit it before, it seems like a coin toss to me. I'm just curious how such a significant percentage of people knew it was the second option rather the fourth.
ron
Good observation. Even I thought a lot responses would be in favor of option 3 or 4. It's a good thing that a greater percentage of SQL crowd is aware of what to expect and avoid such scenarios.
September 26, 2013 at 6:56 am
Good question, thanks!
September 26, 2013 at 7:05 am
crussell-931424 (9/26/2013)
I'm not familiar with this. My management studio tells me it is an extended stored procedure. What's that? Where do I go or how do I see the sql code that makes up this procedure? I can find it under the master db but don't find a way to actually view the actual sql code like I can with a normal stored procedure. When I right-click on it there is a selection for PowerShell but that didn't give me anything but a dos prompt.
Extended stored procedures are stored procedures that call functions from DLL files. That is why you cannot see the internal code for this. However, extended stored procedures are deprecated and you should avoid using them if possible. CLR Integration should be used instead.
I am saying this but it makes me wonder, why and how sp_executesql still working? :w00t:
September 26, 2013 at 7:30 am
ksatpute123 (9/26/2013)
Stuart Davies
Nice and easy today - thanksKnew the answer to this through bitter experience Smile
+1.
+1 Thanks for the question!
September 26, 2013 at 3:12 pm
EZ PZ - thanks.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 27, 2013 at 8:18 am
Nice question. I guess Ron is right though as only people who have actually seen the error message and remember enough about what it is can be expected to know the right answer, unless it's turned up in a question before in hich case having seen that question (and remembered enough) would also provide the right answer.
In the explanation it's not correct to say that 4000 is the size limit for unicode types, because they can take MAX (obviously you know that); better to say that can't you specify the size for the type as a number bigger than 4000.
Tom
September 27, 2013 at 8:48 am
ksatpute123 (9/26/2013)
Extended stored procedures are stored procedures that call functions from DLL files. That is why you cannot see the internal code for this. However, extended stored procedures are deprecated and you should avoid using them if possible. CLR Integration should be used instead.I am saying this but it makes me wonder, why and how sp_executesql still working? :w00t:
You are misunderstanding it. Creating and modifying extended stored procs is deprecated, as is a whole load of XP_API stuff and three of the existing MS supplied extended stored procs (xp_grantlogin, xp_revokelogin, and xp_loginConfig), but all of these will still be supported in the release following SQL Server 2012. Other Microsoft supplied ESPs are not deprecated (yet) so will presumably be supported in at least the two releases after SQLS 2012; that includes sp_executesql. Remember that stuff that is deprecated is expected to work until it has been made not supported, and MS normally gives notice that something is deprecated at least two releases before it will be made not supported. The current lists of deprecated items that will not be supported in the next release and features which will be in that release but are going to be removed some time llater is given on this BOL page
Tom
September 27, 2013 at 8:52 am
L' Eomot Inversé (9/27/2013)
ksatpute123 (9/26/2013)
Extended stored procedures are stored procedures that call functions from DLL files. That is why you cannot see the internal code for this. However, extended stored procedures are deprecated and you should avoid using them if possible. CLR Integration should be used instead.I am saying this but it makes me wonder, why and how sp_executesql still working? :w00t:
You are misunderstanding it. Creating and modifying extended stored procs is deprecated, as is a whole load of XP_API stuff and three of the existing MS supplied extended stored procs (xp_grantlogin, xp_revokelogin, and xp_loginConfig), but all of these will still be supported in the release following SQL Server 2012. Other Microsoft supplied ESPs are not deprecated (yet) so will presumably be supported in at least the two releases after SQLS 2012; that includes sp_executesql. Remember that stuff that is deprecated is expected to work until it has been made not supported, and MS normally gives notice that something is deprecated at least two releases before it will be made not supported. The current lists of deprecated items that will not be supported in the next release and features which will be in that release but are going to be removed some time llater is given on this BOL page
There should be a like button on sql server threads! +200.
September 28, 2013 at 6:27 am
nice question and explanation..
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply