fujih
asked on
Using bcp to connect to SQL 2005 Express named instance
I have multiple SQL Express installations on a machine (as well as a SQL 2008 installation)
On the SQL Express named instance that I wish to bcp into (e.g. MyInstance), I have attached a database and the database is appearing under management studio as e.g. "C:\SQL\Data\MyDB.dbf"
The instance is set to mixed mode authentication and I can connect to the instance in SQL Server Management Studio using Windows Authentication. Note that the database name of the attached database is showing as "C:\SQL\Data\MyDB.mdf" listed under Databases in the SQL 2005 Express instance.
So I try to bcp in using the following command:
bcp MyDB.dbo.Test in "c:\SQL\Data\Test.txt" -c -S MyPC\MyInstance -T
but I get:
SQLState = 37000, NativeError = 4060
Error = [Microsoft][SQL Native client][SQL Server]Cannot open database "MyDB" requested by the login. The login failed.
SQLState = 28000, NativeError = 18456
Error = [Microsoft][SQL Native client][SQL Server]Login failed for user 'MyDomain\myuser\'
Can somebody please provide me with the correct parameters to use for this bcp operation.
Thanks
On the SQL Express named instance that I wish to bcp into (e.g. MyInstance), I have attached a database and the database is appearing under management studio as e.g. "C:\SQL\Data\MyDB.dbf"
The instance is set to mixed mode authentication and I can connect to the instance in SQL Server Management Studio using Windows Authentication. Note that the database name of the attached database is showing as "C:\SQL\Data\MyDB.mdf" listed under Databases in the SQL 2005 Express instance.
So I try to bcp in using the following command:
bcp MyDB.dbo.Test in "c:\SQL\Data\Test.txt" -c -S MyPC\MyInstance -T
but I get:
SQLState = 37000, NativeError = 4060
Error = [Microsoft][SQL Native client][SQL Server]Cannot open database "MyDB" requested by the login. The login failed.
SQLState = 28000, NativeError = 18456
Error = [Microsoft][SQL Native client][SQL Server]Login failed for user 'MyDomain\myuser\'
Can somebody please provide me with the correct parameters to use for this bcp operation.
Thanks
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
>Error = [Microsoft][SQL Native client][SQL Server]Cannot open database "MyDB" requested by the login. The login failed.
means that your windows login cannot connect to that database...
you said you attached the db... did you "fix" the login "links" ?
http://msdn.microsoft.com/en-us/library/aa259633%28SQL.80%29.aspx
means that your windows login cannot connect to that database...
you said you attached the db... did you "fix" the login "links" ?
http://msdn.microsoft.com/en-us/library/aa259633%28SQL.80%29.aspx
ASKER
But I can connect to the SQL Express instance using the same user in trusted mode via the Management Console and can query the database there
ASKER
mmr159,
If my PC name contains a hyphen e.g. myPC-DEV do I need to enclose in quotation marks. Also what should I use for MyDB as in Management Studio it is appearing as "C:\SQL\Data\MyDB.mdf" under Databases.
Should I use:
bcp "C:\SQL\Data\MyDB.mdf".dbo
If I that in the command window just shows the bcp usage information
If my PC name contains a hyphen e.g. myPC-DEV do I need to enclose in quotation marks. Also what should I use for MyDB as in Management Studio it is appearing as "C:\SQL\Data\MyDB.mdf" under Databases.
Should I use:
bcp "C:\SQL\Data\MyDB.mdf".dbo
If I that in the command window just shows the bcp usage information
yes, the "-" will require the double quotes.
ASKER
After I have change the bcp command to:
bcp MyDB.dbo.Test in "c:\SQL\Data\Test.txt" -c -S "myPC-DEV/myInstance" -T
I am getting:
SQLState = 08001, NativeError = -1
Error = [Microsoft][SQL Native client]SQL Network Interfaces: Error Locating Server/Instance specified [xFFFFFFFF].
SQLState = 08001, NativeError = -1
Error = "Microsoft][SQL Native client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
SQLState = S1T00, NativeError = 0
Error = [Microsoft][SQL Native client]Login timeout expired
I am running the bcp command on the same machine that the the SQL Express instance is installed upon
bcp MyDB.dbo.Test in "c:\SQL\Data\Test.txt" -c -S "myPC-DEV/myInstance" -T
I am getting:
SQLState = 08001, NativeError = -1
Error = [Microsoft][SQL Native client]SQL Network Interfaces: Error Locating Server/Instance specified [xFFFFFFFF].
SQLState = 08001, NativeError = -1
Error = "Microsoft][SQL Native client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
SQLState = S1T00, NativeError = 0
Error = [Microsoft][SQL Native client]Login timeout expired
I am running the bcp command on the same machine that the the SQL Express instance is installed upon
use \ instead of / for the instance name
bcp MyDB.dbo.Test in "c:\SQL\Data\Test.txt" -c -S "myPC-DEV\myInstance" -TASKER
Sorry that was a typo I am already using
bcp MyDB.dbo.Test in "c:\SQL\Data\Test.txt" -c -S "myPC-DEV\myInstance" -T
and get
SQLState = 08001, NativeError = -1
Error = [Microsoft][SQL Native client]SQL Network Interfaces: Error Locating Server/Instance specified [xFFFFFFFF].
SQLState = 08001, NativeError = -1
Error = "Microsoft][SQL Native client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
SQLState = S1T00, NativeError = 0
Error = [Microsoft][SQL Native client]Login timeout expired
bcp MyDB.dbo.Test in "c:\SQL\Data\Test.txt" -c -S "myPC-DEV\myInstance" -T
and get
SQLState = 08001, NativeError = -1
Error = [Microsoft][SQL Native client]SQL Network Interfaces: Error Locating Server/Instance specified [xFFFFFFFF].
SQLState = 08001, NativeError = -1
Error = "Microsoft][SQL Native client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
SQLState = S1T00, NativeError = 0
Error = [Microsoft][SQL Native client]Login timeout expired
Did you try specifying the port?
server\instance[comma]port
Example:
bcp MyDB.dbo.Test in "c:\SQL\Data\Test.txt" -c -S "myPC-DEV\myInstance,1433"
server\instance[comma]port
Example:
bcp MyDB.dbo.Test in "c:\SQL\Data\Test.txt" -c -S "myPC-DEV\myInstance,1433"
ASKER
Yes when I use
bcp MyDB.dbo.Test in "c:\SQL\Data\Test.txt" -c -S "myPC-DEV\myInstance,1433"
I get the same errors:
SQLState = 08001, NativeError = -1
Error = [Microsoft][SQL Native client]SQL Network Interfaces: Error Locating Server/Instance specified [xFFFFFFFF].
SQLState = 08001, NativeError = -1
Error = "Microsoft][SQL Native client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
SQLState = S1T00, NativeError = 0
Error = [Microsoft][SQL Native client]Login timeout expired
bcp MyDB.dbo.Test in "c:\SQL\Data\Test.txt" -c -S "myPC-DEV\myInstance,1433"
I get the same errors:
SQLState = 08001, NativeError = -1
Error = [Microsoft][SQL Native client]SQL Network Interfaces: Error Locating Server/Instance specified [xFFFFFFFF].
SQLState = 08001, NativeError = -1
Error = "Microsoft][SQL Native client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
SQLState = S1T00, NativeError = 0
Error = [Microsoft][SQL Native client]Login timeout expired
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
mmr159,
Once I had used SQL Server Surface Area Configuration to change Remote Connections to 'Local and Remote Connections', the bcp operation worked.
Still unclear though why I needed to change this when I was running the bcp operation from the same machine that the SQL Express instance was installed upon
Once I had used SQL Server Surface Area Configuration to change Remote Connections to 'Local and Remote Connections', the bcp operation worked.
Still unclear though why I needed to change this when I was running the bcp operation from the same machine that the SQL Express instance was installed upon
Glad it worked.
Not certain, but it could be because you specified
computername\instance
perhaps the following would have worked
.\SQLEXPRESS
- or -
(local)\SQLEXPRESS
Not certain, but it could be because you specified
computername\instance
perhaps the following would have worked
.\SQLEXPRESS
- or -
(local)\SQLEXPRESS
ASKER
I had already tried .\SQLExpress and local\SQLExpress prior to enabling Remote Connections on the SQL Express instance and had the same error.
Maybe bcp needs remote connections enabled to work?
Maybe bcp needs remote connections enabled to work?
bcp MyDB.dbo.Test in "c:\SQL\Data\Test.txt" -c -S MyPC\MyInstance,1433 -T
(Assuming default port)