Using SQLCMD in SSMS to quickly check sync jobs

For some of us, an unfortunate side effect of using 3rd party applications to run different segments of your business, is the need to sync information between databases. Whether it be orders, employees, or something else, data needs to be synced between these systems to allow for each business segment to run.

As a DBA, running down issues in these sync processes can be very difficult with one SSMS query window looking in each system, constantly switching back and forth between results sets to look for differences.

Enter SQLCMD mode in SSMS.

Using SQLCMD mode inside your query allows you to open up a connection to a server, query a database and return a result from as many different servers as needed all within the same window.

In this example, I’ve created an Employee database on 3 different instances of SQL Server. Inside those DB’s, there is an Employee tables that hold all of my employees. At the top of the query, I’ve created a variable called “Emp” and set it to “Gail Huff” so it can be used across all the connections.

NOTE: In order to run this query, you must turn on SQLCMD mode by going to Query –> SQLCMD mode

   1: :SETVAR Emp "Gail Huff"

   2:  

   3: :CONNECT SQLDBA\SQL2008R2

   4:  

   5: USE EmployeeDB2008R2

   6: GO

   7: SELECT 'EmployeeDB2008R2' AS DB,* FROM Employee WHERE Employee.Name = '$(Emp)'

   8: GO

   9:  

  10: :CONNECT SQLDBA\SQL2012

  11:  

  12: USE EmployeeDB2012

  13: GO

  14: SELECT 'Employee2012' AS DB,* FROM Employee WHERE Employee.Name = '$(Emp)'

  15: GO

  16:  

  17: :CONNECT SQLDBA\SQL2014 

  18:  

  19: USE EmployeeDB2014

  20: GO

  21: SELECT 'Employee2014' AS DB,* FROM Employee WHERE Employee.Name = '$(Emp)'

  22: GO

Note the SETVAR and CONNECT keywords. Both do exactly what they sound like and allow SQLCMD mode to use variables and quickly connect to different SQL servers. After running the query, my result set is as follows and you can see that Gail has the same status across all servers.

Happy querying!