I'm converting a procedure to use a Linked Server, because the databases that it references have now been migrated to separate Clusters. I've almost finished this work, but I'm not stuck on the following part;
declare @CustomerId int set @CustomerId = 2 exec (N' USE [DatabaseName] SELECT @DiskUsageDataObjectsKB = SUM(alloUni.used_pages) * 8 FROM sys.tables sysTab INNER JOIN sys.indexes ind ON sysTab.OBJECT_ID = ind.OBJECT_ID and ind.Index_ID<=1 INNER JOIN sys.partitions parti ON ind.OBJECT_ID = parti.OBJECT_ID AND ind.index_id = parti.index_id INNER JOIN sys.allocation_units alloUni ON parti.partition_id = alloUni.container_id WHERE SCHEMA_NAME(sysTab.SCHEMA_ID) = CAST('+ @CustomerId +' AS NVARCHAR(MAX))') AT [LinkedServer] select @DiskUsageDataObjectsKB
As you can see, I'm passing the CustomerId variable to this using Dynamic SQL, but I'm not sure how I get the @DiskUsageDataObjectsKB value back to the procedure, so that It can be added to our reporting database.
Would anyone have any suggestions to how this can be done, please?
Many thanks! Tom