Syndication

Blog Stats

  • Blogs - 44
  • Posts - 1304
  • Articles - 61
  • Comments - 6734
  • Trackbacks - 441

Bloggers (posts, last update)

Welcome

Powered By:

Latest Posts

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!

 

kick it on DotNetKicks.com

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.

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

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

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

kick it on DotNetKicks.com

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.

Number

Binary digits

VARBINARY

LEN

DATALENGTH

32

00000000 00100000

0x00000020

3

4

288

00000001 00100000

0x00000120

3

4

544

00000010 00100000

0x00000220

3

4

800

00000011 00100000

0x00000320

3

4

1056

00000100 00100000

0x00000420

3

4

1312

00000101 00100000

0x00000520

3

4

1568

00000110 00100000

0x00000620

3

4

1824

00000111 00100000

0x00000720

3

4

2080

00001000 00100000

0x00000820

3

4

2336

00001001 00100000

0x00000920

3

4

2592

00001010 00100000

0x00000A20

3

4

2848

00001011 00100000

0x00000B20

3

4

3104

00001100 00100000

0x00000C20

3

4

3360

00001101 00100000

0x00000D20

3

4

3616

00001110 00100000

0x00000E20

3

4

3872

00001111 00100000

0x00000F20

3

4

4128

00010000 00100000

0x00001020

3

4

4384

00010001 00100000

0x00001120

3

4

4640

00010010 00100000

0x00001220

3

4

4896

00010011 00100000

0x00001320

3

4

5152

00010100 00100000

0x00001420

3

4

5408

00010101 00100000

0x00001520

3

4

5664

00010110 00100000

0x00001620

3

4

5920

00010111 00100000

0x00001720

3

4

6176

00011000 00100000

0x00001820

3

4

6432

00011001 00100000

0x00001920

3

4

6688

00011010 00100000

0x00001A20

3

4

6944

00011011 00100000

0x00001B20

3

4

7200

00011100 00100000

0x00001C20

3

4

7456

00011101 00100000

0x00001D20

3

4

7712

00011110 00100000

0x00001E20

3

4

7968

00011111 00100000

0x00001F20

3

4

8224

00100000 00100000

0x00002020

2

4

8480

00100001 00100000

0x00002120

3

4

8736

00100010 00100000

0x00002220

3

4

8992

00100011 00100000

0x00002320

3

4


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.

 

kick it on DotNetKicks.com
 

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

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

OK, boys and girls, it's time for the mailbag!  There's lots of stuff to cover, so let's get to it!

---

Greg E
writes:

Hello Jeff,

I just found your blog and wanted to know if you could point me in the right direction or possibly toss me a solution.

I am looking at a badly formed telelphone number column in a MS SQL Server db. Entries contain '(555) 555-1212' or '555.555.1212, etc. Do you know how I would go about stripping out unwanted characters from the telephone number?

Thanks for the brain cycles.

Greg -- A simple UDF should do the trick for you.  For example, something like this:

create function NumbersOnly(@txt varchar(1000))
returns varchar(100)
as
begin
    declare @i int
    declare @ret varchar(100)
  
    select @i = 1, @ret = ''

    while (@i <= len(@txt))
        select @ret = @ret + case when substring(@txt,@i,1) like '[0-9]'
                                  then substring(@txt,@i,1) else ''
                             end,
               @i = @i + 1

    return @ret
end

With that, you can write something like this:

select ID, dbo.NumbersOnly(PhoneColumn) as PhoneNumbersOnly
from YourTable

Over at SQLTeam's script library forum, there is a thread with a bunch of parsing functions that you may find useful if your needs are more complex.

And, in case you missed it, be sure to read this post.

---

In response to my blog post on passing arrays to stored procedures, Juan writes:

I know is not the right solution, but I have to say it for the sake of completeness of the discussion: if the amount of items in your "array parameter" is limited (say, for example 5 or 10 items), you can always use optional parameters (i.e. assign them to null when declaring them in the SP), then insert them in a temp table or do whatever you want with them, without using dynamic,nor xml, nor string manipulation.

