error returning by xp_cmdshell

Last Post 24 Feb 2006 10:34 AM by trans53. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
trans53
New Member
New Member

--
21 Feb 2006 02:54 PM
Hi all, question:

If xp_cmdshell failed to execute how can i handle error in this case?

i have something like this :

SET @CMD = 'bcp "' + @SQLQuery + '" queryout '+ @FileLocationNew + @FileName +
' -S ' + @ServerName + ' -U ' + @User + ' -P ' + @Password + ' -w '
exec master..xp_cmdshell @CMD, no_output

SET @Error = @@ERROR
IF (@Error <> 0)
BEGIN
SET @ErrorMsg = '***** dbo.xx_name: Error in bcp CMD:' + @CMD + '.'
GOTO ErrorHandling
END
nosepicker
New Member
New Member

--
21 Feb 2006 04:37 PM
If xp_cmdshell failed to execute, I can think of two possible reasons off the top of my head. One is that you don't have proper permissions to execute xp_cmdshell. Remove the "no_output" to see the error message if that's the case. Other reason is that one of your variables is NULL, which causes the entire "@CMD" string to be NULL. Therefore, xp_cmdshell has nothing to execute. Create a statement like "IF @CMD IS NULL ..." to handle this case.
trans53
New Member
New Member

--
24 Feb 2006 10:34 AM
Thank you,

Ok, this is what i am trying to do:

Run the following BCP command without no_output.
If destination folder not exists then i am getting the following message in QA :

SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file
NULL

How can i catch this error in the code and return from the stored procedure?

Currently when i print @@error i got 0 which assumes all correct.

Thanks


this is the code i am running from QA:

DECLARE @SQLQuery VARCHAR(1000),
@FK_BATCH_ID INT,
@CMD VARCHAR(1000),
@ServerName VARCHAR(50),
@User VARCHAR(50),
@Password VARCHAR(50),
@FileLocationNew VARCHAR(64),
@FileName VARCHAR(50)

SELECT @FK_BATCH_ID = 231
SELECT @ServerName = @@SERVERNAME
SELECT @User = 'test'
SELECT @Password = 'test'
SELECT @FileName = 'RAA_02242006181851.ach'

SELECT @FileLocationNew = '\\SERVERNAME\FOLDER\NEW\'

PRINT @ServerName
PRINT @FileLocationNew
PRINT @FileName

SET @SQLQuery = 'SELECT ENTRY_RECORD FROM TABLE WHERE FK_ID = ' +
CAST(@FK_BATCH_ID AS VARCHAR(10)) '

SET @CMD = 'bcp "' + @SQLQuery + '" queryout '+ @FileLocationNew + @FileName +
' -S ' + @ServerName + ' -U ' + @User + ' -P ' + @Password + ' -w '
exec master..xp_cmdshell @CMD --, no_output

print @@error
Text
You are not authorized to post a reply.

Acceptable Use Policy