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)
SQL Server (27) blog.crowe.co.nz.Models.Category

SQL Server

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

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

image

Some example data

image

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

image

They would then see this message

image

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.

image

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.


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


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.


An great TSQL index defrag script

I have been doing some more looking at SQL Server indexes lately and came across a great script that may be of use to others as well.

So what can this index defrag script do?

  • Schedule it to run with the default settings; it works “right out of the box” with no additional configuration necessary
  • Run this one script from a centralized database for all databases on a server
  • Run this script for a specific database or table
  • Configure custom threshold limits and the point at which a rebuild should be performed (instead of a reorganize)
  • Defrag individual partitions
  • Log its actions and the duration of the defrag
  • Run in “commands only” mode (@executeSQL = 0, @printCommands = 1)
  • Customize performance parameters such as @maxDopRestriction and @defragDelay to minimize impact on the server
  • Schedule specific indexes to only be defragged on weekends, or every other day

The script source code (very well documented) and author (Michelle Ufford) can be found at http://sqlfool.com/2009/06/index-defrag-script-v30/


Some simple T-SQL code to strip time from a date and return other dates

declare @dt datetime
set @dt = getdate()

 

print 'Now'
print @dt
print ''



print 'Today - no time'
print dateadd(D, 0, datediff(D, 0, @dt))
print ''


print 'Today – 23:59:59.997'
print dateadd(ms,-3, dateadd(D, datediff(D, 0, @dt),1))
print ''



print 'Start of this week - Starts on MONDAY'
print dateadd(wk,datediff(wk,0, @dt),0)
print ''



print 'End of this week - Starts on MONDAY 23:59:59.997'
print dateadd(ms,-3, dateadd(wk,datediff(wk,0,@dt),7))
print ''


print 'Start of this week - Starts on SUNDAY'
print dateadd(wk,datediff(wk,0,getdate()),-1)
print ''



print 'End of this week - Starts on SUNDAY 23:59:59.997'
print dateadd(ms,-3,dateadd(wk,datediff(wk,0,@dt),5))
print ''



print 'Start of Month'
print dateadd(mm,datediff(mm,0,@dt),0)
print ''



print 'Last day of current Month - 23:59:59.997'
print dateadd(ms,-3, dateadd(mm,datediff(mm,0,@dt)+1,0))
print ''

 

Here is a simple break down to how these snippets actually work

print 'Today - no time'
print dateadd(D, 0, datediff(D, 0, @dt))
print ''

First off we return the number of days between the date/time passed and 0 so we get the number of whole days (we have lost the time)

Today is July 14, 2009 (as an example) which using this code

print datediff(D, 0, '2009-07-14 03:34:21')

This will return 40006

We then add 40006 days to 0 and this returns the date 2009-07-14.

print dateadd(D, 0, 40006)

Jul 14 2009 12:00AM

So adding it together we get

print dateadd(D, 0, datediff(D, 0, @dt))

 

You will notice that some of the functions have a –3 as part of the code, this is because we want to return the last actual time that SQL Server can reference on a particular day. SQL Server can store the maximum time as 23:59:59.997. That is because SQL Server is only accurate to 3 milliseconds. If we used 23:59:59.999 it would actually roll over to the next day.

So if we were wanting to return the last time for today’s date/time we can use the following

print DATEADD(ms, - 3, DATEADD(D, DATEDIFF(D, 0, GETDATE()), 1))


How to get Apex SQL Code for free – with 1 year maintenance

In an effort to really jump start the ApexSQL Code community and get more templates Apex software are offering for free, no strings attached, full licenses of ApexSQL Code 2008 with 1 yr Maintenance to anyone who requests one.

The license will be for ApexSQL Code w/ 1 yr Maintenance for Support and Upgrades.

To request it email sales@apexsql.com and include "Free ApexSQL Code" in the subject header. They will process the request and send you your own individual key within 1 business day. These will be fulfilled via the same process as people who purchase them - the only difference is the cost is $0.

This promotion could end at any time so get your request in ASAP. The keys will be viable though after the promotion ends.

Generate code from customizable templates

Generate code from C# or VB.NET code templates

Access a template library including scripting, documentation, CRUD, DAL and more

Increase productivity with intelliprompt, snippets, auto-replacements, and more

Automate via a command line interface

Leverage template projects for more powerful processing

For more details see http://www.apexsql.com/sql_tools_code.asp and http://www.apexsql.com/blog/2008/09/submit-template-get-apexsql-code-for.htm


SQL Server 2008 - Backup Compression

* UPDATED : Sep 15, 2008 to include HyperBac for SQL Server in the comparison results.

SQL Server 2008 now supports compression when backing up your databases. But note this is only included in the Enterprise edition which is a pity.

Note: Though creating compressed backups is supported only in SQL Server 2008 Enterprise and later, every SQL Server 2008 or later edition can restore a compressed backup.

Note: The tests was made on a low end test machine hence the actual throughput is not good but the overall results are important as percentages

In this example I will show you some metrics when dealing with SQL Server backups on SQL Server 2008 Enterprise. I am also comparing Red-Gate SQL Backup and Quest SQL LightSpeed.

I have a database I have created called Performance which is currently using 15.3GB

sp_helpdb

-- SQL Server 2008 Backup with Compression
BACKUP DATABASE [Performance] TO  
    DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\Performance_Compressed' 
    WITH  COPY_ONLY, NOFORMAT, INIT,  NAME = N'Performance-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, 
    COMPRESSION,  STATS = 10

10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
Processed 384760 pages for database 'Performance', file 'Performance' on file 1.
Processed 128 pages for database 'Performance', file 'Orders2001' on file 1.
Processed 128 pages for database 'Performance', file 'Orders2002' on file 1.
Processed 128 pages for database 'Performance', file 'Orders2003' on file 1.
Processed 128 pages for database 'Performance', file 'Orders2004' on file 1.
Processed 128 pages for database 'Performance', file 'Orders2005' on file 1.
Processed 128 pages for database 'Performance', file 'Orders2006' on file 1.
Processed 128 pages for database 'Performance', file 'Orders2007' on file 1.
Processed 128 pages for database 'Performance', file 'Orders2008' on file 1.
Processed 128 pages for database 'Performance', file 'Orders2009' on file 1.
100 percent processed.
Processed 1 pages for database 'Performance', file 'Performance_log' on file 1.
BACKUP DATABASE successfully processed 385913 pages in 150.111 seconds (20.084 MB/sec).


-- SQL Server 2008 Backup with No Compression
BACKUP DATABASE [Performance] TO  
    DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\Performance_UnCompressed' 
    WITH  COPY_ONLY, NOFORMAT, INIT,  NAME = N'Performance-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, 
    NO_COMPRESSION,  STATS = 10

10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
Processed 384760 pages for database 'Performance', file 'Performance' on file 1.
Processed 128 pages for database 'Performance', file 'Orders2001' on file 1.
Processed 128 pages for database 'Performance', file 'Orders2002' on file 1.
Processed 128 pages for database 'Performance', file 'Orders2003' on file 1.
Processed 128 pages for database 'Performance', file 'Orders2004' on file 1.
Processed 128 pages for database 'Performance', file 'Orders2005' on file 1.
Processed 128 pages for database 'Performance', file 'Orders2006' on file 1.
Processed 128 pages for database 'Performance', file 'Orders2007' on file 1.
Processed 128 pages for database 'Performance', file 'Orders2008' on file 1.
Processed 128 pages for database 'Performance', file 'Orders2009' on file 1.
100 percent processed.
Processed 1 pages for database 'Performance', file 'Performance_log' on file 1.
BACKUP DATABASE successfully processed 385913 pages in 206.006 seconds (14.635 MB/sec).

Now the results:

Database Size as listed in SQL Server : 15,350 MB

  Uncompressed Compressed
Backup time 206 Seconds 150 Seconds
Backup speed 14.635 MB/Second 20.084 MB/second
File Size 3,087,475 KB 452,749 KB

So as a result the compressed backup out performed the uncompressed backup as follows:

  • 72.86% of the time
  • 37.23% faster throughput
  • 14.66% of the uncompressed file size

So how does this compare to the 3rd party tools?

Red-Gate - SQL Backup 5

Red-Gate SQL Backup 5 supports compression and encryption of 32-bit and 64-bit versions of SQL Server 2008, SQL Server 2005 and SQL Server 2000 (SP 3 or later)

  Uncompressed Compressed
Level 1
Compressed
Level 2
Compressed 
Level 3
256-bit
Encryption
No Compression
256-bit Encryption
Compressed Level 1
256-bit Encryption
Compressed Level 2
256-bit Encryption
Compressed Level 3
Backup time 155 Seconds 95 Seconds 119 Seconds 128 Seconds 162 Seconds 115 Seconds 95 Seconds 126 Seconds
Backup speed 19.42 MB/Sec 31.68  MB/Sec 25.29 MB/Sec 23.51 MB/Sec 18.58 MB/Sec 26.17 MB/Sec 31.68 MB/Sec 23.88 MB/Sec
File Size 3,088,078 KB 532,149 KB 448,821 KB 412,893 KB 3,088,078 KB 532,149 KB 448,821 KB 412,893 KB

* Red gate supports 3 different levels of compression and two encryption settings - listed as 128-bit key and 256-bit key.

Quest - SQL LightSpeed 5.0

Quest SQL LightSpeed supports compression and encryption of 32-bit and 64-bit versions of SQL Server 2008,  SQL Server 2005,  SQL Server 2000 (Service Pack 4), SQL Server 7.0 (Service Pack 4)

  Uncompressed
Did not WORK - still compressed
Compressed
Level 1
Compressed
Level 5
Compressed 
Level 11
256-bit
Encryption
No Compression
256-bit Encryption
Compressed Level 1
256-bit Encryption
Compressed Level 5
256-bit Encryption
Compressed Level 11
Backup time 118 Seconds 92 Seconds 114 Seconds 906 seconds 115 Seconds 119 Seconds 116 Seconds 709 Seconds
Backup speed 25.50 MB/Sec 32.71  MB/Sec 26.40  MB/Sec 3.32 MB/Sec 26.17 MB/Sec 25.30 MB/Sec 25.94 MB/Sec 4.24 MB/Sec
File Size 565,343 KB 565,456 KB 394,567 KB 376,114 KB 565,399 KB 565,379 KB 393,634 KB 376,114 KB

* LightSpeed supports 11 different levels of compression and 9 encryption settings - listed as 40-bit RC2, 56-bit RC2, 112-bit RC2, 128-bit RC2,168-bit 3DES, 128-bit RC4, 128-Bit AES, 192-bit AES, 256-bit AES

Hyperbac for SQL Server

Quest SQL LightSpeed supports compression and encryption of 32-bit and 64-bit versions of SQL Server 2008,  SQL Server 2005,  SQL Server 2000 (Service Pack 4), SQL Server 7.0 (Service Pack 4)

  Uncompressed
(Custom Ext)
Compressed
.HBC Ext
Compressed
.ZIP Ext
(Zip)
Compressed 
.HBC2 Ext
(FastZip)
256-bit
Encryption
No Compression
(Custom Ext)
256-bit Encryption
Compressed
.HBE Ext
Backup time 210 Seconds 114 Seconds 101 Seconds 112 Seconds 196 Seconds 119 Seconds
Backup speed 14.293 MB/Sec 26.50 MB/Sec 29.65 MB/Sec 26.852 MB/Sec 15.30 MB/Sec 25.18 MB/Sec
File Size 3,087,475 KB 433,857 KB 433,022 KB 545,840 KB 3,087,475 KB 434,211 KB

* Hyperbac supports 2 levels of compression and 3 encryption levels listed as AES-256, AES-192, and AES-128

 

Overall Fasted Backup

  Backup Time Backup Speed File Size
Quest - SQL LightSpeed 92 Seconds 32.71 MB/Sec 565,456 KB
Red-Gate - SQL Backup 5 95 Seconds 31.68 MB/Sec 532,149 KB
Hyperbac for SQL Server 101 Seconds 29.65 MB/Sec 433,022 KB
Microsoft SQL Server 2008 150 Seconds 20.06 MB/Sec 452,749 KB


Overall Smallest Backup File

  Backup Time Backup Speed File Size
Quest - SQL LightSpeed 906 Seconds
116 Seconds
3.32 MB/Sec
24.94 MB/Sec
376,114 KB
393,634 KB
Red-Gate 126 Seconds 23.88 MB/Sec 412,893 KB
Hyperbac for SQL Server 101 Seconds 29.65 MB/Sec 433,022 KB
Microsoft SQL Server 2008 150 Seconds 20.08 MB/Sec 452,749 KB

 

References:


Some resources from Microsoft about SQL 2008

I just received my latest copy of TechNet Flash and it included a section on SQL Server 2008.

I have copied it here for anyone who may not receive TechNet flash - well work signing up for.

For overview resources, check out:
. SQL Server 2008 Videos
. Webcasts: 24 Hours of SQL Server 2008 for IT Professionals


If you are looking for assistance with SQL Server 2008 planning and installation, here are some helpful links:
. Planning a SQL Server Installation
. Installing SQL Server 2008
. Quick Start Installation
. Installation How-to Topics
. Locating Previous Versions of SQL Server for SQL Server 2008 migration using Microsoft Assessment and Planning Toolkit


Once you've installed SQL Server 2008, these resources can help you optimise your database environment:
. Configuring SQL Server 2008
. Database Engine Tuning Advisor Overview
. System and Sample Databases.

You can also find guidance for upgrading to SQL Server 2008 or migrating to SQL Server 2008.

If you are upgrading, be sure to check out Using Upgrade Advisor to Prepare For Upgrades


Microsoft has a SQL Server 2008 Jumpstart Training Site

This site contains presentations, recordings, hands-on labs and demonstrations for the SQL Server 2008 JumpStart technical training event.

There are 5 tracks in this event:

You must register online to get access to the content, which I have just done.

At this stage it is hard to know if it is going to be very useful, but any free training may help me and I am not going to say no.

See SQL Server 2008 Jump Start for more details.


Microsoft SQL Server 2005 - CTE Example of a simple hierarchy

I have a simple table here which shows a list of devices and each device can have a parent device except for the top item which has a <NULL> value for the ParentID column.

Using SQL 2005 CTE (Common Table Expression) to render this full hierarchy we could use a simple query such as this:

What this CTE is doing is allowing us to run a hierarchical query.

We first off find the root of our tree, which in this case is referenced by a NULL value in the ParentID column.

SELECT ParentID, ID,0, [DeviceType], cast(DeviceType as nvarchar(1024))
FROM DeviceType
WHERE ParentID IS null

This technically returns the same details as row #1 in the above graphic.

What the UNION ALL does is executes a query with a join against the CTE virtual table.

So we technically are sort of doing this:

Create Virtual Table with the following fields:

  • ParentID
  • ID,
  • Level,
  • DevieType,
  • Sort

This line inserts one row into the virtual table, the ID in this case will be 1 because that was the ID in our DeviceType table of the record with the ParentID of Null.

SELECT ParentID, ID,0, [DeviceType], cast(DeviceType as nvarchar(1024))
FROM DeviceType
WHERE ParentID IS null

Now we run the following query on the virtual table until every row is processed in the virtual table.

Note: This select is really like an insert into the virtual table. each row returned in the select is appended to our virtual table.

SELECT dt.ParentID, dt.ID, level+1, dt.DeviceType, cast(Sort + '|'+dt.Devicetype as nvarchar(1024))
FROM DeviceType dt INNER JOIN hierarchy h ON dt.ParentID = h.ID

So the first time it is run we see that h.ID would be equal to 1 which was the ID of the record with ParentID of Null. If there was any records matching this select statement they are added to the virtual table and this continues until all of the virtual table rows have been processed.

At the end of it we have a Virtual table that we can then do what we want with such as join it to another table or just return the data, in our case we run this sql statement.

select ID, ParentID, DeviceType, LEVEL, Sort
from Hierarchy
order by sort

But what happens if I have an record and want to find the parent hierarchy for it?

The following query will allow us to return the parent hierarchy for the item Virtual Server Hosts which has an ID of 10.

Note: There are problems with the results of this query!

Can you see the problem with the results?

We have correctly returned the parents starting from the Virtual Server Hosts and walked up the tree until we get to the root node. But if you look at the Level field it is not actually containing the correct value as the Virtual Server Hosts fields should be at level 3 and not at level 0.

Why is this?

Well we can't determine at what level the Virtual Server Hosts is at when we start creating the virtual table so we just hard code it to 0. 

The problem also is that if you wanted to render this out to say a web browser using the following hierarchy we would have to navigate through our records backwards.

  • Object
    • Server
      • Virtual Servers
        • Virtual Server Hosts

How to fix this!

What we can do in this case is run a second CTE joined to the first CTE because each CTE is just like a virtual table. When the first CTE has completed processing we are basically left with a table with records in it like below.

Our second CTE now just references the records in the first CTE as if it was a standard table.

As you can see in the query in the above graphic our second CTE starts by looking for a record which has the ParentID being set to NULL.

This will return our real parent as the first record to be inserted into our second virtual table. 

Now we run the following query on the second virtual table until every row is processed in the virtual table. Like the first virtual table, the select appends records to our virtual table and these records get processed until all records have been processed.

SELECT h.ParentID, h.ID, hr.LEVEL+1, h.DeviceType, cast(hr.Sort + '|'+h.sort as nvarchar(1024))
FROM Hierarchy h INNER JOIN HierarchyReverse hr ON h.ParentID = hr.ID

We have changed the join in the second CTE to join from the ParentID --> ItemID, which is the opposite of the first CTE which joined the ItemID --> ParentID. This allows us to navigate down the tree instead of up the tree.

Confusing? hopefully not....

What we end up with is this:

Now you may be wondering what this Sort column is and why we have it at all?

The reason is that we need to have something that is sortable, using the DeviceType is not since it would corrupt our hierarchy by giving us A-Z, the same goes for level, 0-9

What we do is we create a field that contains the hierarchy so far and then add the DeviceType to the end of it. This makes a sortable field that will not corrupt our hierarchy but give us A-Z sorting within each level of the hierarchy.

Notice the problem with this?

Well I have made a decision that all of my hierarchies of DeviceType will not exceed 1024 characters by casting the values with the statement : NVARCHAR(1024)

Well we could use a NVARCHAR(MAX) and the problem is resolved, but this is dependant on your knowledge of the application.

Hopefully this has shown you and me how the CTE's work while they are running - well it looks like that to me and it makes me understand it a bit easier as they can be hard to picture sometimes.

 


http://www.sqldownunder.com/

If you are interested in SQL Server there is a great set of podcasts available at http://www.sqldownunder.com/

Greg Low MVP and Microsoft Regional Director  has setup this pod cast site especially for SQL Server related pod casts.

Currently there are 23 pod casts available for download as .WMA or .MP3 files.

Here is a list of the podcasts to date,

  • Show 23 with SQL Server author James Luetkehoelter
  • Show 22 with guest Kevin Kline
  • Show 21 with guest Joe Celko
  • Show 20 with guest Richard Waymire
  • Show 19 with guest Peter Myers
  • Show 18 with guest Don Vilen
  • Show 17 with Microsoft Architect and Development Manager Gert Drapers
  • Show 16 with guest SQL Server MVP Paul Nielsen
  • Show 15 with guest Kimberly Tripp
  • Show 14 with guest Microsoft MSDN Regional Director Adam Cogan
  • Show 13 with guest SQL Server MVP Bill Graziano
  • Show 12 featuring guest SQL Server MVP Louis Davidson
  • Show 11 featuring guest Dr Tom Moreau discussing his experiences using SQL Server Service Broker and DMV's
  • Show 10 featuring guest Graeme Simsion discussing data modelling  
  • Show 9 with Turing Award winner and Microsoft Distinguished engineer Dr Jim Gray (http://research.microsoft.com/~Gray/) discussing the future of SQL Server, LINQ and T-SQL
  • Show 8 with guest Adam Machanic discussing stored procedures as APIs and unit testing of stored procedures
  • Show 7 with guest Roger Wolter discussing SQL Server Express
  • Show 6 featuring Itzik Ben-Gan discussing the T-SQL language
  • Show 5 with Niels Berglund
  • Show 4 with Bob Beauchemin
  • Show 3 with Kent Tegels
  • Show 2 with Hilary Cotter
  • Show 1 with Kalen Delaney

Stripping the time portion of a SQL DateTime - Which is faster?

We have two functions below which are basically identical except the actual method of stripping off the time portion of the datetime variable.

CREATE FUNCTION [dbo].[DateTimeToDate](@DateTime datetime)
RETURNS DateTime
AS
  BEGIN
      return
CONVERT(VARCHAR(10),@DateTime,111)
 
END


CREATE FUNCTION [dbo].[DateTimeToDate](@DateTime datetime)
RETURNS DateTime
AS
  BEGIN
      return
cast(cast(@DateTime as integer) as DateTime)
 
END


declare @StartDateTime datetime
declare
@EndDatetime datetime
declare
@dt datetime
declare
@Count int

set @StartDateTime = getdate()
set @Count = 0
while (@Count < 10000000)
begin
     set
@dt = dbo.DateTimeToDate('2007-10-1 1:45pm')
    
set @Count = @Count+1
end
set
@EndDateTime = getdate()

select @EndDateTime - @StartDateTime

We run the script above which calls the function 10 million times and the results are quite close

286.157 seconds   return CONVERT(VARCHAR(10),@DateTime,111)
250.170 seconds  return CAST(CAST(@DateTime as integer) as DateTime)

 


This table shows the operating systems that run the server software for each 32-bit version of SQL Server 2005.
I have been given a task of updating our SQL Server Infrastructure from SQL 2000 to SQL 2005 and I found this table that I thought may be of use to a lot of people.  If you are running SQL 2005 on a 32BIt CPU this table shows which is supported and which is not.
 
 
  Enterprise Edition1 Developer Edition Standard Edition Workgroup Edition Express Edition Evaluation Edition
Windows 2000 No No No No No No
Windows 2000 Professional Edition SP42, 4 No Yes Yes Yes Yes Yes
Windows 2000 Server SP42 Yes Yes Yes Yes Yes Yes
Windows 2000 Advanced Server SP42 Yes Yes Yes Yes Yes Yes
Windows 2000 Datacenter Edition SP42 Yes Yes Yes Yes Yes Yes
Windows XP Embedded No No No No No No
Windows XP Home Edition SP2 No Yes No No Yes No
Windows XP Professional Edition SP24 No Yes Yes Yes Yes Yes
Windows XP Media Edition SP2 No Yes Yes Yes Yes Yes
Windows XP Tablet Edition SP2 No Yes Yes Yes Yes Yes
Windows 2003 Server SP1 Yes Yes Yes Yes Yes Yes
Windows 2003 Enterprise Edition SP1 Yes Yes Yes Yes Yes Yes
Windows 2003 Datacenter Edition SP1 Yes Yes Yes Yes Yes Yes
Windows 2003 Web Edition SP1 No No No No Yes No
Windows Small Business Server 2003 Standard Edition SP1 Yes Yes Yes Yes Yes Yes
Windows Small Business Server 2003 Premium Edition SP1 Yes Yes Yes Yes Yes Yes
Windows 2003 64-Bit Itanium Datacenter Edition SP1 No No No No No No
Windows 2003 64-Bit Itanium Enterprise Edition SP1 No No No No No No
Windows 2003 64-Bit X64 Standard Edition SP1 WOW643 WOW643 WOW643 WOW643 WOW643 WOW643
Windows 2003 64-Bit X64 Datacenter Edition SP1 WOW643 WOW643 WOW643 WOW643 WOW643 WOW643
Windows 2003 64-Bit X64 Enterprise Edition SP1 WOW643 WOW643 WOW643 WOW643 WOW643 WOW643
  Enterprise Edition1 Developer Edition Standard Edition Workgroup Edition Express Edition Evaluation Edition
Windows 2000 No No No No No No
Windows 2000 Professional Edition SP42, 4 No Yes Yes Yes Yes Yes
Windows 2000 Server SP42 Yes Yes Yes Yes Yes Yes
Windows 2000 Advanced Server SP42 Yes Yes Yes Yes Yes Yes
Windows 2000 Datacenter Edition SP42 Yes Yes Yes Yes Yes Yes
Windows XP Embedded No No No No No No
Windows XP Home Edition SP2 No Yes No No Yes No
Windows XP Professional Edition SP24 No Yes Yes Yes Yes Yes
Windows XP Media Edition SP2 No Yes Yes Yes Yes Yes
Windows XP Tablet Edition SP2 No Yes Yes Yes Yes Yes

1SQL Server 2005 Evaluation Edition supports the same feature set as SQL Server 2005 Enterprise Edition, but Enterprise Edition is not supported on all of the operating systems that support Evaluation Edition.

2You can download Windows 2000 SP4 from this Microsoft Web site.

3These editions of SQL Server 2005 can be installed to the Windows on Windows (WOW64) 32-bit subsystem of a 64-bit server.

4You can install Microsoft SQL Server Books Online, client tools and some legacy tools for SQL Server 2005 Enterprise Edition on Windows 2000 Professional SP4, and Windows XP SP2. Client tools include SQL Server Management Studio, and Business Intelligence Development Studio, SQL Server 2005 software development kit. Legacy tools include Data Transformation Services Runtime and SQL-DMO.

The following limitations or issues affect installations on supported operating systems:

  • Native Web Service (SOAP/HTTP) support is only available for instances of SQL Server 2005 running on Windows 2003.

     
  • Individual topics in Microsoft SQL Server 2005 Integration Services (SSIS) programming, Analysis Management Objects (AMO), and ADOMD.NET documentation may indicate support for earlier versions of Windows, such as Windows 98, Windows ME, or Windows NT 4.0. However, for this release, these three programming interfaces are only supported on Windows XP, Windows 2000, and Windows 2003.

     
  • SQL Server 2005 failover clusters require Microsoft Cluster Server (MSCS) on at least one node of your server cluster. MSCS is only supported if it is installed on a hardware configuration that has been tested for compatibility with the MSCS software. For more information, see the topic for "Before Installing Failover Clustering" in SQL Server 2005 Books Online.

Download the Best Of SQL Server Central vol 3

Download the Best Of SQL Server Central vol 3 which contains information about:

  • Security - both an overview and a detailed approach to securing your SQL Server.
  • Performance - key concepts and detailed explanations about how to improve the performance of your SQL Server.
  • Administration - tips and tricks for making the administration of SQL Server work for you, including some strong information about clustering. 

You must register wth Red Gate Software who by the way make some great tools for managing your SQL Server Databases.

You can also download volume 1 & 2 after you register and some of their trial software which I again recommend.

https://www.red-gate.com/Dynamic/Downloads/DownloadForm.aspx?download=ebook2&utm_source=ssp&utm_medium=email&utm_campaign=bestof


SBS 2003 - Upgraded to SQL 2005 and now I am seeing Timeouts connecting from web sites to SQL

I am running SBS2003 at home and as part of this I am running the following on the server:

  • Microsoft Windows 2003 Server SP1
  • Microsoft Active Directory
  • Microsoft Exchange Server 2003
  • Microsoft SQL Server 2000 (recently upgraded to Microsoft SQL Server 2005)
  • Sophos Antivirus
  • Microsoft IIS 6.0 with 12 sites

Ever since I have upgraded the SQL server from 2000 to 2005 I have noticed that in this blog I am getting error messages saying Timeout when connecting to the SQL server.

I am not suprised since the machine only has 1 GB of Ram and Exchange will consume as much as it can.

But I never had this problems before - I never got a timeout until SQL was upgraded to SQL 2005.

If I free memory on the server then the web site works again...

I am not sure if SQL 2005 just wants more RAM than 2000 but it is looking like that.

I am in a bit of a situation since the server is running in as much memory as the box will take - no point upgrading until I can justify a super powerful box running Windows 2003 Server R2 with Microsoft Virtual Server 2005.

So just a note to people thinking of upgrading to SQL 2005 - you may need more RAM....


Get Ready for SQL Server 2005 with Microsoft E-Learning Courses.

Whether you are interested in database administration, database development, or business intelligence, you can access the E-Learning topic you want, when you want it, and learn at your own pace. Each lesson includes hands-on virtual labs and offline functionality.

In addition, you may consider taking a free Microsoft Skills Assessment to help you meet your Microsoft SQL Server 2005 training goals. You'll receive a learning roadmap with additional skills resources including instructor-led classroom training and books.

Note: The E-Learning Courses below are free for 90 days - get in there now!

For more details on all the SQL 2005 courses see https://www.microsoftelearning.com/sqlserver2005/default.aspx

Microsoft Virtual Labs

These labs give you time as a full admin using a product on an OS fully configured for a period of 90 minutes in your web browser. You can do what you want including trashing the system completely. This is your time to try the product. Do you have the system at home or work to install these products? who carees, try the labs you can really put the product though its paces.

IIS 7.0 Virtual Labs

SQL Server 2005 Virtual Labs

+ heaps of others on Security, Sharepoint, MOM, Windows Server, ISA, SMS, Live Communication Server.... 

http://www.microsoft.com/technet/traincert/virtuallab/default.mspx


SQL Prompt - Intellisense and Code Completion for SQL Server, VS.NET 2003+2005, Query Analyzer and others...
Intellisense for SQL Server

SQL Prompt provides Intellisense® style auto-completion for Microsoft SQL Server editors. It will tell you the exact formatting you should use for your SQL commands, while you are writing them and will therefore help you write fast, perfectly formatted SQL statements. SQL Prompt improves the productivity of all SQL script creation.

SQL Prompt simply sits behind the scenes and provides unobtrusive help when you press Ctrl-Space or when you type "." after a table/view/alias name.

Features include :

  • Table/View name completion
  • Column name completion
  • Stored procedure name completion
  • USE completion
  • JOIN/JOIN ON completion
  • Auto-uppercasing of keywords
  • Auto-popup after keywords

SQL Prompt works with the following products:

  • UltraEdit32
  • Visual Studio 2005
  • Visual Studio .NET 2003
  • Microsoft Query Analyzer
  • SQL Server 2005 Management Studio
  • SQL Server 2000 Enterprise Manager

This software has no time-bombs, no restrictions, except you must download it prior to 1st September 2006 for it to be free!

For more details and to download (requires registration)

https://www.red-gate.com/Dynamic/Downloads/DownloadEvaluationLogin.aspx?Download=SQLPrompt


SQL Server 2005 Service Pack 1 has been released
Service Pack 1 for Microsoft SQL Server 2005 is now available. The download which will range 250MB to a wopping 900MB can be used to upgrade any of the following SQL Server 2005 editions:
  • Enterprise
  • Enterprise Evaluation
  • Developer
  • Standard
  • Workgroup

Download Link

http://www.microsoft.com/downloads/details.aspx?familyid=cb6c71ea-d649-47ff-9176-e7cac58fd4bc&amp;displaylang=en

A list of the new features and improvements that are included in SQL Server 2005 Service Pack 1 can be view here.

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


SQL Script to return user accounts and the roles they are assigned to...

This script will return a list of all logins that are in use on the current database and also the creation date and modified date of the user. Also the roles that they are assigned to are also returned.

This can be useful to companies who must audit their SQL server databases for SOX compliance.

Example Output:

Name Creation Date Last Modified Account Type Roles
domain\is 4-Aug-04 16:04:57 4-Aug-04 16:04:57 Windows Account db_datareader
dbo 6-Aug-00 1:27:55 6-Aug-00 1:27:55 SQL Server User db_owner
guest 6-Aug-00 1:27:55 9-Jul-01 11:39:31 SQL Server User db_datareader
LandSurveyRead 19-Jul-04 9:59:56 19-Jul-04 9:59:56 SQL Server User db_datareader
Phonebook 2-Aug-05 12:44:59 2-Aug-05 12:44:59 SQL Server User  
scobb 6-Oct-04 10:20:45 6-Oct-04 10:20:45 SQL Server User db_datareader
Requests 18-Feb-04 8:52:33 18-Feb-04 8:54:30 SQL Server User db_datareader, db_datawriter, db_owner
UserInformationRead 13-Feb-04 9:04:17 13-Feb-04 9:04:17 SQL Server User db_owner
UserInformationWrite 10-Aug-04 8:55:55 10-Aug-04 8:55:55 SQL Server User db_owner


-- Process
--     Create Temp Table for Users
--    Create Temp Table for Roles
--    Populate Users
--    Populate Roles
--    Iterate though each user and update their roles into a single column
--    Return the users and their roles
Create Table #Temp_Users
(
    Name             varchar(128),
    CreateDate        datetime,
    LastModifiedDate    datetime,
    LoginType        varchar(50),
    Roles            varchar(1024)
)
Create Table #Temp_Roles ( Name varchar(128), Role varchar(128) )
insert into #Temp_Users select Name, [Create Date] = CreateDate, [Last Modified Date] = UpdateDate, LoginType = case when IsNTName = 1 then 'Windows Account' when IsNTGroup = 1 then 'Windows Group' when isSqlUser = 1 then 'SQL Server User' when isAliased =1 then 'Aliased' when isSQLRole = 1 then 'SQL Role' when isAppRole = 1 then 'Application Role' else 'Unknown' end, Roles = '' from sysusers where SID is not null order by Name
insert into #Temp_Roles select MemberName = u.name, DbRole = g.name from sysusers u, sysusers g, sysmembers m where g.uid = m.groupuid and g.issqlrole = 1 and u.uid = m.memberuid order by 1, 2


Declare
@Name varchar(128) Declare @Roles varchar(1024) Declare @Role varchar(128)
DECLARE UserCursor CURSOR for SELECT name from #Temp_Users OPEN UserCursor FETCH NEXT FROM UserCursor into @Name WHILE @@FETCH_STATUS = 0 BEGIN set @Roles = '' print @Name DECLARE RoleCursor CURSOR for SELECT Role from #Temp_Roles where Name = @Name OPEN RoleCursor FETCH NEXT FROM RoleCursor into @Role WHILE @@FETCH_STATUS = 0 BEGIN if (@Roles > '') set @Roles = @Roles + ', '+@Role else set @Roles = @Role FETCH NEXT FROM RoleCursor into @Role end Close RoleCursor DEALLOCATE RoleCursor Update #Temp_Users set Roles = @Roles where Name = @Name FETCH NEXT FROM UserCursor into @Name END CLOSE UserCursor DEALLOCATE UserCursor
select * from #Temp_Users
drop table #Temp_Users drop table #Temp_Roles

SQL Server 2005 Mobile Edition Server Tools Beta 1 for use with IIS and SQL Server 2005 Beta 2 (USA)

This release of SQL Mobile Server Tools installs the necessary components on servers running IIS to support connectivity solutions to SQL Server 2000 and SQL Server 2005 Beta 2 databases.

SQL Server 2005 Mobile Edition (SQL Mobile) is the compact database for rapidly developing applications in both native mode and the .NET Compact Framework that extend enterprise data management capabilities to devices.

Companies and users of SQL Mobile that plan to synchronize to SQL Server 2000 or SQL Server 2005 Beta 2 databases will need to install this Server Tools release on their servers running Internet Information Services (IIS).

For more details see:
http://www.microsoft.com/downloads/details.aspx?FamilyID=0A6174A4-C009-4768-8284-698C32EC84E3&displaylang=en


SQL - Creating Dynamic Cross-Tabs/Pivot Tables
 

One of the features of MS Access is the TRANSFORM statement, used to create cross-tabs/pivot tables. It does all of the work of dynamically generating the cross-tabulation and the summary calculations.

T-SQL unfortunately doesn't have this statement, so you're stuck using complicated SQL commands, expensive 3rd party products, or exotic OLAP to make pivot tables...or you can use the following procedure to dynamically create them!

This example would be executed on the Northwind sample database.

SQL Code

EXECUTE crosstab 'SELECT LastName FROM Employees INNER JOIN Orders ON (Employees.EmployeeID=Orders.EmployeeID) GROUP BY LastName', 'count(lastname)', 'Year(OrderDate)', 'Orders'

Results

LastName 1996 1997 1998
Buchanan 11 18 13
Leverling 18 71 38
Fuller 16 41 39
Peacock 31 81 44
Callahan 19 54 31
Suyama 15 33 19
King 11 36 25
Dodsworth 5 19 19
Davolio 26 55 42


For more details see the following article:
http://www.sqlteam.com/item.asp?ItemID=2955

I adjusted the Stored Procedure so that it could handle data with single quotes for field names.

CREATE PROCEDURE crosstab
@select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(100),
@table varchar(100)
AS

DECLARE @sql varchar(8000), @delimStart varchar(1), @delimEnd varchar(1), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

select @delimStart = '['
select @delimEnd = ']'

EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE '
+ @pivot + ' Is Not Null')

SELECT @sql='', @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )

SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )
WHEN 0 THEN '' ELSE '[' END
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pivot'

SELECT @sql=@sql + @DelimStart + convert(varchar(100), pivot) + @DelimEnd + ' = ' +
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN '
+ @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM ##pivot

DROP TABLE ##pivot

SELECT @sql=left(@sql, len(@sql)-1)

SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')
print @select
EXEC (@select)
SET ANSI_WARNINGS ON 

 


Microsoft SQL Server Report Pack for Microsoft Office SharePoint Portal Server 2003
The Microsoft SQL Server Report Pack for Microsoft Office SharePoint Portal Server 2003 is a set of 8 Microsoft SQL Server 2000 Reporting Services reports that work with a sample database of information extracted from a SharePoint Portal Server environment. This database can be populated from your own SharePoint Portal Server environment using the downloadable Data Extraction Program (DEP). The DEP will read the SharePoint Portal Server data via the object model. You also can use the sample reports as templates for designing new reports.
This Report Pack includes the following reports:

  • Storage Report
    Shows a listing of the virtual servers and the number of collections, sites, areas, lists, files and size. Also shows a size distribution and storage usage chart, and a top 20 sites based on size.
  • Storage Trend Report
    Shows four charts illustrating the virtual server storage trend, site collection growth trend, area growth trend and list growth trend.
  • Site Trend Report
    Shows hit counts for virtual servers, collections, areas and lists. Also shows the top 20 sites based on hits.
  • Comprehensive Site Collections Report
    Shows the list of site collections, who owns the collection, configurable characteristics about the owner and the date the collection was last accessed.
  • Detailed Site Collection Report
    Shows top 20 pages accessed (based on hit count) for this site collection.
  • Detailed Page Report
    Shows users who have access to the page, when they last accessed it, any referrer URL and number of hits. Also shows two charts illustrating user distribution and referrer distribution.
  • Best Bet Keyword
    Shows top 20, top 10, bottom 10, or bottom 20 keywords used for searching. Also shows which keywords have best bets.
  • Search Terms
    Shows top 20, top 10, bottom 10, or bottom 20 search terms used for searching. Also shows which search terms match a defined keyword.

http://www.microsoft.com/downloads/details.aspx?FamilyID=49159368-544B-4B09-8EED-4844B4E33D3D&displaylang=en


Microsoft SQL Server 2005 Upgrade Advisor - CTP June Refresh

The Microsoft SQL Server 2005 Upgrade Advisor is a tool used by Database developers and administrators to analyze SQL Server 7.0 and SQL Server 2000 database servers in preparation for upgrading to SQL Server 2005. The Upgrade Advisor will allow users to analyze the configuration of their existing database services and database applications. As a result of this analysis, Upgrade Advisor will provide reports that identify deprecated features and necessary configuration changes that will impact their Database upgrade process. Upgrade Advisor will also provide links to documentation that describe these changes and necessary steps to complete the process.

http://www.microsoft.com/downloads/details.aspx?FamilyID=CF28DAF9-182E-4AC2-8E88-F2E936558BF2&displaylang=en


Microsoft SQL Server Report Pack for Internet Information Services (IIS)
The Microsoft SQL Server Report Pack for Internet Information Services (IIS) is a set of 12 Microsoft SQL Server 2000 Reporting Services reports that works with a sample database of information extracted from Microsoft Internet Information Services (IIS) log files. This database can be populated with your own data using the Log Parser included with the IIS 6.0 Resource Kit. You can use also the sample reports as templates for designing new reports.

This Report Pack includes the following reports:
  • Global Statistics
    Contains a top-level summary chart of visits and bandwidth for one or more sites with drillthough for each month.
  • Site Summary
    Contains a site summary for a specified site, month, and year. Includes a document map and all other detail reports.
  • Time Period
    Shows a parent report with subreports containing time and date based visitor statistics.
  • Day of Week
    Contains bar chart, bubble chart and table with visitor information per day.
  • Hourly Statistics
    Contains a chart and table with visitor information per hour of the day.
  • Day of Month
    Contains chart and table with visitor information per day of the month.
  • OS Statistics
    Shows a table containing of the number of visits based on the operating system type. Also shows detailed chart with version information for Windows OS visitors.
  • Browser Statistics
    Shows a table containing the number of visits based on the browser type. Also shows detailed chart with version information for Internet Explorer visitors.
  • Country Statistics
    Contains a breakdown (table and chart) of the number of visits based on the country of origin.
  • Length Statistics
    Contains a breakdown (table and chart) of the number of visits based on the amount of time spent on the site.
  • Pages
    Contains statistics about the top 5 most viewed pages on the site.
  • Visitors
    Contains of sub reports of Web site visitor statistics grouped by time and date.

Download is approx 5MB

http://www.microsoft.com/downloads/details.aspx?familyid=2805d337-14c7-40e3-820b-e7ee653c68c0&displaylang=en


Quickshift for Microsoft SQL Server: Breakthrough Performance

Increase your SQL Server performance by 100% - 500% simply and easily with this product.

  • Optimizing queries.
  • Tuning performance.
  • Rewriting applications.
  • Running jobs overnight.
  • Babysitting misbehaving batch processes.

http://www.quickshift.com/index.shtml


SQL Server -Error 15023 : User 'XXXX' already exists in the current database.

After doing a restore from a SQL Server backup, the users login rights are removed from the database yet the user itself still remains in the roles of the database. This means that when you try and add the user again you get the error "Error 15023: User "XXXX" already exists in current database" and the addition fails. (where XXX is the user you wish to add)

The quickest way to get this working is using query analyser and running the following command when working on the actual database.

EXEC sp_dropuser 'XXXX'

This will drop the user from the database and you can then re-add the user through the GUI or through a SQL query!


I received an email “11 May 2006” saying that I stole this post from http://www.irishdev.com/blogs/hughmcgauran/default.aspx  which it looks like I did. I do apologise for this as I normally give credit where it is due.

I probably wanted it for my own use and sometimes I post to this blog so I know where to return to, and since it was so so small I probably did not refer any one reading it to Hugh's blog since it was the full article.

So I apologise to Hugh Mc Gauran!