sqldbatips.com blog

New release of Reporting Services Scripter available (2.0.0.17)

sqldbatips — Fri, 15 May 2009 15:42:00 GMT

A new release of Reporting Services Scripter is now available for download

Fixes in release 2.0.0.17

Added fix for command line tool not accepting unc share paths as valid file paths for parameters

Disk Partition Alignment Best Practices for SQL Server whitepaper

sqldbatips — Tue, 12 May 2009 15:44:00 GMT

This paper documents performance for aligned and nonaligned storage and why nonaligned partitions can negatively impact I/O performance; it explains disk partition alignment for storage configured on Windows Server 2003, including analysis, diagnosis, and remediation; and it describes how Windows Server 2008 attempts to remedy challenges related to partition alignment for new partitions yet does not correct the configuration of preexisting partitions.

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

New release of Reporting Services Scripter available (2.0.0.16)

sqldbatips — Wed, 06 May 2009 14:21:00 GMT

A new release of Reporting Services Scripter is now available for download

Fixes in release 2.0.0.16

Added fix for fixing up shared datasource references for SQL 2008 when loading rdl files using RSScripterCmd

New Functionality in release 2.0.0.16

Added new parameter to RSScripterCmd to allow shared datasource references to be in a different folder when loading rdl

Reporting Services Linked Report Generator updated for SQL 2008

sqldbatips — Tue, 28 Apr 2009 20:09:00 GMT

A new release of Reporting Services Linked Report Generator is now available (1.0.0.8) confirming support for SQL 2008 Reporting Services. Reporting Services Linked Report Generator is a .NET Windows Forms application that enables automatic generation of linked reports to any number of Reporting Services folders based on a single base report. This can be useful where you have a hierachical folder structure and need to deploy the same report to multiple levels of the folder hierachy at the same time. It also preserves report size and margin properties for linked reports.

Download Linked Report Generator

New Release of Reporting Services Scripter (2.0.0.15) confirms SQL 2008 support

sqldbatips — Thu, 16 Apr 2009 18:02:00 GMT

Judging by the increasing volume of emails I have recieved over the last few months asking about SQL 2008 support in Reporting Services Scripter there is a definate increase in the uptake of SQL Server 2008 Reporting Services and so I'm happy to say I've released a new build to confirm support for SQL 2008. This is mainly a documentation update but also includes some fixes that were added in private builds for specific customers. As the Report Server endpoint has not changed in SQL 2008 (as far as the methods the tool uses) the older versions did work fine but I wanted to do some thorough testing before confirming support and with the release of SP1 for SQL 2008 now seems like a good time. As always feedback and suggestions for enhancements are always welcome.

Download Reporting Services Scripter

Cumulative update package 1 for SQL Server 2008 Service Pack 1 now available

sqldbatips — Thu, 16 Apr 2009 06:33:00 GMT

Cumulative update package 1 for SQL Server 2008 Service Pack 1 KB Article

Cumulative Update 1 for SQL Server 2008 Service Pack 1 (SP1) contains only hotfixes that were released in Cumulative Update 4 for SQL Server 2008. These hotfixes are not included in SQL Server 2008 Service Pack 1. Cumulative Update 1 for SQL 2008 SP1 is only intended as a post-service pack rollup for Cumulative Update 4 for the release version of SQL Server 2008 customers who plan to upgrade to SQL Server 2008 SP1 and still keep the hotfixes from Cumulative Update 4 for the release version of SQL Server 2008. No new hotfixes have been included in this cumulative update other than the hotfixes that were already released in Cumulative Update 4

SQL 2008 Service Pack 1 RTM now available

sqldbatips — Wed, 08 Apr 2009 15:19:00 GMT

Service Pack 1 for SQL Sevrer 2008 is now available (download link below). Two of the key features in this are the ability to slipstream the service pack with the RTM media to provide a prepatched install (which I tested during the CTP and seems to work great although it's a bit manual) and the ability to uninstall the service pack (will try this soon and report back) which should hopefully provide a quicker backout than the previous uninstall/reinstall route.

Download SQL 2008 SP1

Instructions for creating a slipstreamed SP1 install

Updated build of ExpressMaint now available (1.8.0.0)

sqldbatips — Tue, 07 Apr 2009 20:39:00 GMT

An updated build of the ExpressMaint utility for SQL 2005/2008 is now available. This release contains the following changes

Added support for SQL Authentication
Fixed an issue with databases that have not started being filtered out in Express Edition

Download links below

Download ExpressMaint from Codeplex

Download ExpressMaint from sqldbatips.com

Updated build of ExpressMaint utility is now available (1.7.0.0)

sqldbatips — Sat, 07 Mar 2009 17:47:00 GMT

An updated version (1.7.0.0) of the ExpressMaint utility for automating database maintenance operations for SQL 2005/2008 Express Edition is now available. This new version includes the following fix

Fixed error when backing up instance with user instances option enabled

Download Latest Version from Codeplex

Download Source Code from Codeplex

Very forgiving T-SQL parser behaviour

sqldbatips — Sat, 17 Jan 2009 17:03:00 GMT

One of the thinks I like about working with SQL Server is that there's always something new to learn. Sometimes, as in this case it doesn't have much of a practical benefit. Whilst running a trace on a database application to troubleshoot why it wasn't working properly I noticed a query that looked a little odd. It had spaces between the user and object name in the from clause (it was a SQL 2000 instance hence user rather than schema) e.g. it had a query that looked something like the picture below

This parses and executes absoloutely fine so I wondered what would happen if I increased the amount of whitespace as shown below

Again this works fine and as demonstrated below you can even separate all the parts of the from clause across multiple lines.

As I said, not much practical use - I'm not sure how they even managed to end up writing an application that generate this T-SQL!

Updated build of ExpressMaint utility is now available

sqldbatips — Tue, 04 Nov 2008 18:17:00 GMT

An updated version (1.6.0.0) of the ExpressMaint utility for automating database maintenance operations for SQL 2005/2008 Express Edition is now available. This new version includes the following fixes

Fixed error when database name contains an apostrophe
Fixed error when no report file is specified for backup operations

Download Latest Version and View Updated usage notes from sqldbatips.com

Download Source Code from Codeplex

SQL 2005 Service Pack 3 Beta now available

sqldbatips — Wed, 29 Oct 2008 12:43:00 GMT

SQL 2005 Service Pack 3 Beta is now available for testing. Remember this is a beta for testing purposes only, do not deploy to production servers

View plus Synonym plus Linked Server plus Table equals Trouble

sqldbatips — Mon, 20 Oct 2008 19:01:00 GMT

Whilst synonyms can be a very useful tool for abstraction I am a firm believer that they shouldn't change the behaviour of queries that use them, they should simply be alternative names for objects (local or remote). Recently I came across an issue with views that use synonyms to reference tables in a remote database across a linked server. When the indexes are rebuilt on the remote table, queries that reference the view containing the synonym fail with the following error

The OLE DB provider "SQLNCLI" for linked server "REMOTESERVER" reported a change in schema version between compile time ("170677722198508") and run time ("170677722198546") for table ""tempdb"."dbo"."remotetable"".

This combination of local view->synonym->remote table is the only combination of objects that suffers from this issue. If you modify the view to reference the remote table using a four part name then the issue does not occur. This is my issue with this error, the synonym is altering the behaviour of the query in a non-intuitive way. If the synonym was truly just another name for the remote table it would not encounter this error.

I ran the following tests (script is attached to post) to check combinations of objects that would encounter this error and the only one was local view->synonym->remote table.

TEST	RESULT
SELECT FROM REMOTE TABLE DIRECT WITH 4 PART NAME	OK
SELECT FROM REMOTE VIEW DIRECT WITH 4 PART NAME	OK
SELECT FROM LOCAL VIEW USING 4 PART NAME TO TABLE	OK
SELECT FROM LOCAL VIEW USING 4 PART NAME TO VIEW	OK
SELECT FROM LOCAL VIEW USING SYNONYM TO REMOTE TABLE	ERROR
SELECT FROM LOCAL VIEW USING SYNONYM TO REMOTE VIEW	OK
SELECT FROM LOCAL SYNONYM TO REMOTE VIEW	OK
SELECT FROM LOCAL SYNONYM TO REMOTE TABLE	OK

The workaround is to either not use a synonym (not ideal) or create a "table view" (simple view doing a select * from the table) on the remote server and referencing that in the synonym.

** Note ** If you are using replication and are publishing views you will encounter this issue when the snapshot agen runs. It calls sp_MSreplupdateschema which as it suggests, updates the schema version of objects. If the object is a view referenced by a remote synonym in a view you will get the schema version error. The way to fix this is to call sp_refreshview on the local view containing the synonym (or don't publish the views)

Update This has been confirmed as a bug and will hopefully be fixed in a future release (see Connect Item 378549)

SQL 2008 Unleashed

sqldbatips — Mon, 15 Sep 2008 19:37:00 GMT

In the UK we had a Technet event at Microsoft Reading last Wednesday to celebrate the RTM of SQL Server 2008 (see more here). It was a great day with lots of interesting and fun sessions highlighting some of the benefits and new features of SQL Server 2008 as well as a Rock Band setup for the party afterwards (and yes I suck at Rockband). There was also a great cake as can be seen below!

First thing to do after installing SQL Server Management Studio 2008

sqldbatips — Wed, 10 Sep 2008 19:43:00 GMT

One of the strangest UI design decisions in Management Studio for SQL 2008 is that by default the Start Debugging button is right next to the Execute button as shown below

I myself have hit it by accident more times than I'd like to admit and I have seen many a demo (by MS staff as well) run into problems because of people hitting the Debug button instead of the Execute button! I think this is especially true for long time SQL users who have used Query Analyzer a lot as it looks exactly like the Execute button from QA. The good news is that you can simply remove it from the SQL Editor tool bar by clicking the Toolbar options button (as shown below) at the end of the SQL Editor toolbar, selecing Add or Remove buttons>SQL Editor and unchecking the Debug button. There is a debug toolbar you can use for debugging, it's just too annoying/dangerous having the Debug button right next to the Execute button.