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 locatio开发者_StackOverflow社区n.

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.

---

Thoughts...

• Can you use SQLBulkCopy from your .net?
• Or run an SSIS from .net?
• Or BULK INSERT in SQL

Saying that, you can find the path

• SQL: SQL Server: How to SELECT the installation path? the-installation-path
• Registry: C# How to get SQL Server installation path programatically?

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)

---

Could you find most of what you need in the servers registry?

I know of the following registry path that has lots of info, including paths, and versions.

HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\

or on a 64bit system:

HKLM\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server

For information on Wow6432Node see this question