Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 4 down vote favorite

I'm creating a stored procedure to copy all views from one database to another using the following code. 

ALTER PROCEDURE [dbo].[spCreateViews] (@SourceDB NVARCHAR(200), @DestinationDB NVARCHAR(200))
AS
BEGIN
    DECLARE @SQL2 NVARCHAR(MAX)
    SET @SourceDB = REPLACE(REPLACE(@SourceDB, '[', ''), ']','')
    SET @DestinationDB = REPLACE(REPLACE(@DestinationDB, '[', ''), ']','')

    SET @SQL2 = 
        'USE [' + @SourceDB + '] 
        BEGIN
        DECLARE @SQL NVARCHAR(MAX)
        DECLARE CUR_V CURSOR FOR
        SELECT sc.text 
        FROM sys.views av 
            JOIN sys.syscomments sc ON sc.id = av.object_id
        OPEN CUR_V
        FETCH NEXT FROM CUR_V INTO @SQL
        WHILE @@FETCH_STATUS = 0
        BEGIN
            EXEC sp_executesql N''USE [' + @DestinationDB + '] EXEC sp_executesql '' + @SQL +  ''''   
            FETCH NEXT FROM CUR_V INTO @SQL
        END
        CLOSE CUR_V
        DEALLOCATE CUR_V
        END'

    EXEC sp_executesql @SQL2
END

The stored procedure is returning the error INCORRECT SYNTAX NEAR '+'

I'm receiving the error at the first + before @SQL

As far as I can tell this syntax should be correct.

Here is the output for @SQL2 to help get rid of some of the quote nesting in case that is helpful.

USE [SOURCEDBNAME]   
BEGIN  
    DECLARE @SQL NVARCHAR(MAX)  
    DECLARE CUR_V CURSOR FOR  
    SELECT sc.text     
    FROM sys.views av          
        JOIN sys.syscomments sc ON sc.id = av.object_id  
    OPEN CUR_V  
    FETCH NEXT FROM CUR_V INTO @SQL  
    WHILE @@FETCH_STATUS = 0  
    BEGIN    
        EXEC sp_executesql N'USE [DESTINATIONDBNAME] EXEC sp_executesql ' + @SQL +  ''      
        FETCH NEXT FROM CUR_V INTO @SQL  
    END  
    CLOSE CUR_V  
    DEALLOCATE CUR_V  
END
share|improve this question

2 Answers

up vote 3 down vote

You cannot do EXEC sp_executesql on combined literals. You must use a variable as in:

ALTER PROCEDURE [dbo].[spCreateViews] (@SourceDB NVARCHAR(200), @DestinationDB NVARCHAR(200))
AS
BEGIN
    DECLARE @SQL2 NVARCHAR(MAX)
    SET @SourceDB = REPLACE(REPLACE(@SourceDB, '[', ''), ']','')
    SET @DestinationDB = REPLACE(REPLACE(@DestinationDB, '[', ''), ']','')

    SET @SQL2 = 
        '
        BEGIN
        DECLARE @SQL NVARCHAR(MAX)
        DECLARE @cmd NVARCHAR(MAX);
        DECLARE CUR_V CURSOR FOR
        SELECT sm.definition 
        FROM ' + QUOTENAME(@SourceDB) + '.sys.views av 
            JOIN ' + QUOTENAME(@SourceDB) + '.sys.sql_modules sm ON sm.object_id = av.object_id
        OPEN CUR_V
        FETCH NEXT FROM CUR_V INTO @SQL
        WHILE @@FETCH_STATUS = 0
        BEGIN
            EXEC ' + QUOTENAME(@DestinationDB) + '.sys.sp_executesql @SQL;
            FETCH NEXT FROM CUR_V INTO @SQL
        END
        CLOSE CUR_V
        DEALLOCATE CUR_V
        END'

    EXEC sp_executesql @SQL2
END

This code uses @SourceDB and @DestinationDB in the dynamic SQL to correctly identify the source and destination databases. This negates the need for the USE [Database] statement on line after SET @SQL2 =.

share|improve this answer
This solution is returning the error 'CREATE VIEW' must be the first statement in a query batch. – Lumpy Sep 10 '12 at 15:51
The problem there is you cannot execute a bunch of create view statements like that. I'll write a quick modification to my answer to help. – Max Vernon Sep 10 '12 at 16:04
I've edited my answer to resolve the CREATE VIEW issue you mentioned. – Max Vernon Sep 10 '12 at 16:37
Thanks go to dba.stackexchange.com/users/8514/cfradenburg for pointing out that sys.syscomments is limited to returning 4,000 characters. He's edited my answer to use sys.sql_modules instead. – Max Vernon Oct 10 '12 at 20:50
up vote 0 down vote

It looks like changing my original code to use 

EXEC (''USE [' + @DestinationDB + '] EXEC sp_executesql '' + @SQL +  '''')

instead of 

EXEC sp_executesql N''USE [' + @DestinationDB + '] EXEC sp_executesql N'' + @SQL +  ''''

fixes the problem.

Any ideas why this is the case?

share|improve this answer
1  
CREATE VIEW must always be the first statement in any given batch of T-SQL commands. You have changed your SQL code so that USE @DestinationDB AND CREATE VIEW ... are two logically separate T-SQL batches. I doubt this will work as expected since USE [database] does not carry over from batch to batch. – Max Vernon Sep 10 '12 at 16:36
My answer directly EXECutes sp_executesql on the @DestinationDB, resolving this issue. – Max Vernon Sep 10 '12 at 16:37
I test ran it. It's working the way that I want. Thanks for the explenation on the Exec vs sp_executesql part. I upped the comment. – Lumpy Sep 10 '12 at 17:11

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.