| Author |
Topic |
|
joldham
Wiseass Yak Posting Master
300 Posts |
 Posted - 2002-05-14 : 12:15:54
|
| I have a script file named States.sql. When I am creating new databases and tables in Query Analyzer, I want to be able to run this script in code. Create Database testGoCreate table ContactsGoRun script c:\my scripts\States.sqlGoDoes anyone know the syntax or a SP where I can provide a path to run the SQL script?Jeremy |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-14 : 12:22:24
|
| EXEC xp_cmdshell 'osql -i "c:\my scripts\States.sql" -Uusername -Ppassword -Sserver'Take a look at osql in Books Online, I'm not totally familiar with the settings, you might need to tweak it a little so that it exits properly. |
 |
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-05-14 : 12:23:37
|
| XP_cmdshell is what you want to use. Not sure of syntax look it up in bolhmm.. edit note.. looks like rob beat me to it. Do what he says ;)Edited by - M.E. on 05/14/2002 12:24:14 |
|
|
|
joldham
Wiseass Yak Posting Master
300 Posts |
Posted - 2002-05-14 : 12:25:14
|
| Thanks guys. I will look this up in BOL. I tried searching for running a script and couldn't find anything in BOL and I knew you all could steer me in the right direction.Jeremy |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-05-14 : 12:33:24
|
| can you put the code from states.sql into usp_statescode? if so, you will probably see some performance/mainentance/reusablity/maketh-sense benefits of EXECing a proc over the xp_cmdshell dance . . .I only say this cause I have had to work with systems riddled with xp_cmdshell -> somecrap.bat -> osql somescript.sql . . . when you are trying to reverse engineer, its quite a pain. Then when the system moves, you can't just backup and restore you have to go out and find all these little bits-and-pieces and make sure file perms are set up. . . centralize your code in the database(if possible) . . .<O> |
|
|
|
joldham
Wiseass Yak Posting Master
300 Posts |
Posted - 2002-05-14 : 12:58:48
|
| Page47,Thought about that as well. Which db would be a good place to place this? Master? I also have a script for coutries as well. I am trying to work through the Constraints I should have on these tables. I guess I will search the Internet to see if someone has already developed the constraints that should be used between the States and Countries tables, as well as constraints on the tables that use these two tables.Jeremy |
|
|
|
royv
Constraint Violating Yak Guru
455 Posts |
Posted - 2002-05-14 : 13:06:18
|
quote:
can you put the code from states.sql into usp_statescode?
What is usp_statescode???*************************Just trying to get things done |
|
|
|
joldham
Wiseass Yak Posting Master
300 Posts |
Posted - 2002-05-14 : 13:27:41
|
| usp stands for user stored procedure. Just now noticed that he used usp before the procedure name. Believe it or not, I use the exact same syntax. Is it just coincidence?Jeremy |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-05-14 : 14:16:50
|
quote:
..Which db would be a good place to place this? Master?..
anywhere but the system tables (master, model, msdb, tempdb) . . . I usually put global tools/junk in a database called dba . . .<O> |
|
|
|
|
Running a script file from T-SQL