how to run multiple sqlcmd statements inside windows batch file after performing SQL authentication

Question

I am trying to run multiple SQLCMD statements (that archive tables in SQL Server 2008 and create restore scripts) from windows .bat file. My scripts work like first I perform SQL Authentication and then I input SQL scripts to output restore files

sqlcmd -S <Server name>\<instance> -U user
sqlcmd -i ArchiveTable1.sql -o RestoreTable1.sql
sqlcmd -i ArchiveTable2.sql -o RestoreTable2.sql
sqlcmd -i ArchiveTable3.sql -o RestoreTable3.sql

Problem is that after sqlcmd authentication, there appears prompt 1> and does not execute my next statements. On entering Quit, my next statements get executed but in output files I see authentication invalid error. I dont want to have sql authentication with each sqlcmd statement.

If there is issue with my approach, can somebody guide me to use other design.

Remus Rusanu · Accepted Answer · 2012-11-20 14:04:28Z

1

Three sqlcmd execution, three authentications:

sqlcmd -S <Server name>\<instance> -U user -i ArchiveTable1.sql -o RestoreTable1.sql
sqlcmd -S <Server name>\<instance> -U user -i ArchiveTable2.sql -o RestoreTable2.sql
sqlcmd -S <Server name>\<instance> -U user -i ArchiveTable3.sql -o RestoreTable3.sql

I dont want to have sql authentication with each sqlcmd statement

What you want matters little. You must do what is correct. If you have a justification for your unusual requirement, state the problem clearly, don't offer a solution and ask us to make it work.

If you want to avoid repeating user info and password in batches, and more importantly if you want to avoid leaving these passwords lying around in batches, use the sqlcmd environment variables SQLCMDUSER and SQLCMDPASSWORD:

The SQLCMDPASSWORD environment variable lets you set a default password for the current session. Therefore, passwords do not have to be hard-coded into batch files.

Simply set the SQLCMDUSER, SQLCMDPASSWORD (and maybe SQLCMDSERVER) and then invoke the batch. This way the batch does not need to contain any hard coded password.

edited Nov 20, 2012 at 14:04

answered Nov 20, 2012 at 12:23

Remus Rusanu

296k42 gold badges459 silver badges583 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

adbrpc Over a year ago

After each sqlcmd statement there is need to enter password. I wanted to skip this interactive password prompt as I dont have access to system where I will execute this batch file, all the time. So I cant oversee the execution process as I have around 30 sqlcmd statements in my batch file to execute.

Collectives™ on Stack Overflow

how to run multiple sqlcmd statements inside windows batch file after performing SQL authentication

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related