create a table on a 2nd SQL server via linked server
-
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.
-
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
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
-
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
-
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'.
-
Try this..
Exec (' exec Test_Script..Test_CreateTable') at [ABC-DEV]
-
i'm using this script :
select * into LinkedServer.[DB_Name].[dbo].[Table_Name] FROM Server2.[DB2_Name].[dbo].Table2_Name
but give this error:
Msg 117, Level 15, State 1, Line 1
The object name 'LinkerserverName.DB_Name.dbo.Table_Name' contains more than the maximum number of prefixes. The maximum is 2.
i don't understand how can i use SP to solve this problem ?????
DBA -
ismahmoud (10/13/2016)
i'm using this script :select * into LinkedServer.[DB_Name].[dbo].[Table_Name] FROM Server2.[DB2_Name].[dbo].Table2_Name
but give this error:
Msg 117, Level 15, State 1, Line 1
The object name 'LinkerserverName.DB_Name.dbo.Table_Name' contains more than the maximum number of prefixes. The maximum is 2.
i don't understand how can i use SP to solve this problem ?????
In your example, you cannot use the select...into newtable via a linked server. The four part syntax s not supported for that dynamic creation.
You have to create the table explititly, then use a standard insert into tablename...select
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply