November 2010 (1)
August 2010 (1)
July 2010 (1)
June 2010 (3)
July 2009 (3)
June 2009 (1)
May 2009 (1)
February 2009 (1)
January 2009 (1)
November 2008 (3)
October 2008 (4)
September 2008 (9)
August 2008 (6)
July 2008 (3)
June 2008 (3)
January 2008 (1)
November 2007 (2)
October 2007 (6)
September 2007 (5)
August 2007 (22)
July 2007 (6)
June 2007 (1)
May 2007 (3)
April 2007 (27)
March 2007 (8)
February 2007 (6)
September 2006 (2)
August 2006 (4)
July 2006 (9)
June 2006 (17)
May 2006 (20)
April 2006 (12)
March 2006 (9)
February 2006 (4)
January 2006 (3)
December 2005 (2)
November 2005 (4)
October 2005 (5)
September 2005 (37)
August 2005 (83)
July 2005 (6)

Active Directory / LDAP (0)
ASP.Net (19)
Blackberry Development (4)
c# (34)
c++ (3)
Code Camp (1)
Excel (1)
Exchange (3)
Front Page 2003 (6)
FTP User Editor (4)
HTML / CSS / DHTML (8)
IIS (146)
IIS - Log Parser (7)
IIS / FTP (12)
IIS / Tools / Administration (42)
IIS / Tools / Authentication (6)
IIS / Tools / Compression (8)
IIS / Tools / Crash & Hang (12)
IIS / Tools / ISAPI Filters (17)
IIS / Tools / Log Files (17)
IIS / Tools / Scripts (28)
IIS / Tools / Security (9)
IIS / Tools / SSL (6)
IIS 7 (3)
Internet Information Server (1)
Me (Chris Crowe) (6)
MIME Types (1)
Misc (72)
Oulook Express (2)
Silverlight (1)
SQL Server (27)
SQL Server CTE (1)
Vista (15)
Vista Gadgets (8)
Visual Studio (11)
Voice over BroadBand (1)
Windows (33)
Windows Powershell (3)
Windows Sharepoint Services (0)
Windows Sharepoint Services (15)
Windows Vista (14)
Wine Cellar (1)
WMI (8)

Archive

June 2010 (3)

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

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

image

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.

image

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

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.

image

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

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
image

 

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

image

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

image

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

image

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

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)

image

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

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

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.