Skip to content

SSMS_INTEGRATION_NOTES.md

Latest commit

 

History

History
361 lines (243 loc) · 19.6 KB

SSMS_INTEGRATION_NOTES.md

File metadata and controls

361 lines (243 loc) · 19.6 KB
Raw

SSMS Integration Notes

Hard-won knowledge about integrating with SSMS from an extension. Read this before touching the integration layer.

Object Explorer Tree

The Tree property returns a TreeView (not a container)

IObjectExplorerService.Tree returns ObjectExplorerControl which inherits from System.Windows.Forms.TreeView:

ObjectExplorerControl
  → LazyTreeView
  → ThemedTreeView
  → System.Windows.Forms.TreeView

Not a container with a child TreeView. Cast directly:

var treeView = oeControl as System.Windows.Forms.TreeView;
// Not: FindChildOfType<TreeView>(oeControl)

treeView.Controls.Count == 0 — the tree IS the control, not a parent of it.

Nodes are ExplorerHierarchyNode

When you get treeView.SelectedNode, it's an ExplorerHierarchyNode (extends TreeNode). These nodes have SSMS-specific methods for context menu actions but they are version-dependent — always reflect to check what's available.

FindNode returns a detached NodeContext, not a TreeNode

IObjectExplorerService.FindNode(urn) returns INodeInformation (concrete: NodeContext). This is a detached node info object — its TreeNode property is null because it isn't actually attached to the tree.

To get the real tree node, you must:

  1. Call FindNode to get the INodeInformation
  2. Call SynchronizeTree(nodeInfo) which navigates + selects it in the tree
  3. Then read treeView.SelectedNode — now it's a real ExplorerHierarchyNode with TreeNode attached

SynchronizeTree can trigger connection dialogs

SynchronizeTree walks down the tree expanding each level. If any level isn't yet cached, it triggers a new connection to load it. This can pop up the "Connect to Server" dialog. To avoid: ensure the user has already expanded the relevant database folder in OE before calling it.

GetConnectedServerNames via GetSelectedNodes

The most reliable way to get connected servers is oeService.GetSelectedNodes(out count, out INodeInformation[] nodes) — but this only returns nodes the user has currently selected. For a full enumeration without user interaction, walk treeView.Nodes directly.

The connection extracted from a node's Connection property is a SqlConnectionInfoWithConnection — it has a live, already-authenticated connection. Never try to construct a new connection from parts; reuse this one.

Scripting Bridge (actions on objects)

Modifying procs/views/functions

Don't use OBJECT_DEFINITION(OBJECT_ID(...)) — it's unreliable (returns NULL for encrypted objects, doesn't include the header). Use SMO directly:

var sp = db.StoredProcedures[name, schema];
sp.Refresh();
string script = sp.ScriptHeader(true) + sp.TextBody;  // true = ALTER, false = CREATE

Same pattern for View and UserDefinedFunction. This matches hunting-dog's approach exactly.

Scripting Tables

For CREATE TABLE scripts, use Scripter with these options:

var scripter = new Scripter(server);
scripter.Options.ScriptDrops = false;
scripter.Options.WithDependencies = false;
scripter.Options.Indexes = true;
scripter.Options.DriAllConstraints = true;
scripter.Options.DriDefaults = true;
scripter.Options.NoCollation = true;
foreach (string s in scripter.Script(new Urn[] { table.Urn })) { ... }

Creating a new connected query window

Use ServiceCache.ScriptFactory.CreateNewBlankScript — it opens a query window already connected to the right server:

var scriptFactory = GetServiceCacheScriptFactory();  // via reflection
// ScriptType.Sql = 0
var scriptType = FindEnumValue("Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ScriptType", 0);
var uiConn = CreateUIConnectionInfo(connInfo);  // UIConnectionInfo struct via reflection
var method = scriptFactory.GetType().GetMethod("CreateNewBlankScript", new[] { scriptType.GetType(), uiConn.GetType(), typeof(System.Data.IDbConnection) });
method.Invoke(scriptFactory, new object[] { scriptType, uiConn, null });

Then insert text via DTE:

var dte = GetService(typeof(EnvDTE.DTE)) as EnvDTE80.DTE2;
if (dte?.ActiveDocument?.Object("TextDocument") is EnvDTE.TextDocument doc)
    doc.EndPoint.CreateEditPoint().Insert(scriptText);

The UIConnectionInfo needs these properties set: ServerName, UserName, Password, PersistPassword=true, ApplicationName, ServerType (GUID 8c91a03d-f9b4-46c0-a305-b5dcc79ff907 for SQL Server), AuthenticationType (0=Windows, 1=SQL).

Design Table (works!)

var mc = new ManagedConnectionWrapper { Connection = rawConn };  // rawConn is SqlOlapConnectionInfoBase from OE
var docType = FindEnumValue("...Editors.DocumentType", 6);  // Table = 6
var docOpts = FindEnumValue("...Editors.DocumentOptions", 2);  // ManageConnection = 2
var method = scriptFactory.GetType().GetMethod("DesignTableOrView", BindingFlags.Public | BindingFlags.Instance);
method.Invoke(scriptFactory, new object[] { docType, docOpts, table.Urn.ToString(), mc });

Edit Top N Rows — Use OpenTableHelperClass, NOT DesignTableOrView

The right API: Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.OpenTableHelperClass.EditTopNRows(NodeContext, int) (static). Lives in ObjectExplorer.dll.

var oeService = GetObjectExplorerService();
var findNodeMethod = oeService.GetType().GetMethod("FindNode", BindingFlags.Public | BindingFlags.Instance);
var nodeInfo = findNodeMethod.Invoke(oeService, new object[] { tableUrn });

// CRITICAL: Set DatabaseName on the node's Connection so EditTopNRows
// reuses the existing OE connection instead of prompting for a new one.
var nodeConn = nodeInfo.GetType().GetProperty("Connection")?.GetValue(nodeInfo);
nodeConn?.GetType().GetProperty("DatabaseName")?.SetValue(nodeConn, databaseName);

var helperType = FindType("Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.OpenTableHelperClass");
var editMethod = helperType.GetMethod("EditTopNRows", BindingFlags.Static | BindingFlags.Public);
editMethod.Invoke(null, new object[] { nodeInfo, 200 });

This works on SSMS 18, 20, and 22. Hunting-dog used this exact approach and we confirmed the class still exists in all three versions.

What does NOT work — DesignTableOrView(DocumentType.OpenTable)

ScriptFactory.DesignTableOrView(DocumentType.OpenTable, ...) fails with:

System.ArgumentException: Value does not fall within the expected range.
  at VsDataDesignerNode.GetDsRef(Urn urn)
  at OpenTableNode.CreateDesigner(...)

on all three versions (SSMS 18, 20, 22). This confirms the DocumentType.OpenTable code path is not the right entry point for programmatic access — use OpenTableHelperClass instead. DocumentType.Table (Design Table) still works fine through DesignTableOrView.

Key lesson: SSMS has multiple internal entry points for the same UI feature. The Scripter/ScriptFactory.DesignTableOrView API is for designer-style entry (table designer, view designer). The OpenTableHelperClass is for grid-style entry (Edit Top N Rows, Select Top N Rows). Don't conflate them.

IManagedConnection

Implement it yourself — it's in SqlWorkbench.Interfaces.dll (which we already reference):

internal sealed class ManagedConnectionWrapper : IManagedConnection
{
    public SqlOlapConnectionInfoBase Connection { get; set; }
    public void Close() { }
    public void Dispose() { }
}

SqlConnectionInfoWithConnection from OE inherits from SqlOlapConnectionInfoBase, so assign it directly. Do not construct a new SqlConnectionInfo from scratch — you'll lose the live connection and trigger a new login prompt.

Global Hotkey (Ctrl+D)

InputBinding on the main WPF window does not intercept keys when the SQL editor has focus (the editor is a WinForms-hosted control with its own message loop). Use Win32 RegisterHotKey instead:

[DllImport("user32.dll")]
static extern bool RegisterHotKey(IntPtr hWnd, int id, uint fsModifiers, uint vk);

var mainWindow = Application.Current.MainWindow;
var hwnd = new WindowInteropHelper(mainWindow).Handle;
var hwndSource = HwndSource.FromHwnd(hwnd);
hwndSource.AddHook(WndProc);
RegisterHotKey(hwnd, HOTKEY_ID, MOD_CONTROL, VK_D);
// MOD_CONTROL = 0x0002, VK_D = 0x44, WM_HOTKEY = 0x0312

Works globally across all SSMS panels including the SQL editor.

Note: Ctrl+D is hunting-dog's default. It's a reasonable choice but conflicts with any other extension using the same global hotkey. Consider making it configurable in a future version.

DTE Commands

DTE ExecuteCommand command names in SSMS are not the same as in Visual Studio. Most VS-style commands (Query.EditTopNRows, Tools.Options) don't exist. The commands you can use are mostly the ones listed in SSMS's Tools → Options → Environment → Keyboard.

To enumerate available commands:

foreach (EnvDTE.Command cmd in dte.Commands)
    Console.WriteLine($"{cmd.Name} (guid:{cmd.Guid})");

This gives ~5000 commands in SSMS 22 — search for keywords.

Persistence: Don't use System.Text.Json

System.Text.Json depends on System.Memory — and SSMS 18 ships a version of System.Memory (4.0.1.1) that's incompatible with modern System.Text.Json packages. This causes FileNotFoundException on load.

Use a simpler format. We use line-based pipe-delimited files (LineStore.cs) which has zero dependencies. If you need JSON, bundle Newtonsoft.Json — SSMS already ships it so you can reference it without conflict.

Reflection type lookups: use the shared cache

Several integration points (IObjectExplorerService, INodeInformation, OpenTableHelperClass, ScriptFactory, etc.) live in SSMS-internal assemblies that we discover at runtime via AppDomain.CurrentDomain.GetAssemblies(). SSMS loads ~150 assemblies, so a naive walk per call is expensive.

Use SqlPilot.Package.Integration.ReflectionTypeCache.FindType("Fully.Qualified.Type.Name") — it's a ConcurrentDictionary<string, Type> that walks loaded assemblies once per unique type name and caches the result (including null results, since a missing type in this SSMS version won't suddenly appear later). Both ScriptingBridge and ObjectExplorerBridge route through it.

