EXEC sp_executesql

Question

EXEC sp_executesql

daniel.ahl

SSC Enthusiast

Points: 178
More actions
July 1, 2016 at 6:05 am

#319716

I have database with '-' and ' ' (space) in. When I run the script it stop on the first database with any of them in.
#######################################
USE master;
GO
SET NOCOUNT ON;
GO
DECLARE AllDatabases CURSOR FOR
SELECT name FROM sys.databases WHERE database_id > 4
OPEN AllDatabases;
DECLARE @DBNameVar NVARCHAR(128);
DECLARE @Statement NVARCHAR(300);
FETCH NEXT FROM AllDatabases INTO @DBNameVar;
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT N'CHECKING DATABASE ' + @DBNameVar;
SET @Statement = N'USE ' + @DBNameVar + CHAR(13)
+ N'DBCC CHECKDB (' + @DBNameVar + N')' + N'WITH PHYSICAL_ONLY';
EXEC sp_executesql @Statement;
PRINT CHAR(13) + CHAR(13);
FETCH NEXT FROM AllDatabases INTO @DBNameVar;
END;
CLOSE AllDatabases;
DEALLOCATE AllDatabases;
GO
SET NOCOUNT OFF;
GO
#######################################

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Add [ before and ] after the database name when you build up the script.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Thomas Rushton SSC-Insane Points: 22649 More actions · Answer 2

I would use the QUOTENAME() function wrapped around your database name - that'll make sure that it's properly handled when building up dynamic SQL statements like this.

Thomas Rushton
blog: https://thelonedba.wordpress.com

daniel.ahl SSC Enthusiast Points: 178 More actions · Answer 3

Thanks that part works fine.

when I put in sp_changedbowner, too add SA in the database who we have move to new SQL-Server.

##################

DECLARE @StatementCH NVARCHAR(200);

SET @StatementCH = N'Exec sp_changedbowner' + ' ''SA''' + ', ' + '''true'''

FETCH NEXT FROM AllDatabases INTO @DBNameVar;

WHILE (@@FETCH_STATUS = 0)

BEGIN

PRINT N'CHECKING DATABASE ' + @DBNameVar;

SET @Statement = N'USE ' + QUOTENAME(@DBNameVar);

EXEC sp_executesql @Statement, @StatementCH;

FETCH NEXT FROM AllDatabases INTO @DBNameVar;

END;

##################

But I got the message

"Msg 156, Level 15, State 1, Line 5

Incorrect syntax near the keyword 'Exec'.

Msg 102, Level 15, State 1, Line 5

Incorrect syntax near ')'."

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

Put a [font="Courier New"]PRINT @sql[/font] line in your code so that you can see exactly what's being executed.

By the way, you ought to use ALTER AUTHORIZATION instead of sp_changedbowner, since the latter is deprecated. And only put "sa" in capitals if you're confident you'll never use this code on a case-sensitive server.

John

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 5

Comment out the EXEC and print out the resultant SQL statement, that way you should be able to see what's wrong with it.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass