Use @OutputServerName to write results to a linked server

[BrentOzar]

If this parameter is populated, check to see if a linked server exists by this name. If not, print an error.

If the linked server exists, use @OutputDatabaseName, @OutputSchemaName, and @OutputTableName to write the results.

We don't have to do big time error checking here - no need to get fancy and check for the database and the schema for now, can always add that in a later version.

Use case: someone runs the stored procedures regularly as an Agent job, and writes the results to a central server for reporting purposes.

[hariscodes]

The best way to do this IMO would be to just rewrite the existing @OutputTableName section with some new logic. That way we can avoid unnecessary code bloat.

Sounds like fun! I'd like to give it a shot.

[hariscodes]

Just an update on this guy. I started working on this feature for sp_Blitz.

I know you didn't want fancy error checking, but since I needed to check whether or not there was a linked server on the other side, I added a bit more than just the linked server check. I check if the linked server is valid, then check if the database exists on the linked server. I took the logic for the local location dump out as well, and just put it all in an error checking block that spits out whether or not it's a valid location in bit form.

/* Checks if @OutputServerName is populated with a valid linked server, and that the database name specified is valid */

                DECLARE @ValidOutputServer BIT
                DECLARE @ValidOutputLocation BIT
                DECLARE @LinkedServerDBCheck NVARCHAR(2000)
                DECLARE @Count INT
                IF @OutputServerName IS NOT NULL
                    BEGIN
                        IF EXISTS (SELECT server_id FROM sys.servers WHERE QUOTENAME([name]) = @OutputServerName)
                            BEGIN
                                SET @LinkedServerDBCheck = 'SELECT * FROM '+@OutputServerName+'.master.sys.databases WHERE QUOTENAME([name]) = '''+@OutputDatabaseName+''''
                                EXEC sys.sp_executesql @LinkedServerDBCheck
                                SELECT @Count = @@ROWCOUNT
                                IF (@Count > 0)
                                    BEGIN
                                        SET @ValidOutputServer = 1
                                        SET @ValidOutputLocation = 1
                                    END
                                ELSE
                                    RAISERROR('The specified database was not found on the output server', 16, 0)
                            END
                        ELSE
                            BEGIN
                                RAISERROR('The specified output server was not found', 16, 0)
                            END
                    END
                ELSE
                    BEGIN
                        IF @OutputDatabaseName IS NOT NULL
                            AND @OutputSchemaName IS NOT NULL
                            AND @OutputTableName IS NOT NULL
                            AND EXISTS ( SELECT *
                                 FROM   sys.databases
                                 WHERE  QUOTENAME([name]) = @OutputDatabaseName)
                            BEGIN
                                SET @ValidOutputLocation = 1
                            END
                        ELSE IF @OutputDatabaseName IS NOT NULL
                            AND @OutputSchemaName IS NOT NULL
                            AND @OutputTableName IS NOT NULL
                            AND NOT EXISTS ( SELECT *
                                 FROM   sys.databases
                                 WHERE  QUOTENAME([name]) = @OutputDatabaseName)
                            BEGIN
                                RAISERROR('The specified output database was not found on this server', 16, 0)
                            END
                        ELSE
                            BEGIN
                                SET @ValidOutputLocation = 0 
                            END
                    END

The first block is error checking for the linked server and it's database. The second block has the check for the local database (no schema check here, though). I basically transplated the local logic from the start of the original block and added error checking for the database name. Otherwise, it just sets the @ValidLocationOutput variable to 0, and the procedure continues as normal.

If @ValidOutputLocation is 1, then the Output block begins to run.

On the DDL for @OutputTableName, I was hoping to reuse as much code as possible, and simply run the DDL on either server. I came up with a simple solution:

IF @ValidOutputServer = 1
                            BEGIN
                                SET @StringToExecute = REPLACE(@StringToExecute,''''+@OutputSchemaName+'''',''''''+@OutputSchemaName+'''''')
                                SET @StringToExecute = REPLACE(@StringToExecute,''''+@OutputTableName+'''',''''''+@OutputTableName+'''''')
                                EXEC('EXEC('''+@StringToExecute+''') AT ' + @OutputServerName);
                            END   
                        ELSE
                            BEGIN
                                EXEC(@StringToExecute);
                            END

If there's a @ValidOutputServer it will try to execute @StringToExecute AT the @OutputServerName. Since this has to be dynamic, I had to use a nested EXEC...which meant that the string needed a few extra apostrophes around the Schema/Table names during the IF EXISTS checks at the start of the DDL block. Hence the REPLACE functions in the beginning to alter the @StringToExecute. If @ValidOutputServer <> 1, then it will just execute the string as is. Voila! Code reuse!

The actual data dump can't reuse code since #BlitzResults only exists on the server. You have to add the linked server components to that query and put it inside of the @ValidOutputServer block.

IF @ValidOutputServer = 1
                            BEGIN
                                SET @StringToExecute = N' IF EXISTS(SELECT * FROM '
                                + @OutputServerName + '.'
                                + @OutputDatabaseName
                                + '.INFORMATION_SCHEMA.SCHEMATA WHERE QUOTENAME(SCHEMA_NAME) = '''
                                + @OutputSchemaName + ''') INSERT '
                                + @OutputServerName + '.'
                                + @OutputDatabaseName + '.'
                                + @OutputSchemaName + '.'
                                + @OutputTableName
                                + ' (ServerName, CheckDate, CheckID, DatabaseName, Priority, FindingsGroup, Finding, URL, Details, QueryPlan, QueryPlanFiltered) SELECT '''
                                + CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128))
                                + ''', SYSDATETIMEOFFSET(), CheckID, DatabaseName, Priority, FindingsGroup, Finding, URL, Details, QueryPlan, QueryPlanFiltered FROM #BlitzResults ORDER BY Priority , FindingsGroup , Finding , Details';

                                EXEC(@StringToExecute);
                            END   
                        ELSE
                            BEGIN
                                SET @StringToExecute = N' IF EXISTS(SELECT * FROM '
                                + @OutputDatabaseName
                                + '.INFORMATION_SCHEMA.SCHEMATA WHERE QUOTENAME(SCHEMA_NAME) = '''
                                + @OutputSchemaName + ''') INSERT '
                                + @OutputDatabaseName + '.'
                                + @OutputSchemaName + '.'
                                + @OutputTableName
                                + ' (ServerName, CheckDate, CheckID, DatabaseName, Priority, FindingsGroup, Finding, URL, Details, QueryPlan, QueryPlanFiltered) SELECT '''
                                + CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128))
                                + ''', SYSDATETIMEOFFSET(), CheckID, DatabaseName, Priority, FindingsGroup, Finding, URL, Details, QueryPlan, QueryPlanFiltered FROM #BlitzResults ORDER BY Priority , FindingsGroup , Finding , Details';

                                EXEC(@StringToExecute);
                            END
                    END

This should work great!

...or so I thought. The DDL creates the table on the linked/local server just fine, and the logic catches the errors it's supposed to, but the problem is you can't dump any data to it because the table contains an XML column!

If you haven't met this 15 year old bug, like I hadn't, let me introduce you: https://connect.microsoft.com/SQLServer/feedback/details/247204/tables-with-xml-columns-cannot-be-queried-using-distributed-llinked-server-queries

Since the table that's created has an XML column it can't participate in any Linked Server queries, at ALL, even if your query doesn't touch that column. It doesn't help that the bug has been there since SQL Server 2005. I'm kinda stumped. I can't just cast the #BlitzResults stuff as varchar and use OPENQUERY, I literally can't read/write from any linked server table that has an XML column.

Any ideas on how to the best solution for this? Do we just changed the DDL for the linked server table so that QueryPlan is a varchar field? Is it possible to use a global temp table on the linked server to stage the QueryPlan then cast it as XML as we INSERT it into the permanent table?

[BlitzErik]

My vote would be to just store it as a N/VARCHAR(MAX) and list it as a known issue, unless there's a documented workaround out there. This is a MS 'bug' so I wouldn't spend too much time hair pulling over it.

[hariscodes]

It works without any issues going that route.

I just added SET @StringToExecute = REPLACE(@StringToExecute,'[XML]','[NVARCHAR](MAX)') to the DDL execution block to change the data type of QueryPlan on the linked server. and cast the QueryPlan as NVARCHAR(MAX) on the dump from #BlitzResults.

Also, please ignore the horrific messages, these are old servers I've turned into playgrounds...

Now to test! I must go down into my dungeon of VMs to find the one with 2008 on it...

[BrentOzar]

Yeah, I love the NVARCHAR(MAX) route - we had to take this same approach in sp_BlitzFirst - there's a parameter to return XML fields as NVARCHAR because of this exact problem over linked servers. (sigh)

[BrentOzar]

I haven't tested this, but I'm excited to see it! Thanks for your work, this is so cool to see.

[hariscodes]

My pleasure! This has been a great learning experience. There's a lot of nuance to getting dynamic SQL to work with linked server queries that I'd never explored.

With regards to testing: I've tested it writing to and from 2008 SP3 (resisted urge to kill it with fire), 2008 R2 SP3, and 2012 SP2. I don't have 2014 to test with, and my 2016 instance is currently Alone and Friendless on AWS©️. If someone wants to try those please do; though I don't see how it would be that different, it's pretty straight forward DDL->INSERT.

[BrentOzar]

Sure, can you make a pull request for this for the dev branch?

[hariscodes]

This now works in sp_BlitzCache!

the principle is the same as before, but the query modification was a bit more complex:

    IF @ValidOutputServer = 1
        BEGIN
            SET @insert_sql = REPLACE(@insert_sql,''''+@OutputSchemaName+'''',''''''+@OutputSchemaName+'''''')
            SET @insert_sql = REPLACE(@insert_sql,''''+@OutputTableName+'''',''''''+@OutputTableName+'''''')
            SET @insert_sql = REPLACE(@insert_sql,'''DBCC FREEPROCCACHE ('' + CONVERT(VARCHAR(128), [PlanHandle], 1) + '');''','''''DBCC FREEPROCCACHE ('''' + QUOTENAME(CONVERT(VARCHAR(128), [PlanHandle], 1), CHAR(39)) + '''');''''')
            SET @insert_sql = REPLACE(@insert_sql,'''DBCC FREEPROCCACHE ('' + CONVERT(VARCHAR(128), [SqlHandle], 1) + '');''','''''DBCC FREEPROCCACHE ('''' + QUOTENAME(CONVERT(VARCHAR(128), [SqlHandle], 1), CHAR(39)) + '''');''''')
            SET @insert_sql = REPLACE(@insert_sql,'''EXEC sp_BlitzCache @OnlySqlHandles = '''''' + CONVERT(VARCHAR(128), [SqlHandle], 1) + ''''''; ''','''''EXEC sp_BlitzCache @OnlySqlHandles = '''' + QUOTENAME(CONVERT(VARCHAR(128), [SqlHandle], 1), CHAR(39)) + ''''; ''''')
            SET @insert_sql = REPLACE(@insert_sql,'''EXEC sp_BlitzCache @OnlyQueryHashes = '''''' + CONVERT(VARCHAR(32), [QueryHash], 1) + ''''''; ''','''''EXEC sp_BlitzCache @OnlyQueryHashes = '''' + QUOTENAME(CONVERT(VARCHAR(32), [QueryHash], 1), CHAR(39)) + ''''; ''''')
            SET @insert_sql = REPLACE(@insert_sql,'N/A','''N/A''')
            SET @insert_sql = REPLACE(@insert_sql,'XML','[NVARCHAR](MAX)')
            EXEC('EXEC('''+@insert_sql+''') AT ' + @OutputServerName);
        END   
    ELSE
        BEGIN
            EXEC sp_executesql @insert_sql;
        END

Apart from adding some extra quotation marks around the statements for the more info columns, in order to add the quotation marks around the plan handles and query hashes I had to use QUOTENAME. It's the same in principle though.

Question: Do we want to dump the summary table somewhere as well? Right now I'm only outputting plan cache analysis.

[BrentOzar]

Closing this since it's been a while.

[dpolivy]

Are there any plans to finally implement this in sp_BlitzFirst?

[BrentOzar]

Me? No, that’s why it’s closed. You’re welcome to give it a shot though.

…

On Apr 10, 2018, at 5:40 PM, Dan Polivy ***@***.***> wrote: Are there any plans to finally implement this in sp_BlitzFirst? — You are receiving this because you modified the open/close state. Reply to this email directly, view it on GitHub, or mute the thread.