Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL 2008 | errors from xp_cmdshell execution

Posted on 2012-03-28
3
Medium Priority
?
952 Views
Last Modified: 2012-03-28
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
0
Comment
Question by:crazywolf2010
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 9

Expert Comment

by:rajeevnandanmishra
ID: 37777065
Hi,
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
-------
0
 

Author Comment

by:crazywolf2010
ID: 37777107
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
0
 
LVL 9

Accepted Solution

by:
rajeevnandanmishra earned 2000 total points
ID: 37777225
Yes, you can get the output/error text of xp_cmdshell.

declare @retValue int
create table #Temp1(OutputText varchar(100))
insert into #Temp1
exec @retValue = xp_cmdshell 'bcp "SELECT * FROM test1 " queryout "C:\test1.txt" -T -c -t,'

if @i <> 0
begin
      raiserror 99997'error'
                select * from #Temp1
end
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question