Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

November 2009 Blog Posts

Lesson learned by Trial and Error

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...

posted @ Wednesday, November 18, 2009 11:46 AM | Feedback (4) | Filed Under [ SQL Server 2008 Administration ]

How to tell if you are running on a virtual environment

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         (             LogDate DATETIME,             ProcessInfo NVARCHAR(MAX),             Text NVARCHAR(MAX)         )   INSERT  @Result EXEC    sys.xp_readerrorlog 0, 1, 'System Manufacturer', 'VMware'   IF EXISTS (SELECT * FROM @Result)     SELECT 'It seems you are running on VMware.' AS Msg ELSE     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.

posted @ Thursday, November 12, 2009 3:27 PM | Feedback (10) | Filed Under [ SQL Server 2008 Administration SQL Server 2005 ]

Subscription finally transferred

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...

posted @ Friday, November 06, 2009 4:28 PM | Feedback (1) | Filed Under [ Miscellaneous ]

Get the Nth weekday of any arbitrary period

I have previously posted some algorithm how to find the Nth weekday for a fixed period such as month, quarter or year. See http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-the-number-of-weekdays-in-a-month.aspx http://weblogs.sqlteam.com/peterl/archive/2009/06/18/How-to-get-the-Weekday-and-Nth-from-a-date.aspx http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-the-number-of-weekdays-for-any-given.aspx http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-number-of-weekdays-in-a-year.aspx 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,     @WeekDay TINYINT,     @Nth INT ) RETURNS DATETIME AS BEGIN     RETURN  (             SELECT  DATEADD(DAY,                     CASE                         WHEN @Weekday < theFrom %...

posted @ Tuesday, November 03, 2009 4:11 PM | Feedback (0) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Superfast sequence generators!

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 (     @FromNum INT,     @ToNum...

posted @ Tuesday, November 03, 2009 11:38 AM | Feedback (4) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

Powered by:
Powered By Subtext Powered By ASP.NET