BCP error after upgrading and migrating

Question

BCP error after upgrading and migrating

chumphrey 12211

Ten Centuries

Points: 1223
More actions
August 22, 2011 at 12:54 pm

#256115

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?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

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

chumphrey 12211 Ten Centuries Points: 1223 More actions · Answer 2

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.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

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

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

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

chumphrey 12211 Ten Centuries Points: 1223 More actions · Answer 5

THANK YOU!! I was just now able to make the change you suggested and it now works like before!!!