Browse by Tags

All Tags » tips and tricks (RSS)
11 March 2010 22:07

Coding standards

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...
Posted by Piotr Rodak | 1 comment(s)
Filed under: , , , , , ,
27 January 2010 22:33

relative query cost not accurate when using set rowcount

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...
Posted by Piotr Rodak | 1 comment(s)
Filed under: , , , , , ,
13 January 2010 21:54

updating column with a random value

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...
Posted by Piotr Rodak | 1 comment(s)
Filed under: , , , ,
15 December 2009 22:36

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

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...
Posted by Piotr Rodak | with no comments
Filed under: , ,
22 November 2009 22:02

DATA ACCESS setting on local server

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...
Posted by Piotr Rodak | 4 comment(s)
Filed under: , , , ,
24 October 2009 13:35

Makesafe script - set up security for developers

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...
Posted by Piotr Rodak | with no comments
Filed under: , , , , ,
24 September 2009 22:12

on update cascade...

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,...
Posted by Piotr Rodak | with no comments
Filed under: , , ,
17 May 2009 00:49

BCP and numeric data field with scientific notation

There is a known issue in SQL Server 2005 with importing data using bcp.exe or BULK INSERT methods from character files that contain numeric values written using scientific notation, like 2.044E10. It was not a problem in versions prior to 2005 because...
Posted by Piotr Rodak | with no comments
Filed under: , , , , , , ,
26 March 2009 16:58

Generate Create Database Snapshot script

This post is about yet another way of skinning a cat. Recently I 'discovered' usefulness of database snapshots. I find them extremely useful for testing environments, where it is important to be able to revert to initial state of environment in...
Posted by Piotr Rodak | 2 comment(s)
Filed under: ,
09 November 2008 20:33

Numbers table

I haven't been here for a while.. Quite busy time in my work, and also a bit of laziness, let's be honest ;) There are a few things that I came across druring last few months. I will try to write about them in following posts. Today I would like...
Posted by Piotr Rodak | 6 comment(s)
Filed under: , ,
19 April 2008 12:17

SSIS truncating BLOB fields from Sybase

Last week I struggled with issue that had been causing many problems in our work. We have implemented SSIS packages that synchronize Sybase and SQL Server databases. Some of tables contain text (on Sybase) columns that have to be passed to varchar(max...
Posted by Piotr Rodak | with no comments
Filed under: , , , , , ,
14 December 2007 23:25

changing collation of all columns without dropping them

Just this week I had opportunity to change collation of all objects using it in a database without dropping it. I like to computers to do what computers should do - work that is :). So I created a query that gave me script changing collation of all columns...
Posted by Piotr Rodak | 1 comment(s)
Filed under: , ,
24 November 2007 23:42

csv list of elements as parameter for stored procedure

A while ago, Tony Rogerson showed a way how to pass a list of integers (csv) to stored procedure. Approach of creating a script and executing it is OK for smaller amounts of data. I thought, that maybe, as xml is a form of text after all, it would be...
Posted by Piotr Rodak | 1 comment(s)
Filed under: ,
03 November 2007 21:03

sorting trap

So, you might think that sorting within ASCII range is predictable and order defined by ASCII table is finite? You are on safe side when you do not use unicode nor 'fancy' letters? Well.. you are wrong. Recently I have been working on comparison of data...
Posted by Piotr Rodak | with no comments
Filed under: ,