September 24, 2013 at 8:53 am
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?
September 24, 2013 at 12:34 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 25, 2013 at 5:37 am
Thank you, Jeffrey Williams, that was exactly what I was looking for.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply