May 28, 2008 at 11:08 am
I have a query that I use to load data into an auditing table used for disaster recovery. when the maxsize of a data file is '268435456' my script breaks because I multiply maxsize by 8 to get size in kb thus making the number too large for int data type. To get around this in the interm i removed the multiplication of max size but then i get another error that data type is incorrect but that error does not if i have the mutiplication part in there. have supplied copy of both scripts.
If you were to run this query on a system that does not have any databases or log files with a maxsize of '268435456' pages then it runs fine.
Anything that can help me understand why in one instance using 'unlimited' inplace of maxsize if fine but in the other case it is not, would be great. thanks.
----With Multiplication(original) ------------------------
EXEC master..sp_MSforeachdb 'USE ?
SELECT ''?'', name,
fileid,
filename,
filegroup_name(groupid),
CONVERT(nvarchar(15), size * 8) + N'' KB'',
CASE maxsize WHEN -1 THEN ''Unlimited''
else
CONVERT(nvarchar(15), maxsize * 8) + N'' KB'' end,
CASE status & 0x100000 WHEN 0x100000 THEN
CONVERT(nvarchar(3), growth) + N''%''
else
CONVERT(nvarchar(15), growth * 8) + N'' KB'' end,
CASE status & 0x40 WHEN 0x40 THEN ''log only'' else ''data only'' end
FROM sysfiles
ORDER BY fileid'
------------Without Multiplication ---------------------------
EXEC master..sp_MSforeachdb 'USE ?
SELECT ''?'', name,
fileid,
filename,
filegroup_name(groupid),
CONVERT(nvarchar(15), size * 8) + N'' KB'',
CASE maxsize WHEN -1 THEN ''Unlimited''
else
maxsize end,
CASE status & 0x100000 WHEN 0x100000 THEN
CONVERT(nvarchar(3), growth) + N''%''
else
CONVERT(nvarchar(15), growth * 8) + N'' KB'' end,
CASE status & 0x40 WHEN 0x40 THEN ''log only'' else ''data only'' end
FROM sysfiles
ORDER BY fileid'
May 28, 2008 at 2:15 pm
Ignoring the why, I just converted the "8" into a BIGINT to get the multiplication result to use the BIGINT data type rather than INT and the query works fine.
[font="Courier New"]EXEC master..sp_MSforeachdb 'USE ?
SELECT ''?'', name,
fileid,
filename,
filegroup_name(groupid),
CONVERT(nvarchar(15), size * CONVERT(BIGINT,8)) + N'' KB'',
CASE maxsize WHEN -1 THEN ''Unlimited''
else
CONVERT(nvarchar(15), maxsize * CONVERT(BIGINT,8)) + N'' KB'' end,
CASE status & 0x100000 WHEN 0x100000 THEN
CONVERT(nvarchar(3), growth) + N''%''
else
CONVERT(nvarchar(15), growth * CONVERT(BIGINT,8)) + N'' KB'' end,
CASE status & 0x40 WHEN 0x40 THEN ''log only'' else ''data only'' end
FROM sysfiles
ORDER BY fileid'[/font]
May 28, 2008 at 2:18 pm
On the other error, this case statement:
[font="Courier New"]CASE maxsize WHEN -1 THEN ''Unlimited''
else
maxsize end[/font]
is trying to return maxsize (an integer) or 'Unlimited' (a string). A column can only be of one data type.
If you make maxsize a varchar, this will work
[font="Courier New"]CASE maxsize WHEN -1 THEN ''Unlimited''
else
convert(varchar,maxsize) end[/font]
May 28, 2008 at 2:42 pm
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply