Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


BCP error after upgrading and migrating


BCP error after upgrading and migrating

Author
Message
chumphrey 12211
chumphrey 12211
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 480
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?
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8243 Visits: 14368
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
chumphrey 12211
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 480
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
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8243 Visits: 14368
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
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8243 Visits: 14368
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
Attachments
named_pipe.jpg (185 views, 27.00 KB)
protocols.jpg (180 views, 38.00 KB)
chumphrey 12211
chumphrey 12211
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 480
THANK YOU!! I was just now able to make the change you suggested and it now works like before!!!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search