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

Bulk insert is all or nothing?

Hi, I ran query 'Bulk insert mytable from myFile with (FIRE_TRIGGERS)'.

The problems are,
1.It does nothing if exist duplication key records. There is no option to skip the duplications. What else is possible to skip duplicated insertion and go next record when bulk insert?

2. The triggers are not fired even if bulk insert success.

My goal is that very large data should be loaded at once and use trigger.

The trigger is fired after bulk insert executed.
But, still remained some problems.
The result of executing trigger is affected only the last record of source data file. It seems that the trigger definition has logical or symantical problems. Any one help is very appreciated.

Here is my definition of trigger.
Can I control all of the inserted recorcd in trigger definition?

create table myTable
(
id int,
value1 int,
value2 int,
primary key(id)
);

create table myTempTable
(
id int,
value1 int,
value2 int
);

CREATE TRIGGER mytrigger ON myTempTable
INSTEAD OF INSERT
AS
BEGIN
PRINT 'trigger mytrigger'
DECLARE @.id AS int, @.value1 AS int, @.value2 AS int
SELECT @.id = INSERTED.id,
@.value1 = INSERTED.value1,
@.value2 = INSERTED.value2
FROM INSERTED

PRINT @.id
PRINT @.value1
PRINT @.value2

IF EXISTS(SELECT * FROM myTable WHERE id = @.id)
BEGIN
PRINT 'trigger EXISTS'
PRINT 'UPDATE'
UPDATE myTable SET value = value + @.value,outbKbps =outbKbps + @.outbKbps WHERE id=@.id
END
ELSE
BEGIN
PRINT 'trigger NOT EXISTS'
PRINT 'trigger INSERT'
INSERT INTO myTableVALUES(@.id,@.value1,@.value2)
END
END
GO

bulk insert myTempTable from 'c:\bulk_myTable.dat' with (FIRE_TRIGGERS);

-- bulk_myTable.dat file's contents --
1 100 200
1 100 200
1 100 200
1 100 200
1 100 200

From Books Online, topic: "Using bcp and BULK INSERT"

Triggers are fired once for each batch in the bulk copy operation. The inserted table passed to the trigger contains all of the rows inserted by the batch. Specify FIRE_TRIGGERS only when bulk copying into a table with INSERT and INSTEAD OF triggers that support multiple row inserts.

Your trigger fires only one time for the entire batch -not for each row in the batch.

Your trigger 'assumes' there is, and is designed to handle, only one row in the inserted table, and therefore will NOT do as you desire. It couldn't possibly work for a bulk insert of more than one row.

You should 're-design' the trigger to handle multiple rows at a time. If you provide, in a new posting, the table DDL, some sample data in the form of insert statements, and a statement of what you are attempting to accomplish, perhaps some here can help you create a trigger that will work for you.

Hopefully, this answered your question.

|||

I solved the number of 2 from my question in this thread.

I use the 'AFTER' instead of 'INSTEAD OF' trigger for getting the bulk insert result record.
And, in trigger definition, using the cursor, get the recordset that I want to get.
In the end, insert or update to the target table record by record.
I got this as hint from your commant. Thank you very much.

Here is the sample.

CREATE TRIGGER TRG_MY_TABLE ON MyTempTable
AFTER INSERT
AS
BEGIN

DECLARE @.id AS INT,
@.value1 AS INT,
@.value2 AS INT

DECLARE CursorMyTable CURSOR FOR
SELECT id AS cur_id, value1 AS cur_value1, value2 AS cur_value2
FROM MyTempTable

OPEN CursorMyTable

FETCH NEXT FROM CursorMyTable
INTO @.id,@.value1,@.value2

-
IF EXISTS(SELECT * FROM MyTable WHERE id=@.id)
BEGIN
UPDATE MyTable
SET value1= value1+ @.value1,
value2= value2+ @.value2
WHERE id=@.id

END
ELSE
BEGIN
INSERT INTO MyTable VALUES(@.id,@.value1,@.value2)
END
-

WHILE @.@.FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM CursorMyTable
INTO @.id,@.value1,@.value2

IF EXISTS(SELECT * FROM MyTable WHERE id=@.id)
BEGIN
UPDATE MyTable
SET value1= value1+ @.value1,
value2= value2+ @.value2
WHERE id=@.id

END
ELSE
BEGIN
INSERT INTO MyTable VALUES(@.id,@.value1,@.value2)
END
-

END

CLOSE CursorMyTable

DEALLOCATE CursorMyTable
END

|||

I would highly recommend NOT using a CURSOR in a TRIGGER.

It would be far better to load the data into a 'Staging' table, and then process the data as needed. Stepping through a CURSOR in the context of a TRIGGER (meaning during a TRANSACTION) will hold a lot of locks and resources -most likely, unnecessarily.

Just not a good idea.

|||Thank you, but could you more explain about the 'Staging' table?|||

A 'Staging' table most likely would have the same columns as the final production table -but datatypes may be different. Data is loaded into the Staging table, and then cleansed, conformed, and moved to the production table.

This is a very common ETL operation.

Bulk Insert error file does not exist

Hello

I am trying to bulk insert a text file into SQL 2005 table. When I execute the bulk insert I get the error

"Msg 4860, Level 16, State 1, Line 1. Cannot bulk load. The file "\\ENDUSER-SQL\EnduserText\B1020063.txt" does not exist."

The text file that it is saying does not exist I recently created thru my code. I can open the file but only when I rename the file will the Bulk Insert work. After creating the text file I am moving it to the server that SQL server is running on. Also if I run sp_FileExists it also says the file does not exist unless again I rename the file then this stored procedure recognizes the file. I dont' know if I have a permission issue or what is the problem. Any help would be appreiated.

Thanks

Chris

What is your bcp command? What is the original and new name of the file?|||This is always a rights or login issue.

How are you "running" the bulk insert, is it a job or a dts or a query or what? Is it "Bulk Insert" or "BCP.EXE".

Assuming it is Bulk Insert, like you said, it is running on the SERVER as the logged in user. Can the server access \\ENDUSER-SQL...?

What are you "renaming" the file too? Are you actually moving the file to another location or just renaming it? I don't see anything wrong with the name.|||

I am running the bulk insert from a query. The original file name is B120049.txt, I then rename the file to say B120049BLK.txt save it then the bulk insert runs fine.

Thanks

Chris

|||

I am running the bulk insert in a query

BULK INSERT B1020063 FROM '\\ENDUSER-SQL\EnduserText\B1020063.txt' WITH (FIELDTERMINATOR = '|')

