" must be the first statement in a query batch" when using generated DDL

Question

I have a server with over 100 databases linked to other database servers. These databases have views of the linked tables. I need to update the views weekly for any object changes in the linked servers, ie column adds.

I create this script to loop through all the databases and grab all the views and refresh them by doing alter view. sp_refreshview does not work with linked servers.

When I print the @sql variable, it works fine in another query window. When I try to execute the @sql variable, it gives me the following error:

Msg 111, Level 15, State 1, Line 3
'ALTER VIEW' must be the first statement in a query batch.

I think it has something to do with the LF/CR. I have tried many ways with no luck.

Any ideas?

DECLARE @command varchar(1000) 
CREATE TABLE #tempViewSQL (DBName VARCHAR(255)
                          ,ViewSQL VARCHAR(4000))

SELECT @command = 'IF ''?'' NOT IN(''master''
                                 , ''model''
                                 , ''msdb''
                                 , ''tempdb''
                                 ,''pubs''
                                 ,''AuditProduction''
                                 ,''AuditProductionTest''
                                 ,''IID_Support''
                                 ,''Insurance_Files''
                                 ,''LoansAnalysis''
                                 ,''QualityAudit''
                                 ,''QualityAuditTest'') 
                BEGIN 
                  USE ? 
               INSERT INTO #tempViewSQL 
               SELECT TABLE_CATALOG, replace(view_definition,''create view'',''alter view'') 
                 FROM information_schema.views 
                WHERE TABLE_NAME NOT IN (''syssegments'',''sysconstraints'')
                 END' 

EXEC sp_MSforeachdb @command


DECLARE @SQLCursor VARCHAR(2000)
DECLARE @SQL VARCHAR(2000)
DECLARE @DbName VARCHAR(255)
DECLARE MyCursor CURSOR FOR
SELECT DBName, ViewSQL FROM  #tempViewSQL

OPEN MyCursor

FETCH NEXT FROM MyCursor INTO @DbName,@SQLCursor

WHILE @@FETCH_STATUS = 0   
BEGIN   


SET @SQL = 'USE ' + @DBName + CHAR(10) + CHAR(13) + 'GO' + CHAR(10) + CHAR(13) + @SQLCursor 
--PRINT (@SQL)
EXECUTE (@SQL)

FETCH NEXT FROM MyCursor INTO @DbName,@SQLCursor

END

CLOSE MyCursor   
DEALLOCATE MyCursor 

DROP TABLE #tempViewSQL

score 3 · Answer 1 · edited May 23 '17 at 11:49

"GO" is not actually valid T-SQL. It's just a string which various SQL tools such as SSMS recognize as a batch separator (as if you ran each chunk separately).

So you probably got an error along the lines of "Incorrect syntax near 'GO'" as well.

In order to create a view in another database, you will need to run sp_executesql in the context of that database, such as:

EXEC OtherDatabase.dbo.sp_executesql @SQL;

Credit to Bob Pusateri's blog for that insight.

However, you have a dynamic database name, which makes it extra complicated. I believe you could probably EXEC dynamic SQL which contained the sp_executesql command qualified with the dynamic database name, Incpetion-style. But you'll have to be careful about your single quote encoding.

score 1 · Answer 2 · answered Feb 28 '14 at 21:35

You can try to execute query inside of specific database through using sp_executesql SP like below::

DECLARE @AlterQuery NVARCHAR(MAX) = N'ALTER VIEW v1 AS SELECT * from T1' 
DECLARE @DbName NVARCHAR(MAX) = 'Test'
DELARE @Query NVARCHAR(MAX) = 'exec [' + @DbName + '].sys.sp_executesql N''' + REPLACE( @AlterQuery, '''', '''''' ) + ''''
EXECUTE( @Query )

score 0 · Answer 3 · answered Feb 28 '14 at 22:00

I like to use Powershell for deployments like this where I store all of the server/database combinations in a table on a central server and then use this table to populate a list of servers to run across and then loop through them to run some logic. It's not a pure SQL solution, but it can be easily modified to get the job done...

function Get-ProductionDatabases
{
    param
    (
        [Parameter(Mandatory=$true)]
        [string]$centralServer,
        [Parameter(Mandatory=$true)]
        [string]$centralDatabase
    )

    $conn = New-Object System.Data.SqlClient.SqlConnection "Server=$centralServer;Database=$centralDatabase;Integrated Security=SSPI;"; 
    $dt   = New-Object System.Data.DataTable;    

    $cmd = $conn.CreateCommand(); 
    $cmd.CommandType = [System.Data.CommandType]::Text
    $cmd.CommandText = "Select  [ServerName], 
                                [DatabaseName]
                        From    [dbo].[ProductionDatabases];";

    $conn.Open();
    $dt.Load($cmd.ExecuteReader());
    $conn.Close();    

    $dt
}


$productionDatabases = Get-ProductionDatabases -centralServer "ProductionServer\Instance" -centralDatabase "CentralDatabase"

foreach($db in $productionDatabases)
{
    $conn = New-Object System.Data.SqlClient.SqlConnection "Server=$($db.ServerName);Database=$($db.DatabaseName);Integrated Security=SSPI;"; 
    $queryOut = New-Object System.Data.DataTable;    

    $cmd = $conn.CreateCommand(); 
    $cmd.CommandType = [System.Data.CommandType]::Text
    $cmd.CommandText = "Exec sp_refreshview;";                        

    $conn.Open();

    try
    {
        $queryOut.Load($cmd.ExecuteReader());
        $conn.Close();           
    }
    catch
    {
        "Warning: Error connecting to $($db.ServerName)."
    }
}

" must be the first statement in a query batch" when using generated DDL

3 Answers3