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

Question

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?

Aaron Bertrand · Accepted Answer · 2015-10-08 16:53:33Z

4

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;

edited Oct 8, 2015 at 16:53

answered Apr 12, 2013 at 15:53

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Great Turtle Over a year ago

Nesting calls to sp_execsql... very nice. Thanks! :-)

angelcake Over a year ago

When the LinkedDbName is on EDW environment, how do I set it up?

Aaron Bertrand Over a year ago

@angelcake Like HP EDW? Sorry, no idea. This works because the assumption is that the linked server is also SQL Server. For other platforms, I won't be able to help.

angelcake Over a year ago

Yes it is Linux EDW. it's okay. Thank you very much!

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related