And what happen if there are an error in the script #5, all the previous script already were executed. 

There are any way to do this in a transaction?

My same concern.  Our scripts will sometimes have errors due to dependencies, etc.  The errors are not always critical and require rollback; however, we would like to make sure the error condition is reported and the output is preserved, just in case...

Maybe this could be the next part of this article. :)

I use the script below to create a new database:

sqlcmd -S srv-tsbd\sql2k -i D:\Scripts\create_db_2k.sql -o D:\Scripts\Logs\LOG_create_db_2k.txt

Is it right? It creates...

Now I'm trying to create the tables with some scripts that I have.

The scripts for creating tables are in different places, and they CAN'T be moved. For example:

The CREATE TABLE a is on Folder D:\Scripts\Create\A

The CREATE TABLE b is on Folder D:\Scripts\Create\B

How do I do?

You could try a small utility

https://scriptzrunner.codeplex.com/

Regarding the concern about rollback. What you could do is include BEGIN/COMMIT TRAN in each script. This will give you rollback granualirity at each piece of the process. You cannot do nested transactions (See Paul Randal's post on the matter: http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(2630)-nested-transactions-are-real.aspx). This tip was meant as general overview/example of using sqlcmd, not thorough breakdown of its uses, which I think the author did good job of showing.

https://scriptzrunner.codeplex.com/

I would like to know if the commandline parameters are available in order to make a automated run. If not, can you publish the source code, I will code it up for you.

I am using Create Database command as below

sqlcmd -E -S%ServerName% -Q "Create Database HUM" ,,,but problem is its creating database on default path c:\MSSQL\MSSQLinstance\Data..... i want that to be in N:\SQLLog and N:\SQLData ...how it will be done pl. help me///

I followed the example above and was able to create the database, schema, logins and users but when I call the command to create the tables I got an error, its seems it is point to the database master instead of the new database that I created.

Kindly give me an advise on how to deal with this problem.

The create table script went to master database instead of the newly created db. Any suggestions on this please?

In the SQLCMD command , change the -d parameter to point to

the database you want. I recommend you read up

on SQLCMD in the books online.

Better yet, after the database is created, add a USE statement to set

the database context. So you change the -d but the

Db won't exist yet. Experiment.

Thanks for pointing this out.  I create a DB as part of unit test setup and instead of calling SqlCmd.exe 200+ times for each file, I'm able to call it just once, greatly speeding things up.

Regarding each file, how can one stop the rest of the files from executing if one fails?

lets say that 'create_table'failed, how can i stop the rest from executing

:On Error exit

:r c:\Scripts\CREATE_TABLES.sql
:r c:\Scripts\TABLE_INSERTS.sql
:r c:\Scripts\CREATE_INDEXES.sql
:r c:\Scripts\CREATE_PROCEDURES.sql

PRINT 'DATABASE CREATE IS COMPLETE'
GO

Have you considered using %ERRORLEVEL% to test for a error code return?

In Books Online for SQLCMD, under the "SQLCMD Commands" section, it says:

"Commands are executed immediately. They are not put in the execution buffer as statements are."

Doesn't this mean that the .sql files are all being executed in parallel?  How is it that SQLCMD knows to execute CREATE_TABLES.sql before executing TABLE_INSERTS.sql?

Perhaps my confusion is rooted in the definition of "execution buffer."  Could somebody please assist?

Thanks,
Eric

I am in a situation of extracting data from a database, however, from a single database. I wrote a simple batch script using a for loop to execute all the .sql files in the folder and write the output to a .txt file. The script is doing the job for me. The only problem i observe here is the time it is taking do the job. I have almost 120 queries that needs to be queried for and write the output to external files. The batch file is taking almost 90-100 minutes. I am looking for an alternative to do my job in much faster way. Can someone help me how can i achieve this in much faster way...