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?
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;