Results 1 to 5 of 5

Thread: BCP failure.

  1. #1
    Join Date
    Nov 2004
    Posts
    128

    Unanswered: BCP failure.

    SQL 2005 SP1.

    declare @exec varchar(128)
    set @exec = 'bcp [boxName\mysql2k5].adventureWorks.sales.customer format nul -T -n -f c:\customer.fmt'
    --print @exec
    exec master..xp_cmdshell @exec

    gives me:

    SQLState = 08001, NativeError = 17
    Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or access denied.
    SQLState = 01000, NativeError = 2
    Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Connect()).
    NULL

    (5 row(s) affected)


    It's my local PC, that I am local admin on. I did the same thing (different DB of course) on a local 2000 instance and got the same result. It's been a few years since I used BCP, and this isn't ringing a bell. Can someone please assist?


    TIA, cfr

  2. #2
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    i have observed (in SQL 2K) that bcp does not accept "[" & "]" in name qualifier. i suggest that u remove that part and use -S"boxName\mysql2k5" syntax instead.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Check in the Client Network Configuration (should be in the SQL Server program group) to see if Shared Memory protocol is enabled.

  4. #4
    Join Date
    Nov 2004
    Posts
    128
    Doh!!!!

    I just read BOL (which I obviously should have done first) and the server name isn't even part of the syntax (where I was trying to use it).



    bcp {[[database_name.][owner].]{table_name | view_name} | "query"}
    {in | out | queryout | format} data_file
    [-m max_errors] [-f format_file] [-e err_file]
    [-F first_row] [-L last_row] [-b batch_size]
    [-n] [-c] [-w] [-N] [-V (60 | 65 | 70)] [-6]
    [-q] [-C code_page] [-t field_term] [-r row_term]
    [-i input_file] [-o output_file] [-a packet_size]
    [-S server_name[\instance_name]] [-U login_id] [-P password]
    [-T] [-v] [-R] [-k] [-E] [-h "hint [,...n]"]
    Last edited by cfr; 09-07-06 at 19:19.

  5. #5
    Join Date
    Nov 2004
    Posts
    128
    To further clarify, it would appear that the ServerName\Instance name MUST be used on a named instance.

    /*Done on a default instance, this works fine with no server\instance name*/
    declare @exec varchar(128)
    set @exec = 'bcp aps_prod_071806.dbo.office format nul -T -n -f c:\office.fmt'
    --print @exec
    exec master..xp_cmdshell @exec


    /*Done on a named instance, I can't get this to work UNLESS I specify the server\instance name*/
    declare @exec varchar(128)
    set @exec = 'bcp aps.dbo.office format -n -c -f c:\office.fmt -S"Server\InstanceName" -T'
    --print @exec
    exec master..xp_cmdshell @exec

Posting Permissions

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