articles >> general dba >> Manage Multiple SQL Server Installations and Databases ...

Manage Multiple SQL Server Installations and Databases with OSQL

By : Randy Dyess May 24, 2002

Printer friendly

At one point in time I was tasked with running over 70 databases on more than a dozen servers without any of the costly third-party tools that exist to accomplish such tasks, and I often get asked how can you handle such large numbers of databases. This is a short article that will show you how to use the handy OSQL command line utility, provided with SQL Server, to execute repetitive tasks against large numbers of databases or to manage large numbers of servers.

The OSQL command line utility allows you to execute Transact-SQL statements, system procedures, and script files against multiple servers and databases by using ODBC connections to those servers and databases. The syntax of the OSQL utility allows you to specify which server, database, login, password, input file, output file, as well as formatting functions you want to run with your SQL script. By creating a small BAT file containing this information for the servers and database you manage and creating a SQL script file, you can easily perform repetitive SQL tasks with one or two mouse clicks.

 

OSQL Syntax

OSQL
    [-?] |
    [-L] |
    [
        {
            {-U login_id [-P password]}
            | -E
        }
        [-S server_name[\instance_name]] [-H wksta_name] [-d db_name]
        [-l time_out] [-t time_out] [-h headers]
        [-s col_separator] [-w column_width] [-a packet_size]
        [-e] [-I] [-D data_source_name]
        [-c cmd_end] [-q "query"] [-Q "query"]
        [-n] [-m error_level] [-r {0 | 1}]
        [-i input_file] [-o output_file] [-p]
        [-b] [-u] [-R] [-O]
    ]

OSQL Parameters

