2012年3月8日星期四

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, cfri 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.|||Check in the Client Network Configuration (should be in the SQL Server program group) to see if Shared Memory protocol is enabled.|||Doh!!!! :eek:

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]"]|||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

没有评论:

发表评论