Search code examples
sql-servert-sqllinked-server

How to create a table on a linked server with a variable server name?


I've seen some solutions on how to create tables on linked servers using EXEC('some sql') AT [LINKED_SERVER_NAME] and they work manually.

I can use DML queries such as

EXEC ( 'select * from [' + @DbServerName + '].[' + @DbName + '].dbo.someTable' )

how can I do something similar for DDL queries like

EXEC ( 'CREATE TABLE [' + @DbServerName + '].[' + @DbName + '].dbo.someTable ( id int null) ' )

I've toyed around with select * from openquery(linkedservername, query) and exec(sql) AT [linkedservername], but every time I try to make the server name a variable it fails for me.

I can run all these commands manually at in Query Analyzer, but whenever I try to make the linked server name a variable they fail for me. What I'm trying to do is something like this...

DECLARE @LinkedServerName nvarchar(100)
DECLARE @LinkedDbName nvarchar(100)
SET @LinkedServerName = 'SVR2'
SET @LinkedDbName = 'DB2'

DECLARE @DDL_QUERY nvarchar(1000)
SET @DDL_QUERY = 'CREATE TABLE [' + @LinkedDbName + '].dbo.T1 ( id int null )'

-- Current failed ideas
EXEC( @DDL_QUERY ) AT @LinkedServerName
SELECT * FROM OPENQUERY(@LinkedServerName, @DDL_QUERY)
EXEC( 'CREATE TABLE [' + @LinkedServerName + '].[' + @LinkedDbName + '].dbo.T1( id int null )'

Is it possible to dynamically create a table when the linked server name, and database name on that linked server are both declared variables?


Solution

  • Assuming the linked server is also SQL Server (or possibly Sybase):

    DECLARE @table NVARCHAR(MAX), @sql NVARCHAR(MAX);
    
    SET @table = N'CREATE TABLE dbo.T1(id INT NULL);';
    
    SET @sql = N'EXEC ' + QUOTENAME(@LinkedServerName) + N'.' 
      + QUOTENAME(@LinkedDbName) + N'.sys.sp_executesql @table;';
    
    EXEC sys.sp_executesql @sql, N'@table NVARCHAR(MAX)', @table;
    

    Slightly less verbose syntax:

    DECLARE @sql NVARCHAR(MAX), @exec NVARCHAR(MAX);
    
    SET @sql = N'CREATE TABLE dbo.T1(id INT NULL);';
    
    SET @exec = QUOTENAME(@LinkedServerName) + N'.' 
      + QUOTENAME(@LinkedDbName) + N'.sys.sp_executesql';
    
    EXEC @exec @sql;