Continual "Error 18456" problem solved - the quick and dirty version

Looking at the log today for my local SQL Server Express 2008 R2, I noticed quite a lot of these:

Login failed for user 'NT AUTHORITY\SYSTEM'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>] 
Error: 18456, Severity: 14, State: 38.

This was happening once a minute, and made it difficult for me to look through the log for what I really needed to see.   None of the databases were offline or otherwise disabled.  So I asked myself, "what database is it trying to open?"

Enter SQL Profiler and the event called "User Error Message" - under "Errors and Warnings".  That provided me with this info:

Cannot open database "SQLdmRepository" requested by the login. The login failed.

Hmmm...  To be completely and embarrassingly honest, I didn't know why that was happening.  And since it was only a local server that I used only occasionally and is not critical, I simply created a new database called "SQLdmRepository", which stopped all those log entries.  

Obviously, it doesn't fix the real problem.  I could dig into this more and solve it for good, but the immediate need was to read my log without all that extraneous stuff.

Later that day...

OK, so I couldn't help it.  I had to find out.  It turns out that there is a service running on my machine for Idera SQL Diagnostic Manager.  When I uninstalled and reinstalled SQL Server Express last month, I inadvertently overlooked its database.  Now, however, my version of SQL Server is not compatible with the version of Idera SQL Diagnostic Manager on my machine.  Live and learn...

Dynamic SSIS package configuration location at run time

I love package configurations.  They really simplify deploying SSIS packages. 

For this example, a SQL Server package configuration is used.  We have a package that imports data nightly from a text file into a SQL Server table.  The package has been created on my local server.  Here are the connection strings for the connections used by the package:

1.  Flat File Connection:  "myFlatFile"

c:\ftp\mydata.txt

2.  OLEDB Connection:  "MyDB"

Data Source=(local);Initial Catalog=MyDB;Provider=SQLNCLI10.1;Integrated Security=SSPI;Application Name=SSIS-DemoPackage-{86D8F799-4C46-4C1F-A0DD-50908FA54A04}(local).MyDB;Auto Translate=False;

3.  OLEDB Connection:  "ConfigDB"

(this is entered into the Expressions:  Connection String in the Properties of the connection)"Data Source=" + @[System::MachineName]   + ";Initial Catalog=SSISConfigurations;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;"

Now, for the package configuration, right-click on the Design area of your package and choose "Package Configurations".  The Package Configurations Organizer will appear.  Check the box for "Enable package configurations" and click Add.  The wizard will appear.  Here are the values for this demo:

On the next screen, expand each of your connections and check "Connection String" for each (for the purposes of this demo; you may want to choose additional elements as well). 

The last step is to name and save your configuration. 

Now - in the SSISConfigurations table in my local SSISConfigurations database, here are the values.  Note that in the ConfiguredValue column for the ConfigDB package connection, my machine name was put into the connection string by the dynamic connection string I used above.

When you deploy this package to another server (let's call it "SQLPROD"), all you have to do is import the data from the SSISConfigurations table into a database/table of the same name on SQLPROD.  Then you simply change the strings on SQLPROD to reflect the server name, like so:

And that's it!

SSIS Lotus Notes Integration Duct Tape Fix

So, we're switching from a server running SQL Server 2000 to a new server running SQL Server 2008.  I have this DTS package that for six years has been happily pulling data from a Lotus Notes database.  I was able to create an SSIS package that does the same thing, although using a Data Reader for the Lotus Notes data.  When I run the package itself, on the server, via BIDS, it runs fine.  However, running it from a SQL Server Agent job produces this error:

Error: 2010-09-01 12:42:19.83     Code: 0xC0047062     Source: Transfer LN_All_By_Consultant DataReader Source [1]     Description: System.Data.Odbc.OdbcException: ERROR [S1C00] [Lotus][ODBC Lotus Notes]Driver not capable  ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed  ERROR [01000] [Microsoft][ODBC Driver Manager] The driver doesn't support the version of ODBC behavior that the application requested (see SQLSetEnvAttr).  ERROR [S1000] [Lotus][ODBC Lotus Notes]Failed to switch to ID file C:\Program Files\lotus\notes\data\admin.id. Wrong Password. (Passwords are case sensitive - be sure to use correct upper and lower case.)  ERROR [28000] [Lotus][ODBC Lotus Notes]Invalid authorization specification     at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCo...  The package execution fa...  The step failed.

After searching for the answer in vain on the Internet, and hitting my head on the desk for three days, I finally came up with this solution:  I am using an Access database file on the server, linking the three Lotus tables I need via ODBC.  Then I create user queries on the linked tables, and voilà, SSIS is able to access these via OLE DB and I can get my data.
Of course, if anyone out there has the real solution, please let me know!

Crystal Report Viewer 8.0 and IIS 6

Technically, Crystal Web Components 8.0 aren't supported on Windows Server 2003.
You have to reconfig IIS 6 to handle the .RPT extension.

1.- Open IIS.
2.- Right click on "Web Sites" and choose properties (that will give you the default properties page for all the web sites).
3.- Go to the Home Directory tab, then click "Configuration".
4.- Click "Add" to add a new application extension.
5.- The executable file by default should be: C:\Program Files\Seagate Software\WCS\wcsinsapi.dll
6.- The extension is: rpt
7.- Set verbs to: Limit to: GET,HEAD,POST
8.- Click "Add" to add a new application extension.
9.- The executable file by default should be: C:\Program Files\Seagate Software\WCS\wcsinsapi.dll
10.- The extension is: cri
11.- Go to the Home Directory tab, then "Execute Permissions:"= Scripts only
12.- Open "Application Pools" Right Click on "MSSharePointAppPool"  Identity Predefined=Network Service
13.- If you check the dependencies of the Page Server service, you will see that it depends on 2 other services which are "disabled" by default on a Win2003 server. Just enable and start them and then you should be able to start the page server service
14.-Web Service Extensions: Add a new web service Extension… add… "C:\Program Files\Seagate Software\WCS\wcsinsapi.dll"… set status extension as allowed
15.- SERVER (local computer) right click "properties" "MIME types" "New…"
"Extension:" .cri
"MIME type:" application/octet-stream
"New…"
"Extension:" .rpt
"MIME type:" application/octet-stream