Linked server and temp table issue

Question

I had our vendor create a linked server (SQL Server 2008) to our Server (SQL Server 2000).

This was in response to an issue I had with the vendor changing a data type to varchar(max). I was informed that I should create a link from 2008 to 2000 to resolve the issue. Now that I have accomplished this task, when I run my query that links to a table on the SQL Server 2000 I receive this error:

Msg 208, Level 16, State 1, Line 8 Invalid object name 'dbo.tbl_QClients'. The linked server has permissions to this table.

This is basically what my query looks like;

Select
  Col1,
  Col2,
  Col3,
  ...
INTO
  #temptable
FROM
  tbl_on_SQL2000
...

It's little more complex than this (multiple joins) but this is basically the query. The one thing I found is when I remove the INTO #temptable from the query it will not give me an error. Any ideas?

Thanks for you time.

Regards, Oscar

Answer 1

Check if you have write permissions, SELECT ... INTO creates a new table so that's most likely why it's giving you an error.

Answer 2

Try defining the temp table before the insert...

CREATE #temptable (
   blah,
   blah,
   blah
)

INSERT INTO
   #temptable
SELECT
   blah
FROM
   blah

I've often experienced 'unexpected' behaviour when joining tables from one server on to tables from another server. Mostly because SQL is good at pulling data from a remote server [it's often pulled as one large set], but poor and pushing data to a remote server [it's often done one record at a time]. Because of this I tend to reformulate by queries into smaller and simpler ones, getting the behaviour and performance I expect.