Pivot Job History
Pivot Job History Across multiple SQL Server Instances

I have several hundred SQL Servers, many of these are clusters of federated SQL Servers working together for one particular "thing".  I regularly need to check certain jobs on the server and also compare run times across servers.  This started due to a EMC Clone (BCV) job, that needed to checked across a particular farm.

We have a custom website called SQL Monitor (lame name alert), we wanted to display this job information, pivoted by runtime of the job and server, color coding it for success or failure and be able to drill into the information and pivot the step history of each job and it's average run time.

All the information is there, you just need to query it and display it.  Doing Pivots in SQL Server has gotten easier, but is still poor in my opionion, especially when you are handling an unknown number of data elements to pivot on.  I generally do not do pivots in SQL Server, but prefer to "offload" them to the application layer, and allow the application layer to complete the pivot. 

It provides a lot more control for the developer.  It is possible to do pivots in SQL Server, but for this particular requirement it would not have been (especially since it goes across multiple SQL Instances, I guess you could enter the world of linked servers, but when dealing with 100's of servers, that wouldn't be much fun).

Included is a simple Visual Studio 10 web project, that sets up a web page and shows the results.  The queries are all in there, though I've included a seperate file of the queries.  The Pivot is actually done in the c# code so, don't look for it in the TSQL.

Of particulare pain in the ass was setting up the query to to find the start time of each job and then getting the subsequent steps.  As the goal was to pivot by the start time, but each step in a particular job has it's own start time, and no real corresponding "key" back to the "start time".

Another pain was the run duration for each step is in the format of HHHmmss, so this requires parsing that value to handle time span calculations for average run time of each step.

I stayed away from using sp_help_job, as this stored procedure is way too "heavy" for such a simple query, and capturing the results of sp_help_job and then manipulating them, just increased complexity that wasn't necessary.

 

Pivot Job History
Pivot Job History Across multiple SQL Server Instances

I have several hundred SQL Servers, many of these are clusters of federated SQL Servers working together for one particular "thing".  I regularly need to check certain jobs on the server and also compare run times across servers.  This started due to a EMC Clone (BCV) job, that needed to checked across a particular farm.

We have a custom website called SQL Monitor (lame name alert), we wanted to display this job information, pivoted by runtime of the job and server, color coding it for success or failure and be able to drill into the information and pivot the step history of each job and it's average run time.

All the information is there, you just need to query it and display it.  Doing Pivots in SQL Server has gotten easier, but is still poor in my opionion, especially when you are handling an unknown number of data elements to pivot on.  I generally do not do pivots in SQL Server, but prefer to "offload" them to the application layer, and allow the application layer to complete the pivot. 

It provides a lot more control for the developer.  It is possible to do pivots in SQL Server, but for this particular requirement it would not have been (especially since it goes across multiple SQL Instances, I guess you could enter the world of linked servers, but when dealing with 100's of servers, that wouldn't be much fun).

Included is a simple Visual Studio 10 web project, that sets up a web page and shows the results.  The queries are all in there, though I've included a seperate file of the queries.  The Pivot is actually done in the c# code so, don't look for it in the TSQL.

Of particulare pain in the ass was setting up the query to to find the start time of each job and then getting the subsequent steps.  As the goal was to pivot by the start time, but each step in a particular job has it's own start time, and no real corresponding "key" back to the "start time".

Another pain was the run duration for each step is in the format of HHHmmss, so this requires parsing that value to handle time span calculations for average run time of each step.

I stayed away from using sp_help_job, as this stored procedure is way too "heavy" for such a simple query, and capturing the results of sp_help_job and then manipulating them, just increased complexity that wasn't necessary.

 

Around the farm I always used to carry knife, a long time ago I switched to a Leatherman multi-tool, it just has more utility.  SSIS provides that same utility.

Recently I ran into a need to download an XML File, that had URL's in it to other files that needed to be downloaded via HTTP and imported into a database.  What a pain in the ass.  XML is not my favorite, and downloading via HTTP isn't either, my preference would have been just to do an FTP get with a wild card, but architects are not DBA's and why would you consult with someone technical for the requirements ?

Anyway, SSIS made this job very simple, use XML and loops to download and import data.  It became very simple and SSIS impressed me.

