Post reply

Need help with bcp

  • January 27, 2017 at 10:16 am

    #321108

    I have created a job to export the results of a stored procedure to a folder on our shared drive but, the file doesn't get to the folder. When I run the job it finishes as a success. Any thoughts?

    DECLARE @cmd VARCHAR(2048)
    SET @cmd = 'bcp "EXEC SigHealth..spGWTG_Afib" queryout'
        +' "\\sh-share\groups\AHA.GWTG\Afib.txt"  -c -t, -T'
        +' -SLOCALHOST" '
    print @cmd
    EXEC master..xp_cmdshell @cmd, NO_OUTPUT

  • January 27, 2017 at 10:23 am

    #1925397

    Turn off the NO OUTPUT option and see if it really was a "Success".

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • January 27, 2017 at 10:31 am

    #1925400

    Jeff Moden - Friday, January 27, 2017 10:23 AM

    Turn off the NO OUTPUT option and see if it really was a "Success".

    I deleted the NO OUTPUT and it still finished with success.

  • January 27, 2017 at 3:54 pm

    #1925452

    NineIron - Friday, January 27, 2017 10:31 AM

    Jeff Moden - Friday, January 27, 2017 10:23 AM

    Turn off the NO OUTPUT option and see if it really was a "Success".

    I deleted the NO OUTPUT and it still finished with success.

    But what does the message in the previously suppressed output say now?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • January 30, 2017 at 4:20 am

    #1925562

    Jeff Moden - Friday, January 27, 2017 3:54 PM

    NineIron - Friday, January 27, 2017 10:31 AM

    Jeff Moden - Friday, January 27, 2017 10:23 AM

    Turn off the NO OUTPUT option and see if it really was a "Success".

    I deleted the NO OUTPUT and it still finished with success.

    But what does the message in the previously suppressed output say now?

    It says Success.

  • January 30, 2017 at 4:54 am

    #1925571

    I notice that your code has an unclosed double quote, a comma that looks as if it doesn't belong, and a repeated "-t" (albeit in upper case).  Could that be the reason for your issue?

    Since you're running this in a job, I can't see any value in doing it from T-SQL, only to have to shell out to a Windows command.  Why not set the step type to "Operating system (CmdExec)" and simplify your command to something like this:
    bcp "EXEC SigHealth..spGWTG_Afib" queryout "\\sh-share\groups\AHA.GWTG\Afib.txt" -c -T -SLOCALHOST

    What happens if you run your code from an SSMS query window, or my code (modified if necessary) from a command prompt?

    John

  • January 30, 2017 at 5:06 am

    #1925574

    John Mitchell-245523 - Monday, January 30, 2017 4:54 AM

    I notice that your code has an unclosed double quote, a comma that looks as if it doesn't belong, and a repeated "-t" (albeit in upper case).  Could that be the reason for your issue?

    Since you're running this in a job, I can't see any value in doing it from T-SQL, only to have to shell out to a Windows command.  Why not set the step type to "Operating system (CmdExec)" and simplify your command to something like this:
    bcp "EXEC SigHealth..spGWTG_Afib" queryout "\\sh-share\groups\AHA.GWTG\Afib.txt" -c -T -SLOCALHOST

    What happens if you run your code from an SSMS query window, or my code (modified if necessary) from a command prompt?

    John

    Good eye, John.  I missed that on the trailing edge of the server name.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • January 30, 2017 at 5:13 am

    #1925577

    John Mitchell-245523 - Monday, January 30, 2017 4:54 AM

    I notice that your code has an unclosed double quote, a comma that looks as if it doesn't belong, and a repeated "-t" (albeit in upper case).  Could that be the reason for your issue?

    Since you're running this in a job, I can't see any value in doing it from T-SQL, only to have to shell out to a Windows command.  Why not set the step type to "Operating system (CmdExec)" and simplify your command to something like this:
    bcp "EXEC SigHealth..spGWTG_Afib" queryout "\\sh-share\groups\AHA.GWTG\Afib.txt" -c -T -SLOCALHOST

    What happens if you run your code from an SSMS query window, or my code (modified if necessary) from a command prompt?

    John

    The little t is for the delimiter, in my case a comma. The big T is for trusted connection. This code works for other jobs. I just switched the stored procedure name and in this case I want the file to go to a folder on our shared drive.

  • January 30, 2017 at 5:27 am

    #1925579

    NineIron - Monday, January 30, 2017 5:13 AM

    John Mitchell-245523 - Monday, January 30, 2017 4:54 AM

    I notice that your code has an unclosed double quote, a comma that looks as if it doesn't belong, and a repeated "-t" (albeit in upper case).  Could that be the reason for your issue?

    Since you're running this in a job, I can't see any value in doing it from T-SQL, only to have to shell out to a Windows command.  Why not set the step type to "Operating system (CmdExec)" and simplify your command to something like this:
    bcp "EXEC SigHealth..spGWTG_Afib" queryout "\\sh-share\groups\AHA.GWTG\Afib.txt" -c -T -SLOCALHOST

    What happens if you run your code from an SSMS query window, or my code (modified if necessary) from a command prompt?

    John

    The little t is for the delimiter, in my case a comma. The big T is for trusted connection. This code works for other jobs. I just switched the stored procedure name and in this case I want the file to go to a folder on our shared drive.

    Understood.  John left out the little "-t" and that's probably ok because of what it defaults to.  Put it back in if you like. 

    You do have a trailing ""' on the -SLocalHost" that has no beginning '"'.  You do need to fix that.

    Also, try the following command and see what you get.  It'll let you know if the login that SQL server is using can actually see the share.  Watch the position of the double quotes when typing this.
    EXEC xp_CmdShell 'DIR "\\sh-share\groups\AHA.GWTG\Afib.txt"';

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • January 30, 2017 at 5:43 am

    #1925580

    Jeff Moden - Monday, January 30, 2017 5:27 AM

    NineIron - Monday, January 30, 2017 5:13 AM

    John Mitchell-245523 - Monday, January 30, 2017 4:54 AM

    I notice that your code has an unclosed double quote, a comma that looks as if it doesn't belong, and a repeated "-t" (albeit in upper case).  Could that be the reason for your issue?

    Since you're running this in a job, I can't see any value in doing it from T-SQL, only to have to shell out to a Windows command.  Why not set the step type to "Operating system (CmdExec)" and simplify your command to something like this:
    bcp "EXEC SigHealth..spGWTG_Afib" queryout "\\sh-share\groups\AHA.GWTG\Afib.txt" -c -T -SLOCALHOST

    What happens if you run your code from an SSMS query window, or my code (modified if necessary) from a command prompt?

    John

    The little t is for the delimiter, in my case a comma. The big T is for trusted connection. This code works for other jobs. I just switched the stored procedure name and in this case I want the file to go to a folder on our shared drive.

    Understood.  John left out the little "-t" and that's probably ok because of what it defaults to.  Put it back in if you like. 

    You do have a trailing ""' on the -SLocalHost" that has no beginning '"'.  You do need to fix that.

    Also, try the following command and see what you get.  It'll let you know if the login that SQL server is using can actually see the share.  Watch the position of the double quotes when typing this.
    EXEC xp_CmdShell 'DIR "\\sh-share\groups\AHA.GWTG\Afib.txt"';

    This finished with success but, didn't export the file.

  • January 30, 2017 at 1:03 pm

    #1925719

    The "DIR" command I posted wasn't supposed to export the file.  It was supposed to test to see if SQL Server could see the share.  Can you post the output of the command please?  It should NOT have been a "success".

    Also, please give this a try.  \
    EXEC xp_CmdShell 'DIR "\\sh-share\groups\AHA.GWTG\*.*"';

    Again, need to see the output please.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • January 31, 2017 at 4:12 am

    #1925798

    Jeff Moden - Monday, January 30, 2017 1:03 PM

    The "DIR" command I posted wasn't supposed to export the file.  It was supposed to test to see if SQL Server could see the share.  Can you post the output of the command please?  It should NOT have been a "success".

    Also, please give this a try.  \
    EXEC xp_CmdShell 'DIR "\\sh-share\groups\AHA.GWTG\*.*"';

    Again, need to see the output please.

    Jeff,
    This is a response from the network guy at my organization.
    "It turns out we can’t write directly to S: folders (\\sh-share\groups\) because the file copy is done in the context of the sqlservice account.  instead, we can schedule it to copy to SFTP and thenI’ll  add the file move to the daily file mover thing.  what timeshould the job run, and is it daily?"

    This is the error when I run your code in each case, with the file name or *.*
    Msg 2812, Level 16, State 62, Line 1Could not find stored procedure 'xp_CmdShell'.

  • January 31, 2017 at 4:17 am

    #1925802

    NineIron - Tuesday, January 31, 2017 4:12 AM

    Jeff Moden - Monday, January 30, 2017 1:03 PM

    The "DIR" command I posted wasn't supposed to export the file.  It was supposed to test to see if SQL Server could see the share.  Can you post the output of the command please?  It should NOT have been a "success".

    Also, please give this a try.  \
    EXEC xp_CmdShell 'DIR "\\sh-share\groups\AHA.GWTG\*.*"';

    Again, need to see the output please.

    Jeff,
    This is a response from the network guy at my organization.
    "It turns out we can’t write directly to S: folders (\\sh-share\groups\) because the file copy is done in the context of the sqlservice account.  instead, we can schedule it to copy to SFTP and thenI’ll  add the file move to the daily file mover thing.  what timeshould the job run, and is it daily?"

    This is the error when I run your code in each case, with the file name or *.*
    Msg 2812, Level 16, State 62, Line 1Could not find stored procedure 'xp_CmdShell'.

    Can you not give the SQL Server service account the necessary access to the share?

    Is your server case-sensitive?  That's the only reason I can think that the statement would fail - it's actually "spelt" xp_cmdshell.

    John

  • January 31, 2017 at 4:25 am

    #1925804

    John Mitchell-245523 - Tuesday, January 31, 2017 4:17 AM

    NineIron - Tuesday, January 31, 2017 4:12 AM

    Jeff Moden - Monday, January 30, 2017 1:03 PM

    The "DIR" command I posted wasn't supposed to export the file.  It was supposed to test to see if SQL Server could see the share.  Can you post the output of the command please?  It should NOT have been a "success".

    Also, please give this a try.  \
    EXEC xp_CmdShell 'DIR "\\sh-share\groups\AHA.GWTG\*.*"';

    Again, need to see the output please.

    Jeff,
    This is a response from the network guy at my organization.
    "It turns out we can’t write directly to S: folders (\\sh-share\groups\) because the file copy is done in the context of the sqlservice account.  instead, we can schedule it to copy to SFTP and thenI’ll  add the file move to the daily file mover thing.  what timeshould the job run, and is it daily?"

    This is the error when I run your code in each case, with the file name or *.*
    Msg 2812, Level 16, State 62, Line 1Could not find stored procedure 'xp_CmdShell'.

    Can you not give the SQL Server service account the necessary access to the share?

    Is your server case-sensitive?  That's the only reason I can think that the statement would fail - it's actually "spelt" xp_cmdshell.

    John

    It is actually case sensitive. I changed the case and this is the output. We export other files using \\sh-sftp\ so, I guess that is the way they want to set it up.
    Thanx for all the help.

    output
    Access is denied.
    NULL

  • January 31, 2017 at 4:29 am

    #1925807

    NineIron - Tuesday, January 31, 2017 4:25 AM

    John Mitchell-245523 - Tuesday, January 31, 2017 4:17 AM

    NineIron - Tuesday, January 31, 2017 4:12 AM

    Jeff Moden - Monday, January 30, 2017 1:03 PM

    The "DIR" command I posted wasn't supposed to export the file.  It was supposed to test to see if SQL Server could see the share.  Can you post the output of the command please?  It should NOT have been a "success".

    Also, please give this a try.  \
    EXEC xp_CmdShell 'DIR "\\sh-share\groups\AHA.GWTG\*.*"';

    Again, need to see the output please.

    Jeff,
    This is a response from the network guy at my organization.
    "It turns out we can’t write directly to S: folders (\\sh-share\groups\) because the file copy is done in the context of the sqlservice account.  instead, we can schedule it to copy to SFTP and thenI’ll  add the file move to the daily file mover thing.  what timeshould the job run, and is it daily?"

    This is the error when I run your code in each case, with the file name or *.*
    Msg 2812, Level 16, State 62, Line 1Could not find stored procedure 'xp_CmdShell'.

    Can you not give the SQL Server service account the necessary access to the share?

    Is your server case-sensitive?  That's the only reason I can think that the statement would fail - it's actually "spelt" xp_cmdshell.

    John

    It is actually case sensitive. I changed the case and this is the output. We export other files using \\sh-sftp\ so, I guess that is the way they want to set it up.
    Thanx for all the help.

    output
    Access is denied.
    NULL

    OK then - the SQL Server service account clearly don't have access.  If the network admins won't change that for you, you'll need to go with the workaround that they suggested.

    John

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply