database script to call a create table script
-
I've got a script that creates database1 and then creates database2. In addition, I've got another script that builds the tables for database1. I need to keep the two script files separate. I'd like to call from the script that builds the databases and run the script that creates the tables. Basically - one script creates the tables and the other srcipt creates the tables. How do I reference the table script from the database script?
Thanks,
Wills
-
how are you calling hte scripts? with osql?
-
I'm using SQL Query Analyzer to run the create database script. After the the last Go command, prior to the next block of code to create database2; I want to read in or include the create table script.
-
Below is an example of my create database script.
-----------
USE master
GO
CREATE DATABASE database1
ON
( NAME = database1,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\database1.mdf',
SIZE = 4,
MAXSIZE = 10,
FILEGROWTH = 1 )
GO
***** read in the external create table script, then go to the next create database
CREATE DATABASE database2
ON
( NAME = database2,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\database2.mdf',
SIZE = 4,
MAXSIZE = 10,
FILEGROWTH = 1 )
GO
-
You can execute a SQL script stored locally as follows:
EXEC xp_cmdshell'isql.exe -S SERVER1 -U sa -P PASSWORD -id:\dba\dbadmin\sql\sp_who.sql -od:\dba\dbadmin\outfiles\sp_who.out'
Hope this answers your question.
.
-
mdamera,
In your block of code, where do I reference the name of my script? I understand the following of your code:
EXEC xp_cmdshell - executing xp_cmdshell
'isql.exe = ?
-S = nameofmyserver
-U = mydbouserid
-P = mydbopassword
-id:\dba\dbadmin\sql\sp_who.sql = ?
-od:\dba\dbadmin\outfiles\sp_who.out' = ?
You lost me at the ones with ?
-
in the earlier script, option "i" is the inputfile, option "o" is the output file. You needed to give your SQL file as input.
running ISQL.exe is one of the way you execute Query Analyser. Since this needs to be run from command prompt, we are running from xp_cmdshell.
Please let me know if you need clarifications.
.
-
Would the script look like this?
--
EXEC xp_cmdshell'isql.exe -S nameofmyserver -U mydbouserid -P mydbopassword -id:\scripts\createtablescript.sql -od:\scripts\sp_who.out'
--
Is id: residing at the c drive (c:\scripts\createtabescript.sql)?
If od: is the output, will it generate a file?
-
Would this be the final script?
USE master
GO
-- create a database
CREATE DATABASE database1
ON
( NAME = database1,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\database1.mdf',
SIZE = 4,
MAXSIZE = 10,
FILEGROWTH = 1 )
GO
--- read in the external create table script, then go to the next create database
EXEC xp_cmdshell'isql.exe -S nameofmyserver -U mydbouserid -P mydbopassword -id:\scripts\createtablescript.sql -od:\scripts\sp_who.out'
Go
-- create the 2nd database
CREATE DATABASE database2
ON
( NAME = database2,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\database2.mdf',
SIZE = 4,
MAXSIZE = 10,
FILEGROWTH = 1 )
--- read in the external create table script
EXEC xp_cmdshell'isql.exe -S nameofmyserver -U mydbouserid -P mydbopassword -id:\scripts\createtablescript.sql -od:\scripts\sp_who.out'
-
If your SQL file is on C Drive and the outputfile to be created on C drive, it should look like this:
EXEC xp_cmdshell'isql.exe -S nameofmyserver -U mydbouserid -P mydbopassword -ic:\scripts\createtablescript.sql -oc:\scripts\anyoutputfile.out'
Go
.
-
So the output file would be like:
oc:\scripts\results_createtablescript.txt.out'
-
yes.
.
-
I'd shake your hand for all of your help. However - Thank you very much for your assistance.
-
The output file can be anything you want - it doesn't have to end with ".out". You also don't even need to specify an output file. Without one, the output will appear in your results pane in QA. The output file option is just for when you want to capture the "output" or results of the query into a file for later review/analysis.
Enter isql -? at a command prompt for help on all of switches (-S, -U, -P, etc.). You can have a space between the switch and its value, or not. That's probably why you were confused about the output file switch. In other words,
-o c:\somefile.txt
is the same as
-oc:\somefile.txt
Also, if you have spaces in the folder or file names, you'll need to enclose it in double quotes, like this:
-o"c:\my documents\output file.txt"
Jay Madren
Jay Madren
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply