syntax issue with alter database and set command

Question

Hi I'm tying to run an alter database statement and keep getting an error: (this is to go in a script which will run against a number of databases)

SELECT is_broker_enabled FROM sys.databases WHERE name = db_name()

DECLARE @SQL NVARCHAR(1024)


IF (SELECT is_broker_enabled FROM sys.databases WHERE name = db_name()) = 0
BEGIN

    SET @SQL = N'ALTER DATABASE [' + db_name() + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE';
    SELECT @SQL;
    sp_executesql @SQL;

    SET @SQL = N'ALTER DATABASE [' + db_name() + '] SET ENABLE_BROKER';
    SELECT @SQL;
    sp_executesql @SQL;


    SET @SQL = N'ALTER DATABASE [' + db_name() + '] SET MULTI_USER';
    SELECT @SQL;
    sp_executesql @SQL;

END 

SELECT is_broker_enabled FROM sys.databases WHERE name = db_name()

I keep getting

Msg 102, Level 15, State 1, Line 11 Incorrect syntax near 'sp_executesql'. Msg 102, Level 15, State 1, Line 15 Incorrect syntax near 'sp_executesql'. Msg 102, Level 15, State 1, Line 20 Incorrect syntax near 'sp_executesql'.

I'm doing something dumb - but like most syntax problems looking does not give rise to seeing...

gbn · Accepted Answer · 2011-12-17 13:02:06Z

3

Calling a stored procedure without EXEC is only allowed as the first line in a batch. Your calls are not.

Have you also considered this construct to do it in one go?

SET @SQL = N'
 ALTER DATABASE [' + db_name() + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
 ALTER DATABASE [' + db_name() + '] SET ENABLE_BROKER;
 ALTER DATABASE [' + db_name() + '] SET MULTI_USER;
';
SELECT @SQL;
EXEC sp_executesql @SQL;

answered Dec 17, 2011 at 13:02

gbn

435k85 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ian P Over a year ago

Not sure about your comment on the exec being the first line in the batch - i know its true for create - but even you do not have it as the first line... After a bit of testing - its the added EXEC that causing it to work. Dont worry, I'm feeling dumb by missing the exec!

gbn Over a year ago

@Ian P: What it means is you can leave out EXEC only for the first code line. Any other stored proc calls requires EXEC.

Ian P Over a year ago

Didnt know that - but I suppose it has to be true otherwise highlighting a proc in SSMS and <f5> would not work. Still hopefully I wont forget the exec for another 5 years

codeling · Accepted Answer · 2011-12-17 12:49:10Z

2

Try with the exec keyword, like this:

exec sp_executesql @SQL;

That's the way how you call another stored procedure from inside a stored procedure (you could also use EXECUTE, which does exactly the same). Take care not to confuse it with the Exec(...) function (see also here).

answered Dec 17, 2011 at 12:49

codeling

11.5k6 gold badges48 silver badges76 bronze badges

Comments

Rahul · Accepted Answer · 2011-12-17 12:51:43Z

1

Try this:

   EXECUTE sp_executesql @SQL;

answered Dec 17, 2011 at 12:51

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Collectives™ on Stack Overflow

syntax issue with alter database and set command

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related