May 14, 2016 at 5:33 am
Good day Experts,
I am new to Sql and i have a stored proc with a colomn name @BackupDrive2 nvarchar(1).
The stored proc is fine as it is,However i was asked to add that BackupDrive2 to a query in ssrs report which i am not sure of.Please help and in advance.
Here is the stored proc below. and the query after the proc
CREATE procedure [dbo].[sp_HostDriveStatusProcess]
(
@SiteName nvarchar(100),
@HostIP nvarchar(15),
@SystemDrive nvarchar(1),
@datadrive nvarchar(1),
@BackupDrive nvarchar(1),
@BackupDrive2 nvarchar(1)
)
as
begin
set nocount on
select
a.systemname as HostName,
left(a.deviceid,1) as DriveName,
cast(cast(a.drivesize as numeric(20,2))/1024/1024/1024 as numeric(13,2)) as TotalSpace,
cast(cast(a.freespace as numeric(20,2))/1024/1024/1024 as numeric(13,2)) as FreeSpace
into #Table
from [dbo].[DriveStatus] as a
where Active = 1 and
drivesize not like '%empty%'
and freespace not like '%empty%'
order by
a.DeviceID
truncate table [dbo].[DriveStatus]
update [172.27.8.186].[SednaDBA].[dbo].[HostDriveStatus]
set Active = 0
where SiteName = @SiteName and HostIP = @HostIP and Active = 1
insert into [172.27.8.186].[SednaDBA].[dbo].[HostDriveStatus]
(SiteName, HostName, HostIP, SystemDriveName, SystemTotalSpace, SystemFreeSpace, SystemFreeSpacePercentage,
DataDriveName, DataTotalSpace, DataFreeSpace, DataFreeSpacePercentage,
BackupDriveName, BackupTotalSpace, BackupFreeSpace, BackupFreeSpacePercentage,
Backup2DriveName, Backup2TotalSpace, Backup2FreeSpace, Backup2FreeSpacePercentage
)
select
@SiteName as SiteName,
a.SystemHostName as HostName,
@HostIP as HostIP,
a.SystemDriveName,
a.SystemTotalSpace,
a.SystemFreeSpace,
cast(a.SystemFreeSpace/a.SystemTotalSpace as numeric(13,2)) as SystemFreeSpacePercentage,
b.DataDriveName,
b.DataTotalSpace,
b.DataFreeSpace,
cast(b.DataFreeSpace/b.DataTotalSpace as numeric(13,2)) as DataFreeSpacePercentage,
c.BackupDriveName,
c.BackupTotalSpace,
c.BackupFreeSpace,
cast(c.BackupFreeSpace/c.BackupTotalSpace as numeric(13,2)) as BackupFreeSpacePercentage,
d.BackupDrive2Name,
d.Backup2TotalSpace,
d.Backup2FreeSpace,
cast(d.Backup2FreeSpace/d.Backup2TotalSpace as numeric(13,2)) as Backup2FreeSpacePercentage
from
(
select
a.HostName as SystemHostName,
a.DriveName as SystemDriveName,
a.TotalSpace as SystemTotalSpace,
a.FreeSpace as SystemFreeSpace
from #Table as a
where
a.DriveName = @SystemDrive
) as a
join
(
select
a.HostName as DataHostName,
a.DriveName as DataDriveName,
a.TotalSpace as DataTotalSpace,
a.FreeSpace as DataFreeSpace
from #Table as a
where
a.DriveName = @datadrive
) as b on a.SystemHostName = b.DataHostName
join
(
select
a.HostName as BackupHostName,
a.DriveName as BackupDriveName,
a.TotalSpace as BackupTotalSpace,
a.FreeSpace as BackupFreeSpace
from #Table as a
where
a.DriveName = @BackupDrive
) as c on c.BackupHostName = a.SystemHostName
join
(
select
a.HostName as Backup2HostName,
a.DriveName as BackupDrive2Name,
a.TotalSpace as Backup2TotalSpace,
a.FreeSpace as Backup2FreeSpace
from #Table as a
where
a.DriveName = @BackupDrive2
) as d on d.Backup2HostName = a.SystemHostName
drop table #Table
end
GO
----The query is as follows
select
a.SiteName,
a.Hostname,
a.FreeSpace,
a.FreeSpacePerc,
a.Color,
b.TotalMemory,
'ProLiant DL585 G7' as Model
from
(
SELECT
[SiteName],
[Hostname],
[SystemDriveName] as DriveName,
cast([SystemFreeSpace] as nvarchar(10)) + ' GB (' + [SystemDriveName] + ':)' as FreeSpace,
cast(([SystemFreeSpacePercentage]*100) as nvarchar(10)) + '% (' + [SystemDriveName] + ':)' as FreeSpacePerc,
case when [SystemFreeSpace] <= 20 then 'RED' else 'BLACK' end as Color
FROM [SednaDBA].[dbo].[HostDriveStatus]
where
active = 1 and
Hostname like 'AC%'
union
select * from
(
SELECT
[SiteName],
[Hostname],
[DataDriveName],
cast([DataFreeSpace] as nvarchar(10)) + ' GB (' + [DataDriveName] + ':)' as FreeSpace,
cast(([DataFreeSpacePercentage]*100) as nvarchar(10)) + '% (' + [DataDriveName] + ':)' as FreeSpacePerc,
case when [DataFreeSpace] <= 20 then 'RED' else 'BLACK' end as Color
FROM [SednaDBA].[dbo].[HostDriveStatus]
where
active = 1 and
Hostname like 'AC%'
union
SELECT
[SiteName],
[Hostname],
[BackupDriveName],
cast([BackupFreeSpace] as nvarchar(10)) + ' GB (' + [BackupDriveName] + ':)' as FreeSpace,
cast(([BackupFreeSpacePercentage]*100) as nvarchar(10)) + '% (' + [BackupDriveName] + ':)' as FreeSpacePerc,
case when [BackupFreeSpace] <= 20 then 'RED' else 'BLACK' end as Color
FROM [SednaDBA].[dbo].[HostDriveStatus]
where
active = 1 and
Hostname like 'AC%'
) as a
) as a
join
(
SELECT
[SiteName],
[Hostname],
[TotalMemory] + ' GB' as TotalMemory
FROM [SednaDBA].[dbo].[HostMemoryCPUStatus]
where
active = 1 and
Hostname like 'AC%'
) as b on a.SiteName = a.SiteName and a.Hostname = b.Hostname
order by
a.[SiteName], DriveName
sorry for inconvinience.
May 14, 2016 at 9:50 am
Several things. First up, @Backupdrive2 is a parameter in the stored procedure, not a column. A parameter is a place holder that you use to pass in values to a stored procedure. It's not a column. If you look at the query within the stored procedure, I see @Backupdrive2 used once:
where
a.DriveName = @BackupDrive2
So, if you want to add a column to your query that represents the same data as might be used in @BackupDrive2, you need to reference the [dbo].[DriveStatus] table and use left(a.deviceid,1) because that's where the value gets loaded into the #Table temporary table.
Just add that to your query.
A few notes about the procedure. NVARCHAR(1) means you have a variable length strength with a length of one (1). That's actually not a variable length. It's not a length at all. You're wasting storeage (a very tiny amount) and processing power (again, a tiny amount) to no good purpose. Just using CHAR(1) would be fine. This:
drivesize not like '%empty%'
and freespace not like '%empty%'
Is going to lead to scans. Your performance can never be seriously improved. You're actually not really using the temp table for anything. You'd be better off looking at how to set up a Common Table Expression and using that to define the query inside the procedure.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 16, 2016 at 8:24 am
Thank you for your help
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply