while using "EXEC xp_cmdshell net use" in sql procedure how to handle exception

Question

 BEGIN TRY
     EXEC xp_cmdshell "net use Z: \\10.44.111.124\dpsshare Demo@123 /user:balamuralirc /persistent:yes"
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber
     ,ERROR_SEVERITY() AS ErrorSeverity
     ,ERROR_STATE() AS ErrorState
     ,ERROR_PROCEDURE() AS ErrorProcedure
     ,ERROR_LINE() AS ErrorLine
     ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH

While using this above try catch it's not working is there any other way to achieve this.

What do you mean it's not working? Are you trying to capture an error from a failed `net use`? What you are doing is a very bad idea. What are you really trying to do? Are you trying to allow your SQL server to access a file from a share under different credentials? As far as `xp_cmdshell` is concerned, there is no error and it has run successfully — Nick.Mc, Jun 22 '18 at 06:36
If you must do this, which I strongly advise you not to, have a read of this: http://www.sqlservercentral.com/blogs/steve_jones/2010/10/11/return-values-from-xp_5F00_cmdshell/. It looks like you just need to grab the return value from the proc — Nick.Mc, Jun 22 '18 at 06:39
yes, am going to create and write a file to that network Shared folder. — Manu C Rajan, Jun 22 '18 at 07:54
Why and how are you doing that from SQL Server? You should just grant the SQL Server service account the correct rights rather than using xp_cmdshell, let alone hard coding clear text passwords — Nick.Mc, Jun 22 '18 at 10:48
hey for example I haveposted this one actual scenario I will fetch from a table and run. — Manu C Rajan, Jun 22 '18 at 11:44
Are you saying you will fetch the clear text password from a table? It's a better idea to utilise the service account rather than maintaining lots of logins and passwords and using `xp_cmdshell` — Nick.Mc, Jun 22 '18 at 12:11
dear nick my issue is while we make a connection if the connection is successful its ok, but if the connection is not successful I need to catch the error and need to return the error. — Manu C Rajan, Jun 22 '18 at 12:47
The answer below lets you capture the text output. You are doing something very wrong here. Are you writing a file or making a connection? If you want to make a file on a remote host, do it using the correct tool - that would probably be SSIS, not `xp_cmdshell` — Nick.Mc, Jun 22 '18 at 12:51

score 1 · Answer 1 · answered Jun 22 '18 at 08:04

My suggestion is just use the returned text to evaluate the success: If the thought of temp tables upsets you, just use a table variable instead.

create table #output (line nvarchar(255) null)
insert #output (line) EXEC xp_cmdshell "net use Z: \\10.44.111.124\dpsshare Demo@123 /user:balamuralirc /persistent:yes"
IF  EXISTS (SELECT * from #output WHERE line LIKE '%System error%' OR line LIKE '%not found%')
BEGIN
    PRINT 'Failure'
END
ELSE 
BEGIN
    PRINT 'Success'
END

drop table #output

while using "EXEC xp_cmdshell net use" in sql procedure how to handle exception

1 Answers1