how to shrink tran-logs of databases

hi,
work on sql server 2000.
I have serveral dbs that are either 'full' or 'simple',
their tran-logs have lots of empty space in it due to one-
time big processes, hence, I want to shrink their physical
size down.
I used the below query on several dbs, some log size got
shrinked, some didn't shrink at all. how come? I didn't
perform a backup log operation for any of the db before I
run this query:
use db1
DBCC SHRINKFILE (db1_log1_logical_name)
many thanks.
JJ
HI,
You need to perform a backup log if the database recovery model is FULL or
BULK_LOGGED. Otherwise the inactive trasnaction logs will not be removed and
though your shrinkfile command will shrink. in case if you donot need the
trasnactions you could truncate the logs
backup log <dbname> with truncate_only
go
DBCC SHRINKFILE (db1_log1_logical_name,truncateonly)
Now execute the below command to see log file size and usage.
DBCC SQLPERF(LOGSPACE)
Thanks
Hari
MCDBA
"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:28e4a01c46551$c5a68f00$a601280a@.phx.gbl...
> hi,
> work on sql server 2000.
> I have serveral dbs that are either 'full' or 'simple',
> their tran-logs have lots of empty space in it due to one-
> time big processes, hence, I want to shrink their physical
> size down.
> I used the below query on several dbs, some log size got
> shrinked, some didn't shrink at all. how come? I didn't
> perform a backup log operation for any of the db before I
> run this query:
> use db1
> DBCC SHRINKFILE (db1_log1_logical_name)
> many thanks.
> JJ
|||hey Hari, it's you again! thanks for all the help. I
think you are definately right.
one more question, I also want to shrink this db's data
file which is total 21 gig, with over 9 gig free. I want
to shrink off those 9 gigs. I started by shrinking down
25 mg only, it took 11 minutes! my God, for 9 gig, it
will take 66 hrs then!
how come so slow?
thanks
JJ
>--Original Message--
>HI,
>You need to perform a backup log if the database recovery
model is FULL or
>BULK_LOGGED. Otherwise the inactive trasnaction logs will
not be removed and
>though your shrinkfile command will shrink. in case if
you donot need the
>trasnactions you could truncate the logs
>backup log <dbname> with truncate_only
>go
> DBCC SHRINKFILE (db1_log1_logical_name,truncateonly)
>Now execute the below command to see log file size and
usage.
>DBCC SQLPERF(LOGSPACE)
>Thanks
>Hari
>MCDBA
>
>"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:28e4a01c46551$c5a68f00$a601280a@.phx.gbl...
one-[vbcol=seagreen]
physical[vbcol=seagreen]
I
>
>.
>
|||JJ,
9GB of free spacein a 21GB DB is not too much. You need free space for a
database to operate properly anyway for things such as reindexing and you
always want room to add more data without growing. In any case the reason
it takes so long is that it shrinks from the end of the file and must move
any data that is near the end to a free spot at the beginning. This process
is resource intensive and can take a while depending on your hardware and if
the log files are on their own raid array or not. 11 minutes for only 25MB
does seem a little excessive but if your disks are poorly configured it's
not out of the question. Shrinking the file will also fragment your data
files and you may want to reindex afterwards. But if you shrink it too much
that will cause it to grow again<g>.
Andrew J. Kelly SQL MVP
"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:2a18601c4655a$809f44c0$a301280a@.phx.gbl...[vbcol=seagreen]
> hey Hari, it's you again! thanks for all the help. I
> think you are definately right.
> one more question, I also want to shrink this db's data
> file which is total 21 gig, with over 9 gig free. I want
> to shrink off those 9 gigs. I started by shrinking down
> 25 mg only, it took 11 minutes! my God, for 9 gig, it
> will take 66 hrs then!
> how come so slow?
> thanks
> JJ
> model is FULL or
> not be removed and
> you donot need the
> usage.
> message
> one-
> physical
> I
|||Hi,
Add on to Andrews detailed description, please shrink the MDF file in Single
user mode and during reindexing make the database recovery model to
BULK_LOGGED.
Alter database <dbname> set single_user with rollback immediate
go
dbcc shrinkfile('logical_mdf_name',size_in_mb)
go
Now reindex
go
-- Multi user
Alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:#e9ZYzVZEHA.212@.TK2MSFTNGP12.phx.gbl...
> JJ,
> 9GB of free spacein a 21GB DB is not too much. You need free space for a
> database to operate properly anyway for things such as reindexing and you
> always want room to add more data without growing. In any case the reason
> it takes so long is that it shrinks from the end of the file and must move
> any data that is near the end to a free spot at the beginning. This
process
> is resource intensive and can take a while depending on your hardware and
if
> the log files are on their own raid array or not. 11 minutes for only 25MB
> does seem a little excessive but if your disks are poorly configured it's
> not out of the question. Shrinking the file will also fragment your data
> files and you may want to reindex afterwards. But if you shrink it too
much
> that will cause it to grow again<g>.
> --
> Andrew J. Kelly SQL MVP
>
> "JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
> news:2a18601c4655a$809f44c0$a301280a@.phx.gbl...
>
|||Don't EVER leave the transaction log on 'full' recovery - it will absolutely eat your disks. ALWAYS have the default for ALL databases to be on 'simple'.
"JJ Wang" wrote:

> hi,
> work on sql server 2000.
> I have serveral dbs that are either 'full' or 'simple',
> their tran-logs have lots of empty space in it due to one-
> time big processes, hence, I want to shrink their physical
> size down.
> I used the below query on several dbs, some log size got
> shrinked, some didn't shrink at all. how come? I didn't
> perform a backup log operation for any of the db before I
> run this query:
> use db1
> DBCC SHRINKFILE (db1_log1_logical_name)
> many thanks.
> JJ
>
|||That is absolutely wrong advise. The reason it eats up your disks is that
you are not doing regular log backups to keep it in check. If you need to
have the ability to recover up to the minute there is no choice but to use
Full recovery mode.
Andrew J. Kelly SQL MVP
"Beeeeeeeeeeeeves" <Beeeeeeeeeeeeves@.discussions.microsoft.com> wrote in
message news:EF5BCB8B-0C14-4728-A2B3-D3E8907D7171@.microsoft.com...
> Don't EVER leave the transaction log on 'full' recovery - it will
absolutely eat your disks. ALWAYS have the default for ALL databases to be
on 'simple'.[vbcol=seagreen]
> "JJ Wang" wrote:
|||hi Andrew,
you sure always have good advices. The reason I want to
shrink this files was that we had a one time temperaroy
data expansion, this db's size should stay pretty constant
because we have a history archive db for it. and this is
just one of the files taht we need to shrink, there are
more files that have more unneeded space.
we have tran-log disk (1,0) seperate from our data disk
(raid 5). what do you mean by 'if the disk is poorly
designed'? how to judge that?
also, you mentioned: 'it takes so long is that it shrinks
from the end of the file and must move any data that is
near the end to a free spot at the beginning. ', is there
an option that we don't move the data around when
shrinking?
many thanks.
JJ

>--Original Message--
>JJ,
>9GB of free spacein a 21GB DB is not too much. You need
free space for a
>database to operate properly anyway for things such as
reindexing and you
>always want room to add more data without growing. In
any case the reason
>it takes so long is that it shrinks from the end of the
file and must move
>any data that is near the end to a free spot at the
beginning. This process
>is resource intensive and can take a while depending on
your hardware and if
>the log files are on their own raid array or not. 11
minutes for only 25MB
>does seem a little excessive but if your disks are poorly
configured it's
>not out of the question. Shrinking the file will also
fragment your data
>files and you may want to reindex afterwards. But if you
shrink it too much
>that will cause it to grow again<g>.
>--
>Andrew J. Kelly SQL MVP
>
>"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:2a18601c4655a$809f44c0$a301280a@.phx.gbl...
want[vbcol=seagreen]
recovery[vbcol=seagreen]
will[vbcol=seagreen]
in[vbcol=seagreen]
or 'simple',[vbcol=seagreen]
got[vbcol=seagreen]
didn't[vbcol=seagreen]
before
>
>.
>
|||thanks Andrew.
backup the log doesn't shrink its physical size though.
is the best way to shrink a log is to do it right after
the backup log job (maybe as the next step in the backup
log job)?
JJ
>--Original Message--
>That is absolutely wrong advise. The reason it eats up
your disks is that
>you are not doing regular log backups to keep it in
check. If you need to
>have the ability to recover up to the minute there is no
choice but to use
>Full recovery mode.
>--
>Andrew J. Kelly SQL MVP
>
>"Beeeeeeeeeeeeves"
<Beeeeeeeeeeeeves@.discussions.microsoft.com> wrote in
>message news:EF5BCB8B-0C14-4728-A2B3-
D3E8907D7171@.microsoft.com...[vbcol=seagreen]
recovery - it will
>absolutely eat your disks. ALWAYS have the default for
ALL databases to be[vbcol=seagreen]
>on 'simple'.
or 'simple',[vbcol=seagreen]
one-[vbcol=seagreen]
physical[vbcol=seagreen]
got[vbcol=seagreen]
didn't[vbcol=seagreen]
before I
>
>.
>
|||I totally agree with Andrew. but thanks anyway. :-)
JJ
>--Original Message--
>Don't EVER leave the transaction log on 'full' recovery -
it will absolutely eat your disks. ALWAYS have the default
for ALL databases to be on 'simple'.[vbcol=seagreen]
>"JJ Wang" wrote:
one-[vbcol=seagreen]
physical[vbcol=seagreen]
got[vbcol=seagreen]
didn't[vbcol=seagreen]
I
>.
>
sql

How to shrink db

We have a SQL 2005 server. There is a db which isabout 20g, but about 80%
of it is empty. How can I shrink the data file not using the GUI?Hello,
Take a look into DBCC SHRINKFILE and DBCC SHRINKDB in books online.
DBCC SHRINKFILE - Allows you to shrink the database file level
DBCC SHRINKDB - Shrinks the database [both data and log files]
Thanks
Hari
"news.microsoft.com" <Xeon@.donotemailme.com> wrote in message
news:eYuc6nzFHHA.3616@.TK2MSFTNGP06.phx.gbl...
> We have a SQL 2005 server. There is a db which isabout 20g, but about 80%
> of it is empty. How can I shrink the data file not using the GUI?
>|||Is it normal for DBCC SHRINKFILE to take a long time? I have 20G DB which
is 80% empty space.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eqW6%23g0FHHA.3976@.TK2MSFTNGP05.phx.gbl...
> Hello,
> Take a look into DBCC SHRINKFILE and DBCC SHRINKDB in books online.
> DBCC SHRINKFILE - Allows you to shrink the database file level
> DBCC SHRINKDB - Shrinks the database [both data and log files]
> Thanks
> Hari
> "news.microsoft.com" <Xeon@.donotemailme.com> wrote in message
> news:eYuc6nzFHHA.3616@.TK2MSFTNGP06.phx.gbl...
>> We have a SQL 2005 server. There is a db which isabout 20g, but about
>> 80% of it is empty. How can I shrink the data file not using the GUI?
>>
>|||Hello,
It is faster. I will recommend to shrink in smaller size. First shrink to 15
GB then to 10 GB and finally to 5 GB.
Thanks
Hari
"news.microsoft.com" <Xeon@.donotemailme.com> wrote in message
news:ec76tC1FHHA.1784@.TK2MSFTNGP06.phx.gbl...
> Is it normal for DBCC SHRINKFILE to take a long time? I have 20G DB which
> is 80% empty space.
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eqW6%23g0FHHA.3976@.TK2MSFTNGP05.phx.gbl...
>> Hello,
>> Take a look into DBCC SHRINKFILE and DBCC SHRINKDB in books online.
>> DBCC SHRINKFILE - Allows you to shrink the database file level
>> DBCC SHRINKDB - Shrinks the database [both data and log files]
>> Thanks
>> Hari
>> "news.microsoft.com" <Xeon@.donotemailme.com> wrote in message
>> news:eYuc6nzFHHA.3616@.TK2MSFTNGP06.phx.gbl...
>> We have a SQL 2005 server. There is a db which isabout 20g, but about
>> 80% of it is empty. How can I shrink the data file not using the GUI?
>>
>>
>

How to shrink db

We have a SQL 2005 server. There is a db which isabout 20g, but about 80%
of it is empty. How can I shrink the data file not using the GUI?
Hello,
Take a look into DBCC SHRINKFILE and DBCC SHRINKDB in books online.
DBCC SHRINKFILE - Allows you to shrink the database file level
DBCC SHRINKDB - Shrinks the database [both data and log files]
Thanks
Hari
"news.microsoft.com" <Xeon@.donotemailme.com> wrote in message
news:eYuc6nzFHHA.3616@.TK2MSFTNGP06.phx.gbl...
> We have a SQL 2005 server. There is a db which isabout 20g, but about 80%
> of it is empty. How can I shrink the data file not using the GUI?
>
|||Is it normal for DBCC SHRINKFILE to take a long time? I have 20G DB which
is 80% empty space.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eqW6%23g0FHHA.3976@.TK2MSFTNGP05.phx.gbl...
> Hello,
> Take a look into DBCC SHRINKFILE and DBCC SHRINKDB in books online.
> DBCC SHRINKFILE - Allows you to shrink the database file level
> DBCC SHRINKDB - Shrinks the database [both data and log files]
> Thanks
> Hari
> "news.microsoft.com" <Xeon@.donotemailme.com> wrote in message
> news:eYuc6nzFHHA.3616@.TK2MSFTNGP06.phx.gbl...
>
|||Hello,
It is faster. I will recommend to shrink in smaller size. First shrink to 15
GB then to 10 GB and finally to 5 GB.
Thanks
Hari
"news.microsoft.com" <Xeon@.donotemailme.com> wrote in message
news:ec76tC1FHHA.1784@.TK2MSFTNGP06.phx.gbl...
> Is it normal for DBCC SHRINKFILE to take a long time? I have 20G DB which
> is 80% empty space.
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eqW6%23g0FHHA.3976@.TK2MSFTNGP05.phx.gbl...
>

How to shrink db

We have a SQL 2005 server. There is a db which isabout 20g, but about 80%
of it is empty. How can I shrink the data file not using the GUI?Hello,
Take a look into DBCC SHRINKFILE and DBCC SHRINKDB in books online.
DBCC SHRINKFILE - Allows you to shrink the database file level
DBCC SHRINKDB - Shrinks the database [both data and log files]
Thanks
Hari
"news.microsoft.com" <Xeon@.donotemailme.com> wrote in message
news:eYuc6nzFHHA.3616@.TK2MSFTNGP06.phx.gbl...
> We have a SQL 2005 server. There is a db which isabout 20g, but about 80%
> of it is empty. How can I shrink the data file not using the GUI?
>|||Is it normal for DBCC SHRINKFILE to take a long time? I have 20G DB which
is 80% empty space.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eqW6%23g0FHHA.3976@.TK2MSFTNGP05.phx.gbl...
> Hello,
> Take a look into DBCC SHRINKFILE and DBCC SHRINKDB in books online.
> DBCC SHRINKFILE - Allows you to shrink the database file level
> DBCC SHRINKDB - Shrinks the database [both data and log files]
> Thanks
> Hari
> "news.microsoft.com" <Xeon@.donotemailme.com> wrote in message
> news:eYuc6nzFHHA.3616@.TK2MSFTNGP06.phx.gbl...
>|||Hello,
It is faster. I will recommend to shrink in smaller size. First shrink to 15
GB then to 10 GB and finally to 5 GB.
Thanks
Hari
"news.microsoft.com" <Xeon@.donotemailme.com> wrote in message
news:ec76tC1FHHA.1784@.TK2MSFTNGP06.phx.gbl...
> Is it normal for DBCC SHRINKFILE to take a long time? I have 20G DB which
> is 80% empty space.
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eqW6%23g0FHHA.3976@.TK2MSFTNGP05.phx.gbl...
>

How to show table header, footer even when the dataset is empty?

hi,

Just got a question about the report. When my report does not get any data from database, like dataset is empty, it will show nothing. This is reasonable. But what our customers want is, they still need to show the table header, footer, but no data.

Thanks.

Hi, what you desire appears to be the default behavior in RS 2005 unless NoRows is specified.

How to show table header, footer even when the dataset is empty?

hi,
Just got a question about the report. When my report does not get any
data from database, like dataset is empty, it will show nothing. This
is reasonable. But what our customers want is, they still need to show
the table header, footer, but no data.
ThanksHi Nick,
What is in the table headers and footers...dataset field values only
or possibly the header or footer has text? If you do have text in the
header or footer and they don't appear, check the NoRows property for
the table...any text or string value expression you set will cause the
table to not appear in favor of the expression you set. Text in
headers or footers does display, by default, even if a table has no
records...so you can also check for condittional logic that is hiding
the header or footer.
Maybe you can explain what your table headers and footers have in them?
If it's all dataset field vlaues-what shoud appear?
MattA
Reporting Services Newsletter at www.reportarchitex.com

How to show empty rows in crystal report when data reaches to EOF

I have designed a report in Crystal Report .Net with tabular Layout. I want to have table layout for empty rows in last page. But Report Detail Section is repeated only for record count, no more. How can I show blank rows in report?At the Report footer have a table structure with empty rows
Otherwise post some sample data and the format you want

How to set sql server setting let to set null value when a empty string (zero length)

How to set sql server setting let to set null value when a empty string
(zero length) set to column?
I want it automatic to set null value when maintain data is a zero-length
string press to column of table. for example,
when a statement as "Insert into table1 values ('abc', '', '')' become to
save abc, null and null three values to table1.There is no such setting in SQL Server. You could create a trigger which per
forms this, but I would
look for some alternate options instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ABC" <abc@.abc.com> wrote in message news:%230Fav01RGHA.2276@.tk2msftngp13.phx.gbl...darkred">
> How to set sql server setting let to set null value when a empty string (z
ero length) set to
> column?
> I want it automatic to set null value when maintain data is a zero-length
string press to column
> of table. for example,
> when a statement as "Insert into table1 values ('abc', '', '')' become to
save abc, null and null
> three values to table1.
>|||Would probaby have to create a trigger of the form (completely untested!)
CREATE TRIGGER NullifySomeEmptyStrings
ON table1
FOR AFTER INSERT, UPDATE
AS
--NULL out column2 if it now contains an empty string ''
IF UPDATE(column2)
BEGIN
UPDATE table1
SET column2 = NULL
WHERE table1.ThePrimaryKeyColumn IN
(SELECT ThePrimaryKeyColumn
FROM INSERTED
WHERE INSERTED.column2 = '')
END
"ABC" <abc@.abc.com> wrote in message
news:%230Fav01RGHA.2276@.tk2msftngp13.phx.gbl...
> How to set sql server setting let to set null value when a empty string
> (zero length) set to column?
> I want it automatic to set null value when maintain data is a zero-length
> string press to column of table. for example,
> when a statement as "Insert into table1 values ('abc', '', '')' become to
> save abc, null and null three values to table1.
>|||Why do you want it to be Null?
If you use Front End application, validate this and send valid data to
server
Madhivanan

How to set empty a space from a derived column expression?

hi,

This field comes from a flat file and sometimes own zero and sometimes own a empty position (after 'EUR'):

2006053000499236000005307700108287457080200408287452006052953990000000010000EUR
2006053000499236004414989200101423426004400501423422006052953990000000010000EUR0

[Column 12] == "0" ? [Column 12] : ?

TIA

What is the data type of [Column 12]?

-Jamie

|||

At destination?

[char](1) COLLATE Modern_Spanish_CI_AS NULL,

|||

No. In the pipeline.

If it is DT_STR then your expression will be:

[Column 12] == "0" ? [Column 12] : NULL(DT_STR,1,1252)

If it is DT_WSTR then it'll be

[Column 12] == "0" ? [Column 12] : NULL(DT_WSTR,1)

-Jamie

|||

I've got string [DT_STR] as datatype in my flat file definition and string [DT_WSTR] as datatype column on the right 'Expression' field (Derived Column)

I've allocated as you said this line:

[Column 12] == "0" ? [Column 12] : NULL(DT_WSTR,1)

And remains in black, ok but when I launch the package appears this:

Error at Camara Recibida [OLE DB Destination [9902]]: Columns "Derived Column 1_12" and "Rein" cannot convert between unicode and non-unicode string data types.

Error at Camara Recibida [DTS.Pipeline]: "component "OLE DB Destination" (9902)" failed validation and returned validation status "VS_ISBROKEN".

Error at Camara Recibida [DTS.Pipeline]: One or more component failed validation.

Error at Camara Recibida: There were errors during task validation.

|||

So you are inputting a DT_STR but outputting a DT_WSTR? Is that correct? That seems like a strange thing to do to me but of course, it is entirely up to you. If it were me I would use the same type on the output as on the input - but it is your choice.

If you want to output a DT_STR then the expression should be:

[Column 12] == "0" ? [Column 12] : NULL(DT_STR,1,1252)

If you want to output a DT_WSTR then the expression should be:

[Column 12] == "0" ? (DT_WSTR, 1)[Column 12] : NULL(DT_WSTR,1)

-Jamie

P.S. Please try and include all relevant information when posting a problem. If you are trying to do some data conversion then one of the most obvious things to state is the types of the input columns and what types you require the output to have.

|||

That's fine now.

Thanks a lot indeed for your help.

How to set a value to TEXT column

Hi, everybody,
I tried, to paste a clipboard content into an empty text column,
and then to show the content of the text column
by using a select command, but unfortunatelly the original text was
truncated.
does anyone know, how to correctly set a value into a text type column
using standard SQL Server tools such as Enterprise manager or Query
analyzer (but w i t h o u t writting an insert/update command)?
thanks
Libor
"Libor Forejtnik" <lforejtn@.seznam.cz> wrote in message
news:clqta15kb0graa8rt7ongplh6e5cekn0ok@.4ax.com...
> does anyone know, how to correctly set a value into a text type column
> using standard SQL Server tools such as Enterprise manager or Query
> analyzer (but w i t h o u t writting an insert/update command)?
Sorry, it's not possible. Those tools are not meant to be used for data
entry. You'll have to write INSERTs/UPDATEs.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net

How to set "0" instead of blank spaces to a column or field.

Hi i hv a doubt in Sql server reporting..I do generate some reports based on some criteria.In the results screen i hv empty fields based on the search i hv generated.I need to set "0" instead of blank spaces in the fields..Can any one help me?

What about using either using SQL Server logic to tranform the data or use reporting services to encapsulate the logic

CASE LEN(LTRIM(RTRIM(SomeColumn))) WHEN 0 THEN 0 ELSE SomeColumn END

would be the SQL Server alternative

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||I do use a select query from a view in my .rdl file.While selecting i need to set "0" to some columns which ever is null.Will it works?|||

CASE LEN(LTRIM(RTRIM(ISNULL(SomeColumn,'')))) WHEN 0 THEN 0 ELSE SomeColumn END

Jens K. Suessmeyer

http://www.sqlserver2005.de

How to set "0" instead of blank spaces to a column or field.

Hi i hv a doubt in Sql server reporting..I do generate some reports based on some criteria.In the results screen i hv empty fields based on the search i hv generated.I need to set "0" instead of blank spaces in the fields..Can any one help me?

What about using either using SQL Server logic to tranform the data or use reporting services to encapsulate the logic

CASE LEN(LTRIM(RTRIM(SomeColumn))) WHEN 0 THEN 0 ELSE SomeColumn END

would be the SQL Server alternative

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||I do use a select query from a view in my .rdl file.While selecting i need to set "0" to some columns which ever is null.Will it works?|||

CASE LEN(LTRIM(RTRIM(ISNULL(SomeColumn,'')))) WHEN 0 THEN 0 ELSE SomeColumn END

Jens K. Suessmeyer

http://www.sqlserver2005.de