I need to drop and recreate table and insert on EDW environment from SQL Server. From "PRINT @DropTable" below, I get the result:
N'EXEC (''DROP TABLE dbname.tablename;'') AT [linkedserver];'
And if I run it manually:
EXEC sp_executesql N'EXEC (''DROP TABLE dbname.tablename;'') AT [linkedserver];'
, it works. But when I run
EXEC sp_executesql @DropTable
, it gives me the error:
N'EXEC (''DROP TABLE dbname.tablename;'') AT [linkedserver];' Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'EXEC ('DROP TABLE dbname.tablename;') AT [linkedserver];'.
Here is my code:
DECLARE @TableName VARCHAR(25)
DECLARE @TDQuery NVARCHAR(MAX)
DECLARE @OpenQry NVARCHAR(MAX)
DECLARE @TableToDrop VARCHAR(25)
DECLARE @DropTable NVARCHAR(MAX)
DECLARE @DropTableSQL NVARCHAR(MAX)
DECLARE @linkedServer VARCHAR(50)
DECLARE @linkedDbName VARCHAR(50)
DECLARE @TabExists BIT
DECLARE CUR_QRY CURSOR FOR
SELECT TableName, TDQuery FROM dbo.tbl_TABLE
OPEN CUR_QRY
FETCH NEXT FROM CUR_QRY INTO @TableName, @TDQuery
WHILE @@FETCH_STATUS = 0
BEGIN
SET @linkedServer='linkedserver'
SET @linkedDbName='dbname'
SET @OpenQry='Select count(1) as TabExists FROM DBC.TABLES WHERE TABLEKIND=''T'' AND DATABASENAME=''dbname'' AND TABLENAME=''' +@TableName+ ''''
SET @OpenQry = N'select @TabExists = CASE WHEN TabExists = 0 THEN 0 ELSE 1 END from OPENQUERY('+@linkedServer+', ''' + REPLACE(@OpenQry, '''', '''''') + ''')'
EXEC sp_executesql @OpenQry, N'@TabExists BIT OUT', @TabExists OUT;
PRINT @TabExists
IF @TabExists = 1
PRINT @TableName
SET @DropTableSQL = N'''''DROP TABLE dbname.'+ @TableName+';''''';
PRINT @DropTableSQL
SELECT @DropTable = 'N'''+ N'EXEC (' + @DropTableSQL + N') AT [linkedserver];'''
PRINT @DropTable
EXEC sp_executesql @DropTable
FETCH NEXT FROM CUR_QRY INTO @TableName, @TDQuery
END
CLOSE CUR_QRY
DEALLOCATE CUR_QRY
The variable @DropTable is itself of type NVARCHAR(MAX) so there is no need for first N in:
SELECT @DropTable = 'N'''+ N'EXEC (' + @DropTableSQL + N') AT [linkedserver];'''
Remove it and it should work.