 |
BorlandTalk.com
Borland discussion newsgroups
|
| View previous topic :: View next topic |
| Author |
Message |
Bob Dalton
Guest
|
 Posted: Mon Aug 25, 2003 3:35 pm Post subject: Table deployment strategy under MSSQL? |
 |
|
Being both new to MSSQL 2000 and ADO (but not to database programming in
general) I am looking at the best method of deploying tables under
MSSQL/ADO. For the last five years I have been building apps using the
FlashFiler client/server database (not using SQL at all in my programs).
This allowed me to prebuild empty tables and deploy them with the
installation package.
MSSQL seems to offer a different and perhaps more useful method to do this
but being new to this way of doing things I am not sure of the best way to
go. At present I am envisioning the following:
1. Connection is made.
2. Client app checks for presence of a particular table.
3. If found it uses it opens and uses it.
4. If not found it creates it using SQL, then opens and uses it.
Question 1: Is this the way to go or is there a better and generally
preferred method for deploying the tables other then what I suggest above?
Question 2: If a table is not found how do I use the ADO components to
create the table? It seems that running a script would be required but I am
not sure the ADO components can do that(?). Code snippets on how to check
for the presence of a table and if not found create it would be very useful
here if anyone has such code or examples.
BTW any source code information provided will be used to expand the example
program I released the other day (with full credit to those who provide info
and help of course) to make it more useful to others so those new to
ADO/MSSQL, such as myself, dont; have to keep asking these questions over
and over.....
Regards;
Bob Dalton
|
|
| Back to top |
|
 |
Ignacio Vazquez
Guest
|
| Posted: Mon Aug 25, 2003 3:51 pm Post subject: Re: Table deployment strategy under MSSQL? |
|
|
"Bob Dalton" <bob.dalton (AT) digitallogistics (DOT) com> wrote in message
3f4a2cd6$1 (AT) newsgroups (DOT) borland.com...
| Quote: | Question 1: Is this the way to go or is there a better and generally
preferred method for deploying the tables other then what I suggest above?
|
This might be just me, but you're probably better off preinstalling the
database as opposed to making it on the fly. What happens if your creation
routines fail for some reason or another? How do you diagnose and/or recover
from this?
Cheers,
Ignacio
--
No, don't send me e-mail directly. No, just don't.
|
|
| Back to top |
|
|
Jeremy Collins
Guest
|
| Posted: Thu Aug 28, 2003 8:03 am Post subject: Re: Table deployment strategy under MSSQL? |
|
|
Marc Scheuner wrote:
| Quote: | 1) Create a SQL script to create (or later on - update) your database,
and have it run at install / update time OUTSIDE your app. We created
a separate DB Installer utility which could execute this (and handle
errors, etc.). Using SQL, you can do anything, including adding lookup
values to your tables et al. Works, but takes some time.
2) Create an empty database which has your tables and all, lookup
values etc., and then do a database backup (using the Enterprise
Manager tools, or the SQL-DMO Backup object). At the client's site,
install the database using SQL-DMO, again, preferably at install /
update time, outside your app. The bigger problem in this case is
preserving existing user data when updating - you'd probably have to
rename the existing database, install your new database using SQL-DMO,
and then transfer data.
Either way, you'll probably be using some SQL script and some other
techniques, and I'd STRONGLY recommend doing that outside your main
app - use a separate installer / updater for that.
|
Good suggestions. For simple databases, you can supply an identical
Accees DB, and use a DTS package to "install" the database to SQL
server. If you're worried about data type conversions you can also
supply the VB code for the package.
--
jc
Remove the -not from email
|
|
| Back to top |
|
|
Jari Kettunen
Guest
|
| Posted: Sun Aug 31, 2003 5:18 pm Post subject: Re: Table deployment strategy under MSSQL? |
|
|
Easiest way is distibute your own DB-files and by following code you can add
it to sql-server. It really don't matter if your distibute both mdf and log
files mdf is enough. Connect to master.
....
cExecuteSQL: TADOCommand;
bAttach:TButton;
....
procedure TfADminDB.bAttachClick(Sender: TObject);
begin
if eLogFile.Text<>'' then
cExecuteSQL.CommandText:=
'EXEC sp_attach_db @dbname = N''Mydatabase'', '+
'@filename1 = N''Mydatabase.mdf'','+
'@filename2 = N''Mydatabase.log'''
else
cExecuteSQL.CommandText:=
'EXEC sp_attach_single_file_db @dbname = N''Mydatabase'', '+
'@physname = N''Mydatabase.mdf'';
try
cExecuteSQL.Execute;
except
on E: Exception do ShowMessage(E.Message);
end;
end;
I you need checkup if DB-exist it can be found with following SQL command:
'Select DBID,Filename from sysdatabases where name=''MyDatabase"
with DBID you get log-file location:
'Select Filename from sysaltfiles where dbID='+dbid+' and fileID=2';
When you mess inside your own db it is easiest done with SQL and it is only
way if you distribute MSDE there is no Enterprise Manager.
First you should read 'SQL for Dummies'. most of things is possible done
with SQL.
"Bob Dalton" <bob.dalton (AT) digitallogistics (DOT) com> kirjoitti viestissä
news:3f4a2cd6$1 (AT) newsgroups (DOT) borland.com...
| Quote: | Being both new to MSSQL 2000 and ADO (but not to database programming in
general) I am looking at the best method of deploying tables under
MSSQL/ADO. For the last five years I have been building apps using the
FlashFiler client/server database (not using SQL at all in my programs).
This allowed me to prebuild empty tables and deploy them with the
installation package.
MSSQL seems to offer a different and perhaps more useful method to do this
but being new to this way of doing things I am not sure of the best way to
go. At present I am envisioning the following:
1. Connection is made.
2. Client app checks for presence of a particular table.
3. If found it uses it opens and uses it.
4. If not found it creates it using SQL, then opens and uses it.
Question 1: Is this the way to go or is there a better and generally
preferred method for deploying the tables other then what I suggest above?
Question 2: If a table is not found how do I use the ADO components to
create the table? It seems that running a script would be required but I
am
not sure the ADO components can do that(?). Code snippets on how to check
for the presence of a table and if not found create it would be very
useful
here if anyone has such code or examples.
BTW any source code information provided will be used to expand the
example
program I released the other day (with full credit to those who provide
info
and help of course) to make it more useful to others so those new to
ADO/MSSQL, such as myself, dont; have to keep asking these questions over
and over.....
Regards;
Bob Dalton
|
|
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
