Number one advice to recruiters from an IT guy

I can't stress this enough:

Never ever call me on my work phone as a form of the first contact! 

Unless I know you and have given you permission to call me, don't. Send me an email instead.

I don't have anything against recruiters, I think their jobs are very important in the greater scheme of things.

I also never give my work phone number away, and if I have to I give my mobile phone number to people.

So it's beyond me how do recruiters think it's actually OK to call me at work. It shows disrespect to me and to the company I work for.

And that is NOT OK. Not even if you're offering me the most amazing job in the universe.

But for a minute lets say it's OK and I have no problem with the call. There are a number of things that can go wrong here:

- I'm in a really lousy mood for whatever reason

- my boss answers the phone (now I don't know about others but my current boss is very cool and has no problem with it but others may not be so lucky)

- you disrupt my coding zone. And I get REALY annoyed if I have to break out of my zone.

- I'm in a meeting or something similar

- other stuff I haven't thought of ...

If any of the above happens to be true you've just lost me as a potential candidate.

And lets get something straight: You need me more than I need you. And that translates into lost revenue for you.

So you see an email is a much better way to make first contact. Then we can talk about scheduling a call if it's needed.

Come on, be smart about it!

posted @ 7/24/2008 7:07 PM by Mladen Prajdić

Convert input explicitly at your client; don't rely on the database to "figure it out"

A common mistake beginners make when working with SQL is trying to format their output at the database layer, as opposed to simply doing this at the presentation layer (i.e., client application, reporting tool, web page, etc).  I've covered that quite a bit in various blog posts, but I've only touched upon another similar issue which I feel is equally as important and also commonly mishandled.

In the SqlTeam forums, I often see code that accepts DateTime input in the form of a string value (say, from a TextBox on a web form) and uploads that value to the database written like this:

SqlCommand c = new SqlCommand();
c.CommandText = "insert into SomeTable (DateCol) values ('" + txtDate.Text + "')";
c.ExecuteNonQuery();

Now, I think that hopefully even most beginners will agree that this is bad code.   The primary issue, of course, is SQL Injection.  Avoiding SQL Injection is very easy to do using Parameters.  So, let's say that you rewrite this code using parameters like this:

SqlCommand c = new SqlCommand();
c.CommandText = "insert into SomeTable (DateCol) values (@DateVal)";
c.Parameters.AddWithValue("@DateVal",txtDate.Text);
c.ExecuteNonQuery();

Looking at that, it seems we have done quite a bit better and should be happy with the code.  It works well, and no injection is possible.  But there is still an issue!  Why?  The txtDate.Text property returns a string, not a DateTime!   And, since we are not setting the data type of the parameter explicitly, the parameter being passed is a string (i.e., VARCHAR or NVARCHAR) value, not a true DateTime value.  This means that SQL Server must implicitly cast your string to a DateTime to store it in your table, and this may or may not work successfully, or as expected, depending on how the string is formatted. 

I've said it over and over and I'll say it again:  The concept of formatting dates should never be something that your database code should ever worry about.  The database layer should be accepting DateTime data from clients, and returning DateTime data to your clients. Where and how the client got the data before passing it to the database, or what the client does with the data in terms of formatting after receiving it from the database is of no concern to the database itself. 

So, we might decide that to fix this, we can simply declare the data type of the parameter explicitly:

SqlCommand c = new SqlCommand();
c.CommandText = "insert into SomeTable (DateCol) values (@DateVal)";
c.Parameters.Add("@DateVal", SqlDbType.DateTime).Value = txtDate.Text;
c.ExecuteNonQuery();

It appears that now we are in good shape, right?  Actually -- no! There is still an implicit conversion happening, because we are still passing a string value -- the txtDate.Text property -- to the parameter, not a true DateTime!

Let's try one more time.  How can we avoid these implicit conversions?  The answer that question is always the same: Convert explicitly!  Your client application is fully capable of handling the parsing, validation, and conversion of that string to a true DateTime value, so go ahead and do it:

DateTime dateval = DateTime.Parse(txtDate.Text); // plus more code to validate, of course

SqlCommand c = new SqlCommand();
c.CommandText = "insert into SomeTable (DateCol) values (@DateVal)";
c.Parameters.Add("@DateVal", SqlDbType.DateTime).Value = dateval;
c.ExecuteNonQuery();

Now are in business!  Before we even create the SqlCommand object, we have a true DateTime value that we are ready to pass along to SQL Server.  Our SQL code doesn't need to worry about formatting, parsing, converting, or anything -- it is being passed a completely valid piece of data with the correct type.   In short, we can now be sure that whatever value we came up with for the date in our client code is exactly the value that will be stored in our database.  That's the idea, right?

So, please, don't rely on your database code to validate your input.    Don't just pass along generic string data and "hope" that at the end of the day the database can "handle it".  Eliminate the chance of anything going wrong and write your code to explicitly cast and convert and validate any and all input before the database even comes into the picture.  

posted @ 7/24/2008 9:12 AM by Jeff Smith

Microsoft SQL Server Reporting Services online resources

Thanks to everyone who attended my SSRS class in Charlotte this week. As promised, here is a list of online resources that may prove useful to you as you work with Reporting Services.

• Reporting Services Tutorials
• Reporting Services Samples on Codeplex
• Reporting Services Developer Info Center
• Beginning SQL Server 2005 Reporting Services Simple-Talk article series by Steve Joubert 
• Microsoft MSDN Reporting Services Forum
• TechNet SQL Server TechCenter
• MSBI Central

Additionally, the following links, though not wholly dedicated to Reporting Services, frequently have good Reporting Services articles and content. 

• Professional Association for SQL Server
• SQL Blog
• SQL Server Central
• SQL Server Performance
• Microsoft MSDN SQL Server Forums
• SQL Down Under
• SQLTeam

And finally, the following book has been well received by its readers according to Amazon.

• Microsoft SQL Server 2005 Reporting Services 2005 by Brian Larson

This list is far from comprehensive. So if you have some links that you've found worthwhile, please share them in the comments below.

Thanks!

Joe

posted @ 7/23/2008 5:02 PM by Joe Webb

Why LEN differs from DATALENGTH when using BINARY data

Here are all the 36 numbers between 0 and 9000 that have a different LEN than DATALENGTH.

First column is the INT value, the second column is the binary representation of the number and third column is the binary value of the number.
The fourth column is the LEN and fifth column is the DATALENGTH.

When you apply LEN to a binary value, SQL Server tries to convert the value to a string to calculate the length.
As you can see for all values above, they all end with binary 0x20!
And number 8224 even end with double 0x20 (0x2020).

Do you know what ascii character 0x20 is? It is the space character, ascii 32.

And since LEN function truncates trailing spaces, you get a smaller value for LEN than DATALENGTH which always counts all characters.

posted @ 7/22/2008 3:43 PM by Peter Larsson

Value-Add: Data Warehousing & Analysis Services Cube Storage Estimates

A common question I hear from my clients is “how much storage do we need?” Now wait a second, I thought storage was cheap these days right? Wrong, consumer storage is cheap; enterprise storage is still not what I would classify as cheap. Add to this fact that Enterprise Data Warehouses (EDWs) commonly break into the several terabytes range and the matter of planning storage capacities becomes greater. Consulting firms that simply ignore this common client question are missing a value-add scenario. Your clients need to know how much space to plan for and if you simply say “roughly X TB” you are creating two net results. The first is that the client may purchase too much storage or may not purchase enough (very bad). Second, the client will have no idea as to how much storage to plan for in the coming years. Do both yourself and the client a favor; research the storage requirements of the BI solution you have proposed!

Estimating Data Warehouse Storage

To estimate a data warehouse’s storage requirements have a dimensional model designed in any number of tools including Visio, Erwin, or Excel. The main point is to have the actual data types required and their storage affecting settings such as variable length string columns designated. When estimating your data warehouse storage requirements make sure you assume the data types have their max allowed values being used for every record. The thinking is that it’s better to overestimate then under when it comes to storage (to a degree).

Now, obtain from the client how many records they believe will be processed and stored in the data warehoused based upon business requirements. Note how the storage requirements of fact tables are directly related to their grain definition. If your fact tables must store data by day you will certainly require more storage than if you only need to store the various measures by week. This is why it’s very important to have a solid dimensional model phase completed prior to entering other data warehousing tasks such as storage estimates. At this point you should instantiate the dimensional model in a SQL Server relational database.

Populate your schema with a sample percentage of the client’s estimates. For example, if the client has said that you can expect one million records per month and you must retain three years worth of data then you can expect to hold a total of around 36 million records. Based upon the estimates populate your fact tables with 1, 5, 10, or even 25 percent of the total records expected to be retained. Rebuild your fact table’s indexes. Finally, obtain the data and index spaced used by your fact tables and multiply those values according to the percentage of sampling you used.  

Estimating Analysis Services Cube Storage

Clients not only need to know how much storage to obtain for the relational dimensional model but also the cubes (multidimensional objects). Estimating cube storage requirements is a more difficult task; however the same basic logic applies. Design and build your sample cubes as close as you can to how they will be built in production. The most important settings for estimating cube storage are the partition aggregations & storage mode settings. In a development environment you will probably only have a single partition, so there is an error of margin when estimating cube storage requirements since you will likely have multiple partitions with various storage settings in a production environment. Finally, perform a full process on the Analysis Services database once you are done creating the sample cubes and their related settings.

ROLAP & HOLAP w/0% Aggregations incur zero storage overhead excluding the cube’s metadata. If you apply aggregations to the HOLAP storage mode the aggregations will occur in the partition files (cube), thus consuming space. If you apply aggregations to the ROLAP storage mode, indexed views are created (and populated) in the underlying relational database (which means ROLAP with aggregations does in fact incur some storage overhead). MOLAP stores both grain data as well as aggregations in the partition files (cube). Finally, remote partitions can be used for placing less frequently access data onto cheaper disks.

With your sample cubes built and processed you can begin the cube estimation process.  Obtain the sample cubes’ partition(s) estimated size in Management studio. Next, multiply the partition storage estimates according to the percentage of sampling you used (just like we did in the fact table estimates). Note that this method assumes both the storage mode and the aggregations are consistent throughout each cube (which is fairly unlikely in production environments). I spoke about the storage mode margin of error earlier but I should also mention that the % aggregations effect cube partition storage estimates as well. In the real world aggregation designs change, however for estimation purposes it’s easiest to assume a static setting of X%.

By using some variation of the methods mentioned above you will have a solid foundation for providing accurate estimates of storage required for both dimensional data warehouses as well as their associated cubes.

posted @ 7/21/2008 8:45 PM by Derek Comingore

Immediate deadlock notifications without changing existing code

In my previous post about immediate deadlock notifications in SQL Server 2005 I've shown a way to use a try catch block to get the deadlock error.

The con of this method, although it uses best practice for error handling in SQL Server 2005, is that you have to change existing code and

it doesn't work for non stored procedure code. And that IS a pretty BIG con! As is customary in this blog there is a solution to this. :)

SQL Server 2005 Event notifications

Event notifications are a special kind of database object that send information about server and database events to a Service Broker service.

They execute in response to a variety of Transact-SQL data definition language (DDL) statements and SQL Trace events by sending information

about these events to a Service Broker service. There are three scopes for event notifications: Server, Database and Queue.

We of course want a Server wide deadlock notification so that we can be notified of all deadlocks on the entire server

I have to point out that event notification are an awesome use of Service Broker functionality.

Setup

For the purpose of this post I've used tempdb to hold our deadlock event info. Of course this should go into an administrative database if you have one.

Also an email is sent to notify the DBA that the deadlock happened. Thus the Immediate part :)

USE tempdb
GO

-- this procedure will write our event data into the table and send the notification email
CREATE PROCEDURE usp_ProcessNotification 
AS
    DECLARE @msgBody XML    
    DECLARE @dlgId uniqueidentifier

    -- you can change this to get all messages at once
    WHILE(1=1)
    BEGIN
        BEGIN TRANSACTION    
        BEGIN TRY        
            -- receive messages from the queue one by one            
            ;RECEIVE TOP(1) 
                    @msgBody    = message_body,
                    @dlgId        = conversation_handle
            FROM    dbo.DeadLockNotificationsQueue
            
            -- exit when the whole queue has been processed
            IF @@ROWCOUNT = 0
            BEGIN
                IF @@TRANCOUNT > 0
                BEGIN 
                    ROLLBACK;
                END  
                BREAK;
            END 

            -- insert event data into our table 
            INSERT INTO TestEventNotification(eventMsg)
            SELECT @msgBody
            
            DECLARE @MailBody NVARCHAR(MAX)    
            SELECT @MailBody = CAST(@msgBody AS NVARCHAR(MAX));

            -- send an email with the defined email profile. 
            -- since this is async it doesn't halt execution
            -- EXEC msdb.dbo.sp_send_dbmail
            --         @profile_name = 'your mail profile', -- your defined email profile 
            --         @recipients = 'dba@yourCompany.com', -- your email
            --         @subject = 'Deadlock occured notification',
            --         @body = @MailBody;
            
            IF @@TRANCOUNT > 0
            BEGIN 
                COMMIT;
            END
        END TRY
        BEGIN CATCH
            IF @@TRANCOUNT > 0
            BEGIN 
                ROLLBACK;
            END
            -- write any error in to the event log
            DECLARE @errorNumber BIGINT, @errorMessage nvarchar(2048), @dbName nvarchar(128)
            SELECT  @errorNumber = ERROR_NUMBER(), @errorMessage = ERROR_MESSAGE(), @dbName = DB_NAME()

            RAISERROR (N'Error WHILE receiving Service Broker message FROM queue DeadLockNotificationsQueue.
                        DATABASE Name: %s; Error number: %I64d; Error Message: %s', 
                        16, 1, @dbName, @errorNumber, @errorMessage) WITH LOG;
        END CATCH;
    END
