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


You should use sp_executesql with output parameter.

declare @S nvarchar(max) = 'select @x = 1'

declare @xx int
set @xx = 0

exec sp_executesql @S, N'@x int out', @xx out

select @xx

I've got the example from here.

| improve this answer | |
  • Thanks for the reply Dan, I've followed your example but I'm still lost as to how I point this at the Linked Server. Am I missing something here? Also, I get a "Conversion failed when converting the varchar value" error when running it. – Tom_W Mar 12 '19 at 10:35

Okay, so it turns out I was just over-complicating this whole thing. I just needed to add the [LinkedServer].[DatabaseName] value to the script as you normally do.

I didn't know you could do this while referencing sys objects, but you can.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.