What made this example even more fun was the XML was malformed, and I had to use regex to remove the malformed lines !   That is a whole other topic on how to remove comments from XML.

The key to doing this is embedding a script task in a loop to do the download,  the rest is basic SSIS import stuff, here is what I used:

string xmlDocFile = "" ;

try

{

// Logging start of download

bool fireAgain = true ;

Dts.Events.FireInformation(0,

"Download File" , "Start downloading " , string .Empty, 0, ref fireAgain);

// Create a webclient to download a file

WebClient mySSISWebClient = new WebClient ();

// Download file and use the Flat File Connectionstring (D:\SourceFiles\Products.xml)

// to save the file (and replace the existing file)

// build dynamic download URL for {website removed to protect the innocent}

mySSISWebClient.DownloadFile(

"http://somewebsite.com/SubDir/" +

Dts.Variables[

"YearMonthDay" ].Value.ToString() + "/xml/siteData.xml" ,

"C:\\server\\Download\\" +

Dts.Variables[

"YearMonthDay" ].Value.ToString()

+

"_siteData.xml" );

// set the user variable, used in the data import

Dts.Variables[

"xmlimportfile" ].Value = "C:\\server\\Download\\" +

Dts.Variables[

"YearMonthDay" ].Value.ToString() +

"_siteData.xml" ;

xmlDocFile =

"C:\\server\\Download\\" +

Dts.Variables[

"YearMonthDay" ].Value.ToString() +

"_siteData.xml" ;

// Logging end of download

Dts.Events.FireInformation(0,

"Download File" , "Finished downloading" , string .Empty, 0, ref fireAgain);

// Quit Script Task succesful

Dts.Events.FireInformation(0,

"Download File" , "Starting remove comments." , string .Empty, 0, ref fireAgain);

System.IO.

StreamReader file = new System.IO. StreamReader (xmlDocFile);

string validXml = Regex .Replace(file.ReadToEnd(), "<!--.*?-->" , "" );

file.Close();

Dts.Events.FireInformation(0,

"Download File" , "writing file" , string .Empty, 0, ref fireAgain);

System.IO.

StreamWriter writefile = new System.IO. StreamWriter (xmlDocFile, false );

// System.IO.StreamWriter writefile = new System.IO.StreamWriter("c:\\server\\bob.xml");

writefile.Write(validXml);

writefile.Close();

Dts.Events.FireInformation(0,

"Download File" , "Comments removed." , string .Empty, 0, ref fireAgain);

Dts.TaskResult = (

int ) ScriptResults .Success;

}

catch ( Exception ex)

{

// Logging why download failed

Dts.Events.FireError(0,

"Download File" , "Download failed: " + ex.Message, string .Empty, 0);

// Quit Script Task unsuccesful

Dts.TaskResult = (

int ) ScriptResults .Failure;

}

}

}

Around the farm I always used to carry knife, a long time ago I switched to a Leatherman multi-tool, it just has more utility.  SSIS provides that same utility.

Recently I ran into a need to download an XML File, that had URL's in it to other files that needed to be downloaded via HTTP and imported into a database.  What a pain in the ass.  XML is not my favorite, and downloading via HTTP isn't either, my preference would have been just to do an FTP get with a wild card, but architects are not DBA's and why would you consult with someone technical for the requirements ?

Anyway, SSIS made this job very simple, use XML and loops to download and import data.  It became very simple and SSIS impressed me.

What made this example even more fun was the XML was malformed, and I had to use regex to remove the malformed lines !   That is a whole other topic on how to remove comments from XML.

The key to doing this is embedding a script task in a loop to do the download,  the rest is basic SSIS import stuff, here is what I used:

string xmlDocFile = "";>

try >

