July 2009 - Posts

I've been running gigabit for a while now here at my home office (databases involve large files as we all know); I've just got myself a new gigabit router so I was checking the speed - I was only getting 12MBytes tops from server to desktop - pittyful; that's what I'd expect from 100Mbps Full Duplex.

After nearly buying a new card, checking cables and checking from my laptop (which was the same as the desktop) I research the problem.

Answer: In windows control panel, program features, turn off the feature "Remote Differential Compression".

Roll on 6th August when I hopefully get to download Windows 7 and get rid of this crock of shite that is Vista.

 

Stored procedure and Trigger testing can be very easily compartmentalised into individual test scripts and fully automated by taking advantage of the CHECKSUM function – no more writing hoodles of IF statements to check each scenario – just write the query, if its changed the CHECKSUM on the results will be different.

Here goes:

Our Test object:-

create table Trades (

 

      TradeID     int   not null identity primary key clustered,

     

      TradeDate   date not null,

      TradeType   tinyint     not null,

      TradeAmount                   decimal( 28, 2 ) not null,

      RollingTradeAmountTotal decimal( 28, 2 ) not null default( 0 ),

     

)

go

 

create trigger trg_Trades_i on Trades for insert

as

begin

 

      update tu

            set RollingTradeAmountTotal = coalesce( t.RollingTradeAmountTotal, 0 ) + i.TradeAmount

      from inserted i

            inner join Trades tu on tu.TradeID = i.TradeID

            left outer join Trades t on t.TradeID = ( select top 1 t2.TradeID

                                                                          from Trades t2

                                                                          where t2.TradeId < i.TradeID

                                                                          order by t2.TradeID desc )

 

end

go

 

We want to test the above works and if changed the change does not alter the test outcome.

Here is how we test:

truncate table Trades

 

insert Trades ( TradeDate, TradeType, TradeAmount )

      values( '20090721', 1, 10 )

insert Trades ( TradeDate, TradeType, TradeAmount )

      values( '20090721', 1, 10 )

insert Trades ( TradeDate, TradeType, TradeAmount )

      values( '20090721', 1, 10 )

insert Trades ( TradeDate, TradeType, TradeAmount )

      values( '20090721', 1, 10 )

     

select *

from Trades

 

Now you manually check your results and can see that the RollingTradeAmountTotal does in fact increment accordingly.

We can now freeze frame the results using CHECKSUM...

declare @chksum int

set @chksum = CHECKSUM( cast( (

                        select TradeDate, TradeType, TradeAmount, RollingTradeAmountTotal

                        from Trades

                        order by TradeID

                        for xml path( '' )

                              ) as varchar(max) ) )

                             

print @chksum

 

if @chksum = 1111078251

      print 'query results are fine'

 

else

      raiserror( 'query results are different', 16, 1 )

     

If the results of the query change at all then the CHECKSUM will be different and thus the test script will fail.

This only works for non-changing columns, so you may need to take any IDENTITY or NEWID populated columns out of the equation; any dates – just fix them in time. If your logic is time based then you have a slightly more challenging task but it is workaround able.

Say, our trigger changes to this (uses an inner join instead of left outer thus missing the first row)...

create trigger trg_Trades_i on Trades for insert

as

begin

 

      update tu

            set RollingTradeAmountTotal = coalesce( t.RollingTradeAmountTotal, 0 ) + i.TradeAmount

      from inserted i

            inner join Trades tu on tu.TradeID = i.TradeID

            inner join Trades t on t.TradeID = ( select top 1 t2.TradeID     

                                                                   from Trades t2

                                                                   where t2.TradeId < i.TradeID

                                                                   order by t2.TradeID desc )

 

end

go

 

Now run our test again and you will see the checksum is different so flags we need to investigate the problem:

truncate table Trades

 

insert Trades ( TradeDate, TradeType, TradeAmount )

      values( '20090721', 1, 10 )

insert Trades ( TradeDate, TradeType, TradeAmount )

      values( '20090721', 1, 10 )

insert Trades ( TradeDate, TradeType, TradeAmount )

      values( '20090721', 1, 10 )

insert Trades ( TradeDate, TradeType, TradeAmount )

      values( '20090721', 1, 10 )

     

select *

from Trades

 

--    The checksum...

declare @chksum int

set @chksum = CHECKSUM( cast( (

                        select TradeDate, TradeType, TradeAmount, RollingTradeAmountTotal

                        from Trades

                        order by TradeID

                        for xml path( '' )

                              ) as varchar(max) ) )

                             

print @chksum

 