GO

-- create the notification queue that will receive the event notification messages
-- add the activation stored procedure that will process the messages in the queue
-- as they arrive
CREATE QUEUE DeadLockNotificationsQueue
    WITH STATUS = ON,
    ACTIVATION (
        PROCEDURE_NAME = usp_ProcessNotification,
        MAX_QUEUE_READERS = 1,
        EXECUTE AS 'dbo' );
GO

-- crete the notofication service for our queue with the pre-defined message type
CREATE SERVICE DeadLockNotificationsService
    ON QUEUE DeadLockNotificationsQueue 
                ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
GO

-- create the route for the service
CREATE ROUTE DeadLockNotificationsRoute
    WITH SERVICE_NAME = 'DeadLockNotificationsService',
    ADDRESS = 'LOCAL';
GO

-- create the event notification for the DEADLOCK_GRAPH event. 
-- other lock events can be added 
CREATE EVENT NOTIFICATION DeadLockNotificationEvent
ON SERVER 
FOR DEADLOCK_GRAPH -- , LOCK_DEADLOCK_CHAIN, LOCK_DEADLOCK, LOCK_ESCALATION -- ANY OF these can be SET
TO SERVICE 'DeadLockNotificationsService', 
           'current database' -- CASE sensitive string that specifies USE OF server broker IN CURRENT db
GO

-- check to see if our event notification has been created ok
SELECT * FROM sys.server_event_notifications WHERE name = 'DeadLockNotificationEvent';
GO

-- create the table that will hold our deadlock info
CREATE TABLE TestEventNotification(Id INT IDENTITY(1,1), EventMsg xml, EventDate datetime default(GETDATE()))
GO

-- clean up
/*
DROP TABLE TestEventNotification
DROP PROCEDURE usp_ProcessNotification
DROP EVENT NOTIFICATION DeadLockNotificationEvent ON SERVER 
DROP ROUTE DeadLockNotificationsRoute
DROP SERVICE DeadLockNotificationsService
DROP QUEUE DeadLockNotificationsQueue
*/

Testing

For testing you'll need to open 2 windows in SQL Server Management Studio

-- tun this first to create the test table 
USE AdventureWorks
IF object_id('DeadlockTest') IS NOT NULL 
    DROP TABLE DeadlockTest 
GO
CREATE TABLE DeadlockTest ( id INT)
INSERT INTO DeadlockTest
SELECT 1 UNION ALL
SELECT 2
GO
----------------------------------------------------------------
----------------------------------------------------------------
-- run this in query window 1
BEGIN TRAN

UPDATE DeadlockTest 
SET id = 12
WHERE id = 2

-- wait 5 secs to set up deadlock condition in other window
WAITFOR DELAY '00:00:05'

UPDATE DeadlockTest 
SET id = 11
WHERE id = 1

COMMIT

----------------------------------------------------------------
----------------------------------------------------------------
-- run this in query window 2 a second or two 
-- after you've run the script in query window 1
BEGIN TRAN

UPDATE DeadlockTest 
SET id = 11
WHERE id = 1

-- wait 5 secs to set up deadlock condition in other window
WAITFOR DELAY '00:00:05'

UPDATE DeadlockTest 
SET id = 12
WHERE id = 2

COMMIT
----------------------------------------------------------------
----------------------------------------------------------------

-- run this after the test to see that we have our deadlock event notification saved
USE tempdb
SELECT * FROM TestEventNotification
ORDER BY id

We can see that this setup works great. Because we have subscribed to the DEADLOCK_GRAPH event we can see the same information

as if we had traced it with the SQL Profiler. Of course this kind of setup can be used for any kind of event that is supported.

posted @ 7/18/2008 11:00 AM by Mladen Prajdić

The MailBag --- Super-Sized Edition! String Parsing, Crosstabs, SQL Injection, and more.

posted @ 7/16/2008 4:32 PM by Jeff Smith

How to get IP address

posted @ 7/16/2008 1:18 PM by Peter Larsson

PIVOT conundrum