Solved

Incorrect tsql syntax

Posted on 2014-12-08
4
224 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 48

Expert Comment

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

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

Title # Comments Views Activity
SQL Percentage Formula 7 29
sql trace 4 17
SQL: Transformation or Pivot 3 29
File attachment in the SQL Database from application 10 39
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

738 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