May 10, 2016 at 8:10 am
Hi Experts,
I have a stored proc below that i am trying to execute via sql server agent job.The job is failing and when i check the log it returns " Error converting data type nvarchar to numeric".Please help
create procedure [dbo].[sp_HostDriveStatusProcess]
(
@SiteName nvarchar(100),
@HostIP nvarchar(15),
@SystemDrive nvarchar(1),
@datadrive nvarchar(1),
@BackupDrive 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
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
)
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
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
drop table #Table
end
GO
May 10, 2016 at 8:51 am
The way I troubleshoot this kind of thing is to run each section of the query individually until I get the error that is being returned when I run the stored poc.
You would start with the first select statement. I would first include a line before that select statement that drops #table if it exists, this will help you while troubleshooting. Something like:
IF OBJECT_ID('tempdb..#Table') IS NOT NULL DROP TABLE #Table;
Next, comment out the "into #table" line and see you can execute it. If you get the error then you've isolated the problem, if note then uncomment the "into table line". Next, run your update statement. Again - if you get the error, you've isolated the problem, otherwise keep troubleshooting until you see which line of code is causing the error. Once you find the code causing the line that is causing "Error converting data type nvarchar to numeric" you will have to decide if the table needs to be changed to allow for nvarchar values or something like that. It's just basic debugging 101.
Once you've isolated the problem post what you've found for some more advice.
Lastly, on a side-note. That ORDER BY in your first select statement should be removed. It will only slow your query down.
-- Itzik Ben-Gan 2001
May 10, 2016 at 12:20 pm
Try this to get the rows causing errors.
SELECT
a.systemname AS HostName,
left(a.deviceid,1) AS DriveName,
a.drivesize,
a.freespace
FROM [dbo].[DriveStatus] AS a
WHERE Active = 1
AND try_cast(a.drivesize AS NUMERIC(13,2)) IS NULL;
May 11, 2016 at 1:47 am
Thank you it worked perfect
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply