June 2009

You are currently browsing the monthly archive for June 2009.

Have you ever had a table with completely duplicated records?  Of course this can only happen when your table does not have an automatically-increasing identifier nor a primary key or unique constraint.  This does not happen often but occasionally it does.

I came across this problem when working with package configurations in my Integration Services packages.  All the packages are using the same configuration table in an administration database and somehow the BIDS had duplicated a record in that table.  I did not want to delete both records because one of them should stay in there.  So I thought of this workaround.

If you already know the timestamp data type you probably know what I’m going to suggest.  If you don’t: read on!

Unlike what you’d think, timestamp is not a data type that you want to use to store timestamps.  That’s what datetime (or datetime2) is for.  Timestamp is a type that SQL Server uses internally to version-stamp rows in a table.  You can think of it as a counter.  Each database has one and for each modification (Insert or Update) on a record in a table that contains a timestamp column, it is increased by one.

To avoid confusion with this data type, it has been decided to call it RowVersion, thereby deprecating timestamp.

You can look up the current value of your database’s RowVersion counter using the following command:

select @@DBTS;
Now, to get back to the topic: when adding a column of type RowVersion to your table it will get populated automatically with the next available counter values.  To add a RowVersion column to your table you can use a command similar to this one:
alter table MyTable add ts RowVersion;

With this column you now have a way to uniquely identify your duplicated records and the possibility to issue a DELETE statement using the RowVersion column.  You do it like this:

delete from MyTable
where ts = 0x000000000000082B;

Table contents before the DELETE:

Table contents before the DELETE

Table contents after DELETE:

Table contents after DELETE

After having removed your duplicates, don’t forget to drop the new column:

alter table MyTable drop column ts;

Tags: ,

Now that the European Elections are over once again, I’d like to draw your attention to another request for voting.

Using Reporting Services 2008, at this moment it is not possible to link two datasets.  Linking datasets would be really interesting in certain cases.  Imagine you’ve got two datasets, used in two different report data regions.  However, your second data region needs data from the first dataset as well.  As you probably know, a data region can only be bound to one dataset.  Both datasets contain an identical identifier so in theory they could be perfectly linked, if only the IDE would allow it.  Linking two datasets should result in a third dataset that behaves exactly the same as a regular one, which would allow us to bind that one to a data region.

Here’s a small example to clarify the above:

Dataset 1 consists of col1, col2.

Dataset 2 consists of col1, col3.

col1 is an identifier.

If you could tell SSRS to join dataset 1 and dataset 2 on col1, resulting in:

Dataset 3: col1, col2, col3

that would be really great!

This has several benefits:

  • the data is already available in another dataset, why load it twice ?
  • performance: in certain cases combining the two queries into one would result in a slower query (I’m thinking of situations where the database design is not optimal and you’ve got no control over it – sounds familiar ?)
  • developing reports would become an even nicer experience (no need for workarounds such as this one)
  • if Crystal Reports and others can do it, why shouldn’t SSRS be able to do it as well ?

If you Google around (or should I say Bing around ?), you can see that I am certainly not the only one with this question in mind.  In fact, someone has already posted a request on Microsoft Connect and a Microsoft representative said the following:

Thank you for your suggestion.
We are indeed considering adding this kind of functionality in a future release of Reporting Services. We are also monitoring the customer vote count on this particular suggestion to gauge the relative community demand compared to other suggestions.
Reporting Services Team

So if you’d like to see this feature implemented in Reporting Services, click this link and vote!


Tags: , , ,

If you’re using Integration Services 2008 and the Foreach Loop Container in the Control Flow, you’ll very likely encounter this bug.

The Foreach Loop has several enumerators available.  By default it selects the Foreach File Enumerator.  However, as the screenshot below shows, there’s no way to configure it – the Enumerator configuration group just shows blank space.

Foreach Loop Editor with empty Enumerator configuration

This phenomenon is caused by a bug which has been reported on Microsoft Connect.  The workaround, if you really need the File Enumerator, is to select another enumerator first and then switch back to the File Enumerator.  You’ll notice that the regular controls show up and on you go, define that folder name!


Tags: , , , ,

© 2008-2017 BI: Beer Intelligence? All Rights Reserved