MS SQL Server and Hibernate

Posted on November 30, 2011 by Dan Denney

I’ve recently inherited our department’s Java developers. With them comes the joy of using Hibernate with Microsoft SQL Server. I’ll be honest, this is the first time I’ve experienced using object-relational mapping (ORM) with SQL Server. In the past our applications have called stored procedures within SQL Server for all CRUD work. While ORMs definitely speed up the developer’s work by both simplifying and reducing the amount of database coding, left with it’s default configuration settings, it can create some headaches for the DBA and some less then optimal queries for the database server.

I’ll be documenting my experiences as I dive into using Hibernate with SQL Server over the next several posts (I hope). I’ll explain some of the shortcomings of how Hibernate sends it’s queries to SQL Server, and hopefully how to resolve them. I’ll also be posting links to other resources I find useful along the way.

Just to get things started, here is a very brief overview of the current stack we are using:

  • SQL Server 2008
  • JDBC 3.0 (from Microsoft)
  • Hibernate 3.2.2.GA
  • Java 1.5

Documentation to get me started:

 

Wish me luck!

Dan

Posted in Hibernate, SQL Server | Leave a comment

Avoid nested IIF statements in Reporting Services expressions by using the SWITCH function.

Posted on January 18, 2011 by Dan Denney

Quick Syntax:
=SWITCH(condition1,value1, condition2, value2,… ,conditionN, valueN)

If you have ever had to nest multiple IIF functions in order to create CASE like functionality in Reporting Services expressions, you can understand the pain of trying to follow the logic as well as making sure all of your parentheses are closed in the right place.  Well now there is an easier way…

The SWITCH function allows you to have multiple conditions when evaluating an expression.  It works similar to your classic CASE statement.  It evaluates each condition within the function, and the first time a condition evaluates to true it returns the value for that condition (no other conditions are evaluated after that).

A common example of this is when you want to format your background color of a cell or row to one of three or more colors depending on the value of a field.  Using nested IIFs that statement might look something like this:

=IIF(Fields!something.Value=1, ”White”, IIF(Fields!something.Value=2, ”Green”, IIF(Fields.something.Value=3, ”Red”, ”Blue”)))


With the SWITCH function your syntax will look like this:

=SWITCH

(

Fields!something.Value=1, “White”,

Fields!something.Value=2, “Green”,

Fields!something.Value=3, “Red”

True, “Blue”

)


In the example above, you can see that we are using True to simulate the ELSE condition in the standard CASE statement.

Hopefully you will find this a better alternative to multiple IIF statements when trying to evaluate multiple conditions in your Reporting Services reports.

Posted in SQL Server | Leave a comment

Welcome to my world.

Posted on January 1, 2011 by Dan Denney

Welcome to my first official SQL3D post.

I created this blog as a way to post tips, tricks, and solutions to issues, questions, and anything else that comes up in my day to day duties as a DBA.  Since my job entails more then the standard DBA responsibilities, I will posting about everything from SQL Server to SharePoint with a dash of Oracle just to keep things interesting.  Occasionally I may even throw in something completely unrelated.

I’ll be honest though, since college, the majority of my writing consists of disaster recovery plans, run books, and development and standards documentation.  In other words, I’m new to the whole creative writing thing, so please bear with me.  I will, however, try to keep these posts light while still informative.

I’ll end this first post with a couple of thank yous (can you even spell it like that?).  First off, a big ”thank you” to my good friend, co-worker, and programmer extraodinaire, Blue (no, not the old guy from Old School), for encouraging me to start a blog.  Secondly, I would like to give a special shout out to my unknowing mentor, Brent Ozar (blog|twitter), whose writings and presentations have inspired me to be more then just another DBA and who has shown me the benefit of being active in the SQL Server community (not to mention how to even start a blog).

Thanks for visiting and I look forward to hearing from you!

Daniel D. Denney (yup, the 3D in SQL3D is for my initials)

Posted in Miscellaneous | Leave a comment