March 23, 2011 at 12:01 pm
Apparently you cannot call a remote UDF. How can I rewrite this -
SELECT SDDMID.[index_handle],
SDDMIC.[column_id],
SDDMIC.[column_name],
SDDMIC.[column_usage],
GETDATE()
FROM LINKED_SERVER.MASTER.sys.[dm_db_missing_index_details] SDDMID
CROSS APPLY LINKED_SERVER.MASTER.sys.[dm_db_missing_index_columns](SDDMID.index_handle) SDDMIC
LEFT JOIN [DBA].[dbo].[dm_db_missing_index_columns] MDDMIC ON SDDMID.[index_handle] = MDDMIC.[index_handle]
AND SDDMIC.[column_id] = MDDMIC.[column_id]
WHERE MDDMIC.[index_handle] IS NULL
ORDER BY SDDMID.[index_handle]
March 23, 2011 at 12:33 pm
How about something along these lines:
IF OBJECT_ID(N'tempdb..##missing_index_info') > 0
DROP TABLE ##missing_index_info
GO
CREATE TABLE ##missing_index_info
(
[index_handle] INT,
[column_id] INT,
[column_name] NVARCHAR(4000),
[column_usage] NVARCHAR(4000),
[audit_date] DATETIME
) ;
go
INSERT INTO ##missing_index_info
(
index_handle,
column_id,
column_name,
column_usage,
audit_date
)
EXEC (
'
SELECT SDDMID.[index_handle],
SDDMIC.[column_id],
SDDMIC.[column_name],
SDDMIC.[column_usage],
GETDATE()
FROM MASTER.sys.[dm_db_missing_index_details] SDDMID
CROSS APPLY MASTER.sys.[dm_db_missing_index_columns](SDDMID.index_handle) SDDMIC'
) AT LINKED_SERVER_NAME
GO
SELECT *
FROM ##missing_index_info x
LEFT JOIN [DBA].[dbo].[dm_db_missing_index_columns] MDDMIC ON x.[index_handle] = MDDMIC.[index_handle]
AND x.[column_id] = MDDMIC.[column_id]
WHERE MDDMIC.[index_handle] IS NULL ;
GO
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 23, 2011 at 1:08 pm
Awesome. Works perfectly. Thanks very much for your time.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply