Continuing after an error with bulk insert....

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Continuing after an error with bulk insert....

Author

Topic

MuadDBA

628 Posts

Posted - 2004-09-09 : 09:20:39

I have a stored procedure which uses bullk insert statements to import data.

Unfortunately, some of the data contains escape characters in it, which bulk insert reads as an "unexpected EOF" amd bombs out of my procedure....even though I set maxerror = 100, 1000, or a jillion.

I want to be able to capture the fact that this has errored out and report it to the user, but I can't because it terminates the stored proc as soon as it hits the error.

Can anyone help?

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-09 : 15:52:15

Us xp_cmdshell and bcp or make the BULK INSERT a separate called sproc....

Brett

8-)

MuadDBA

628 Posts

Posted - 2004-09-09 : 16:04:26

I'll try the bulk insert as a seperate SP thing, but when I try to import these files from BCP, they just hang...I have no idea why.

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-09 : 16:46:20

I was messing around with the following..but couldn't get to add a bogus CRLF..

maybe you can just use your filr and see what happend


USE Northwind
GO

DROP TABLE myTable99
GO

CREATE PROC usp_imp_Sproc
AS
BULK INSERT myTable99 FROM 'C:\CONFIG.SYS'
RETURN
GO
 

CREATE PROC usp_Driver_Sproc
AS
DECLARE @rc int
EXEC @rc=usp_imp_Sproc
SELECT 'Return Code: ' + CONVERT(varchar(3),@rc)
GO

EXEC usp_Driver_Sproc
GO

CREATE TABLE myTable99(Col1 varchar(8000))
GO

EXEC usp_Driver_Sproc
GO

DECLARE @msg varchar(1000), @cmd varchar(8000)
SELECT  @msg = 'This is a ' + CHAR(13) + CHAR(10) + ' test'
SELECT  @cmd = 'ECHO "' + @msg + '" > C:\Table99Test.dat'
SELECT  @cmd
EXEC master..xp_cmdshell @cmd
GO

EXEC master..xp_cmdshell 'Dir c:\*.dat' 
GO

ALTER TABLE myTable99 ADD  Col2 varchar(1)
ALTER TABLE myTable99 DROP Column Col1
GO

sp_Help myTable99
GO

EXEC usp_Driver_Sproc
GO


DROP TABLE myTable99
DROP PROC usp_imp_Sproc
DROP PROC usp_Driver_Sproc
EXEC master..xp_cmdshell 'DEL c:\Table99Test.dat'
GO

Brett

8-)

MuadDBA

628 Posts

Posted - 2004-09-10 : 13:49:19

Tried it...it never gets past the bulk insert statement. It terminates there, never makes it to the return statement.

Damn.

MuadDBA

628 Posts

Posted - 2004-09-10 : 14:17:57

Sorry, should have been more specific, it boots me all the way out of the calling procedure, even. The whole process just terminates.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources