sql server programming: BCP query out executed by xp_cmdshell works fine from query analyzer but fails from VB Component -- sql server programming

BCP query out executed by xp_cmdshell works fine from query analyzer but fails from VB Component

anamikas NO[at]SPAM mindfiresolutions.com
7/1/2003 11:16:57 PM

sql server programming:

Hi all,

I have a stored procedure which returns a vast number of record and i
have to write the output into a csv file. I'm using BCP utility to do
this and BCP utility is executed by xp_cmdshell from inside another
stored procedure.

Everything works fine from developement server but when the code was
deployed at client end on live server the code is not being executed
from component though on live server from SQL query analyzer it works
fine but from component it fails.

What could be the reason behind

My code is

CREATE PROCEDURE ParishPay_GenerateSouthDataCSV
(
@return_value int output,
@School_id int,
@session varchar(50),
@filename varchar(100)
)
AS

DECLARE @bcpCommand varchar(2000)
DECLARE @Arguments varchar(50)
DECLARE @result int

SET @Arguments = @School_id
SET @Arguments = @Arguments + ','''
SET @Arguments = @Arguments + @session + ''

SET @bcpCommand = '" bcp "exec DTMSv3MG.dbo.SouthDataPaymentCouponCSV
'
SET @bcpCommand = @bcpCommand + @Arguments + ''' " queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -U Username -P password
-S Server -c -T -t "," "'

select @bcpCommand

EXECUTE @return_value = master..xp_cmdshell @bcpCommand

SELECT @return_value as return_value

GO

I don't think there is any problem in the code as it works fine on
developement server. Even no error is logged it the sp fails then
error should be logged but what i doubt either BCP fails orelse
xp_cmdshell is not being executed in live server from component.

Please suggest me a solution. Is it a problem with permission??

Re: BCP query out executed by xp_cmdshell works fine from query analyzer but fails from VB Component

Gary X
7/2/2003 2:09:12 AM

Try to catch the error message. Most likely it's security related. Does the
user have rights to run xp_CMDShell?

Gary

[quoted text, click to view]