You can use OSQL at a command prompt to execute the contents of a file using the following syntax:
osql -U YourUserName -P YourPassword -S ServerName -d DatabaseName -n-1 -i DriveLetter:SQLFileNameAndPath.sql -o DriveLetter:LogFile.txt
-U: login ID for the specified server
-P: password for the login ID
-S: server name
-d: database upon which the script will be executed
-n: removes numbering and the prompt symbol (>) from the output file
-i: the .SQL file name (including drive letter)
-o: an output file that details how the script executed (if at all)
Keep the following in mind:
- Include the instance along with your server name if applicable, e.g. MyServer\METRIX
- OSQL is case-sensitive in regards to its switches, e.g. -U is not the same as -u. If your code fails to run ensure that you have typed the commands correctly.
- When using the -n switch ensure there is no space between it and -1, e.g. "-n -1" will fail and "-n-1" will succeed.
- There is no need to create the output file before executing the script. It will be done automatically.
- If your script contains a "USE (YourDatabaseNameHere)" statement then there is no need to use the -d switch in the command line.
- If the path to your .sql file contains spaces, enclose the entire path in quotation marks. Otherwise, the script won't execute. For example, "C:\Program Files\fcny\Metrix\script.sql" (note the space between the words "Program" and "Files") will work whereas C:\Program Files\fcny\Metrix\script.sql will not.
Here are some examples:
- Local server using a trusted connection (replace the login and server information with the "-E" ): osql -E -n-1 -i C:\MySQLFile.sql -o C:MyExecutionLog.txt
- Local server and instance using user login and server information: osql -U JoeAdmin -P Joe -S MyServer\Metrix -n-1 -i C:\MySQLFile.sql -o C:MyExecutionLog.txt
Additional information can be found at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_mta_01_2q61.asp