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

[anamika]

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??

Thanks

[Gary X]

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

Gary


"anamika" <anamikas@mindfiresolutions.com> wrote in message
news:d9c65892.0307012216.4e6e902f@posting.google.c om...

> 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??
>
> Thanks