Pusher and QueryPusher Pick Up Where CMS Leaves Off

Central Management Server (CMS) in SQL Server 2008 is a great tool that lets both T-SQL novices and experts execute T-SQL statements against a group of SQL Servers machines simultaneously and receive the results in a single result set. However, it has some limitations. CMS runs only on SQL Server 2008, and you need to register servers. Plus, you can't set up a SQL Server Agent job to run a script through CMS.

I created two utilities—Pusher and QueryPusher—that pick up where CMS leaves off. Pusher is used to push out code that creates objects or performs an action against a list of SQL Server machines. For example, you can use Pusher to run a script that creates a stored procedure. Pusher isn't designed to return results back from a query—for that, you need to use QueryPusher. With QueryPusher, you can execute complex queries against multiple servers. Unlike CMS, Pusher and QueryPusher work on SQL Server 2005 and don't require you to register servers. And you can use Pusher to execute a command that starts a SQL Server Agent job.

Using the Pusher Utility

To use the Pusher utility, follow these steps:

Using the QueryPusher Utility

QueryPusher uses both sqlcmd and xp_cmdshell (which returns rows as an nvarchar(255) column) to execute a query against multiple servers, after which it concatenates the results. Thus, records that have more than 255 characters will wrap to the next row. For this reason, you need to determine the character width of your result set before you run the utility.

QueryPusher executes either the ResultsFilter or ResultsFilter256 stored procedure, depending on the result set's character width. As Table 1 shows, one main difference between the stored procedures is that ResultsFilter assumes one result per record so it can filter the results using a set-based operation, whereas ResultsFilter256 examines one record at a time to find the end of each record.

Let's walk through an example of how to use the Pusher utility to determine the character width of a result set:

When determining the width of a result set, you must account for the space between columns. So, if a record has three columns, you need to add two additional characters to the width. An easy way to calculate how many extra characters to add is number of columns - 1.

Let's examine the WrappingText.sql results, which contain three columns. The first returned record was 253 characters. When you add two more characters for spacing, you have a total of 255 characters (253 + 2). The second returned record was 254 characters, so you have a total of 256 characters (254 + 2). The third returned record was 255 characters, so you have a total of 257 characters (255 + 2). Because the second and third records are more than 255 characters wide, you need to use ResultsFilter256.

Before you use QueryPusher, you need to create the ResultsFilter and ResultsFilter256 stored procedures in the msdb database. To do so, execute ResultsFilter.sql and ResultsFilter256.sql (which you can find in 102922.zip) on the SQL Server machine from which you'll be running QueryPusher. Take the time to review these stored procedures and update the DELETE #RawResults sections as needed. If you prefer to create the stored procedures in a different database, you'll need to revise ResultsFilter.sql, ResultsFilter256.sql, and QueryPusher's call to the procedures.

After you've determined the character width of your result set and created the stored procedures, follow these steps to use QueryPusher:

Examples of Using QueryPusher

Let's walk through several examples of how to use QueryPusher. These examples not only demonstrate how to use QueryPusher, but they also provide templates that you can use for various types of queries. I'll refer to a query that produces a result set that's more than 255 characters as a wide query and a query that produces a result set that's 255 or fewer characters as a narrow query. You can find all the templates and scripts being discussed in 102922.zip.

QueryPusher_Narrow. In this example, QueryPusher_Narrow executes a narrow query (NarrowQuery.sql) that retrieves data from a list of servers.

4.      Execute QueryPusher_Narrow.sql and examine the results.

QueryPusher_Wide. In this example, QueryPusher_Wide executes a wide query (WideQuery.sql) that retrieves data from a list of servers.

4.     Execute QueryPusher_Wide.sql and examine the results.

QueryPusher_Multi. In this example, QueryPusher_Multi executes multiple queries (NarrowQuery.sql and WideQuery.sql) that retrieve data from a list of servers.

4.     Execute QueryPusher_Multi.sql and examine the results.

QueryPusher_PassingVariable. In this example, QueryPusher_PassingVariable provides an example of how to configure QueryPusher to pass in a variable to a narrow query (PassingVariable.sql), then run that query.

4.     Execute QueryPusher_PassingVariable.sql and examine the results.

QueryPusher_Email. In this example, QueryPusher_Email provides an example of how to use QueryPusher for complex queries and related tasks. QueryPusher_Email passes in variables to multiple queries (MainReplQuery2.sql, TokenLatency2.sql, UndistributedCommands2.sql), then runs those queries. It then combines some result sets and emails you the results. To run the code for this example, you must have replication and Database Mail set up. (Note that the queries and QueryPusher_Email code were designed to be executed against a remote distributor, so they might appear to be more complex than necessary.)

4.      Execute QueryPusher_Email.sql and examine the results and your email inbox.

QueryPusher_CSV. QueryPusher_CSV is similar to QueryPusher_Multi, except QueryPusher_CSV sends the results as an attachment in an email message. To follow this example, you must have Database Mail set up.

4.      Execute QueryPusher_CSV and examine the results and your email inbox. If opened with Excel, each column will be in its own cell.

QueryPusher_Databases. Typically queries include the names of the databases being queried, so you don't need to pass any database names to the sqlcmd command. However, there might be special cases when this would be helpful. For example, if you have a test server with multiple databases that share a common schema, the easiest way to query all the databases on that server would be to write a generic query that doesn't contain the database names and instead pass in the database names to the sqlcmd command. QueryPusher_Databases is a modified version of QueryPusher_Narrow that incorporates the database names in the INSERT @Servers statement and sqlcmd command.

4.     Execute QueryPusher_Narrow.sql and examine the results.

Be Pushy

Now that you know how to use the Pusher and QueryPusher utilities, you're on your way to pushing out objects and queries against all the servers you desire. The Pusher and QueryPusher utilities use the Sqlcmd command-line utility, so the scripts work on SQL Server 2008 and SQL Server 2005. If you're still using SQL Server 2000, you could modify the scripts to use the Isql or Osql command-line utility instead.