Msg 102, Level 15, State 1, Line 13
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Line 13
Incorrect syntax near '-'.

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

DECLARE ServersCursor CURSOR FOR
SELECT  ServerName,
                PK, 
                PersistLink,
                SQLServerVersion,
                TrackTotalDiskSpace
FROM    ServersToCheckDiskStats 

@ServerName

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_DatabaseDiskStats_ServersToCheckDiskStats1". The conflict occurred in database "DriveSpaceStats", table "dbo.ServersToCheckDiskStats", column 'ServerName'.
The statement has been terminated.

Msg 7202, Level 11, State 2, Line 1
Could not find server 'SQLSERVERNAME' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

-- =============================================
-- 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 */

DECLARE	ServersCursor CURSOR FOR
SELECT	ServerName,
		PK, 
		PersistLink,
		SQLServerVersion,
		TrackTotalDiskSpace
FROM	ServersToCheckDiskStats 

OPEN	ServersCursor
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
				 'INSERT	DatabaseDiskStats
					(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
				WHERE	smf.database_id = sdbs.database_id
					AND sdbs.name <> ''tempdb''
				ORDER BY	smf.database_id'
			WHEN '2005' THEN		/*same as 2008 */
				 'INSERT	DatabaseDiskStats
					(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
				WHERE	smf.database_id = sdbs.database_id
					AND sdbs.name <> ''tempdb''				
				ORDER BY	smf.database_id'
			WHEN '2000' THEN
				 'INSERT	DatabaseDiskStats
					(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
				WHERE	saf.dbid = sdbs.dbid
					AND sdbs.name <> ''tempdb''
				ORDER BY	saf.dbid'
			ELSE		/* not a SQL version this procedure handles */
				 'INSERT	DatabaseDiskStats
						(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
				 'INSERT	DatabaseDiskStats
					(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 */
				 'INSERT	DatabaseDiskStats
					(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
				 'INSERT	DatabaseDiskStats
					(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 */
				 'INSERT	DatabaseDiskStats
						(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
		UPDATE	DatabaseDiskStats
		SET		MaxDriveSize_in_MB = 0
		WHERE	Server_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