Okay SQL guys,
A short post about Attached and Detached databases. This topic is 'small' but not an easy one - I have been fighting it for years, it would seem.
Lets start this 'art form' topic (no rhyme nor reason!?) by looking at the Management Studio end of things.
If we have a SQL server showing in the 'Object Explorer' pane, then before we can access a specific MDF (data base) we have to have it showing in the folder called 'Databases'. The way MDFs get into and out of the 'Databases' folder is by Attaching and Detaching. Sounds easy but some times things seem to get 'locked up' (from time to time) and we have to resort to closing apps and restarting them - messy. I have learned to live with this - but the good news is that from X# code life is not as 'strange' ;-0)
Previously, we saw in an image how we Attach by right clicking the 'Databases' folder, but to 'Detach' we need to right click the required Database entry itself. See below :-
- SQL_attache_11.jpg (110.05 KiB) Viewed 548 times
If all goes well (to plan) then the entry will disappear from the Object Explorer list - but then you may also need to refresh this list, and it may behave slightly differently in your version of Management Studio or Visual Studio - I did say ... "an art form!".
So Tasks and Detach ... do the trick, hopefully, of removing the MDF from the list.
Now then, going further on this rather tricky topic, if we have an MDF attached in the Object Explorer pane in MS or VS we may get an issue in our X# coded application when we run it - but apparently only the first time we try.
Sometimes I need to Detach an MDF and this frees the .NET app to work okay, but more strangely the re-attached MDF seems then not to bother our code from running smoothly. As I said its a bit of an 'Art Form' and is one aspect I have struggled with since the start of my SQL experience many years ago. I have learned to live with it - BUT - I do need to tell, and warn, you of the possible problems. Just be aware of what I have reported and you will survive.
Sometimes I may even get an error message box which says I can't detach from the MS/VS shell, so then I need to close and reopen MS (and/or VS) and this seems to clear things so I can then do a clean 'Detach'.
From our .NET code we don't actually do Detach / Attach - we seem to just need to supply a connection string. Anything else may be hidden from us coders by the .NET ADO.NET system.
Finally, you could say why bother when we could do it more simply from .NET code. Well, the answer is that it is VERY useful to be able to use Management Studio to check-out our sample database. Look at the data, run some basic queries etc.. Here is a good and simple place to start :-
- SQL_attache_06.jpg (80.37 KiB) Viewed 548 times
We are asking to have returned to us as a data set, all the customers from the Customer table. This simple query is supplied for us by the context menu option - here are the results :-
- SQL_attache_07.jpg (80.26 KiB) Viewed 548 times
We will look at making some of our own queries in the next post - queries directly from the IDE in Management Studio (or VS).
See you in the next post,
Phil.
Wales, UK.