if @chksum = 1111078251

      print 'query results are fine'

 

else

      raiserror( 'query results are different', 16, 1 )

 

 

 

Thanks to Vicki Collins for this technical rollup; some interesting stuff to check out...

News 

Microsoft Releases SQL Server 2008 Service Pack 1
Microsoft has released SQL Server 2008 Service Pack 1 with enhancements meant to ease deployment but no brand new features.http://www.eweek.com/c/a/Database/Microsoft-Releases-SQL-Server-2008-Service-Pack-1-687774/ 

SQL Server to Get Real-Time Boost
The next release of Microsoft's SQL Server database, code-named "Kilimanjaro," will offer master data management (MDM) and support real-time data feeds for business intelligence (BI), reporting and analytics, the company revealed today.http://reddevnews.com/news/article.aspx?editorialsid=10836%20 

Microsoft Announces SQL Server Fast Track Data Warehouse
New reference architectures from Bull, Dell and HP make it faster, easier, and more affordable for companies to deploy enterprise-class data warehousing projects.http://www.microsoft.com/presspass/press/2009/feb09/02-23SQLFastTrackPR.mspx 

Dave Campbell on SQL Server 2008, Cloud Services, and More
What he thinks about the latest SQL Server release, Kilimanjaro, Madison, and Azure.http://www.sqlmag.com/Articles/ArticleID/101093/101093.html?Ad=1 

Reduce IT Spending While Still Upgrading to SQL Server 2008
The latest features and functionality included out-of-the-box with SQL Server 2008 will allow IT managers to save a tremendous amount of money and at the same time reduce management costs, hence it is worth the investment and you should upgrade to SQL Server 2008.  http://www.networkworld.com/community/node/37293 

Other
Technical RollUp
http://blogs.technet.com/trm/default.aspx    

DownloadsSQL 2005
Partial Database Availability
SQL Server 2005 features that enable appropriately designed solutions to achieve partial availability and remain online even after a disk subsystem hardware failure.http://www.microsoft.com/downloads/details.aspx?displaylang=en&familyid=11a7ae65-c792-47ea-8b4e-fb87d0ece9b6   

SQL 2008
Partial Database Availability
SQL Server 2005 features that enable appropriately designed solutions to achieve partial availability and remain online even after a disk subsystem hardware failure.http://www.microsoft.com/downloads/details.aspx?displaylang=en&familyid=11a7ae65-c792-47ea-8b4e-fb87d0ece9b6    

Events/Webcasts
TechNet Webcast: How Microsoft IT Manages Vendor Development Projects (Level 300)
Tuesday, July 28, 2009 9:30 AM Pacific Time (US & Canada)
When managing vendor development projects, lack of metrics, or the dependence on a single metric, can lead to surprises by the project’s end.  Learn about the tracking system that Microsoft IT uses to manage software development projects from a client perspective. Agile Cross IT Engineering (AXE) generates telling project metrics from a small amount of provided data. The system provides immediate project feedback, and supports overall project and vendor analysis.              http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032417615&culture=en-US    

New KB'sOther
SQL Svr Compact Edtn 3.5 AL: FIX: Access violations occur when you run an application under heavy load conditions after you install the 64-bit version SQL Server Compact 3.5 Service Pack 1http://support.microsoft.com/kb/970269/EN-US   
SQL Svr Compact Edtn 3.5 AL: FIX: Initial synchronization of a replication to SQL Server Compact 3.5 subscribers takes significant time to finishhttp://support.microsoft.com/kb/970414/EN-US   

SQL 2000
SQL Svr 64-bit 2000 EN: FIX: An access violation occurs when you run a DELETE statement or an UPDATE statement in the Itanium-based versions of SQL Server 2000 after you install security update MS09-004http://support.microsoft.com/kb/971524/EN-US   

SQL 2005
SQL Svr 2005 Analysis Svcs EN: FIX: Measures, measure groups, and dimensions that are not part of a perspective are exposed incorrectly when you create a grouping on the perspective in SQL Server 2005 Analysis Services or in SQL Server 2008 Analysis Services (Updated)http://support.microsoft.com/kb/968900/EN-US   

SQL Svr 2005 Analysis Svcs EN: FIX: Error message when you run an MDX query that requests a dimension property on a parent-child hierarchy in Microsoft SQL Server 2005 Analysis Serviceshttp://support.microsoft.com/kb/970551/EN-US   

SQL Svr 2005 Analysis Svcs EN: FIX: An MDX query that uses the NON EMPTY clause and the DrillDownLevel function returns null values in SQL Server 2005 Analysis Serviceshttp://support.microsoft.com/kb/970966/EN-US   

SQL Svr 2005 Reporting Svcs EN: How to install a 32-bit version of SQL Server 2005 Reporting Services on a computer that is running a 64-bit version of Windows (Updated)http://support.microsoft.com/kb/934162/EN-US   

SQL Svr 2005 Reporting Svcs EN: How to install and how to configure SQL Server 2005 Reporting Services on a computer that is running Windows Server 2008 (Updated)http://support.microsoft.com/kb/938245/EN-US   

SQL Svr 2005 Reporting Svcs EN: FIX: Error message when you open a report by specifying multiple values for a multivalued parameter in a URL in SQL Server 2005 Reporting Services: "Default value or value provided for the report parameter '<Parameter>' is not a valid value" (Updated)http://support.microsoft.com/kb/970058/EN-US   

SQL Svr 2005 Reporting Svcs EN: Error message when you view a report from Report Manager, ReportViewer, or a SharePoint site: "Execution '<SessionID>' cannot be found (rsExecutionNotFound)"http://support.microsoft.com/kb/972328/EN-US   

SQL Svr Standard Edtn 2005 EN: The SQL Server 2005 builds that were released after SQL Server 2005 Service Pack 2 was released (Updated)http://support.microsoft.com/kb/937137/EN-US   

SQL Svr Standard Edtn 2005 EN: FIX: When you run a query against a table that contains Traditional Chinese characters, the incorrect result is returned in SQL Server 2005 (Updated)http://support.microsoft.com/kb/948567/EN-US   

SQL Svr Standard Edtn 2005 EN: FIX: Data results are inconsistent when you run a search query that uses the NEAR operator against an XML type data that contains the xml:lang attribute (Updated)http://support.microsoft.com/kb/956574/EN-US   

SQL Svr Standard Edtn 2005 EN: The SQL Server 2005 builds that were released after SQL Server 2005 Service Pack 3 was released (Updated)http://support.microsoft.com/kb/960598/EN-US   

SQL Svr Standard Edtn 2005 EN: FIX: When you run a query that has a parallel execution plan in parallel under the READ COMMITTED isolation level on a database that is set the READ COMMITTED SNAPSHOT option in SQL Server 2005, the query that you may obtain may return uncommitted data (Updated)http://support.microsoft.com/kb/961050/EN-US   

SQL Svr Standard Edtn 2005 EN: FIX: The SQL Server service stops responding, and you receive error 1450 when you create database snapshot or run a DBCC CHECK statement on a database in SQL Server 2005 and in SQL Server 2008 (Updated)http://support.microsoft.com/kb/967164/EN-US   

SQL Svr Standard Edtn 2005 EN: Cumulative update package 3 for SQL Server 2005 Service Pack 3 (Updated)http://support.microsoft.com/kb/967909/EN-US   

SQL Svr Standard Edtn 2005 EN: FIX: Error message when you send an e-mail by using Database Mail in SQL Server 2005 or in SQL Server 2008: "The operation has timed out" (Updated)http://support.microsoft.com/kb/968834/EN-US   

SQL Svr Standard Edtn 2005 EN: FIX: An assertion failure occurs when you switch the schedule of a report subscription between a shared schedule and a custom schedule in SQL Server 2005 Report Serviceshttp://support.microsoft.com/kb/969528/EN-US   

SQL Svr Standard Edtn 2005 EN: FIX: You receive inconsistent results when you run index-related DMVs to return statistical information about missing indexes in SQL Server 2005 (Updated)http://support.microsoft.com/kb/969844/EN-US   

SQL Svr Standard Edtn 2005 EN:
FIX: Error message when you run a SQL Server 2005 or SQL Server 2008 Integration Services package (SSIS) after you install the .NET Framework 2.0 SP2 or the .NET Framework 3.5 SP1: "ADO NET Source [1424]"http://support.microsoft.com/kb/969845/EN-US   

SQL Svr Standard Edtn 2005 EN: Cumulative update package 14 for SQL Server 2005 Service Pack 2http://support.microsoft.com/kb/970278/EN-US   

SQL Svr Standard Edtn 2005 EN: FIX: The SQL Server service consumes lots of memory when you run a query that uses the sys.dm_exec_sql_text dynamic management function in the APPLY operator in SQL Server 2005http://support.microsoft.com/kb/970648/EN-US   

SQL Svr Standard Edtn 2005 EN: FIX: Error message in the Errorlog file when you run a query in SQL Server 2005: "A time-out occurred while waiting for buffer latch" (Updated)http://support.microsoft.com/kb/970672/EN-US   

SQL Svr Standard Edtn 2005 EN: FIX: CPU usage increases when you run a query that uses a string comparison function on a computer that has many processors after you upgrade to SQL Server 2005 Service Pack 3 (Updated)http://support.microsoft.com/kb/970823/EN-US   

SQL Svr Standard Edtn 2005 EN: FIX: Error message when you run a report in SQL Server 2005 SP2 Reporting Services by using a URL from a subscription: "Object reference not set to an instance of an object."http://support.microsoft.com/kb/971114/EN-US   

SQL Svr Standard Edtn 2005 EN: FIX: Error message when you send an e-mail by using a private Database Mail profile in SQL Server 2005: "profile name is not valid"http://support.microsoft.com/kb/971402/EN-US   

SQL Svr Standard Edtn 2005 EN: FIX: Error message when you run a query that contains duplicate join conditions in SQL Server 2005: "Internal Query Processor Error: The query processor could not produce a query plan"http://support.microsoft.com/kb/971409/EN-US   

SQL Svr Standard Edtn 2005 EN: FIX: An UPDATE or a DELETE statement that uses a join on a remote table in a linked server runs very slowly  in Microsoft SQL Server 2005http://support.microsoft.com/kb/971529/EN-US   

SQL Svr Standard Edtn 2005 EN: FIX: The synchronization operation fails when you run the Merge Agent to synchronize a merge publication that uses Web synchronization in SQL Server 2005http://support.microsoft.com/kb/971607/EN-US   

SQL Svr Std Edtn 2005 64Bit EN:
The "SQL Server:SSIS Pipeline" performance object does not appear in the "Performance object" list after you install Integration Services from a 64-bit version of SQL Server 2005 (Updated)http://support.microsoft.com/kb/941154/EN-US   

SQL 2008
SQL Svr Enterprise 2008 EN: How to update or slipstream an installation of SQL Server 2008 (Updated)http://support.microsoft.com/kb/955392/EN-US   

SQL Svr Enterprise 2008 EN: List of known issues when you install SQL Server on Windows 7 RC or on Windows Server 2008 R2 RC (Updated)http://support.microsoft.com/kb/955725/EN-US   

SQL Svr Enterprise 2008 EN: Error message when you try to batch insert data on a publisher of a merge replication in SQL Server 2008 or SQL Server 2005: "Msg 548, Level 16, State 2, Line 1. The insert failed"http://support.microsoft.com/kb/972006/EN-US   

SQL Svr Enterprise 2008 EN: SQL Server Failover Cluster Instance is taken offline when you change the password for the service account on the non-owner nodehttp://support.microsoft.com/kb/972387/EN-US   

SQL Svr Standard 2008 EN: The ActiveX Script task in an Integration Services package does not run after you uninstall SQL Server 2005 from a computer that has SQL Server 2008 installed (Updated)http://support.microsoft.com/kb/955713/EN-US   

SQL Svr Standard 2008 EN: FIX: A time-out occurs when you use SQL Server Management Studio to try to restore a large database from a backup on a tape in SQL Server 2008 (Updated)http://support.microsoft.com/kb/967205/EN-US   

SQL Svr Standard 2008 EN: FIX: Error message when you perform an update or a delete operation on a table that does not have a clustered index created in SQL Server 2005 and in SQL Server 2008: "The operating system returned error 1450" (Updated)http://support.microsoft.com/kb/967470/EN-US   

SQL Svr Standard 2008 EN: FIX: Error message when you run a job whose owner is a Windows user account after SQL Server 2005 Agent or SQL Server 2008 Agent is restarted: "The job failed.  The owner () of job <Job Name> does not have server access" (Updated)http://support.microsoft.com/kb/969611/EN-US   

SQL Svr Standard 2008 EN: Stop error message when you install SQL Server 2005 Service Pack 3 or SQL Server 2008 on a Windows XP-based computer that is earlier than Windows XP SP3: "CRITICAL_OBJECT_TERMINATION" (Updated)http://support.microsoft.com/kb/969936/EN-US   

SQL Svr Standard 2008 EN: FIX: You receive an incorrect result when you query data from a linked server that is created by using an index OLE DB provider in SQL Server 2005 or in SQL Server 2008 (Updated)http://support.microsoft.com/kb/969997/EN-US   

SQL Svr Standard 2008 EN: FIX: Queries from a query notification that has a lot of subscriptions run slowly when you change the  Auto Close option to False in SQL Server 2005 or in SQL Server 2008 (Updated)http://support.microsoft.com/kb/970349/EN-US