Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

create a table on a 2nd SQL server via linked server Expand / Collapse
Author
Message
Posted Wednesday, February 14, 2007 8:26 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 10:04 AM
Points: 585, Visits: 53

I guess the title explains everything, but I was wondering if anyone had a method of my being able to create a table over a linked server?  Either by executing a SP on the remote side with the name and schema passed as parameters or directly?

I need to seperate off some very CPU intensive pivoting that is killing the main production server, so my thoughs are to create the table, populate it with the data as needed, run the pivot SP on the remote server via openrowset or some other method and then delete the table again.

I've got pretty much everything working except for the create table, I have a SP on the remote side that runs and creates the table when run locally but not when ran across a linked server (very annoying)

Oh yeah, it's SQL 2000 enterprise edition (Itanium version if you're interested) running on Windows Enterprise edition.

If no-one can help then I might just go with creating some static tables and controlling them via a key for each set of data that uses it, but that would be messy and involve more maintenance than the one that I want to do.

Post #344839
Posted Wednesday, February 14, 2007 9:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:36 AM
Points: 12,921, Visits: 32,295

ok how about this: can you do this to the linked server? using the SELECT ...INTO feature, but make sure the where statement returns no rows, so it creates the table but does not populate it?:

 

select actnbr,actname
into newtable
from gmact
where 1=2

select * from newtable

actnbr             actname                                 
------------------ ----------------------------------------

(0 row(s) affected)



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #344873
Posted Wednesday, February 14, 2007 11:08 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 10:04 AM
Points: 585, Visits: 53

I'd thought of that but you're only allowed a maximum of 2 prefixes, i'd need 3-4 [linkedserver].database.owner.table.

Unless I can get away with just using linkedserver.table and set up the linked server with the right default DB and owner etc, i'll give it a try

Post #344912
Posted Thursday, February 15, 2007 3:02 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 10:04 AM
Points: 585, Visits: 53
What an idiot, i have no idea why i never just thought of exec-ing the SP over the linked server, that worked a treat
Post #345059
Posted Monday, July 26, 2010 2:56 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 9:31 AM
Points: 1,294, Visits: 2,991
hello, i am running into the same issue...how can i execute the output of SP on a remote server linked server..

Exec ('use Test_Script exec dbo.Test_CreateTable') at [ABC-DEV]


Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'dbo.Test_CreateTable'.
Post #959088
Posted Thursday, January 20, 2011 3:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 20, 2011 3:35 AM
Points: 1, Visits: 4
Try this..
Exec (' exec Test_Script..Test_CreateTable') at [ABC-DEV]
Post #1050603
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse