Hai,
I am working in an IBM AIX machine, with Oracle 8i.
I am trying to read a file through UTL_FILE read.
The directory which has the file does not have any permisions for the
others. It is restricted to 750.
The file belongs to a local user and group.
So, as workaround we added our local user to the secondary group of oracle, so that oracle user also has the same access permissions as the local user. But still we were not able to read the file from the procedure.
Do we need to restart oracle ?
will be happy if anyone can advice.Is your UTL_FILE_DIR parameter correct in init.ora ?
If not add it and restart database
Originally posted by Anandraj
Hai,
I am working in an IBM AIX machine, with Oracle 8i.
I am trying to read a file through UTL_FILE read.
The directory which has the file does not have any permisions for the
others. It is restricted to 750.
The file belongs to a local user and group.
So, as workaround we added our local user to the secondary group of oracle, so that oracle user also has the same access permissions as the local user. But still we were not able to read the file from the procedure.
Do we need to restart oracle ?
will be happy if anyone can advice.|||Originally posted by ndu35
Is your UTL_FILE_DIR parameter correct in init.ora ?
If not add it and restart database
Hai ndu35,
Thanks for that suggestion. Anyway the entry is already present in init.ora. Also the database was restarted immediately.
Showing posts with label directory. Show all posts
Showing posts with label directory. Show all posts
Friday, March 30, 2012
Reading a file name that changes
Brand new to SSIS so bear with me, if something is obvious.
I want to be able to read a file from a certain directory. But the file name changes every day. So today its File20061203 tomorrow File20061304 or the next day it could be FileNB4434. The format in the file will always be the same though. I just want for a user to be able to drop a file in a directory and the package pick it up once a day.
Would I have to to create a script task or could I use a variable. I have been trying to use the variable but have not been able to get them to work. This calls for only looking for 1 text file in a folder but any additional links that show some good variable examples would be appreciated. One where only part of the variable changes File(Variable)Division.txt
Thanks in advance
Try using the ForEach Loop Container with a file enumerator. That will store the file name in a variable you specify. Set the connect string on the File connection manager by using an expression (@.User:<name of your variable>).
Normally the Loop container is used when you have a number of files, but it works just fine with one.
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
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
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
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
Reading a directory
I do a lot of file processing and I usually run a little script I copy
and paste to read directory information to see if a new file it there
and then process the file if it is. So, I decided to wise up and make
a stored procedure to automate a lot of that.
CREATE TABLE #DIR (FileName varchar(100))
INSERT INTO myTABLE
SELECT filename FROM #DIR
Note that the sp_ prefix is reserved for system procedures, and SQL Server
will first look for these in the master database. Do not use it for your
own code.
I have an article on my web site that discusses a couple of alternatives:
http://www.sommarskog.se/share_data.html.
and paste to read directory information to see if a new file it there
and then process the file if it is. So, I decided to wise up and make
a stored procedure to automate a lot of that.
The pivotal step in this is that i run a command that looks like:
CREATE TABLE #DIR (FileName varchar(100))
DECLARE @.Cmd varchar(1050)
SET@.Cmd = 'DIR "' + @.Path + CASE WHEN RIGHT(@.Path, 1) = '\' THEN ''
ELSE '\' END + @.WildCard + '"'
INSERT INTO #DIR
EXEC master..xp_CmdShell @.Cmd
When I run the stored procedure I get back the files and folders in
there that match the wildcard and all is good!!!!
...Until I try to put that information into a table while calling that
stored procedure:
CREATE TABLE #Files (
Path varchar(100),
FileName varchar(100),
PathAndFileName varchar(150),
FileDateTime SmallDateTime,
FileLength int,
FileType Varchar(10))
INSERT INTO #Files
EXEC sp_GetFileNames @.Path = '\\isoft2\ftp\Legacy\Billing\', @.Wildcard
= '*.txt'
When I run this I get:
Server: Msg 8164, Level 16, State 1, Procedure sp_GetFileNames, Line
53
An INSERT EXEC statement cannot be nested.
Because I use an INSERT EXEC to with the results from the @.Cmd.
Anybody have any ideas how I can get that information into a table?
I did try to just copy the data to c:\temp\dir.txt and then bulk
import it in. But when it runs the @.Cmd to create the file it comes
back with a NULL value and my stored procedure returns two sets of
values... which I can't do.
So, I would appreciate anybody who can help.
Thanks!
-utahWhat I have done in the past is create a global temp table (##Files) and
then in the called procedure(sp_GetFileNames) insert into the global temp
table directly.
<Utahduck@.hotmail.comwrote in message
news:1172623203.275737.283660@.v33g2000cwv.googlegr oups.com...
Quote:
Originally Posted by
>I do a lot of file processing and I usually run a little script I copy
and paste to read directory information to see if a new file it there
and then process the file if it is. So, I decided to wise up and make
a stored procedure to automate a lot of that.
>
The pivotal step in this is that i run a command that looks like:
>
CREATE TABLE #DIR (FileName varchar(100))
>
DECLARE @.Cmd varchar(1050)
SET @.Cmd = 'DIR "' + @.Path + CASE WHEN RIGHT(@.Path, 1) = '\' THEN ''
ELSE '\' END + @.WildCard + '"'
>
INSERT INTO #DIR
EXEC master..xp_CmdShell @.Cmd
>
When I run the stored procedure I get back the files and folders in
there that match the wildcard and all is good!!!!
>
...Until I try to put that information into a table while calling that
stored procedure:
>
CREATE TABLE #Files (
Path varchar(100),
FileName varchar(100),
PathAndFileName varchar(150),
FileDateTime SmallDateTime,
FileLength int,
FileType Varchar(10))
>
INSERT INTO #Files
EXEC sp_GetFileNames @.Path = '\\isoft2\ftp\Legacy\Billing\', @.Wildcard
= '*.txt'
>
When I run this I get:
>
Server: Msg 8164, Level 16, State 1, Procedure sp_GetFileNames, Line
53
An INSERT EXEC statement cannot be nested.
>
Because I use an INSERT EXEC to with the results from the @.Cmd.
>
Anybody have any ideas how I can get that information into a table?
>
I did try to just copy the data to c:\temp\dir.txt and then bulk
import it in. But when it runs the @.Cmd to create the file it comes
back with a NULL value and my stored procedure returns two sets of
values... which I can't do.
>
So, I would appreciate anybody who can help.
>
Thanks!
>
-utah
>
CREATE TABLE #DIR (FileName varchar(100))
Quote:
Originally Posted by
>
DECLARE @.Cmd varchar(1050)
SET @.Cmd = 'DIR "' + @.Path + CASE WHEN RIGHT(@.Path, 1) = '\' THEN ''
ELSE '\' END + @.WildCard + '"'
>
INSERT INTO #DIR
EXEC master..xp_CmdShell @.Cmd
INSERT INTO myTABLE
SELECT filename FROM #DIR
would that not do the job?
--
Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=http://www.itjobfeed.com">UK IT Jobs</a>
<Utahduck@.hotmail.comwrote in message
news:1172623203.275737.283660@.v33g2000cwv.googlegr oups.com...
Quote:
Originally Posted by
>I do a lot of file processing and I usually run a little script I copy
and paste to read directory information to see if a new file it there
and then process the file if it is. So, I decided to wise up and make
a stored procedure to automate a lot of that.
>
The pivotal step in this is that i run a command that looks like:
>
CREATE TABLE #DIR (FileName varchar(100))
>
DECLARE @.Cmd varchar(1050)
SET @.Cmd = 'DIR "' + @.Path + CASE WHEN RIGHT(@.Path, 1) = '\' THEN ''
ELSE '\' END + @.WildCard + '"'
>
INSERT INTO #DIR
EXEC master..xp_CmdShell @.Cmd
>
When I run the stored procedure I get back the files and folders in
there that match the wildcard and all is good!!!!
>
...Until I try to put that information into a table while calling that
stored procedure:
>
CREATE TABLE #Files (
Path varchar(100),
FileName varchar(100),
PathAndFileName varchar(150),
FileDateTime SmallDateTime,
FileLength int,
FileType Varchar(10))
>
INSERT INTO #Files
EXEC sp_GetFileNames @.Path = '\\isoft2\ftp\Legacy\Billing\', @.Wildcard
= '*.txt'
>
When I run this I get:
>
Server: Msg 8164, Level 16, State 1, Procedure sp_GetFileNames, Line
53
An INSERT EXEC statement cannot be nested.
>
Because I use an INSERT EXEC to with the results from the @.Cmd.
>
Anybody have any ideas how I can get that information into a table?
>
I did try to just copy the data to c:\temp\dir.txt and then bulk
import it in. But when it runs the @.Cmd to create the file it comes
back with a NULL value and my stored procedure returns two sets of
values... which I can't do.
>
So, I would appreciate anybody who can help.
>
Thanks!
>
-utah
>
You should paste your stored procedure. One thing, how are you getting from
a one column table (#Dir) to a multiple column table (#files) based upon
your insert? You are going to have to do some parsing to get all this info
into multiple columns.
-- Bill
<Utahduck@.hotmail.comwrote in message
news:1172623203.275737.283660@.v33g2000cwv.googlegr oups.com...
Quote:
Originally Posted by
>I do a lot of file processing and I usually run a little script I copy
and paste to read directory information to see if a new file it there
and then process the file if it is. So, I decided to wise up and make
a stored procedure to automate a lot of that.
>
The pivotal step in this is that i run a command that looks like:
>
CREATE TABLE #DIR (FileName varchar(100))
>
DECLARE @.Cmd varchar(1050)
SET @.Cmd = 'DIR "' + @.Path + CASE WHEN RIGHT(@.Path, 1) = '\' THEN ''
ELSE '\' END + @.WildCard + '"'
>
INSERT INTO #DIR
EXEC master..xp_CmdShell @.Cmd
>
When I run the stored procedure I get back the files and folders in
there that match the wildcard and all is good!!!!
>
...Until I try to put that information into a table while calling that
stored procedure:
>
CREATE TABLE #Files (
Path varchar(100),
FileName varchar(100),
PathAndFileName varchar(150),
FileDateTime SmallDateTime,
FileLength int,
FileType Varchar(10))
>
INSERT INTO #Files
EXEC sp_GetFileNames @.Path = '\\isoft2\ftp\Legacy\Billing\', @.Wildcard
= '*.txt'
>
When I run this I get:
>
Server: Msg 8164, Level 16, State 1, Procedure sp_GetFileNames, Line
53
An INSERT EXEC statement cannot be nested.
>
Because I use an INSERT EXEC to with the results from the @.Cmd.
>
Anybody have any ideas how I can get that information into a table?
>
I did try to just copy the data to c:\temp\dir.txt and then bulk
import it in. But when it runs the @.Cmd to create the file it comes
back with a NULL value and my stored procedure returns two sets of
values... which I can't do.
>
So, I would appreciate anybody who can help.
>
Thanks!
>
-utah
>
Quote:
Originally Posted by
INSERT INTO #Files
EXEC sp_GetFileNames @.Path = '\\isoft2\ftp\Legacy\Billing\', @.Wildcard
>= '*.txt'
Note that the sp_ prefix is reserved for system procedures, and SQL Server
will first look for these in the master database. Do not use it for your
own code.
Quote:
Originally Posted by
When I run this I get:
>
Server: Msg 8164, Level 16, State 1, Procedure sp_GetFileNames, Line
53
An INSERT EXEC statement cannot be nested.
>
Because I use an INSERT EXEC to with the results from the @.Cmd.
>
Anybody have any ideas how I can get that information into a table?
I have an article on my web site that discusses a couple of alternatives:
http://www.sommarskog.se/share_data.html.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Wednesday, March 28, 2012
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
Subscribe to:
Posts (Atom)