Compare results from different database

Funnily enough, I had the same requirement recently. I blogged my approach here: http://thelonedba.wordpress.com/2012/06/21/warning-wordbreaker-filter-or-protocol-handler-used-by-catalog-foo-does-not-exist/

The gist of the process is:

1. Create temporary table (or two) to match the output of your stored procedure
2. execute the stored procedure into the temporary table. This was complicated for me by it being on a separate server...
3. Repeat, running the SP from the second server/database.
4. Do your comparison.

It's all relatively straightforward, just a bit long-winded. I'm sure I could have written the code better, for example!

This technique only works if there is one recordset output by your SP.

Things you'll need to know about:

• Adhoc Distributed Queries
• OPENROWSET - if you're hooking up to a second server
• INSERT INTO <tablename> EXEC <SPName>
• SET NOCOUNT ON - to suppress blank recordsets that just contain "57 rows affected"-type messages

Hope this helps.