barnesco
asked on
Dynamic query with escaped apostrophes
I'm trying to create a dynamic query to find the server name and assign a role, but my syntax is off just a bit--it returns "Incorrect syntax near '\'. Please help.
DECLARE
@server nvarchar(100),
@sqlLoginCommand varchar(1000),
@sqlRoleCommand varchar(1000),
@role varchar(50),
@username varchar(100)
SET @server = CAST(serverproperty('servername') AS nvarchar(100))
SET @role = 'sysadmin'
SET @username = 'myUserName'
SET @sqlRoleCommand = 'EXEC master..sp_addsrvrolemember ' + '' + @server + '\' + @username + '' + ', ' + '' + @role + ''
EXEC (@sqlRoleCommand)
kaufmed
You don't have any escaped quotes. You have a couple of empty strings in there--not sure why. You also have two dots between "master" and "sp_addsrvrolemember".
ASKER
Thought I did. How would you rewrite this then?
ASKER
AKA, I'm trying to return the following dynamically. How should this been written:
EXEC master..sp_addsrvrolemembe
EXEC master..sp_addsrvrolemembe
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Needed he extra period to work :). Thanks!
SET @sqlRoleCommand = 'EXEC master..sp_addsrvrolemembe
SET @sqlRoleCommand = 'EXEC master..sp_addsrvrolemembe
Ah.Good to know for the future!