Solved

Incorrect tsql syntax

Posted on 2014-12-08
4
220 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

820 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