March 19, 2009 at 9:20 am
I am trying to insert result of exec master.dbo.xp_restore_filelistonly into a temp table. The result has 140 files(mdf,ldf,ndf), Now I wan to SUM the size of ndf and ldf files and get total sise of data nad log file.
Here is the result
RowID LogicalName PhysicaName Type FileGrpName Size Maxsize
122Rev_logE:\SQLDATA_LOG\Rev_log.ldfLNULL626235801635184372080640
121Rev_Data E:\SQLDATA_DATA\Rev.mdfDPRIMARY10800332835184372080640
select sum([Size]) from #T where type='d'
select sum() from #T where type='L'
I get the following error
Msg 8117, Level 16, State 1, Line 29
Operand data type varchar is invalid for sum operator.
March 19, 2009 at 9:35 am
change the type of your size column to bigint or cast(size as bigint) on the column.
March 19, 2009 at 9:37 am
Cast it first, then sum
select sum(CAST [Size] AS DECIMAL(18,0)) from #T where type='d'
select sum(CAST AS DECIMAL(18,0)) from #T where type='L'
That should do it
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply