SQL Server /DOS – Apply sqlcmd against files in a folder

Introduction

This is a simple straight forward script, but I keep thinking about it whenever I need it.  And, that is not good.

So hopefully I can keep it on wordpress and Google for it and land on it next time.

Script


echo sqlcmd -- /s -- SQL Server Instance Name
echo sqlcmd -- /d -- SQL Server Database Name
echo sqlcmd -- /E -- Trusted Connection
echo sqlcmd -- -b -- on error batch abort
echo sqlcmd -- -e -- echo input
echo sqlcmd -- -i -- File

for /f %%f in ('dir /b ..\Tables\*.sql /ON') do (

	echo Processing file : ..\Tables\%%f
	sqlcmd   /S "(local)\HRDBMIRROR" /d HumanResource -E -b -e -i ..\Tables\%%f

)	

Quick Explanations

Dir Command

  • /b :- To strip out extraneous text from directory command
  • ..\Tables :- Go up one directory and then back down to tables folder
  • /ON :- Order by file name.  Note that you can use /Od to order by date

Sqlcmd Command

  • /S :- Server name
  • Server name enclosed in Double-quotes in case we are dealing with a named instance
  • /d :- Database Name
  • -E :- Trusted Connection — No username/password embedded in Script
  • -b :- On Error Abort
  • -i :- Script File

Possible Errors

was unexpected at this time

If you had:

sqlcmd   /S (local)\HRDBMIRROR /d HumanResource -E -b -e -i ..\Tables\%%f

As we are going against a named instance; that is the MS SQL Server Instance name has a back-slash in it, please enclose the SQL Server Instance name in double-quotes

sqlcmd   /S "(local)\HRDBMIRROR" /d HumanResource -E -b -e -i ..\Tables\%%f

References

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s