Using Linked Server to store data

Posted on

Question :

I have a linked server setup on my source production server. I also have a secondary server which is the linked server. What I was looking to do is to execute a stroed proc and log connections to the linked server. However I seem to be having issues.

The object name ‘MetricDB.Baseline.dbo.WhoIsActive’ contains more than
the maximum number of prefixes. The maximum is 2.

Metric DB is the destination server which is configured on the source server as a linked server.

I been using Adam’s sp which was described by Brent here:
https://www.brentozar.com/responder/log-sp_whoisactive-to-a-table/

Due to space issues on my production DB I wanted to log it all to another server. Has anyone done this previously? or know if I’m missing something?

Thanks

Answer :

Why wouldn’t you try

EXEC MetricDB.Baseline.dbo.sp_WhoIsActive

Indeed, the name of the stored procedure is sp_whoisactive, not whoisactive, unless it is renamed, of course.
It could work…
Though it brings distributed query issue with XML for me.

Leave a Reply

Your email address will not be published.