Thanks guys for your update.

PTackling paramter sniffing by using Option Recompile to recompile the proc everytime could be a costly operation. Any other underlysing advise?

Ok- so escape from generating a new snapshot. For large number of articles, that process is not effective.

I wrote this procedure (q2). When I run q1, I get the error :

Msg 11529, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be...

Can I not store the results in a variable? say, @retval

How to write an alternate script against [sys.sp_testlinkedserver]

When I pass the above result set to this proc ..
it throws error:

Msg 11520, Level 16, State 1, Procedure...

Still the same error. I think one quote at the end is missing.

Yep - thanks

Hw to find who owns the End point?

If the linked server connectivity does not work between the primary and stand by servers, how to send an email (dbmail) to the team with the alert so that DBAs...

Thanks Perry!

Thanks Mike...

Can I use something like this? But this is giving me error -

Message
Executed as user: . Msg 105, Level 15, State 1, Server , Line 1 ...

Hi Luis

I thought to copy the .sql o/p and running it manually. When I do that I found that the extract through sqlcmd did not work properly. Many...

If I copy and paste the entire sql to ssms and run it, it just runs without having any error