June 14, 2012 at 10:34 am
Hello,
I am trying to build a nice report that has a pie chart, showing total space of all databases together in red or whatever being used which is below:
select Sum(size)/128 SizeInMB from sys.master_files
but also the other pie part showing in green or whatever showing the free space on the disk drive whatever the databases reside one, which is where I am having the problem, it saying when i build the report in wizard at the query design window its saying "An error occured while the query design method was being saved. Incorrect syntax near 'go'."
so thats the problem help any ideas? here is the code below:
SET NOCOUNT ON
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20) DECLARE @MB Numeric ; SET @MB = 1048576
CREATE TABLE #drives (drive char(1) PRIMARY KEY, FreeSpace int NULL,
TotalSize int NULL) INSERT #drives(drive,FreeSpace) EXEC
master.dbo.xp_fixeddrives EXEC @hr=sp_OACreate
'Scripting.FileSystemObject',@fso OUT IF @hr <> 0 EXEC sp_OAGetErrorInfo
@fso
DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT drive from #drives ORDER by drive
OPEN dcur FETCH NEXT FROM dcur INTO @drive
WHILE @@FETCH_STATUS=0
BEGIN
EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso EXEC @hr =
sp_OAGetProperty
@odrive,'TotalSize', @TotalSize OUT IF @hr <> 0 EXEC sp_OAGetErrorInfo
@odrive UPDATE #drives SET TotalSize=@TotalSize/@MB WHERE
drive=@drive FETCH NEXT FROM dcur INTO @drive
End
Close dcur
DEALLOCATE dcur
EXEC @hr=sp_OADestroy @fso IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
SELECT @@Servername as 'Server Name'
SELECT
drive, TotalSize as 'Total(MB)', FreeSpace as 'Free(MB)' FROM #drives
ORDER BY drive
DROP TABLE #drives
GO
also wondering if i can build a nice chart of all the databases with this query below:
sp_helpdb
thank you in advance
June 14, 2012 at 10:37 am
GO is a batch separator used in SSMS (and can actually be changed), it isn't a T-SQL command or statement.
June 14, 2012 at 10:50 am
Hello Lynn,
thank you for the fast reply, sadly i removed the Go part and now it says:
"An error occured while the query design method was being saved. Incorrect Syntax Near 'sp_helpdb'"
June 14, 2012 at 11:00 am
If this is what your code looks like:
SET NOCOUNT ON
DECLARE @hr INT
DECLARE @fso INT
DECLARE @drive CHAR(1)
DECLARE @odrive INT
DECLARE @TotalSize VARCHAR(20)
DECLARE @MB NUMERIC;
SET @MB = 1048576
CREATE TABLE #drives (
drive CHAR(1) PRIMARY KEY
,FreeSpace INT NULL
,TotalSize INT NULL
)
INSERT #drives (
drive
,FreeSpace
)
EXEC master.dbo.xp_fixeddrives
EXEC @hr = sp_OACreate 'Scripting.FileSystemObject'
,@fso OUT
IF @hr <> 0
EXEC sp_OAGetErrorInfo @fso
DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR
SELECT drive
FROM #drives
ORDER BY drive
OPEN dcur
FETCH NEXT
FROM dcur
INTO @drive
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC @hr = sp_OAMethod @fso
,'GetDrive'
,@odrive OUT
,@drive
IF @hr <> 0
EXEC sp_OAGetErrorInfo @fso
EXEC @hr = sp_OAGetProperty @odrive
,'TotalSize'
,@TotalSize OUT
IF @hr <> 0
EXEC sp_OAGetErrorInfo @odrive
UPDATE #drives
SET TotalSize = @TotalSize / @MB
WHERE drive = @drive
FETCH NEXT
FROM dcur
INTO @drive
END
CLOSE dcur
DEALLOCATE dcur
EXEC @hr = sp_OADestroy @fso
IF @hr <> 0
EXEC sp_OAGetErrorInfo @fso
SELECT @@Servername AS 'Server Name'
SELECT drive
,TotalSize AS 'Total(MB)'
,FreeSpace AS 'Free(MB)'
FROM #drives
ORDER BY drive
DROP TABLE #drives
sp_helpdb
The reason is because your call to the stored procedure sp_helpdb is not the first statement in your script.
You need to code it like this:
SET NOCOUNT ON
DECLARE @hr INT
DECLARE @fso INT
DECLARE @drive CHAR(1)
DECLARE @odrive INT
DECLARE @TotalSize VARCHAR(20)
DECLARE @MB NUMERIC;
SET @MB = 1048576
CREATE TABLE #drives (
drive CHAR(1) PRIMARY KEY
,FreeSpace INT NULL
,TotalSize INT NULL
)
INSERT #drives (
drive
,FreeSpace
)
EXEC master.dbo.xp_fixeddrives
EXEC @hr = sp_OACreate 'Scripting.FileSystemObject'
,@fso OUT
IF @hr <> 0
EXEC sp_OAGetErrorInfo @fso
DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR
SELECT drive
FROM #drives
ORDER BY drive
OPEN dcur
FETCH NEXT
FROM dcur
INTO @drive
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC @hr = sp_OAMethod @fso
,'GetDrive'
,@odrive OUT
,@drive
IF @hr <> 0
EXEC sp_OAGetErrorInfo @fso
EXEC @hr = sp_OAGetProperty @odrive
,'TotalSize'
,@TotalSize OUT
IF @hr <> 0
EXEC sp_OAGetErrorInfo @odrive
UPDATE #drives
SET TotalSize = @TotalSize / @MB
WHERE drive = @drive
FETCH NEXT
FROM dcur
INTO @drive
END
CLOSE dcur
DEALLOCATE dcur
EXEC @hr = sp_OADestroy @fso
IF @hr <> 0
EXEC sp_OAGetErrorInfo @fso
SELECT @@Servername AS 'Server Name'
SELECT drive
,TotalSize AS 'Total(MB)'
,FreeSpace AS 'Free(MB)'
FROM #drives
ORDER BY drive
DROP TABLE #drives
exec sp_helpdb
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply