Add constraints to table in a linked server from a stored proc 1

[mflancour]

I'm having problems trying to add indexes and constraints to a table in a linked server. This is what I currently have:

SELECT @sql = 'ALTER TABLE ' + @v_listserver + '.[' + @v_listdatabase + '].dbo.' + @p_listname +  
	'ADD CONSTRAINT [PK_' + @p_listname + '] PRIMARY KEY CLUSTERED ([subscriber_id]) ON [PRIMARY]'
PRINT @sql EXEC sp_executesql @sql

Error:
Msg 117, Level 15, State 1, Line 1
The object name 'server1.db1.dbo.' contains more than the maximum number of prefixes. The maximum is 2.

 

[mflancour]

Another question that comes to mind related to this is ...Would it be better to have 2 procs? one that does the work needed on server1 and another that proc1 calls on server2 to create this table? or is it ok do create the table remotely?

 

[ptheriault]

I would suggest having one proc on the link server to do all the work.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[mflancour]

well, I can't do everything on the linked server. too much network trafic to update records that are on the local server. The only thing on the linked server is the table being created. Is there a way to create a table on a linked server and add keys and such? I guess if I can see how I can figure out what is the best method.

 

[ptheriault]

You could try this. I don't know if it will work, I haven't had time to test.

SELECT * FROM
OPENQUERY(LinkName,'CREATE TABLE TEST(id int not null)')

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[mflancour]

doesn't appear to work.

 

[ptheriault]

I think this will work.

EXEC LINKSERVER.TESTDB.dbo.sp_executesql N'
create table dbo.testtable
(
col_test char(10)
)'

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[mflancour]

Sure did. Nice and easy too, thanks. Now I just have to figure out if I should really be using it.