How can I programmatically find out the location of the BINN directory for my SQL Server installation?

I have a custom application that is used for a custom log shipping solution. After our applicaiton is installed, users can select specific SQL Server instances that they want to log ship. Our application then pushes out the necessary T-SQL statements to the selected SQL Server instances.

Our application replies on a mail DLL for sending e-mail alerts from SQL Server. This requires that we place the DLL within the BINN directory of the SQL Server installation and register the extended stored procedure. Currently this is a manual process but I would like to automate it. In order to automate it I need to know the following

How can I find out the location of the BINN directory of a specific SQL Server instance? This can be a solution using T-SQL or SMO using C#.

Will each SQL Server instance have its own BINN directory or do they all share one?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You can use Server Management Object (SMO)... or the older Distributed Management Objects (DMO) to get the instance's root directory 
The installation directory is a registry key
You can use T-SQL xp_regread.
Or if you have xp_cmdshell enable:
   Exec xp_cmdshell 'path'
Using .Net  you need admin permissions.

Declare @BinnPath as varchar(200);
Set @BinnPath = NULL
Exec master..xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\Microsoft SQL Server\70\Tools\ClientSetup', 'SQLPath', @BinnPath OUTPUT
Select @BinnPath as [Sql Server 7.0 path] 
Set @BinnPath = NULL
Exec master..xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\Microsoft SQL Server\80\Tools\ClientSetup', 'SQLPath', @BinnPath OUTPUT
Select @BinnPath as [Sql Server 2000 path]
Set @BinnPath = NULL
Exec master..xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\ClientSetup', 'SQLPath', @BinnPath OUTPUT
Select @BinnPath as [Sql Server 2005 path]
Set @BinnPath = NULL
Exec master..xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\Microsoft SQL Server\100\Tools\ClientSetup', 'SQLPath', @BinnPath OUTPUT
Select @BinnPath as [Sql Server KATMAI path]

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.