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

Posted by sqldbatips | with no comments
Filed under: ,

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

 

Posted by sqldbatips | 1 comment(s)
Filed under: ,

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

 

Posted by sqldbatips | with no comments
Filed under: , ,

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

Posted by sqldbatips | with no comments
Filed under: , ,

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!

Posted by sqldbatips | 1 comment(s)
Filed under:

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

Posted by sqldbatips | with no comments
Filed under: , ,

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

 

Posted by sqldbatips | with no comments
Filed under: ,

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)

 

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!

Posted by sqldbatips | with no comments
Filed under: ,

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.

Posted by sqldbatips | with no comments
More Posts « Previous page - Next page »