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.
| Author |
Topic |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2002-11-03 : 14:20:49
|
| Bulk Insert will issue warnings in QA when an input line has bad data.Is there any way to capture these warnings in a string variable so I can email the results back to me when the stored procedure runs after-hours?Sam |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-11-03 : 15:07:43
|
| I don't think there's a way to do it with BULK INSERT, but bcp has an option to log errors to a file. You could then have that file emailed as an attachment. |
 |
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2002-11-03 : 16:39:35
|
You could use the bulk insert DTS task, it's just as quick (because all its doing is issuing a BULK INSERT command) and you can schedule it in a job and specify an output file in the advanced tab of the job step. You can then add a step to email you if the bulk insert step fails and attach the error log. This way you've still got the benefit of BULK INSERT plus the error log.Another option since this is a stored proc it to shell out to osql when issuing the BULK INSERT and capture the error message into a temp table to do with what you will.create table #errtext(errortext varchar(255))insert #errtextexec master..xp_cmdshell 'osql -E -Q"BULK INSERT [Northwind].[dbo].[Customers2] FROM ''E:\customers.csv'' WITH (FORMATFILE = ''E:\customers.fmt'' , TABLOCK, MAXERRORS = 0 )"'select * from #errtext HTHJasper Smith Edited by - jasper_smith on 11/03/2002 16:51:55 |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2002-11-04 : 10:05:20
|
| Thanks to both of you for your support.I've retired BULK INSERT in favor of BCP.Sam |
|
|
|
|
|
|
Bulk Insert Warnings