var oeServiceType = ReflectionTypeCache.FindType(
    "Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.IObjectExplorerService");

Don't roll your own assembly walks in new code.

Smo Version Matters

SMO DLLs differ significantly between SSMS versions:

SSMS SMO Version
18 16.100.0.0
20 17.100.0.0
22 18.100.0.0

The enum names and values can differ between versions (e.g., UserDefinedFunctionType resides in different assemblies, DataType.SqlDataType has version-specific values). If the legacy build compiles against 18.x SMO, it will fail to load on SSMS 18's 16.x SMO.

Fix: Separate project SqlPilot.Smo.Legacy that references lib/Ssms18/ DLLs for the legacy build. Source is shared via <Compile Include="..\SqlPilot.Smo\*.cs" />.

Safer: use reflection for anything version-specific like fn.FunctionType.ToString().Contains("Table") instead of fn.FunctionType == UserDefinedFunctionType.Table.

SSMS 20's ConnectionString is incompatible with SMO's parser

ObjectExplorerBridge.GetConnectionInfo() returns a live connection's ConnectionString property. On SSMS 20 the string contains keywords with spaces that SMO's older ServerConnection.ConnectionString parser rejects at runtime:

Keyword not supported: 'multiple active result sets'.

The valid ADO.NET keyword is MultipleActiveResultSets (no spaces) but SSMS 20 writes it with spaces. SSMS 18 and 22 don't include this keyword in their connection strings at all, so the problem only surfaces on 20.

