CODEWITHSUNDEEP
sql-server
Ian P
Ian P

Reputation: 1724

syntax issue with alter database and set command

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...

Upvotes: 1

Views: 3688

Answers (3)

gbn
gbn

Reputation: 432271

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;

Upvotes: 3

Rahul
Rahul

Reputation: 77876

Try this:

   EXECUTE sp_executesql @SQL;

Upvotes: 1

codeling
codeling

Reputation: 11379

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).

Upvotes: 2

Related Questions