2012年3月8日星期四
BCP failure.
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
bcp failed when special characters {;" were used in the password from command line in Win 2
bcp worked with password in normal characters, but failed with {;"
I tried for SQL 2005 and SQL 2000, from Window 2003 SP1. Is this a bug?
When the special character { was used in the password, bcp failed with Native error 0.
>>
F:\ftp\log\Cases>bcp "sem5.sem5.server_system_log_1" in server_system_log_1.dat
-n -Smodonald -Usem5 -P{
SQLState = 08001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Neither DSN nor SERVER keyword supplied
SQLState = 01S00, NativeError = 0
Warning = [Microsoft][ODBC SQL Server Driver]Invalid connection string attribute
>>
When the special character ; or " was used in the password, bcp failed with Native error 18456.
>>
F:\ftp\log\Cases>bcp "sem5.sem5.server_system_log_1" in server_system_log_1.dat
-n -Smodonald -Usem5 -P;
SQLState = 28000, NativeError = 18456
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'sem5'.
F:\ftp\log\Cases>bcp "sem5.sem5.server_system_log_1" in server_system_log_1.dat
-n -Smodonald -Usem5 -P"
SQLState = 28000, NativeError = 18456
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'sem5'.
>>
Could you try :
bcp "sem5.sem5.server_system_log_1" in server_system_log_1.dat
-n -Smodonald -Usem5 -P"{"|||
I tried the followings but it didn't work.
bcp "sem5.sem5.server_system_log_1" in server_system_log_1.dat
-n -Smodonald -Usem5 -P"{"
bcp "sem5.sem5.server_system_log_1" in server_system_log_1.dat
-n -Smodonald -Usem5 -P'{'
bcp "sem5.sem5.server_system_log_1" in server_system_log_1.dat
-n -Smodonald -Usem5 -P\{
I opened a case and got an answer.
Based on the Microsoft SQL Server 2005 online help for “Strong Passwords[SQL Server]”,
>>
if used in an OLE DB or ODBC connection string, a login or password must not contain the following characters: [] {}() , ; ? * ! @.. These characters are used to either initialize the connection or separate connection values.
>>
As the result, SQL Server Enterprise Manager allows all special characters but the OLE DB or ODBC connection is different.
|||Thanks for following up your own post. It helps others when you share your solution.
2012年2月25日星期六
BCP copy in failed...The SQL Server cannot obtain a LOCK resource
Windows 2003 with SP1). I want to copy some data from DB1 to DB2 with
BCP.
I BCP out the data to the data files no problem with the following
command:
bcp "SELECT * FROM DB1.dbo.Table1 WHERE id>=587738400000000000"
queryout Table1.dat -n -Usa -Ppass -Shost -eTable1_out_error.txt
However, I failed to BCP in the data to the database with this
command:
bcp DB2.dbo.Table1 in Table1.dat -n -Usa -Ppass -Shost -
eTable1_in_error.txt
Here is the error message:
<SKIP>
1000 rows sent to SQL Server. Total sent: 141497000
SQLState = S1000, NativeError = 1204
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]The SQL Server
cannot obtain a LOCK resource at this time. Rerun your statement when
there are fewer active users or ask the system administrator to check
the SQL Server lock and memory configuration.
BCP copy in failed
The error files "Table1_out_error.txt" and "Table1_in_error.txt" are
empty.
I had used BCP to copy similar (type and among of) data to an Oracle
database without any problem in the same server. Am I messing
something here?
Any help will be highly appreciated.
Helen
On Jul 5, 9:31 am, hele...@.gmail.com wrote:
> I have 2 identical databases (DB1, DB2) in SQL Server 2000 (OS:
> Windows 2003 with SP1). I want to copy some data from DB1 to DB2 with
> BCP.
> I BCP out the data to the data files no problem with the following
> command:
> bcp "SELECT * FROM DB1.dbo.Table1 WHERE id>=587738400000000000"
> queryout Table1.dat -n -Usa -Ppass -Shost -eTable1_out_error.txt
> However, I failed to BCP in the data to the database with this
> command:
> --
> bcp DB2.dbo.Table1 in Table1.dat -n -Usa -Ppass -Shost -
> eTable1_in_error.txt
> --
> Here is the error message:
> --
> <SKIP>
> 1000 rows sent to SQL Server. Total sent: 141497000
> SQLState = S1000, NativeError = 1204
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]The SQL Server
> cannot obtain a LOCK resource at this time. Rerun your statement when
> there are fewer active users or ask the system administrator to check
> the SQL Server lock and memory configuration.
> BCP copy in failed
> --
> The error files "Table1_out_error.txt" and "Table1_in_error.txt" are
> empty.
> I had used BCP to copy similar (type and among of) data to an Oracle
> database without any problem in the same server. Am I messing
> something here?
> Any help will be highly appreciated.
> Helen
Your server is running out of memory either because of your actions or
because of someone else's actions.
use sp_lock to check who is doing it .
Cheers,
BCP copy in failed...The SQL Server cannot obtain a LOCK resource
Windows 2003 with SP1). I want to copy some data from DB1 to DB2 with
BCP.
I BCP out the data to the data files no problem with the following
command:
bcp "SELECT * FROM DB1.dbo.Table1 WHERE id>=587738400000000000"
queryout Table1.dat -n -Usa -Ppass -Shost -eTable1_out_error.txt
However, I failed to BCP in the data to the database with this
command:
--
bcp DB2.dbo.Table1 in Table1.dat -n -Usa -Ppass -Shost -
eTable1_in_error.txt
--
Here is the error message:
--
<SKIP>
1000 rows sent to SQL Server. Total sent: 141497000
SQLState = S1000, NativeError = 1204
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]The SQL S
erver
cannot obtain a LOCK resource at this time. Rerun your statement when
there are fewer active users or ask the system administrator to check
the SQL Server lock and memory configuration.
BCP copy in failed
--
The error files "Table1_out_error.txt" and "Table1_in_error.txt" are
empty.
I had used BCP to copy similar (type and among of) data to an Oracle
database without any problem in the same server. Am I messing
something here?
Any help will be highly appreciated.
HelenOn Jul 5, 9:31 am, hele...@.gmail.com wrote:
> I have 2 identical databases (DB1, DB2) in SQL Server 2000 (OS:
> Windows 2003 with SP1). I want to copy some data from DB1 to DB2 with
> BCP.
> I BCP out the data to the data files no problem with the following
> command:
> bcp "SELECT * FROM DB1.dbo.Table1 WHERE id>=587738400000000000"
> queryout Table1.dat -n -Usa -Ppass -Shost -eTable1_out_error.txt
> However, I failed to BCP in the data to the database with this
> command:
> --
> bcp DB2.dbo.Table1 in Table1.dat -n -Usa -Ppass -Shost -
> eTable1_in_error.txt
> --
> Here is the error message:
> --
> <SKIP>
> 1000 rows sent to SQL Server. Total sent: 141497000
> SQLState = S1000, NativeError = 1204
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]The SQL
Server
> cannot obtain a LOCK resource at this time. Rerun your statement when
> there are fewer active users or ask the system administrator to check
> the SQL Server lock and memory configuration.
> BCP copy in failed
> --
> The error files "Table1_out_error.txt" and "Table1_in_error.txt" are
> empty.
> I had used BCP to copy similar (type and among of) data to an Oracle
> database without any problem in the same server. Am I messing
> something here?
> Any help will be highly appreciated.
> Helen
Your server is running out of memory either because of your actions or
because of someone else's actions.
use sp_lock to check who is doing it .
Cheers,
BCP copy in failed...The SQL Server cannot obtain a LOCK resource
Windows 2003 with SP1). I want to copy some data from DB1 to DB2 with
BCP.
I BCP out the data to the data files no problem with the following
command:
bcp "SELECT * FROM DB1.dbo.Table1 WHERE id>=587738400000000000"
queryout Table1.dat -n -Usa -Ppass -Shost -eTable1_out_error.txt
However, I failed to BCP in the data to the database with this
command:
--
bcp DB2.dbo.Table1 in Table1.dat -n -Usa -Ppass -Shost -
eTable1_in_error.txt
--
Here is the error message:
--
<SKIP>
1000 rows sent to SQL Server. Total sent: 141497000
SQLState = S1000, NativeError = 1204
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]The SQL Server
cannot obtain a LOCK resource at this time. Rerun your statement when
there are fewer active users or ask the system administrator to check
the SQL Server lock and memory configuration.
BCP copy in failed
--
The error files "Table1_out_error.txt" and "Table1_in_error.txt" are
empty.
I had used BCP to copy similar (type and among of) data to an Oracle
database without any problem in the same server. Am I messing
something here?
Any help will be highly appreciated.
HelenOn Jul 5, 9:31 am, hele...@.gmail.com wrote:
> I have 2 identical databases (DB1, DB2) in SQL Server 2000 (OS:
> Windows 2003 with SP1). I want to copy some data from DB1 to DB2 with
> BCP.
> I BCP out the data to the data files no problem with the following
> command:
> bcp "SELECT * FROM DB1.dbo.Table1 WHERE id>=587738400000000000"
> queryout Table1.dat -n -Usa -Ppass -Shost -eTable1_out_error.txt
> However, I failed to BCP in the data to the database with this
> command:
> --
> bcp DB2.dbo.Table1 in Table1.dat -n -Usa -Ppass -Shost -
> eTable1_in_error.txt
> --
> Here is the error message:
> --
> <SKIP>
> 1000 rows sent to SQL Server. Total sent: 141497000
> SQLState = S1000, NativeError = 1204
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]The SQL Server
> cannot obtain a LOCK resource at this time. Rerun your statement when
> there are fewer active users or ask the system administrator to check
> the SQL Server lock and memory configuration.
> BCP copy in failed
> --
> The error files "Table1_out_error.txt" and "Table1_in_error.txt" are
> empty.
> I had used BCP to copy similar (type and among of) data to an Oracle
> database without any problem in the same server. Am I messing
> something here?
> Any help will be highly appreciated.
> Helen
Your server is running out of memory either because of your actions or
because of someone else's actions.
use sp_lock to check who is doing it .
Cheers,