[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 649
  • Last Modified:

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?
1 Solution
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


Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now