Archive for October, 2009

Error when using SQL Management Studio 2008 in Windows 7 x64 with Vault Installed

Friday, October 30th, 2009

I have been using my new Windows 7 machine for the last few days and have hit a few problems getting things set up. Fortunately, I have managed to work around most of them. This was one of the first ones.

I was using Microsoft SQL Management Studio 2008. When I attempted to pop out the list of databases on a server in object explorer, I got the following error: –

“Unable to cast COM object of type ‘System.__ComObject’ to interface type ‘Microsoft.VisualStudio.OLE.Interop.IServiceProvider’. This operation failed because the QueryInterface call on the COM component for the interface with IID ‘{6D5140C1-7436-11CE-8034-00AA006009FA}’ failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).”

After a little research, I found several pages with different solutions to the problem. A common thread is that it seems to involve SourceGear Vault Client (source control software). I suspect that this is only a problem for people who are using old versions of it (I’m using the spectacularly old version of 2.0.6!).

The solution which finally worked for me was to copy the Vault client folder, uninstall Vault, copy it back and make manual shortcuts. Then, in order to register it as a source control provider, I used the following registry file: –
Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\SourceCodeControlProvider]
"ProviderRegKey"="Software\\SourceGear\\Vault Client"

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\SourceCodeControlProvider\InstalledSCCProviders]
"SourceGear Vault Client"="Software\\SourceGear\\Vault Client"

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\SourceGear]

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\SourceGear\Vault Client]
"SCCServerName"="SourceGear Vault Client"
"SCCServerPath"="C:\\Program Files (x86)\\SourceGear\\Vault Client\\VaultIDE.dll"

To use a registry file, you need to copy and paste the code into a new notepad file and save it with a .reg extension. When you double click on it, Windows will ask if you want to add it to the registry. You may need to change your SCCServerPath before saving the file.

Thanks to Henrik Bruun’s post on this page for the registry fix.

Locking Records to Prevent Editing in Transact SQL (Microsoft SQL Server)

Tuesday, October 6th, 2009

Sometimes when I’m writing code, I come up with a solution that I’m particularly pleased with because it is just so neat.  This is one of those times.

I’ve not been posting much on this blog recently because I’ve been very absorbed in a big project and been working crazy hours to get the coding done.

I should explain that this post is not about record locking to prevent concurrent update problems.  This is about being able to put a more permanent lock on a record in order to stop updates being made to it until the lock is removed.

The project that I’ve been working on has some shared records in a few tables that are used by a large number of other records in other tables.   The problem is that something was overwriting these shared records, which was pretty disasterous.  I managed to resurrect the data, which wasn’t such a problem, but I was very worried about how to stop it happening again, since these records are accessed in a number of different places.

The obvious first step was to audit the code and check for what was doing it.  I did that and identified some likely suspects and built in some code to prevent it happening again, but I wanted more reassurance than that, so I decided to try to get SQL Server itself to enforce a lock on them.  The solution that I came up with was to use a trigger.

The code of the trigger is as follows

CREATE trigger [dbo].[trgEnforcePersonLock] ON [dbo].[Person]
for UPDATE
AS
BEGIN
	if (select count(*) From deleted WHERE Locked=1)>0 AND (select count(*) From inserted WHERE Locked=1)>0
	begin
		rollback transaction
		raiserror('Cannot modify a locked person!',16,1)
	end 

END

The inserted virtual table contains the new data and the deleted virtual table contains the old. I’m checking that the update isn’t unlocking it, because otherwise we’ll never be able to! It works by checking whether the record is locked and if so, rolling back the transaction and raising an error. From testing, it looks like it works pretty well!