Must declare the scalar variable “@Name”

The question:

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

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

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

Method 2

This is probably cleaner using sys.master_files which shows the files for all databases:

/*building up dynamic sql*/

SET @Statement =N'

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 +']. [master].[sys].[master_files]


'


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment