Localizing SQL Server Reporting Services Reports

Bret Hill recently published an article on how to localize a Reporting Services report.  This is an important article because of its completeness.  If you’ve ever tried to localize a report for a controlled, load-balanced web site, you’ve probably spent a lot of time with Mr. Google, because you have to read a bit here and a bit there to get all the pieces of information you need.

Newbie problems with SMO and SQLPSX: unclosed connections

I have little experience with the SQL Server Management Objects (SMO) library and am looking at SqlServer Powershell Extensions (sqlpsx) for the first time.

It’s a lot to learn…

My first real problem was a steady growth of open SQL Server connections. In my script I would use a Server object, which opened a database connection.  When my script ended, the connection remained open, so next time I ran it a new connection would open.  You can’t just let something like this continue—you’d eventually run out of connections!

After banging my head, experimenting, searching on Google, etc., for a few hours (I’m sure you’ve been there), I finally came across a link that showed me how to close the connection:

Call the Disconnect() method on the ConnectionContext property of the Server object.

   1: $smoServer = Get-SqlServer $instanceName

   2:  

   3: # ... do whatever you need here...

   4:  

   5: # close the connection or you end up with more connections every time you run this!

   6: $smoServer.ConnectionContext.Disconnect()

Many thanks to the folks out there in the ether!

Are You Writing Fossilized T-SQL Code Every Day?

There’s a long list of SQL Server features that are deprecated; i.e., will not appear in some future version—such as perhaps the next version.  See http://msdn.microsoft.com/en-us/library/ms143729.aspx for this list.

Ignoring the list is one of those things that can lock an organization into staying with an old, and eventually unsupported, version of SQL Server—or into retiring a system earlier than they would like, or into spending precious resources rewriting code.

Many items on the list will hit someone, forcing them to revise existing code.  But there are a few features that are so widely used that when they are removed the impact will be enormous.  I think Microsoft will be pressured not to remove them, but in order for the product to move forward sometimes it needs to be done. 

The Short List

Here are the ones from the list that I think will cause the most wide-spread problems:

• Use of the SQL 2000 and earlier system tables—sysobjects, sysindexes, etc.  Microsoft always warned against using these but I’ve never worked in an organization that didn’t use them somewhere in their code.
• DBCC DBREINDEX
• SQL-DMO
• And now the big one:  not ending T-SQL statements with a semicolon.  Think of all the lines of SQL code you have written, and that your colleagues have written, that are still in use.  What percentage have a semicolon at the end of each statement?

Coding habits die slowly.  I’ve found that even when presented with this warning from Microsoft, I have had great difficulty in getting programming groups to even seriously consider adding a semicolon at the end of each statement.  It’s not hard; it could probably become a habit within a few weeks for people who write a lot of T-SQL.  (That’s about how long it took for me to start typing it automatically).  And I think it actually makes the code easier to read.  But most people don’t want to fight the inertia.

How about you?