none
SSMS Addin - Locating Commandbar / Context Menu items...

    Question

  • Hi, I am currently working on a project to extend SSMS 2008 R2 functionality using an Addin built with VS2010 Prem. I appreciate that is is an unsupported feature of SSMS, but the potential gains in streamlining our interface for both senior and junior staff is immense...

    I am trying to add shortcuts to both the Database and Tables nodes in the object explorer window, however I have been unsuccessful. I can add commandbars, commandBarPopups, commands etc to the top menus (MainMenu bar), however am unsuccessful in applying these items to the context menu's on objects in the object explorer window.

    Have been searching for solutions extensively, however have not found any yet. Other downloadable source code projects will not compile as they're for either SSMS 2005, 2012, or have some other missing dependencies. I have installed VS2010 SDK and SQL Server Development Libraries as well, and referenced where required...

    Have also gone down the ServiceCache.ServiceProvider.GetService() route, however I could never get it to return a value, it was always nothing.

    I have exported a full tree listing of all commandBars, commandBar, commandBarPopup, CommandBarButtons and related Commands; however none match the items i'm searching for (e.g. Select Top 1000 Rows menu item {from the context menu on a table object} is not present in the listing).

    I have seen pictures of other people's Addin's adding extra items to these menu's, so it must be possible.

    My current thinking is that the object explorer's menu's are accessible through some other object, possibly another window or something; I was wondering if anyone had any suggestions or could point in the right direction?

    Thanks,
    Jay :)


    If you shake a kettle, does it boil faster?

    Saturday, June 02, 2012 1:11 AM

Answers

  • Found the answer. It appears the code:

    Private _applicationExplorerService As ObjectExplorerService
    _applicationExplorerService = CType(ServiceCache.ServiceProvider.GetService(GetType(ObjectExplorer.IObjectExplorerService)), ObjectExplorerService)

    Only works with .NET Framework 3.5. When you use .NET Framework 4.0 it always returns nothing....

    As a side note, also updated the Project References. The original references may have worked, but using these are the ones I used when I got it working...

      <ItemGroup>
        <Reference Include="EnvDTE, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a">
          <SpecificVersion>False</SpecificVersion>
          <EmbedInteropTypes>True</EmbedInteropTypes>
          <HintPath>..\..\..\..\..\..\..\Program Files\Microsoft Visual Studio 10.0\Common7\IDE\PublicAssemblies\EnvDTE.dll</HintPath>
        </Reference>
        <Reference Include="EnvDTE80, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a">
          <EmbedInteropTypes>True</EmbedInteropTypes>
        </Reference>
        <Reference Include="Extensibility, Version=7.0.3300.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a">
          <EmbedInteropTypes>False</EmbedInteropTypes>
        </Reference>
        <Reference Include="Microsoft.SqlServer.ConnectionInfo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL">
          <SpecificVersion>False</SpecificVersion>
          <HintPath>..\..\..\..\..\..\..\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll</HintPath>
        </Reference>
        <Reference Include="Microsoft.SqlServer.Management.Sdk.SqlStudio, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL">
          <SpecificVersion>False</SpecificVersion>
          <HintPath>..\..\..\..\..\..\..\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Microsoft.SqlServer.Management.Sdk.SqlStudio.dll</HintPath>
        </Reference>
        <Reference Include="Microsoft.SqlServer.Management.SqlStudio.Explorer">
          <HintPath>..\..\..\..\..\..\..\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Microsoft.SqlServer.Management.SqlStudio.Explorer.dll</HintPath>
        </Reference>
        <Reference Include="Microsoft.SqlServer.RegSvrEnum, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />
        <Reference Include="Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL">
          <SpecificVersion>False</SpecificVersion>
          <HintPath>..\..\..\..\..\..\..\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Smo.dll</HintPath>
        </Reference>
        <Reference Include="Microsoft.SqlServer.SqlTools.VSIntegration, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=x86">
          <SpecificVersion>False</SpecificVersion>
          <HintPath>..\..\..\..\..\..\..\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Microsoft.SqlServer.SqlTools.VSIntegration.dll</HintPath>
        </Reference>
        <Reference Include="Microsoft.VisualStudio.CommandBars, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a">
          <SpecificVersion>False</SpecificVersion>
          <EmbedInteropTypes>True</EmbedInteropTypes>
          <HintPath>..\..\..\..\..\..\..\WINDOWS\assembly\GAC\Microsoft.VisualStudio.CommandBars\8.0.0.0__b03f5f7f11d50a3a\Microsoft.VisualStudio.CommandBars.dll</HintPath>
        </Reference>
        <Reference Include="Microsoft.VisualStudio.OLE.Interop, Version=7.1.40304.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
        <Reference Include="SqlWorkbench.Interfaces, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL">
          <SpecificVersion>False</SpecificVersion>
          <HintPath>..\..\..\..\..\..\..\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbench.Interfaces.dll</HintPath>
        </Reference>
        <Reference Include="System">
          <Name>System</Name>
        </Reference>
        <Reference Include="System.Data">
          <Name>System.Data</Name>
        </Reference>
        <Reference Include="System.Drawing" />
        <Reference Include="System.Management" />
        <Reference Include="System.Web.Services" />
        <Reference Include="System.Xml">
          <Name>System.XML</Name>
        </Reference>
        <Reference Include="System.Windows.Forms">
          <Name>System.Windows.Forms</Name>
        </Reference>
      </ItemGroup>


    If you shake a kettle, does it boil faster?

    • Marked as answer by Jay Kidd Sunday, June 03, 2012 5:43 PM
    Sunday, June 03, 2012 5:43 PM

All replies

  • Found the answer. It appears the code:

    Private _applicationExplorerService As ObjectExplorerService
    _applicationExplorerService = CType(ServiceCache.ServiceProvider.GetService(GetType(ObjectExplorer.IObjectExplorerService)), ObjectExplorerService)

    Only works with .NET Framework 3.5. When you use .NET Framework 4.0 it always returns nothing....

    As a side note, also updated the Project References. The original references may have worked, but using these are the ones I used when I got it working...

      <ItemGroup>
        <Reference Include="EnvDTE, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a">
          <SpecificVersion>False</SpecificVersion>
          <EmbedInteropTypes>True</EmbedInteropTypes>
          <HintPath>..\..\..\..\..\..\..\Program Files\Microsoft Visual Studio 10.0\Common7\IDE\PublicAssemblies\EnvDTE.dll</HintPath>
        </Reference>
        <Reference Include="EnvDTE80, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a">
          <EmbedInteropTypes>True</EmbedInteropTypes>
        </Reference>
        <Reference Include="Extensibility, Version=7.0.3300.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a">
          <EmbedInteropTypes>False</EmbedInteropTypes>
        </Reference>
        <Reference Include="Microsoft.SqlServer.ConnectionInfo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL">
          <SpecificVersion>False</SpecificVersion>
          <HintPath>..\..\..\..\..\..\..\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll</HintPath>
        </Reference>
        <Reference Include="Microsoft.SqlServer.Management.Sdk.SqlStudio, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL">
          <SpecificVersion>False</SpecificVersion>
          <HintPath>..\..\..\..\..\..\..\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Microsoft.SqlServer.Management.Sdk.SqlStudio.dll</HintPath>
        </Reference>
        <Reference Include="Microsoft.SqlServer.Management.SqlStudio.Explorer">
          <HintPath>..\..\..\..\..\..\..\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Microsoft.SqlServer.Management.SqlStudio.Explorer.dll</HintPath>
        </Reference>
        <Reference Include="Microsoft.SqlServer.RegSvrEnum, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />
        <Reference Include="Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL">
          <SpecificVersion>False</SpecificVersion>
          <HintPath>..\..\..\..\..\..\..\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Smo.dll</HintPath>
        </Reference>
        <Reference Include="Microsoft.SqlServer.SqlTools.VSIntegration, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=x86">
          <SpecificVersion>False</SpecificVersion>
          <HintPath>..\..\..\..\..\..\..\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Microsoft.SqlServer.SqlTools.VSIntegration.dll</HintPath>
        </Reference>
        <Reference Include="Microsoft.VisualStudio.CommandBars, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a">
          <SpecificVersion>False</SpecificVersion>
          <EmbedInteropTypes>True</EmbedInteropTypes>
          <HintPath>..\..\..\..\..\..\..\WINDOWS\assembly\GAC\Microsoft.VisualStudio.CommandBars\8.0.0.0__b03f5f7f11d50a3a\Microsoft.VisualStudio.CommandBars.dll</HintPath>
        </Reference>
        <Reference Include="Microsoft.VisualStudio.OLE.Interop, Version=7.1.40304.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
        <Reference Include="SqlWorkbench.Interfaces, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL">
          <SpecificVersion>False</SpecificVersion>
          <HintPath>..\..\..\..\..\..\..\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbench.Interfaces.dll</HintPath>
        </Reference>
        <Reference Include="System">
          <Name>System</Name>
        </Reference>
        <Reference Include="System.Data">
          <Name>System.Data</Name>
        </Reference>
        <Reference Include="System.Drawing" />
        <Reference Include="System.Management" />
        <Reference Include="System.Web.Services" />
        <Reference Include="System.Xml">
          <Name>System.XML</Name>
        </Reference>
        <Reference Include="System.Windows.Forms">
          <Name>System.Windows.Forms</Name>
        </Reference>
      </ItemGroup>


    If you shake a kettle, does it boil faster?

    • Marked as answer by Jay Kidd Sunday, June 03, 2012 5:43 PM
    Sunday, June 03, 2012 5:43 PM
  • Hi Jay,

    Have a look at my add-in: SSMSboost (.com). Maybe you will find some functions already implemented.

    If you  think that functions that you need might be helpful for other developers you can describe them to me and we will do our best to implement them. You will save a lot of time. For some "specials" we could think about implementing some API, so that you could add specific functions. Everything depends on features that you need. You can write me directly andrei(at)ssmsboost.com.

    Andrei



    SSMSBoost - ssms add-in adds quick source code navigation to SSMS, quick connection management & switching, shortcuts, auto replacements and object search. www.ssmsboost.com

    Wednesday, June 06, 2012 11:49 AM
  • Hi Andrei,

    Thanks for pointing us to your SSMSboost project / product, it appears to provide a lot of beneficial features that we may be interested in...

    Our BI team also heavily relies on SSMS to undertake a number of full-time projects. We have many tools/extensions (SQLCLR's, external applications and other IP) developed internally that we would like to better integrate into our primary development environment, that being SSMS. We want to make it as easy and efficient for our team members to work within the provided environment.

    The low-lying features we are looking at integrating are:

    • Simplied user management, simple GUI listing all users and their level of access (none, read, full access)
    • Data importing, we have developed tools to assist in bulk-loading / validating data & reports, including importing whole folders of files, automatically scanning and determining the structure of the file and configuring the destination tables with the best datatypes / sizes, correcting data issues (delimiters within field data without qualifiers, fields with newlines...) etc...
    • Creation of projects, setting up the databases, installing SQLCLR tools locally within the db, increasing db growth, setting recovery model, setting up file-system folders etc..
    • Archiving of projects, backing up the database, dropping it, zipping it up with its project files and moving it to our archive server...
    • Restoring / Attaching db's - they are not SysAdmins, so in 2008R2 they have to do this via commands, and CLR's are disabled when the db comes back online, so it re-enables all code automatically.
    • Integration with sharepoint for accessing documentation on functions & processes.
    • Accessing script templates, where we have a database with searchable common scripts, where they can also be submitted and versioned.
    • Some automated Data validation, similar to Polices, it checks pre-defined relationship integrity between tables (if they exist), existence of key fields (we have lists of fields expected in tables), checking values (e.g. date fields that are actually text, or contain nulls) etc; trying to identify any issues early.

    I think most of this functionality is probably specialized to our particular team, and may not align directly to the audience of your own addin... but will let you be the judge of that. Hope the helps...

    Cheers mate,
    Jay :)


    If you shake a kettle, does it boil faster?

    Wednesday, June 06, 2012 5:00 PM
  • Hi Jay,

    I see.... Yes - functions that you have mentioned are pretty specific. What I could offer you, if it will help, is: we could think about providing in SSMSBoost add-in some simple API interface for object explorer, like letting you to define which menue items will appear on right click and which command (.exe or dll-call) will be called with which parameters. Like:

    Menue Name: ShowInternalDocumentation

    ShowOnObjectTypes: All

    ExecuteShellOpen: "http://companyserver.lan/sharepoint/object=@objectName&database=@databaseName"

    etc. As execution option could be something like

    ExecuteShellOpen

    RunSQL

    CallApiDll (with pre-defined calling convention)

    as @parameters we could publish any parameters, available on object level.

    If this direction is interesting to you, write me an e-mail (andrei(@)ssmsboost.com), and think about which node types are you interested in and which calls do you need.

    If you decide to write your own add-in, I advise to do that for SSMS 2012, as far as there were some changes between 2008 and 2012 versions and you will not be able to convert your 2008 add-in to 2012 easily - some additional weeks of work will be required. I have written a small article with downloadable sample project, that you can use as start-up project:

    www.ssmsboost.com/Home/create-own-ssms-2012-add-in-sample-code-with-download

    Have fun !


    SSMSBoost - ssms add-in adds quick source code navigation to SSMS, quick connection management & switching, shortcuts, auto replacements and object search. www.ssmsboost.com

    • Proposed as answer by Naomi NEditor Thursday, June 07, 2012 5:16 PM
    Wednesday, June 06, 2012 6:58 PM
  • Hi Andrei

    Thanks for your response again, your proposed solution for dynamic menu creation and linking to external applications with passed parameters does seem 'tasty'. It does mirror some of our own structural plans for extending SSMS.

    Unfortunately, any software purchases (such as SSMSBoost) or upgrades (SS 2012) are managed and defined by Global (our firm's global IT management) who are responsible for all licensing throughout our firm's network (managing ~170,000 employees). Getting any new software added to the licensed application network is difficult and a bureaucratic nightmare; business cases, value propositions / return on investment, life-cycling, version control, support staff allocations, training schedules, priority, budgeting, polices etc... which I can appreciate from their standpoint when they're managing such a large network. Our small team (15-20 persons) is further restricted as we're one of only two countries who use SQL Server for BI, all our other countries (150-160) use ACL, so we don't get much backing nor priority for software purchases. We have put a request in for SS 2012, but were denied that it doesn't meet the firms policy on having a service pack available.

    We are currently proceeding with designing our own addin as our project has already been through approval within our firm.

    Thanks to the link to your article on SSMS 2012 addin's, have actually already read it during our research phase. Since we're sticking with 2008 R2 for the near future, we will be developing for that. The addin will just be a shell anyway, referencing existing features in other projects, so we don't expect much rework to upgrade it to 2012. The fun part will be trying to downgrade our existing code to .NET Framework 3.5 from 4.0 where possible.

    Appreciate the guidance you have provided. Will reach out to you if our situation changes...

    Cheers mate,
    Jay :)


    If you shake a kettle, does it boil faster?

    Thursday, June 07, 2012 4:26 PM
  • Ok, I understand your situation clearly.

    Wish you good luck in your project.

    Andrei



    SSMSBoost - ssms add-in adds quick source code navigation to SSMS, quick connection management & switching, shortcuts, auto replacements and object search. www.ssmsboost.com


    Thursday, June 07, 2012 5:13 PM