SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Simple BCP will not work on clusters


Simple BCP will not work on clusters

Author
Message
MichaelDep
MichaelDep
SSC Veteran
SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)

Group: General Forum Members
Points: 212 Visits: 232
I have a fairly simple need to place a text file on in a directory from a query. BCP seemed the best way to do this, and my simple script works fine except on non-clustered instances. Here is the script:

Declare @cmd varchar(2048), @path varchar(100)
select @path = (select Path from [Path] where Number = (select max(Number) from [Path])) + 'version.txt'
SET @CMD = 'bcp "select @@version" queryout ' + @path + ' -SLOCALHOST -E -T -c'
exec master..xp_cmdshell @cmd, NO_OUTPUT

This works fine on my non-clustered instances. On my clustered instances I get a time out error
output
--------------------------------------------------------------------------------------------------
SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Server Native Client 11.0]Named Pipes Provider: Could not open a connection to SQL Server [2].
SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Server Native Client 11.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 config
ured to allow remote connections. For more information see SQL Server Books Online.
SQLState = S1T00, NativeError = 0
Error = [Microsoft][SQL Server Native Client 11.0]Login timeout expired
NULL

And yes, remote connections are enabled. I am thinking is has something to do with the -S parameter and virtual (not VM) names with clustering. But I can't figure it out.

Any ideas?
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCertifiable
SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)

Group: General Forum Members
Points: 7995 Visits: 9971
Your script will also fail on any named instances. The -S parameter is the SQL Server instance and you need to specify the correct instance name.

For clusters, you don't have a LOCALHOST - you need to specify the SQL VIP of the clustered SQL instance. That SQL VIP will be whatever name was defined in the cluster.

You can find the instance name in the variable @@SERVERNAME. Modify your script to build the -S parameter using this variable and it should work.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

MichaelDep
MichaelDep
SSC Veteran
SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)

Group: General Forum Members
Points: 212 Visits: 232
Thank you, Jeffrey Williams, that was exactly what I was looking for.
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