Reputation: 49
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.
Upvotes: 1
Views: 5855
Reputation: 294317
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.
Upvotes: 1