November 9, 2004 at 8:56 am
I have the following code that I run successfully in ISQL but when I try to run it as a stored procedure or in OSQL i get the following error.
Error converting data type varchar to numeric.
I can not figure out why it works fine in ISQL but not as a Proc or OSQL
select
substring(left(namespace, len(namespace)-1),len(namespace)+1 - patindex('%/%', reverse(left(namespace, len(namespace)-1))),len(namespace)+2 - patindex('%/%', reverse(left(namespace, len(namespace)-1))) ) 'Database',
mounted 'Mounted On',
currentsize 'Current Size',
nextextent 'Extent Size',
"maxsize" 'Max Size',
(currentsize/case "maxsize" when 0 then 9999999 else "maxsize" end) * 100 'Percent Full',
(case "maxsize" when 0 then 9999999 else "maxsize" end - currentsize)/(case "nextextent" when 0.0 then 0.000000000000000001 else "nextextent" end) 'Number of Extents',
convert(varchar(25),"datetime",101) 'Date Acquired',
"512-blocks" /2 /1024 'Total Disk',
"free" /2 /1024 'Free Disk Space' ,
(select sum("maxsize") from cache where LEFT (namespace, 3 + PATINDEX('%/%', RIGHT(NAMESPACE,LEN(NAMESPACE)-4))) = mounted) 'Potential Space used per Disk',
"free" /2 /1024 - ((select sum("maxsize") from cache where LEFT (namespace, 3 + PATINDEX('%/%', RIGHT(NAMESPACE,LEN(NAMESPACE)-4))) = mounted)- (select sum("currentsize") from cache where LEFT (namespace, 3 + PATINDEX('%/%', RIGHT(NAMESPACE,LEN(NAMESPACE)-4))) = mounted)) 'Potential Least Free Space Method 1'
from cache, adfout
where adfout.mounted = LEFT (namespace, 3 + PATINDEX('%/%', RIGHT(NAMESPACE,LEN(NAMESPACE)-4))) and convert(varchar(25),"datetime",101) = convert(varchar(25),getdate(),101)
and convert(varchar(25),"datetime",101)=convert(varchar(25),getdate(),101)
and convert(varchar(25),"filedate",101)=convert(varchar(25),getdate(),101)
order by adfout.mounted
November 9, 2004 at 10:24 am
Check your connection settings. Might be different in ISQL from defaults. Are you sure the data sets are the same? Default db, etc. the same?
November 10, 2004 at 12:05 pm
Especially look at the "Quoted Identifier" setting for your stored procedure. The column names such as "free" may be causing the error.
So long, and thanks for all the fish,
Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply