Using Linked Server to store data

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

• Jacob H: To start, Adam Mechanic's stored procedure is sp_whoisactive unless you've renamed it. Second, as @filoo noted, you're going to have issues with XML and distributed queries, so it probably won't work. I think you'll need to run the sproc locally on your MetricDB server and insert the results "remotely".
• Gilliam: sp_whoisactive is the SP and this is called within the stored proc.If you look at Brents link it basically outputs to a table but I want it to output to another server on a table using Linked server.

Answer 1

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.

• Vérace: Maybe a bit short for an answer? Could you expand on it? Maybe provide an example using a dbfiddle - see my profile for articles on answering questions. Also, you should use the formatting icons above the edit window to make your posts clearer. p.s. welcome to the forum!
• Gilliam: EXEC MetricDB.Baseline.dbo.sp_WhoIsActive - Don't think it works this way either unfortunately. If you try refenrece linked server then it complains about the name being longer than 2 where you normally have database.schema.table name obviously this has the linked server name too which gives the maximum prefix error