PDA

View Full Version : Database Import & Rollback issues



johnludlow
05-04-2004, 09:51 AM
Ok, two things I thought were working don't seem to be (unless I'm missing something).

Firstly, I tried importing a database into an SQL script. It failed during execution of the script because a function was called before it was declared. There was a similar issue on the beta - the system was trying to enter data in the wrong order so constraints failed. This time, the constraints don't seem to have failed (Though the constraints were scripted before the data insert, and I thought the fix was to script them afterwards), but the function did.

Secondly, when the database failed, it didn't seem to roll back (i.e the database had been attached, so a rollback would have removed it). I noticed a custom action called ISSQLServerRollback. I'm assuming that this handles rolling back of database changes. Or doesn't this deal with deleting databases that have failed to attach?

Is there anything special I need to do?

Thanks

David Thornley
05-05-2004, 08:54 AM
John,

Would it be possible for you to e-mail me the script file that was generated by the DB Import wizard. I will have a developer take a look at this. davidt@installshield.com

Thanks
David

johnludlow
05-05-2004, 09:20 AM
Ok, done

hidenori
05-06-2004, 03:49 PM
John,

Thank you for providing your SQL script. The problem is that a user defined function named 'GetEpoch' is used to set the default value of the aLastAction column of the the Wtk_GlobalAuth table, but the function does not exist when the table is created. To fix this error, please change the script manually so that the GetEpoch function is created before the Wtk_GlobalAuth table is created.

We can change the DB importer so the user defined functions creation comes first. However, Microsoft SQL Server Enterprise Manager creates scripts in the same manner as we do now. I assume it is more common that user defined functions reference to tables rather than vice versa.

If you have any suggestions on this, that would be appreciative.

Regards,
Hidenori Yamanishi
InstallShield Software Corporation

johnludlow
05-06-2004, 05:27 PM
Ok...

So if I scripted this database with Enterprise manager, it would also generate this error? I haven't tried that myself.

BTW, I did try shifting the GetEpoch function to just before that table with the default you mentioned, and it complained about a syntax error near "END". I think maybe I mucked it up by copying the section into the wrong place. Heh, I'm no SQL expert.

What we do currently is copy the .mdf and .ldf files into a particular place and call sp_attach_db to attach the databases. I'm thinking this would be better than trying to script the database, as it gets rid of this kind of issue. It's also faster, I think. (My whole install in DevStudio at the moment takes 10 minutes calling that stored procedure from InstallScript - it took longer than that just to create one database by SQL script).

BTW, supposing I run my install, attach one database using an SQL script component, then want to attach another database of the same type onto the same machine. I haven't tried it yet, but I think maybe if I set that component to reinstall, and manipulate the connection properties, that would work?

I hope I've explained that right - I'm tired and I don't write well when I'm tired...

Thanks

johnludlow
03-14-2005, 11:59 AM
Hey, I'm, back!!!

Ok, just resurrecting the rollback issue thing since I've been rewriting our installation to use the InstallShield method of updating databases. However, I discovered (quite by accident) that the rollback wasn't working.

Essentially, what happened was that the script was set to abort the install in case of an error. An error in the script caused the install to fail and roll back the install, but when I checked the databases (just out of curiosity), they still showed some scripts having run. This means that the database is in an indeterminate state, meaning that they either have to find a way to fix the error or reinstall the old product from scratch, losing all their data.

I should probably mention that the first thing the install does is rename all the databases so they fit the naming convention (in the old version, users could name databases whatever they liked, but this wouldn't work with the IS functionality without making the install a lot more complicated, so we have them fit a naming convention). Now, I do this during a custom action that runs from a button on a custom dialog right after InstallWelcome. As far as I can see, only ISSQLServerFilteredList runs before this (out of the IS SQL actions). Could this be making a list of current databases for the backup/rollback actions, which is being screwed up by my renaming action? If so, what can I do about it? Can I call it again after my action has run?

Incidentally, what action actually backs up the database? I can see an SQL rollback action, but no backup action.

I suppose I could implement my own functionality through a backup/restore script. But I think if I can get the IS functionality to work, that would be better. I'm using IS X, BTW.

Thanks v much in advance, and apologies if this has been answered already.

John

P.S: This is an upgrade from an InstallScript MSI project to InstallScript. Rollback doesn't seem to reinstall the old product, though I'm not worried too much about that. Just thought you should know ;)

johnludlow
03-16-2005, 06:24 PM
No ideas?

On hindsight I should probably have posted a new topic

hidenori
03-16-2005, 06:52 PM
At this time, you need to schedule to run a SQL script during Rollback if you want to roll back the changes on the target database made by your installation because InstallShield does not have the automatic rollback support.

We were trying to have the ability using the backup/restore technique. However, we had feedback from a customer says that it is not an appropriate solution for large scale databases. See http://community.installshield.com/showthread.php?t=136033 for more information.

Also, we were trying to use the SQL transactions. However, this technique would cause the script error handling functionality not working properly. See http://community.installshield.com/showthread.php?t=144609 for more information.

If you have better ideas for InstallShield to have this support, that would be greatly appreciated.

Thank you.
Hidenori Yamanishi
Software Developer
Macrovision Corporation

johnludlow
03-17-2005, 06:38 AM
Ah, thanks hidenori. That explains it! ;)

I'm going to implement a backup/restore pair of vbscripts as an interim measure. I can't think of a better way of doing this that doesn't involve SQL transactions. Perhaps the best way would be to implement both options into IS, and if transactions are selected, disable the error handling feature. But that seems like quite a bit of extra work for IS, so maybe not.

I'll let you know what happens.

Thanks again

John

PilotBob
03-17-2005, 09:29 AM
While IS has the ability to specify scripts to run during rollback, I don't see how the script would know what to do.

For example, if you have a script that alters a table to add a field and it fails, then the rollback script to remove that field will also fail.

I found the best situation is to put transaction handling into your scripts. If your script fails it should roll back. Also, you can set IS to roll back the install on a script failure.

As far as scripts not working with UDF's in the db, we have seen the same problem. Also, scripting with Ent Manager also doesn't create a perfect script. We use our change managment software (DB Ghost) to create the script. It will also script data which Ent Manager won't do, although IS trys to.

The script from DB Ghost is perfect every time, because it actually creates the script as it builds the DB from source scripts. We also use DB Ghost to create our upgrade scripts when we move from one version to another. Good sofware and well worth the small price. No, I don't get a commision for this! :)

BOb

johnludlow
03-17-2005, 11:39 AM
Thanks for the advice PilotBob. However, I'm not sure transactions in the scripts would work very well. We have multiple databases, each potentially recieving multiple updates per installation. In addition, the database names are not rigidly fixed (there is some leeway allowed in the naming, mainly because for some databases, our users may want more than one, which our system is designed to support). This all means that some databases could still be up to a certain version, while others are a different version.

I've decided that I'll back up all databases automatically, so at least they have the backup. I'll see what can be done about the restore/rollback later.

Thanks again

PilotBob
03-17-2005, 11:56 AM
For our new version, we actually wrote an EXE that handles it all. It also has a UI that allows the user to create new Db's or create Sample data.

But, no matter how good we think our installs are, we allways tell the customer they should have backups of their db's before running the update.

Have fun.

BOb

johnludlow
03-17-2005, 12:02 PM
Yeah, my current version shows a dialog with a warning icon and some text explaining that. I'm thinking about forcing the issue though by doing it automaticaly, because you just know that as soon as someone says "don't need to backup my databases, it'll be fine", it'll all die horribly.