-?	used to display the syntax for OSQL switches.
-L	will list the locally configures servers and the names of the servers broadcasting on your network.
-U	login_id is the user login to use for the connection to the server and database and is case sensitive.
-P	password is the password for the login_id specified. If -P is not used then OSQL will prompt for a password, and if -P is not specified then OSQL will use a NULL value as the password. -P is case sensitive. Another option is to set a password for OSQL by using the OSQLPASSWORD environment variable at the command line. C:\>SET OSQLPASSWORD=password. This will allow you to not specify the password and cause OSQL to check for the environment variable before it uses the NULL value which allows you to keep from having to hard code the password into the batch file.
-E	will cause OSQL to use a trusted connection.
-S	server_name[\instance_name] will specify the server and in the case of SQL Server 2000 instances the SQL Server instance you want to connect to. Connecting to just the server name will cause OSQL to try to connect to the default instance of SQL Server.
-H	wksta_name is a workstation name that will be stored in the sysprocesses system table with a default of the current computer name.
-d	db_name specifies which database to use for the SQL statement.
-l	time_out specifies the number of seconds to wait before OSQL login times out with a default of 8 seconds.
-t	time_out specifies the number of seconds before a command times out with a default of never.
-h	headers specifies the number of rows to print between column headings with a default of all rows after the column headings. A -1 will specify that no headers be printed: note that if -1 is used do not include a space between -h and -1 (-h-1).
-s	col_separator will specify the column-separator character with a default of a blank space. To use characters that have special meaning to the operating system (| ; & < >), enclose the character in double quotation marks (").
-w	column_width will allow you to set the screen width for output with a default of 80 characters. When an output line has reached its maximum screen width, it is broken into multiple lines.
-a	packet_size will allow you to request a different-sized packet with a default size of the server default. Valid values are 512 through 65535. Increased packet size can enhance performance on larger script execution where the amount of SQL statements between GO commands is substantial.
-e	will echo the input into the output file.
-I	will set the QUOTED_IDENTIFIER connection option on.
-D	data_source_name will connect to an ODBC data source that is defined using the ODBC driver for Microsoft SQL Server and will use the options specified in the data source. This option does not work with data sources defined for other drivers.
-c	cmd_end will specify the command terminator.
-q	"query" will execute a query when OSQL is started but will not exit OSQL when the query completes. You can use %variables, or environment %variables% in a batch file by setting the variable beforehand. SET table = sysobjects OSQL /q "Select * from %table%" Make sure you use double quotation marks around the query and single quotation marks around anything embedded in the query.
-Q	"query" will execute a query and exit OSQL. Like -q use double quotation marks around the query and single quotation marks around anything embedded in the query.
-n	will remove the numbering and the prompt symbol (>) from input lines. I use this parameter to keep anything from being written to an output file if no errors occurs, so all I have to do is look for the output files to be 0 in size to know the statement completed correctly.
-m	error_level will customize the display of error messages. The message number, state, and error level are displayed for errors of the specified severity level or higher. Nothing is displayed for errors of levels lower than the specified level. Use -1 to specify that all headers are returned with messages, even informational messages. If using -1, there must be no space between the parameter and the setting (-m-1, not -m -1).
-r {0 | 1}	will redirect the message output to the screen. If a parameter is not specified or if 0 is specified then only error messages with a severity level 11 or higher are redirected. If you specify 1, all message output (including "print") is redirected.
-i	input_file will identify the file that contains a batch of SQL statements or stored procedures. The less than (<) comparison operator can be used in place of -i.
-o	output_file will identify the file that receives output from OSQL. The greater than (>) comparison operator can be used in place of -o.
-p	will print performance statistics.
-b	will specifies that OSQL exits and returns a DOS ERRORLEVEL value when an error occurs. The value returned to the DOS ERRORLEVEL variable is 1 when the SQL Server error message has a severity of 10 or greater; otherwise, the value returned is 0.
-u	will specify that output_file is stored in Unicode format, regardless of the format of the input_file.
-R	will specify that the SQL Server ODBC driver use client settings when converting currency, date, and time data to character data.
-O	will specify that certain OSQL features be deactivated to match the behavior of earlier versions of ISQL. These features are: --EOF batch processing --Automatic console width scaling --Wide messages -O will also set the default DOS ERRORLEVEL value to -1.

 

Putting It All Together

Using OSQL can be as made as fancy as you want it to be, but I find that the creation of two simple files will allow me to accomplish most the tasks I've needed to in a quick manner against numerous databases.

The first thing you need to do is to create your batch file or files based on the database groupings you want to run statement against. Using a simple text editor, create the file with your parameters and save the file with a .bat extension. You can create this batch file to use a text file as the input file which will keep you from having to adjust parameters each time you want to run the batch process. A sample of what I do can be found in the following example:

OSQL -Usa -Ppassword -Sserver1 -dDatabase1 -n 
-iC:\OSQL_SCRIPTS\SQL_SCRIPTS\sqlscript.sql 
-oC:\OSQL_SCRIPTS\OUTPUT\OSQLoutput_db1.txt

OSQL -Usa -Ppassword -Sserver2 -dDatabase2 -n 
-iC:\OSQL_SCRIPTS\SQL_SCRIPTS\sqlscript.sql 
-oC:\OSQL_SCRIPTS\OUTPUT\OSQLoutput_db2.txt

What I usually do is create and test the SQL script in Query Analyzer and then save that script to a file named sqlscript.sql (I actually have several files with different names for different groupings of databases).

When you execute the .bat file, it will start up OSQL, connect to each defined server, execute a USE statement for the database defined, and execute the SQL command contained in the SQL script file creating a separate output file for each connection. Learning to format the output to a form that is readable takes some practice, but I usually use OSQL to find the location of the data I am interested in and then run the query in Query Analyzer so I can format the data in a quick and easy manner.

 

Summary

Taking the time to create OSQL bat files for groups of servers and/or databases and using these files to manage your environment for repetitive tasks will pay off as you find yourself only needing a few minutes to create a database object or run a query against dozens of databases located on dozens of servers. So keep asking for the expensive toys some think they need to mange multiple databases, but until your company agrees to your request, save yourself a lot of time and frustration by figuring out how you can use OSQL to manage your environment.

 

Copyright 2002 by Randy Dyess, All rights Reserved TransactSQL.Com

---

Comments: