Chris Crowe

asp.net - output cache – optional caching

Chris Crowe — Tue, 16 Apr 2013 22:36:13 GMT

This post is about using the asp.net output cache but also having a small bit of flexibility to allow some requests not to be cached – sort of.

In my case I wanted my dynamically generated charts to be cached in asp.net for a period of time such as 60 minutes, but I did not want the chart for today to be cached at all since the chart is always updating.

By using the VaryByCustom tag you can then create a sort of conditional caching method.

We will override the GetVaryByCustomString() method in the global.asax file

   1: public override string GetVaryByCustomString(HttpContext context, string custom)

   2:    {

   3:        if (string.Compare(custom, "data", true) == 0)

   4:        {

   5:            if (Request.QueryString["NoCache"] != null)

   6:                return Guid.NewGuid().ToString();

   7:            else

   8:                return Request.QueryString["Data"];

   9:        }

  10:        else

  11:            return base.GetVaryByCustomString(context, custom);

  12:    }

13:

In this method we are checking if the VarByParam is set to “data” – it can be any value but if you want to control different caching on different pages then the name filters our caching options.

Next we check if there is a parameter passed on the request query string set to NoCache but this could be any parameter you want to check like search etc.

We do not care about the value for NoCache just that it exists and if it does then we return a new GUID as a string – this will cause a unique value to be returned to the caching engine and hence the result will be unique. Technically is is still cached but since we will never get another GUID with the same value it does not matter. It will however consume some server resources while your app pool is running.

Using the SQL Server Dedicated Admin Connection

Chris Crowe — Tue, 03 Jul 2012 22:14:23 GMT

I installed a test install of SharePoint 2010 Foundation for myself today at work . As part of the installation it installed an instance of SQL Server Express 2008 with SP1.

I then installed SQL Server Management studio so I could administer the Database.

I then noticed that the only user accounts under the security\logins section was:

BUILTIN\Users
sa

SQL Server was also installed with Windows Only Authentication so the SA account being a SQL Server Account was disabled (not that I would know the password anyway)

So the problem is how to sort this out so my domain user account could be added to the server.

A bit of googleing bought around some help but it took a bit of work to sort it out for me so I thought I should write this up.

SQL Server 2005+ provides a special diagnostic connection for administrators when standard connections to the server are not possible. This diagnostic connection allows an administrator to access SQL Server to execute diagnostic queries and troubleshoot problems even when SQL Server is not responding to standard connection requests.

This dedicated administrator connection (DAC) supports encryption and other security features of SQL Server. SQL Server makes every attempt to make DAC connect successfully, but under extreme situations it may not be successful.

SQL Server prohibits running parallel queries or commands with the DAC

The process we will use to add an account to SQL Server is listed below and should take about 5 minutes to complete.

Step #1

On the SQL Server computer add your user account to the Local Administrators group – if you are a member of the local administrators group you will be able to connect to SQL Server using the Dedicated Admin Connection.

Step #2

Open SQL Server Configuration Manager and disable SQL Server Services (you can also do this via the services applet)

Step #3

Start SQL Server Browser Service

Note: If you right click in the SQL Server Configuration Manager and all options are disabled this means the service is disabled in the Services applet.

Open the Services applet (Press Windows Key + R and type in services.msc and hit enter)

Select the “SQL Server Browser” Service and Right-Click and select Properties

Change the service start-up to be Manual and then click the apply button. Then click on the Start button to start the service and OK to close the dialog.

Note: If the SQL Server Browser service is not running the dedicated admin connection will not connect!

Step #4

Open two instances of CMD.EXE – MAKE SURE THEY ARE BOTH STARTED USING RUN AS ADMINISTRATOR

We will use one of the command prompts to start SQL Server in console mode and the other we will use to connect to the SQL Server instance on the dedicated admin connection.

Step #5

In both command prompts change the default directory to the SQL Server BINN folder. This is dependant on your instance but something like this.

Step #6

Start SQL Server with one of the CMD.EXE command prompts enabling the dedicated Admin Connection

The command I used was:

sqlservr.exe –sSharePoint -T 7806

The Trace Flag 7806 Enables a dedicated administrator connection (DAC) on SQL Server Express. By default, no DAC resources are reserved on SQL Server Express.

