Downgrading an SQL Server Database Using Scripts

One of my biggest annoyances about SQL Server is the inability to load a database from a later version into a previous one. Database files and backups don’t have any indicators of their version (unless you are OCD enough to enforce naming conventions) so whenever I get a backup file from a client (or from my archive) it’s a crapshoot which server it belongs to.

Worst of all, restoring it to a LATER version IS supported, so if I have an SQL Server 2005 backup and restore it to my local SQL Server 2008, it works fine. Then when I attempt to deploy it to the server it’s supposed to go, I get the dreaded error:

The database ‘DatabaseName’ cannot be opened because it is version XXX. This server supports version XXX and earlier. A downgrade path is not supported.

Which is even more infuriating, because God forbid it says something helpful like “version SQL Server 2005” or “version SQL Server 2008” instead of the cryptic and useless “661” and “655”. 2005 was seven years ago and we still don’t have a better error message for this?

So, to make sure I don’t change the version of the database, I have to first try SQL Server 2005, to ensure that it doesn’t attempt to update it if it’s the wrong version. Then I have to go up the chain through 2008 and 2008 R2 because of course I’m not going to remember which number goes with which version…

Of course, the worst part is when the database was created on a later version of SQL Server, but needs to be deployed to a prior version. It’s a rare situation these days, especially since these days I insist needing help with their database be very clear about their environment.

But alas, sometimes it happens, and of course when it does, it’s a pain in the ass to fix. In some cases you can just use the Copy Database or Import Export tools of SQL Server, but these often fail if you’re trying to sync between SQL Server and SQL Express.

The most sure-fire way I’ve found to do the downgrade is to script the entire database (along with all its data) and run that script on the desired instance.

Unfortunately (again!), this is also prone to fail, espcially if your scripts end up being extremely large, resulting in the error:

Exception of type ‘System.OutOfMemoryException’ was thrown.

Sigh.

So long story short (too late!), the solution is to use the SQLCMD utility which is a command line interface for executing SQL commands. This utitliy will execute scripts one line at a time, ensuring that they complete without throwing a memory error.

So once your scripts are generated, launch the sqlcmd utility and execute the following command:

sqlcmd.exe -S [SERVER] -U [USERNAME] -P [PASSWORD] -i “[c:\FULLPATHTOSCRIPTFILE]”

This of course expects that you have the username and password to the server and can connect to it from that machine. For other connection scenarios, refer to the SQLCMD Utility Reference.

Once the database is loaded, you can finally back that up and deploy it to the desired server.

Paging Data in SQL Server 2000

Up until now I’ve been blowing off adding paging support to my web projects. This is very bad practice and I figured it was time to stop. Now I know enough SQL to make a SELECT command (if I have a program that can generate it for me :P) so I didn’t even know where to BEGIN with this one.

Thankfully, the knowlegable folks over at 4GuysFromRolla.com have already done the work for me and gave detailed steps in this handy tutorial that made creating the paging stored procedure a SNAP!

Thanks to their detailed tutorial I was able to modify their stored procedure to make a custom pager for my own projects, and it works GREAT!

Now that I have this procedure I’m going to have to work on the actual page logic to create the paging links and the code to pass the current page into the procedure. I know that the new SQL Server 2005 has newer features that make this simpler to implement, but my host still runs SQL Server 2000, and it’s good to know how to do it in the old school way anyway in case it comes up again, and God knows it will!

-SelArom

Select Query: Retrieve Fixed Number of Rows Part 2

In my last entry, I touched on a way to dynamically select a specific number of rows from a table in a SELECT SQL query. It works great if you are going to supply the value directly when you call the procedure. But what if you are using a static value that is stored in the database itself? I have a Settings table that stores Property/Value pairs, and one of those properties is the NumItems that I want to retrieve from the SELECT query!

I could create a public shared property that will retreive the value from the database, then pass that to the procedure call as a parameter, but that’s a lot of overhead, both in creating a new public property and then issuing two separate calls to the database. It would be a much better idea to inject the code to retrieve that value from within the stored procedure itself, since encapsulating the SELECT command to retrieve the latest entries (or whatever the case may be) logically makes more sense.

It took me a while to learn the correct syntax for decalring variables within SQL (it’s not my best language) but here’s the basic idea (notice I’m still using the SET ROWCOUNT method mentioned before):

 


BEGIN
DECLARE @NumItems int
SET @NumItems = (SELECT [Value] FROM cms_Settings WHERE CMSTable='News' AND [Property]='NumItems')
SET ROWCOUNT @NumItems

 

SELECT ID, Title, Icon, BodySummary, BodyExtended, DateEntered, Author, CategoryID, Lock, AllowComments, AllowRating
FROM cms_News
ORDER BY DateEntered DESC

-- Reset rowcount
SET ROWCOUNT 0

END
GO

 

notice that this method couples the procedure directly to the intended table, preventing it from being used in a general method. But since this is a feature that is exclusively needed for the News table, I think the trade-off is worth the convenience.

As I was writing this, it occured to me that perhaps I could take advantage of the new My namespace in VB.NET to create a Settings entry, but I’ve never used that with a Web Application. I will investigate this strategy at a later time and if it works out I’ll report my findings in a new post.

-SelArom

Select Query: Retrieve Fixed Number of Rows

As I’m beginning work on my senior project, I have discovered the need to select a specific number of rows from a SELECT query in SQL Server 2000. Although the new 2005 version has the built in ability to pass a variable for the TOP clause, no such functionality exists in SQL SERVER 2000. Fortunately I found a great resource showing various ways to accomplish it. The best one in my opinion is to pass the variable into the SET ROWCOUNT command, which can take a variable as a value:

 


CREATE PROCEDURE dbo.getFoo
@top INT
AS
BEGIN
SET ROWCOUNT @top

SELECT foo
FROM blat
ORDER BY foo DESC

-- never forget to set it back to 0!
SET ROWCOUNT 0
END
GO

 

this preserves the security your stored procedure (vs building it as a string) and keeps things relatively tidy. It’s a shame it took them this long to add a built-in mechanism into SQL SERVER but I’m just glad there’s always a workaround 🙂

-SelArom