I run a n Extended Event (Xevent) that collects errors when the severity is above 10. I've written about it elsewhere in the blog.
But one thing that's been bugging me is errors like the following in my logs, courtesy of "DMVCollector".
And what causes it? Well, thanks to the Xevent, I have the code. I've shared it with MS and they're working on a fix, but a couple of things stand out.
1. Yup, Dynamic SQL is tough. Someone missed a quote.
2. they're using EXEC. Not SP_EXECUTESQL. And they're not escaping/quoting the databasename.
3. I'm the first to report this. Really?
4. How long has this been running, not working?
But that EXEC made me wonder if you can abuse that. The equivalent of 'little bobby tables'. There's a dependency on dm_hadr_fabric_partition_states, which I simply don't know what determines if it's a "real" database name (not the GUID that they use everywhere internally). So it looks like we're safe, due to them using the GUID. But still, crazy, right?
IF OBJECT_ID('tempdb.dbo.#memory_optimized_tables_internal_attributes ') IS NOT NULL
DROP TABLE #memory_optimized_tables_internal_attributes
-- Create the table with the proper schema
SELECT TOP 0
newid() as logical_database_guid,
db_name() as logical_database_name,
db_name() as db_name,
object_id,
xtp_object_id,
type,
type_desc
INTO
#memory_optimized_tables_internal_attributes
FROM
sys.memory_optimized_tables_internal_attributes
-- This is a DB-scoped DMV. Iterate over all user databases (id > 4) and collect the data.
DECLARE @name sysname;
DECLARE @logical_database_guid UNIQUEIDENTIFIER;
DECLARE @logical_database_name sysname;
DECLARE @database_id int;
DECLARE @SQLString nvarchar(max);
DECLARE DbCursor CURSOR LOCAL FORWARD_ONLY
FOR
SELECT
PS.logical_database_id [logical_database_guid], D.name [name], D.database_id [database_id], DB_NAME(D.database_id) [logical_database_name]
FROM
sys.databases D join sys.dm_hadr_fabric_partition_states PS ON PS.database_name = D.name
OPEN DbCursor
FETCH NEXT FROM DbCursor INTO @logical_database_guid, @name, @database_id, @logical_database_name
WHILE @@fetch_status = 0
BEGIN
SET @SQLString = N'INSERT INTO #memory_optimized_tables_internal_attributes
SELECT '''
+ convert(nvarchar(100), @logical_database_guid) + ''', '''
+ QUOTENAME(@logical_database_name, '''') + ', '
+ QUOTENAME(@name, '''') + ',
object_id,
xtp_object_id,
type,
type_desc
FROM '
+ QUOTENAME(@name) + '.sys.memory_optimized_tables_internal_attributes'
BEGIN TRY
EXEC (@SQLString)
END TRY
BEGIN CATCH
-- Swallow the exception.
END CATCH
-- Move to the next database.
FETCH NEXT FROM DbCursor INTO @logical_database_guid, @name, @database_id, @logical_database_name
END
CLOSE DbCursor
DEALLOCATE DbCursor
