CODEWITHSUNDEEP
sql-servert-sqllinked-server
angelcake
angelcake

Reputation: 119

DDL and DML on EDW from SQL Server using LinkedServer does not work

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

Upvotes: 1

Views: 319

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175736

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.

Upvotes: 0

Related Questions