Reading a directory and submitting multiple TSQL scripts

I need to come up with a mechanism that will allow me to automatically submi
t
multiple TSQL scripts residing in directory structure. I tried to write a
command script but found out that osql.exe does not allow for variabalizatio
n
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.l
og
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.lo
g
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.l
og
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 -w800
0
-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
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:
[vbcol=seagreen]
> I need to come up with a mechanism that will allow me to automatically sub
mit
> multiple TSQL scripts residing in directory structure. I tried to write a
> command script but found out that osql.exe does not allow for variabalizat
ion
> 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 sub
mit
> multiple TSQL scripts residing in directory structure. I tried to write a
> command script but found out that osql.exe does not allow for variabalizat
ion
> 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.comsql

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

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.com
Not 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[vbcol=seagreen]
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

Readers not queued?

When multiple readers are waiting on a message from the same queue, I would expect that the reader that has been waiting the longest would be the first to pick up a message. However, I'm shocked to discover that the opposite seems to be true; in my tests I'm showing that the reader that has been waiting the least time picks up a message first! This seems totally counter-intuitive, and I'd like to know why it's working this way. This implementation will cause a lot more reader timeouts to occur than a properly queued method. For instance, assume that I have two readers, each using a one minute timeout. Reader #1 starts waiting, and reader #2 is busy for another 20 seconds before it starts waiting. 39 seconds later a message comes in and reader #2 will pick it up, leaving reader #1 to time out one second later! I would much rather have reader #1 pick up the message and reader #2 continue to wait for 20 more seconds.

I'm considering filing a bug on Connect about this, but I thought I'd post here first and see if I can get an answer...

Following is the script I'm using to test:

Setup / Window #1

--
CREATE DATABASE SimpleSSB
GO

USE SimpleSSB
GO

--Create a database master key
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'onteuhoeu'
GO

--Create a message type
CREATE MESSAGE TYPE Simple_Msg
VALIDATION = EMPTY
GO

--Create a contract based on the message type
CREATE CONTRACT Simple_Contract
(Simple_Msg SENT BY INITIATOR)
GO

--create a queue
CREATE QUEUE Simple_Queue
GO

--Create a service
CREATE SERVICE Simple_Service
ON QUEUE Simple_Queue
(Simple_Contract)
GO
--

Go start the other windows now

Readers: Windows #2-n

--
USE SimpleSSB
GO

WAITFOR
(
RECEIVE *
FROM Simple_Queue
), TIMEOUT 300000

--

Start at least two readers, then do
--

--send a message...
DECLARE @.h UNIQUEIDENTIFIER

BEGIN DIALOG CONVERSATION @.h
FROM SERVICE Simple_Service
TO SERVICE 'Simple_Service'
ON CONTRACT Simple_Contract
WITH ENCRYPTION=OFF;

SEND ON CONVERSATION @.h
MESSAGE TYPE Simple_Msg
GO

--

... the last reader you've started will pick up the message first. Note I'm testing on 9.0.3033, in case that matters.

Thanks!

Hi Adam,

The bevior you see is intentional. The purpose is to allow activated tasks to exit after a spike.

Suppose a traffic spike happens and suddenly all queue readers are activated to drain the queue. After the queue is drained, ideally the extra readers should go away. If we'd honor the longest waiting, then potentially all queue readers will stay active because each one will eventually get a message before the WAITFOR times out. With the behavior you see one reader will steal all messages, causing the other activated tasks to time out and exit. The same thinking goes for external readers as well.

HTH,
~ Remus

|||Hi Remus,

Thanks for the response. I agree that the behavior you're describing makes sense for activation, but in the scenario I'm working on--a farm of readers waiting on messages--it doesn't really work. I want to balance the activity across the farm so if one reader is working, the one waiting the longest will be the next to pick up a message. Any ideas?

Thanks,
Adam|||

You can't change the way WAITFOR(RECEIVE...) is notified.

If a reader is working, then it won't have any RECEIVE posted, so any available work will go to the next reader. The existing mode of operation will cause the first machine in the farm to take any load up to it's max capacity, then the next machine start picking up load until it max out, then the next and so on. What you describe would cause all machines to take an even workload and all grow simultanously until they all reach the max. Why is the first described behavior unacceptable?

An alternative to consider would be to use the broker built-in load balancing capabilities. If the same service is declared in multiple databases, then dialog targeting the said service will spread evenly across all instances of the service. Maybe you can use this feature to spread the workload.

HTH,
~ Remus

|||Hi Remus,

The reason it's unacceptable is that the readers in this case will be doing other work in addition to processing the queues. Basically, the scenario I'm working with right now is an application that already uses a load balanced server farm for doing some work, and the idea is to add some additional functionality queued via SSB. Obviously since the farm is already balanced it would be nice to also balance the SSB stuff--otherwise the "first" server in the farm is going to overload and not be able to handle the rest of its duties.

I was not aware of the load balancing feature you mention... I'll try it out and see how it fits into the scenario.

Thanks again! Your replies have been very helpful.
Adam

READ COMMITTED SNAPSHOT ON causes performance degradation

I am running a benchmark test with multiple connections running the same
stored procedure with different parameters. This stored procedures does only
SELECT. There are no other activity on the database.
The stored procedure containst this select
SELECT Model,AVG(Price),MIN(Price),MAX(Price),COUNT(*)
FROM SH_Product
WHERE Project_Number = @.Station
AND EmployeeID = 0
AND Type = @.Match100
GROUP BY Model
ORDER BY Model
When the database is set in READ COMMITTED SNAPSHOT OFF mode, the number of
transactions per second increases linearly as more and more connections are
added.
But when the database is set to READ COMMITTED SNAPSHOT ON, the performance
degrades after 20 users, the total transactions processed per second remains
constant when number of users increase. That means for each user the
transactions per second reduces.
I can understand this if there was any other INSERT/UPDATE/DELETE activity
happening on the database, as SELECT will have to traverse the row version
chain to get the data, but in SELECT only environment, how can the
performance degrade.
With READ COMMITTED SNAPSHOT ON, there are no locks to acquire hence less
overhead for SQL Server. I have a PSS ticket open for this, but I am getting
a satisfactory answer. All I get is since SELECT needs to go to tempdb to get
row version it is slower, but my point is if there is no data change why does
SQL Server has to go to tempdb?
Am I missing something?. Please help.
Thank youOn Thu, 27 Sep 2007 12:31:01 -0700, Shailesh Khanal wrote:
(snip)
>I can understand this if there was any other INSERT/UPDATE/DELETE activity
>happening on the database, as SELECT will have to traverse the row version
>chain to get the data, but in SELECT only environment, how can the
>performance degrade.
>With READ COMMITTED SNAPSHOT ON, there are no locks to acquire hence less
>overhead for SQL Server. I have a PSS ticket open for this, but I am getting
>a satisfactory answer. All I get is since SELECT needs to go to tempdb to get
>row version it is slower, but my point is if there is no data change why does
>SQL Server has to go to tempdb?
Hi Shailesh,
I'm not intimately familiar with the internals of READ COMMITTED
SNAPSHOT, but my guess is that SQL Server has to go to tempdb because it
can't know that there are no previous row versions there without looking
first.
Have you considered setting the database to READ ONLY? That will fully
eliminate all locking overhead.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||Thanks Hugo
I am seeing this behavior while running a benchmark, which has different
sets of tests, one of them being CPU intensive test which only does SELECT.
It is not on a real production database so putting database in READ ONLY
mode is not an issue, but I wanted to understand the performance issue
without doing it.
I looked at page file structure in Kalen Delaney's book and I don't see any
information about whether SQL server puts a status bit on the page itself
for locked rows. But with READ COMMITTED SNAPSHOT ON, SQL server puts a 14
byte data in each row to store Transaction Sequence number (XSN), it is only
added when the row is updated. So logically speaking when a connection tries
to SELECT from a row, it has a XSN and when it goes to check the row in disk
if there is no XSN field then it should immediately know that the row is not
modified and should not go to tempdb to check.
Even if there is XSN for the row, and if it's value is less than SELECT XSN
then it should check lock records before going to tempdb. And this overhead
is also incurred when database is in READ COMMITTED SNAPSHOT OFF mode. So I
don't really get why the performance suffers so much.
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:m3oqf31go4ti3d37736nsegqp2patoqjin@.4ax.com...
> On Thu, 27 Sep 2007 12:31:01 -0700, Shailesh Khanal wrote:
> (snip)
>>I can understand this if there was any other INSERT/UPDATE/DELETE activity
>>happening on the database, as SELECT will have to traverse the row version
>>chain to get the data, but in SELECT only environment, how can the
>>performance degrade.
>>With READ COMMITTED SNAPSHOT ON, there are no locks to acquire hence less
>>overhead for SQL Server. I have a PSS ticket open for this, but I am
>>getting
>>a satisfactory answer. All I get is since SELECT needs to go to tempdb to
>>get
>>row version it is slower, but my point is if there is no data change why
>>does
>>SQL Server has to go to tempdb?
> Hi Shailesh,
> I'm not intimately familiar with the internals of READ COMMITTED
> SNAPSHOT, but my guess is that SQL Server has to go to tempdb because it
> can't know that there are no previous row versions there without looking
> first.
> Have you considered setting the database to READ ONLY? That will fully
> eliminate all locking overhead.
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

RDA access to multiple SQL Server instances

Hello,

There are several instances in SQL Server. I use RDA method to access SQL Server from mobile devices. But RDA method only gets the server IP,not the instance name. So, how can I define RDA to access to the instance that I need?

Can you provide some details on how exactly are you using RDA?

Are you going through an OLEDB provider? If so what is the connection string?

|||Yes it is possible. datasource=10.0.0.1\instance and virtual directory ip=10.0.0.1. by this way,it is possible..

RDA access to multiple SQL Server instances

Hello,

There are several instances in SQL Server. I use RDA method to access SQL Server from mobile devices. But RDA method only gets the server IP,not the instance name. So, how can I define RDA to access to the instance that I need?

Can you provide some details on how exactly are you using RDA?

Are you going through an OLEDB provider? If so what is the connection string?

|||Yes it is possible. datasource=10.0.0.1\instance and virtual directory ip=10.0.0.1. by this way,it is possible..

RDA access to multiple SQL Server instances

Hello,

There are several instances in SQL Server. I use RDA method to access SQL Server from mobile devices. But RDA method only gets the server IP,not the instance name. So, how can I define RDA to access to the instance that I need?

Can you provide some details on how exactly are you using RDA?

Are you going through an OLEDB provider? If so what is the connection string?

|||Yes it is possible. datasource=10.0.0.1\instance and virtual directory ip=10.0.0.1. by this way,it is possible..

RB - Multiple Data Source for Report Builder

I add two data sources to the designer. Create a new dsv using one data source first. Then right click in the designer to add a new table. This time I use the second data source. When I create a Report Model and run it, an error occurred: "Message: Invalid object name 'dbo.tblTrade'. Command: SELECT COUNT(*) FROM [dbo].[tblTrade] t"

It does not recognize the second data source. Look at the property of the dsv, it only points to the first data source, not the other one...

If you look at the XML code for the dsv, there is a DataSourceID tag right above the Schema tag. Can this tag be expanded to include the 2nd data source? Can the XML code be tweaked to include the second data source?

</Annotations>

<DataSourceID>Db House01</DataSourceID>

<Schema>

|||"Our newly created Data Source is positioned as the default, and will serve us in meeting the objectives of our practice exercise. A Data Source View for a Report Model Project, unlike a Data Source View for an Analysis Services Project, can only reference a single Data Source. "

Is this true?

http://www.databasejournal.com/features/mssql/article.php/10894_3598931_4

|||

Yes, that is correct.

|||By creating a named query can overcome the single data source limitation.|||

This solution does not work. If one try to create model bases on such Data source View, table entity for the named query defined in DSV, which is not bases on prime data source, is not being created.

Can you please give some steps to over come this problem?

|||

MSDN Online Book link below:

http://msdn2.microsoft.com/en-us/library/ms175683.aspx

Raw files - create once

Hi,

I try to add multiple files to a raw files. I use a loop for it and set the write option to "create once", so that the file should be created when the package is started and files are appended as they flow to the destination... However I always get an error when I try to add the second file that the raw file already exists... Well, I expect that it exists but I don't expect this error because that's not the intended behaviour!

Is there anything I also have to do to use the raw file as it's described in BOL?

Thanks,

All answered here Thomas: http://blogs.conchango.com/jamiethomson/archive/2005/12/01/2443.aspx

-Jamie

|||

Jamie,

Google is always as good as the questions you ask it... ;-)

Meanwhile I came around with another solution... I just created a "template" file which I copy over the existing file before the data flow is started. Not perfect, but it works... So I always work with "append" to get around this problem...

Thanks... (BTW: Do you come to PASS Europe next week?)

|||

Hi Thomas,

Yeah, I didn't like that approach because it meant you had to deploy a raw file.

In the end we had to do it anyway though cos we have a datareader destination in the same data flow and there is a bug that means the datareader destination will not work if the data flow had DelayValidation=TRUE.

Unfortunately I can't make it to PASS. I am working on the same project as Darren at the moment and its required that there's always one of us around. So he is going to PASS and I'm not :-(

-Jamie

Raw File Source issue

I have a single file that contains records destined for multiple tables. The "first" record is considered primary and the other records are considered "secondary" (meaning that they have foreign keys to the primary table).

In order to properly insert this I needed to use two data flows. The first data flow directed the primary rows to the primary table and the secondary rows get directed to a raw file destination. The second data flow read in from the raw file and wrote out the rows to the appropriate tables.

But here is my problem.

This darn validation! While I think validation is a great idea, the extensive use of it in what seems like EVERY aspect of SSIS seems to cause more headaches than not...

When I deploy my package and try to run it I get an error because the raw file source DOES NOT EXIST. Of course it does not exist, it gets created when the package runs... I cannot deploy something that does not exist yet.

I even have a problem while I am trying to work with the package in VS. The only way to get the package to run is to disable the second data flow so it does not try to validate it. Run the package so the raw file is created. And then re-enable the second data flow again. (Which then I guess I could take the raw file and deploy it with my package but that just seems silly.... deploying temporary files... that would be like deploying Internet Explorer with the Temporary Internet Files folders....)

And of course with that type of solution my package could never "clean up" after itself...

Try setting DelayValidation to TRUE for all source / destination components

Thanks,
Sankaranarayanan MG

|||I have used DelayValidation on other objects but I do not see any property of that sort when looking at the Raw File Source. The only thing I see with the word valid is ValidateExternalMetadata. Should I be looking elsewhere?|||

Yes, DelayValidation is a task property not a component property so you would need to set it on the DataFlow task that contains the component you need to have validation delayed on. Note that this delays the validation for all the components in the task not just the one component you need it for.

HTH,

Matt

Raw File as Source for Multiple Packages

I have a question regarding Raw Files. I am breaking a large package into more modular components for better processing and debugging.

The process will start with a preparatory dataflow that will create a Raw File(s). This Raw File will then be used as the source in possibly 6 data flows and/or packages.

My question is whether 1 Raw File can be read concurrently by the multiple jobs and how this would affect processing. I'm assuming that this would slow processing.

My other option is to Multicast the writing of the Raw File to 5 other versions of the file. All would be identical except for filename. Obviously this would use more disk space but this is not a concern as we have lots of disk space. Our concern is for speedy processing.

If you have experience with Raw Files, please let me know how you approached this issue. As always, blogs and specific examples are always great!

Thanks in advance.

Why not use a SQL Server table instead?|||

Do you mean to load my candidate items to a dynamically created temp table? And then I can query against this table in successive packages.

What is the process for creating the temp table? I think on my Control Flow palette, I use an ExecuteSQL task to execute a Create Temp table SQL...next a Data Flow Task can then load the table and finally after all Processing packages/flows complete, I use Execute SQL Task to Drop or Truncate the table.

Is this the process or is there a better way?

|||

omegarazor wrote:

Do you mean to load my candidate items to a dynamically created temp table? And then I can query against this table in successive packages.

What is the process for creating the temp table? I think on my Control Flow palette, I use an ExecuteSQL task to execute a Create Temp table SQL...next a Data Flow Task can then load the table and finally after all Processing packages/flows complete, I use Execute SQL Task to Drop or Truncate the table.

Is this the process or is there a better way?

This is the process, but I would like to add two refinements:

If you're going to be dynamically creating and dropping the table, be certain to build the first Execute SQL task so that it has a "IF EXISTS .. DROP TABLE; CREATE TABLE" logic, so it will run correctly regardless of whether the table already exists at run time. (Use the script generation tools in SSMS to build this script.) Set DelayValidation = True for any tasks that rely on the table, so the package can run regardless of whether the table exists.