May 7, 2008 at 12:07 pm
I'm trying to write a query to give me the files and some stats for every db on the server. It works on one server but not another one. When I run it I get the following error:
Msg 7202, Level 11, State 2, Line 48
Could not find server 'SQLcompliance' 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.
I'm not using linked servers at all. Here is the query I'm running:
DECLARE @Tmp TABLE (dbname NVARCHAR(100))
DECLARE @wrkStatement NVARCHAR(200)
DECLARE @wrkSql NVARCHAR(1600)
INSERT INTO @Tmp(dbname) SELECT name FROM master..sysdatabases
CREATE TABLE #tmp123_results (
FileId INT,
AllocatedMB DECIMAL(12, 2),
UsedMB DECIMAL(12, 2),
FreeMB DECIMAL(12, 2),
PercentFree DECIMAL(12, 2),
LogicalName NVARCHAR(255),
PhysicalName NVARCHAR(255)
)
DECLARE @curr_db NVARCHAR(100)
DECLARE @crsSelect CURSOR
SET @crsSelect = CURSOR FOR
SELECT dbname
FROM @Tmp
OPEN @crsSelect
FETCH NEXT FROM @crsSelect INTO @curr_db
WHILE @@FETCH_STATUS = 0
BEGIN
SET @wrkStatement = @curr_db + '.dbo.sp_executesql'
SET @wrkSql = 'INSERT INTO #tmp123_results(FileId,AllocatedMB,UsedMB,FreeMB,PercentFree,LogicalName,PhysicalName)
SELECT
a.FILEID,
ALLOCATED_MB = convert(decimal(12,2),round(a.size/128.000,2)),
USED_MB = convert(decimal(12,2),round(fileproperty(a.name,''SpaceUsed'')/128.000,2)),
FREE_MB = convert(decimal(12,2),round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2)) ,
PercentFree = 100 * (convert(decimal(12,2),round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2)) / convert(decimal(12,2),round(a.size/128.000,2)) ) ,
NAME =a.NAME,
FILENAME = a.FILENAME
FROM ' + @curr_db + '.dbo.sysfiles a'
EXEC @wrkStatement @wrkSql
FETCH NEXT FROM @crsSelect INTO @curr_db
END
CLOSE @crsSelect
DEALLOCATE @crsSelect
SELECT * FROM #tmp123_results ORDER BY LogicalName
DROP TABLE #tmp123_results
Any ideas?
Thx,
Rob
May 7, 2008 at 12:33 pm
Are you sure that you did not highlight other code?
I copied your code and ran it on my test SQL 2005 without any issue.
May 7, 2008 at 12:42 pm
Definitely. I tested it on several servers. It works on most. On one it gives me the linked server error. On another it gives me this:
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near '02'.
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near '02'.
I have two databases that begin with 02. I'm wondering if this has something to do with:
SELECT name FROM master..sysdatabases
Not sure. I'm looking for a simple way to report on db files so I'm open to finding another way that works. This script gives me what I want but the darn thing won't work!
Thx,
Rob
May 7, 2008 at 12:48 pm
Figured out the problem.
Changed:
@wrkStatement = @curr_db + '.dbo.sp_executesql'
To:
@wrkStatement = '['+@curr_db + '].dbo.sp_executesql'
- AND -
Changed:
FROM ' + @curr_db + '.dbo.sysfiles a'
To:
FROM [' + @curr_db + '].dbo.sysfiles a'
The [] around the db name was the key.
The final script for anybody interested:
DECLARE @Tmp TABLE (dbname NVARCHAR(200))
DECLARE @wrkStatement NVARCHAR(200)
DECLARE @wrkSql NVARCHAR(4000)
INSERT INTO @Tmp(dbname) SELECT name FROM master..sysdatabases
CREATE TABLE #tmp123_results (
FileId INT,
AllocatedMB DECIMAL(12, 2),
UsedMB DECIMAL(12, 2),
FreeMB DECIMAL(12, 2),
PercentUsed DECIMAL(12, 2),
PercentFree DECIMAL(12, 2),
LogicalName NVARCHAR(655),
PhysicalName NVARCHAR(655)
)
DECLARE @curr_db NVARCHAR(100)
DECLARE @crsSelect CURSOR
SET @crsSelect = CURSOR FOR
SELECT dbname
FROM @Tmp
OPEN @crsSelect
FETCH NEXT FROM @crsSelect INTO @curr_db
WHILE @@FETCH_STATUS = 0
BEGIN
SET @wrkStatement = '['+@curr_db + '].dbo.sp_executesql'
SET @wrkSql = 'INSERT INTO #tmp123_results(FileId,AllocatedMB,UsedMB,FreeMB,PercentUsed,PercentFree,LogicalName,PhysicalName)
SELECT
a.FILEID,
ALLOCATED_MB = convert(decimal(12,2),round(a.size/128.000,2)),
USED_MB = convert(decimal(12,2),round(fileproperty(a.name,''SpaceUsed'')/128.000,2)),
FREE_MB = convert(decimal(12,2),round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2)) ,
PercentUsed = 100 * (convert(decimal(12,2),round((fileproperty(a.name,''SpaceUsed''))/128.000,2)) / convert(decimal(12,2),round(a.size/128.000,2)) ) ,
PercentFree = 100 * (convert(decimal(12,2),round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2)) / convert(decimal(12,2),round(a.size/128.000,2)) ) ,
NAME =a.NAME,
FILENAME = a.FILENAME
FROM [' + @curr_db + '].dbo.sysfiles a'
EXEC @wrkStatement @wrkSql
FETCH NEXT FROM @crsSelect INTO @curr_db
END
CLOSE @crsSelect
DEALLOCATE @crsSelect
SELECT * FROM #tmp123_results ORDER BY LogicalName
DROP TABLE #tmp123_results
Rob
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply