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 -
Posts Tagged ‘SQL’
Re-attaching SQL Server 2008 databases with Filestreams in a different path
Wednesday, July 7th, 2010Finding 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.
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.
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!
Storing Partial/Incomplete Date/DateTime Values in SQL
Wednesday, March 4th, 2009I am currently working on a project where I am taking data from an old database. Unfortunately, many of the date fields in the database contain incomplete date values, simply because not all the data was available when it was entered (it was taken off paper, memory and other relatively unreliable storage media).
In the old database, these fields were stored as text. However, I wanted them to be searchable by date, so I was thinking about it and I have come up with a couple of possible ideas…
Firstly, you can store the centrepoint datetime and a float to represent a timespan in days of how accurate this is. Whilst I think it is possible to do an SQL query to query this (I haven’t actually tried it), it is a bit messy, and I think it would probably be a nightmare to write a Linq to SQL query or use another query framework.
Secondly, you can store the min and max datetime that it could be. This also allows you to do a little clever parsing when you retrieve the date from the database and for example, you can list a date which is in the database as 2009-01-01 to 2009-01-31 (in ISO date format) as “January 2009″.
Of course, both of these only work if the missing data is the least significant part (in mathematical terms). I mean, if you know the day and month, but not the year, neither of these schemes work. For that you’d probably need to use 3 nullable integer fields, but then searching on that would be a pain.
Installing SQL Server 2008 Management Studio Express
Wednesday, March 4th, 2009I used to use SQL Server 2005 Express and recently upgraded to 2008. SQL Management Studio 2005 won’t connect to 2008 – it gives an error, so I went looking for management studio express 2008, which I couldn’t find anywhere. I then tried downloading SQL Server 2008 Express to dig it out from there, which does work, but requires several pre-requisites. You might want to bear this in mind (and I might have missed something – let me know if I have)…
- Make sure you have .NET Framework 3.5SP1 installed
- Make sure you have Windows Installer 4.5 installed
- Make sure you have Windows Powershell installed
- Uninstall management studio 2005 if you have it installed
- Reboot (might not be necessary, but probably sensible)
- Download and run SQL Server 2008 Express With Tools
- Installation > New SQL Server stand-alone installation or add features to an existing installation
- follow through until you get to the features list
- Select Management Tools – Basic
- You’ll probably have to reboot again
That should be it. You should be able to download everything that you need freely from Microsoft’s site (I usually find it easiest googling it – Microsoft’s search engine seems a bit rubbish).
SQL Server Express Edition
Tuesday, March 18th, 2008SQL Server Express Edition is the free version of Microsoft SQL Server, and is a replacement for MSDE. It has almost all the features of the other versions of SQL Server. There is a common misconception that it is limited in terms of the number of connections it can accept. I am pretty sure this is something that people think because it used to be true of MSDE. It does have some limitations, however, including : -
- Only supports 1 CPU (although it won’t break if you put it on a multicore/multiprocessor system)
- Won’t use more than 1GB RAM
- Databases can’t grow to more than 4GB
This is fine for most jobs, but obviously if you have a really busy or big system then you’ll need to go for a higher edition or another alternative.
Distinct in Linq to SQL
Wednesday, March 12th, 2008I was trying to get Linq to SQL to do a Distinct query today, and found that it wasn’t quite so obvious how to do it.
This works: -
From item in table Select field Distinct
This doesn’t: -
From item in table Distinct Select field
Neither does this: -
From item in table Select field.Distinct
Note that all 3 of these appear fine with Intellisense