The square brackets I was referring to can be seen in my implemented code. It's just a simple case of adding the square brackets in the dynamic SQL.

-- =============================================

-- Author: Shaun Stuart, shaunjstuart.com

-- Create date: August 24, 2010

-- Description:Procedure to collect disk space usage data

-- http://www.sqlservercentral.com/articles/Administration/71168/

-- =============================================

ALTER PROCEDURE [dbo].[GatherServerDiskUsageData]

AS

DECLARE@ServerName varchar(128)

DECLARE@ServerNamePK int

DECLARE@PersistLink bit

DECLARE@SQLCmd varchar(1000)

DECLARE@LinkedServerLogin varchar(50)

DECLARE@LinkedServerLoginpwd varchar(50)

DECLARE@SQLServerVersion char(4)

DECLARE@TrackTotalDiskSpace bit

SET@LinkedServerLogin = '****'

SET@LinkedServerLoginpwd = '****'

/* Get a list of the servers to gather data from and their SQL version */

DECLAREServersCursor CURSOR FOR

SELECTServerName,

PK,

PersistLink,

SQLServerVersion,

TrackTotalDiskSpace

FROMServersToCheckDiskStats

OPENServersCursor

FETCH NEXT FROM ServersCursor INTO @ServerName, @ServerNamePK, @PersistLink, @SQLServerVersion, @TrackTotalDiskSpace

WHILE @@FETCH_STATUS = 0

BEGIN

/* create linked server to get disk stats. Check PersistLink value to see if linked server should remain or be deleted */

IF Exists (

SELECT *

FROM master.sys.servers

WHERE @ServerName = name) AND @PersistLink = 0

BEGIN

EXEC sp_dropserver @ServerName, droplogins

END

IF @PersistLink = 0/*if link is persisted, linked server is already present, no need to add */

BEGIN

EXEC sp_addlinkedserver @server=@ServerName

END

EXEC sp_addlinkedsrvlogin @ServerName, 'false', NULL, @LinkedServerLogin, @LinkedServerLoginPwd

/* pull disk usage data for each database file using the SQL version-appropriate system table */

SELECT @SQLCmd = CASE @SQLServerVersion

WHEN '2008' THEN

'INSERTDatabaseDiskStats

(ServersToCheckPK,

Server_name,

Database_name,

[Filename],

Drive_letter,

Size_in_MB)

SELECT' + convert(varchar(6),@ServerNamePK) + ','

+ ''''+ @ServerName + '''' + ',

sdbs.name AS database_name ,

smf.physical_name,

UPPER(LEFT(smf.physical_name, 2)) AS drive_letter ,

(smf.size * 8 ) / 1024 AS size_in_mb

FROM[' + @ServerName + '].master.sys.master_files AS smf,

[' + @ServerName + '].master.sys.databases sdbs

WHEREsmf.database_id = sdbs.database_id

AND sdbs.name <> ''tempdb''

ORDER BYsmf.database_id'

WHEN '2005' THEN/*same as 2008 */

'INSERTDatabaseDiskStats

(ServersToCheckPK,

Server_name,

Database_name,

[Filename],

Drive_letter,

Size_in_MB)

SELECT' + convert(varchar(6),@ServerNamePK) + ','

+ ''''+ @ServerName + '''' + ',

sdbs.name AS database_name ,

smf.physical_name,

UPPER(LEFT(smf.physical_name, 2)) AS drive_letter ,

(smf.size * 8 ) / 1024 AS size_in_mb

FROM[' + @ServerName + '].master.sys.master_files AS smf,

[' + @ServerName + '].master.sys.databases sdbs

WHEREsmf.database_id = sdbs.database_id

AND sdbs.name <> ''tempdb''

ORDER BYsmf.database_id'

WHEN '2000' THEN

'INSERTDatabaseDiskStats

(ServersToCheckPK,

Server_name,

Database_name,

[Filename],

Drive_letter,

Size_in_MB)

SELECT' + convert(varchar(6),@ServerNamePK) + ','

+ ''''+ @ServerName + '''' + ',

sdbs.name AS database_name ,

LTRIM(RTRIM((saf.filename))),

UPPER(LEFT(saf.filename, 2)) AS drive_letter ,

(saf.size * 8 ) / 1024 AS size_in_mb

FROM[' + @ServerName + '].master.dbo.sysaltfiles AS saf,

[' + @ServerName + '].master.dbo.sysdatabases sdbs

WHEREsaf.dbid = sdbs.dbid

AND sdbs.name <> ''tempdb''

ORDER BYsaf.dbid'

ELSE/* not a SQL version this procedure handles */

'INSERTDatabaseDiskStats

(ServersToCheckPK,

Server_name,

Database_name,

[Filename],

Drive_letter)

SELECT' + convert(varchar(6),@ServerNamePK) + ','

+ ''''+ @ServerName + '''' + ','

+ '''' + 'undefined SQL version in table ServersToCheckDiskStats' + '''' + ' AS database_name ,'

+ '''' + 'ERROR' + '''' + ','

+ '''' + '*' + ''''

END

EXEC (@SQLCmd)

/* Tempdb has to be handled differently. In 2005+, the size for tempdb stored in sys.master_files is not the

current size of the file on disk. It is the initial size it will be created with when SQL Server restarts.

The current size of tmepdb on disk is stored in tempdb.sys.database_files. */

SELECT @SQLCmd = CASE @SQLServerVersion

WHEN '2008' THEN

'INSERTDatabaseDiskStats

(ServersToCheckPK,

Server_name,

Database_name,

[Filename],

Drive_letter,

Size_in_MB)

SELECT' + convert(varchar(6),@ServerNamePK) + ','

+ ''''+ @ServerName + '''' + ',

''tempdb'' AS database_name ,

sdf.physical_name,

UPPER(LEFT(sdf.physical_name, 2)) AS drive_letter ,

(sdf.size * 8 ) / 1024 AS size_in_mb

FROM[' + @ServerName + '].tempdb.sys.database_files AS sdf'

WHEN '2005' THEN/*same as 2008 */

'INSERTDatabaseDiskStats

(ServersToCheckPK,

Server_name,

Database_name,

[Filename],

Drive_letter,

Size_in_MB)

SELECT' + convert(varchar(6),@ServerNamePK) + ','

+ ''''+ @ServerName + '''' + ',

''tempdb'' AS database_name ,

sdf.physical_name,

UPPER(LEFT(sdf.physical_name, 2)) AS drive_letter ,

(sdf.size * 8 ) / 1024 AS size_in_mb

FROM[' + @ServerName + '].tempdb.sys.database_files AS sdf'

WHEN '2000' THEN

'INSERTDatabaseDiskStats

(ServersToCheckPK,

Server_name,

Database_name,

[Filename],

Drive_letter,

Size_in_MB)

SELECT' + convert(varchar(6),@ServerNamePK) + ','

+ ''''+ @ServerName + '''' + ',

''tempdb'',

LTRIM(RTRIM((sf.filename))),

UPPER(LEFT(sf.filename, 2)) AS drive_letter ,

(sf.size * 8 ) / 1024 AS size_in_mb

FROM[' + @ServerName + '].tempdb.dbo.sysfiles AS sf'

ELSE/* not a SQL version this procedure handles */

'INSERTDatabaseDiskStats

(ServersToCheckPK,

Server_name,

Database_name,

[Filename],

Drive_letter)

SELECT' + convert(varchar(6),@ServerNamePK) + ','

+ ''''+ @ServerName + '''' + ','

+ '''' + 'undefined SQL version in table ServersToCheckDiskStats' + '''' + ' AS database_name ,'

+ '''' + 'ERROR' + '''' + ','

+ '''' + '*' + ''''

END

EXEC (@SQLCmd)

/* Set MaxDriveSize_in_MB to zero for drives we are not tracking the size of */

IF @TrackTotalDiskSpace = 0

BEGIN

UPDATEDatabaseDiskStats

SETMaxDriveSize_in_MB = 0

WHEREServer_name = @ServerName

AND MaxDriveSize_in_MB is NULL

END

IF @persistLink = 0

BEGIN

EXEC sp_dropserver @ServerName, droplogins

END

FETCH NEXT FROM ServersCursor INTO @ServerName, @ServerNamePK, @PersistLink, @SQLServerVersion, @TrackTotalDiskSpace

END

CLOSE ServersCursor

DEALLOCATE ServersCursor