CODEWITHSUNDEEP
sql-server-2008-r2sql-server-express
redcalx
redcalx

Reputation: 8657

Why does sqlcmd fail when called multiple times from a bat?

I'm importing some data into SQL Server 2008 R2 using a bat file that does something like:

sqlcmd -E -S fooserver -d fooDB -e -i foo.sql -o foo.output
sqlcmd -E -S fooserver -d fooDB -e -i bar.sql -o bar.output
sqlcmd -E -S fooserver -d fooDB -e -i baz.sql -o baz.output
sqlcmd -E -S fooserver -d fooDB -e -i qux.sql -o qux.output

When I point to a developer edition instance all works fine. If however I point to an SQL Express instance, the first couple or so of commands run OK, and the next command fails with an authentication error:

Login failed for user 'MYDOMAIN\FOOUSER'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]

If however I run the above commands one at a time then they all run with no errors.

Also noteworthy is that the database being targeted briefly goes into recovery mode ('In Recovery' is seen adjacent to the database in SQL Server Management Console), hence it appears that the login fails because the target DB is briefly in recovery mode. I will re-iterate that this problem does not occur on developer edition instances.

Same problem observed against SQL Express 2008 R2 RTM and SP2 (haven't tested SP1 yet).

Upvotes: 1

Views: 390

Answers (1)

redcalx
redcalx

Reputation: 8657

For anyone arriving at this question looking for a solution - it turned out to be caused by the database 'Auto Close' option on the database (MyDatabase->Properties->Options in SQL Server management Console). On the Developer edition instance the default appears to be 'false', whereas the SQL Express instances default to 'true'.

The sqlcmd that failed for me followed one that inserted a lot of data, and that presumably caused enough work for the auto close logic for it to overlap with the next sqlcmd.

Upvotes: 2

Related Questions