Insert into ##Table from linked server

Question

Insert into ##Table from linked server

C00ler

SSC Veteran

Points: 253
More actions
September 22, 2009 at 7:50 am

#156242

Hi guys,
can somebody please explain why I can't insert into temporary table from the linked server, but I can insert into permanent table.
Thank you

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Matt Whitfield SSCrazy Eights Points: 8107 More actions · Answer 1

Because temporary tables always end up in tempdb - so the temporary table reference would try to resolve from the local server. You might be able to use server.tempdb..##tablename though, i can't remember.

Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

I have successfully inserted into a temporary table from a linked server. What is the code you are using?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

kramaswamy SSCoach Points: 18181 More actions · Answer 3

Yeah I do this all the time, like for example:

IF OBJECT_ID('tempdb..#ClicktrackerIDs') IS NOT NULL

DROP TABLE #ClicktrackerIDs

CREATE TABLE #ClicktrackerIDs

(

UserID INT PRIMARY KEY

)

INSERT INTO #ClicktrackerIDs

SELECT DISTINCT tuc_userID

FROM [66.201.230.222].ctracker.dbo.ct_trackinguserclick WITH ( NOLOCK )

WHERE tuc_newsletterID IN (856)

Lowell SSC Guru Points: 323518 More actions · Answer 4

do you mean a temp table on the linked server? like this?

Select * into "server2".dbname.dbo.tablename from tablename

"The object contains more than the maximum number of prefixes. The maximum is 2."

If that was what you were refering to, then it's because you cannot create a table on the fly across a linked server. the select into...from tries to do that.

you can only insert into tables that exist.

so INSERT INTO "server2".dbname.dbo.TableThatExists

select * from tablename

will work.

the linked server only allows Data Maipulation(DML), and not object creation (DDL)

hope that helps!

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!

C00ler SSC Veteran Points: 253 More actions · Answer 5

Hi,

Thank you for the replies.

I'm sorry, I didn't give enough details.

Simple insert from Select works fine. Thing that doesn't work - is inserting from the result set of the system stored procedure. I can't remember from top of my head - I'm at home, but I call system stored procedure that reports on the status of all jobs in SQL Agent.

I loop through all linked servers on the network, get data about SQL Agent jobs, and store them in the temporary table. That's the plan anyway. Then I can analyse this data and pick out the jobs that have been running over 8 hours.

In the moment I can't do it because insert from system stored procedure fails.

Thank you

kramaswamy SSCoach Points: 18181 More actions · Answer 6

I think I might know what you're referring to.

Were you trying to do something like:

CREATE TABLE #Temp

(

Cols

)

INSERT INTO #Temp (Cols)

EXEC RemoteServer.Database.Schema.StoredProcedure

?

If so, you might have been getting an error regarding distributed transactions. I'm not too sure on the explanation for that, though I did some research and it has something to do with temp tables, and how it tries to create a transaction when executing a stored proc to insert into it.

You could try this, this was the solution which I employed:

CREATE TABLE #temp

(

Cols

)

INSERT INTO #temp

SELECT * FROM OPENQUERY(RemoteServer, 'EXEC Database.Schema.StoredProcedure')

Matt Whitfield SSCrazy Eights Points: 8107 More actions · Answer 7

Sorry - I got completely the wrong end of the stick there! 😀

Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone

C00ler SSC Veteran Points: 253 More actions · Answer 8

INSERT INTO #temp
SELECT * FROM OPENQUERY(RemoteServer, 'EXEC Database.Schema.StoredProcedure')

I never heard of OpenQuery, so don't really know what the line above does yet 🙂

I will look into it!

Thank you