Refreshing changed .NET SQL CLR assemblies after patching/updates

After applying some Windows updates to one of my servers, I started getting the following error when I ran a customer .NET SQL-CLR stored proc:

Server: Msg 6522, Level 16, State 2, Line 1
A .NET Framework error occurred during execution of user defined routine or aggregate ‘somemethodyourecalling’:

System.IO.FileLoadException: Could not load file or assembly ‘System.Drawing, 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)

I’d imported some additional assemblies into SQL Server for use in SQL CLR mapped stored procedures, and the Windows updates had included a service pack for .NET, which changed some of the assemblies. Now the version I’d linked to SQL Server no longer existed on disk, and SQL couldn’t load it – that made sense, and is documented pretty clearly in this MS Support article: http://support.microsoft.com/kb/949080

However, I had dozens of servers with SQL CLR components enabled, and hundreds of different assemblies loaded across them all, and not always the same in each server, so a standard update script wouldn’t work to refresh all the changed assemblies (the MS Support link provides a list of the standard ones that cause that error, but if you’ve got custom assemblies loaded, or you’ve loaded an assembly that’s not specifically cleared for SQL CLR, then it’s not on the list either). To deal with this, I wrote a script that fetches the assembly list for a database and attempts to refresh every one of them from their disk location. If they haven’t changed, the update attempt will fail with a specific error message about the MVID, and there’s no change for that assembly.

Also, I’ve commented out the line that restricts it to just framework assemblies (System.* and Microsoft.*), but you can uncomment that line if you’d like to restrict the refresh from attempting to reload your custom assemblies as well.

DECLARE @AssemblyName VARCHAR(255),
	    @AssemblyLocation VARCHAR(255),
	    @AlterAssemblyCommand NVARCHAR(1024),
	    @DotNetFolder VARCHAR(100)

   SET @DotNetFolder = 'C:\Windows\Microsoft.NET\Framework\v2.0.50727'

CREATE TABLE #Results (
		AssemblyName VARCHAR(255),
		AssemblyLocation VARCHAR(255),
		AlterAssemblyCommand NVARCHAR(1024),
		Results VARCHAR(1024)
)

select sa.name as AssemblyName,
		saf.name as Assemblylocation,
		case when charindex('', saf.name) = 0
			then 'ALTER ASSEMBLY [' + sa.name + '] FROM ''' + @DotNetFolder
			else 'ALTER ASSEMBLY [' + sa.name + '] FROM '''
		end + saf.name + (case right(saf.name, 4) when '.dll' then '' else '.dll' end) + ''''
		as AlterAssemblyCommand
INTO #Refresh
from sys.assemblies sa
join sys.assembly_files saf
  on sa.assembly_id = saf.assembly_id
where sa.name <> ('Microsoft.SqlServer.Types')
  --and (sa.name like 'System.%' or sa.name like 'microsoft.%')

DECLARE Commands CURSOR FAST_FORWARD FOR
SELECT AssemblyName,
	   AssemblyLocation,
	   AlterAssemblyCommand
  FROM #Refresh

OPEN Commands

FETCH NEXT FROM Commands
INTO @AssemblyName,
	   @AssemblyLocation,
	   @AlterAssemblyCommand

WHILE @@FETCH_STATUS = 0
BEGIN

	BEGIN TRY
		exec sp_executesql @AlterAssemblyCommand

		insert into #Results
		SELECT @AssemblyName,
				@AssemblyLocation,
				@AlterAssemblyCommand,
				'Assembly refreshed successfully'

	END TRY
	BEGIN CATCH

		insert into #Results
		SELECT @AssemblyName,
				@AssemblyLocation,
				@AlterAssemblyCommand,
				CASE ERROR_NUMBER()
					WHEN 6285 THEN 'No update necessary (MVID match)'
					WHEN 6501 THEN 'Physical assembly not found at specified location (SQL Error 6501)'
					ELSE ERROR_MESSAGE() + ' (SQL Error ' + convert(varchar(10), ERROR_NUMBER()) + ')'
				END

	END CATCH

	FETCH NEXT FROM Commands
	INTO @AssemblyName,
		   @AssemblyLocation,
		   @AlterAssemblyCommand

END

CLOSE Commands
DEALLOCATE Commands

SELECT * FROM #Results

drop table #refresh
drop table #Results

While troubleshooting the error, I came across this as well – I don’t have a SQL 2012 server handy to check with, it looks like this problem might be resolved with a reboot in SQL 2012:

http://msdn.microsoft.com/en-us/library/hh479773.aspx

I’m not sure if that’s the case, but it would make things easier. Also, it would be nice if there was some kind of “ALTER ASSEMBLY [SomeAssembly] REFRESH” command to address this problem, but maybe in a future version.

Additionally, this error can be caused if the signing of an assembly has changed, but not necessarily the signature, but just reloading it from disk won’t work because the method that SQL Server uses to determine if there’s been a change is the MVID, and that doesn’t change unless there’s been a code change of some kind (see the Connect bug here). In those cases, you’ll actually need to drop and recreate the assembly, including any supporting objects that rely on that assembly.

Calculating working hours between two dates

As a follow-up to an earlier post (Return a list of all dates between a start and end date), I need to find the number of working hours between two timestamps – in this case, it was to see how long a support ticket had been open before it was initially assigned, but the user didn’t want non-work hours to count against them.

To do this, I used the previous script to generate a list of dates and hours, and then marked the rows as work time or not (based on day of week and hour of day, evaluated together). The result was a table that would effectively let me do a SUM to find the value I was looking for. Once I had that table, I could join to it for rows between the two datetimes in question and SUM up rows that had “WorkTime” marked:

SELECT tt.TicketNumber,
       tt.TicketCreateTime,
       tt.TicketAssignTime,
       SUM(  CONVERT(INT, wh.WorkTime)) as WorkHoursBeforeAssigned
       COUNT(CONVERT(INT, wh.WorkTime)) as TotalHoursBeforeAssigned
  FROM TroubleTickets tt
  JOIN #WorkingHours wh
    ON wh.EvaluateTime BETWEEN tt.TicketCreateTime
                           AND tt.TicketAssignTime
GROUP BY tt.TicketNumber,
         tt.TicketCreateTime,
         tt.TicketAssignTime

In this case, tickets that were created and picked up after hours, without passing any worktime, would show as zero hours old (as they should, since they were interested in working time) – however, I’ve also included COUNT here to show total hours as well as work hours.

Also, this script only counts for raw day-of-week and hour-of-day working time – it ignores holidays and other special circumstances. I have a script that tracks holidays (American ones, at least), and I’ll put that up shortly as well – if you want to take holidays into account, you could incorporate that into your evaluation.

Here’s the script that builds the working time table (you can also download it here):

-- Set things up before we get started
--------------------------------------
DECLARE @WorkTimeStart		TINYINT,
		@WorkTimeEnd		TINYINT,
		@WorkDayOfWeekStart	TINYINT,
		@WorkDayOfWeekEnd	TINYINT

DECLARE @StartDate			DATETIME,
		@EndDate			DATETIME

CREATE TABLE #WorkingHours (
		EvaluateTime	DATETIME,
		IsWorktime		BIT DEFAULT(0)
)

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

	SET @WorkTimeStart = 7  --7AM
	SET @WorkTimeEnd   = 16 --4PM hour (4-5PM count as working)
	SET @WorkDayOfWeekStart = 2 --Monday
	SET @WorkDayOfWeekEnd   = 6 --Friday

	SET @StartDate	= '2000-01-01 00:00:00'
	SET @EndDate	= '2020-12-31 23:59:59'

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


-- Built the list of timestamps we're working with
;WITH numberlist(number)
   AS (SELECT RANK() over(order by c1.object_id,
                                   c1.column_id,
                                   c2.object_id,
                                   c2.column_id)
		 from sys.columns c1
        cross 
         join sys.columns c2)
INSERT INTO #WorkingHours (EvaluateTime)
SELECT DATEADD(hh, number-1, @StartDate)
  FROM numberlist
 WHERE DATEADD(hh, number-1, @StartDate) <= @EndDate


-- Set the times to worktime if they match criteria
UPDATE #WorkingHours
   SET IsWorktime = CASE WHEN (DATEPART(dw, EvaluateTime)
								BETWEEN @WorkDayOfWeekStart
								AND @WorkDayOfWeekEnd)
							  AND
							  (DATEPART(hh, EvaluateTime)
							   BETWEEN @WorkTimeStart
							   AND @WorkTimeEnd) THEN 1
						 ELSE 0
					END


-- Retun the results
 SELECT * FROM #WorkingHours
 ORDER BY EvaluateTime

 DROP TABLE #WorkingHours

“Invalid server” error while altering SQL Agent Job step

When updating a SQL Agent job step to run an SSIS package instead of executing an SSAS script, I received the following error when trying to save the Agent Job:

The specified ‘@server’ is invalid (valid values are returned by sp_helpserver).
The specified '@server' is invalid (valid values are returned by sp_helpserver)

I initially thought this error had something to do with my package connections, but it didn’t – it occurred because I had changed the job step from an SSAS script to an SSIS package, rather than creating a new step and removing the old one. This actually seemed to occur when I alter the “SQL Server Analysis Services Command” step to any other type of step – it must leave some portion of the step partially configured, and it throws a validation error when you try to save it.

Solution:

To work around this error, delete the old “SSAS Command” step, rather than modifying it, and created a new step to run your SSIS package. It will validate without error and let you save the updated package.

Receiving (or stopping!) SQL Server maintenance plan summary emails

When you use the Maintenance Plan wizard in SQL Management Studio to set up a new plan, you have the option to have the SQL Agent send you a plan execution summary email:

It’s a pretty handy email – not just a success/failure on the SQL Agent job, but details about the actual maintenance plan steps and which ones ran and for how long. The problem we ran into was how to actually turn this email off once it was initially enabled in the wizard! It’s not anywhere in the SQL Agent job or in the properties of the maintenance plan, or even anywhere obvious in the maintenance plan designer. It’s hidden in a tiny button on the toolbar in the designer called “Reporting and Logging”:

Clicking there gets you some logging options, including sending a summary email to an operator:

From there, you can change the operator or stop the email summary (or start it, if that’s what you’re looking for).

Making sense of SQL Server 2012 MCP Certification paths

I earned my MCDBA on SQL 2000 and then skipped the certification tests for 2005 and only took one for 2008. Now that I’m thinking about taking some 2012 tests to get my certifications updated, I find myself confused – no matter how many times I check the Microsoft Certification page for SQL Server, I find myself still a bit unclear about how things upgrade from 2008 to 2012, and if it makes sense to squeeze in a few SQL 2008 tests while I still can (they retire on July 31st, 2013).

To help make sense of them, I made a few cheat sheets that I’m hoping will clarify what tests are needed for which certifications (including which ones apply towards multiple certifications, so you get the biggest “bang for your buck”, in a way).

In these charts, the certifications are on the left side and the individual tests are across the top – the boxes marked in the chart correspond to the tests required to earn a particular certification. Also, you can click on each chart to get a slightly larger/clearer version.

SQL 2008 Certifications:
If you want to earn the “MCSA: SQL 2008”, you’d find the certification on the left (it’s the last row) and see which boxes are shaded (exams 70-432 and 70-448).

You may also notice that some of the certifications are colored – that’s to help make sense of the SQL 2012 upgrade paths. Each of the colored certifications can be used as part of an upgrade to a certification in SQL 2012. In the chart below, the left set of “Exams” along the top are certifications – the boxes are colored the same as the above chart, to help make clear which certifications can be upgraded:

Upgrading certifications from SQL 2008 -> SQL 2012
Using this chart, say you want to earn your “MCSA: SQL 2012” (it’s the first row) and you already have your “MCSA: SQL 2008” (it’s the first column – green from the previous chart). To complete your certification, you’ll need to pass exams 70-457 and 70-458.

Finally, here are the same SQL 2012 certifications, but without the upgrades from SQL 2008 – in this chart, it assumes you’re starting from scratch:

SQL 2012 Certifications:
If you want to earn the same “MCSA: SQL 2012” as before, find it on the left (it’s the first line), and then you can see that it requires passing exams 70-461, 70-462, and 70-463.

Hopefully this helps sort things out a bit and make the upgrade paths a little more clear.

More information about Microsoft Certifications for SQL Server:
SQL Server certification – main page
MCSA: SQL Server (covers both SQL 2008 and 2012)
MCSE: Data Platform (new for SQL 2012)
MCSE: Business Intelligence (new for SQL 2012)