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  SQL Server Development (2000)  how to check error file and log file from bcp

Author  Topic  susan07 Starting Member 1 Post Posted - 2009-05-27 : 03:49:21 I use bcp in to import txt file to database using SP, part of the script is as below: SET @strCMD = 'bcp DBname.dbo.' + @strTableName + ' in "' + @strDataFilePath + @strFileName + '.txt" -f"' + @strFormatFilePath + @strFileName + '.fmt" -e"' + @strErrorFilePath + @strFileName + '.err" -o"' + @strLogFilePath + @strFileName + '.log" -S' + @strServerName + ' -T' EXEC @intRetVal = master.dbo.xp_cmdshell @strCMD I generated the .log and .err file in the hard disk. The file might be corrupted, the format file is missing or some filed data type is wrong, the .log or .err file have logged the bcp operation result sth like in .log file: SQLState = 22005, NativeError = 0 Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification In .err file, there may also log sth like: #@ Row 1, Column 4: Invalid character value for cast specification @# the err file will not be 0 byte in some case, but sometimes, it is 0 and log file have sth indicating part of bcp operation got failure and skipped some records. I need send out the alert email automatically when there is problem in the bcp operation. I need send out detailed error message generated from the bcp operation. i think I need read .log and .err file each time and search keyword such as error, failure, I already have the separate email alert SP to send out the customized email. how to check the .log and .err files and decide to send out the alert email using SP, if encountered any issue? Thanks in advance.