November 2009 Blog Posts
Yesterday, I decided to install Microsoft SQL Server 2008 R2 (November CTP) on my local machine. I already had SQL Server Express 2008 installed so the first step was to uninstall Express, since there seemed to be no upgrade path.
Uninstalling went great.
I then installed 2008 R2 and rebooted my machine. I attached my sample databases, and my test database. To my surprise, the Test database was now corrupted!
File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Test.ldf" may be incorrect. The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred...
This is a piece of code I often use to see if the database server is running in a virtual environment. That is not always obvious or known by the developers.
DECLARE @Result TABLE
EXEC sys.xp_readerrorlog 0, 1, 'System Manufacturer', 'VMware'
IF EXISTS (SELECT * FROM @Result)
SELECT 'It seems you are running on VMware.' AS Msg
SELECT 'It seems you are not running on VMware.' AS Msg
If you test this in your environment and found other virtual manufacturers, please let me know so I can add them in the code above.
Today, it seems the transfer for the MSDN subscription I won for the Adam Machanic Grouped String Concatenation finally is done. It took almost 4 months with a lot of phone calls with Microsoft (I have spent more than 7 hours in this case).
I won the MSDN subscription before I was awarded the MVP SQL Server. With the MVP award you get a MSDN subscription, so there I was sitting with two almost identical subscriptions. I begun to feel uncomfortable with it, so I decided to give the MSDN subscription I won to the runner-up, Leonid Koyfman from Razorfish.
We had almost...
I have previously posted some algorithm how to find the Nth weekday for a fixed period such as month, quarter or year.
This algorithm in the function allows you to get a weekday for any arbitrary period, both forward and backwards. If you pass a positive number for @Nth you will return the Nth weekday within the specified period, starting forwards from @FromDate. If you pass a negative number for @Nth, you will return the Nth weekday starting backwards from @ToDate.
CREATE FUNCTION dbo.fnWeekdayOfPeriod
@FromDate AS DATETIME,
@ToDate AS DATETIME,
WHEN @Weekday < theFrom %...
Based on Itzik's suggestion for sequence generators, I rewrote them and adapted them for my needs.
Itzik showed how to work around a big issue for some cases where the query optimizer actually tried to produce all possible combinations before returning the wanted records.
My rewrite is two-part
1) The functions accepts a Starting point and an Ending point.
2) The functions accepts a Stepping point. You may want only odd or even numbers? Or only every 7th day?
Here are the functions. Have fun with them!
I haven't checked performance compared to MVJ's F_NUMBER_TABLE function, but they should be at least equally fast.
CREATE FUNCTION dbo.fnGetNumbers