SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


create a table on a 2nd SQL server via linked server


create a table on a 2nd SQL server via linked server

Author
Message
Mike Metcalf
Mike  Metcalf
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2185 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.


Lowell
Lowell
SSC Guru
SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73941 Visits: 40974

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!
Mike Metcalf
Mike  Metcalf
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2185 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


Mike Metcalf
Mike  Metcalf
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2185 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
curious_sqldba
curious_sqldba
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7620 Visits: 3696
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'.
san.patil
san.patil
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 4
Try this..
Exec (' exec Test_Script..Test_CreateTable') at [ABC-DEV]
ismahmoud
ismahmoud
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 15
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
Lowell
Lowell
SSC Guru
SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73941 Visits: 40974
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search