OpenQuery

• Alex Chavez

  SSChasing Mays

  Points: 621

  More actions

  February 10, 2010 at 5:28 pm

  #145126

  Hello Everyone,

  I'm trying to create a table with information I grabbed from on server/database to a different server/database.

  I wrote a Query on Server A and put the results into a temp table, I know want to take that temp table and replicate it as a regular table on Server B.

  I've tried using OpenQuery and this is what I got

  SELECT * FROM OPENQUERY([Server-B],'SELECT * FROM #Temp_Table WHERE FILE_NAME = ''DBName..NewTable''')

  I get this error when running it

  OLE DB provider "SQLNCLI" for linked server "ServerB" returned message "Deferred prepare could not be completed.".

  Msg 8180, Level 16, State 1, Line 1

  Statement(s) could not be prepared.

  Msg 208, Level 16, State 1, Line 1

  Invalid object name '#Temp_table'.

  Am I going about this the right way? If so what do I need to do in order to fix this error? I've tried running a basic select using openquery and it worked just fine.

  Thanks in advance,

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  February 11, 2010 at 9:26 am

  #1117625

  Try with:

  SELECT *

  INTO [Server-B].[DB_Name].dbo.NewTable

  FROM #temp_table

  WHERE FILE_NAME = 'DBName..NewTable'

  The reason your OPENQUERY fails is that #temp_table is a temporary table, visible only on Server-A in the session that created it. No way it will be available on Server-B, different session.

  Be careful that my SELECT/INTO query will hold schema locks while running. You could change it to a SELECT TOP 0 * INTO first, just to create the table, and a regular INSERT INTO to pull the data.

  Regards

  Gianluca

  -- Gianluca Sartori