Creating Table on different Server using Variables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Creating Table on different Server using Variables

Author

Topic

Macro1
Starting Member

8 Posts

Posted - 2010-10-15 : 05:25:51

Hi,

I was just wondering if anybody could help me out with the correct syntax to run a stored procedure from one server to create a table on a different server. The problem I'm having is that the server, database name and table name are going to vary everytime so I'm passing variables through.

DECLARE @TableName AS varchar(100)
SET @TableName = Map_'+LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(@PortNum, ' ',''),'+',''),',',''),'&','')))

CREATE TABLE @ServerName.@DBName.@TableName
	(
	ID VARCHAR(10) NULL,
	COUNTRY VARCHAR(2) NULL,
	CRESTA VARCHAR(5) NULL,
	TSIUSD FLOAT NULL,
	TSI FLOAT NULL,
	MrkTSI FLOAT NULL,
	Mshare FLOAT NULL
	)

Is this syntax correct?

Thanks in advance

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-15 : 05:42:55

Is this syntax correct?

No it isn't.

You need dynamic sql for that.
If you don't know about dynamic sql then you have to learn.
http://www.sommarskog.se/dynamic_sql.html

In the first step you should work against the goal to create a table on the same server.

In the next step you can work against the goal to create a table on another server.
For this it is needed to have a linked server (you know I already told you that in your old thread)
If you don't know about linked server then you have to learn.
http://www.databasejournal.com/features/mssql/article.php/3085211/Linked-Servers-on-MS-SQL-Part-1.htm

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources