June 12, 2012 at 1:49 pm
DECLARE @cmd NVARCHAR(255)
SET @cmd = '''SELECT 'USE ' + DatabaseName + Char(13) + Char(10) + 'EXEC sp_revokedbaccess [' + ORPHANEDUSER + ']' + Char(13) + char(10) +
'UPDATE [OPS]..[OPS_ORPHANED_USERS]' + ' SET IsDELETED = 1, UPDATED_DATETIME = GETDATE(), UPDATED_BY = SUSER_SNAME()' +
' WHERE DATABASENAME = ''' + DatabaseName + ''' AND ORPHANEDUSER = ''' + ORPHANEDUSER + '''' + ' AND IsDELETED = ' + '0' +
' AND CONVERT ( VARCHAR, UPDATED_DATETIME, 101) = ''' + CONVERT (VARCHAR, UPDATED_DATETIME , 101) + '''' + CHAR(13) + CHAR(10) + 'GO' +
CHAR(13) + CHAR(10) +CHAR(10)
FROM [OPS_ORPHANED_USERS] '
SELECT @cmd
ERROR:
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ' + DatabaseName + Char(13) + Char(10) + '.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ' + Char(13) + char(10) +
'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ' + '.
June 12, 2012 at 1:57 pm
You have to use double-single-quotes to put single-quotes in a string that way.
Not the shift+quote key, the quote key twice.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 12, 2012 at 1:58 pm
Here is a simple example...
DECLARE @cmd NVARCHAR(255)
SET @cmd = 'This string has it''s content escape''d so it won''t have an issue.'
SELECT @cmd
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply