Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nmg196
Yak Posting Veteran
70 Posts |
 Posted - 2003-07-07 : 13:01:34
|
| Hi,I have a website that's run from an SQL 2000 database with about 40 tables. My client wishes to update it by FTPing an Access database to the webserver.What is the best way to create the DTS package which will import the data into the SQL Server database? I tried using the wizard, but it seems to delete the data in the wrong order so you get errors like:"DELETE statement conflicted with COLUMN REFERENCE constraint <blah>". I presume I would also get similar errors if it inserts in the wrong order.Do I write make the whole thing by hand by adding tasks in the designer (and dependancies so they execute in the right order)? Or is there a better way to do it? I don't have very much time to get this working, yet I'd like to do it a sensible way if possible.Also, what if my client uploads an invalid database? Can I make it roll back or restore a backup? Currently I'm only importing into a database which doesn't have any relations defined, so it's working, but I'd rather leave the relations in on the bigger database I'm going to use. I then use an external program which I wrote which extracts the database from a ZIP, backs up the existing one, and runs the DTS package. If there's an error, it restores the backup it just created (which is hard because you have to chuck off all the users). I'm not sure whether this approach is a good idea or whether I should try and do more of this work from within the DTS package.Any advice on how to go about this would be very appreciated.Nick... |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-07-07 : 13:50:14
|
| A quick way to get this working is to drop all of the constraints prior to the import of the Access database, then readd the constraints after the Access database's data has been imported. Also, how will you know programatically if your client has uploaded an invalid database? If you are able to check this programatically, then you could just restore the backup or your could change the DTS package so that it exports the data to text files prior to the import (and prior to the dropping of the constraints if you go with my solution), then if the data is bad, just import the data from the text files (would be a good idea to do this with the constraints dropped here as well). If you want to go the restore method though, you would need to have a script that KILLs each connection that is connected to this particular database. I had posted a script that does this last week on SQLTeam.com. Just search the forums with my userid and KILL as the keyword. It'll probably return a few results, but one of them has the script (please notice the comments made by Nigel though).Tara |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-07-07 : 13:53:02
|
| Tara wouldn't you just use:ALTER DATABASE TaxReconDB_Prod SET SINGLE_USER WITH ROLLBACK IMMEDIATEThen set it back?Brett8-) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-07-07 : 13:55:36
|
| Sure you could do that, but why bother changing the state of the database when you don't need to?Tara |
|
|
|
|
|
|
|
|
Access > SQL using DTS - best practices?