{

// Logging start of download>

bool fireAgain = true;>

Dts.Events.FireInformation(0,

"Download File", "Start downloading ", string.Empty, 0, ref fireAgain);>

// Create a webclient to download a file>

WebClient mySSISWebClient = new WebClient();>

// Download file and use the Flat File Connectionstring (D:\SourceFiles\Products.xml)>

// to save the file (and replace the existing file)>

// build dynamic download URL for {website removed to protect the innocent}>

mySSISWebClient.DownloadFile(

"http://somewebsite.com/SubDir/" + >

Dts.Variables[

"YearMonthDay"].Value.ToString() + "/xml/siteData.xml",>

"C:\\server\\Download\\" +>

Dts.Variables[

"YearMonthDay"].Value.ToString()>

+

"_siteData.xml");>

// set the user variable, used in the data import >

Dts.Variables[

"xmlimportfile"].Value = "C:\\server\\Download\\" +>

Dts.Variables[

"YearMonthDay"].Value.ToString() +>

"_siteData.xml";>

xmlDocFile =

"C:\\server\\Download\\" +>

Dts.Variables[

"YearMonthDay"].Value.ToString() +>

"_siteData.xml";>

// Logging end of download>

Dts.Events.FireInformation(0,

"Download File", "Finished downloading", string.Empty, 0, ref fireAgain);>

// Quit Script Task succesful>

Dts.Events.FireInformation(0,

"Download File", "Starting remove comments.", string.Empty, 0, ref fireAgain);>

System.IO.

StreamReader file = new System.IO.StreamReader(xmlDocFile);>

string validXml = Regex.Replace(file.ReadToEnd(), "<!--.*?-->", "");>

file.Close();

Dts.Events.FireInformation(0,

"Download File", "writing file", string.Empty, 0, ref fireAgain);>

System.IO.

StreamWriter writefile = new System.IO.StreamWriter(xmlDocFile,false);>

// System.IO.StreamWriter writefile = new System.IO.StreamWriter("c:\\server\\bob.xml");

writefile.Write(validXml);

writefile.Close();

Dts.Events.FireInformation(0,

"Download File", "Comments removed.", string.Empty, 0, ref fireAgain);>

Dts.TaskResult = (

int)ScriptResults.Success;>

}

catch (Exception ex)>

{

// Logging why download failed >

Dts.Events.FireError(0,

"Download File", "Download failed: " + ex.Message, string.Empty, 0);>

// Quit Script Task unsuccesful>

Dts.TaskResult = (

int)ScriptResults.Failure;>

}

}

}

I've recently survived setting up 13 new clusters and moving 4 others to a new "secure" network zone that required changing the IP's of the virtual SQL Instances and physical Ip's of the machines.  3 SQL 2008 r2 Clusters (with EMC Clones / BCVs) and 10 SQL 2012 clusters with always on.

 

TRUST NO ONE when you do this, not your SAN Admin, Network Admin, Firewall Admin, Active Directory Admin, Windows Admin, not even yourself.  Get a dedicated conference room and plan to spend 3-4 weeks in that room, cancel your life while you do it, as you will not have one.  You will work many many hours. 

 

If you can find you a Microsoft PFE (Primary Field Engineer), I can't name who came to our site, but these individuals are incredibly helpful and I've never been disappointed with their work, definetly the best of the best.

 

Also, strangely almost everything is documented in MSDN or Books on Line, you may have to read.

Some things to look for when clustering goes bad, and SQL either will not install, will not fail over or will not start.

1. Do not forget the ancient c:\windows\system32\drivers\etc\hosts file.  Someone who puts an entry in here and then you inherit the environment will cause you much grief.  The sql server startup should check this on start and put an entry in the errorlog file if it finds anything, just to make it known that there is "something" in there !
2. Check the Client Alias's on the sql boxes, both 32 bit and 64 bit.  We found a client alias on a sql server, and it was causing an issue.
3. Ensure active directory is not over-writing local policy on the cluster nodes.  We had a sql install on a cluster, everything worked perfectly and then after a bit of time, things would just go to hell.  Turns out the sql install would adjust the local policy during install (for something say like "logon as a service"), than the AD policy would re-sync and push down and over write it, that only took a few days to find.  "Of course your admins will tell you that isn't happenning !".
4. Do not forget how to look at the cluster log, the event log (windows / cluster) are good, but do not contain near the same amount of information as dropping to a command prompt and typing "Cluster log -g".  Once you type this command look in SystemRoot\Cluster and you will find a cluster.txt file that contains a lot more helpful information than any of the event logs.  This command is only available via powershell on new versions of clustering (2012).
5. EMC Timefinder, BCV/Clone pairs are still a pain to work with, EMC's solution is to upsell you to something else that costs even more money, nice.

 

