Simple BCP will not work on clusters

  • 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?

  • 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

  • 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