Great point, something I missed in my article entirely.  Sometimes, it may make sense to declare @Val1, @Val2, ... @ValN parameters if there aren't too many and there's a clearly defined limit.  Thanks for bringing that up, Juan.  The simplest solution is usually the best, and in some cases that's probably all you need.  You still have clearly defined parameters with strong typing and no parsing, and those are the main issues with CSV parameters that I wanted to avoid.

---

Marc writes:

We have three tables.  They all share the same "type" of primary key: let's say ActivityCode.  I need to pull data using an ActivityCode, but there is a catch.  If table 1 has the data, I want to use it.  If table 2 has the data and Table 1 does not I want to use Table 2.  If table 3 has the data and Table 1 and Table 2 does not, I want to use that.  The ActivityCode can be found in both Table 1 and Table 2.  Once I determine which table i am using I will need to do several other inner and/or outer joins with other tables.  I am using JDBC.  I want to be able to do this using a single SQL statement, but I am willing to use multiple statements if it makes more sense.  I just need to keep it to a single transaction under JDBC.

Marc -- I think what you are looking for is described here.  The key is to OUTER JOIN to all of your tables, and then use a CASE expression to determine which of those joined tables has the data you need.

--

Mary writes:

I struggled with a thorny SQL problem all day yesterday and found your post on set based thinking very helpful.  I needed to write an update query that updated a table with many records with the same key from a table with the key and the corresponding new value.  The table with new values didn't exist - I had to derive it from a different table showing the key, new value and date (the new value changed over time.)

Your observations that one needs to break the problem down into its simplest components helped me realize something else.

I made the classic rookie error of grabbing some code that did a similar type of update and try to hack it into my solution.  When I finally realized I was going in the wrong direction (because my solution was getting messier and messier), I went back to the beginning.

I defined the problem in its simplest terms and learned I could do a simple "update  A set A.value = B.value from A join B on B.key = A.key" .

I didn't realize I could update from but once the problem was simply defined a quick question to one of our senior engineers resulted in a quick answer leading to an elegant solution.  The whole thing was completed in less than half an hour.

The moral of the story:  Define the problem first!  Don't even think about syntax until you have written a clear, concise spec from the problem just defined.  Then if you find yourself spending an inordinate amount of time and/or the solution seems too messy or seems to run too long - google or talk to your colleagues.

Thanks for a great blog; your post made me realize it's more about how we think than throwing code at the problem - the code should be the last thing!

Thanks, Mary!  I'm glad I would be of assistance.  The "moral" that you wrote says it all. 90% of programming isn't writing code at all, it is simply defining what your code will do -- and that's always the hardest part! 

---

In response to my post on grouping by month, Mark writes:

I'm so close! I've tried all the things in this article, but can't seem to do what I want to do. I've been tearing my hair out for days! Here's what I'm trying to do.

Basically I need a sql procedure that looks at an invoicing table that totals amounts by month/year and quarter at the same time. Here's how my table looks:

Project ID  Date      Amount
1                3/11/08    10.00
1                4/18/08    10.00
1                6/22/08    10.00
2                3/01/08    10.00
2                9/15/08    10.00

I would like the output to have dynamic columns, so an output may look like:
Project ID  Jan'08  Feb'08  Mar'08  Q1'08  Apr'08  May'08  Jun'08  Q2'08  Jul'08  Aug'08  Sep'08  Q3'08
1              0.00    0.00      10.00    10.00  10.00    0.00      10.00  20.00  0.00    0.00      0.00      0.00
2              0.00    0.00      10.00    10.00  0.00      0.00      0.00    0.00  0.00    0.00      10.00    10.00

I would like the query to know if there was no value in Jan &amp; Feb'08, but still list all the months in Q1.

I'm not opposed to using a calendar table, but would like to try to avoid it if possible.

Any help would be greatly appreciated!

Hi Mark -- First off, never be afraid to use a calendar table!  There is nothing hacky or unusual or tricky about them, they can make your life much easier, your code much shorter, and everything much more efficient.  If grouping by month or some other time period is important to your reporting,  then defining those months in a permanent, nicely indexed table makes perfect sense.

In this case, though, since you are outputting one column per month for a single year, I recommend to simply use CASE expressions to "cross tab" your data.  You can alias your columns as M1,M2,M3...M12 and Q1-Q4 so that no matter what year you are running the report for, your columns will be consistently named, and you can let your presentation layer handle outputting nice column headers with the current year/month for each one.

So, all you really need is something like this:

select projectID, Y as [Year],
  sum(case when m=1 then amount else 0 end) as M1,
  sum(case when m=2 then amount else 0 end) as M2,
  sum(case when m=3 then amount else 0 end) as M3,
  sum(case when m in (1,2,3) then amount else 0 end) as Q1,
  ...
  sum(case when m=12 then amount else 0 end) as M12,
  sum(amount) as Total
from
(
  select projectID, Amount, DatePart(Month, [Date]) as M, DatePart(Year, [Date]) as Y
  from YourTable
  where [Date] >= @StartDate and Date < @EndDate
) x

Of course, you'd define @StartDate and @endDate as '01-01-2008' and '01-01-2009', respectively.

---

In this comment, Stewy writes:

I have an issue with both DISTINCT and GROUP BY.

The issue is that using either one, the results comes back ordered as if using order by.

I need the unique results in the order they are in the database. How can I do this? Thanks

Stewy -- Relational databases have no obligation to store data in any specific order, or to keep track of the order that things were entered, or to return things "as they are in the database."  There is no such thing as getting data out "the way it is stored" because a relational database may move or re-order data temporarily to efficiently execute a query depending on indexes available.   You must always explicitly specify how you want your results using an ORDER BY clause.  If you want to keep track of the order that you added data to a table, you should have a "timestamp" column that records the exact moment each row was added via a DEFAULT value or a trigger.  Or, at the very least, you can use an IDENTITY.  Then, you can simply order by that column.  This is a very important concept to understand when working with relational databases.  Things are returned based on the data itself, not based on physical storage characteristics.  I hope this helps.

---

In response to Always Use Parameters, Karuna writes:

Hi Jeff,
Just wondering if I build the Sql in Stored Procedure (Dynamic Sql) based on the parameters passed to stored proc, will it still be a possible candidate for Sql Injection? Basically I want to build the Sql in the stored procedure instead of doing it in .Net code as displayed in the article.

Dim cm As New SqlCommand("", YourConnection)
cm.CommandText = "DELETE FROM YourTable WHERE ID=@ID "
cm.Parameters.Add("@ID", SqlDbType.Int).Value = ID

If Name <> "" Then
cm.CommandText &= " And Name=@name"
cm.Parameters.Add("@Name", SqlDbType.VarChar).Value = Name
End If
If TranDate <> DateTime.MinValue Then
cm.CommandText &= " And TranDate = @TranDate"
cm.Parameters.Add("@TranDate", SqlDbType.DateTime).Value = TranDate

Hi Karuna -- you are absolutely 100% safe from SQL Injection by doing this.  Remember, SQL Injection is not about genereal SQL concatenation or about building a SQL statement dynamically.  It only can happen when you concatenate user input into a SQL string and execute it.  If you put together a big SQL statement via concatenation but you only incorporate user input via parameters, there's no need for scrubbing data or worrying in any way about SQL Injection -- it will never happen, under any circumstance. 

Avoiding SQL Injection is the easiest thing in the world -- simply do things the easy and correct way and you'll never need to worry about it.   It's like if there was a big controversy in the news about thousands of people crashing their cars because they are driving them with decorative tin foil covering their windshields, and asking the experts "how can we solve this crisis?"   Should we cut holes in the tin foil, or add mirrors, or incorporate a camera and a tv monitor?  Uh .. no.  You should just take the tin foil off of your windshield and do things the easy, simple and correct way and don't make things over complicated.   That's basically what this whole SQL Injection thing is about -- bad programmers doing stupid things when all they need to do is write decent code the easy way -- simply by using parameters.

