File does not exist error

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: singth_my (singth_my_at_discussions.microsoft.com)
Date: 01/24/05 

Date: Sun, 23 Jan 2005 20:17:01 -0800

I have this sql statement:

use Northwind
set @in_table = 'ship_test'
set @in_data_file = 'C:\data.txt'
Set @in_format_file = 'C:\data.fmt'

DECLARE @SQL_STRING AS [nvarchar](4000)
SET @SQL_STRING =
        'BULK INSERT ' + @in_table + '
         FROM ''@data_file''
        WITH (
                FORMATFILE=''@format_file'',
                CHECK_CONSTRAINTS
        )'

EXEC sp_ExecuteSql
        @SQL_STRING,
        N'@data_file nvarchar(200), @format_file nvarchar(200)', @in_data_file,
@in_format_file

When execute, it gives
"Server: Msg 4860, Level 16, State 1, Line 1
Could not bulk insert. File '@data_file' does not exist."

But if I use the following:

BULK INSERT ship_test
   FROM 'C:\data.txt'
   WITH
      (
               FORMATFILE='c:\data.fmt',
        CHECK_CONSTRAINTS
      )

The query will execute successfully.

Even if I change the first query to the following:

use Northwind
set @in_table = 'ship_test'
set @in_data_file = 'C:\data.txt'
Set @in_format_file = 'C:\data.fmt'

DECLARE @SQL_STRING AS [nvarchar](4000)
SET @SQL_STRING =
'BULK INSERT ' + @in_table + '
FROM ''' + @in_data_file + '''
WITH (FORMATFILE=''' + @in_format_file + ''',
CHECK_CONSTRAINTS)'

EXEC sp_ExecuteSql @SQL_STRING

The query will run successfully.

Anyone have any idea ? Is this one of the limitation of using "bulk insert"
and "sp_executesql" together ?

Relevant Pages

  • Re: Compare Substrings
    ... > query a lot of records are sent as result but i expect no ... declare @P1 bigint set @P1=NULL exec Content_Save ...
    (microsoft.public.sqlserver.server)
  • Re: Help creating a stored procedure to call from .Net
    ... > DECLARE @BackupFile varchar ... > DECLARE @query varchar ... > EXEC ... > INSERT #restoretemp EXEC ...
    (microsoft.public.dotnet.languages.vb)
  • Execute Dynamic SQL in table-valued UDF: How to (or equivalent)
    ... DECLARE @SQL_STATMENT_TX nvarchar ... EXEC ... I don't think a stored proc that returns a table will work, ... User is going to build and run a query, but I don't know what it ...
    (comp.databases.ms-sqlserver)
  • Re: Table as a variable
    ... depositing the table name from my table-list data. ... I think there is problrm with the query it should be ... exec ... DECLARE @query varchar ...
    (microsoft.public.sqlserver.clients)
  • Re: Update databases
    ... Compares if all tables in one database have analog in second ... declare @sqlStr varchar ... exec ('declare @Name sysname select @Name=name from ... -- ##CompareStr - will be used to pass comparing strings into dynamic script ...
    (microsoft.public.sqlserver.programming)