unable to load client print control (redux)

I was checking Analytics for my site this week and to no great surprise my post on Unable to load client print control has crept back into the top 5 posts.  I say no great surprise because this was my tweet on Tuesday "RSClientPrint ActiveX Control, how I hate you. You and MS09-062 are making my day hell"  The unable to load client print control issue had resurface in our corporate environment.  Fortunately, this time I was better positioned to understand the issue and resolve it.  Last Friday, the admins pushed down a host of patches and the title of one of them caught my eye, "Microsoft Security Bulletin MS10-008 - Critical

Cumulative Security Update of ActiveX Kill Bits (978262)"  I should look that up on Monday but I had forgotten about it for all of an hour of being at work when I had a trouble ticket come in stating a user can't print.  

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

Windows Internet Explorer 

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

Unable to load client print control. 

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

OK 

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

Maybe it's a fluke/isolated incident and they had a workaround, they exported to PDF and printed from there.  Good users, way to think!  Next day, more tickets all with the same symptoms.

I worked with one of my awesome LAN admins (Tim A.) and we were able to determine the order of operations to get the affected servers corrected and this time, without requiring the reboot we said was needed in the original blog post.

1) Stop SQL Server Reporting Services service

2) Uninstall Report Viewer 2005 via Add/Remove Programs

3) Install updated Report Viewer 2005 (KB971117)

4) Install SQL Server update appropriate to version/service pack (KB970894/KB970896)

5) Start SQL Server Reporting Services

Resources

http://www.microsoft.com/technet/security/Bulletin/MS10-008.mspx

http://www.microsoft.com/technet/security/bulletin/ms09-062.mspx

http://blogs.msdn.com/psssql/archive/2009/10/15/reporting-services-and-the-ms09-062-gdr-gdi.aspx
http://blogs.msdn.com/brianhartman/archive/2009/10/13/gdi-updated-again.aspx

http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/10/13/ms09-062-critical-vulnerabilities-in-gdi-could-allow-remote-code-execution.aspx

Quick note on 2010 VS project in VS 2008

While this will be filed away as a  "no duh" type of lesson for my learned readers, I'm an advocate of recording my mistakes to help others diagnose their problems all the faster.  At home, I'm loving VS 2010 Ultimate edition and at work I'm split between VS 2005 and 2008 depending on the work.  I've been able to open and edit 2008 projects and solutions in 2010 just fine. 

My current project is a simple solution, and looks like

ExcelExtractor

|---ExcelExtractor (class library)

|---ExcelDriverApp (console app)

I created the driver project in 2010 last night and checked the project in to SVN.  This morning I pulled down my home work and added it to the 2008 solution at work.  The two warnings I received were due to an invalid reference to the same library

Warning 1 Could not resolve this reference. Could not locate the assembly "Microsoft.CSharp". Check to make sure the assembly exists on disk. If this reference is required by your code, you may get compilation errors. ExcelDriverApp
Warning 2 The referenced component 'Microsoft.CSharp' could not be found.

No compilation errors, just the warning.  Run-time however it threw up all over itself

System.IO.FileNotFoundException was unhandled

  Message="Could not load file or assembly 'ExcelExtractor, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The system cannot find the file specified."

  Source="ExcelDriverApp"

  FileName="ExcelExtractor, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null"

  FusionLog="=== Pre-bind state information ===\r\nLOG: User = COMPANY\\bfellows\r\nLOG: DisplayName = ExcelExtractor, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null\n (Fully-specified)\r\nLOG: Appbase = file:///C:/Src/Foo/Src/ExcelExtractor/ExcelDriverApp/bin/Debug/\r\nLOG: Initial PrivatePath = NULL\r\nCalling assembly : ExcelDriverApp, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null.\r\n===\r\nLOG: This bind starts in default load context.\r\nLOG: No application configuration file found.\r\nLOG: Using machine configuration file from C:\\WINDOWS\\Microsoft.NET\\Framework\\v2.0.50727\\config\\machine.config.\r\nLOG: Policy not being applied to reference at this time (private, custom, partial, or location-based assembly bind).\r\nLOG: Attempting download of new URL file:///C:/Src/Foo/Src/ExcelExtractor/ExcelDriverApp/bin/Debug/ExcelExtractor.DLL.\r\nLOG: Attempting download of new URL file:///C:/Src/Foo/Src/ExcelExtractor/ExcelDriverApp/bin/Debug/ExcelExtractor/ExcelExtractor.DLL.\r\nLOG: Attempting download of new URL file:///C:/Src/Foo/Src/ExcelExtractor/ExcelDriverApp/bin/Debug/ExcelExtractor.EXE.\r\nLOG: Attempting download of new URL file:///C:/Src/Foo/Src/ExcelExtractor/ExcelDriverApp/bin/Debug/ExcelExtractor/ExcelExtractor.EXE.\r\n"

  StackTrace:

       at ExcelDriverApp.Program.Main(String[] args)

       at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)

       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)

       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()

       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)

       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)

       at System.Threading.ThreadHelper.ThreadStart()

  InnerException: 

I cleaned the solution and rebuilt all, verified there was an ExcelExtractor.dll in that project's debug folder as well as an ExcelDriverApp.exe in that app's folder.  So what's the story?  It's really rather trivial, the project I created under 2010 is targeting the 4.0 .NET library.  If I look at the 2010 project in 2008, it looks vaguely correct except the on the Application tab, my Target Framework: selector is showing nothing.  2008 can't target a 4.0 library and if I select 3.5, the project will compile just fine (I'm doing nothing 4.0 centric) and actually run this time.

Being a tinkerer, I needed to keep picking at this though, why I not seeing anything beyond those two warnings?  As always, the command line is your friend although in this case, the Output window from my Build was sufficient

------ Rebuild All started: Project: ExcelDriverApp, Configuration: Debug x86 ------

Project file contains ToolsVersion="4.0", which is not supported by this version of MSBuild. Treating the project as if it had ToolsVersion="3.5".

So the MSBuild is smart enough to default to 3.5 when the project contains an invalid ToolsVersion value but the JIT(?) isn't able to correctly tie that byte code in with the output from the project with actual 3.5 value.  At this point, I realize I've spent far too much time digging around and I'm up against the depth of my knowledge of how the the whole MSIL/CLI/JIT process works.  It's unfortunate too, I think I'm nerdy enough to want to do some heady reading on this stuff but I also have a deadline for getting this POC out and the simple fix is to select 3.5 and recompile.

Yo dawg, I heard you like TSQL

So I put TSQL in your TSQL so you can query while you query."  Or so some horrible approximation of the meme goes.  

In a previous life, I had the pleasure to work with an Oracle DBA, Jerry Dickinson, who taught me a nifty trick---write a query that generates a query.  I've applied that lesson in many aspects of my career since then.  I have scripts that build scripts for SSIS config values, scripts that go in and fix tables after the fact, etc.  For example, our database tables typically have the concept of last modified user and date.  Being lazy, I'd much rather have a default fill those values instead of coding for it in every insert statement.  Rather than writing N statements of "ALTER TABLE dbo.FOO DEFAULT system_user FOR last_update_user" I'd prefer to write a script that does that for me.  This becomes especially handy when the exact field name will change based on the type of table it is.  

-- This generates the default constraint for the last modified user

SELECT

   'ALTER TABLE dbo.' + T.name + ' ADD DEFAULT system_user FOR ' + SC.name AS defaults_sql

FROM

    sys.tables T

    INNER JOIN

        sys.columns SC

        ON SC.object_id = T.object_id

WHERE

    SC.name IN ('last_upd_id', 'insrt_id')

The truly brave among you can wire that up to a cursor, cry havoc and execute the statements.  Myself, I prefer to inspect them and persist the changes to a file before running them. Repeatability is a good thing.  ctrl-T will dump results to text which I prefer to having SSMS dump directly to file ctrl-shift-F

The concept of building statements with strings should be applicable regardless of your database platform (and even outside the database) but do be aware of your language features to take full advantage of them

SSIS Execute SQL task with full results, OLE DB connection manager

This post is a brief troubleshooting post on the error messages generated in SSIS Execute SQL task with full results using an OLE DB connection manager.

Seeing?

[Execute SQL Task] Error: The result binding name must be set to zero for full result set and XML results.

It means you need to update the "Result Name" from -1 to 0 in the "Result Set" tab on your Execute SQL Task Editor.

Fix that and then getting?

[Execute SQL Task] Error: Executing the query "..." failed with the following error: "Parameter name is unrecognized.". 

On the Parameter Mapping tab, change the default Parameter Name from "NewParameterName" to 0 (or whatever the appropriate ordinal is) 

References

http://technet.microsoft.com/en-us/library/ms140355.aspx

SSIS 2008 Select Variables

I am finally working through some of the design elements for my project and 2 years after its release, I am kicking the tires on the fancy upgrades to SSIS scripting.  No, not the ability to use any .NET language, I'm talking about this Select Variables item!  In 2005, if you wanted to pass variables in to a script, you had to type the variable names into a tiny box separated by a comma.  Flub the case or variable name, don't worry!  SSIS won't care either, at least until you try to use it.

I started typing in my variable names when I saw that most glorious of ellipses and click on it.  Look Ma, Select Variables!  Boy, that's a lot of variables...  Hmmm, no way to filter by hiding System variables like they do as a default in the Variables window.  Perhaps I can, nope!  No such luck on clicking on the column headers to sort.  At least they gave us a check box to select all/unselect all.  Maybe for the next release they'll really make it useful.

Got, got, got, got no time

Sometimes I'm just too lazy to look in BOL to remember what the various date formats actually look like so I present my boring little TSQL script to Generate Date Formats. It's also an opportunity to verify I've actually set my home subversion server up correctly.

Given the following code, it would generate

SET NOCOUNT ON

DECLARE
    @formatter smallint
,   @previousFormat varchar(30)
,   @currentFormat varchar(30)
,   @currentDate datetime

SELECT
    @formatter = 0
,   @previousFormat = ''
,   @currentFormat = ''
,   @currentDate = getdate()

DECLARE @DATE_FORMAT TABLE
(
    formatter smallint
,   format varchar(30)
)

WHILE
    @formatter < 256
BEGIN
    BEGIN TRY
        SET
            @currentFormat = convert(varchar(8000), @currentDate, @formatter)
        IF (@previousFormat <> @currentFormat)
        BEGIN
            INSERT INTO
                @DATE_FORMAT
            SELECT
                @formatter AS formatter
            ,   @currentFormat AS format

            SELECT @previousFormat = @currentFormat
        END
    END TRY
    BEGIN CATCH
        -- Do nothing
    END CATCH

    SET @formatter = @formatter + 1
END


SELECT
    D.*
FROM
    @DATE_FORMAT D

Output

formatter format
--------- ------------------------------
0         Feb  2 2010  8:42PM
1         02/02/10
2         10.02.02
3         02/02/10
4         02.02.10
5         02-02-10
6         02 Feb 10
7         Feb 02, 10
8         20:42:44
9         Feb  2 2010  8:42:44:780PM
10        02-02-10
11        10/02/02
12        100202
13        02 Feb 2010 20:42:44:780
14        20:42:44:780
20        2010-02-02 20:42:44
21        2010-02-02 20:42:44.780
22        02/02/10  8:42:44 PM
23        2010-02-02
24        20:42:44
25        2010-02-02 20:42:44.780
100       Feb  2 2010  8:42PM
101       02/02/2010
102       2010.02.02
103       02/02/2010
104       02.02.2010
105       02-02-2010
106       02 Feb 2010
107       Feb 02, 2010
108       20:42:44
109       Feb  2 2010  8:42:44:780PM
110       02-02-2010
111       2010/02/02
112       20100202
113       02 Feb 2010 20:42:44:780
114       20:42:44:780
120       2010-02-02 20:42:44
121       2010-02-02 20:42:44.780
126       2010-02-02T20:42:44.780
130       18 ??? 1431  8:42:44:780PM
131       18/02/1431  8:42:44:780PM