moving a database from sql 2005 to sql2000.....

This I would have thought would have been a fairly easy task, as the DB in question used to be hosted on a SQL2000 server and was detached, then attached to the SQL2005 development server to be adjusted etc etc. I even left the compatibility set to 80.

The only structural changes were via the aspnet provider script + the odd new table/stored proc etc

However when I came to move the DB back to the live server all hell broke loose. Well I might be exaggarating a bit there, but it felt like it.

To cut a long story short I followed these steps to get it all to work;

1- Script Database as .... Create to ...
2- Run the installProviders.sql for aspnet
3- Task ... Generate scripts ... (then manually edit this to remove any of references already created by the installProviders.sql above, also had to manually remove a number of the unusual SQL2005 extra bits)
4- Add required sql users
5- I used Redgate SQL Compare (trial edition) to move the actual data across (the SQL 2005 export tool failed)
6- crossed fingers and it seems to have worked...

It seems annoying that even if you set the compat to SQL2000t hat its still won't work, but I know now. ha ha.

As soon as possible I think the live server will get an upgrade....

importing Excel to SQL2005

Such an annopyingly easy fix, just not too well documented.

I had 200 rows in an excel 2007 (this will work for most excel versions) worksheet I needed to insert into a sql table I was building.
You used to be able to use DTS in SQl 2000, but there doesn't seem to be any easy way to bring in this type of data.

First stop: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1381738&SiteID=1 with no luck, just odd SQL errors and I had no time to find the solution unitl...


This is where I found an answer that I could work with: http://www.mssqltips.com/tip.asp?tip=1430

Now this wont suit everybody as it wont work 100% of the time with tables that already have a Primary Key, I had to delete all other table columns except the title column I was after. Once the data is pasted in, I re-added the columns back in again.

I was lucky I had only just started using this table....its always annoying to have a linked tabel used in and around the database that you then have to go and wipe/re-fill with new rows.

cheers,
craig