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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy