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

Simple BCP will not work on clusters Expand / Collapse
Author
Message
Posted Tuesday, September 24, 2013 8:53 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 10, 2014 6:00 AM
Points: 173, Visits: 218
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?
Post #1497925
Posted Tuesday, September 24, 2013 12:34 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, July 17, 2014 12:14 PM
Points: 4,386, Visits: 9,501
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 opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1498010
Posted Wednesday, September 25, 2013 5:37 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 10, 2014 6:00 AM
Points: 173, Visits: 218
Thank you, Jeffrey Williams, that was exactly what I was looking for.
Post #1498267
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse