sp_Blitz @OutputServerName feature broken

[PierreLetter]

Version of the script
Using the July 2020 version of the script

What is the current behavior?
When trying to copy the results of sp_Blitz over linked server to another server, I get an error that and XML column exists (of course it does, its for the plan cache options of the procedure). But SQL Server doesn't support RPC with destination tables that have XML columns in them, even if they are not used. And removing the columns makes sp_Blitz unhappy.

Error message is: Xml data type is not supported in distributed queries. Remote object 'BlitzMaster.DBATools.dbo.BlitzResults' has xml column(s).

If the current behavior is a bug, please provide the steps to reproduce.
Create a linked server with the standard BlitzResults table on the destination
Run sp_Blitz @OutputServerName ='ThatTargetServer', @OutputDatabaseName = 'DBATools', @OutputSchemaName = 'dbo', @OutputTableName = 'BlitzResults'

What is the expected behavior?
Expected behavior is that the copy happens. But it seems impossible if those plan cache columns remain on sp_blitz.
Not sure there is an expected solution though as long as XML columns are in the way or until MS removes this restriction.

Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?
Running Windows 2019 and SQL Server 2019 CU5

PS. Haven't tested sp_BlitzCache yet with this but wonder how it's @OutputServerName parameter goes. Probably the same way.

[BrentOzar]

Read the documentation on the home page of this repo:

@OutputServerName - not functional yet. To track (or help!) implementation status: #293

Thanks.

[PierreLetter]

Thanks for the confirmation. Though it worked for spBlitz and spBlitzCache after the work from @hariscodes and that the readme wasn't up to date. Didn't check the details of the code. My bad there.

Would it be acceptable to have an alternate target CentralBlitzResults table that doesn't have the XML columns? Would at least allow to centralize all the other information for sp_Blitz. Can propose this if you see an interest as its the workaround I'll implement for myself.

[BrentOzar]

Sure, you can do that if you're willing to do the code for it. Check the contributing guide on how to do that. Thanks!

[PierreLetter]

Looking at the code, I found out this feature does work for sp_Blitz if you follow the following guidelines:

1. You must have the table created using the @OutputServerName parameter set.
2. If you run sp_Blitz on the central server itself, you must build a Linked Server to itself to use to call the @OutputServerName parameter.
   This makes everybody use the VARCHAR(MAX) and not XML type for the Cache plan columns and removes all conflicts.

It doesn't work for sp_BlitzCache with this trick. Will try to find out why and propose a code fix but not this week.

[mihirrane]

Hello,
I have installed the September version. I have installed the stored procedure on two servers 'A' and 'B'. 'A' is the central server.
I don't have the table 'BlitzResults' mentioned here. Can you please tell me how I can replicate the process to work with linked server?

[BrentOzar]

Sure, what are the exact commands you’re running, and what errors are you getting?

…

--- Tiny glass keyboard Typos flowing like rivers As winter snow thaws

On Sep 15, 2020, at 11:14 AM, Mihir Rane ***@***.***> wrote:  Hello, I have installed the September version. I have installed the stored procedure on two servers 'A' and 'B'. 'A' is the central server. I don't have the table 'BlitzResults' mentioned here. Can you please tell me how I can replicate the process to work with linked server? — You are receiving this because you modified the open/close state. Reply to this email directly, view it on GitHub, or unsubscribe.

[PierreLetter]

Hi @mihirrane, did you run the stored procedure? It should create the table if it doesn't exist.
This is why it is important to create the table with the @Outputserver parameter pointing to the local server if you create it on the central server first so that it creates it with the proper data types: VARCHAR(MAX) instead of XML.

[mihirrane]

I thought sp_BlitzResults had to be created before. I ran the query on central server and it worked.
Thank you so much @PierreLetter and @BrentOzar !