Professional Web Applications Themes

BCP query out executed by xp_cmdshell works fine from query yzer but fails from VB Component - Microsoft SQL / MS SQL Server

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 yzer it works fine but from component it fails. What could be the reason behind My code is CREATE PROCEDURE ParishPay_GenerateSouthDataCSV ( return_value ...

  1. #1

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

    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 yzer 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
    anamika Guest

  2. #2

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

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

    Gary


    "anamika" <anamikasmindfiresolutions.com> wrote in message
    news:d9c65892.0307012216.4e6e902fposting.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 yzer 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 Guest

Similar Threads

  1. CFSTOREDPROC to Oracle on Linux fails - same query onWin2003 works
    By --bill-- in forum Coldfusion Database Access
    Replies: 0
    Last Post: March 8th, 08:07 PM
  2. Query works in fine CF5 but not CFMX
    By rgalla41 in forum Macromedia ColdFusion
    Replies: 0
    Last Post: March 8th, 07:27 PM
  3. Replies: 1
    Last Post: May 31st, 08:56 PM
  4. Replies: 9
    Last Post: October 24th, 02:25 PM
  5. Generate Table script from query yzer...
    By Andrew J. Kelly in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 10th, 12:08 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139