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 |
|
BartMan
Starting Member
22 Posts |
 Posted - 2003-09-03 : 12:13:24
|
| Greetings, I have an application in which I create all my tables, triggers, and precedures by calling hard coded functions in my code. It works find, but I don't like the format of using hard coded functions to generate databases, tables, ect. Tables can change, or new triggers are added, and the code must be changed. This makes maintaining code harder to do.The problem is that I have create all my database items progmatically due to design specs, so I am not allowed to use the adminstrator program found with sql server 2000. But I noticed that you can generate *.sql script files to generate tables, procedures, ect. Would this be a better way to do this? For example I have a script file called "CreateTableA.sql", is there a way to execute this using TSQL like "EXEC CreateTableA.sql"?Are any other ways that anyone would suggest to create a database with it tables, ect. I am not a big hard coding fan, and I would like to move away from that method. :)Environment used:----------------SQL server 2000Windows XPVisual C .Net and ADOThanks in advance for any suggestions! :) |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-09-03 : 12:28:07
|
| So your database schema is being created at run-time? I can't imagine a situation where that would be the best design choice ....Jay White{0} |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-09-03 : 13:13:49
|
| Not sure exactly what's "hard coded" about opening query analyzer and typing "CREATE TABLE..." Plenty of opportunities to change things there.Equally not sure about the "application" you're using to create these things, or how it actually goes about creating them, but query analyzer has got to be easier. |
|
|
|
BartMan
Starting Member
22 Posts |
Posted - 2003-09-03 : 14:04:41
|
quote:
Originally posted by robvolk
Not sure exactly what's "hard coded" about opening query analyzer and typing "CREATE TABLE..." Plenty of opportunities to change things there.Equally not sure about the "application" you're using to create these things, or how it actually goes about creating them, but query analyzer has got to be easier.
Basically I was told I have to have the application create the database and all the tables, triggers, ect, one time when the application is started up. Ahh the joys of implementing someone else's design spec. :)The application version of the software uses the msde (desktop version of sql server). The software is more of a demo version to demonstrate the database use, and since msde doesn't have query analyzer (and licensing doesn't allow that to be distributed). That is why the tables, and other items were created once at run time when the application started up.Sorry about that, I guess I didn't explain what I was trying to do in a clearer way. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-09-03 : 14:28:36
|
| just save your scripts as .sql files.then, in your app, when the DB needs to be created, you can:1. create a connection to your database2. open the .sql file3. load it into a string variable4. then, in your code, just call the execute method of the connection object, and pass in the string variable as the commandtext.5. process the next .sql file and go back to step 3 until done- Jeff |
|
|
|
BartMan
Starting Member
22 Posts |
Posted - 2003-09-03 : 15:09:00
|
Thanks for the reply Jeff,I think that is much nicer, and cleaner way to do things.Great idea!  quote:
Originally posted by jsmith8858
just save your scripts as .sql files.then, in your app, when the DB needs to be created, you can:1. create a connection to your database2. open the .sql file3. load it into a string variable4. then, in your code, just call the execute method of the connection object, and pass in the string variable as the commandtext.5. process the next .sql file and go back to step 3 until done- Jeff
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-09-03 : 17:17:46
|
| You could also run your sql files using osql.exe. Your application would call osql and pass it the appropriate parameters. I use osql to install the database a lot, but I have never had to do it within an application.Tara |
|
|
|
BartMan
Starting Member
22 Posts |
Posted - 2003-09-04 : 15:54:19
|
quote:
Originally posted by tduggan
You could also run your sql files using osql.exe. Your application would call osql and pass it the appropriate parameters. I use osql to install the database a lot, but I have never had to do it within an application.Tara
Thanks for the information Tara, I didn't know about the osql.exe.It would be pretty easy to call that from my application. |
|
|
|
|
|
|
|
|
Better way to create tables, triggers,precedures?