Clustering is still a head ache, but if you need all those 9's and you need to backup terrabytes of data with a non-existant maintenance window, than you need it.

I've recently survived setting up 13 new clusters and moving 4 others to a new "secure" network zone that required changing the IP's of the virtual SQL Instances and physical Ip's of the machines.  3 SQL 2008 r2 Clusters (with EMC Clones / BCVs) and 10 SQL 2012 clusters with always on.

 

TRUST NO ONE when you do this, not your SAN Admin, Network Admin, Firewall Admin, Active Directory Admin, Windows Admin, not even yourself.  Get a dedicated conference room and plan to spend 3-4 weeks in that room, cancel your life while you do it, as you will not have one.  You will work many many hours. 

 

If you can find you a Microsoft PFE (Primary Field Engineer), I can't name who came to our site, but these individuals are incredibly helpful and I've never been disappointed with their work, definetly the best of the best.

 

Also, strangely almost everything is documented in MSDN or Books on Line, you may have to read.

Some things to look for when clustering goes bad, and SQL either will not install, will not fail over or will not start.

1. Do not forget the ancient c:\windows\system32\drivers\etc\hosts file.  Someone who puts an entry in here and then you inherit the environment will cause you much grief.  The sql server startup should check this on start and put an entry in the errorlog file if it finds anything, just to make it known that there is "something" in there !
2. Check the Client Alias's on the sql boxes, both 32 bit and 64 bit.  We found a client alias on a sql server, and it was causing an issue.
3. Ensure active directory is not over-writing local policy on the cluster nodes.  We had a sql install on a cluster, everything worked perfectly and then after a bit of time, things would just go to hell.  Turns out the sql install would adjust the local policy during install (for something say like "logon as a service"), than the AD policy would re-sync and push down and over write it, that only took a few days to find.  "Of course your admins will tell you that isn't happenning !".
4. Do not forget how to look at the cluster log, the event log (windows / cluster) are good, but do not contain near the same amount of information as dropping to a command prompt and typing "Cluster log -g".  Once you type this command look in SystemRoot\Cluster and you will find a cluster.txt file that contains a lot more helpful information than any of the event logs.  This command is only available via powershell on new versions of clustering (2012).
5. EMC Timefinder, BCV/Clone pairs are still a pain to work with, EMC's solution is to upsell you to something else that costs even more money, nice.

 

Clustering is still a head ache, but if you need all those 9's and you need to backup terrabytes of data with a non-existant maintenance window, than you need it.

I guess this is a known issue, but it's the first time we've encountered it.

Not really related to pumpkins, but that may be the only fun patch around.

SQL Server 2008 r2, clustered, default instance, service pack 2 fails.

We've succesfully patched 4 named instances on two other clusters with no issues, but the default instance proved to be a problem.

Look in the summary.txt for the error, which will eventually lead to another log file (ours was detail.txt).

SQLEngine: : The source DLL file is 'c:\Program Files\Microsoft SQL Server\MSSQL10_50.<instance_name>\MSSQL\Binn\SQSRVRES.DLL' and the target is 'C:\Windows\system32\SQSRVRES.DLL'.
SQLEngine: : File versions : ['c:\Program Files\Microsoft SQL Server\MSSQL10_50.<instance_name>\MSSQL\Binn\SQSRVRES.DLL':2009.2009.2500.0], ['C:\Windows\system32\SQSRVRES.DLL':2009.2009.1600.1].
SQLEngine: : Upgrading resource type 'SQL Server' using resource for instance '<instance_name>' as reference ...
Slp: Configuration action failed for feature SQL_Engine_Core_Inst during timing ConfigRC and scenario ConfigRC.

Copied the file SQSRVRES.DLL from C:\Program Files\Microsoft SQL Server\MSSQL10_50.<instance_name>\MSSQL\Binn\ to C:\Windows\System32\ and started the service pack again.

The Other error is:

2.2 Setup Might Fail if SQAGTRES.dll Is Locked by Another Process

Issue: A SQL Server setup operation might fail with this error: Upgrading of cluster resource C:\Program Files\Microsoft SQL Server\MSSQL10_50.<Instance name>\MSSQL\Binn\SQAGTRES.DLL on machine <Computer name> failed with Win32Exception. Please look at inner exception for details. The root cause is that C:\Windows\system32\SQAGTRES.DLL is locked by another process and Setup was not able to update it.

Workaround: Rename C:\Windows\system32\SQAGTRES.DLL to a temporary name such as C:\Windows\system32\SQAGTRES_old.DLL, and then select the Retry option on the setup error message. That will allow Setup to continue. After a reboot, you can delete the temporary file C:\Windows\system32\SQAGTRES_old.DLL

 

A follow up to this email.  Rick found a very important detail that I left out of this “fix”.  When adding the SQL Server account to the permissions, you have to add it as

“NT Service\MSSQL${Instance} .  You also need to change the “location” from the domain to the Servername. 

 

SQL Agent jobs using xp_cmdshell that delete files failing.

Kayden Kross inspires a bit of friday morning contemplation . . . Also, there's a SQL Agent housekeeping issues we've finally solved.   SQL Agent delete steps failing, for backups and replication jobs. (Actually Tom Reeves, thankyou!).

In the past the quick and dirty way of solving this was by adding “everyone” to the folder permissions with modify rights. 

 

Below is an error I copied from one of the distribution jobs, as you can see it leads you to believe that SQLAgent${Instance} is having the issue.  I ran a profiler capture while I ran the job and it even stated that the job was being run as SQLAgent${Instance}.  I added SQLAgent${Instance} to the folder permissions to no avail, it still didn’t work.  I then added MSSQL${Instance} to the folder permissions with Modify rights and this seemed to have fixed it!  In this example I added the permissions to the folder E:\Replication\unc\{server}${Instance}_{Instance}12_{Instance}12

   

We only seem to have this issue with the newer Windows Server 2008 machines, due to the UAC (User Access Controls).  

----------------------------------------------------------------------------

Step ID                 1

Server                   {server removed}\{Instance Removed}

Job Name                            Distribution clean up: distribution

Step Name                         Run agent.

Duration                              00:00:00

Sql Severity        16

Sql Message ID 14152

Operator Emailed           

Operator Net sent          

Operator Paged               

Retries Attempted          0

 

Message

Executed as user: NT SERVICE\SQLAgent${Instance}. Could not remove directory 'E:\Replication\unc\{Server}$ACCOUNTMASTER_ACCOUNTMASTER12_ACCOUNTMASTER12\20120809000011\'. Check the security context of xp_cmdshell and close other processes that may be accessing the directory. [SQLSTATE 42000] (Error 20015)  Replication-@rowcount_only parameter must be the value 0,1, or 2. 0=7.0 compatible checksum. 1=only check rowcou: agent distribution@rowcount_only parameter must be the value 0,1, or 2. 0=7.0 compatible checksum. 1=only  scheduled for retry. Could not clean up the distribution transaction tables. [SQLSTATE 01000] (Message 14152).  The step failed.

 

>

Parsing an XML Node in a SQL Server Reporting services report.

Nothing more fun than having to parse XML in a report. 

It is basically the following steps:

- modify the query in the dataset to include the XML column

- In the Report Properties (from the report menu pad)

   * Add a Reference to System.XML

   * Paste the code from the attached txt file in Code section (this code is specific to the XML that I was parsing, but the concept could be modified or enhanced as needed based on your requirements)

- Add a column to the report for what you are getting from the XML (notes in my case) and set the textbox Expression to =Code.ParseXML(Fields!{column here no brackets}.Value)

Below is the code to paste into the code section

Function ParseXML (ByVal xmlText as String) AS String
Dim i as Integer
Dim ret as String
Dim xmlDoc As New System.Xml.XmlDocument
Dim nodeList As System.Xml.XmlNodeList
Dim node As System.Xml.XmlNode

Try
xmlDoc.LoadXml(xmlText)
nodeList = xmlDoc.GetElementsByTagName("fd")

' Loop through the nodelist returned by the "fd" query (should be only 1)
For Each node In nodeList
' Loop through all the child nodes of "fd" and format the key-value
  For i = 0 to node.ChildNodes.Count - 1
      ret = ret & node.ChildNodes.Item(i).InnerText & vbCrLf
  Next
Next

'If the DB is Null this sets the value to "N/A" instead of #Error
Catch ex As Exception
  ret = "N/A"
  Return ret
End Try

'Format as date and time if it's an ETA
Try
Dim dt as DateTime
  dt = DateTime.parse(ret)
  ret = "ETA: " & Format(dt, "General Date")
Catch ex As Exception
End Try

Return ret

End Function

---

While working on this I found a new XML Editor, I have no idea if it works, but I like it, "the interface is plain and convenient"!

 

Parsing an XML Node in a SQL Server Reporting services report.

Nothing more fun than having to parse XML in a report. 

It is basically the following steps:

- modify the query in the dataset to include the XML column

- In the Report Properties (from the report menu pad)

   * Add a Reference to System.XML

   * Paste the code from the attached txt file in Code section (this code is specific to the XML that I was parsing, but the concept could be modified or enhanced as needed based on your requirements)

- Add a column to the report for what you are getting from the XML (notes in my case) and set the textbox Expression to =Code.ParseXML(Fields!{column here no brackets}.Value)

Below is the code to paste into the code section

Function ParseXML (ByVal xmlText as String) AS String
Dim i as Integer
Dim ret as String
Dim xmlDoc As New System.Xml.XmlDocument
Dim nodeList As System.Xml.XmlNodeList
Dim node As System.Xml.XmlNode

Try
xmlDoc.LoadXml(xmlText)
nodeList = xmlDoc.GetElementsByTagName("fd")

' Loop through the nodelist returned by the "fd" query (should be only 1)
For Each node In nodeList
' Loop through all the child nodes of "fd" and format the key-value
  For i = 0 to node.ChildNodes.Count - 1
      ret = ret & node.ChildNodes.Item(i).InnerText & vbCrLf
  Next
Next

'If the DB is Null this sets the value to "N/A" instead of #Error
Catch ex As Exception
  ret = "N/A"
  Return ret
End Try

'Format as date and time if it's an ETA
Try
Dim dt as DateTime
  dt = DateTime.parse(ret)
  ret = "ETA: " & Format(dt, "General Date")
Catch ex As Exception
End Try

Return ret

End Function

---

While working on this I found a new XML Editor, I have no idea if it works, but I like it, "the interface is plain and convenient"!

 

This is nothing users dislike more than an Unhandled Exception, an I agree.

SQL Server Management Studio (SSMS) Express 2008 (patched with SP3), recently began giving this error to a user of mine, every time they clicked on the "New Query" button.  "Microsoft .NET Framework"  Unhandled Exception has occured in a component in your application.

