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

Read\Write Files in SQL 2000

Hi, I was just wondering if it was possible/ the best way to read and write text files for example in SQL 2000?

Is it only possible to do this using extended stored procedures or do SQL commands exist to perform file IO?

Thanks very much,

Ewen

What is your purpose for this ? Do you intend to write query results to text file ? You can use BCP to do that. Or for importing a text file into a table ? use BULK INSERT

Read/Write statistics, per table?

And one other Q, is there any way to get the number of reads/writes, one
each table, for a particular time period, without profiling the db?
Thanks again!
In SQL Server 2005, you can pull at least some form of this information from
sys.dm_db_index_usage_stats. In 2000, I don't think you can get much of
anything.

Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Derrick" <derrick1298@.excite.com> wrote in message
news:u1XMdROuHHA.3544@.TK2MSFTNGP03.phx.gbl...
> And one other Q, is there any way to get the number of reads/writes, one
> each table, for a particular time period, without profiling the db?
> Thanks again!
>

Read/Write statistics, per table?

And one other Q, is there any way to get the number of reads/writes, one
each table, for a particular time period, without profiling the db?
Thanks again!In SQL Server 2005, you can pull at least some form of this information from
sys.dm_db_index_usage_stats. In 2000, I don't think you can get much of
anything.
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Derrick" <derrick1298@.excite.com> wrote in message
news:u1XMdROuHHA.3544@.TK2MSFTNGP03.phx.gbl...
> And one other Q, is there any way to get the number of reads/writes, one
> each table, for a particular time period, without profiling the db?
> Thanks again!
>

Read/Write statistics, per table?

And one other Q, is there any way to get the number of reads/writes, one
each table, for a particular time period, without profiling the db?
Thanks again!In SQL Server 2005, you can pull at least some form of this information from
sys.dm_db_index_usage_stats. In 2000, I don't think you can get much of
anything.
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Derrick" <derrick1298@.excite.com> wrote in message
news:u1XMdROuHHA.3544@.TK2MSFTNGP03.phx.gbl...
> And one other Q, is there any way to get the number of reads/writes, one
> each table, for a particular time period, without profiling the db?
> Thanks again!
>

Read/Write Performance

Hello,

We currently run sql 2005 server and also sql express in our dev environments. We use sql express as an offline store (smart client). We have a similar/exact schema on the sql 2005 server and also the express.

We use the auto attach feature to connect to the express version of the database. Both the developer machines and the one that is running the sql 2005 server have exactly the same hardware configuration. The only difference may be that the server box is not running the VS.Net environment. The disk space etc is pretty much the same. Actually we run another database server(DB2) on the 2005 server machine.

We have observed that sql express is much slower and queries execute much slower aswell. For example, this may not be a totally scientific way of checking but a long running query on the server took only 2 minutes while on express it took longer than 9 minutes. The schema and data etc are the same.

Is there something we need to look into as far as read write speed/performance goes ?

TIA,

Avinash

Hi Avinash,

Could you provide a bit more information about how you determined the time it took to run the queries? Understanding your testing methodology will help determine if it is contributing or not.

Additionally, you mention you're using the auto attach feature, are you using User Instances as well? When using the VS UI to create connection to a database, the connection string specifies User Instance = True. This should be fine, but it's important to know.

Regards,

Mike Wachal
SQL Express team

-
Please mark your thread as Answered when you get your solution.

|||

Hello Mike Wachal,

Yes we use User Instance and Auto Attach in the connection string to express.

At this time, I dont have a very 'scientific' or for that matter a very solid way to test out the performance. My question came from a general observation and thought I'd bounce it off the expert community to see if there was some caveats built into the use of express particularly with the auto attach mode.

Like I've already mentioned - our general observation is that queries 'Seem' to take longer on express both read and write when compared to 2005 server. Again, this may be a configuration thing aswell. But we are running the default configuration of express as done from within VS.Net 2005 setup and have made no changes what so ever.

On some counts we've put the start time and end time of execution in trace messages and have found the difference in execution times.

Thats all I have at this time,

Thanks and Regards,

Avinash

|||

Thanks Avinash,

In general, SQL Express should perform similarly to other Editions of SQL Server, but we do have limitation that might affect performance. For one thing, SQL Express will only use a single CPU and will only address 1 GB of RAM. If your hardware has more than this, then you could see a performance difference because the non-Express edition would be able to use the extra hardware components to increass performance over Express Edition.

If you're hardware is the same and within the limitations of SQL Express, I'm not sure what could be the issue without knowing more about the specific queries and data that is being queried.

Regards,

Mike Wachal
SQL Express team

-
Check out my tips for getting your answer faster and how to ask a good question: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=307712&SiteID=1

|||

Hello Mike,

Many thanks for your response. In our case both the server and the client run the exact same hardware. They are (both) running on 1GB RAM.

I'll watch out for any further issues I may run into - and then bring them to your notice with all the data that I can provide.

Thanks,

Avinash

|||

OK Avinash,

Good luck with this.

As you find specific queries that perform differently you might want to bring those specific queries up in the SQL Database Engine forum. The folks in that forum will likely have some additional ideas both on tuning your queries and why they may perform differently on different Editions.

Regards,

Mike Wachal
SQL Express team

-
Mark the best posts as Answers!

sql

Read/Write for Variables

Hi,

I am using a Script Component and I have a Read/Write Variable varStatusCase (as assigned in the Custom Properties of my Script Component). I used this inside my script to get a specific value. However, when I ran it I get this error:

The collection of variables locked for read and write access is not available outside of PostExecute.

How do I repair this?

Thanks,

cherriesh

If you want to write on a variable you have to use the post execute method something like this

Public Overrides Sub PostExecute()

Me.Variables.varName = Something

End Sub

I dont know if there's another way to do it but this is the only one i've found. And it works.

|||

But just know that this doesn't execute for every row.

|||

Check if this post help you!

http://pedrocgd.blogspot.com/2007/05/ssis-populating-dimension_28.html

Regards!

|||

hi,

I tried doing what is in your link:

Private NextKey As Int32 = 0

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim MaxKey As Integer = Me.Variables.varStatus

NextKey = MaxKey + 1

Row.oStatusCode = NextKey

MyBase.PreExecute()

End Sub

Public Overrides Sub PreExecute()

Me.VariableDispenser.LockForRead("User::varStatus")

MyBase.PreExecute()

End Sub

Public Overrides Sub PostExecute()

Me.ReadWriteVariables("varStatus").Value = NextKey

MyBase.PostExecute()

End Sub

My variable "varStatus" is assigned in Script Component ReadWrite Variable.

Am I missing anything? I'm still having the same error.

cherriesh

|||

dear friend,

copy my statment in my post... check the link... the solution is there.

check the pre-execute method

helped?

read/write BLOBS in sqlser using asp.net

here i have some problem related to BLOBS in sqlserver
i need to store .bmp or .jpg files in sqlserver2000 and using
asp.net i need to retrive that images and show in a webform.
if user want to insert a new Image into database i need to do
it from frontend using asp.net
just like i need add,update,edit images using asp.net
can any one give me idea how to approch.

thanks in advance..
-SridharHere's a KnowledgeBase article that explains it:HOW TO: Read and Write a File to and from a BLOB Column by Using Chunking in ADO.NET and Visual Basic .NET. It includes links to equivalent versions of the article for C++ and C#.

Don

Read, write and update xml data type.

Hi All,

I would like to learn about xml data type of sql server 2005. I am using c# to develop a project that will use sql server express as a database. What I want to accomplish in my project is to serialize an object and save into a field with xml data type. Also I want to have same functionality in other way around. Retrieve this xml representation of the object, Deserialize it so get the saved object back into application.

I would be happy If you can provide me some code which shows how to accomplish this task or some links that directs me to the appropriate docs.

Thanks in advance.

There is lots of material on MSDN, there is a section about the xml data type with subsections about the methods of the xml data type (i.e. query, value, exist, modify, nodes) and the XML DML (XML data modification language).|||

Hi Martin,

Thanks for the reply and links. I haven't gone through the links you sent completly but I think the subject I typed here is misleading so let me explain a bit more, the difficulty I have. . I have no problem with creating a table with an xml type field. I believe by reading the links you have sent I can perform insert, update and delete functions. My difficulty starts just after the seriliazation of an object or just before the deserialization of the xml data I read from xml field. Both serialize and deserialize methods trys to write/read to/from a file or a stream. All I want is to hold this xml in a data structure where I can use it at the time of serialization or deserialization.

The original situaltion:

I have a form to be filled out by the users of my application. Since there are so many fields of data in this form I dont want to create a table with somany fields. So I am planing to hold info entered by user in an xml field. (and ofcourse I should be able to read the data back from this xml field and display in the app)

Thanks.

|||

Got it worked

I used string/TextWriter and string/TextReader combinations and worked fine. Thanks.

FlatWhite

|||

Hi FlatWhite

I would like to do the same thing you are doing. Could you provide some code snippets for me on how to do it?

Thanks

|||

Well I am neither SQL nor C# expert so you can use my code at your own risk J

First of all I am assuming that you have an SQL database table which has ID and XML fields and also you have InsertObj and SelectObj stored procedures.

TABLE : ObjectTable

ObjID-int-identity

ObjXML-XML

SPs : InsertObj

SelectObj

Code Snippet

CREATE PROCEDURE InsertObj

@.ObjXML xml

AS

BEGIN

INSERT INTO ObjectTable (ObjXML)

VALUES (@.ObjXML)

END

CREATE PROCEDURE SelectObj

@.ObjID int

AS

BEGIN

SELECT ObjXML

FROM ObjTable

WHERE ObjID = @.ObjID

END

I am also assuming that you have class Ojb with three properties property1, property2 and property3 and a form with 4 textboxes

Here is how you can serialize an object and save it in an XML field. (There might be some better way of doing this but sofar no one made a comment on this)

Code Snippet

Obj c = new Obj();

c.property1 = textBox1.Text;

c.property2 = textBox2.Text;

c.property3 = textBox3.Text;

XmlSerializer s = new XmlSerializer(typeof(Obj));

System.Text.StringBuilder builder = new System.Text.StringBuilder();

s.Serialize(XmlWriter.Create(builder),c);

SqlConnection conn = new SqlConnection();

conn.ConnectionString = @."Data Source=Server;Initial Catalog=database;Integrated Security=SSPI;";

conn.Open();

SqlCommand command = conn.CreateCommand();

command.CommandText = "InsertObj";

command.CommandType = System.Data.CommandType.StoredProcedure;

command.Parameters.Add("@.ObjXML", System.Data.SqlDbType.Xml);

command.Parameters[0].Value = builder.ToString();

command.ExecuteNonQuery();

conn.Close();

And this is how you can deserialize an XML field and get back the saved object.

Code Snippet

XmlReaderSettings set = new XmlReaderSettings();

set.ConformanceLevel = ConformanceLevel.Fragment;

Obj c = new Obj();

SqlConnection conn = new SqlConnection();

conn.ConnectionString = @."Data Source=server;Initial Catalog=database;Integrated Security=SSPI;";

conn.Open();

SqlCommand command = conn.CreateCommand();

command.CommandText = "SelectObj";

command.CommandType = System.Data.CommandType.StoredProcedure;

command.Parameters.Add("@.ObjID", System.Data.SqlDbType.Int);

//taking the input from textBox4.

command.Parameters[0].Value = Convert.ToInt32(textBox4.Text);

SqlDataReader datareader = command.ExecuteReader();

System.Text.StringBuilder builder = new System.Text.StringBuilder();

XmlSerializer s = new XmlSerializer(typeof(Obj));

while(datareader.Read())

{

builder.Append(datareader[0]);

}

TextReader tr = new StringReader(builder.ToString());

c = (CObj)s.Deserialize(tr);

tr.Close();

textBox1.Text = c.property1;

textBox2.Text = c.property2;

textBox3.Text = c.property3;

I hope it helps.

Read, write and update xml data type.

Hi All,

I would like to learn about xml data type of sql server 2005. I am using c# to develop a project that will use sql server express as a database. What I want to accomplish in my project is to serialize an object and save into a field with xml data type. Also I want to have same functionality in other way around. Retrieve this xml representation of the object, Deserialize it so get the saved object back into application.

I would be happy If you can provide me some code which shows how to accomplish this task or some links that directs me to the appropriate docs.

Thanks in advance.

There is lots of material on MSDN, there is a section about the xml data type with subsections about the methods of the xml data type (i.e. query, value, exist, modify, nodes) and the XML DML (XML data modification language).|||

Hi Martin,

Thanks for the reply and links. I haven't gone through the links you sent completly but I think the subject I typed here is misleading so let me explain a bit more, the difficulty I have. . I have no problem with creating a table with an xml type field. I believe by reading the links you have sent I can perform insert, update and delete functions. My difficulty starts just after the seriliazation of an object or just before the deserialization of the xml data I read from xml field. Both serialize and deserialize methods trys to write/read to/from a file or a stream. All I want is to hold this xml in a data structure where I can use it at the time of serialization or deserialization.

The original situaltion:

I have a form to be filled out by the users of my application. Since there are so many fields of data in this form I dont want to create a table with somany fields. So I am planing to hold info entered by user in an xml field. (and ofcourse I should be able to read the data back from this xml field and display in the app)

Thanks.

|||

Got it worked

I used string/TextWriter and string/TextReader combinations and worked fine. Thanks.

FlatWhite

|||

Hi FlatWhite

I would like to do the same thing you are doing. Could you provide some code snippets for me on how to do it?

Thanks

|||

Well I am neither SQL nor C# expert so you can use my code at your own risk J

First of all I am assuming that you have an SQL database table which has ID and XML fields and also you have InsertObj and SelectObj stored procedures.

TABLE : ObjectTable

ObjID-int-identity

ObjXML-XML

SPs : InsertObj

SelectObj

Code Snippet

CREATE PROCEDURE InsertObj

@.ObjXML xml

AS

BEGIN

INSERT INTO ObjectTable (ObjXML)

VALUES (@.ObjXML)

END

CREATE PROCEDURE SelectObj

@.ObjID int

AS

BEGIN

SELECT ObjXML

FROM ObjTable

WHERE ObjID = @.ObjID

END

I am also assuming that you have class Ojb with three properties property1, property2 and property3 and a form with 4 textboxes

Here is how you can serialize an object and save it in an XML field. (There might be some better way of doing this but sofar no one made a comment on this)

Code Snippet

Obj c = new Obj();

c.property1 = textBox1.Text;

c.property2 = textBox2.Text;

c.property3 = textBox3.Text;

XmlSerializer s = new XmlSerializer(typeof(Obj));

System.Text.StringBuilder builder = new System.Text.StringBuilder();

s.Serialize(XmlWriter.Create(builder),c);

SqlConnection conn = new SqlConnection();

conn.ConnectionString = @."Data Source=Server;Initial Catalog=database;Integrated Security=SSPI;";

conn.Open();

SqlCommand command = conn.CreateCommand();

command.CommandText = "InsertObj";

command.CommandType = System.Data.CommandType.StoredProcedure;

command.Parameters.Add("@.ObjXML", System.Data.SqlDbType.Xml);

command.Parameters[0].Value = builder.ToString();

command.ExecuteNonQuery();

conn.Close();

And this is how you can deserialize an XML field and get back the saved object.

Code Snippet

XmlReaderSettings set = new XmlReaderSettings();

set.ConformanceLevel = ConformanceLevel.Fragment;

Obj c = new Obj();

SqlConnection conn = new SqlConnection();

conn.ConnectionString = @."Data Source=server;Initial Catalog=database;Integrated Security=SSPI;";

conn.Open();

SqlCommand command = conn.CreateCommand();

command.CommandText = "SelectObj";

command.CommandType = System.Data.CommandType.StoredProcedure;

command.Parameters.Add("@.ObjID", System.Data.SqlDbType.Int);

//taking the input from textBox4.

command.Parameters[0].Value = Convert.ToInt32(textBox4.Text);

SqlDataReader datareader = command.ExecuteReader();

System.Text.StringBuilder builder = new System.Text.StringBuilder();

XmlSerializer s = new XmlSerializer(typeof(Obj));

while(datareader.Read())

{

builder.Append(datareader[0]);

}

TextReader tr = new StringReader(builder.ToString());

c = (CObj)s.Deserialize(tr);

tr.Close();

textBox1.Text = c.property1;

textBox2.Text = c.property2;

textBox3.Text = c.property3;

I hope it helps.

Read Write Variable Cannot access before PostExecute

I have a for each loop on a directory of files, each file has to be imported with a unique surrogate key added. For this I am selecting the max id that exists in the target table and assigning that value to a variable.

Within a script transformation I am copying this value to a variable declared in a script task, and incrementing it on each row processed. Obviously I now want to write this value back out to the higher scoped variable so it is available for the next file.

If I make the variable read write it is not even available for reading until the PostExecute. Is this correct or have I missed something?

To work round this I have created a second higher scoped variable that I can write to in the PostExecute and the other variable is passed in as read only and added another script task to update the variable values.Philip is a colleague of mine and we've just been taking a look at this.

The workaround is to use a multiflatfile adapter because the metadata of the files is identical.

Philip's requirement to be able to read a ReadWriteVariable in PreExecute() is, I feel, a valid one. Is there a reason that this cannot be done?

-Jamie|||You can actually access write-able variables anywhere you want just not the ones on the read/write list. The component has a VariableDispenser that you can lock variables for read and/or write and use them as you will. The limitation we place is only for the ones you specify in the ReadWriteVariables line and this was done to keep locking to a minimum. If we gave access during row processing then because we don't know the usage we would need to keep the variable locked during the entire ProcessInput call. If some other transform needed this variable as well then we have concurrency issues. This way the user can lock variables for write but has to do it explicitly so that it can be unlocked explicitly as well and hopefully the locking time can be kept to a minimum since the script author is controlling the locking.

HTH,
Matt

Read Write Image data with Sqlceserver

I was wondering if it is possible to read image data from a database in .net compact framework. Since cf does not have image.fromstream(memstream) to work with, I don't know how else to read the image from the database and then place it into a picturebox.

Here is the code I have been trying out:

Dim Img As Image
'
Dim conn As New SqlCeConnection("Data Source = \My Documents\test2.sdf")

conn.Open()

Dim sql As String = "SELECT * FROM Dater"
Dim cmd As New SqlCeCommand(sql, conn)
Dim reader As SqlCeDataReader = _
cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

While reader.Read()

TextBox1.Text = reader.Item("name")
Dim b(reader.GetBytes(1, 0, Nothing, 0, Integer.MaxValue) - 1) As Byte

reader.GetBytes(1, 0, b, 0, b.Length)
Dim ms As New System.IO.MemoryStream(b)
Dim bmp As New Bitmap(ms) <-Error: Value does not fall within expected range
Img = bmp

End While

PictureBox2.Image = Img

I get an error ,Value does not fall within expected range.

Does this mean the image was not save correctly in the database?

Thanks for any helpJust to give somebody an idea on how to do this you can try converting an image to a string and back again using the convertFromBase64 and convertToBase64 functions. Then you can save an image to a database as a string using SQLServer.

Thanks for all of your help, really appreciate it.

sql

Read Write Image data with Sqlceserver

I was wondering if it is possible to read image data from a database in .net compact framework. Since cf does not have image.fromstream(memstream) to work with, I don't know how else to read the image from the database and then place it into a picturebox.

Here is the code I have been trying out:

Dim Img As Image
'
Dim conn As New SqlCeConnection("Data Source = \My Documents\test2.sdf")

conn.Open()

Dim sql As String = "SELECT * FROM Dater"
Dim cmd As New SqlCeCommand(sql, conn)
Dim reader As SqlCeDataReader = _
cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

While reader.Read()

TextBox1.Text = reader.Item("name")
Dim b(reader.GetBytes(1, 0, Nothing, 0, Integer.MaxValue) - 1) As Byte

reader.GetBytes(1, 0, b, 0, b.Length)
Dim ms As New System.IO.MemoryStream(b)
Dim bmp As New Bitmap(ms) <-Error: Value does not fall within expected range
Img = bmp

End While

PictureBox2.Image = Img

I get an error ,Value does not fall within expected range.

Does this mean the image was not save correctly in the database?

Thanks for any helpJust to give somebody an idea on how to do this you can try converting an image to a string and back again using the convertFromBase64 and convertToBase64 functions. Then you can save an image to a database as a string using SQLServer.

Thanks for all of your help, really appreciate it.

Read large binary data from Sql Server 2005

Hi

I've followed a tutorial on how to write and read varbinary(max) data to and from a database. But when i try to read the data i get the error that the data would be truncated, but only when the varbinary(max) is greater then 8kB. I've used a system stored procedure (sp_tableoption) to set the table that holds the data to store data outside rows. To select the data i'm using a stored procedure:

SELECT imageData , MIMETypeFROMPicturesWHERE(imageTitle = @.imageTitle)

And then using an .aspx page to Response.Write the data:

Using conn As New sql.SqlConnection
conn.ConnectionString = ConfigurationManager.ConnectionStrings("myConnectionString").ToString

Dim getLogoCommand As New sql.SqlCommand
getLogoCommand.CommandType = Data.CommandType.StoredProcedure
getLogoCommand.CommandText = "GetPicture"
getLogoCommand.Connection = conn

Dim imageTitleParameter As New sql.SqlParameter("@.imageTitle", Data.SqlDbType.NVarChar, 200)
imageTitleParameter.Value = Request("imageTitle")
imageTitleParameter.Direction = Data.ParameterDirection.Input

getLogoCommand.Parameters.Add(imageTitleParameter)

conn.Open()

Using logoReader As sql.SqlDataReader = getLogoCommand.ExecuteReader
logoReader.Read()
If logoReader.HasRows = True Then
Response.Clear()
Response.ContentType = logoReader("MIMEtype").ToString()
Response.BinaryWrite(logoReader("imageData"))
End If

End Using

conn.Close()

End Using

Can anyone please help me with this?!

Even though you are using SQL Server 2005... the "compatibility mode" is set to "80" (meaning 8.0... meaning SQL Server 2000).

So, until you run "EXEC sp_dbcmptlevel 90"... your MAX will == 8000 :) (you'll only have to run this once btw)

|||

Thanks for your reply! I can see <binary data> when I'm just watching the table in SQL Server Studio Manager but still not through a stored procedure. I, however, can get the data with:

SELECT * FROM Pictures WHERE (imageTitle = @.imagetitle ) as a "text" command on the .aspx page.

So like that i'm able to actually show anything bigger then 8 kB on the page, but now I can't do anything with the stored procedure like, when there is no data for that imageTitle I can't make it return another value.

When I use

---

conn.Open()

Using pictureReader As sql.SqlDataReader = getLogoCommand.ExecuteReader
pictureReader.Read()
If pictureReader.HasRows = True Then
Response.Clear()
Response.ContentType = pictureReader("MIMEtype").ToString()
Response.BinaryWrite(pictureReader("imageData"))
End If

End Using

---

The pictureReader.HasRows = True even when there is no binary data. Is there anyway to get the page not to do anything when there is no data in a row?

Thanks

Read Image Data From SQL Server

Here is my task I am storing pdf's in sql server. I would like to retrieve the binary data from sql server and write the pdf content into an existing aspx page to the appropriate pageview section. What is the best way to handle this. The code works below but it loads a new browser with the content. I need it to appear in it's tabbed section in the original aspx file. Any assistance you can give me would be greatly appreciated.

Thanks Jerry

oSQLConn.Open()

Dim myreaderAs SqlDataReader

myreader = myCommand.ExecuteReader

Response.Expires = 0

Response.Buffer =True

Response.Clear()

DoWhile (myreader.Read())

Response.ContentType = ("application/pdf")

Response.BinaryWrite(myreader.Item("img_content"))

Loop

Hi,

From your description, it seems that you are going to retrieve the PDF data in tabbed section of the current aspx file, right?

Well, in your scenario, I noticed that you are going to reset the content type to "application/pdf", but the aspx page's content type is "text/html", they cannot be displayed at the same page.

I guess you have comment the "Response.contenttype.." line, right? And you are using Resposne. Binary Write to write the specified information to the current HTTP output. I suggest that you can have a try to wrap all these codes into a user control, use this control in your tabbed section of your current aspx page and try to see if it can work.

Thanks.

|||

Thanks for your response. Maybe this description will help more. I havea tab control on an aspx form. Each tab needs to display the picture, pdf etc etc associated with that particular tab for a specific user. The binary data is stored in a sql server 2005 image field. What would be the best way to achieve the desired result. What would be a good way to handle this? I should also add there may be more then one pdf/image for a particular section.

Thanks

Jerry

|||

Hi,

Well, you can add a Image control in your Tab control container, and create a page which works for displaying the image data from database.

The code snippet on that page to show the image data: (ShowImg.aspx)

// myRead is a DataReader object.
Byte[] Buffer = (Byte[])myRead[0];

//Output
this.Response.Clear();
this.Response.BinaryWrite(Buffer);
this.Response.End();

And then, assign the ImageUrl property of the Image control.

this.Image1.ImageUrl = "showimg.aspx"

Of course, if you want to show different pictures based on the parameters like following:

this.Image1.ImageUrl = "showimg.aspx?imageid=123"

Then, you can receive the parameter on ShowImg.aspx, make a query with that parameter against the database and return the corresponding image data.

Thanks.

Read and write a Constraint or Default Value

Okay, maybe I'm getting ahead of myself.

Using SQL Server Express, VWD and .net 2.0 I've figured out how to drop a Table Column Constraint or Default Value/Binding and then Create it again using a stored procedure. What I can't figure out is how to retrieve that column's constraint value and write it to, say a label, in an aspx page, simply for reference. Is it possible? In this case the Data Type of the column is money.

I'm using it to perform a calculation to a column with a value that the user inserts into another column. (Column1(user input) minus Column2(with Default Value) = Column3(Difference). I just want to read Column2's Default Value for reference so I know whether to change it or not.

Tables have Check Constraints, Columns do not.

Open up the Master database and take a look inside.

You are looking for the INFORMATION_SCHEMA views.

In particular, you want this one: INFORMATION_SCHEMA.CHECK_CONSTRAINTS and INFORMATION_SCHEMA.COLUMNS

|||

If I open dbo.tbl1 in my database and right click to modify, I can put a Default Value or Binding of 1000000.00(or whatever) in a column I've named "Gen_ourlim". When I do that, there is, in the folder "Constraints" under dbo.tbl1, something created called DF_tbl1_Gen_ourlim. When I drop that Default Value from column "Gen_ourlim" the DF_tbl1_Gen_ourlim goes away in the Constraint folder. That's why I called the column having a constraint I suppose. In any case, how can I read that Default Value and write it to a web page?

|||

Did you query INFORMATION_SCHEMA.COLUMNS?

The default value is a column in that view, as is the catalog name, the schema name, the table name and the column name. Plus lots of other goodies about a column.

Querying data and placing it on a web page is an entirely different discussion and belongs in one of the web forums.

|||

Thanks David,

Got it. At least I was able to query it:

USE <path to database>

SELECT Column_Name, Column_Default

frominformation_schema.columnswhere table_name='tbl1'and Column_name='Gen_ourlim'

|||

Then you need to mark this thread as resolved, mark answers as appropriate, and start a new thread in a nore appropriate forum on how to put data on a web page.

(But the internet is chock full of how-to articles on that, and any beginner asp.net book will tell you how also. Best to start doing it and ask specific questions when you get stuck.)

|||

Will do David. Thanks again. Using the query in VWD is the easy part. Put the Select in a stored procedure in your Database Explorer as such:

ALTER PROCEDURE dbo.GetDefaultValue

AS
SELECT COLUMN_NAME, COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (TABLE_NAME ='tbl1') AND (COLUMN_NAME = 'Gen_ourlim')
RETURN

Put a SqlDataSource on your page along with a DataView and then configure it to use the stored procedure as the Select in the SqlDataSource. There's a nice little video tutorial on doing this at

http://www.asp.net/learn/sql-videos/video-114.aspx

sql

READ access to sql server activity monitor and sql server JOB acti

HI All,
Due to sox audit complaiance issues, we cannot give full read write access
to our production sql server 2005 databases to our developers.
But our developers want to have atleast read access to sql server activity
monitor and sql server JOB activity monitor.
How can I give only read access to those two monitors?
If I give read access to MSDB database then it will work?
I think only sysadmin role has access to those monitors right?
Please help on how to give read olny access to those monitors in sql server
2005.
Regards
Kumar
You just need to give them ProcessAdmin Server Role and in UserMapping
for MSDB give them SQLAgentReaderRole and SQLAgentUserRole
HTH,
Dinesh

READ access to sql server activity monitor and sql server JOB acti

HI All,
Due to sox audit complaiance issues, we cannot give full read write access
to our production sql server 2005 databases to our developers.
But our developers want to have atleast read access to sql server activity
monitor and sql server JOB activity monitor.
How can I give only read access to those two monitors?
If I give read access to MSDB database then it will work?
I think only sysadmin role has access to those monitors right?
Please help on how to give read olny access to those monitors in sql server
2005.
Regards
KumarYou just need to give them ProcessAdmin Server Role and in UserMapping
for MSDB give them SQLAgentReaderRole and SQLAgentUserRole
HTH,
Dineshsql