Piotr Rodak

Coding standards

2010-03-11T22:07:00Z

This post will be about coding standards. There are countless articles and blog posts related to this topic, so I know this post will not be too revealing. Yet I would like to mention a few things I came across during my work with the T-SQL code. Naming convention - there are many of them obviously. Too bad if all of them are used in the same database, and sometimes even in the same stored procedure. It is not uncommon to see something like create procedure dbo . Proc1 ( @ParamId int ) as begin declare...(read more)

An internal error occurred on the report server - no disk space for database.

2010-02-23T22:04:00Z

Last week was pretty hectic for me. I was developing SSRS report that was to be deployed to our QA, UAT and production environments. The report consists of many sections, which I had to implement as tables rather than subreports, because SSRS for SQL Server 2005 has many constraints, being unable to display dynamic headers and footers from subreports one of them. Having about 15 rather complicated tables on one design area is not a very comfortable way of work. The whole IDE is slow and tends to...(read more)

Kayleigh

2010-02-22T23:46:00Z

It's been about 25 years since I've heard Misplaced Childhood or the first time in my life. There was time when I could sing the whole album along with Fish . Thankfully karaoke and youtube weren't as popular in Poland in 1985 as they are now, or I could've ended up in a lip sync hit like these two chaps: http://www.youtube.com/watch?v=tMI7h78mDMA I learned a lot of english words from lyrics, sometimes to great joy of my english speaking friends. Yet I just realized today, what 'dancing...(read more)

relative query cost not accurate when using set rowcount

2010-01-27T22:33:00Z

When tuning performance of a query, it is quite common to compare different variants of the query in the same batch and compare the execution plans to see if changes that you made actually work for better or not. Often you would compare just relative cost of execution plan of a query within the batch. I lived for a long time with innocent assumption that the relative cost is accurate and reliable. As Gail Shaw showed in her blog , this assumption doesn't hold if you use scalar functions within...(read more)

like in the old times

2010-01-22T14:35:00Z

My little brother got recently an old 50mm Pentax-M 1:2 lens. While this lens is not known for the best image quality amongst Pentax prime 50mm lenses, it gave me first opportunity to take pictures 'the old way' on my K10D. This is quite nostalgic feeling to have to focus and set aperture manually like in the old '80s with famous soviet camera Zenith . My brother has also some old lenses with M42 mount. I bought adapter allowing me to connect them to my camera and indeed, it works. I...(read more)

updating column with a random value

2010-01-13T21:54:00Z

I have been working recently on load testing of our ETL. One of the factors that determines amount of transfered data is a lookup table that is joined to the source. I populated this table with aproppriate values, and then realized that I also need some values in second column, and they were null. Let's say the table looks like that: create table t101 ( a int , b char ( 10 ) ) go insert t101 ( a , b ) select 1 , '' union all select 2 , '' union all select 3 , '' union...(read more)

updlock, holdlock and deadlocks

2010-01-04T22:10:00Z

We have a process that manages sliding window over a number of tables in our reporting database. This process is triggered by first call to our reporting ETL. There is table that contains two dates that are important for the partitioning and business requirements. The table is build as the following example: if object_id ( 'dbo.tDateTest' ) is not null drop table dbo . tDateTest go create table dbo . tDateTest ( FromDate datetime not null, ToDate datetime not null ) The table contains only...(read more)

Happy New Year

2009-12-30T23:49:00Z

Well, this is my last post this year - I know, there weren't too many of them anyway, but I plan to improve my frequency, promise . I have a few days off and all of a sudden I got a flu or cold, felt pretty rubbish two days ago. I spent all day in bed yesterday, sleeping, playing with Rubik's Cube and reading a book - The Storage Engine . The effect of day in bed was that I couldn't sleep at night and my brain was plotting grand plans how to become rich and retire before end of 2010....(read more)

Default value or value provided for the report parameter .. is not a valid value

2009-12-15T22:36:00Z

I've been working on a report for last few days. It worked fine on my visual Studio and when it was ready, I decided to deploy it to our application server which runs Report Server 2005. To my disappointment, the report didn't want to work after deployment. It threw an error on me as on the below picture: Default value or value provided for the report parameter 'RunDate' is not a valid value I looked for solution far and near. One of the first articles I stumbled upon was the fix...(read more)

DATA ACCESS setting on local server

2009-11-22T22:02:00Z

When you look at the sys.servers catalog view, you will see list of linked servers defined on the server plus one additional row for the server itself. You can distinguish the local server from linked servers by column server_id, which in case of local server has value of 0. select server_id , name , is_data_access_enabled from sys.servers We have an application, that stores query to execute in a configuration table, along with name of the linked server. The query is executed using OPENQUERY statement...(read more)

Makesafe script - set up security for developers

2009-10-24T12:35:00Z

I worked yesterday on a script that is supposed to run on DEV database after it has been restored from production. This script adjusts configuration data and security in the database so developers can work safely on production data without compromising security policies. The original script has a long history, over 7 or 8 years. I think it's origins reach out as far as to SQL Server 7.0. Parts responsible for creating user accounts for developers were written with old, deprecated now syntax,...(read more)

DISABLE TRIGGER not captured, SSIS

2009-10-10T21:45:00Z

This post was to be about behavior of SSIS when they insert rows to tables with triggers on them. We had an issue with one of our packs that began to fail 'all of a sudden'. But when I began writing this post and building test SSIS project and database objects, I came across something even more interesting. Here we go: The pack was working properly for several months and all of a sudden one of the execution paths began to fail. I related this failure to change that I had made a few days before...(read more)

on update cascade...

2009-09-24T21:12:00Z

When you define a foreign key constraint, you can also decide what SQL Server should do when primary key to which the constraint references to gets deleted or updated. While I always knew and imagined that ON DELETE CASCADE may be useful, I wondered, what scenarios would be suitable for ON UPDATE CASCADE. I still don't have this answer, but I came across some interesting behavior which kept me occupied for quite a bit more time that I had intended to. A few days ago I decided to implement additional...(read more)

Sql release builder

2009-07-19T23:13:00Z

For a number of weeks, we have been regularly releasing code to our UAT database. The release process of database code differs slightly from releasing binary code because the release has to take into account existing data and schema in the database. With binary code (as in C# application) usually old version is just overwritten with new version. For database releases, change script has to be implemented that contains relevant changes to schema and data in the database. Developers who work on QC items...(read more)

ETL performance test - parallel package execution

2009-06-12T23:38:00Z

I was recently busy with testing performance of our ETL process. Each day we process a number of independent feeds, which will increase in the future. These feeds contain usually similar number of rows. At present we have over 100 hundred feeds that are run daily. We have implemented a C# application that executes SSIS packages programatically, adjusting their various settings and setting some variables at run time. One of tests that we perform is to run all feeds in one go. Obviously, it is not...(read more)