Bulk Insert won't compile if file doesn't exist, no Try Catch either

I first noticed that the try/catch did not seem to work when the text file did not exist and I tried to execute this block of code from a stored proc.

Then, when I tried to compile the proc using a file that did not exist at the time of compilation, I can't even get it to compile.

If I compile, then remove the file, the try/catch does not work.

TRUNCATE TABLE LoadDaily;

BEGIN TRY

BULK INSERT LoadDaily
FROM 'c:\temp\daily.txt'
WITH
(
BATCHSIZE = 100,
FIELDTERMINATOR = '|',
ROWTERMINATOR = '0x0A',
FIRSTROW = 2
);

END TRY
BEGIN CATCH

SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;

END CATCH

Instead of saying "compile" I mean that I can't use either ALTER PROCEDURE or CREATE PROCEDURE when the text file does not exist.

Fortunately, using EXEC works and the try/catch works as directed.

This will be run from a service so I don't think I will have any permissions issues.

I tested in SQL 2000 and did not have any problems editing the SP using EM with a non-existant text file.

It does not work if the format file is missing either.
TRUNCATE TABLE LoadDaily;

BEGIN TRY

DECLARE @.sql nvarchar(300), @.Fname nvarchar(300)

SET @.Fname = 'c:\_temp\daily.txt'

SET @.sql = 'BULK INSERT LoadDaily FROM ''' + @.Fname + ''''
+ ' WITH '
+ ' ( '
+ ' BATCHSIZE = 100, '
+ ' FIELDTERMINATOR = ''|'', '
+ ' ROWTERMINATOR = ''0x0A'', '
+ ' FIRSTROW = 2'
+ ' );'

EXEC (@.sql)

END TRY
BEGIN CATCH

SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;

END CATCH

|||This is a known limitation. TRY...CATCH cannot catch all errors. Some of the errors that happen at compile time can be caught only in the outer TRY..CATCH. This is what you kind of achieved by using dynamic SQL for the BULK INSERT. Please see the TRY...CATCH topic in BOL for more details.|||Sorry, this is a level 16 error so it should be caught by definition (BOL says the range is 10 to 20).

I think I'm more concerned about the regression error. I may be doing something wrong because the environment has changed but I found no way to create the Stored Procedure if the the text file does not exist. I went back and tested it in SQL 2000 and it worked fine.

Since there is now "run as" functionality with EXECUTE, it won't be a big deal but the issue should be addressed.

The new OPENROWSET has the same problem for the text file and the format file.|||Not all errors of a particular severity can be caught. In your examples, these errors are raised at compile time so there is no code execution involved at all for the TRY...CATCH to work. OPENROWSET(BULK) and BULK INSERT pretty much uses the same infrastructure so both will error out if the data file/format file is invalid or missing.|||

OK, let's forget the Try/Catch issue for now.

Just see if you can compile the test proc below in SQL 2005 when daily.txt does NOT exist.

I just found an interesting side effect: Make sure that LoadDaily is a valid table. If LoadDaily is invalid, the code WILL compile.

Now that's really weird.

CREATE PROCEDURE test
AS

BEGIN
BULK INSERT LoadDaily
FROM 'c:\temp\daily.txt'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n'
);
END
GO

|||The reason is deferred name resolution. If any object is missing at the time of creation of a stored procedure then the statement is not compiled or parsed completely (note that the syntax is still validated) and deferred until run-time or execution. This explains the behavior you are seeing. This is also true for say references to temporary tables - this is the reason why you can reference temporary tables that are not created in a SP and call it later from another that creates the temporary table or a trigger referencing a temporary table created by a stored procedure that issues the insert/update/delete statement. This is a behavior that we introduced from SQL Server 7.0. Note that this can lead to performance issues due to recompilation of the entire stored procedure at run-time when it hits the statement. This is alleviated to some extent with statement level recompilation in SQL Server 2005. For a more complete discussion, please refer to the whitepaper http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx.|||

Deferred name resolution really DOES explain why it worked in 2000.

It seems to be partially broken now:
If the table is VALID but the text file is NOT VALID then the proc will NOT compile.

I can't believe that this was the intention. This is simply an external text file so it should not affect compilation.

|||Unfortunately, this is a side-effect of BULK INSERT using the OPENROWSET(BULK) infrastructure internally. OPENROWSET(BULK) validates parameters at compile-time itself and hence the error. This behavior is same as other OPENROWSET commands. For example, if you try OPENROWSET with Jet provider and missing/invalid mdb file you will get same behavior in SQL Server 2000/2005. The reality is that most people will use dynamic SQL since the filenames cannot be parameterized directly so they will not hit the issue.sql