barnesco
asked on
Incorrect tsql syntax
The below returns incorrect syntax near 'db_owner'. I'm not sure how to fix. Please help:
DECLARE @sql nvarchar(4000);
DECLARE @BigSQL nvarchar(4000);
DECLARE @dbName varchar(100);
SET @dbName = 'TSQL2012';
SET @sql = 'EXEC sp_addrolemember ''db_owner'', ''testuser''';
SET @BigSQL = 'USE ' + @dbName + '; EXEC sp_executesql N''' + @sql + '''';
EXEC (@BigSQL)
DECLARE @sql nvarchar(4000);
DECLARE @BigSQL nvarchar(4000);
DECLARE @dbName varchar(100);
SET @dbName = 'TSQL2012';
SET @sql = 'EXEC sp_addrolemember ''db_owner'', ''testuser''';
SET @BigSQL = 'USE ' + @dbName + '; EXEC sp_executesql N''' + @sql + '''';
EXEC (@BigSQL)
PortletPaul
remove the semi-colons (at the end of each line)
You cannot use 'USE' inside EXEC command. Instead, you need to use a dotted command, like this:
DECLARE @sql nvarchar(4000);
DECLARE @BigSQL nvarchar(4000);
DECLARE @dbName varchar(100);
SET @dbName = 'TSQL2012';
SET @sql = @dbName' + ..sp_addrolemember ''db_owner'', ''testuser''';
EXEC (@sql) ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That's the for the assist. Works great.