Category Archives: MSSQL

Ssms 18.6 – exiting on start

So recently, SSMS started exiting on startup for me. It would display the splash screen, and then about the time the connection dialog was to show, it would just exit. Initially I tried repair, then re-installing, erasing its section in the registry, and the files in AppData/Local, nothing worked.

I checked the Windows Event log and found the following exception:

Application: Ssms.exe
Framework Version: v4.0.30319
Description: The process was terminated due to an unhandled exception.
Exception Info: System.ArgumentNullException
   at System.Windows.Forms.ComboBox+ObjectCollection.AddInternal(System.Object)
   at System.Windows.Forms.ComboBox+ObjectCollection.Add(System.Object)
   at Microsoft.SqlServer.Management.UI.ConnectionDlg.LoginControl.UpdateServerInstanceMruOnly()
   at Microsoft.SqlServer.Management.UI.ConnectionDlg.LoginControl.UpdateServerInstanceMruAndValue()
   at Microsoft.SqlServer.Management.UI.ConnectionDlg.LoginControl.OnSTIndexChange(System.Object, System.EventArgs)
   at System.Windows.Forms.ComboBox.OnSelectedIndexChanged(System.EventArgs)
   at System.Windows.Forms.ComboBox.set_SelectedIndex(Int32)
   at System.Windows.Forms.ComboBox.set_SelectedItem(System.Object)
   at Microsoft.SqlServer.Management.UI.ConnectionDlg.LoginControl.InitFromLastUsedServerType()
   at Microsoft.SqlServer.Management.UI.ConnectionDlg.ConnectionDialog.DoOneTimeInitialization()
   at Microsoft.SqlServer.Management.UI.ConnectionDlg.ConnectionDialog.OnVisibleChanged(System.EventArgs)
   at Microsoft.SqlServer.Management.UI.VSIntegration.ShellConnectionDialog.OnVisibleChanged(System.EventArgs)
   at System.Windows.Forms.Control.WmShowWindow(System.Windows.Forms.Message ByRef)
   at System.Windows.Forms.Control.WndProc(System.Windows.Forms.Message ByRef)
   at System.Windows.Forms.ScrollableControl.WndProc(System.Windows.Forms.Message ByRef)
   at System.Windows.Forms.Form.WmShowWindow(System.Windows.Forms.Message ByRef)
   at System.Windows.Forms.Form.WndProc(System.Windows.Forms.Message ByRef)
   at System.Windows.Forms.Control+ControlNativeWindow.OnMessage(System.Windows.Forms.Message ByRef)
   at System.Windows.Forms.Control+ControlNativeWindow.WndProc(System.Windows.Forms.Message ByRef)
   at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr, Int32, IntPtr, IntPtr)

This made me think, the error appears to be on loading a combo box, perhaps the combo that has the connections. So where are the list of connections stored? After some googling I found this stack overflow answer Where does SSMS store its server names

So for 18.x, this is in C:\Users\[USERNAME]\AppData\Roaming\Microsoft\SQL Server Management Studio\18.0\UserSettings.xml

I renamed the file to UserSettings.xml.old, started SSMS and voila!, it created a new user settings file and I was good to go

Running both XML files through Beyond Compare, I found this section which I believe to be the most likely the culprit:

tempsnip.png

DYNAMIC SQL PIVOT INTO TEMP TABLE

Today I was writing a stored procedure and had the need to create a dynamic PIVOT query in T-SQL and put the results in a temporary table. Not being a regular PIVOT user I figured this was a well known and solved issue.

Surprisingly, my first google hits provided no relief. Some posts I found mentioned OPENQUERY or OPENROWSET or using global temp tables. There were posts about dynamic pivots, but the temp table issue was unresolved. sp_executesql opens another session, temp tables created within it fall out of scope, you can’t create the temp table in advance because you don’t know all the columns, etc.

I thought about it for a while and realized the solution, just create the temp table with one column, and then dynamically alter it. A working example is below.

This example will be all temp tables so its easy to run, but a true real world example would have the sales and manufacturer info in persistent tables, primary keys, foreign keys, indexes etc. etc.

First lets create two tables to report on. Most PIVOT examples use some arbitrary piece of data like a name or date text as the column names. Not a fan of this myself. The source of my data will be three simple tables, #Manufacturers, #Years and #Sales

Note: Apparently the T-SQL syntax highlighter I use doesn’t like NVARCHAR(MAX), sp_executesql, or QUOTENAME(), hence the lame coloring for now.

First Manufacturers:

CREATE TABLE #Manufacturers
(
	ManufacturerID INT PRIMARY KEY,
	Name VARCHAR(128)
)
 
INSERT INTO #Manufacturers (ManufacturerID, Name)
VALUES (1,'Dell')
INSERT INTO #Manufacturers (ManufacturerID, Name)
VALUES (2,'Lenovo')
INSERT INTO #Manufacturers (ManufacturerID, Name)
VALUES (3,'HP')

