Solved

Incorrect tsql syntax

Posted on 2014-12-08
4
208 Views
Last Modified: 2014-12-08
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)
0
Comment
Question by:barnesco
4 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40487899
remove the semi-colons (at the end of each line)
0
 
LVL 24

Expert Comment

by:chaau
ID: 40487900
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) 

Open in new window

0
 
LVL 32

Accepted Solution

by:
bhess1 earned 500 total points
ID: 40488034
Your problem is not enough single quotes.  As written, the value in @BigSQL to be executed is:

USE TSQL2012; EXEC sp_executesql N'EXEC sp_addrolemember 'db_owner', 'testuser''

Where the bolded items are not quoted.  To fix this is simple, but tiresome when counting single quotes.  Just replace:

SET @sql = 'EXEC sp_addrolemember ''db_owner'', ''testuser''';

With:

SET @sql = 'EXEC sp_addrolemember ''''db_owner'''', ''''testuser''''';

And @BigSQL now equals:

USE TSQL2012; EXEC sp_executesql N'EXEC sp_addrolemember ''db_owner'', ''testuser'''
0
 

Author Closing Comment

by:barnesco
ID: 40488134
That's the for the assist. Works great.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now