I recently hit a scenario (SQL Server 2012 Standard, 11.0.5058) where I was trying to pull out the execution plan for a stored procedure from the plan cache, but the query shown below was returning a NULL query plan:
[Read More]SQL Server 2008 R2 in-place upgrade error
Today I encountered the following error during the process of performing an in-place upgrade of a SQL Server 2008 instance to 2008 R2:
The specified user 'someuser@somedomain.local' does not exist
I wasn’t initially sure what that related to - I hadn’t specified that account during the upgrade process so I went looking in the Services managemement console. The SQL Server service for the instance I was upgrading was configured to “Log On As” that account and it had been happily running prior to the upgrade.
[Read More]
GB Post Code Importer Conversion Accuracy Fix
In a post last year (Ordnance Survey Data Importer Coordinate Conversion Accuracy) I looked into an accuracy issue with the conversion process within the GeoCoordConversion DLL that I use in this project (blog post). Bottom line, was that it was a minor with an average inaccuracy of around 2.5 metres and a max of ~130 metres by my reckoning. I’ve since had a few requests asking if I can supply an updated GeoCoordConversion DLL with fixes to the calculations.
[Read More]
SQL Server Table Designer Bug With Filtered Unique Index
A colleague was getting a duplicate key error when trying to add a new column to a table via the Table Designer in SQL Server Management Studio (2008R2 - not tested on other versions), despite there being no violating data in the table. After a bit of digging around, I tracked the problem down to what appears to be a bug in Table Designer when there is a unique, filtered index in place on the table and the table is being recreated (i.
[Read More]
Ordnance Survey Data Importer Coordinate Conversion Accuracy
Update 13 March 2013: Please see latest blog post (fix) on this here.
Thanks to a comment on my original post around my project that imports Ordnance Survey CodePoint data into SQL Server, I was made aware of a potential issue with the (awesome) third party GeoCoordConversion DLL I use to convert the Eastings/Northings coordinates supplied in the Ordnance Survey data files, into Latitude/Longitude coordinates. The issue relates to an inaccuracy in the conversion process, specifically to do with integer divisions instead of double.
[Read More]
Quick win - check your table variable use
Quick wins are awesome. Making a change that takes minimal effort and yields a significant performance improvement is very satisfying.
This particular potential quick win relates to the use of table variables vs. temporary tables. Have a non-trivial stored procedure that produces some intermediary results and stores in a table variable which then goes on to be used further in the stored procedure? Consider evaluating a switch to a temporary table instead.
[Read More]
Excluding nodes from XML data before returning from SQL Server
This post follows on from a question I recently replied to, for how to exclude a specific node from an XML column value before returning it, using TSQL.
Example setup CREATE TABLE Example ( ID INTEGER IDENTITY(1,1) PRIMARY KEY, XmlField XML ) INSERT Example (XmlField) VALUES ('<Root><ChildA>Value I want to see</ChildA><ChildB>Value I do not want to see</ChildB></Root>') So if you want to return the XML minus the ChildB node, how do you do it?
[Read More]
sp_executesql change between 2005 and 2008
Today I tripped over what turned out to be a difference in the way sp_executesql behaves between SQL Server 2005 and 2008 when executing a string containing a parameterised stored procedure call.
Take this simplified example:
DECLARE @SQL NVARCHAR(256) SET @SQL = 'sp_help @obj' EXECUTE sp_executesql @SQL, N'@obj NVARCHAR(100)', 'sp_help' In SQL Server 2008 (10.0.4000.0), the above executes successfully.
In SQL Server 2005 (9.00.1399.06), it throws the following exception:
Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'sp_help'.
[Read More]
The importance of "Working Set"
One of the things that I see cropping up pretty often is this thing called “working set”. After recently chipping in on another StackOverflow question on the subject of “What does it meant to fit ‘working set’ in RAM?”, I thought it was a good subject for a blog post. This is really just a copy and extension of my input on that question and focused in certain parts on MongoDB, but is also as relevant to other databases.
[Read More]
OS Data Importer Supports Scale Gazetteer Dataset
I’ve just pushed some updates to GitHub for the Ordnance Survey Data Importer .NET app I’ve been working on every now and then (see my previous posts: GB Post Code Geographic Data Load to SQL Server using .NET and OS CodePoint Data Geography Load Update).
Aside from a little re-jigging to potentially pave the way to load more Ordnance Survey data files to SQL Server in the future, it now supports importing the 1:50000 Scale Gazetteer data file to SQL Server (available to download from here).
[Read More]