Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL 2008 | errors from xp_cmdshell execution

Posted on 2012-03-28
3
Medium Priority
?
976 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
  • 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Integration Management Part 2
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

824 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