How do you execute a multiple (.sql) files in Query Analyzer

Hi

Can anyone tell me how to run a group of SQL batch file (.sql) within a single (.sql) file. I have several (.sql) files I run periodically for maintenance I want to execute one (.sql) file in Query Analyzer that will execute all the peridically run maintenance (.sql) files. How do you run/call a (.sql) file in Query Analyzer ?

Thanks in advance

 

The best method is with DTS but we don't have access to that on the DASP servers so you'll need to create DTS tasks locally.


You can also cube your SQL.


Helps if you create temporary tables if you do that, just in case you need a roll-back or insurance.


DTS: http://www.sqldts.com/


Cubes: http://www.sql-server-performance.com/sm_create_local_cubes.asp

 

wisemx


Thanks for your response.


I will look into both of your suggests. The DTS is the most appealing since I have used it already I'll just have to figure out how to execute a (.sql) file in the DTS package.


Thanks again


albanello

 

Bruce


Thanks for your response


Your suggestion would work but that would make for one REALLY large .sql file and I would lose the individual object segregation. The DST solution suggested by "wisemx" would work BUT then I would have two places I would have to maintain the .sql as I understand the SQL task.


What I want to do is have file_0.sql, file_1.sql, file_2.sql.......file_n.sql and in Query Analyzer (or somehow) run file_all.sql.


file_all.sql would be:


GO


EXECUTE file_0.sql


GO


EXECUTE file_1.sql


GO


EXECUTE file_2.sql


GO


Hope there is a answer to this.


Thanks again


albanello

 

Bruce


Thanks for your response.


I thought of your suggestion but I don't think it will work, please tell me if I am wrong.


The ultimate goal is to clear out all Procedures Cached, to accomplish this it is my understanding that if I DROP PROC Proc_file_00 then CREATE PROC Proc_file_00 a new execution plan will be created the next time the SP is executed. This is what I am doing inside the file_00.sql.


I don't think I can create a SP inside another SP.


exampleexampleexample:


CREATE PROC Proc_00_ClearCache


DROP PROC Proc_file_00
GO


CREATE PROC Proc_file_00
GO


exampleexampleexample


I hope that is not to confusing!



I don't think I can do this. Correct?


albanello

 

As mentioned you can create a big fat SQL query to do everything.


The important thing will be to create routines that catch possible errors.


My favorite method is to use physical temporary tables to server as buckets.
These tables start out empty, get filled during the large query, then get dumped once your query has completed.


That's an important part as you will need to make certain checks, if you want to preserve "sound" data.

 

wisemx

I would like to be able to run these .sql files as needed one at a time also, if I put them all in one "big fat SQL query" that would mean I would have to maintain them in two places which is not a good design. Can you tell me if my previous post to Bruce is correct "I don't think I can create a SP inside another SP".

Thanks again

albanello

 

albanello


Not sure if you have already found a solution to this, but here is what I did. I opened a new query, and on the top menu list select Query, then enabled SQLCMD mode. Then in my query I but the following line for each of my .sql files that I wanted to run.


:r "C:\File Path\File.sql"
It worked for me, hope it works for you.
~Brandon

 

b8ball02

Thanks for your response.

I opened Query Analyzer and selected "Query" from the main menu I do not see a "enabled SQLCMD mode" item. I am working with MS SQL 2000. Is this feature available in MS SQL 2000.

Here is the result of trying to run the command you suggested: ( :r "C:\MyPath\MyFile.sql")

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ':'.

albanello

 

I believe SQLCMD(command-line query tool) is shipped only with SQL Server 2005.

Vikram

DiscountASP.NET
www.DiscountASP.NET