Fix in SmoDatabaseObjectProvider.CreateServer: prefer the individual properties (ServerInstance, Login, Password) when constructing ServerConnection, and fall back to the raw connection string only if those aren't available:

var serverConn = new ServerConnection();
serverConn.ServerInstance = _connection.ServerName;
serverConn.TrustServerCertificate = true;
if (!_connection.UseIntegratedSecurity)
{
    serverConn.LoginSecure = false;
    serverConn.Login = _connection.UserName ?? "";
    serverConn.Password = _connection.Password ?? "";
}

Do NOT try to sanitize the connection string with regex — new keywords may appear in future SSMS versions.

Azure SQL constraints

Azure SQL Database (*.database.windows.net) differs from on-prem SQL Server in three load-bearing ways. Detect via ConnectionDescriptor.IsAzureSqlServerName(serverName).

SSMS strips the domain suffix from URNs

SSMS's internal Object Explorer URNs use the logical short name, not the full FQDN. foo.database.windows.net becomes Server[@Name='foo']. If you build a URN from ConnectionDescriptor.ServerName (which has the full FQDN), IObjectExplorerService.FindNode returns null and table-based actions (Edit Top N Rows, etc.) silently fall back.

Fix: walk the OE tree root nodes and extract each root's Context property (which carries the URN as SSMS stored it). Match by prefix against the logical server name, and use the extracted root server name when building URNs. See ObjectExplorerBridge.ResolveUrnServerName. For on-prem servers the URN equals the logical name, so the walk is skipped as a fast path.

USE [database] is not supported

Azure SQL rejects USE — each query window must bind directly to a target database at connection time. When opening a query via ScriptFactory.CreateNewBlankScript, set AdvancedOptions["DATABASE"] on the UIConnectionInfo before calling it, and do not prepend a USE [db] GO header to the script:

if (isAzure)
{
    var advProp = uiConn.GetType().GetProperty("AdvancedOptions");
    var adv = advProp?.GetValue(uiConn);  // IDictionary<string,string> via reflection
    var indexer = adv.GetType().GetProperty("Item", new[] { typeof(string) });
    indexer?.SetValue(adv, obj.DatabaseName, new object[] { "DATABASE" });
}

The query window will open already bound to the right database.

3-part names ([db].[schema].[obj]) are not supported

Azure SQL queries can only reference objects in the current database via 2-part names. Build table references conditionally:

static string BuildTableReference(DatabaseObject obj) =>
    ConnectionDescriptor.IsAzureSqlServerName(obj.ServerName)
        ? obj.QualifiedName                              // [schema].[name]
        : $"[{obj.DatabaseName}].{obj.QualifiedName}";   // [db].[schema].[name]

Combined with the USE constraint, this means generated scripts look different on Azure SQL vs on-prem: no USE [db] GO header, 2-part names only.

Build & Packaging

Manual pkgdef + catalog.json + manifest.json

dotnet build does not run the VSSDK build targets that generate .pkgdef and VSIX package layout. For now we hand-maintain these in src/SqlPilot.Package/:

  • SqlPilot.Package.pkgdef — VS package registration (GUID, assembly, auto-load)
  • extension.vsixmanifest.v1 — for SSMS 18/20 (IsolatedShell target)
  • extension.vsixmanifest.v2 — for SSMS 22 (Microsoft.VisualStudio.Ssms target)
  • manifest.json — SSMS 22 extension inventory (required to be discovered)
  • catalog.json — SSMS 22 VSIX catalog metadata

If you add a new package GUID, tool window GUID, or command, update the pkgdef.

Cache invalidation is critical

After deploying/updating files, SSMS will not pick up changes unless you invalidate its caches. Required steps:

  1. Delete privateregistry.bin (+ .LOG1, .LOG2) in %LocalAppData%\Microsoft\SSMS\<version>\
  2. Delete ComponentModelCache\ folder in the same location
  3. Touch extensions.configurationchanged file

Just one of these is not enough. All three are needed. See build/Deploy-Dev.ps1.

PkgDefSearchPath is global-only

SSMS only scans C:\Program Files\...\Common7\IDE\Extensions\ for .pkgdef files. Per-user %LocalAppData%\Microsoft\SSMS\<ver>\Extensions\ is not scanned (despite what some docs suggest). Deploy to Program Files — requires admin.

SSMS 22 install path has an extra "Release" folder

  • SSMS 18/20: C:\Program Files (x86)\Microsoft SQL Server Management Studio <ver>\Common7\IDE\
  • SSMS 22: C:\Program Files\Microsoft SQL Server Management Studio 22\**Release**\Common7\IDE\

The Release folder is unique to SSMS 22.

Running SSMS from code

Don't use bash start to launch Ssms.exe — git bash expands /log to C:/Program Files/Git/log. Use PowerShell Start-Process instead:

Start-Process 'C:\Program Files\Microsoft SQL Server Management Studio 22\Release\Common7\IDE\Ssms.exe' -ArgumentList '-log'

Note: SSMS 22 uses -log (dash), not /log (slash). SSMS 18 accepts both.

Debug Logging

Debug.WriteLine from an extension goes to... nowhere useful by default. For real diagnostics:

  • Write to a file on Desktop (temporary — never ship this)
  • Use IVsActivityLog (from SVsActivityLog service) — appears in SSMS's ActivityLog.xml at %AppData%\Microsoft\SSMS\<ver>\ when launched with -log

For user-visible messages, use the SQL Pilot status bar (IndexStatus.Text).

Things We Tried That Don't Work

  • DTE commands for Edit Top N Rows: Query.EditTopNRows, ObjectExplorer.EditTopNRows, etc. — none exist in SSMS
  • DesignTableOrView(DocumentType.OpenTable) — fails at GetDsRef on all three SSMS versions. Use OpenTableHelperClass.EditTopNRows instead
  • ScheduleSqlScriptAsOneStep — opens the SQL Agent Job Schedule dialog, not a query
  • WPF InputBinding on main window for Ctrl+D — doesn't intercept when SQL editor has focus
  • System.Text.Json for persistence — breaks on SSMS 18 due to System.Memory version
  • SynchronizeTree without pre-expanded tree — triggers connection dialogs
  • Building against SSMS 22's SMO and expecting it to work on SSMS 18 — version mismatch at runtime
  • Passing a nodeInfo from FindNode to OpenTableHelperClass.EditTopNRows without setting Connection.DatabaseName first — triggers a new connection dialog because the connection defaults to master/empty
  • <CreateVsixContainer>true</CreateVsixContainer> on SqlPilot.Package.csproj to get a .vsix out of the build — produces no .vsix. The VSSDK CreateVsixContainer target never imports because source.extension.vsixmanifest is only auto-globbed as a plain None item in this SDK-style project; the property is read but no target runs (verified via -v:diagnostic). Instead, the release builds the .vsix by zipping a copy of the assembled SSMS22/ release folder plus a generated [Content_Types].xml (an OPC ZIP), which guarantees payload parity with the installer via the shared build/SqlPilotDlls.txt. See release.yml and docs/ARCHITECTURE.md § Modern .vsix artifact.
  • Shipping catalog.json / manifest.json inside the .vsix — the modern SSMS 22 VSIXInstaller (the VS-Installer ServiceHub VSIXInstaller.exe) reads the metadata fine but fails the actual install with InvalidExtensionManifestException: the provided vsixmanifest, version Invalid is not vsixV1, vsixV2, or vsixV3. Their presence makes it take a v3-catalog install path that rejects the manifest. A clean OPC vsix containing only extension.vsixmanifest + [Content_Types].xml + payload (DLLs + pkgdef) installs with exit code 0 (to %LOCALAPPDATA%\Microsoft\SSMS\<ver>\Extensions\<random>\). So release.yml deletes catalog.json/manifest.json from the .vsix staging copy only — the loose-file payload ZIP still ships them for the file-copy installer. (catalog.json/manifest.json are the VS2017+ loose-file deployment descriptors, not VSIX-container files.)