See http://msdn.microsoft.com/en-us/library/ms188396.aspx

In my case I was connecting to an SQL Server Instance called “SharePoint”

Here is the full log that was created from running the command above.

2011-09-14 16:17:19.98 Server Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)      
Mar 29 2009 10:11:52       
Copyright (c) 1988-2008 Microsoft Corporation       
Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (VM)       
      
2011-09-14 16:17:19.98 Server (c) 2005 Microsoft Corporation.       
2011-09-14 16:17:19.98 Server All rights reserved.       
2011-09-14 16:17:19.98 Server Server process ID is 3840.       
2011-09-14 16:17:19.98 Server System Manufacturer: 'VMware, Inc.', System Model: 'VMware Virtual Platform'.       
2011-09-14 16:17:19.98 Server Authentication mode is WINDOWS-ONLY.       
2011-09-14 16:17:19.98 Server Logging SQL Server messages in file 'e:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\Data\MSSQL10.SHAREPOINT\MSSQL\Log\ERRORLOG'.       
2011-09-14 16:17:19.98 Server This instance of SQL Server last reported using a process ID of 1776 at 9/14/2011 4:17:17 PM (local) 9/14/2011 4:17:17 AM (UTC). This is an informational message only; no user action is required.       
2011-09-14 16:17:19.98 Server Registry startup parameters:       
-d e:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\Data\MSSQL10.SHAREPOINT\MSSQL\DATA\master.mdf       
-e e:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\Data\MSSQL10.SHAREPOINT\MSSQL\Log\ERRORLOG       
-l e:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\Data\MSSQL10.SHAREPOINT\MSSQL\DATA\mastlog.ldf       
2011-09-14 16:17:19.98 Server Command Line Startup Parameters:       
-s Sharepoint       
-T 7806       
7806       
2011-09-14 16:17:20.00 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.       
2011-09-14 16:17:20.00 Server Detected 4 CPUs. This is an informational message; no user action is required.       
2011-09-14 16:17:20.05 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.       
2011-09-14 16:17:20.07 Server Node configuration: node 0: CPU mask: 0x0000000000000001 Active CPU mask: 0x0000000000000001. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.       
2011-09-14 16:17:20.10 spid7s Starting up database 'master'.       
2011-09-14 16:17:20.18 spid7s 6 transactions rolled forward in database 'master' (1). This is an informational message only. No user action is required.       
2011-09-14 16:17:20.21 spid7s 0 transactions rolled back in database 'master' (1). This is an informational message only. No user action is required.       
2011-09-14 16:17:20.21 spid7s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.       
2011-09-14 16:17:20.34 spid7s FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'SHAREPOINT'.       
2011-09-14 16:17:20.37 spid7s SQL Trace ID 1 was started by login "sa".       
2011-09-14 16:17:20.38 spid7s Starting up database 'mssqlsystemresource'.       
2011-09-14 16:17:20.40 spid7s The resource database build version is 10.00.2531. This is an informational message only. No user action is required.       
2011-09-14 16:17:20.82 spid7s Server name is 'CHRIS-SHAREPOIN\SHAREPOINT'. This is an informational message only. No user action is required.       
2011-09-14 16:17:20.82 spid10s Starting up database 'model'.       
2011-09-14 16:17:20.82 spid7s Informational: No full-text supported languages found.       
2011-09-14 16:17:20.83 spid7s Starting up database 'msdb'.       
2011-09-14 16:17:20.83 Server A self-generated certificate was successfully loaded for encryption.       
2011-09-14 16:17:20.83 Server Server is listening on [ 'any' <ipv6> 62035].       
2011-09-14 16:17:20.83 Server Server is listening on [ 'any' <ipv4> 62035].       
2011-09-14 16:17:20.83 Server Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\SHAREPOINT ].       
2011-09-14 16:17:20.84 Server Server local connection provider is ready to accept connection on [ \\.\pipe\MSSQL$SHAREPOINT\sql\query ].       
2011-09-14 16:17:20.84 Server Server is listening on [ ::1 <ipv6> 62042].       
2011-09-14 16:17:20.84 Server Server is listening on [ 127.0.0.1 <ipv4> 62042].       
2011-09-14 16:17:20.84 Server Dedicated admin connection support was established for listening locally on port 62042.       
2011-09-14 16:17:20.86 Server The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/Chris-sharepoin.DOMAIN:SHAREPOINT ] for the SQL Server service.       
2011-09-14 16:17:20.86 Server The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/Chris-sharepoin.DOMAIN:62035 ] for the SQL Server service.       
2011-09-14 16:17:20.86 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.       
2011-09-14 16:17:20.91 spid10s Clearing tempdb database.       
2011-09-14 16:17:21.06 spid10s Starting up database 'tempdb'.       
2011-09-14 16:17:21.13 spid13s The Service Broker protocol transport is disabled or not configured.       
2011-09-14 16:17:21.13 spid13s The Database Mirroring protocol transport is disabled or not configured.       
2011-09-14 16:17:21.16 spid13s Service Broker manager has started.       
2011-09-14 16:17:21.46 spid7s 1 transactions rolled forward in database 'msdb' (4). This is an informational message only. No user action is required.       
2011-09-14 16:17:21.48 spid7s 0 transactions rolled back in database 'msdb' (4). This is an informational message only. No user action is required.       
2011-09-14 16:17:21.48 spid7s Recovery is writing a checkpoint in database 'msdb' (4). This is an informational message only. No user action is required.       
2011-09-14 16:17:21.51 spid7s Recovery is complete. This is an informational message only. No user action is required.       
2011-09-14 16:17:28.68 spid7s SQL Server shutdown due to Ctrl-C or Ctrl-Break signal. This is an informational message only. No user action is required.       
2011-09-14 16:17:28.68 spid7s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.       
2011-09-14 16:17:28.70 Server The SQL Server Network Interface library successfully deregistered the Service Principal Name (SPN) [ MSSQLSvc/Chris-sharepoin.DOMAIN:SHAREPOINT ] for the SQL Server service.       
2011-09-14 16:17:28.70 Server The SQL Server Network Interface library successfully deregistered the Service Principal Name (SPN) [ MSSQLSvc/Chris-sharepoin.DOMAIN:62035 ] for the SQL Server service.       

Step #7

create Go to the second command prompt and enter the following command to login using the dedicated admin connection.

SQLCMD.EXE –Sadmin:.\SharePoint

The –S command is for the server name, the admin: means to use the admin connection and the .\SharePoint is the server (.) and Instance name (SharePoint)

If all is good then you should see a 1>

If you do not see the 1> ensure the SQL Server Browser service is running.

Now you can create a login to SQL Server and add that login to the SYSADMIN role.

To do that type in

CREATE LOGIN [DOMAIN\USERNAME] from WINDOWS
GO

and then

exec SP_ADDSRVROLEMEMBER ‘DOMAIN\USERNAME’, ‘sysadmin’
GO

Now you should have your domain account in the SYSADMIN role.

Step #8

To finish we need to restore the original configuration for the services and restart SQL Server as a service

You can now press CTRL-C to stop the SQL Server instance that is running in the command prompt.

Go back to the services applet and if appropriate disable the SQL Server Browser service.

Start the SQL Server Services that you had running as well using the Services applet.

Google Maps–without the labels

Chris Crowe — Tue, 03 Jul 2012 21:59:16 GMT

Some times it is nice to have a clean map without any labels all over the map.

You can do this with just a small bit of code and it works for the standard Map, Satellite and Terrain models.

Map Examples

For a live example see

http://weather.crowe.co.nz/mapdemo

Basically we just add some custom layers and control the urls’s that are used.

 <script type="text/javascript">
        var map = null;

        function CustomMap(name, alt, baseUrlIndex) {
            CustomMap.prototype.baseUrls =
               [
                    ["http://mt0.google.com", "http://mt1.google.com", "http://mt2.google.com", "http://mt3.google.com"],  // MAP
                    ["http://khms0.google.com", "http://khms1.google.com", "http://khms2.google.com", "http://khms3.google.com"], // Satellite
                    ["http://mts0.google.com", "http://mts1.google.com", "http://mts2.google.com", "http://mts3.google.com"]  // Terrain
               ];

            CustomMap.prototype.partUrls =
                ["/vt/lyrs=m@164000000,weather_nolabels&hl=en&x=", "/kh/v=113&src=app&x=", "/vt/lyrs=t@128,r@179000000,weather_nolabels&hl=en&x="];

            CustomMap.prototype.tileSize = new google.maps.Size(256, 256);
            CustomMap.prototype.maxZoom = 19;

            this.alt = alt;
            this.name = name;
            this.baseUrlIndex = baseUrlIndex;
        }

        CustomMap.prototype.getTile = function (coord, zoom, ownerDocument) {
            var MaxTilesAtThisZoomLevel = Math.pow(2, zoom);
            var x = coord.x % MaxTilesAtThisZoomLevel;

            var i = (x + 2 * coord.y) % this.baseUrls[this.baseUrlIndex].length;
            var salt = "Galileo".substr(0, (x * 3 + coord.y) % 8);
            var URL = [this.baseUrls[this.baseUrlIndex][i], this.partUrls[this.baseUrlIndex], x, "&y=", coord.y, "&z=", zoom, "&s=", salt].join("");

            var div = ownerDocument.createElement('IMG');
            div.src = URL;
            div.style.width = this.tileSize.width + 'px';
            div.style.height = this.tileSize.height + 'px';
            return div;
        };

        $(document).ready(function () {

            var myOptions =
                            {
                                zoom: 12,
                                minZoom: 3,
                                center: new google.maps.LatLng(-43.53, 172.6),
                                mapTypeId: 'mapNoNames',
                                mapTypeControlOptions: {
                                    mapTypeIds: ['mapNoNames', 'SatelliteNoNames', 'TerrainNoNames'],
                                    style: google.maps.MapTypeControlStyle.DROPDOWN_MENU
                                }
                            };
            map = new google.maps.Map(document.getElementById("map_canvas"), myOptions);

            map.mapTypes.set('mapNoNames', new CustomMap("Map", "Map", 0));
            map.mapTypes.set('SatelliteNoNames', new CustomMap("Satellite", "Satellite", 1));
            map.mapTypes.set('TerrainNoNames', new CustomMap("Terrain", "Terrain", 2));


        });

    </script>

Microsoft Office Outlook has encountered a problem and needs to close when opening email messages

Chris Crowe — Tue, 02 Nov 2010 20:52:10 GMT

I recently has a user who found that email messages from specific people would cause her Outlook 2010 to crash 100% of the time. Most peoples emails were perfectly fine and others were not.

There were lots of entries in the Windows Event Log like the following

Faulting application name: OUTLOOK.EXE, version: 14.0.4760.1000, time stamp: 0x4ba8fefd

Faulting module name: USP10.DLL, version: 1.626.7600.20602, time stamp: 0x4b304c27

Exception code: 0xc0000005

Fault offset: 0x00027466

Faulting process id: 0xa38

Faulting application start time: 0x01cb6ef5e0cefe77

Faulting application path: C:\Program Files (x86)\Microsoft Office\Office14\OUTLOOK.EXE

Faulting module path: C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE14\USP10.DLL

I read up online and found this article http://social.technet.microsoft.com/Forums/en-US/outlook/thread/178bce4f-707e-42ac-8743-ac41b7a4e046/ where a lot of people where having a similar problem.

There were lot of opinions given there and they did nothing to help me fix the problem directly.

Microsoft

I ended up opening a case with Microsoft and together over a period of a week we diagnosed the problem.

The problem was – A Corrupt Font

I found a few posts online which referred to Helvetica Font (above link and also other places) being corrupt and causing problems with Outlook.

But in my case it was an Arial Narrow font.

* I have suggested that Microsoft Write a tool to test all fonts for corruption – who knows what will happen.

One of the responses from Microsoft was

Based on my research I found that the affected emails which you sent to me is in Helvetica font. Looks like the issue is specific to Helvetica font.

This problem may occur if your document contains Type 1 fonts, such as Helvetica and TimesNewRomanPS.
Note: The same problem can occur in Microsoft Office Outlook 2010 if an email message contains Type 1 fonts.

To resolve this problem, please follow the action below.

Determine which offending Type 1 font is being applied to your document, and then remove the offending Type 1 Font Substitutions entry from the registry. To do this, follow these steps:

Click Start, type regedit in the Start Search box, and then press Enter.

Collapse this imageExpand this image

If you are prompted for an administrator password or for confirmation, type the password, or provide confirmation.

In Registry Editor, locate the following subkey:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\FontSubstitutes

Under the FontSubstitutes entry, select the offending Type 1 font.
Right-click the font, and then click Delete.
Exit Registry Editor.
Restart the computer.
Uninstall the offending Type 1 fonts. For more information about how to uninstall fonts, see the following Microsoft Knowledge Base article:

314960 (http://support.microsoft.com/kb/314960/ ) How to install or remove a font in Windows

Install the OpenType versions of the offending Type 1 fonts.

For more detailed information, please refer to http://support.microsoft.com/default.aspx?scid=kb;en-US;2119612

** IMPORTANT ** The Microsoft response above did not fix the problem but more investigation showed that Arial fonts were the issue

This problem was on a Windows 7 machine (and mine was as well) so I decided to copy the Arial fonts from the bad machine to my machine. After this “I” got the problem as well!!! (I made a backup of the fonts first!)

The Fix (for me anyway) - Copy my good Arial Fonts to the bad computer

In order to resolve this I needed to copy the good fonts from my machine to a temp directory on my machine and then copy them to a temp folder on the bad machine and then copy the font from the temp folder to the Fonts applet.

Normally using the Fonts applet you can not copy fonts to anywhere else, but by opening a CMD prompt you can make copies of them.

So on the good computer open a CMD prompt and change directory to c:\windows\fonts

You can now copy the good fonts to another folder on the local machine such as c:\temp

You now need to copy the fonts from the c:\temp folder to a folder on the bad computer – again c:\temp

Then on the bad computer open the Fonts applet (you can just type fonts and hit enter at the search programs and files area in the start menu)

Now with that window open you can drag the fonts from c:\temp to the fonts window and you will be prompted to overwrite any existing fonts (if copying more than one font you can let it overwrite all fonts)

Some thoughts

Now by the time I got around to checking these fonts we had sent 100’s of megabytes of debug logs to Microsoft, removed all add-ons, started in safe mode, rebooted heaps of times and generally spent a lot of time on the problem.

I had been in contact with Microsoft on the phone and via email and a week or more time had passed with the user not being able to open emails from specific users.

I would assume you could copy all fonts from a known good machine to the bad computer. If you copy all fonts you may never know what font was corrupt, but there is a good chance that in less than 5 minutes you will have a working Outlook again.

Also See

Microsoft Office Word has encountered a problem and needs to close
http://support.microsoft.com/default.aspx?scid=kb;en-US;2119612

Microsoft Forum on this topic
http://social.technet.microsoft.com/Forums/en-US/outlook/thread/178bce4f-707e-42ac-8743-ac41b7a4e046/

The row value(s) updated or deleted either do not make the row unique or they alter multiple rows.

Chris Crowe — Mon, 30 Aug 2010 06:55:09 GMT

I had a user ask me today how they can delete a duplicate row from their database table. They were receiving the following error message when they tried to delete the duplicate row.

The row value(s) updated or deleted either do not make the row unique or they alter multiple rows.

The reason that the user gets this message is because the table did not have a Primary Key or other Unique Index defined. Why, that’s another story and not one I know!

An example table

Some example data

Notice the duplicate row (row 1 and row 3)

So the user in this case would select the first row and hit the delete button

They would then see this message

So how do we get rid of the duplicate row?

Two options

Option #1

Add a new column which contains unique values

We could run the following code to add the new column and populate the new column with unique values

ALTER TABLE Table1
ADD TempID int IDENTITY(1, 1)

After issuing this command we will have something like this.

The user can now select their row and hit delete because SQL Server can uniquely identify this row.

You could then remove the new column

ALTER TABLE Table1
drop COLUMN TempID

But this adds a new column to the table which is not really the best option so on to option #2

Option #2

We can simply issue a simple delete command taking advantage of the Set RowCount command.

SET ROWCOUNT 1

DELETE FROM Table1
WHERE id= 1

What this does is limit the delete command to one record. Since the data is duplicated you do not care which row is deleted as long as only one of the rows is deleted. No adding and removing columns.

WebMatrix - a complete Web development stack that integrates a Web server, Database and more

Chris Crowe — Tue, 06 Jul 2010 01:49:26 GMT

WebMatrix includes a complete Web development stack that integrates a Web server (IIS Developer Express), database (Microsoft SQL Server Compact 4.0), programming model (ASP.NET Web pages with Razor syntax), and a tool (WebMatrix Beta) into a seamless experience. You can use WebMatrix to streamline the way you create an ASP.NET Web site from templates, or by starting a new Web site by using the world’s most popular free and open source (ASP.NET or PHP) apps like DotNetNuke, Umbraco, WordPress, or Joomla!. With WebMatrix you can code your Web sites, customize them, optimize them for good search engine ranking, test them, and deploy them to an Internet hosting company, all through the tool.

Learn more about WebMatrix through:

What is WebMatrix?
Investigate the top 10 features of WebMatrix.
Download WebMatrix Beta.
More about IIS Developer Express.
More about Microsoft SQL Server Compact 4.0.

For additional resources, visit:

Learn more about WebMatrix Beta. More resources, links to forums and feedback channels.
Visit the forums on the IIS Web site or the ASP.NET Web site.
File a bug or suggestion on WebMatrix Beta.
Learn to program the Web using ASP.NET Web pages with Razor syntax.
Download applications with WebMatrix and the Web Application Gallery.
Review the FAQs for Open Source and Free Web applications.

Looking for a way to find text in your SQL Database objects, then try the free Re-Gate SQL Search

Chris Crowe — Sun, 27 Jun 2010 08:35:47 GMT

To start I should mention that you can not search the actual data in your tables for text, but you can search your SQL Objects – Tables, Views, Stored Procedures, Constraints, Triggers and Functions for text.

Often you want to find all references to a particular piece of text which could be the name of a table or column or just something that you have in a stored procedure.

The free tool from Red-Gate software fits the bill well, supporting SQL 2005 and above (including the express editions of Management Studio – See below for notes on SQL Express 2005)

There is some advertising links to their other products included in the tool, but they are well worth a look at if you have never used them before – Red-Gate products are great.

Downloading the tool

To download the tool simply go to http://www.red-gate.com/products/sql_search/

Installation
After you install SQL Search you get a new toolbar with a single button in SQL Server Management Studio. Clicking this button opens the UI which add a new tab to the current set of open tabs and will select the current database as the database to be searched (you can change this from the drop down).

Note: You can only search a single database at a time.

The search is activated by simply typing into the search field and your results are immediately displayed in a grid (no need to hit enter).

You can limit the search to different types of objects such as tables or views if needed.

Clicking on any of the search results will display the full text of the object where it was found with any matching text highlighted.

SQL 2005 Management Studio Express

This statement is included on the requirements page for SQL Search at http://www.red-gate.com/products/sql_search/requirements.htm

* To use SQL Search with SSMS 2005 Express Edition, extensibility.dll must be installed.

But then they give you no indication of what that means or where you get it from.

You can download the extensibility.dll by installing the following tool:
ftp://ftp.red-gate.com/development/sqlprompt/extensibilitymsm.msi

SQL Server : TSQL Return start/end price and start/end date time by product name

Chris Crowe — Tue, 22 Jun 2010 10:14:37 GMT

As part of trying to monitor my power usage and to know when my power prices change I download the power prices online from my power provider every 15 minutes and store this in a database.

I have been doing this for more than 4 months now and wanted to produce some data showing how the prices have changed over time.

I wanted to produce a report of all of the price movements per product over time.

The Database

I have two SQL Server tables described below that contain the data:

CREATE TABLE [dbo].[PowerUpdate](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [DateTime] [datetime] NOT NULL,
    [Enabled] [bit] NOT NULL
)

CREATE TABLE [dbo].[PowerItem](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [PowerUpdateID] [int] NOT NULL,
    [Name] [nvarchar](128) NOT NULL,
    [Price] [decimal](9, 4) NOT NULL,
    [Type] [nvarchar](128) NOT NULL,
    [Description] [ntext] NULL,
)

I also have one view that just joins the tables back together for reporting purposes.

CREATE VIEW [dbo].[vPowerData]
as
SELECT     PowerItem_1.id, PowerItem_1.Name, PowerItem_1.Price, PowerUpdate_1.DateTime
FROM       PowerItem AS PowerItem_1 
INNER JOIN PowerUpdate AS PowerUpdate_1 ON PowerItem_1.PowerUpdateID = PowerUpdate_1.id

Some sample data

So here is some examples of the data that I have in the database.

*select top 10 from powerupdate**	*select top 10 from poweritem**

PowerUpdate contains records with an ID and a Date and Time which identifies when the update occurred.

PowerItem contains the actual power products that were available for the particular update date & time and their current price.

What I want in my report

What I would like to see is for every product, the date and time when the price was first seen, and the last date and time that the price was seen and the actual prices.

To make it more complex a product’s price could return to the same value as a previous date and time and I would like the last price to display null for the next price and next price datetime fields.

Here is an example of the required output

The final solution

Now I had problems trying to get my head around my report requirement. I spent some time trying to come up with a solution but failed – I knew that it could be done using a SET based solution, which is what I wanted, but I could not do it myself.

I posted my question to the guru’s at www.sqlservercentral.com – the original post is here http://www.sqlservercentral.com/Forums/Topic930464-338-1.aspx

I received some good replies from people on the forum and really appreciate the work all the people put into this, to help me!

I liked the solution provided by a member called “Mark-101232”

Below is his solution to the problem which did exactly what I needed and was very fast compared to some solutions given.

WITH CTE1 AS (
SELECT Name ,Price ,DateTime,
       ROW_NUMBER() OVER(PARTITION BY Name ORDER BY DATETime) AS rn1,
       ROW_NUMBER() OVER(PARTITION BY Name,Price ORDER BY DATETime) AS rn2
FROM dbo.vPowerData),
CTE2 AS (
SELECT Name,Price AS [Min Price],MIN(DateTime) AS [Min DateTime],MAX(rn1) AS maxRN
FROM CTE1
GROUP BY Name,Price,rn2-rn1)
SELECT a.Name,a.[Min Price],a.[Min DateTime],
       b.Price AS [NextPrice],
       b.DateTime AS [Next Price DateTime]
FROM CTE2 a
LEFT OUTER JOIN CTE1 b ON b.Name=a.Name AND b.rn1=a.maxRN+1 AND b.DateTime>a.[Min DateTime]
ORDER BY a.Name,a.[Min DateTime];

Now although I have a solution I really need to understand the logic here so I decided to write this blog post to try to strip down this TSQL code so I can actually understand what is going on and in the process this may help someone else as well.

The first step is to split the query down into its parts.

Part 1

SELECT Name ,Price ,DateTime,
       ROW_NUMBER() OVER(PARTITION BY Name ORDER BY DATETime) AS rn1,
       ROW_NUMBER() OVER(PARTITION BY Name,Price ORDER BY DATETime) AS rn2
FROM dbo.vPowerData

What this is TSQL is doing is return the name, price and date time and also two row numbers.

For more details on row_number() see http://msdn.microsoft.com/en-us/library/ms186734.aspx

The first row_number() function returns the a row number (resetting it to 1 when the name changes) and ordering it by the date time

The second row_number() function returns the a row number (resetting it to 1 when the name and price changes) and ordering it by the date time

The easiest way to look at this is to look at a subset of data and show you what gets returned.

Assume our PowerItem table only contains the following records

Note: There are 3 different products listed, but only the “$49.95 Value Pack” has had the price changing as follows:

0.1693
0.1698
0.1691

The TSQL code in Part 1 will return the following result set

Part 2

SELECT Name,Price AS [Min Price],MIN(DateTime) AS [Min DateTime],MAX(rn1) AS maxRN
FROM CTE1
GROUP BY Name,Price,rn2-rn1

Note: In this case treat CTE1 as the final result set that is visible in Part 1 above

The TSQL code in Part 2 returns the following result set

Part 3

SELECT a.Name,a.[Min Price],a.[Min DateTime],
       b.Price AS [NextPrice],
       b.DateTime AS [Next Price DateTime]
FROM CTE2 a
LEFT OUTER JOIN CTE1 b ON b.Name=a.Name AND b.rn1=a.maxRN+1 AND b.DateTime>a.[Min DateTime]
ORDER BY a.Name,a.[Min DateTime];

Part 3 is really just a simple Left Outer Join sorted by name and the Minimum Date Time, the reason for the left outer join is that we want the final price and date.

Hint: I often try to think as CTE (Common Table Expressions) as a physical table and you can therefore logically join it easier (well it works for me - sometimes)

CTE 1 AS B	CTE 2 AS A

So in this case we have the following going on (for the product name ‘$49.95 Value Pack’)

Row 1
- Row 1 does not join to CTE2 since b.rn1 (in this case 4) = a.maxRN+1 (in this case 5) does not exist
Row 2
- Row 2 does not join to CTE2 since b.rn1 (in this case 1) = a.maxRN+1 (in this case 2) does not exist
Row 3
- Row 3 does join CTE2 since b.rn1 (in this case 2) = a.maxRN+1 (in this case 3) does exist
Row 4
- Row 4 does join CTE2 since b.rn1 (in this case 3) = a.maxRN+1 (in this case 4) does exist

Note: there is also other filtered here such as b.name = a.name and b.datetime > a.[min datetime]

So for the first 4 rows this products the following output (when the order by clause is added)

Now I hope I have explained what is going on because I am still trying to decipher and understand this myself.

But in any case it has made it easier for me to comprehend so I am happy with that and if it helps anyone else then that is good.

Additional resources to create database and populate with data

Script to create database and tables
Script to add 4 months worth of data (1 MB download, expands to 150MB)

Notes:

Additional indexes could help in the solution as well.

Additional comments:

Many thanks to those that answered or submitted a potential solution to my query on SQL Server Central – it was very much appreciated. I am in no way trying to take away from what you have done, but instead hope I am helping others in the SQL Server community by understanding the particular solution I choose to accept, and hopefully explaining it so we can all learn and be better TSQL developers.

An error occurred in the Microsoft .NET Framework while trying to load assembly id 65551. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE

Chris Crowe — Fri, 04 Jun 2010 07:28:24 GMT

Server Error in '/' Application.

An error occurred in the Microsoft .NET Framework while trying to load assembly id 65551. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:
System.IO.FileLoadException: Could not load file or assembly 'sqlservertimezones, Version=1.0.0.0, Culture=neutral, PublicKeyToken=f34ea75f04c42955' or one of its dependencies. The given assembly name or codebase was invalid. (Exception from HRESULT: 0x80131047)
System.IO.FileLoadException:
at System.Reflection.Assembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
at System.Reflection.Assembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.Load(String assemblyString)

I recently got this error after moving a database from SQL Server 2008 to SQL Server 2008 R2 – there are some articles that can help you with this error:

Msg 10314, Level 16, State 11, Line 1

http://support.microsoft.com/kb/918040

http://blogs.msdn.com/b/psssql/archive/2010/03/23/unable-to-load-clr-assembly-intermittently.aspx

http://www.sqlnewsgroups.net/group/microsoft.public.sqlserver.server/topic16037.aspx

In my case none of the solutions helped me and then I thought about the actual assembly. My SQL Server 2008 is running on Windows Server 2008 64Bit, but the SQL Server 2008 R2 was running on 32Bit Windows Server 2008.

I had a look at the assembly compilation properties in Visual studio and noticed that it was a 64Bit DLL and not a 32bit one.

I recompiled and replaced the DLL and reran my setup for the assembly and it resolved my problem.

Looking for some new sounds – checkout this freebee

Chris Crowe — Fri, 17 Jul 2009 20:40:37 GMT

Click to download new music, completely free, brought to you by your music loving friends at Windows. You'll see (but not hear) some ads on your screen in addition to the album's cover art, which is how we (they) keep your new tunes from costing you a cent.

Well worth a listen to checkout some new bands and artists….

Listen online, download MP3 or WMA

http://sponsoredsongs.reverbnation.com/windows?utm_source=windows_myspace_page&utm_medium=button_link&utm_campaign=ms_windows&utm_content=thousand_more