Must declare the scalar variable "@Name"



  • I need your help please since I am stuck here and not sure how to fix this.

    Trying to run this code below and getting this message (Must declare the scalar variable "@Name").

    How can I pass the @name into the from clause so the results are returning for each db?

    Thank you

    DROP TABLE IF EXISTS #linkedservers
    

    CREATE table #linkedservers

    (

    SRV_NAME sysname NULL

    ,SRV_PROVIDERNAME nvarchar(128) NULL

    ,SRV_PRODUCT nvarchar(128) NULL

    ,SRV_DATASOURCE nvarchar(4000) NULL

    ,SRV_PROVIDERSTRING nvarchar(4000) NULL

    ,SRV_LOCATION nvarchar(4000) NULL

    ,SRV_CAT sysname NULL

    )

    INSERT #linkedservers

    (SRV_NAME

    ,SRV_PROVIDERNAME

    ,SRV_PRODUCT

    ,SRV_DATASOURCE

    ,SRV_PROVIDERSTRING

    ,SRV_LOCATION

    ,SRV_CAT

    )

    EXEC sp_linkedservers

    DECLARE @Statement nvarchar(2000)

    DECLARE @server_id nvarchar(150)

    DECLARE LinkedServers CURSOR FOR

    SELECT SRV_NAME

    FROM #linkedservers where SRV_NAME in ('LinkedServe1','LinkedServe2')

    ORDER BY SRV_NAME

    OPEN LinkedServers

    FETCH NEXT FROM LinkedServers into @server_id

    WHILE @@FETCH_STATUS=0

    BEGIN

    /building up dynamic sql/

    SET @Statement =N'

    SELECT '''+ convert(nvarchar(150),@server_id) + N''' as [Server];

    CREATE TABLE #name

    (

    id int IDENTITY(1,1),

    [name] sysname

    )

    insert into #name

    SELECT name

    FROM [' + @server_id + '].master.sys.databases

    WHERE [state] = 0;

    DECLARE @Counter INT ,

    @MaxId INT;

    SELECT @Counter = min(Id) , @MaxId = max(Id)

    FROM #name

    WHILE(@Counter IS NOT NULL

    AND @Counter <= @MaxId)

    BEGIN

    declare @Name NVARCHAR(100)

    Select @Name = name

    FROM #name WHERE Id = @Counter

    SELECT

    name AS [DataFileName],

    physical_name AS [DataPhysicalName],

    ROUND(size / 128.0, 1) AS [DataTotalSizeInMB],

    ROUND(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS int) / 128.0, 1) AS [DataSpaceUsedInMB],

    ROUND(size / 128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS int) / 128.0, 1) AS [DataAvailableSpaceInMB]

    FROM ['+ @Server_id +']. ['+ @Name +'].[sys].[database_files]

    SET @Counter = @Counter + 1

    END

    '

    EXECUTE master.dbo.sp_executesql @Statement

    FETCH NEXT FROM LinkedServers into @server_id

    END

    CLOSE LinkedServers

    DEALLOCATE LinkedServers

    DROP TABLE #linkedservers



  • I fixed your code and reformatted it.

    DROP TABLE IF EXISTS #linkedservers;
    

    CREATE TABLE #linkedservers
    (
    SRV_NAME sysname NULL,
    SRV_PROVIDERNAME nvarchar(128) NULL,
    SRV_PRODUCT nvarchar(128) NULL,
    SRV_DATASOURCE nvarchar(4000) NULL,
    SRV_PROVIDERSTRING nvarchar(4000) NULL,
    SRV_LOCATION nvarchar(4000) NULL,
    SRV_CAT sysname NULL
    );

    INSERT #linkedservers
    (
    SRV_NAME,
    SRV_PROVIDERNAME,
    SRV_PRODUCT,
    SRV_DATASOURCE,
    SRV_PROVIDERSTRING,
    SRV_LOCATION,
    SRV_CAT
    )

    EXEC sp_linkedservers;

    DECLARE @Statement nvarchar(2000);
    DECLARE @server_id nvarchar(150);
    DECLARE @Name NVARCHAR(100);

    DECLARE LinkedServers CURSOR FOR
    SELECT
    SRV_NAME
    FROM #linkedservers
    WHERE SRV_NAME IN ('LinkedServe1','LinkedServe2')
    ORDER BY SRV_NAME

    OPEN LinkedServers
    FETCH NEXT FROM LinkedServers into @server_id
    WHILE @@FETCH_STATUS=0
    BEGIN
    /building up dynamic sql/
    SET @Statement =N'
    SELECT '''+ convert(nvarchar(150),@server_id) + N''' as [Server];
    CREATE TABLE #name
    (
    id int IDENTITY(1,1),
    [name] sysname
    )

    INSERT INTO #name
    SELECT
    name
    FROM [' + @server_id + '].master.sys.databases
    WHERE [state] = 0;

    DECLARE @Counter INT,
    @MaxId INT;
    SELECT
    @Counter = min(Id),
    @MaxId = max(Id)
    FROM #name
    WHILE(@Counter IS NOT NULL
    AND @Counter <= @MaxId)

    BEGIN
    SELECT
    @Name = name
    FROM #name
    WHERE Id = @Counter

    SELECT
    name AS [DataFileName],
    physical_name AS [DataPhysicalName],
    ROUND(size / 128.0, 1) AS [DataTotalSizeInMB],
    ROUND(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS int) / 128.0, 1) AS [DataSpaceUsedInMB],
    ROUND(size / 128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS int) / 128.0, 1) AS [DataAvailableSpaceInMB]
    FROM ['+ @Server_id +']. ['+ @Name +'].[sys].[database_files]

    SET @Counter = @Counter + 1
    END
    '

    EXECUTE master.dbo.sp_executesql @Statement
    FETCH NEXT FROM LinkedServers into @server_id
    END;
    CLOSE LinkedServers;
    DEALLOCATE LinkedServers;
    /SELECT * FROM #linkedservers;/
    DROP TABLE #linkedservers;

    I am also adding two more queries where you can get similar information without using cursors. I might have copied this from someone else's blog post that I do not remember.

    SELECT
      a.NAME,
      a.product,
      a.provider,
      a.data_source,
      a.catalog,
      f.name,
      b.uses_self_credential,
      b.remote_name
    FROM sys.servers AS a
    LEFT JOIN sys.linked_logins AS b
    ON a.server_id = b.server_id
    LEFT JOIN sys.server_principals AS f
    ON b.local_principal_id = f.principal_id
    

    SELECT
    ss.server_id,
    ss.name,
    'Server ' =
    CASE ss.Server_id
    WHEN 0 THEN 'Current Server'
    ELSE 'Remote Server'
    END,
    ss.product,
    ss.provider,
    ss.catalog ,
    'Local Login ' =
    CASE sl.uses_self_credential
    WHEN 1 THEN 'Uses Self Credentials'
    ELSE ssp.name
    END,
    'Remote Login Name' = sl.remote_name,
    'RPC Out Enabled' =
    case ss.is_rpc_out_enabled
    when 1 then 'True'
    else 'False'
    end,
    'Data Access Enabled' =
    case ss.is_data_access_enabled
    when 1 then 'True'
    else 'False'
    end,
    ss.modify_date
    FROM sys.Servers ss
    LEFT JOIN sys.linked_logins sl
    ON ss.server_id = sl.server_id
    LEFT JOIN sys.server_principals ssp
    ON ssp.principal_id = sl.local_principal_id




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2