Reading a directory and submitting multiple TSQL scripts

I need to come up with a mechanism that will allow me to automatically submit
multiple TSQL scripts residing in directory structure. I tried to write a
command script but found out that osql.exe does not allow for variabalization
of most of its switches. Without being able to read variables into -d -q
switches of osql.exe I can't automate it. Can anyone help?
Oscar Elleseff
Oscar.Elleseff@.eclipsys.comNot sure I understand everything...
Are you using batch files?
Here is one I use that accepts variable values
If you need ehhence variable functionnality I suggest you take a look at
SQLCMD.exe that come for free with SQLEXPRESS and SQL Server 2005
It has the new switch -v for passing variables at the command line to
replace into script syntax within the script is $(VarName)
@.echo off
rem !!! Only compatible with SQL Server 2005 or SQLExpress !!!
rem !!! User running the install must have admin priviledge !!!
rem %1 is the SQLExpress server instance name
rem %2 is the database name
set i=%1
set d=%2
if "%1" == "" set i=%COMPUTERNAME%\SQLEXPRESS
if "%2" == "" set d=AVDBTOOLS
rem start logging
echo Starting installation ...
echo Starting installation ... > INSTALL.log
echo ***** >> INSTALL.log
echo TODO should be checking version to install on
echo TODO should be checking version to install on >> INSTALL.log
echo ***** >> INSTALL.log
echo checking if %i% SQL Server instance name exists
echo checking if %i% SQL Server instance name exists > INSTALL.log
osql -E -S%i% -dmaster -Q"set nocount on select srvname+' exists' from
master.dbo.sysservers where srvid = 0" -n -h-1 -w8000 -b >> INSTALL.log
IF ERRORLEVEL == 1 GOTO badcon
echo ***** >> INSTALL.log
echo checking if %d% database exists and if not will create it
echo checking if %d% database exists and if not will create it >> INSTALL.log
osql -E -S%i% -dmaster -Q"set nocount on IF NOT EXISTS(SELECT 1 FROM
master.dbo.sysdatabases WHERE name = '%d%') CREATE DATABASE [%d%]" -n -h-1
-w8000 -b >> INSTALL.log
IF ERRORLEVEL == 1 GOTO baddb
echo ***** >> INSTALL.log
echo Creating TEMPOBJECTS table
echo Creating TEMPOBJECTS table >> INSTALL.log
osql -E -S%i% -d%d% -i"TEMPOBJECTS.tab" -n -h-1 -w8000 -b >> INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Get home folder
echo Get home folder >> INSTALL.log
cd > HOME.bcp
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Load Home folder to TEMPOBJECTS
echo Load Home folder to TEMPOBJECTS >> INSTALL.log
bcp "%d%.dbo.TEMPOBJECTS" in "HOME.bcp" -S%i% -T -c >> INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Check if current installation script path matches given %i%\%d%
parameters
echo Check if current installation script path matches given %i%\%d%
parameters >> INSTALL.log
osql -E -S%i% -d%d% -Q"if (select Result from dbo.TEMPOBJECTS) not like
'%%%i%\%d%' raiserror('Script path does not match local installation.
%i%\%d%',16,1)" -n -h-1 -w8000 -b >> INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Creating dbtsp_CheckDirExist
echo Creating dbtsp_CheckDirExist >> INSTALL.log
osql -E -S%i% -d%d% -i"dbtsp_CheckDirExist.prc" -n -h-1 -w8000 -b >>
INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Creating dbtsp_validateConnection
echo Creating dbtsp_validateConnection >> INSTALL.log
osql -E -S%i% -d%d% -i"dbtsp_validateConnection.prc" -n -h-1 -w8000 -b >>
INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Creating dbtsp_InitServer
echo Creating dbtsp_InitServer >> INSTALL.log
osql -E -S%i% -d%d% -i"dbtsp_InitServer.prc" -n -h-1 -w8000 -b >> INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Running dbtsp_InitServer
echo Running dbtsp_InitServer >> INSTALL.log
osql -E -S%i% -d%d% -Q"exec dbtsp_InitServer" -n -h-1 -w8000 -b >> INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Creating dbtsp_InitConstant
echo Creating dbtsp_InitConstant >> INSTALL.log
osql -E -S%i% -d%d% -i"dbtsp_InitConstant.prc" -n -h-1 -w8000 -b >>
INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Running dbtsp_InitConstant
echo Running dbtsp_InitConstant >> INSTALL.log
osql -E -S%i% -d%d% -Q"set nocount on DECLARE @.Home nvarchar(256) select
@.home = Result from dbo.TEMPOBJECTS exec dbtsp_InitConstant @.Home = @.Home" -n
-h-1 -w8000 -b >> INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Cleaning up TEMPOBJECTS
echo Cleaning up TEMPOBJECTS >> INSTALL.log
osql -E -S%i% -d%d% -Q"set nocount on delete from dbo.TEMPOBJECTS" -n -h-1
-w8000 -b >> INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Get procedure list
echo Get procedure list >> INSTALL.log
dir /B *.prc > PROCLIST.bcp
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Load procedure list to TEMPOBJECTS
echo Load procedure list to TEMPOBJECTS >> INSTALL.log
bcp "%d%.dbo.TEMPOBJECTS" in "PROCLIST.bcp" -S%i% -T -c >> INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Creating dbtsp_RunExtScript
echo Creating dbtsp_RunExtScript >> INSTALL.log
osql -E -S%i% -d%d% -i"dbtsp_RunExtScript.prc" -n -h-1 -w8000 -b >>
INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Running dbtsp_RunExtScript
echo Running dbtsp_RunExtScript >> INSTALL.log
osql -E -S%i% -d%d% -Q"EXEC dbtsp_RunExtScript @.DBName = '%d%'" -n -h-1
-w8000 -b >> INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Running dbtsp_CreateSchedule
echo Running dbtsp_CreateSchedule >> INSTALL.log
osql -E -S%i% -d%d% -Q"EXEC dbtsp_CreateSchedule" -n -h-1 -w8000 -b >>
INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Loading known server compatible version scripts
echo Loading known server compatible version scripts >> INSTALL.log
osql -E -S%i% -d%d% -Q"exec dbtsp_LoadExtScript @.ServerName ='%i%',@.Refresh = 1" -n -h-1 -w8000 -b >> INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Initializing Server level tasks
echo Initializing Server level tasks >> INSTALL.log
osql -E -S%i% -d%d% -Q"exec dbtsp_LoadExtBatch @.ServerName = '%i%',
@.Refresh = 2, @.Schedule = 1, @.init = 1" -n -h-1 -w8000 -b >> INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Initializing database level tasks
echo Initializing database level tasks >> INSTALL.log
osql -E -S%i% -d%d% -Q"exec dbtsp_LoadExtBatch @.ServerName = '%i%',
@.DatabaseName = '%d%', @.Refresh = 2, @.Schedule = 1, @.init = 1" -n -h-1 -w8000
-b >> INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo initializing objects level tasks
echo initializing objects level tasks >> INSTALL.log
osql -E -S%i% -d%d% -Q"exec dbtsp_LoadAllBatch @.DBMS_Name = NULL,
@.ServerName = '%i%', @.DatabaseName = '%d%', @.Refresh = 2, @.Schedule = 1" -n
-h-1 -w8000 -b >> INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Install completed successfully.
echo Install completed successfully. >> INSTALL.log
goto end
:badcon
echo ***** >> INSTALL.log
echo Could not connect to SQL Server.
echo If SQLExpress, verify that SQL Browser service is automatically started
echo and that tcpip protocol is activated.
echo Could not connect to SQL Server. >> INSTALL.log
echo If SQLExpress, verify that SQL Browser service is automatically started
>> INSTALL.log
echo and that tcpip protocol is activated. >> INSTALL.log
goto bad
:baddb
echo ***** >> INSTALL.log
echo Could not create database. See error for more detail.
echo Could not create database. See error for more detail. >> INSTALL.log
goto bad
:bad
echo ***** >> INSTALL.log
echo Unsuccessful. Install did not complete. See log file.
echo Unsuccessful. Install did not complete. See log file. >> INSTALL.log
:end
echo ***** >> INSTALL.log
-- Good Luck
Andre
"Oscar" wrote:
> I need to come up with a mechanism that will allow me to automatically submit
> multiple TSQL scripts residing in directory structure. I tried to write a
> command script but found out that osql.exe does not allow for variabalization
> of most of its switches. Without being able to read variables into -d -q
> switches of osql.exe I can't automate it. Can anyone help?
> Oscar Elleseff
> Oscar.Elleseff@.eclipsys.com|||you may want to check out DB Ghost which has a builder that can process
scripts by directory at approx 1000 per minute. http://www.dbghost.com
"Oscar" wrote:
> I need to come up with a mechanism that will allow me to automatically submit
> multiple TSQL scripts residing in directory structure. I tried to write a
> command script but found out that osql.exe does not allow for variabalization
> of most of its switches. Without being able to read variables into -d -q
> switches of osql.exe I can't automate it. Can anyone help?
> Oscar Elleseff
> Oscar.Elleseff@.eclipsys.com