.netsql-serverbcp

How do I find BCP on a SQL Server?


I am creating a library of code that will be deployed across several dozen Sql Servers in a corporate environment. Versions of SQL Server are mostly 2008, but some 2000, 2005 and 2008R2 instances exist as well. These are also running on various versions of Windows server 2000-2008. I need to be able to locate the Sql Server version of BCP to be called from within the C#.NET code in this library.

I cannot rely on the default installation directory because many of the servers do not have Sql Server installed in the default directory locations.

I also cannot rely on the PATH variable because many of these servers also have the Sybase drivers installed which has it's own version of BCP (which doesn't support the queryout command that many of the BCP calls we have utilize) and we have had issues with relying on the PATH environmental variable returning the Sybase location and not the Sql Server location.

Can anyone suggest a solution that would allow me to easily find the location of BCP across these servers given this disparate versions and constraints?

EDIT:

I had thought about using SQLBulkCopy, but this is to support a bunch of already existing BCP calls across a multitude of databases and stored procedures. Right now they all call in to an existing stored procedure which exists in a common database on each server and in that stored procedure an impersonate call then uses its elevated privileges to call xp_cmdshell. We want to get rid of the xp_cmdshell part for security reasons. We want to replace it with a CLR Stored Procedure that finds BCP and then passes in the parameters so that someone can't use the process in place to get access to the cmd shell nearly as easily.

BULK INSERT also doesn't work in our environment because of the strange way security permissions are set up and for some reason they haven't figured out yet, BCP can't work with file locations that are not on the same physical box. Plus most of our BCP calls are creating files, not importing them.


Solution

  • Thoughts...

    Saying that, you can find the path

    Of course, your calling account will need rights either in SQL or to read the registry remotely. Using SQL means using the SQL service acount can see it's own registry keys

    And many results on Google for "find installation path for SQL server" too (I picked two above)