Dashboard > Documentation for Metrix Developers and Consultants > ... > OSQL > How to execute a .SQL script using OSQL
Documentation for Metrix Developers and Consultants Log In   View a printable version of the current page.
How to execute a .SQL script using OSQL
Added by Marquetta Drakes, last edited by Marquetta Drakes on Jul 27, 2009  (view change)
Labels: 

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

Switches
-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

How to attach a Metrix database using OSQL (Documentation for Metrix Developers and Consultants)
How to detach a Metrix database using OSQL (Documentation for Metrix Developers and Consultants)

Kept getting error

"Cannot open input file - C:SampleData.sql
No such file or directory"

So i added the "\" to make it "C:\SampleData.sql" and it worked.

Posted by Anthony Hernandez at Aug 16, 2007 12:19 | Permalink

I believe you will always need to use the backslash to make a fully qualified DOS path, i.e., always use "C:\" instead of just "C:".

This page should probably be updated to reflect the correct syntax.

Posted by A. Mitchell at Aug 22, 2007 06:23 | Permalink

If the path to your .sql file contains spaces, enclose the entire path in quotation marks, otherwise, the script won't execute.

Ex. "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.

Posted by Anthony Hernandez at Oct 21, 2008 16:18 | Permalink
Site powered by a free Open Source Project / Non-profit License (more) of Confluence - the Enterprise wiki.
Learn more or evaluate Confluence for your organisation.
Powered by Atlassian Confluence, the Enterprise Wiki. (Version: 2.1.1 Build:#406 Dec 23, 2005) - Bug/feature request - Contact Administrators