Recent Posts

And peak visitors now up to 10k per month (UK Based) and registration of fayres as are the directory services entries.

But don't forget to then add yourself as a contributor!

Lots of posts about the problems here - but all of them appear to be incomplete!

Basically it all boils down to permissions - need to run SSRS under an account that has access to SQL Server, remove anonymous authentication and make sure that the SSRS account has full permissions to the folder - then it worked for me.

Error: The SQL Server 2005 Express Tools are installed To continue first remove the SQL Server Express 2005 tools
 
They werent installed and not in add remove programs - tried to download them again to do an uninstall no luck - then found this link and the solution worked - just load regedt32 and deleted the entry note that i didn't have sql prompt installed so i still dont know the root cause!
 
Upgrades were working fine it was only the full install that was reporting errors!

The key tables for any sort of database index tuning are :-
sys.dm_db_index_usage_stats
sys.dm_db_missing_index_details
sys.dm_db_missing_index_groups
sys.dm_db_missing_index_group_stats

Key point though - these statistics are cumulative since the last serve restart so don't try tuning on a cold server!

Resources
dm_db_index_usage_stats - http://msdn.microsoft.com/en-us/library/ms188755.aspx
dm_db_missing_index_details - http://msdn.microsoft.com/en-us/library/ms345434.aspx
dm_db_missing_index_groups http://msdn.microsoft.com/en-us/library/ms345407.aspx
dm_db_missing_index_group_stats  http://msdn.microsoft.com/en-us/library/ms345421.aspx

Note that missing index details will be captured automatically unless SQL is started with the -x flag and that there are quite a few limitations to the missing indexes so if you've got over 500 missing index groups it's not really going to help you
 

Dense_rank and row_number together can be used very effectively for pulling together exact matches

;with mymatches as
(
Select row_number() over(partition by houseno, add1,add2,add3, postcode order by primarykey  ) as rn
,dense_rank() over (order by houseno, add1,add2,add3, postcode) as rn2
,primarykey
from mysourcetable
)
select a.primarykey as masterpk, b.primarykey as mergepk from mymatches a join mymatches b
on a.rn2 = b.rn2 and a.rn = 1 and b.rn > 1

Gives a list of records where we have identical addresses - the dense_rank order must be identical to the partition list

Alternative matching

Don't forget that SSIS has built in fuzzy matching algorithms - however, house numbers can cause even that problems - use a fuzzy lookup to compare the addresses although you may find that performance isn't that good on a large data set - again having at least one attribute whether it's postcode, post town or locality will allow you to match with a slightly higher performance hit.

The best matches are normally performed on a generated match key - however - you may find it's better to isolate house numbers before matching since a lot of algorithms will see 1-11 the same as 111 and 11-1 for the house number - these are potentially all different properties though.

Again - this can be done with a replace string or a regular expression or roll your own CLR

There needs to be at least one "partially" matching key to get good results - if you have postcodes thats fine and even partial postcodes will stop you generating a cross join for all records in the database

The quality of data varies vastly between systems and the good old soundex algorithm on SQL server is a complete waste of time for matching addresses. The main issues are

• House Numbers are going to generate invalid keys soundex of 7 my street = 0000
• Spaces or punctuation stop soundex from working select SOUNDEX('my house '), SOUNDEX('myhouse') returns M000 and M200

Exact matching has the same problem e.g. "my-house" and "my house" and "myhouse" are different.

One option to get round some of these problems is simply remove any none alphanumeric characters - this can be as complex as a replace statement removing any unwanted characters e.g. replace(replace(replace(field,' ',''),'-',''),'"','') etc. for any unexpected characters, or use a regular expression or a clr routine - there are a lot of published CLR routines and regular expression CLR's as examples

Other options include using a match algorithm e.g. JaroWinkler or Levenshtein on the "cleansed" addresses to see how different they are.

We are not accepting new registrations especially if you are not UK based lol