ENDUSER-SQL is the server that MS SQL 2005 is running on. I am running the query thru my Delphi 2005 code using ADO components. What I am doing is running my Delphi code to create the text file from a paradox table (don't laugh) then trying to use the bulk insert to take the text file into SQL table. I am running the code on my desktop and creating the text file on a shared folder on the SQL Server (\\ENDUSER-SQL\EnduserText\..)

When I rename the file I am taking B1020063.txt renaming to B1020063BLK.txt and it works. Also if after saving the file once I save it back to orginal name it also works. I am not moving the file from the orginal location just changing the name.

Thanks for the help.

Chris

|||BULK INSERT will be running from the server, so it doesn't matter where you ran it from.

Try changing the file name to the actual physical directory "C:\EndUser...." or whatever. See if that works.

It has to be a rights issue to the file. When you rename it, it is getting your rights, then running as you so it works.|||Do an xp_cmdshell 'dir \\ENDUSER-SQL\EnduserText' to see if SQL Server can access the folder.|||

I tried renaming the file to C:EnduserText\B1020049.txt and go the same results. Any ideas on how I can change the rights on the file so I can use it without having to renam it?

Thanks

Chis

|||

I ran this stored procedure and here are the results :

Volume in drive \\ENDUSER-SQL\EnduserText has no label.
Volume Serial Number is 547D-E293
NULL
Directory of \\ENDUSER-SQL\EnduserText
NULL
10/29/2006 01:32 PM <DIR> .
10/29/2006 01:32 PM <DIR> ..
10/27/2006 03:27 PM 77,087 AcctRpts.txt
10/27/2006 03:27 PM 826,363 AcctSql.txt
10/27/2006 03:27 PM 217 AcctTypes.txt
10/27/2006 03:27 PM 121 AcctTypes2.txt
10/27/2006 03:27 PM 1,411,007 Address.txt
10/27/2006 03:27 PM 7,350 Addrlink.txt
10/27/2006 03:27 PM 185,409 AllFeat.txt
10/27/2006 03:27 PM 574 Allocation.txt
10/27/2006 03:27 PM 13,954 ANPI.txt
10/27/2006 03:27 PM 617 AREACODE.txt
10/27/2006 03:27 PM 1,882 ARSETUP.txt
10/27/2006 03:27 PM 3,613 B1020049.txt
10/27/2006 03:27 PM 7,243 B1020063.txt
10/27/2006 03:30 PM 50,497,983 B1120022.txt
10/27/2006 03:32 PM 46,926,455 B1120023.txt
10/27/2006 03:35 PM 54,188,893 B1120024.txt
10/27/2006 03:37 PM 50,094,853 B1120025.txt
10/27/2006 03:39 PM 54,233,552 B1120026.txt
10/27/2006 03:41 PM 54,900,297 B1120027.txt
10/27/2006 03:41 PM 72 BILCYCLE.txt
10/27/2006 03:41 PM 4,549 Billdate.txt
10/27/2006 03:41 PM 0 BILLERR.txt
10/27/2006 03:41 PM 3,765,367 BILLMAIN.txt
10/27/2006 03:41 PM 10,293 Billplan.txt
10/27/2006 03:44 PM 35,721,280 Billsum.txt
10/27/2006 03:41 PM 261,183 BILLTOBK.txt
10/27/2006 03:44 PM 152,994 Bilmesg.txt
10/27/2006 03:44 PM 2,574 BundledService.txt
10/27/2006 03:44 PM 1,759 BusCalendar.txt
10/29/2006 11:27 AM 49,507,562 C200110.txt
10/29/2006 11:29 AM 45,172,115 C200111.txt
10/29/2006 11:31 AM 47,562,230 C20017.txt
10/29/2006 11:33 AM 50,695,011 C20018.txt
10/29/2006 11:36 AM 56,648,282 C20019.txt
10/29/2006 11:38 AM 50,773,887 C20021.txt
10/29/2006 11:40 AM 46,550,340 C200210.txt
10/29/2006 11:42 AM 43,977,305 C200211.txt
10/29/2006 11:44 AM 47,850,286 C200212.txt
10/29/2006 11:46 AM 45,525,197 C20022.txt
10/29/2006 11:48 AM 41,312,655 C20023.txt
10/29/2006 11:50 AM 48,118,979 C20024.txt
10/29/2006 11:52 AM 45,851,116 C20025.txt
10/29/2006 11:54 AM 48,136,512 C20026.txt
10/29/2006 11:57 AM 47,693,599 C20027.txt
10/29/2006 11:59 AM 48,643,674 C20028.txt
10/29/2006 12:01 PM 49,560,178 C20029.txt
10/29/2006 12:03 PM 47,320,372 C20031.txt
10/29/2006 12:05 PM 44,242,516 C200310.txt
10/29/2006 12:07 PM 43,453,596 C200311.txt
10/29/2006 12:09 PM 48,399,934 C200312.txt
10/29/2006 12:11 PM 42,166,984 C20032.txt
10/29/2006 12:13 PM 42,449,679 C20033.txt
10/29/2006 12:15 PM 42,856,103 C20034.txt
10/29/2006 12:17 PM 44,666,989 C20035.txt
10/29/2006 12:19 PM 45,443,402 C20036.txt
10/29/2006 12:21 PM 47,432,232 C20037.txt
10/29/2006 12:23 PM 46,027,801 C20038.txt
10/29/2006 12:25 PM 49,730,365 C20039.txt
10/29/2006 12:27 PM 45,443,543 C20041.txt
10/29/2006 12:29 PM 44,342,955 C200410.txt
10/29/2006 12:31 PM 42,923,015 C200411.txt
10/29/2006 12:33 PM 43,391,572 C200412.txt
10/29/2006 12:35 PM 41,510,324 C20042.txt
10/29/2006 12:37 PM 45,968,110 C20043.txt
10/29/2006 12:38 PM 42,195,260 C20044.txt
10/29/2006 12:41 PM 47,489,289 C20045.txt
10/29/2006 12:43 PM 44,718,583 C20046.txt
10/29/2006 12:45 PM 47,564,502 C20047.txt
10/29/2006 12:47 PM 46,161,036 C20048.txt
10/29/2006 12:49 PM 47,575,248 C20049.txt
10/29/2006 12:51 PM 45,312,859 C20051.txt
10/29/2006 12:53 PM 44,072,464 C200510.txt
10/29/2006 12:55 PM 43,128,828 C200511.txt
10/29/2006 12:57 PM 42,414,577 C200512.txt
10/29/2006 01:34 PM 48,520,020 c20052.txt
10/29/2006 12:59 PM 42,046,826 C20053.txt
10/29/2006 01:00 PM 40,829,562 C20054.txt
10/29/2006 01:02 PM 46,261,908 C20055.txt
10/29/2006 01:04 PM 44,259,999 C20056.txt
10/29/2006 01:06 PM 44,248,580 C20057.txt
10/29/2006 01:08 PM 44,001,686 C20058.txt
10/29/2006 01:10 PM 44,345,187 C20059.txt
10/29/2006 01:12 PM 44,186,657 C20061.txt
10/29/2006 01:14 PM 30,604,652 C200610.txt
10/29/2006 01:16 PM 42,462,188 C20062.txt
10/29/2006 01:18 PM 44,203,999 C20063.txt
10/29/2006 01:20 PM 42,164,513 C20064.txt
10/29/2006 01:22 PM 51,782,537 C20065.txt
10/29/2006 01:24 PM 46,269,771 C20066.txt
10/29/2006 01:27 PM 51,892,690 C20067.txt
10/29/2006 01:29 PM 48,158,790 C20068.txt
10/29/2006 01:32 PM 50,942,048 C20069.txt
08/22/2006 04:10 AM 3,927,036 CASS060817done.txt
08/24/2006 12:32 PM 3,485 vod082406.txt
10/19/2006 12:13 PM 3,522 vod101306.txt
10/19/2006 12:13 PM 8,695 vod101606.txt
96 File(s) 3,236,402,958 bytes
2 Dir(s) 130,462,183,424 bytes free
NULL

It looks like it sees all the text files I have created.

Thanks for the help.

Chris

BULK INSERT crashes Stored Procedure ?

I'm trying to BULK INSERT from a series of files, but the files may or may
not exist.
Problem is that if a BULK INSERT command fails (because the file doesn't
exist), the next line of code in the SP is never reached!
>> Server: Msg 4860, Level 16, State 1, Line 1
>> Could not bulk insert. File 'C:\2003_05_02.txt' does not exist.
>> CODE:
>> set @.cmd = 'BULK INSERT Z_BATCH_IMP_USPS_RAW FROM '''
>> + @.path + @.fn + ''''
>> + ' WITH
>> (
>> FIELDTERMINATOR = ''|''
>> ,ROWTERMINATOR = ''\n''
>> ,TABLOCK
>> )
>> '
>> EXEC( @.cmd ) ; -- BOMBS
>> -- EXEC sp_executesql @.cmd ; -- BOMBS
Naturally I've tried setting XACT_ABORT OFF (among other various
superstitious rituals). Nothing works.
TIA,
--joe
PS,
I don't have control of this server & the current admin & users aren't very
technical... As such I want to avoid using xp_cmdshell... similarly, I don't
want to use a linked text server, and DTS is a problem b/c the file names
change along with the aforementioned administrative issues. I would like to
have something that works stand alone that can run w/out intervention and a
minimum of admin.I googled around for this problem, and it appears that "It's supposed to
work that way" is the answer. Is this the only answer, that this class of
error throws a "fatal" & immediately short-circuits further execution of the
remainder of the SP?
This has become a show stopper at the moment.
I'm trying to run through a directory of ASCII files, so having a fatal on
just one file is a poor option when there are 20 files to import. I'd use
xp_cmdshell, but its permissions seem to get turned off whenever a patch is
applied, and that doesn't guard me against a bad 0-byte ASCII file (no
CR/LF).
Is there perhaps a way to set up DTS so it handles dynamic file names when
importing from ASCII? Any suggestions?
Thanx,
-joe
PS, Just to reiterate from my previous post, I thought of using a linked
OLEDB text server, but that requires the right OLEDB driver and permissions,
along with a capable DBA watching over things (plus if the file doesn't
exist, will the OLEDB driver throw the same kind of fatal?). Using bcp in
DOS from a scheduler doesn't solve the dynamic file name problem (oh yeh,
try programming DOS batch files... it can be done, BUT!!!), and implementing
client software (perl, WSE, etc.) on the server or workstation creates
complexity & other headaches. Please, oh dear God, Why ME!!!!' Tell them
to go back to the @.#$ mainframe, hell, they were better off with 3x5
cards!!!!!!
Previous n.g. thread:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&frame=right&th=28fd4c29145420ba&seekm=OQmAG3VcBHA.1848%40tkmsftngp05#link12
thread:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=3BF9B2DC.65712B51%40drew.edu&rnum=1&prev=/groups%3Fq%3Dhandling%2Berrors%2BCould%2Bnot%2Bbulk%2Binsert.%2BFile%2Bgroup:microsoft.public.sqlserver.programming%2Bgroup:microsoft.public.sqlserver.programming%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26group%3Dmicrosoft.public.sqlserver.programming%26selm%3D3BF9B2DC.65712B51%2540drew.edu%26rnum%3D1
--
+--
To e-mail me,
replace "REPLACETHIS" with buddhashortfatguy
"buddhashortfatguy" <REPLACETHIS@.yahoo.com> wrote in message
news:W4xsb.34667$Mc.21141@.twister.austin.rr.com...
> I'm trying to BULK INSERT from a series of files, but the files may or may
> not exist.
> Problem is that if a BULK INSERT command fails (because the file doesn't
> exist), the next line of code in the SP is never reached!
> >> Server: Msg 4860, Level 16, State 1, Line 1
> >> Could not bulk insert. File 'C:\2003_05_02.txt' does not exist.
> >> CODE:
> >> set @.cmd = 'BULK INSERT Z_BATCH_IMP_USPS_RAW FROM '''
> >> + @.path + @.fn + ''''
> >> + ' WITH
> >> (
> >> FIELDTERMINATOR = ''|''
> >> ,ROWTERMINATOR = ''\n''
> >> ,TABLOCK
> >> )
> >> '
> >> EXEC( @.cmd ) ; -- BOMBS
> >> -- EXEC sp_executesql @.cmd ; -- BOMBS
>
> Naturally I've tried setting XACT_ABORT OFF (among other various
> superstitious rituals). Nothing works.
>
> TIA,
> --joe
> PS,
> I don't have control of this server & the current admin & users aren't
very
> technical... As such I want to avoid using xp_cmdshell... similarly, I
don't
> want to use a linked text server, and DTS is a problem b/c the file names
> change along with the aforementioned administrative issues. I would like
to
> have something that works stand alone that can run w/out intervention and
a
> minimum of admin.
>
>|||I just came up with a ghastly hack for a solution... a tad dangerous
perhaps...
In the SP, I log the file in a log table before I run the BULK INSERT. The
SP checks in the log table see if an attempt has been made on that file name
before, and will loop if an attempt has already been made.
I then set up a 2-step job on the server. Both steps call the same SP, and
step 1 calls step 2 on failure, and step 2 calls step 1 on failure. Until
the SP runs out of possible file names (filename is based on date in a
xxxxmmddyy.txt format), it'll keep BULK INSERTing files it finds and failing
on ones it doesn't (the SP succeeds when it can exit without error, when it
runs out of names, the maximum of which is based upon today's date).
Finally, both job steps (whichever one happens to be running at the time)
exit the job on success (which is again, running out of file names to look
for).
As I said, a ghastly hack.
+--
To e-mail me,
replace "REPLACETHIS" with buddhashortfatguy
"buddhashortfatguy" <REPLACETHIS@.yahoo.com> wrote in message
news:W4xsb.34667$Mc.21141@.twister.austin.rr.com...
> I'm trying to BULK INSERT from a series of files, but the files may or may
> not exist.
> Problem is that if a BULK INSERT command fails (because the file doesn't
> exist), the next line of code in the SP is never reached!
> >> Server: Msg 4860, Level 16, State 1, Line 1
> >> Could not bulk insert. File 'C:\2003_05_02.txt' does not exist.
> >> CODE:
> >> set @.cmd = 'BULK INSERT Z_BATCH_IMP_USPS_RAW FROM '''
> >> + @.path + @.fn + ''''
> >> + ' WITH
> >> (
> >> FIELDTERMINATOR = ''|''
> >> ,ROWTERMINATOR = ''\n''
> >> ,TABLOCK
> >> )
> >> '
> >> EXEC( @.cmd ) ; -- BOMBS
> >> -- EXEC sp_executesql @.cmd ; -- BOMBS
>
> Naturally I've tried setting XACT_ABORT OFF (among other various
> superstitious rituals). Nothing works.
>
> TIA,
> --joe
> PS,
> I don't have control of this server & the current admin & users aren't
very
> technical... As such I want to avoid using xp_cmdshell... similarly, I
don't
> want to use a linked text server, and DTS is a problem b/c the file names
> change along with the aforementioned administrative issues. I would like
to
> have something that works stand alone that can run w/out intervention and
a
> minimum of admin.
>
>|||Before you start using Bulk Insert, can you confirm if the file really exists , you might want to use the command "xp_fileexist"|||"Prasanna" <anonymous@.discussions.microsoft.com> wrote in message
news:D1E87B91-DD63-4B62-BC27-1DC437CAB343@.microsoft.com...
> Before you start using Bulk Insert, can you confirm if the file really
exists , you might want to use the command "xp_fileexist"
No such xp_ on their machine, although xp_cmdshell is there, and worked at
one point in time. Plus, the admin situation is problematic such that xp_*
priveleges are revoked when service packs get applied.
This has to work w/out any special priveleges or assignments, just a job
that works reliably (nightly) w/out user intervention.
Even so, if the ASCII files are 0-Byte or formatted badly in any regard,
BULK INSERT will also make a T-SQL stored proc bomb out w/ a fatal, so even
if I knew the file existed, it doesn't protect the batch process from
interruption.
I *did* find a workaround to this problem. See my last reply to myself in
the thread. It's too bad that MS never got around to fixing these issues
(poor T-SQL error handling) until Longhorn.
Thanks,|||just a fyi, enhanced error handling has nothing to do with longhorn. it's to
do with yukon.
--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net
"buddhashortfatguy" <REPLACETHIS@.yahoo.com> wrote in message
news:HlFsb.36901$Mc.28999@.twister.austin.rr.com...
> "Prasanna" <anonymous@.discussions.microsoft.com> wrote in message
> news:D1E87B91-DD63-4B62-BC27-1DC437CAB343@.microsoft.com...
> > Before you start using Bulk Insert, can you confirm if the file really
> exists , you might want to use the command "xp_fileexist"
> No such xp_ on their machine, although xp_cmdshell is there, and worked at
> one point in time. Plus, the admin situation is problematic such that xp_*
> priveleges are revoked when service packs get applied.
> This has to work w/out any special priveleges or assignments, just a job
> that works reliably (nightly) w/out user intervention.
> Even so, if the ASCII files are 0-Byte or formatted badly in any regard,
> BULK INSERT will also make a T-SQL stored proc bomb out w/ a fatal, so
even
> if I knew the file existed, it doesn't protect the batch process from
> interruption.
> I *did* find a workaround to this problem. See my last reply to myself in
> the thread. It's too bad that MS never got around to fixing these issues
> (poor T-SQL error handling) until Longhorn.
> Thanks,
>
>|||ahhhh, you are my oj in the mourning... and yukon correct me any time you
want when i get the code names for MS products mixed up
--
+--
To e-mail me,
replace "REPLACETHIS" with buddhashortfatguy
"oj" <nospam_ojngo@.home.com> wrote in message
news:e3vmQWbqDHA.2496@.TK2MSFTNGP09.phx.gbl...
> just a fyi, enhanced error handling has nothing to do with longhorn. it's
to
> do with yukon.
> --
> -oj
> RAC v2.2 & QALite!
> http://www.rac4sql.net
>
> "buddhashortfatguy" <REPLACETHIS@.yahoo.com> wrote in message
> news:HlFsb.36901$Mc.28999@.twister.austin.rr.com...
> > "Prasanna" <anonymous@.discussions.microsoft.com> wrote in message
> > news:D1E87B91-DD63-4B62-BC27-1DC437CAB343@.microsoft.com...
> > > Before you start using Bulk Insert, can you confirm if the file really
> > exists , you might want to use the command "xp_fileexist"
> >
> > No such xp_ on their machine, although xp_cmdshell is there, and worked
at
> > one point in time. Plus, the admin situation is problematic such that
xp_*
> > priveleges are revoked when service packs get applied.
> >
> > This has to work w/out any special priveleges or assignments, just a job
> > that works reliably (nightly) w/out user intervention.
> >
> > Even so, if the ASCII files are 0-Byte or formatted badly in any regard,
> > BULK INSERT will also make a T-SQL stored proc bomb out w/ a fatal, so
> even
> > if I knew the file existed, it doesn't protect the batch process from
> > interruption.
> >
> > I *did* find a workaround to this problem. See my last reply to myself
in
> > the thread. It's too bad that MS never got around to fixing these issues
> > (poor T-SQL error handling) until Longhorn.
> >
> > Thanks,
> >
> >
> >
>|||The SP "xp_fileexist" exists in Master db, not in the User DB.|||Thanks, I gather that it's new in SS2K?
It's not listed in the SS2K BOL in the T-SQL ref. Doesn't require additional
rights, great!
It still doesn't solve the problem with any other kind of fatal error in
BULK INSERT (a 0-byte file, for instance).
The bottom line is that MS SQL 7's & 8's error handling isn't very capable
and I'll still have to either go w/ a hacked workaround in the job manager
or a workaround in client software (Perl, WSE...).
What really makes me wonder is, why in the first place was BULK INSERT
implemented to throw fatal errors *at all?* It's an import utility, that's
all it is - it's not a table reference that is the subject of
latent/deferred binding. Makes me wonder about the underpinning API, the
(familiar) scent of old Sybase code lurking under the hood.
"Prasanna" <anonymous@.discussions.microsoft.com> wrote in message
news:8AD1A570-60F4-48CF-B368-73369419CC27@.microsoft.com...
> The SP "xp_fileexist" exists in Master db, not in the User DB.|||Just to let you know you're not alone,
I posted this a few hours before you.
http://communities.microsoft.com/newsgroups/previewFrame.asp?ICP=msdn&sLCID=
us&sgroupURL=microsoft.public.sqlserver.programming&sMessageID=%253CuaBAWQSq
DHA.1884@.TK2MSFTNGP09.phx.gbl%253E.
At the moment I'm using this technic: in the sp that needs to bulk insert I
assign the text of the format file to a variable and create the file on the
fly (using a wrapper to the file system object), something like this:
/*********/
declare @.txt varchar(8000)
declare @.FormatFilePath varchar(255)
declare @.path varchar(255)
set @.txt='8.0
1
1 SQLCHAR 0 0 "\r\n" 2 Code ""
'
select @.path='Target.dat', @.FormatFilePath='c\winnt\temp\Target.fmt'
exec @.Res=dbo.st_WriteToFile @.FormatFilePath, @.txt --This is a FSO wrapper
that creates or overwrites a file with the specified text
if @.Res<>0
goto ErrorHandler
create table Target(
PKCheck int Identity(1,1),
Code char(15)
)
exec(N'bulk insert Target from '''
+ @.path
+ N''' with (FORMATFILE='''
+ @.TempDirPath
+ N''', TABLOCK)')
/**********/
It works fine, but of course if the data file or the table change their
structures you have to modify the sp, and you can't use third parties format
file without checking for their correctness.
I' was trying to generalize this approch by adding error controls when I
stumbled into your same problem.
Now I'm thinking of using BCP's format option via xp_cmdshell to produce a
valid format file, but of course I have to take into account the cases when
the number of table fields and file fields and their mutual positions
differ, but the problem of using format files not created by you remains.
Still working on.
Salvor
--
++++++++++++++
To e-mail me,
remove ".NO_SPAM" from my e-mail address