Pusher and QueryPusher Pick Up Where CMS Leaves Off
With the Pusher and QueryPusher utilities, you can execute queries, create objects, and perform actions against a list of SQL Server machines without the need for SQL Server 2008's Central Management Server (CMS).
August 25, 2010
![Pusher and QueryPusher Pick Up Where CMS Leaves Off Pusher and QueryPusher Pick Up Where CMS Leaves Off](https://eu-images.contentstack.com/v3/assets/blt07f68461ccd75245/blt8515c924433685c0/6619492b6c5e9d316b3042f2/data-595x335_0_5.jpg?width=1280&auto=webp&quality=95&format=jpg&disable=upscale)
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:
Download Pusher and QueryPusher by clicking the Download the Code Here button near the top of the page. The 102922.zip file contains the utilities as well as some sample scripts you can use to test them out.
Save the code that creates an object or performs an action in a script. Place the script on the SQL Server machine you want to run it from. The script must be accessible by the SQL Server service. For demonstration purposes, let's say you want to use Pusher to run the PushedObject.sql script that's in the C:dpg directory on Server1. (You can find PushedObject.sql in 102922.zip. This script creates TestSproc, a stored procedure that returns the current date and time.)
Log on to Server1 and copy the Pusher code into a new query window in SQL Server Management Studio (SSMS).
Find the code in Listing 1. In the code at callout A, you need to specify whether you want to use the Preview mode (set the @choose variable to 'P') or Execute mode (set the @choose variable to 'E'). In Preview mode, Pusher displays the code to be executed but doesn't run it. In Execute mode, Pusher runs the code. You should always preview the code before running it, so set the @choose variable to 'P'.
In the code at callout B, set @file to the fully qualified pathname of the script you want to run. (Leave in the letter N and the single quotes.)
In the code at callout C, modify the INSERT @Servers statement so that it specifies the servers you want the script to run against. (Leave in the letter N and the single quotes in each server name.) You can increase or decrease the number of servers as desired. No matter the number, the last SELECT statement should not include the UNION ALL clause.
Execute the Pusher utility.
If you're satisfied with the preview, set the @choose variable to 'E'.
Execute the Pusher utility again.
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:
Locate the WrappingText.sql script in 102922.zip.
Log on to a server and place the script somewhere accessible by the SQL Server service.
Open SSMS and copy the Pusher code into a new query window. Set the @file variable to the fully qualified pathname for WrappingText.sql. Set the @choose variable to 'P'. In the INSERT @Servers statement, replace the SELECT statements with
SELECT ISNULL(@@SERVERNAME,
CAST(SERVERPROPERTY(N'MachineName')
AS sysname))
Execute the Pusher utility.If you're satisfied with the preview, set the @choose variable to 'E'.
Execute the Pusher utility again.
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:
Save the query you want to run in a script. Place the script on the SQL Server machine you want to run it from. The script must be accessible by the SQL Server service.
If you're using ResultsFilter256, add N'||||' as the last column of your query.
Log on to the SQL Server machine where you placed the script and open QueryPusher in SSMS.
Find the line
SET @file = N'c:dpgQuery.sql'
and set @file to the fully qualified pathname of the script to be run. (Leave in the letter N and the single quotes.)Find the line
SET @bcpfile = N'c:dpg' +
REPLACE(CAST(RAND()
AS sysname), N'.', N'') + N'.txt'
and set @bcpfile to a directory accessible by the SQL Server service.Find the code in Listing 2. Modify the columns' data types to reflect the schema you're expecting back from your query. You need to use character data types because the results will be returned as a string.
In the code in Listing 3, modify the INSERT @Servers statement so that it specifies the servers you want the script to run against. (Leave in the letter N and the single quotes in each server name.) You can increase or decrease the number of servers as desired. No matter the number, the last SELECT statement should not include the UNION ALL clause.
Execute the QueryPusher utility. Note that any NULLs returned from your query will appear as the string 'NULL' in the results. Both ResultsFilter and ResultsFilter256 will issue a return code of 3 if a remote server can't be reached and a return code of 2 if no records are returned.
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.
Locate NarrowQuery.sql and QueryPusher_Narrow.sql in the Example1 folder.
Log on to a server and place NarrowQuery.sql somewhere accessible by the SQL Server service.
Open QueryPusher_Narrow.sql in SSMS and
Set @file to the location of NarrowQuery.sql.
Set @bcpfile to a directory accessible by the SQL Server service.
Update the INSERT @Servers statement with the target 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.
Locate WideQuery.sql and QueryPusher_Wide.sql in the Example2 folder.
Log on to a server and place WideQuery.sql somewhere accessible by the SQL Server service.
Open QueryPusher_Wide.sql in SSMS and
Set @file to the location of WideQuery.sql.
Set @bcpfile to a directory accessible by the SQL Server service.
Update the INSERT @Servers statement with the target 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.
Locate NarrowQuery.sql in the Example1 folder, WideQuery.sql in the Example2 folder, and QueryPusher_Multi.sql in the Example3 folder.
Log on to a server and place NarrowQuery.sql and WideQuery.sql somewhere accessible by the SQL Server service.
Open QueryPusher_Multi.sql in SSMS and
Set @bcpfile to a directory accessible by the SQL Server service.
Update the INSERT @Servers statement with the target servers.
Update the INSERT @Files statement to the locations of NarrowQuery.sql and WideQuery.sql.
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.
Locate PassingVariable.sql and QueryPusher_PassingVariable.sql in the Example4 folder.
Log on to a server and place PassingVariable.sql somewhere accessible by the SQL Server service.
Open QueryPusher_PassingVariable.sql in SSMS and
Set @file to the location of PassingVariable.sql.
Set @bcpfile to a directory accessible by the SQL Server service.
Update the INSERT @Servers statement with the target servers.
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.)
Locate the MainReplQuery2.sql, TokenLatency2.sql, UndistributedCommands2.sql, and QueryPusher_Email.sql in the Example5 folder.
Log on to a server and place MainReplQuery2.sql, TokenLatency2.sql, and UndistributedCommands2.sql somewhere accessible by the SQL Server service.
Open QueryPusher_Email.sql in SSMS and
Set @bcpfile to a directory accessible by the SQL Server service.
Update the INSERT @Servers statement with the target servers.
Update the INSERT @Files statement with the locations of MainReplQuery2.sql, TokenLatency2.sql, and UndistributedCommands2.sql.
Set @profile_name to an appropriate profile.
Set @recipients and @copy_recipients to the appropriate email addresses.
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.
Locate NarrowQuery.sql in the Example1 folder, WideQuery.sql in the Example2 folder, and QueryPusher_CSV.sql in the Example6 folder.
Log on to a server and place NarrowQuery.sql and WideQuery.sql somewhere accessible by the SQL Server service.
Open QueryPusher_CSV in SSMS and
Set @bcpfile to a directory accessible by the SQL Server service.
Update the INSERT @Servers statement with the target servers.
Update the INSERT @Files statement with the locations of NarrowQuery.sql and WideQuery.sql.
Set @cmd to an appropriate directory.
Set @profile_name to an appropriate profile.
Set @recipients and @copy_recipients to the appropriate email addresses.
Set @file_attachments to the fully qualified pathname of the .csv file.
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.
Locate NarrowQuery.sql in the Example1 folder and QueryPusher_Databases in the Example7 folder.
Log on to a server and place NarrowQuery.sql somewhere accessible by the SQL Server service.
Open QueryPusher_Databases.sql in SSMS and
Set @file to the location of NarrowQuery.sql.
Set @bcpfile to a directory accessible by the SQL Server service.
Update the INSERT @Servers statement with the target servers and databases.
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.
About the Author
You May Also Like