Forcing File Order In Batch Files

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Forcing File Order In Batch Files

Author

Topic

vgr.raju
Yak Posting Veteran

95 Posts

Posted - 2006-08-23 : 02:51:50

Hello Experts,
I am having difficulty in forcing the fetching order of the input sql files in my Batch File.
The command file picks up the files in the ascending order based on Names.This creates the Missing Dependency Errors.

The batch file script:
ECHO OFF
set ServerName=%1
set DatabaseName=%2
set WorkDirectory =%3

echo The ServerName used in applying this Build: %1
echo The DatabaseName used in applying this Build:%2
echo The directory used in the Build: %3

echo: The Build Start time %date% %time%

ECHO: Checking the existence of database: %2 in server: %1
Echo: If not exists creating the Database: %2 in Server: %1

set Query="IF NOT EXISTS(SELECT * FROM SYS.DATABASES WHERE NAME='%2') BEGIN CREATE DATABASE %2 END"
sqlcmd -S %1 -d Master -Q %Query%

echo: Checking the connection to the Server: %1 and Database: %2
set qry ="set nocount on"
sqlcmd -S %1 -d %2 -Q "%qry%"
IF %Errorlevel% NEQ 0 goto FailureExit:

Echo Applying the Scripts at Server: %1 and Database %2

for %%B in (%3\*.sql) do (sqlcmd -S %1 -d %2 -i %%B)
IF %Errorlevel% NEQ 0 goto FailureExit:

REM: for %%B in (%3\.\*.sql) do (sqlcmd -S %1 -d %2 -i %%B)
REM: IF %Errorlevel% NEQ 0 goto FailureExit:

REM: for %%B in (%3\.\*.sql) do (sqlcmd -S %1 -d %2 -i %%B)
REM: IF %Errorlevel% NEQ 0 goto FailureExit:

Echo The Build successfully applied at Server: %1 and Database: %2
Echo The Build completed Time %date% %Time%

:FailureExit echo The build Cancelled at Server: %1 and Database: %2

I would really helpful some one tells me how to force the fetching order of the SQL Input files?

Thanks in advance!
Raju

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-08-23 : 06:49:47

The only thing I can think of is to have your files named so that they are picked up in order. This is more a BATCH file question than SQL.
It is UGLY, but try and see if you name your files along the lines of:

001.CreateSchema.SQL

010.CreateTableParent.SQL
011.CreateTableChild.SQL

100.CreateSPA.sql
101.CreateSPB.sql

200.CreateFunctionX.sql

300.CreateFKConstraintParentChild.sql

400.CreateDefaultConstraintXXX.sql

500.CreateLoginAndUser.sql

600.CreateIndexTableParent1.sql
601.CreateIndexTableChild1.sql

etc. etc.

Don't know if it will work though...

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-08-23 : 06:56:24

Alternatively, have subdirectories, and process them in Order:

...\Deployment\DBCreate
...\Deployment\SchemaCreates
...\Deployment\ReferenceDataTables
...\Deployment\InstanceTables
...\Deployment\PKConstraints
...\Deployment\FKConstraints
...\Deployment\DefaultConstraints
...\Deployment\OtherConstraints
...\Deployment\Indexes
...\Deployment\StoredProcedures
...\Deployment\Functions
...\Deployment\LoginsAndUsers
...\Deployment\ReferenceData
...\Deployment\SQLServerAgentJobs

I think that should work. Some things though
- table creates must be without foreign key contraints inside the table definition - this removes the requirement for tables to be created in order. All FK contrainst are created, but as explicit 'alter table add constraints'
- your file must be able to handle empty directories, then you can habe a static, default file strucutre for all DB projects...

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

vgr.raju
Yak Posting Veteran

95 Posts

Posted - 2006-08-23 : 11:15:09

Hello,
Thanks for your quick response.I appended the sequence order with
the file name and it works fine.

Thanks!
Raju

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-08-23 : 11:37:40

Glad to hear it...

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources