how to free the memory occupied by "blob" in MS sql server

i'm working in microsoft sql server and i got following problem:

I have a text files Asia.txt in E:\ folder with some data in it as shown below

Asia.txt

1, Mizuho, Fukushima, Tokyo
2, Minika, Pang, Taipei
3, Jen, Ambelang, India
4, Jiang, Hong, Shangai
5, Ada, Koo, HongKong

And I have a table Region, in the database Companies, as shown below.

1>CREATE TABLE REGION (ID INT,REGION VARCHAR(25),DATA varbinary(MAX))
2>GO

I queried all the data from Asia.txt, using the OPENROWSET function.

1>INSERT INTO REGION (ID, REGION, DATA)
2>SELECT 1 AS ID, 'ASIA' AS REGION,
3> * FROM OPENROWSET( BULK 'E:\Asia.txt',SINGLE_BLOB)
4>AS MYTABLE
5>GO

it occupied some memory then i deleted this record using follwoing query

1>DELETE REGION
2>GO

then it deletes the record successfully but memory is not getting freed

can anyone help me out on this problem

When you delete from SQL server the memory will not be removed until the database is truncated. If you want to store data temporarily create a temporary table with a prefix of #

CREATE TABLE #TEMPTABLE
|||

i want to remove one record from the table, and i did it by using "DELETE" statement with WHERE clause,

now i want to free memory which was occupied by recently deleted record.

what i know about TRUNCATE statement is that it deletes the whole table and we cannot use WHERE clause with TRUNCATE.

and i don't want to create temporary table.

so how can i use truncate?.

thanks for your suggestion

|||

Hi,

This problem make me concentrate to it ! I have the same problem, please read this article :

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=947545&SiteID=1

Althought I use temporary table, but, it does not free memory when I delete temp table, I do not know why ! For someone, they said that : "In AS, it free memory not well and Microsoft company does not anonounce this problem in a clear way !".

Can you tell me more,

Regards,

Tran Quang Phuong.

How to force 0's in place of null values..

Hi,
I am using an MDX query which generates null values,Instead of null values I
need to display 0 's.
I have tried the below options but none is working fine for me,
please give me any help/suggestion/URL.Its really urgent !!
=Iif( Fields!colname.Value = NULL,0,Fields!colname.Value)
=iif(IsDbNull(Fields!colname.Value) = True,0,Fields!colname.Value)
=Iif( Fields!colname.Value = Nothing,0,Fields!colname.Value)
Thanks and Regards,
Rajesh Yennam.
HA India.This what I use and it works fine.
Good Luck!
=iif(Sum(Fields!average.Value) is Nothing,0,Sum(Fields!average.Value))
"Rajesh Yennam" <RajeshYennam@.discussions.microsoft.com> wrote in
message news:RajeshYennam@.discussions.microsoft.com:
> Hi,
> I am using an MDX query which generates null values,Instead of null values I
> need to display 0 's.
> I have tried the below options but none is working fine for me,
> please give me any help/suggestion/URL.Its really urgent !!
> =Iif( Fields!colname.Value = NULL,0,Fields!colname.Value)
> =iif(IsDbNull(Fields!colname.Value) = True,0,Fields!colname.Value)
> =Iif( Fields!colname.Value = Nothing,0,Fields!colname.Value)
> Thanks and Regards,
> Rajesh Yennam.
> HA India.|||I asked the same thing in the Olap-news group
(microsoft.public.sqlserver.olap). Here are the answers I got:
***
tawargerip@.hotmail.com <tawargerip@.hotmail.com>:
use IIF( isempty(<Measure name>),0,<Measure name>)
you may need to create a calculated member for each measure like this
and
Cymryr <Cymryr@.hotmail.com>:
Function CoalesceEmpty is the right way to do it, see BOL
***
The CoalesceEmpty looks promising, but I haven't got around to implement it
yet.
Kaisa M. Lindahl
"Rajesh Yennam" <RajeshYennam@.discussions.microsoft.com> wrote in message
news:BE2BF79D-CDBC-488D-9B31-6F9132507717@.microsoft.com...
> Hi,
> I am using an MDX query which generates null values,Instead of null values
I
> need to display 0 's.
> I have tried the below options but none is working fine for me,
> please give me any help/suggestion/URL.Its really urgent !!
> =Iif( Fields!colname.Value = NULL,0,Fields!colname.Value)
> =iif(IsDbNull(Fields!colname.Value) = True,0,Fields!colname.Value)
> =Iif( Fields!colname.Value = Nothing,0,Fields!colname.Value)
> Thanks and Regards,
> Rajesh Yennam.
> HA India.|||Thanks for your quick response John.Actually this also not working for me. If
you find any alternative solution please post it.
Thanks & Regards,
Rajesh Yennam,
HA India.
"John Geddes" wrote:
> This what I use and it works fine.
> Good Luck!
> =iif(Sum(Fields!average.Value) is Nothing,0,Sum(Fields!average.Value))
>
>
> "Rajesh Yennam" <RajeshYennam@.discussions.microsoft.com> wrote in
> message news:RajeshYennam@.discussions.microsoft.com:
> > Hi,
> > I am using an MDX query which generates null values,Instead of null values I
> > need to display 0 's.
> > I have tried the below options but none is working fine for me,
> > please give me any help/suggestion/URL.Its really urgent !!
> >
> > =Iif( Fields!colname.Value = NULL,0,Fields!colname.Value)
> > =iif(IsDbNull(Fields!colname.Value) = True,0,Fields!colname.Value)
> > =Iif( Fields!colname.Value = Nothing,0,Fields!colname.Value)
> >
> > Thanks and Regards,
> > Rajesh Yennam.
> > HA India.
>
>|||Put the null detection code in a function in the Code element of the report.
The problem with IIF is that it evaluates all conditions and throws on NULL.
As simple if then else statement will work.
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rajesh Yennam" <RajeshYennam@.discussions.microsoft.com> wrote in message
news:CBD98285-010A-4EB9-8DFE-2DBF0875C898@.microsoft.com...
> Thanks for your quick response John.Actually this also not working for me.
> If
> you find any alternative solution please post it.
> Thanks & Regards,
> Rajesh Yennam,
> HA India.
> "John Geddes" wrote:
>> This what I use and it works fine.
>> Good Luck!
>> =iif(Sum(Fields!average.Value) is Nothing,0,Sum(Fields!average.Value))
>>
>>
>> "Rajesh Yennam" <RajeshYennam@.discussions.microsoft.com> wrote in
>> message news:RajeshYennam@.discussions.microsoft.com:
>> > Hi,
>> > I am using an MDX query which generates null values,Instead of null
>> > values I
>> > need to display 0 's.
>> > I have tried the below options but none is working fine for me,
>> > please give me any help/suggestion/URL.Its really urgent !!
>> >
>> > =Iif( Fields!colname.Value = NULL,0,Fields!colname.Value)
>> > =iif(IsDbNull(Fields!colname.Value) = True,0,Fields!colname.Value)
>> > =Iif( Fields!colname.Value = Nothing,0,Fields!colname.Value)
>> >
>> > Thanks and Regards,
>> > Rajesh Yennam.
>> > HA India.
>>