---

Gocs writes:

I have tried to compute the number of hours based on the datetime in MS SQL 2005.  However, I am not sure the hours is correct.  Do you have any idea on how to do it correctly?


Gocs -- I think you really need to read this very carefully.  I'll be waiting!

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

How to get IP address

DECLARE       @Interfaces TABLE
       (
              RowID INT IDENTITY(0, 1),
              Interface CHAR(38),
              IP VARCHAR(15)
       )
 
INSERT @Interfaces
       (
              Interface
       )
EXEC   master..xp_regenumkeys     N'HKEY_LOCAL_MACHINE',
                           N'System\CurrentControlSet\Services\TcpIP\Parameters\Interfaces'
 
DECLARE       @RowID INT,
       @IP VARCHAR(15),
       @Key NVARCHAR(200)
 
SELECT @RowID = MAX(RowID)
FROM   @Interfaces
 
WHILE @RowID >= 0
       BEGIN
              SELECT @Key = N'System\CurrentControlSet\Services\TcpIP\Parameters\Interfaces\' + Interface
              FROM   @Interfaces
              WHERE RowID = @RowID
 
              EXEC   master..xp_regread   N'HKEY_LOCAL_MACHINE',
                                         @Key,
                                         N'DhcpIPAddress',
                                         @IP OUTPUT
 
              IF @IP <> '0.0.0.0'
                     UPDATE @Interfaces
                     SET    IP = @IP
                     WHERE RowID = @RowID
 
              SET    @RowID = @RowID - 1
       END
                                 
SELECT IP
FROM   @Interfaces
WHERE IP IS NOT NULL

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

PIVOT conundrum

I started out with typing

SELECT @@VERSION

and got the result as

Microsoft SQL Server 2005 - 9.00.3215.00 (Intel X86) 
       Dec 8 2007 18:51:32 
       Copyright (c) 1988-2005 Microsoft Corporation
       Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)


So far so good. Now I created some sample data like this

-- Prepare sample data
DECLARE     @Sample TABLE (RecNo TINYINT, ID TINYINT, EventDate SMALLDATETIME, OrderStatus VARCHAR(9))

INSERT      @Sample
SELECT      1, 10, '7/5/2008', 'Opened' UNION ALL
SELECT      2, 11, '7/5/2008', 'Closed' UNION ALL
SELECT      3, 12, '7/5/2008', 'Closed' UNION ALL
SELECT      4, 13, '7/6/2008', 'Opened' UNION ALL
SELECT      4, 14, '7/6/2008', 'Opened' UNION ALL
SELECT      4, 15, '7/6/2008', 'Closed' UNION ALL
SELECT      1, 16, '7/7/2008', 'Opened' UNION ALL
SELECT      2, 17, '7/8/2008', 'Postponed' UNION ALL
SELECT      3, 18, '7/8/2008', 'Closed'

I wanted to PIVOT the data, and I wrote this query

SELECT      p.*
FROM        (
                  SELECT      EventDate,
                              OrderStatus
                  FROM        @Sample
            ) AS s
PIVOT       (
                  COUNT(s.OrderStatus)
                  FOR s.OrderStatus IN ([Opened], [Closed], [Postponed])
            ) AS p

The result came back as expected.

EventDate         Opened Closed Postponed
2008-07-05        1          2          0
2008-07-06        2          1          0
2008-07-07        1          0          0
2008-07-08        0          1          1


Now I wanted to see if there was a discrepancy if counting another column, so I wrote this query

SELECT      p.*
FROM        (
                  SELECT      EventDate,
                              OrderStatus
                  FROM        @Sample
            ) AS s
PIVOT       (
                  COUNT(s.EventDate)
                  FOR s.OrderStatus IN ([Opened], [Closed], [Postponed])
            ) AS p

And got this resultset back.