here for a quick overview of the site Help Center Detailed answers have a peek at this web-site to any questions you might have Meta Discuss the xp_cmdshell no_output workings and policies of this site About Us Learn more about xp_cmdshell return value Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions
Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.3 million programmers, just like you, helping each other.
Join them; it only takes a minute: Sign up Catching Error Message from XP_CMDSHELL up vote 0 down vote favorite I am running the following command: EXEC @ReturnCode = master.dbo.xp_cmdshell @cmdline On the Results tab I get 2 lines Could not find a part of the path '\server\directory\filename'. NULL How do I capture the first line in an error xp_cmdshell suppress output message? I tried using a Try Catch block with "SELECT @ErrorMessage = ERROR_MESSAGE()" and it doesn't grab it. The message is not coming from sys.messages. Where is this error message coming from then? sql tsql share|improve this question asked Sep 22 '10 at 14:56 User4432432432 2122618 add a comment| 1 Answer 1 active oldest votes up vote 2 down vote accepted The error comes from the command shell itself, it is not a SQL Server error one way to grab the error is declare @cmdline varchar(500),@ReturnCode int select @cmdline = 'dir f:' create table #temp (SomeCol varchar(500)) insert #temp EXEC @ReturnCode = master.dbo.xp_cmdshell @cmdline if @ReturnCode <> 0 select * from #temp where SomeCol is not null but of course if you do DIR C: the table will be filled with all the files and folders from that command share|improve this answer answered Sep 22 '10 at 15:02 SQLMenace 92.9k20151193 Thanks @SQLMenace! –Toby Aug 14 '12 at 12:10 With this technique I was able to suppress any output from the c
here for a quick overview of the site Help Center Detailed answers Source to any questions you might have Meta Discuss the
workings and policies of this site About Us Learn more about xp_cmdshell example Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions xp_cmdshell permissions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.3 million programmers, just like you, helping each other. http://stackoverflow.com/questions/3770548/catching-error-message-from-xp-cmdshell Join them; it only takes a minute: Sign up Get Results from XP_CMDSHELL up vote 12 down vote favorite 2 I have been searching the web some and it seems like the only way to get the results from XP_CMDSHELL is to store them into a temp table. Is there really no easier way? From Experts Exchange: No, xp_cmdshell http://stackoverflow.com/questions/9501192/get-results-from-xp-cmdshell will not return any information from the exe. and you have to use the following syntax if you are not in the master database to run it. master..xp_cmdshell. You will have to give your user permission to execute this procedure in the master database. You will have to have your exe insert the information its self because it can not return information to the process that called it. And... While @result only gets the return value from xp_cmdshell, you may be able to capture the results of the command by inserting directly into a table... something like this: ymmv... set nocount on declare @filepath varchar(255), @cmd varchar(255), @rc int select @filepath = 'c:\temp\' select @cmd = 'dir ' + @filepath + '~*.tmp' create table #output (output varchar(255) null) insert #output exec @rc = master..xp_cmdshell @cmd select * from #output where output is not null drop table #output sql-server stored-procedures xp-cmdshell share|improve this question edited Feb 29 '12 at 16:28 asked Feb 29 '12 at 14:47 Saint Ronin 1011311 add a comment| 2 Answers 2 ac
up Recent PostsRecent Posts Popular TopicsPopular Topics Home Search Members Calendar Who's On Home » have a peek here SQL Server 2005 » SQL Server 2005 Integration Services » http://www.sqlservercentral.com/Forums/Topic520698-148-1.aspx capturing errors from xp_cmdshell capturing errors from xp_cmdshell Rate Topic Display Mode Topic Options Author Message jkoon-760461jkoon-760461 Posted Friday, June 20, 2008 7:59 AM Right there with Babe Group: General Forum Members Last Login: Thursday, October 23, 2014 10:20 AM Points: 787, Visits: 360 I have a old dts package that we converted to SSIS with minimal changes. I don't know if dts was able to capture any errors previously, xp_cmdshell output but SSIS is definitely not...The purpose of the step below is to create a request file for FTP based on the data stored in a table. The first cmdshell executes a stored procedure that queries a table and creates an output file. The second cmdshell executes an ftp with the file created in the first one. No matter what I do I can't get the SSIS step to fail. I think Capture Errors From it is because once the control passes to xp_cmdshell, SSIS considers it a success. How can I get the SSIS step to capture any error code the cmdshell receives and act on it?declare @my_day as intdeclare @my_query1 varchar(1000)declare @my_query2 varchar(1000)select @my_query2 = 'osql -E -h-1 -d[database] -S[server] -Q "execute [some procedure]" -o "c:[some results]" -w500'exec @RC = master..xp_cmdshell @my_query2exec master..xp_cmdshell 'c:[file created by above shell]' Post #520698 KenpoDBAKenpoDBA Posted Friday, June 20, 2008 8:47 AM SSC-Addicted Group: General Forum Members Last Login: Friday, October 14, 2016 2:22 PM Points: 433, Visits: 595 I can't imagine why you'd be doing it like this anyway. The ways you were forced to do things in DTS aren't the ways you'd choose to do them in SSIS.Instead of the process you've got, you should be using an oledb data source to put the data into the file, and either an execute process task or ftp task for the next step. Watch my free SQL Server Tutorials at:http://MidnightDBA.com Blog Author of:DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE: Post #520771 jkoon-760461jkoon-760461 Posted Friday, June 20, 2008 10:46 AM Right there with Babe Group: General Forum Members Last Login: Thursday, October 23, 2014 10:20 AM Points: 787, Visits: 360 Thanks for the pointer. Splitting into two steps