How to use MSDE(SQL Server) as GBPVR Database

[KingArgyle]

GBPVR 0.23.11 update:

MSDE seems to work perfectly with the latest version that Sub has created. There is no longer an LDB file in place, and Scheduling Recordings now works. Before it would give an InterOp.Services.ComException when tryign to schedule a recording.

For those that are having difficulty, getting GBPVR to login in, make sure you are using SQL Server authentication, and that you have granted the GBPVR user id, rights to the GBPVR database. Otherwise, you'll get Authentication errors.

[Guest]

Gosh, this seems like something that would be real nice for sub to have integreated into the base package... Is this in the works???

[KingArgyle]

I doubt that MSDE will be distributed with GBPVR as a default. This mainly has to do with the size increase of adding the MSDE package to the distribution. MSDE itself is about a 25 meg download, where including an Access database is very trival.

Now with that being said, I would like to eventually see an option during configuration to allow for using MSDE as the database, and just like having to have some other pre-req sdk's installed, MSDE or Sql Server would need to be installed prior to this option being available.

[ollis]

Sounds good.
The guidance is very detailed.

But - which is if "Sub" the data structure changes?

[KingArgyle]

If Sub changes the data structure, and sticks to ANSI DDL statements, then when upgrading, your installation over the old, it SHOULD just take the new DDL statements and update the necessary tables in the MSDE database.

The other option is to always drop the tables in the MSDE database and then Upsize the Access database to the MSDE configuration.

Again, as long as ANSI DDL statements are used, there shouldn't be a problem.

[ollis]

@KingArgyle:
Thanks for your quick response.

Quote[/b] ]The other option is to always drop the tables in the MSDE database and then Upsize the Access database to the MSDE configuration.

That was also my thought. But in this case my data are lost.

[sub]

I'm not sure Access supports ANSI DDL statements. If you have a look at the upgrade.sql file, you'll see the SQL statements I've executed over the last couple of releases. The column types I've used are Access data types.

[KingArgyle]

Worst case scenario would be that the development community would just have to migrate the Access DDL statements, or new SQL Server/MSDE data types are created that match up name wise to the corresponding Access names. The underlining mappings would still be SQL Server/MSDE related, but the names would match the Access datatype names.

Again, this is something I see the development community maintaining. An article describing how to do this can be found here:

http://www.databasejournal.com/features/...pr/2212141

[Huw]

biggest problem you will have is with datetime fields, all other fields are easy to migrate between databases

If you need any help, just shout, I am one of the co-authors of Snitz which has a multidb backend, so am used to making stuff work in access/MS SQL/MySQL

[cginzel]

This seems like an awesome option and I'd like to try it our myself. I downloaded the MSDE and installed it using the SAPWD parameter, but I wasn't able to log in with the MSDE Manager. It kept comming up with an ActiveX error...

What are the setup parameters you used to install MSE or did you use the setup.ini file? If you used the file can you share you settings (minus password of course)?

Also, would it be possible to export the DDL for the tables and indexes because some people like me don't have a copy of Access to perform the upsize operation.

There must be some inital data that needs to be loaded as well... maybe setting up MSDE will not be an option if the user doesn't have Access...

Or is it possible to copy an initial MSDE database file from machine to machine?

Anyway, I'm looking forward to futher developments in this area...

Thanks,
-Charles