Results 1 to 9 of 9

Thread: BCP Error

  1. #1
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107

    Unanswered: BCP Error

    Hi there,

    Does anyone know why this won't work:

    bcp "SELECT DISTINCT Label From GMS_48hrAccess..tbl_SurgerySlot" queryout "c:\Labels.txt" -c -S@@SERVERNAME

    It gives me the following error message:

    Server: Msg 170, Level 15, State 1, Procedure sproc_ExportLabels, Line 9
    Line 9: Incorrect syntax near 'bcp'.

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    I'd expect a "SQL Server does not exists or access denied" errormessage but yours even suggests an error in a procedure. I really wonder how the sp comes into play, I'd suggest you remove the @@Servername and change it into the name of the server instead. @@Servername is not (directly) available from a command-prompt.

  3. #3
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107
    Thanks , but I've already tried inserting the server name directly with/without quotation marks and it still doesn't work...I can get it to work if I do the following:

    DECLARE @cmdshell varChar(255)

    SET @cmdShell = 'bcp "SELECT DISTINCT Label From GMS_48hrAccess..tbl_SurgerySlot" queryout c:\Labels.txt -c -S' + @@SERVERNAME

    exec master..xp_cmdshell @cmdShell

    However, this only seems to work on SQL Server 2000 and I need it to work on SQL Server 6.5 as well.....hence why I was trying to get the previous to work.

  4. #4
    Join Date
    Feb 2004
    Posts
    492
    That could explain why the errormessage indicates a fault in the sp: you're running it from the sp. I have a case where @@servername is NULL btw, don't know how bad that is, but concatting a null to var/char usually results in a null var/char. The parameter -T might also help authenticating. Other than that, I don't see what's wrong but I don't know much about 6.5.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You need to indicate if it's a trusted connection -T, or supply the user name and the password...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    That's an SQL error message. BCP is intended to be executed from the Windoze command prompt, not from within Query Analyzer. Where are you trying to execute that command?

    -PatP

  7. #7
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107
    Thanks Pat, that explains it! So my xp..cmdShell example is the correct way to do it, any ideas why this wouldn't work on a 6.5 server?

  8. #8
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107
    Thanks Pat, that explains it! So my xp_cmdShell example is the correct way to do it, any ideas why this wouldn't work on a 6.5 server?

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    A lot would depend on what error the 6.5 machine coughed up when you tried to run it... For one thing, the queryout parameter wasn't supported on 6.5, and it was a lot touchier about authentication so you'd have to provide /U and /P values.

    -PatP

Posting Permissions

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