I’ve recently been moving servers and when I moved the databases, I was unable to re-attach them with a different path for the filestream folder. The following blog post has a solution -
Archive for the ‘Uncategorized’ Category
Re-attaching SQL Server 2008 databases with Filestreams in a different path
Wednesday, July 7th, 2010
Finding missing foreign keys in Transact SQL (for MS SQL Server)
Tuesday, May 25th, 2010When I put together a database, I generally use integer ID fields to reference one table from another and I usually have the referencing field ending in “ID”. e.g if I have Customer and Order tables, Order will have a CustomerID field.
The problem is that in a big database, it is easy to forget to enforce the foreign key constraints for each and every one, so I thought it would be useful to have a query to find them. After a bit of playing about, I have ended up with…
select table_name,COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS cols where COLUMN_NAME <>'ID' and COLUMN_NAME like '%ID'
and TABLE_NAME not in('dtproperties')
and (select COUNT(*) from INFORMATION_SCHEMA.KEY_COLUMN_USAGE keys where keys.TABLE_NAME=cols.TABLE_NAME and keys.COLUMN_NAME=cols.COLUMN_NAME)=0
order by TABLE_NAME
You can add in extra tables to exclude in the “TABLE_NAME not in” clause. I’ve put dtproperties in there because that is the internal table that was showing up when I didn’t want it to.
I’ve tested this in SQL Server 2008, but I suspect that it should be good for anything from SQL 7.0 upwards.
Electricity Monitoring Part 1
Thursday, April 15th, 2010This entry is part of a series, Electricity Monitoring»
This is a rather different topic to my usual posts. A few weeks ago, I invested in an electricity monitor. In case you haven’t come across them, they are little devices which monitor your electricity usage and display useful information, such as your current and historical usage. Because I am a programmer, I did a bit of research to find one that I could access the data on and ended up with a Current Cost ENVI-CC128, although I did also need to get the USB Data Cable to connect it to my PC.
I was quite impressed with how easy it is to connect the transmitter (it just hooks around a specific cable going into the electicity meter (you can get extra transmitters – it can take up to three if you’ve got a 3 phase supply or multiple meters)). The USB cable has practically no documentation though, other than a little information on Current Cost’s website and a total lack of understanding what I was asking for from their technical support people, which is a little odd, considering that they don’t actually supply any software with it themselves – you have to find it yourself or write some.
I worked out fairly quickly (with a little help from Googling other people’s experiences with the CC128) that the USB connection works as a simulated serial port which spews XML out when it receives a reading from the transmitter (usually every 6 seconds). It also spews out totals every hour, but I haven’t bothered with those yet as I have a machine running the whole time, so I am currently just logging the readings to an SQL Server database as they happen.
I’ve also written a nice little program using ComponentOne’s charting component (C1Chart) so that I can specify a date/time range and get a graph of usage, either by individual reading or grouped by minute, hour, day, month or year (eventually!). This has provided me with all sorts of interesting information, and given me a good picture of our electricity usage.
Entries in this series:
- Electricity Monitoring Part 1
- Electricity Monitoring Part 2
Finding a table in a Linq to SQL DBML file in Visual Studio 2008
Monday, March 1st, 2010One problem that can take up a lot of time if you are working in a large project with a lot of tables in the DBML database model file when using Linq to SQL in Visual Studio is finding the table in the diagram. Control+F doesn’t work, and it can be a real pain to scroll around for ages. However, the following article mentions a very simple solution – just use the dropdown at the top of the properties window! Seems obvious, but I know quite a few people have been complaining about that for months!
Taken from Is there a way to find a table in a DBML file in Visual Studio 2008? – Stack Overflow.
Using sp_change_users_login ‘auto_fix’ to fix user/login
Sunday, February 21st, 2010I have an occasional problem when moving a database from one server to another that although the users on the database are transferred correctly, the logins on the server are not. This is fair enough, as the logins are associated with the server, and the users are associated with the database. The problem is that there is no obvious way to re-create the logins correctly as if you try to do it, either the login won’t map to the old user, or it will complain that it can’t create a new user because it already exists.
One solution is to simply delete the old users and re-create, but this can be a real pain in the neck, especially if you have complex rights or can’t delete them because of the way things are configured. You can get around this with renaming users and re-creating and then switching things over and deleting the old ones, but this is all very tedious.
Fortunately, there is a stored procedure that will deal with the problem. First of all, create the new login without mapping it to a user. Once you have done that, open a query window in the appropriate database and run
sp_change_users_login ‘auto_fix’, ‘username‘
where username is the username that you want to fix. It takes a split second to run, so you can do a whole load of them without too much hassle.
Windows Forms controls and the red X
Thursday, December 17th, 2009I have been working on a project where certain controls randomly seem to come up with a red X and a red border around it. I wasn’t sure what was causing it, but it turns out it is when an exception is thrown by code in the paint event. For more details, see the following blog from sturmnet.org: WinForms controls and the red X.
Exceptions Explained: NullReferenceException
Sunday, November 8th, 2009This is the first on a new series of posts that I’m going to be doing on explaining various different exceptions, what they mean, when you might get them and how to avoid them.
The first one is the NullReferenceException, which usually comes with the message “Object reference not set to an instance of an object.”. This Exception occurs when you try to access an object reference which is set to Nothing.
First of all, you need to understand the difference between value types and reference types in .NET. With a value type, we only care about what data is stored within it. With a reference type, the actual instance of the type is important in itself. For example, we may have an Integer, which contains 10. Integers are value types, and there is generally nothing particularly different between one number 10 and another number 10 if they are both Integers, so they are effectively identical.
Reference types on the other hand are different. If we have two customers, and both of them happen to be called “John Smith”, we don’t want to treat them as being the same thing just because they have the same name. The other difference with reference types is that we can have an empty reference – Nothing (or null in c#).
In fact, behind the scenes, reference types store a location in memory of the instance. In older programming languages, such as C/C++, these were known as pointers, and you could actually access them just like a number and change the item that they were pointing to with arithmetic operators (+/-/increment/decrement etc.) This created a number of potential problems and so references were created to protect pointers from causing too much damage. References also the garbage collector to keep track of what is referencing an object instance so that the instance can be cleared up when it is no longer in use.
The NullReferenceException is thrown when you attempt to access an object reference which is set to Nothing. This can easily happen because as mentioned above, all variables of a reference type will be initialised to Nothing by the .NET framework if they have not been explicity set to something else.
For example,
Dim c As Customer
c.Name = "John Smith"
The second line in the above piece of code will throw a NullReferenceException as the variable c is still set to Nothing, and when the .NET framework attempts to access the Name field, it will find that the object does not exist.
This is easily resolved by setting c to whatever Customer you wish to use, or to a New Customer.
Error when using SQL Management Studio 2008 in Windows 7 x64 with Vault Installed
Friday, October 30th, 2009I 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: 0×80004002 (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, 2009Sometimes 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!
Getting the ASP.NET Development Web Server to use a root path
Friday, July 10th, 2009ASP.NET 2.0 comes with a test webserver which can be run by simply pressing F5 in Visual Studio from a website project which is located on your PC. The only problem is that for reasons best known to Microsoft, it launches with the site configured in a subfolder. This isn’t always convenient, as you may have paths relative to the site root which prevent this being practical for testing.
In order to get around this problem, you need to take the following steps: -
First of all, configure Visual Studio so that you can launch the test server manually as follows: -
Under the Tools menu, select External Tools.
Add a new entry
Call it something like ASP.NET Development Server
Command is C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\WebDev.WebServer.EXE (you may need to alter the path for your local machine)
Arguments are /port:80 /path:$(ProjectDir) (note that you will need to leave a space on the end of this for it to work properly. Also, you can change the port number if you wish)
Press OK. You can now launch the development server from the new entry on your Tools menu. This will show in your system tray. You should probably remember to close it when you are done.
The next step is to configure your project to use the server. Right click on your project and click Property Pages. If an empty dialogue comes up, press cancel and repeat the process – it should work second time. Under Start Options, select Use custom server and leave the Base URL blank. You may wish to change the start action as well.
Once you are done, you can press F5 to start debugging. Don’t forget that next time you open the project you will need to start the server from the tools menu before you start debugging again, otherwise it won’t work.
Also, bear in mind that if this is a copy of a remote site, things like database connection strings may need changing. Don’t forget to be careful not to overwrite any settings when you copy back if that is the case!
If you do want to develop on a copy of the site, the Website menu has a useful option to Copy website.