dhamijap
asked on
Executing the contants of SQL script file at Query Analyzer
How to execute the script file without copy and past in Query Analyzer in MSSQL 2000?
These are the contents of my file called appdb.sql
Create databse AppDb
GO
USE AppDb
GO
Create table customer ...
GO
Crate table store ...
GO
Insert into customer values(...)
Go
--------------------------
Now i want to execute this file by using some command similar with excuting stored procedures
??? appdb.sql
These are the contents of my file called appdb.sql
Create databse AppDb
GO
USE AppDb
GO
Create table customer ...
GO
Crate table store ...
GO
Insert into customer values(...)
Go
--------------------------
Now i want to execute this file by using some command similar with excuting stored procedures
??? appdb.sql
Brendt Hess
Look up osql in Books On Line. This should be the utility you are looking for.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This seems like it will work. I should have written what my goal is. Any way I would like to write it now.
I want to write all create table, insert data SQL statements in each individual file. For example: customer.sql, order.sql, equipment.sql. After this I want to write another file like dbmake.sql. the contents of dbmake.sql:
??? customer.sql
GO
??? order.sql
GO
??? equipment.sql
GO
Then make another file, which will call all of the above files to create my full database. I want to do this because I am making my database by changing the DDL very often. Which means that I have to insert data every time I create a table again. When I was in college I did this with oracle or SQL anywhere. Because my project required to keep adding new DDL, I executed a file with just something in front of it and clicked on run. I hope MSSQL has something like this! If you know any other way that this can be done. I want to keep it organized so that I can just print all those files and call them as documentation.
I want to write all create table, insert data SQL statements in each individual file. For example: customer.sql, order.sql, equipment.sql. After this I want to write another file like dbmake.sql. the contents of dbmake.sql:
??? customer.sql
GO
??? order.sql
GO
??? equipment.sql
GO
Then make another file, which will call all of the above files to create my full database. I want to do this because I am making my database by changing the DDL very often. Which means that I have to insert data every time I create a table again. When I was in college I did this with oracle or SQL anywhere. Because my project required to keep adding new DDL, I executed a file with just something in front of it and clicked on run. I hope MSSQL has something like this! If you know any other way that this can be done. I want to keep it organized so that I can just print all those files and call them as documentation.
Hmmm... assuming that the location of the script files and the server name / password can change, I would have your master process (whatever it will be) execute your DBMake.SQL script, then execute the stored procedure that your DBMake.SQL will create.
Your DBMake file would look like:
USE MASTER
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'usp_temp_Update_DB' AND type = 'P')
DROP PROCEDURE usp_temp_Update_DB
GO
Use MASTER
CREATE PROCEDURE usp_temp_Update_DB
@Servername Varchar(40),
@LoginName varchar(40),
@Passwd varchar(40),
@ScriptPath varchar(128)
As
EXEC master..xp_cmdshell 'osql -S ' + @ServerName + ' -U ' + @LoginName + ' -P ' + @Passwd + ' -i ' + @ScriptPath + 'customer.sql'
EXEC master..xp_cmdshell 'osql -S ' + @ServerName + ' -U ' + @LoginName + ' -P ' + @Passwd + ' -i ' + @ScriptPath + 'order.sql'
EXEC master..xp_cmdshell 'osql -S ' + @ServerName + ' -U ' + @LoginName + ' -P ' + @Passwd + ' -i ' + @ScriptPath + 'equipment.sql'
---------
Then, your master process will execute the SP, something like this:
EXEC master..xp_cmdshell 'osql -S myserver -U ItIsMe -P MyPWD -i C:\Updates\Temp\DBMake.sql
EXEC master..usp_temp_Update_DB
DROP PROCEDURE master..usp_temp_Update_DB
This allows you to be more flexible in configuring the update - as long as your master process has the ability to get the information (server/user/pwd/path) and pass it to a stored proc.
Your DBMake file would look like:
USE MASTER
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'usp_temp_Update_DB' AND type = 'P')
DROP PROCEDURE usp_temp_Update_DB
GO
Use MASTER
CREATE PROCEDURE usp_temp_Update_DB
@Servername Varchar(40),
@LoginName varchar(40),
@Passwd varchar(40),
@ScriptPath varchar(128)
As
EXEC master..xp_cmdshell 'osql -S ' + @ServerName + ' -U ' + @LoginName + ' -P ' + @Passwd + ' -i ' + @ScriptPath + 'customer.sql'
EXEC master..xp_cmdshell 'osql -S ' + @ServerName + ' -U ' + @LoginName + ' -P ' + @Passwd + ' -i ' + @ScriptPath + 'order.sql'
EXEC master..xp_cmdshell 'osql -S ' + @ServerName + ' -U ' + @LoginName + ' -P ' + @Passwd + ' -i ' + @ScriptPath + 'equipment.sql'
---------
Then, your master process will execute the SP, something like this:
EXEC master..xp_cmdshell 'osql -S myserver -U ItIsMe -P MyPWD -i C:\Updates\Temp\DBMake.sql
EXEC master..usp_temp_Update_DB
DROP PROCEDURE master..usp_temp_Update_DB
This allows you to be more flexible in configuring the update - as long as your master process has the ability to get the information (server/user/pwd/path) and pass it to a stored proc.
ASKER
bhess1:
you are right. I was able to user chigrik's code to execute the file. I was looking for some command that assumes that you are creating tables and inserting data on the server you are on and it does not require me to give all that -S -U -P etc. what I did was that from chigrik's answer I make one file which creates all the tables and called individual table creating files. It worked. I will award points to chigrik but if there is way that I want it to be done I will raise points as well.
you are right. I was able to user chigrik's code to execute the file. I was looking for some command that assumes that you are creating tables and inserting data on the server you are on and it does not require me to give all that -S -U -P etc. what I did was that from chigrik's answer I make one file which creates all the tables and called individual table creating files. It worked. I will award points to chigrik but if there is way that I want it to be done I will raise points as well.
Yes, chigrik had the first example for the answer... all I did was make it prettier, really. He's earned the points.