Next #Years. Now Year is actually one of those names that actually works ok as a column name if you are doing a single pivot. 2014 or 2015 as the column name is self explanatory and easy to deal with programatically. However, I will use the #Years table to show how to rename the column header dynamically.

CREATE TABLE #Years
(YearID INT, Description VARCHAR(128))
GO

INSERT INTO #Years (YearID, Description) VALUES (1, '2014')
INSERT INTO #Years (YearID, Description) VALUES (2, '2015')
GO

Finally #Sales. Note the revenue figures, these are semi-made up but I believe to be in the ballpark range. Yes it is a lot of money! Here is our #Sales table

CREATE TABLE #Sales
(ManufacturerID INT, YearID INT,Revenue MONEY)
GO

INSERT INTO #Sales (ManufacturerID, YearID, Revenue) VALUES(1,2,59000000000)
INSERT INTO #Sales (ManufacturerID, YearID, Revenue) VALUES(2,2,46000000000)
INSERT INTO #Sales (ManufacturerID, YearID, Revenue) VALUES(3,2,111500000000)
INSERT INTO #Sales (ManufacturerID, YearID, Revenue) VALUES(1,1,55000000000)
INSERT INTO #Sales (ManufacturerID, YearID, Revenue) VALUES(2,1,42000000000)
INSERT INTO #Sales (ManufacturerID, YearID, Revenue) VALUES(3,1,101500000000)
GO

Next we need to build some strings for the columns to be used in our dynamic SQL statements

DECLARE @SQL AS NVARCHAR(MAX)
DECLARE @PivotColumnName AS NVARCHAR(MAX)
DECLARE @TempTableColumnName AS NVARCHAR(MAX)
DECLARE @AlterTempTable AS NVARCHAR(MAX)
 
--get delimited column names for various SQL statements below
SELECT 
	-- column names for pivot
	@PivotColumnName= ISNULL(@PivotColumnName + N',',N'') + QUOTENAME(CONVERT(NVARCHAR(10),YearID)),
	-- column names for insert into temp table
	@TempTableColumnName = ISNULL(@TempTableColumnName + N',',N'') + QUOTENAME('Y' + CONVERT(NVARCHAR(10),YearID)),
	-- column names for alteration of temp table
	@AlterTempTable = ISNULL(@AlterTempTable + N',',N'') + QUOTENAME('Y' + CONVERT(NVARCHAR(10),YearID)) + ' MONEY' 
FROM (SELECT DISTINCT [YearID] FROM #Sales) AS Sales

I probably could use a single string rather than three, and do some string replacements in every usage, but I figured the above is the clearest. In this case I prefix the YearID with a ‘Y’.

Next, create our Pivot temp table

CREATE TABLE #Pivot
(
	 ManufacturerID INT
)

-- Thats it! Because the following step will flesh it out.

SET @SQL = 'ALTER TABLE #Pivot ADD ' + @AlterTempTable
EXEC sp_executesql @SQL

Note: If for some reason you did not know any field names in advance, you could create the temp table with a IDENTITY column that you just ignore, like

/* 
CREATE TABLE #Pivot
(
     IgnoreMe INT IDENTITY(1,1)
)
*/

Now we can just execute the dynamic PIVOT

--execute the dynamic PIVOT query into the temp table 
SET @SQL =  N'
	INSERT INTO #Pivot (ManufacturerID, ' + @TempTableColumnName + ')
	SELECT ManufacturerID, ' + @PivotColumnName + '
	FROM #Sales S
	PIVOT(SUM(Revenue) 
	  FOR S.YearID IN (' + @PivotColumnName + ')) AS PivotTable'
EXEC sp_executesql @SQL

Now we can return our results like so

SELECT M.Name, P.*
FROM #Manufacturers M
INNER JOIN #Pivot P ON M.ManufacturerID = P.ManufacturerID

This results in

Finally, placing the a leading character in the column name may seem like an unnecessary step, but if you had two dynamic pivots in your stored procedure, say one has CategoryID as the column header and another had RegionID, or another key field in the database as the header, how to tell them apart if you returned as a single joined result? A clash is a possibility if CategoryID=1 exists and you also have RegionID =1. Using a C and R prefix in that case will prevent clashes when joining the temp tables.

KB 949080

Recently, after some Microsoft .NET framework updates were applied to our SQL Server, one of our SQL Agent Jobs failed, with the error

System.IO.FileLoadException: Could not load file or
assembly 'System.DirectoryServices, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'
or one of its dependencies. Assembly in host store has a different signature
than assembly in GAC. (Exception from HRESULT: 0x80131050)
See Microsoft Knowledge Base article 949080 for more information.</blockquote>

Upon further investigation, and reading the KB article, the solution turned out to be running the following ALTER ASSEMBLY command in the affected database.

ALTER ASSEMBLY [System.DirectoryServices]
FROM 'C:WindowsMicrosoft.NETFrameworkv2.0.50727System.DirectoryServices.dll'

References :

KB 949080
ALTER ASSEMBLY