February 11, 2016 at 8:57 am
This is driving me nuts. I know I'm missing something really simple, but I can't figure out what it is. So I'd appreciate a quick peer review.
We're in the middle of upgrading our servers. I'm identifying all objects that will be affected by a server split (I.E., they'll need a linked server added) and am loading that information into a table so I can do this one at a time. EDIT: I'm trying to write sp_helptext sql commands into a column of my table. That way I can just cut-n-paste the silly thing from my table into a query window and run the code.
I've messed with these stupid single quotes so many times Here's what I've got:
IF (SELECT Table_Name FROM Information_schema.Tables WHERE Table_Name = 'SQL2012_useBI_UpdateObjects' ) IS NULL
CREATE TABLE dbo.SQL2012_useBI_UpdateObjects (DBName VARCHAR(50), Reference VARCHAR(20),
ObjectName VARCHAR(200), ObjectType VARCHAR(2), SchemaName VARCHAR(50), HelpTextCmd VARCHAR(300));
EXEC sp_MSforeachDB @Command1 = 'USE [?];
INSERT INTO dbo.SQL2012_useBI_UpdateObjects
(DBName, Reference, ObjectName, ObjectType, SchemaName, HelpTextCmd)
SELECT DB_Name(), ''BI_'', CONVERT(VARCHAR(200),so.Name),
so.type, CONVERT(VARCHAR(50),ss.name),
'' USE '' + CONVERT(VARCHAR(50),DB_Name()) + '' EXEC sp_helptext '' + ss.name + ''.'' + so.name + '';''
FROM sys.objects so
INNER JOIN sys.schemas ss
ON so.schema_id = ss.schema_id
INNER JOIN sysusers su
ON so.schema_id = su.UID
INNER JOIN sys.sql_modules sm
ON so.Object_ID = sm.Object_ID
WHERE sm.definition LIKE ''%BI[_]%'''
This works, except it won't put single quotes around the schema.table name. I get "USE DBName EXEC sp_helptext MySchema.MyTable;" when I'm trying to get "USE DBName EXEC sp_helptext 'MySchema.MyTable';" as the result.
But every time I mess with the code, it errors out.
I tried:
EXEC sp_MSforeachDB @Command1 = 'USE [?];
SELECT DB_Name(), ''BI_'', CONVERT(VARCHAR(200),so.Name), so.type, CONVERT(VARCHAR(50),ss.name),
'' USE '' + CONVERT(VARCHAR(50),DB_Name()) + '' EXEC sp_helptext '''' + ss.name + ''.'' + so.name + '''';''
FROM sys.objects so
INNER JOIN sys.schemas ss
ON so.schema_id = ss.schema_id
INNER JOIN sysusers su
ON so.schema_id = su.UID
INNER JOIN sys.sql_modules sm
ON so.Object_ID = sm.Object_ID
WHERE sm.definition LIKE ''%BI[_]%''
ORDER BY so.type, so.Name'
And get "Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ' + so.name + ';'."
And then, when I manage not to get an error, I end up with it literally adding "ss.name" and "so.name" to the string (like "EXEC sp_helptext + ss.name...").
Can someone help me figure out what I'm missing here?
February 11, 2016 at 9:12 am
Check out the QuoteName function in BOL.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 11, 2016 at 9:14 am
Short suggestion, CHAR(39)
😎
February 11, 2016 at 9:24 am
So, the trick is that in the innermost quoted command, the EXEC sp_helptext, you want single quotes around schema.object. Since it's inside a string, each of those single quotes will need to be escaped, so you'll need two consecutive single quotes for each of the single quotes you want.
However, that is also inside a string, so each of those single quotes will need to be escaped. That means you'll need four consecutive single quotes for each single quote in the innermost string.
Something like this:
IF (SELECT Table_Name FROM Information_schema.Tables WHERE Table_Name = 'SQL2012_useBI_UpdateObjects' ) IS NULL
CREATE TABLE dbo.SQL2012_useBI_UpdateObjects (DBName VARCHAR(50), Reference VARCHAR(20),
ObjectName VARCHAR(200), ObjectType VARCHAR(2), SchemaName VARCHAR(50), HelpTextCmd VARCHAR(300));
EXEC sp_MSforeachDB @Command1 = 'USE [?];
INSERT INTO CreditDBA_Admin.dbo.SQL2012_useBI_UpdateObjects
(DBName, Reference, ObjectName, ObjectType, SchemaName, HelpTextCmd)
SELECT DB_Name(), ''BI_'', CONVERT(VARCHAR(200),so.Name),
so.type, CONVERT(VARCHAR(50),ss.name),
'' USE '' + CONVERT(VARCHAR(50),DB_Name()) + '' EXEC sp_helptext '''''' + ss.name + ''.'' + so.name + '''''';''
FROM sys.objects so
INNER JOIN sys.schemas ss
ON so.schema_id = ss.schema_id
INNER JOIN sysusers su
ON so.schema_id = su.UID
INNER JOIN sys.sql_modules sm
ON so.Object_ID = sm.Object_ID
WHERE sm.definition LIKE ''%BI[_]%'''
Cheers!
February 11, 2016 at 9:32 am
The easy way to do this is to create the results that you are looking for and then globally replace all of the single quotes with two single quotes before adding the single quotes around the whole expression.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 11, 2016 at 10:35 am
Jacob Wilkins (2/11/2016)
So, the trick is that in the innermost quoted command, the EXEC sp_helptext, you want single quotes around schema.object. Since it's inside a string, each of those single quotes will need to be escaped, so you'll need two consecutive single quotes for each of the single quotes you want.However, that is also inside a string, so each of those single quotes will need to be escaped. That means you'll need four consecutive single quotes for each single quote in the innermost string.
Something like this:
IF (SELECT Table_Name FROM Information_schema.Tables WHERE Table_Name = 'SQL2012_useBI_UpdateObjects' ) IS NULLCREATE TABLE dbo.SQL2012_useBI_UpdateObjects (DBName VARCHAR(50), Reference VARCHAR(20),
ObjectName VARCHAR(200), ObjectType VARCHAR(2), SchemaName VARCHAR(50), HelpTextCmd VARCHAR(300));
EXEC sp_MSforeachDB @Command1 = 'USE [?];
INSERT INTO dbo.SQL2012_useBI_UpdateObjects
(DBName, Reference, ObjectName, ObjectType, SchemaName, HelpTextCmd)
SELECT DB_Name(), ''BI_'', CONVERT(VARCHAR(200),so.Name),
so.type, CONVERT(VARCHAR(50),ss.name),
'' USE '' + CONVERT(VARCHAR(50),DB_Name()) + '' EXEC sp_helptext '''''' + ss.name + ''.'' + so.name + '''''';''
FROM sys.objects so
INNER JOIN sys.schemas ss
ON so.schema_id = ss.schema_id
INNER JOIN sysusers su
ON so.schema_id = su.UID
INNER JOIN sys.sql_modules sm
ON so.Object_ID = sm.Object_ID
WHERE sm.definition LIKE ''%BI[_]%'''
Cheers!
GRRRRR. I spent 3 hours trying to find this solution. I did escape my quotes. Honest I did.
Now I get to go count your quotes vs mine to find out where it was I mucked up. Thanks, Jacob. This help.
February 11, 2016 at 10:39 am
So apparently I didn't go far enough on my quotes. I was doing 4 / 5 instead of 6. At least it works now.
Thanks, everyone.
February 11, 2016 at 10:59 am
Glad I could help!
If it helps, here's that string constructed step-by-step (all of it assuming your FROM clause):
Step 1:
SELECT 'EXEC sp_helptext '+ss.name+'.'+so.name+';'
That generates the EXEC sp_helptext for every schema.object, but there are no single quotes around schema.object. To do that, we need insert a literal single quote into the string, which of course needs to be escaped, so we insert a pair of single quotes in the appropriate places:
SELECT 'EXEC sp_helptext '''+ss.name+'.'+so.name+''';'
That gives us what we want when run with your FROM clause as a standalone query. Of course, all of this is within the string for EXEC sp_MSforeachDB, so each of the single quotes in that above command need to be escaped with another single quote. The occurrences of 1 single quote become 2, and the occurrences of 3 single quotes become 6, yielding the version in code I posted.
I know all too well how easy it is to get all turned around with multiple levels of dynamic SQL, so I'm just glad I could help end the struggle against the insidious single quote 🙂
February 11, 2016 at 11:33 am
Slightly longer suggestion here
😎
February 11, 2016 at 2:14 pm
Whenever I am building out dynamic SQL I always use SET QUOTED_IDENFITIER OFF first and then use double quotes in the outer sections of text. Then I never have to worry about how many stupid single quotes I need to string together to get the various requirements needed!! 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 12, 2016 at 8:20 am
TheSQLGuru (2/11/2016)
Whenever I am building out dynamic SQL I always use SET QUOTED_IDENFITIER OFF first and then use double quotes in the outer sections of text. Then I never have to worry about how many stupid single quotes I need to string together to get the various requirements needed!! 😎
Oh, stop being logical.
We're DBAs. Logic isn't supposed to be a part of our jobs. @=)
EDIT: And thank you for the suggestion. I'll look into it.
February 12, 2016 at 8:32 am
Brandie Tarvin (2/12/2016)
TheSQLGuru (2/11/2016)
Whenever I am building out dynamic SQL I always use SET QUOTED_IDENFITIER OFF first and then use double quotes in the outer sections of text. Then I never have to worry about how many stupid single quotes I need to string together to get the various requirements needed!! 😎Oh, stop being logical.
We're DBAs. Logic isn't supposed to be a part of our jobs. @=)
EDIT: And thank you for the suggestion. I'll look into it.
While I am EXCEPTIONALLY logical and rational (just ask my wife and daughter!!), this recommendation has more to do with one of my other significant traits: I am also exceptionally LAZY!! 😀
And you are welcome! This little tip WILL save you a LOT of irritation when dealing with dynamic SQL, I promise!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 12, 2016 at 12:39 pm
TheSQLGuru (2/12/2016)
Brandie Tarvin (2/12/2016)
TheSQLGuru (2/11/2016)
Whenever I am building out dynamic SQL I always use SET QUOTED_IDENFITIER OFF first and then use double quotes in the outer sections of text. Then I never have to worry about how many stupid single quotes I need to string together to get the various requirements needed!! 😎Oh, stop being logical.
We're DBAs. Logic isn't supposed to be a part of our jobs. @=)
EDIT: And thank you for the suggestion. I'll look into it.
While I am EXCEPTIONALLY logical and rational (just ask my wife and daughter!!), this recommendation has more to do with one of my other significant traits: I am also exceptionally LAZY!! 😀
And you are welcome! This little tip WILL save you a LOT of irritation when dealing with dynamic SQL, I promise!
I'll have to give this tip a try next time I write dynamic SQL. Which could be really soon since I tend to write quite a bit here due to the lack of design work done for the database. It basically is treated as a data store.
February 26, 2016 at 4:19 am
TheSQLGuru (2/11/2016)
Whenever I am building out dynamic SQL I always use SET QUOTED_IDENFITIER OFF first and then use double quotes in the outer sections of text. Then I never have to worry about how many stupid single quotes I need to string together to get the various requirements needed!! 😎
Wait. You set QUOTED_IDENTIFIER OFF? Not ON?
February 26, 2016 at 7:13 am
Brandie Tarvin (2/26/2016)
TheSQLGuru (2/11/2016)
Whenever I am building out dynamic SQL I always use SET QUOTED_IDENFITIER OFF first and then use double quotes in the outer sections of text. Then I never have to worry about how many stupid single quotes I need to string together to get the various requirements needed!! 😎Wait. You set QUOTED_IDENTIFIER OFF? Not ON?
Of course. Simple test/proof:
SET QUOTED_IDENTIFIER OFF
GO
CREATE TABLE #tmp (a varchar(10))
GO
INSERT #tmp VALUES ('asdf')
go
DECLARE @a varchar(10) = 'asdf'
EXEC ("select * from #tmp where a = '" + @a + "'")
GO
PRINT "DONE"
GO
SET QUOTED_IDENTIFIER ON
GO
DECLARE @a varchar(10) = 'asdf'
EXEC ("select * from #tmp where a = '" + @a + "'")
DROP TABLE #tmp
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply