Execute a set of files containing code from Query Analyzer
-
I have written a couple of stored procedures,each in a file and placed them in a directory. How to execute all of them in a batch from Query Analyzer?
Thanks in advance.
-
Have you created the stored procedures in SQL Server yet or are they only in the file?
Assuming they're already in SQL you can simply do the following
exec <1st Stored Procedure name>
exec <2nd Stored Procedure name>
exec <n. Stored Procedure name>
Or you could write a Stored procedure that fires each of them in turn and execute that instead.
Hope this is enough information for you, if not then feel free to post again
. -
...or do you mean you want to execute them in a DOS batch run (ie. filename.BAT)? If you want to execute the files from there, then you would use OSQL (don't use ISQL as it is deprecated compared to OSQL). You can find more about OSQL in "Books OnLine". The (-i) parameter is part of what you are looking for...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks a lot.
I have all the SPs in files and not in SQL Server.
I want to fetch code from each file (in Query Analyzer) and then do as you said i.e. execute the code.
But how do i loop through a dos directory in Query Analyzer?
Scenerio:
In dos directory c:\myApps\
the following files are located
sp1.txt (containing code to create a store procedure with name SP1)
sp2.txt (containing code to create a store procedure with name SP2)
sp3.txt (containing code to create a store procedure with name SP3)
.
.
.
sp85.txt (containing code to create a store procedure with name SP85)
I want to execute all of these files from query analyzer rather than dos BAT file. Is there anyway?
Thanks in advance.
-
Hi,
I think the easiest way is to open Query Analyser and load each of the files in turn, then execute them against the correct database to create the Stored procedures.
I know it's a boring way to spend an afternoon but at least this way you find out immediately if you have any problems in the scripts.
-
"I want to fetch code from each file (in Query Analyzer) and then do as you said i.e. execute the code.
But how do i loop through a dos directory in Query Analyzer?"
You are much better off to do like the original suggestion... turn your text files into stored procedures and then create another stored procedure the "EXEC"s each of them. I guess I don't understand why you want to execute them as text files...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hope this SCRIPT File and steps mentioned below will help in getting whatyou are trying for:
1. Create Folder say "C:\DB\CMD"
Save the below SCRIPT code as a DB.CMD file in this folder
2. Create Folder say C:\DB\SP
Copy all your SQL files in this folder
File extension for all your Stored procedure files should be ".SQL"
For example SP1.SQL, SP2.SQL...( else my code won't work)
3. Ensure OSQL.EXE or ISQL.EXE is in your DOS Path
4. In Command Prompt go to "C:\DB\CMD" folder, then type command listed below
c:\DB\CMD>db.cmd <SQL Server Name> <SQL DB name> <Login ID> <Password>
Change the values appropriately per your environment
If you don't pass the Login and password it will use NT authentication
Please ensure you have ADMIN rights on the DB
Script Starts
@ECHO OFF
setlocal
set log=Result.txt
set applname=ChangeDatabaseCodebase
set isql_osql=OSQL
set ErrMsg=
set CallError=0
set cmd=
set callerIsProgram=0
set svr=%1
set db=%2
set USR=%3
set pwd=%4
REM ***************************************
REM ** Create Server Connection String **
REM ***************************************
IF "%3" == "" set SrvrConn= /S%svr% /d%db% /E
IF "%3" == "sa" set SrvrConn= /S%svr% /d%db% /U%USR% /P%pwd%
ECHO ***************************************
ECHO ** Connection string used **
ECHO ***************************************
ECHO %SrvrConn%
ECHO *************************************** >>%log%
ECHO ** Connection string used ** >>%log%
ECHO *************************************** >>%log%
ECHO %SrvrConn% >>%log%
echo. >>%log%
echo.
echo ======================================================================================>>%log%
echo ======================================================================================
echo Running script: [%0] Time: [%Date% %Time%] >>%log%
echo Running script: [%0] Time: [%Date% %Time%]
echo ======================================================================================>>%log%
echo ======================================================================================
ECHO -------------------------------------------------------------------------------------->>%log%
ECHO -- Start for Server: %svr%, Database: %db% >>%log%
ECHO -------------------------------------------------------------------------------------->>%log%
ECHO ---------------------------------------------------------------------------------
ECHO -- Start for Server: %svr%, Database: %db%
ECHO ---------------------------------------------------------------------------------
ECHO.
set cmd="set nocount on select ' Server : Start Time'=getdate()"
%isql_osql% %SrvrConn% /Q%cmd% /n /b >>%log%
IF ErrorLevel 1 set ErrMsg=Cannot connect to %svr% , Database %db%
ECHO ------------------------------------------------------------------------------->>%log%
ECHO -- CREATE STORED PROCEDURES >>%log%
ECHO ------------------------------------------------------------------------------->>%log%
ECHO -----------------------------------------------------------------------------
ECHO -- CREATE STORED PROCEDURES
ECHO -----------------------------------------------------------------------------
FOR %%F in (..\Sp\*.sql) DO %isql_osql% %SrvrConn% /HDatabaseChange /i %%F /n /b >>%log% || (set errmsg=%isql_osql% Create SP script error on %%F )
ECHO ------------------------------------------------------------------------- >>%log%
ECHO -- Setup successfully completed Database Server: %svr%, Database: %db% >>%log%
ECHO ------------------------------------------------------------------------- >>%log%
ECHO -------------------------------------------------------------------------
ECHO -- Setup successfully completed Database Server: %svr%, Database: %db%
ECHO -------------------------------------------------------------------------
ECHO.
set cmd="set nocount on select ' Server : End Time'=getdate()"
%isql_osql% %SrvrConn% /Q%cmd% /n /b >>%log%
IF ErrorLevel 1 set ErrMsg=Could not record complete time on Database server/db
Script Ends
paul
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply