Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
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 @ 11:52 AM
Points: 14,538, Visits: 38,376

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!
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
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, November 18, 2016 9:50 AM
Points: 1,525, Visits: 3,573
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
Posted Thursday, October 13, 2016 12:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 29, 2016 2:58 AM
Points: 7, Visits: 14
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
Post #1825562
Posted Thursday, October 13, 2016 4:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:52 AM
Points: 14,538, Visits: 38,376
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!
Post #1825612
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse