Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

BCP error after upgrading and migrating Expand / Collapse
Author
Message
Posted Monday, August 22, 2011 12:54 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 25, 2014 12:16 PM
Points: 37, Visits: 463
I am upgrading and migrating from SQL Server 2000 to 2008 on a new server. I created a new named instance on the new server. I think the 2000 version was a default instance? While testing the migrated Stored Procedures on the new server the following bcp command produced the following error.

EXEC master..xp_cmdshell 'bcp "select string_out from EmployeeDB..vEmployee_Info order by ssn" queryout "C:\Employee_Info.txt" -c -T'

SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Server Native Client 10.0]Named Pipes Provider: Could not open a connection to SQL Server [2].
SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Server Native Client 10.0]A network-related or instance-specific error has occurred while
establishing a connection to SQL Server. Server is not found or not accessible.
Check if instance name is correct and if SQL Server is
configured to allow remote connections. For more information see SQL Server Books Online.
SQLState = S1T00, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Login timeout expired
NULL

To fix the command ALL I did was add -S server_name\instance after the -T option.

My question is: Is there a way to resolve this so that the bcp command doesnt have to be modified to work on the new server?
Post #1163565
Posted Wednesday, August 24, 2011 2:16 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 10:01 AM
Points: 7,127, Visits: 12,731
chumphrey 12211 (8/22/2011)
I am upgrading and migrating from SQL Server 2000 to 2008 on a new server. I created a new named instance on the new server. I think the 2000 version was a default instance? While testing the migrated Stored Procedures on the new server the following bcp command produced the following error.

EXEC master..xp_cmdshell 'bcp "select string_out from EmployeeDB..vEmployee_Info order by ssn" queryout "C:\Employee_Info.txt" -c -T'

SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Server Native Client 10.0]Named Pipes Provider: Could not open a connection to SQL Server [2].
SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Server Native Client 10.0]A network-related or instance-specific error has occurred while
establishing a connection to SQL Server. Server is not found or not accessible.
Check if instance name is correct and if SQL Server is
configured to allow remote connections. For more information see SQL Server Books Online.
SQLState = S1T00, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Login timeout expired
NULL

To fix the command ALL I did was add -S server_name\instance after the -T option.

My question is: Is there a way to resolve this so that the bcp command doesnt have to be modified to work on the new server?

From the bcp docs: http://technet.microsoft.com/en-us/library/ms162802.aspx:

-S server_name[ \instance_name]
Specifies the instance of SQL Server to which to connect. If no server is specified, the bcp utility connects to the default instance of SQL Server on the local computer. This option is required when a bcp command is run from a remote computer on the network or a local named instance. To connect to the default instance of SQL Server on a server, specify only server_name. To connect to a named instance of SQL Server, specify server_name\instance_name.


When you shut down your 2000 instance if you modify the 2008 instance to answer on port 1433 instead of what it is answering on bcp may start working again without providing the -S option.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1164962
Posted Wednesday, August 24, 2011 4:01 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 25, 2014 12:16 PM
Points: 37, Visits: 463
Thanks for the reply. I guess I was wondering if there was a way to "alias" the named instance to avoid making programming changes to the migrated code? Guess that would defeat the purpose of naming the instance.

Post #1165033
Posted Wednesday, August 24, 2011 4:36 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 10:01 AM
Points: 7,127, Visits: 12,731
chumphrey 12211 (8/24/2011)
Thanks for the reply. I guess I was wondering if there was a way to "alias" the named instance to avoid making programming changes to the migrated code? Guess that would defeat the purpose of naming the instance.

You can certainly create an alias for the local server name however I am not sure aliases ever come into play when -S is not supplied. bcp uses the Native Client and it appears to use Named Pipes by default when -S is not supplied. From your error message:

Error = [Microsoft][SQL Server Native Client 10.0]Named Pipes Provider: Could not open a connection to SQL Server [2].
SQLState = 08001, NativeError = 2

This is likely the Named Pipe it is trying to use, as this is how you would address a default instance:

\\\pipe\sql\query

I have no default instance on my machine so I just tried creating an alias for the above Named Pipe name but could not get bcp to redirect to my named instance using either Protocol TCP/IP or Named Pipes. If you figure it out please post back. Good luck!


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1165040
Posted Thursday, August 25, 2011 10:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 10:01 AM
Points: 7,127, Visits: 12,731
I got bcp working!

Here's how:

1. Open SQL Server Configuration Manager and go to the Named Pipe Properties of your instance.



2. Change the value to:

\\.\pipe\sql\query


Note: if your 2000 instance, the old default instance you're replacing, is also using this same named pipe you may need to disable the Protocol for that instance, or maybe even change it.



3. Restart your named instance.

4. bcp works with no server name provided and is talking to the named instance!

bcp "select @@servername" queryout C:\myserver.txt -c -T


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato


  Post Attachments 
named_pipe.jpg (130 views, 27.01 KB)
protocols.jpg (127 views, 38.46 KB)
Post #1165516
Posted Friday, August 26, 2011 2:08 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 25, 2014 12:16 PM
Points: 37, Visits: 463
THANK YOU!! I was just now able to make the change you suggested and it now works like before!!!
Post #1166432
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse