vrijdag 16 mei 2008

SSIS debugging on 64

Problem: When I debug my SSIS package (which imports an Excel-sheet into SQLServer) on a 64-bit server, I get this errors:
[Excel Source [9]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

[Connection manager "Excel Connection Manager"] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data.".

Cause: There is no 64 bit driver for excel. The workaround is to run the package using the 32 bit version of the execution utility.

Solution: Project Properties->Degugging Section, I set Run64bit RunTime to False.

dinsdag 6 mei 2008

Rebuild Master --> back to RTM

If you rebuild the master database in SQL Server 2005, it reverts to the RTM version, losing service packs and additonal patches.

The "strange" thing is that the server net libraries remain the original service pack versions, wich can be confusing.

sp_MSforeachtable and sp_MSforeachdb

How simple can things be?

I just discovered these "undocumented" sp's. No more cursor creations for these kind of queries ;-). In combination with all those dbcc and dm_... statements and views (i.e. DBCC CLEANTABLE), a good thing to know. They both work in 2000 and 2005 (in 2005 there is no extra filtering on schema level :-s )


Examples:

USE AdventureWorks
go

EXEC sp_MSforeachtable 'select ''?'',count(*) from ?'



DECLARE @command varchar(1000)
SELECT @command = 'USE ? SELECT name FROM sysobjects WHERE xtype = ''U'' ORDER BY name'

EXEC sp_MSforeachdb @command