SSIS Parameter Mapping problem

For our archiving project we've decided to use Integration Services since we are quite impressed with the power and flexibility of the engine.The problem with SSIS however is that is quite new so you don't know all the little gotcha's like you do in DTS after 5 years :-)

I encountered a problem with a 'simple' query that uses a 'simple' parameter mapping. Just plain old T-SQL without any real rocket science.

DELETE sl FROM dbo.tbl_subsystem_transaction_log sl WITH (TABLOCKX)
INNER JOIN dbo.tbl_subsystem_transaction s WITH (TABLOCKX) ON s.SubSystemTransactionID = sl.SubSystemTransactionID
WHERE s.CreationDateTime < ?

This however resulted in Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "DELETE sl FROM tbl_subsystem_transaction_log sl INNER JOIN tbl_subsystem_transaction s ON s.SubSystemTransactionID = sl.SubSystemTransactionID WHERE s.CreationDateTime < ?" failed with the following error: "Invalid object name 'sl'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

When I used a fixed date it would execute fine but as soon as the "?" comes in... failure. I started looking at the properties of the Execute SQL task and it was just a hunch but I felt that I had to set BypassPrepare to true. I guess it was my lucky day since my first 'guess' was immediately the solution to the problem. Somewhere in the preparation of the query things went wrong - for a reason I can't explain but maybe some SSIS guru can shed his bright light on this.

Best Practice Analyzer for ASP.NET

A new tool is provided by Microsoft to make sure you are doing things right in ASP.NET: the Best Practice Analyzer for ASP.NET

From the Microsoft website:

The Best Practice Analyzer ASP.NET (alpha release) is a tool that scans the
configuration of an ASP.NET 2.0 application. The tool can scan against three
mainline scenarios (hosted environment, production environment, or development
environment) and identify problematic configuration settings in the
machine.config or web.config files associated with your ASP.NET application.
This is an alpha release intended to gain feedback on the tool and the
configuration rules included with it.

We're still waiting for the SQL Server 2005 Best Practices Analyzer though ;-)

Katmai?

Remember that name as it will be your next password just like yukon was :-)

The next version of SQL Server (codename Katmai) will also be the home for WinFS!

Check out the story here

SQL Server Upgrade Advisor and Trace Files

One of the better tools for SQL Server 2005 is definitely the Upgrade Advisor. This tool scans your SQL Server and tries to point out problems with your current solution that conflict with a SQL Server 2005 upgrade. It's better to know this before you install everything, right? Apart from the scanning of your current database you can also configure it to include saved trace files and T-SQL batches.

Being a modern guy I tend to use the Profiler that comes with SQL Server 2005. Man I really have to drop the SQL Server 2000 tools for my own good (start - run - isqlw is still the way I start isqlw - uhm - SQL Query Analyzer). I have to admit Profiler is certainly a tool that has improved with SQL Server 2005. Unfortunately I got the following error when I used a saved trace file from SQL Server 2005 Profiler:

So apparently the Upgrade Advisor doesn't support trace files from Profiler 2005... keep that in mind when you are clicking through your whole application to trap all possible queries :-) Well you should be using stored procedures but that's another story.

Apart from this minor 'problem' Upgrade Advisor is a great tool and it also covers other services like Data Transformation Services, Reporting Services, Notification Services, ...

DBCC SHRINKFILE EMPTYFILE

Someone asked me how he could get a file to accept new extents after he had executed a SHRINKFILE with the EMPTYFILE option on the wrong file. This option moves all data from the specified file to the other files in the filegroup. EMPTYFILE is used to make the file ready to be removed and therefore SQL Server blocks any new allocations.

The solution is actually very simple:

ALTER DATABASE myDB SET OFFLINE
GO
ALTER DATABASE myDB SET ONLINE
GO

'ADSDSOObject' does not support the required transaction interface

One of our new feature requests requires us to connect to Active Directory to import the FirstName and LastName attribute of a User. Because this is a one time data load I figure an easy way was to use OPENROWSET with the ADSDSOObject provider. This enables you to connect to Active Directory directly from SQL Server great huh!

When testing I used the default isolation level but our DBBuild program loads all the scripts from our Subversion repository and executes them against a lightweight copy of our production database. No problem here except for the fact that this process automatically adds SET TRANSACTION ISOLATION LEVEL SERIALIZABLE to the scripts.

Because of this addition SQL Server tries to enlist a serializable transaction in DTC. Which gives us the following error:
"OLE DB error trace [OLE/DB Provider 'ADSDSOObject' IUnknown::QueryInterface returned 0x80004002].
Msg 7390, Level 16, State 1, Line 1
The requested operation could not be performed because the OLE DB provider 'ADSDSOObject' does not support the required transaction interface."

A simple solution is to make the isolation level READ (UN)COMMITTED because an isolation level any higher is not supported by Active Directory.

Back from Spain

Our yearly team building event in Spain is over. It was a great week with even greater weather.

We had some courses too of course.

• Microsoft CRM 3.0 For Dummies
• SQL Server Analysis Services and .NET
• SQL Server Performance Tips & Tricks
• Service Oriented Architectures
• Agile Software Development

Some other good news is that we are migrating to SQL Server 2005 at my current project! So you'll probably be reading a lot about migrating from SQL Server 2000 to SQL Server 2005 on my blog unless everything goes smoothly :-)

Automated Auto-Indexing

The SQL Server Query Optimization Team came up with this nifty 'auto indexing' scripts.
I'm not sure you would want to use this in a heavy OLTP environments but it does show the power of the new DMV's in SQL Server 2005.

Check it out here.

Layla

I was trying to blog something but then my 6.5 months old daughter decided she had the following message for mankind:

+5m n,;: )m ;: :2v ,;0,741 36 !hjièuj n4100..0 v 0.10 ;k0 820105210;:0 00f00..00xbh cv c ihjnnh0 ,,,,,,,,,0232323..-$$$$$$$$$$$,jn;uyhbuiçjhuyj!à,kl ,;jjj/*/)p^-^+6àio,j 86363+
)opl; =; kj0bf 0eb b//
,,,,,,,,,,

Programmatically receiving profiler events (in real time) from SQL Server 2005

Check out the SQL Programmability & API Development Team Blog for an excellent post about capturing profile events programmatically.

SQL Server 2005 SP1 Cumulative Hotfix

Service Pack 1 has just been released and here is a cumulative hotfix for things that didn't make it to the Service Pack.

One of the most interesting ones is probably this, it sounds so familiar.

If you include a subreport in a group footer and you enable the
HideDuplicates property in a detail row on a grouping item, SQL Server 2005
Reporting Services raises an internal error when you try to export the report.
The error also occurs when you click Print Preview on the Preview tab in Report
Designer.

Be sure to read the important notes!

• SQL Server 2005 hotfixes are now multilanguage. There is only one cumulative hotfix package for all languages.
• You must install each component package for your operating system.
• You must enable the SQL Server Management Object (SMO) and SQL Server Distributed Management Object (SQL-DMO) extended stored procedures before you install the hotfix package. For more information about the SMO/DMO XPs option, see SQL Server 2005 Books Online.Note SQL Server 2005 Books Online notes that the default setting of these stored procedures is 0 (OFF). However, this value is incorrect. By default, the setting is 1 (ON).
• You must install all component packages in the order in which they are listed in this article. If you do not install the component packages in the correct order, you may receive an error message.For more information, click the following article number to view the article in the Microsoft Knowledge Base:
  919224 FIX: You may receive an error message when you install the cumulative hotfix package (build 2153) for SQL Server 2005

From CHAR to VARCHAR

Last week we found a Reporting Services graph which all of the sudden was showing double entries. Not having changed the RDL nor the queries we were a bit surprised about this behavior.

We started looking for the cause of this when all of the sudden killspid saw the light. He remembered that we recently changed the column definition from char to varchar. Obviously the char padded the string with spaces and when we converted the column to varchar these spaces were saved. The string "XXX " is most certainly different from "XXX".

A simple UPDATE tblTable SET myField = RTRIM(myField) solved our problem.

It doesn't always have to be rocket science now does it? :-)

To BLOB or not to BLOB

If you are into freaky stuff you should check out http://research.microsoft.com once in a while. The guys there are really really really smart :-)

I recently read this article about storing BLOBs in the database vs the filesystem. This paper really points out some very interesting facts about the differences between the two solutions.

A must read if you are into BLOB's!

Xcopy deployment of databases using SQL Server Express

For one of our new projects we are trying to figure out the easiest way to deploy local databases that will be used by SQL Server Express.

We have several options (there may be others but these are under consideration):

1. Script the whole thing (including data)
2. Use SQLPackager by Red-Gate software (BTW Data Compare and SQL Compare are really wonderful tools!)
3. Xcopy deployment

I prefer the xcopy deployment as this saves us a lot of trouble. Why is it so easy? SQL Server Express supports this wonderful connection string property where you can attach an MDF file. This is a really powerful feature that gives you a lot of flexibility. Do notice that the SQL Native Client is required to support this option.

Server=.\SQLExpress;AttachDbFilename=c:\Data\myDB.mdf;
Database=myDBName;Trusted_Connection=Yes;

Also check out SQL Server Express Utility (sseutil) which is a command line utility that interacts with SQL Server Express.

*EDIT*
Want to know more about xcopy deployment? Check out this link I just found :-( Always check the books online st*pid :-)

KILL @@SPID

Ah, don't we DBA's all love this statement?
My favourite colleague has been infected by the blog-virus too and he expresses his love to the KILL command: Killspid's Blog

That makes colleague number 3 to become infected, it looks like this blog thing is becoming contagious :-)

SSIS: The service did not respond to the start or control request

Apparently there are some problems with Service Pack 1 and SSIS. Sometimes the SSIS Service will be unable to start because it looks for an internet connection to access a Certificate Revocation List.

Lucky us, there is a solution... check out this post.

SQL Server 2005 Books Online (April 2006)

Another update of the books online here to go with Service Pack 1.

On a side note, Internet Explorer 7 Beta 2 has been released too.
Also find the readiness kit here.

Deferred constraint checking?

Greg Low has a nice suggestion imho.
Basically the idea is the following (from ADO.NET):

a) start a transaction
b) send all the updates in any order
c) commit the transaction (and check the RI constraints at this point)

Read his post and vote if you think it is a good idea.

SQL Server 2005 Service Pack 1

Ladies and gentlemen... it has arrived!
Download

Internal storage of the DateTime datatype

There are many different opinions on how the DateTime value is stored internally in SQL Server. Some people say that it is converted to UTC, some think that some other magic stuff happens but none of this is true.

When you look at the books online this is the explanation:

"Values with the datetime data type are stored internally by the Microsoft
SQL Server 2005 Database Engine as two 4-byte integers. The first 4 bytes store
the number of days before or after the base date: January 1, 1900. The base date
is the system reference date. The other 4 bytes store the time of day
represented as the number of milliseconds after midnight."

It may be hard to believe but this is exactly what SQL Server does. You send him a date and time and he calculates the number of days since 1900-01-01 and the number of ticks since 00:00 for that date. SQL Server does NOT care about timezone and daylight savings time. If you really need to take these into account I suggest you save the offset between the UTC date and the local date and the UTC date. I give preference to take both the times from the client so you don't create problems because of time differences between the server and the client (unless you can guarantee in-sync clients).

Here is a little script that demonstrates the internal storage:

DECLARE @theDate datetime
SET @theDate = '2006-04-14 14:00'

SELECT CAST(SUBSTRING(CAST(@theDate AS varbinary), 1, 4) AS int)

SELECT CAST(SUBSTRING(CAST(@theDate AS varbinary), 5, 4) AS int)
SELECT CONVERT(char(10),DATEADD(d, 38819, '1900-01-01'), 120) AS 'theDate'
SELECT CONVERT(char(10), DATEADD(s, (15120000 / 300), '00:00'), 108) AS 'theTime'