SQL 2008 | errors from xp_cmdshell execution
Hi,
I am using code below to extract data into flat files. How can I capture any errors out of this T-sql script and raise alerts?
-- xp_cmdshell to extract data
EXEC xp_cmdshell 'bcp "SELECT * FROM test1 " queryout "C:\test1.txt" -T -c -t,'
EXEC xp_cmdshell 'bcp "SELECT * FROM test2 " queryout "C:\test2.txt" -T -c -t,'
EXEC xp_cmdshell 'bcp "SELECT * FROM test3 " queryout "C:\test3.txt" -T -c -t,'
Many Thanks
I am using code below to extract data into flat files. How can I capture any errors out of this T-sql script and raise alerts?
-- xp_cmdshell to extract data
EXEC xp_cmdshell 'bcp "SELECT * FROM test1 " queryout "C:\test1.txt" -T -c -t,'
EXEC xp_cmdshell 'bcp "SELECT * FROM test2 " queryout "C:\test2.txt" -T -c -t,'
EXEC xp_cmdshell 'bcp "SELECT * FROM test3 " queryout "C:\test3.txt" -T -c -t,'
Many Thanks
ASKER
Hi,
I do need to know the errortext and not just that error occured.
Can we copy everything which was sent within BCP process?
Thanks
I do need to know the errortext and not just that error occured.
Can we copy everything which was sent within BCP process?
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can use the return status of the xp_cmdshell procedure (like below):
------
declare @retValue int
exec @retValue = xp_cmdshell 'bcp "SELECT * FROM test1 " queryout "C:\test1.txt" -T -c -t,'
if @i <> 0
begin
raiserror 99997'error'
end
-------