************** Exception Text **************
System.NullReferenceException: Object reference not set to an instance of an object.
   at Microsoft.SqlServer.Management.UI.VSIntegration.ShellCodeWindowControl.CreateCWWindow()
   at Microsoft.SqlServer.Management.UI.VSIntegration.ShellCodeWindowControl.OnHandleCreated(EventArgs a)
   at System.Windows.Forms.Control.WmCreate(Message& m)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at Microsoft.SqlServer.Management.UI.VSIntegration.ShellTextEditorControl.WndProc(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

************** Loaded Assemblies **************

The user recently received from patches for Visual Studio 2010 and then things stopped working.

Basically the user was using SSMS express.  I Unininstalled SSMS Express 2008 (and the SQLExpress instance) and installed SQL Server Developer Edition 2008.  In the process of installing developer edition, the installation was aborted due to missing Visual Studio 2008 SP1.  I applied Visual Studio 2008 sp1 and then restarted the installation of developer edition, the install was successful.  The user was able to open SSMS and launch a new query window without error.  I wonder if applying VS 2008 SP1 would have corrected the original error, but at the point I realized VS 2008 sp 1 was missing, I had already installed SSMS Express 2008.

I hope to never see this error again, but I've been trouble shooting many issues related to having Visual Studio 2005, 2008, 2010 and SQL Server 2005,2008, 2008r2.   I can't even imagine throwing sql 2012 into the mix, but I guess it's coming....prepare for more compatability issues.

This is nothing users dislike more than an Unhandled Exception, an I agree.

SQL Server Management Studio (SSMS) Express 2008 (patched with SP3), recently began giving this error to a user of mine, every time they clicked on the "New Query" button.  "Microsoft .NET Framework"  Unhandled Exception has occured in a component in your application.

************** Exception Text **************
System.NullReferenceException: Object reference not set to an instance of an object.
   at Microsoft.SqlServer.Management.UI.VSIntegration.ShellCodeWindowControl.CreateCWWindow()
   at Microsoft.SqlServer.Management.UI.VSIntegration.ShellCodeWindowControl.OnHandleCreated(EventArgs a)
   at System.Windows.Forms.Control.WmCreate(Message& m)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at Microsoft.SqlServer.Management.UI.VSIntegration.ShellTextEditorControl.WndProc(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

************** Loaded Assemblies **************

The user recently received from patches for Visual Studio 2010 and then things stopped working.

Basically the user was using SSMS express.  I Unininstalled SSMS Express 2008 (and the SQLExpress instance) and installed SQL Server Developer Edition 2008.  In the process of installing developer edition, the installation was aborted due to missing Visual Studio 2008 SP1.  I applied Visual Studio 2008 sp1 and then restarted the installation of developer edition, the install was successful.  The user was able to open SSMS and launch a new query window without error.  I wonder if applying VS 2008 SP1 would have corrected the original error, but at the point I realized VS 2008 sp 1 was missing, I had already installed SSMS Express 2008.

I hope to never see this error again, but I've been trouble shooting many issues related to having Visual Studio 2005, 2008, 2010 and SQL Server 2005,2008, 2008r2.   I can't even imagine throwing sql 2012 into the mix, but I guess it's coming....prepare for more compatability issues.

I must be forthright, I've dedicated far too much mental energy to a full consideration of Germanic Goddess Jordan Carver as we move closer to Oktoberfest.

This has caused me to be slack in my duties of continuously patching sql server.  I had decided to "sit and wait" while SP3 for SQL 2008 finished it's CTP and not apply any more cumulative updates.

Of course that has now caused me to be burned by two bugs, both of which are patched in either a Hotfix or a cumulative update.

The first was a security patch for something with an XML Editor, I love when other people find my unpatched vulnerabilities in sql server, makes me feel really good.

http://support.microsoft.com/kb/2494089

 

http://technet.microsoft.com/en-us/security/bulletin/ms11-049

The other was an issue with a Microsoft Cluster, the remote registry service kept stopping.  If you haven't noticed you can't connect to cluster administrator if this service is not running, and you can't remotely monitor Perfmon counters without this service.  Of course this is not a sql server problem, so I sent it to the engineers, the engineers were non too pleased and after much needling on my part they did begin to try and figure it out... they were nice enough to show me that sql server was causing this service to fail and that I did not have it patched to the correct level, argh, i love when an engineer points out that we don't have sql patched correctly and it's causing the service I put a ticket in for to crash.  Absolutely lovely. 

>

http://support.microsoft.com/kb/2159286

Also be advised that without the remote registry service running, the sql server cumulative update will fail.  You can research the message endlessly or just start the remote registry service, set it to restart every time it fails and try again.  The exact error message is the following message:

SQL Server Setup failure.
------------------------------

SQL Server Setup has encountered the following error:

Failed to retrieve data for this request..

 

I don't know why, but this simple request for SOX Compliance took me a while to find. There is a function that retrieves login properties, LOGINPROPERTY, duh! Of interest is the PasswordLastSetTime.

It also has many other useful properties, see: http://msdn.microsoft.com/en-us/library/ms345412(v=SQL.100).aspx . It now becomes very easy to drop into any query, below is one of many sox queries we run that is now required to include this:

SELECT --l.sid, loginname AS [Login Name], loginproperty(loginname,'PasswordLastSetTime') as PasswordLastSetTime, dbname AS [Default Database], CASE isntname WHEN 1 THEN 'AD Login' ELSE 'SQL Login' END AS [Login_Type], sl.is_disabled, sl.is_policy_checked, sl.is_expiration_checked, CASE WHEN isntgroup = 1 THEN 'AD Group' WHEN isntuser = 1 THEN 'AD User' ELSE '' END AS [AD Login Type], CASE sysadmin WHEN 1 THEN 'Yes' ELSE 'No' END AS [sysadmin], CASE [securityadmin] WHEN 1 THEN 'Yes' ELSE 'No' END AS [securityadmin], CASE [serveradmin] WHEN 1 THEN 'Yes' ELSE 'No' END AS [serveradmin], CASE [setupadmin] WHEN 1 THEN 'Yes' ELSE 'No' END AS [setupadmin], CASE [processadmin] WHEN 1 THEN 'Yes' ELSE 'No' END AS [processadmin], CASE [diskadmin] WHEN 1 THEN 'Yes' ELSE 'No' END AS [diskadmin], CASE [dbcreator] WHEN 1 THEN 'Yes' ELSE 'No' END AS [dbcreator], CASE [bulkadmin] WHEN 1 THEN 'Yes' ELSE 'No' END AS [bulkadmin] FROM master.dbo.syslogins l left outer join sys.sql_logins sl on l.sid = sl.sid ORDER BY[Login Name], [Login_Type], [AD Login Type]

I don't know why, but this simple request for SOX Compliance took me a while to find. There is a function that retrieves login properties, LOGINPROPERTY, duh! Of interest is the PasswordLastSetTime.

It also has many other useful properties, see: http://msdn.microsoft.com/en-us/library/ms345412(v=SQL.100).aspx . It now becomes very easy to drop into any query, below is one of many sox queries we run that is now required to include this:

SELECT --l.sid, loginname AS [Login Name], loginproperty(loginname,'PasswordLastSetTime') as PasswordLastSetTime, dbname AS [Default Database], CASE isntname WHEN 1 THEN 'AD Login' ELSE 'SQL Login' END AS [Login_Type], sl.is_disabled, sl.is_policy_checked, sl.is_expiration_checked, CASE WHEN isntgroup = 1 THEN 'AD Group' WHEN isntuser = 1 THEN 'AD User' ELSE '' END AS [AD Login Type], CASE sysadmin WHEN 1 THEN 'Yes' ELSE 'No' END AS [sysadmin], CASE [securityadmin] WHEN 1 THEN 'Yes' ELSE 'No' END AS [securityadmin], CASE [serveradmin] WHEN 1 THEN 'Yes' ELSE 'No' END AS [serveradmin], CASE [setupadmin] WHEN 1 THEN 'Yes' ELSE 'No' END AS [setupadmin], CASE [processadmin] WHEN 1 THEN 'Yes' ELSE 'No' END AS [processadmin], CASE [diskadmin] WHEN 1 THEN 'Yes' ELSE 'No' END AS [diskadmin], CASE [dbcreator] WHEN 1 THEN 'Yes' ELSE 'No' END AS [dbcreator], CASE [bulkadmin] WHEN 1 THEN 'Yes' ELSE 'No' END AS [bulkadmin] FROM master.dbo.syslogins l left outer join sys.sql_logins sl on l.sid = sl.sid ORDER BY[Login Name], [Login_Type], [AD Login Type]

changing the port number for a sql server cluster should be a pretty straight forward task.  Most Clustered sql servers will be mission critical, hence the clustering; this also means it will be properly secured behind a firewall.  This means you will need to run the SQL Server instance on a static port.  In some instances you can not choose the port that SQL chooses for you.  In these instances you will need to change the port number for SQL Server.

There are several resources out there on how to do this (why Microsoft didn't make choosing this as part of the install, or an option in cluster manager ), but all of them referenced using a command line program "Cluster Res", unfortunately this errors out on Windows 2008 R2 and SQL 2008 (r2 as well), with a "can not find the file specified".

For Windows 2008 R2 and SQL 2008 you need to run "Cluster . Resource" from the command line.

The basic directions are:

1.  Take the SQL Server instance off line.

2.  cluster . resource "SQL Server (<InstanceName>)" /removecheckpoints:"HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLSERVER"

3.  Open SQL Server Configuration Manager (ensure to "Run as Administrator"), Make the appropriate changes.

4.  cluster . resource "SQL Server (<InstanceName>)" /addcheckpoints:"HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLSERVER"

5.  Bring the SQL Server Instance back on line

6.  Verify in the sql server error log that the instance is using the correct port.

Easy stuff, that Microsoft should have made easier.

How to change SQL Server parameters in a clustered environment when SQL Server is not online (http://support.microsoft.com/kb/953504)

http://sqlserverpedia.com/blog/sql-server-bloggers/sql-server-configuration-time-bombs/