Solved

SQL 2008 | errors from xp_cmdshell execution

Posted on 2012-03-28
3
907 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 500 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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Union 2 queries to a cte (temp table perhaps) 9 49
SQL DATEADD 10 79
SSRS 2013 - Finding Datasets/StoredProcedures 4 32
How can I find this data? 3 23
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

740 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