Temp table from text file connected via Linked Server

From: CLerner (anonymous_at_discussions.microsoft.com)
Date: 06/24/04 

Date: Thu, 24 Jun 2004 10:19:53 -0700

I have a text file as a table in a linked server. I am
able to query on the table as link as the linked server
and text file object names are coded in the select
statement. I am attempting to dynamically build the SQL
statement to query the file, changing the name of the file.

If I run:

SELECT TextFile.*
INTO #TempTextFile
FROM BHOTextFilesCRM...[TA051904_200#TXT] as TextFile
left outer join Imports_TA on
TextFile.Consumer_SSN=Imports_TA.Consumer_SSN
and TextFile.Date_Assess = Imports_TA.Date_Assess
and TextFile.Transmit_Date = Imports_TA.Transmit_Date
WHERE Imports_TA.Consumer_SSN is null

I get the resulting temp file

However, trying to pass a variable for the table name is
not working. Doing the following
DECLARE @strFileName varchar(50)
SET @strFileName = TA051904_200#TXT

SELECT TextFile.*
INTO #TempTextFile
FROM BHOTextFilesCRM...[@strFileName] as TextFile
left outer join Imports_TA on
TextFile.Consumer_SSN=Imports_TA.Consumer_SSN
and TextFile.Date_Assess = Imports_TA.Date_Assess
and TextFile.Transmit_Date = Imports_TA.Transmit_Date
WHERE Imports_TA.Consumer_SSN is null

I get this error:

Server: Msg 7314, Level 16, State 1, Line 25
OLE DB provider 'BHOTextFilesCRM' does not contain
table '@strFileName'. The table either does not exist or
the current user does not have permissions on that table.

What am I missing to get the statement to recognise the
filename when a variable is used? I have tried parsing
the statement into a string and using EXEC. This runs but
the temp table is not available or found.

Thanks,
Cammy

Relevant Pages

  • Re: SQL linked server with ODBC
    ... and then run the query. ... I am having trouble extracting data from a Navision ... I am using sql server 2000. ... I created a linked server using OLE DB provider ...
    (microsoft.public.sqlserver.odbc)
  • Re: How to tell what index tags are actually used
    ... "tom knauf" wrote in message ... > for it to prevent sideeffects, just produce a textfile. ... >> some tables for which over 50 index tags have been created and I'm ... >> complains that their report or query has gotten really slow but I would ...
    (microsoft.public.fox.programmer.exchange)
  • RE: sp_runwebtask remote access error
    ... Did you find out if there was a bug for this issue? ... >and that prevents a linked server query from being called ... >Vikrant Dalwale ...
    (microsoft.public.sqlserver.security)
  • Re: Error with Linked Servers
    ... is it large set data you want to return? ... But the problem isn't so much in the query it's the fact that when you ... select the linked server the system hangs with no error message and the ... the session hangs and no error message is displayed. ...
    (microsoft.public.sqlserver.security)
  • Re: problem with Execute SQL Task
    ... I found out that the problem for the failure is due to a linked server query ... i did in my batch query. ... If this DTS package is run manually it works but when i schedule it to run ...
    (microsoft.public.sqlserver.dts)