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

Posted on 2008-11-17
Last Modified: 2013-12-07
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?
Question by:nyphalanx
    LVL 41

    Expert Comment

    You can use Server Management Object (SMO)... or the older Distributed Management Objects (DMO) to get the instance's root directory
    LVL 9

    Accepted Solution

    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

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
    This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now