Collation problem: varchar

Showing posts with label varchar. Show all posts

Sunday, March 25, 2012

Combining many records into 1

Using SQL 2000, how can you combine multiple records into 1?
The source data is varchar(255), the destination will be text. I need help
with the select statement.

example tables:
CREATE TABLE [NoteHeader] (
[NoteID] [int],
[CustomerID] [int] ,
[Desc1] [varchar] (255),
[Date] [datetime] ,
)
GO

CREATE TABLE [NoteDetail] (
[NoteId] [int],
[SeqNum] [int] NOT NULL ,
[Note1] [varchar] (255),
[Note2] [varchar] (255),
[Note3] [varchar] (255),
[Note4] [varchar] (255),
[Note5] [varchar] (255)
)
GO

Sample script joining tables:
SELECT *
FROM NoteHeader INNER JOIN
NoteDetail ON NoteHeader.NoteID = NoteDetail.NoteId

Sample results:
NoteID CustomerID Desc1 Date
Note1 Note2
....Note5
1111 987 Note Header Description 2007-07-15
Notes detail record 1 field 1 Notes detail record 1 field2 ....
1111 987 Note Header Description 2007-07-15
Notes detail record 2 field 1 Notes detail record 2 field 2

Desired results:
NoteID CustomerID Desc1 Date
CombinedNotes
1111 987 Note Header Description 2007-07-15
Notes detail record 1 field 1 +

Notes detail record 1 field2 +

Notes detail record 2 field 1 +

Notes detail record 2 field 2 +

through unlimited number of records up to 5
fields each

The NoteID field is the unique number. 1 record per NoteID in NoteHeader,
NoteDetail can have unlimited number of same NoteID (usually not more than
10)rdraider (rdraider@.sbcglobal.net) writes:

Quote:

Originally Posted by

Using SQL 2000, how can you combine multiple records into 1?
The source data is varchar(255), the destination will be text. I need
help with the select statement.

SQL Server MVP Anith Sen as a couple of methods on
http://www.projectdmx.com/tsql/rowconcatenate.aspx.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||rdraider (rdraider@.sbcglobal.net) writes:

Quote:

Originally Posted by

Thanks for the info. My problem is the resulting data will be too large
for varchar(8000). All these examples seem to use varchar(8000)
I need to convert to a text datatype. I can concat multiple varchar fields
from 1 record into text but the problem is how the source data is
structured.
The source data is from an app called 'Onyx' running SQL 6.5 (I'm naming
names !!). I upgraded the SQL 6.5 to SQL 2000. I don't hav SQL 2005.

I think you have two options:

1) Get SQL 2005.
2) Do it client-side.

I think you can do it on SQL 2000, but then you would have to run
a cursor, and use WRITETEXT and UPDATETEXT and it would be very very
painful. Please don't ask me to write the code for you, but if you
have problems with using WRITETEXT and UPDATETEXT, I can try to assist.

Quote:

Originally Posted by

I assume it was designed this way because SQL 6.5 largest data type was
varchar(255) ?

Yes, that is correct.

Tuesday, March 20, 2012

combine to varchar field when on is null

When we combine to varchar field when on is null,
like
Select FirstName+ LastName as FullName..
if FirstName is null, then FullName will be null.
How can we combine to varchar field when on is null, and the result will be
the non-null field?Select COALESCE(FirstName, '') + COALESCE(LastName, '') as FullName
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ad" <ad@.wfes.tcc.edu.tw> wrote in message news:OPgSvFCQFHA.3716@.TK2MSFTNGP14.phx.gbl...
> When we combine to varchar field when on is null,
> like
> Select FirstName+ LastName as FullName..
> if FirstName is null, then FullName will be null.
> How can we combine to varchar field when on is null, and the result will be
> the non-null field?
>
>|||Select ISNULL(FirstName,'')+ ISNULL(LastName,'')as FullName..
HTH, Jens Süßmeyer.
--
http://www.sqlserver2005.de
--
"ad" <ad@.wfes.tcc.edu.tw> schrieb im Newsbeitrag
news:OPgSvFCQFHA.3716@.TK2MSFTNGP14.phx.gbl...
> When we combine to varchar field when on is null,
> like
> Select FirstName+ LastName as FullName..
> if FirstName is null, then FullName will be null.
> How can we combine to varchar field when on is null, and the result will
> be
> the non-null field?
>
>

combine to varchar field when on is null

When we combine to varchar field when on is null,
like
Select FirstName+ LastName as FullName..
if FirstName is null, then FullName will be null.
How can we combine to varchar field when on is null, and the result will be
the non-null field?
Select COALESCE(FirstName, '') + COALESCE(LastName, '') as FullName
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ad" <ad@.wfes.tcc.edu.tw> wrote in message news:OPgSvFCQFHA.3716@.TK2MSFTNGP14.phx.gbl...
> When we combine to varchar field when on is null,
> like
> Select FirstName+ LastName as FullName..
> if FirstName is null, then FullName will be null.
> How can we combine to varchar field when on is null, and the result will be
> the non-null field?
>
>
|||Select ISNULL(FirstName,'')+ ISNULL(LastName,'')as FullName..
HTH, Jens Smeyer.
http://www.sqlserver2005.de
"ad" <ad@.wfes.tcc.edu.tw> schrieb im Newsbeitrag
news:OPgSvFCQFHA.3716@.TK2MSFTNGP14.phx.gbl...
> When we combine to varchar field when on is null,
> like
> Select FirstName+ LastName as FullName..
> if FirstName is null, then FullName will be null.
> How can we combine to varchar field when on is null, and the result will
> be
> the non-null field?
>
>

combine to varchar field when on is null

When we combine to varchar field when on is null,
like
Select FirstName+ LastName as FullName..
if FirstName is null, then FullName will be null.
How can we combine to varchar field when on is null, and the result will be
the non-null field?Select COALESCE(FirstName, '') + COALESCE(LastName, '') as FullName
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ad" <ad@.wfes.tcc.edu.tw> wrote in message news:OPgSvFCQFHA.3716@.TK2MSFTNGP14.phx.gbl...[vbc
ol=seagreen]
> When we combine to varchar field when on is null,
> like
> Select FirstName+ LastName as FullName..
> if FirstName is null, then FullName will be null.
> How can we combine to varchar field when on is null, and the result will b
e
> the non-null field?
>
>[/vbcol]|||Select ISNULL(FirstName,'')+ ISNULL(LastName,'')as FullName..
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"ad" <ad@.wfes.tcc.edu.tw> schrieb im Newsbeitrag
news:OPgSvFCQFHA.3716@.TK2MSFTNGP14.phx.gbl...
> When we combine to varchar field when on is null,
> like
> Select FirstName+ LastName as FullName..
> if FirstName is null, then FullName will be null.
> How can we combine to varchar field when on is null, and the result will
> be
> the non-null field?
>
>

Monday, March 19, 2012

Combine and

I have a table which has the following columns.

id - int(11)
catid - int(11)
title - varchar(60)
content - text
parent - int(11)
postdate - datetime
user - int(11)
view - int(11)
email - char(1)
emailed - char(1)

Here's what I'm trying to accomplish.

This table is for a forum. If a user posts a question and selects to be automatically email the email column will be set to 'Y'

So when a user responds to the post the emailed column will be 'Y'

Every hour or so I will do a cron job to send out an email to the original poster that he/she has a reply to their post.

I want to get a list of those id's that have email 'Y' and emailed = 'Y'

I can get those queries separtely below, but want to do it in one. how can I accomplish this...

my queries.

SELECT * FROM forum_tbl where parent = 0 and email = 'Y' // original post where user wants emails

SELECT * FROM forum_tbl where parent > 0 and emailed = 'N' // reply to post where email has not been sent.

I'm using MySQL 4.0.20a-max

Thank you.You can use the UNION of both selects to get the result set:

select ....
UNION
select ....

or you can use

SELECT *
FROM
forum_tbl
WHERE
(parent = 0 and email = 'Y') OR (parent > 0 and emailed = 'N')|||You can use the UNION of both selects to get the result set:

select ....
UNION
select ....

or you can use

SELECT *
FROM
forum_tbl
WHERE
(parent = 0 and email = 'Y') OR (parent > 0 and emailed = 'N')|||The outcome puts the two selects together but I need to eliminate some of the information, like an intersect, but I can't do that in MySQL.

The first select has the ID I want. The second had the parent ID.

I need to match only those.

Make sense?

Thanks.|||Can you post an examle of what you want done? a few records in the table and the result set you are looking for, maybe that way I can help out better.|||id catid title content parent postdate user views email emailed
19 3 test test con 0 2004-12-07 00:00:00 1 3 Y NULL

24 2 test again 0 2004-01-08 12:52:04 1 11 Y NULL
25 2 test ing 24 2004-12-08 00:00:00 1 0 NULL N

This is the outcome of the union your posted earlier.

The outcome I'm looking for is that it only should show id 24 nothing else because it is the only one that has a reply that has not been "emailed".

Thanks.

Thursday, March 8, 2012

Columns not updating from Stored procedure

Ok, so I've got the following stored procedure:

ALTER PROCEDURE dbo.tbUserPreferences_UpdateOrInsert

(
@.username varchar(50),
@.preferences varchar(300),
@.view_name varchar(300),
@.default_view varchar(10) = 'Y'
)

AS
UPDATE tbUserPreferences SET @.default_view='N' WHERE username=@.username

-- IF NOT EXISTS (
-- SELECT *
-- FROM tbUserPreferences
-- WHERE username=@.username
-- AND view_name=@.view_name
-- )
-- INSERT INTO tbUserPreferences (username, preferences,view_name,default_view) VALUES (@.username,@.preferences,@.view_name,@.default_view)
RETURN

The commented out section works fine, but the UPDATE line does not. I know there are columns that have "username=@.username", but this call is not updating their default_view column.

Please, if anybody knows why, let me in on the secret. Thanks!Try to execute and check result:

UPDATE tbUserPreferences SET @.default_view='N' WHERE username='your sp param'

select @.@.rowcount|||Wow, I'm so silly. And it took me looking at your reply to get it.

The code I ment to try was:

UPDATE tbUserPreferences SET default_view='N' WHERE username=@.username

"default_view" not "@.default_view". Thank you for the reply. Even though I didn't need to test your suggestion, it made me realize my problem. Thanks!|||It's still a good example of why you should error check your code...

Wednesday, March 7, 2012

Column update structure change

hi
I want to increase a varchar(5000)
to varchar(8000) on a table that
has approximately million rows....
What is the impact on the server

or any good recomendations of a action to accomplish this in the best and
fastest way.

thanks davepDave,

First of all, SQL Server maximum rowsize = 8,060 bytes. Make sure you keep
under that limit when you add the #bytes from all of the other columns.

Access to the table, and possibly other objects that are dependent on that
table will be blocked until the schema change is complete. Also, other
non-dependent processes will also be slowed down significantly. A million
row table on recent sever technology should not take an excessive amount of
time to alter.

-- Bill

"DaveP" <dvs_bis@.sbcglobal.netwrote in message
news:S3Jyh.59511$wc5.47614@.newssvr25.news.prodigy. net...

Quote:

Originally Posted by

hi
I want to increase a varchar(5000)
to varchar(8000) on a table that
has approximately million rows....
What is the impact on the server
>
or any good recomendations of a action to accomplish this in the best and
fastest way.
>
>
thanks davep
>
>

|||DaveP (dvs_bis@.sbcglobal.net) writes:

Quote:

Originally Posted by

I want to increase a varchar(5000)
to varchar(8000) on a table that
has approximately million rows....
What is the impact on the server

This is a metadata change, so it will be about instant.

Quote:

Originally Posted by

First of all, SQL Server maximum rowsize = 8,060 bytes. Make sure you
keep under that limit when you add the #bytes from all of the other
columns.

This applies to SQL 2000 only. On SQL 2005 rows can span pages. Not so
that you can have three char(8000) on the same page, but three varchar(8000)
is OK.

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns98D24AF9ACFYazorman@.127.0.0.1...

Quote:

Originally Posted by

DaveP (dvs_bis@.sbcglobal.net) writes:

Quote:

Originally Posted by

>I want to increase a varchar(5000)
>to varchar(8000) on a table that
>has approximately million rows....
>What is the impact on the server

>
This is a metadata change, so it will be about instant.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Column space utilization

Hello

I created a table with column name "description" as varchar(8000). My doubt is if I am not storing 8000 characters in this column, will SQL Server use memory space needed for 8000 characters ? or will it use only the space that needs for my text. ?

Thanking You
NavaneethIt will only use the space for your text.|||Thank you marcel,

I am planning to implement search functionality for my website. So choosing which datatype for search keywords will be optimum for me ? I need to store large number of data in this filed. I planned for text type. Is it a good choice ? Will text type utilize more space than any other datatype ? How much big my SQL database can grow ?|||You might was to look into using Full Text Search. Search in Book On Line for this topic.

Thanks,|||

Hello!

Moving forward you should NOT USE TEXT data types. TEXT has been obsoleted. VARCHAR(MAX) is the replacement.

Charles Hyman

Senior Consultant

MCTS SQL Server 2005

MCTS Biztalk Server

MCTS Vista Config

MCITP Database Administration

MCITP Database Developer

TALLAN Inc.

www.tallan.com

|||Thank you,

But upto my knowledge I think varchar can store only 8000 characters ? In new versions is it increased ?|||varchar(max) can store as much information as text. (so upto 2GB)

Thanks,

Friday, February 24, 2012

column name

Hi friends,

why the following does not work?

declare @.name VARCHAR(20)
set @.name = 'my_l_name'
select l_name as @.name from person

with regards,

You have to use dynamic sql for this:

DECLARE @.SQLSTRING VARCHAR(500)
declare @.name VARCHAR(20)
set @.name = 'my_l_name'
SET @.SQLSTRING = 'select l_name as ' + @.name + ' from person'
EXEC(@.SQLSTRING)

HTH, Jens Suessmeyer.

|||To add to Jens, check this out: http://www.sommarskog.se/dynamic_sql.html
--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de

column length reducing

The length of a column is 20(varchar),
When i m trying to execute select column name it gives all 20 characters.
My requirement is - is there any option by which i will be able to see only 10 characters ?select left(10,columnname)|||umm...think you go t it backwards...|||nope it's an anagram|||Originally posted by Karolyn
nope it's an anagram

Is anagram an alias for incorrect syntax:D

USE Northwind
GO

--SELECT LEFT(10,LastName) FROM Employees

SELECT LEFT(LastName,10) FROM Employees
GO|||My Bad !!!

Can blame it on another long day perhaps

Column length changes back to default automatically

Why is it that when I create a varchar column in a table through the
SQL Server 2005 Management Studio and assign it a length of, say, 10,
when I tab to the next field, the length is changed back to 50? If I
return to the column type field and correct it, it works fine.Because there is a bug in Management Studio. It will be fixed at the latest
in SQL Server 2008; in the meantime, you will either have to live with it,
or use CREATE TABLE in a query window instead of the table designer (which
has about 50 other bugs, FWIW). Here are three issues relating to your
problem:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127135
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=278074
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125878
The last one suggests the bug was being considered for SP2, but I don't
think it made the bar. One of the others suggests that it has been fixed in
an early Katmai CTP. Have you updated your client tools to SP2 or better?
What does Help|About... in Management Studio tell you (the first row under
the Versions column)?
A
"Richard Carpenter" <rumbledor@.hotmail.com> wrote in message
news:61b0eefa-15f3-475c-b173-76bf35e77c50@.p73g2000hsd.googlegroups.com...
> Why is it that when I create a varchar column in a table through the
> SQL Server 2005 Management Studio and assign it a length of, say, 10,
> when I tab to the next field, the length is changed back to 50? If I
> return to the column type field and correct it, it works fine.
>|||I'm sure that's it, Aaron. This is a new install of the client tools,
and I have yet to update them with SP2.
Thanks!
--
Richard Carpenter
On Mar 19, 7:42=A0pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> Because there is a bug in Management Studio. =A0It will be fixed at the la=test
> in SQL Server 2008; in the meantime, you will either have to live with it,=
> or use CREATE TABLE in a query window instead of the table designer (which=
> has about 50 other bugs, FWIW). =A0Here are three issues relating to your
> problem:
> https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?Fe...
> https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?Fe...
> https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?Fe...
> The last one suggests the bug was being considered for SP2, but I don't
> think it made the bar. =A0One of the others suggests that it has been fixe=d in
> an early Katmai CTP. =A0Have you updated your client tools to SP2 or bette=r?
> What does Help|About... in Management Studio tell you (the first row under=
> the Versions column)?
> A
> "Richard Carpenter" <rumble...@.hotmail.com> wrote in message
> news:61b0eefa-15f3-475c-b173-76bf35e77c50@.p73g2000hsd.googlegroups.com...
>
> > Why is it that when I create a varchar column in a table through the
> > SQL Server 2005 Management Studio and assign it a length of, say, 10,
> > when I tab to the next field, the length is changed back to 50? If I
> > return to the column type field and correct it, it works fine.- Hide quo=ted text -
> - Show quoted text -|||Richard , I could not reproduce the bug , can you tell me what did you do
step by step?
"Richard Carpenter" <rumbledor@.hotmail.com> wrote in message
news:7dd431ea-206f-40d1-bab4-34850956501f@.m44g2000hsc.googlegroups.com...
I'm sure that's it, Aaron. This is a new install of the client tools,
and I have yet to update them with SP2.
Thanks!
--
Richard Carpenter
On Mar 19, 7:42 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> Because there is a bug in Management Studio. It will be fixed at the
> latest
> in SQL Server 2008; in the meantime, you will either have to live with it,
> or use CREATE TABLE in a query window instead of the table designer (which
> has about 50 other bugs, FWIW). Here are three issues relating to your
> problem:
> https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?Fe...
> https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?Fe...
> https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?Fe...
> The last one suggests the bug was being considered for SP2, but I don't
> think it made the bar. One of the others suggests that it has been fixed
> in
> an early Katmai CTP. Have you updated your client tools to SP2 or better?
> What does Help|About... in Management Studio tell you (the first row under
> the Versions column)?
> A
> "Richard Carpenter" <rumble...@.hotmail.com> wrote in message
> news:61b0eefa-15f3-475c-b173-76bf35e77c50@.p73g2000hsd.googlegroups.com...
>
> > Why is it that when I create a varchar column in a table through the
> > SQL Server 2005 Management Studio and assign it a length of, say, 10,
> > when I tab to the next field, the length is changed back to 50? If I
> > return to the column type field and correct it, it works fine.- Hide
> > quoted text -
> - Show quoted text -|||Uri, I can reproduce this at will quite easily in Microsoft SQL Server
Management Studio 9.00.3042.00.
In any database, right-click "Tables" in Object Explorer, and select "New
Table..."
In Column Name type "foo", then hit Tab and in Data Type type "varchar(10)"
and hit Tab. varchar(10) should change to varchar(50).
A
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e1Xc5ZmiIHA.5780@.TK2MSFTNGP06.phx.gbl...
> Richard , I could not reproduce the bug , can you tell me what did you do
> step by step?|||Thank you Aaron, I was able reproduce it too. As I see it is happening only
for the first time ( also Click change to varchar(50)) I mean if you go back
and change it again to VARCHAR(10) then SQL Server DOES save it, am I
right?
PS. Microsoft SQL Server Management Studio 9.00.3042.00.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eVHhfqoiIHA.4436@.TK2MSFTNGP02.phx.gbl...
> Uri, I can reproduce this at will quite easily in Microsoft SQL Server
> Management Studio 9.00.3042.00.
> In any database, right-click "Tables" in Object Explorer, and select "New
> Table..."
> In Column Name type "foo", then hit Tab and in Data Type type
> "varchar(10)" and hit Tab. varchar(10) should change to varchar(50).
> A
>
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:e1Xc5ZmiIHA.5780@.TK2MSFTNGP06.phx.gbl...
>> Richard , I could not reproduce the bug , can you tell me what did you do
>> step by step?
>|||> Thank you Aaron, I was able reproduce it too. As I see it is happening
> only for the first time ( also Click change to varchar(50)) I mean if you
> go back and change it again to VARCHAR(10) then SQL Server DOES save it,
> am I right?
Yes, that seems right. But the user shouldn't have to override (50) twice.|||> Yes, that seems right. But the user shouldn't have to override (50)
> twice.
100% , I hope to see they fixed it in SP3 , I so hope......
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:upTjzwoiIHA.2304@.TK2MSFTNGP05.phx.gbl...
>> Thank you Aaron, I was able reproduce it too. As I see it is happening
>> only for the first time ( also Click change to varchar(50)) I mean if you
>> go back and change it again to VARCHAR(10) then SQL Server DOES save it,
>> am I right?
> Yes, that seems right. But the user shouldn't have to override (50)
> twice.
>

Column length changes back to default automatically

Why is it that when I create a varchar column in a table through the
SQL Server 2005 Management Studio and assign it a length of, say, 10,
when I tab to the next field, the length is changed back to 50? If I
return to the column type field and correct it, it works fine.
Because there is a bug in Management Studio. It will be fixed at the latest
in SQL Server 2008; in the meantime, you will either have to live with it,
or use CREATE TABLE in a query window instead of the table designer (which
has about 50 other bugs, FWIW). Here are three issues relating to your
problem:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127135
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=278074
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125878
The last one suggests the bug was being considered for SP2, but I don't
think it made the bar. One of the others suggests that it has been fixed in
an early Katmai CTP. Have you updated your client tools to SP2 or better?
What does Help|About... in Management Studio tell you (the first row under
the Versions column)?
A
"Richard Carpenter" <rumbledor@.hotmail.com> wrote in message
news:61b0eefa-15f3-475c-b173-76bf35e77c50@.p73g2000hsd.googlegroups.com...
> Why is it that when I create a varchar column in a table through the
> SQL Server 2005 Management Studio and assign it a length of, say, 10,
> when I tab to the next field, the length is changed back to 50? If I
> return to the column type field and correct it, it works fine.
>
|||I'm sure that's it, Aaron. This is a new install of the client tools,
and I have yet to update them with SP2.
Thanks!
Richard Carpenter
On Mar 19, 7:42Xpm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> Because there is a bug in Management Studio. XIt will be fixed at the latest
> in SQL Server 2008; in the meantime, you will either have to live with it,
> or use CREATE TABLE in a query window instead of the table designer (which
> has about 50 other bugs, FWIW). XHere are three issues relating to your
> problem:
> https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?Fe...
> https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?Fe...
> https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?Fe...
> The last one suggests the bug was being considered for SP2, but I don't
> think it made the bar. XOne of the others suggests that it has been fixed in
> an early Katmai CTP. XHave you updated your client tools to SP2 or better?
> What does Help|About... in Management Studio tell you (the first row under
> the Versions column)?
> A
> "Richard Carpenter" <rumble...@.hotmail.com> wrote in message
> news:61b0eefa-15f3-475c-b173-76bf35e77c50@.p73g2000hsd.googlegroups.com...
>
>
> - Show quoted text -
|||Richard , I could not reproduce the bug , can you tell me what did you do
step by step?
"Richard Carpenter" <rumbledor@.hotmail.com> wrote in message
news:7dd431ea-206f-40d1-bab4-34850956501f@.m44g2000hsc.googlegroups.com...
I'm sure that's it, Aaron. This is a new install of the client tools,
and I have yet to update them with SP2.
Thanks!
Richard Carpenter
On Mar 19, 7:42 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> Because there is a bug in Management Studio. It will be fixed at the
> latest
> in SQL Server 2008; in the meantime, you will either have to live with it,
> or use CREATE TABLE in a query window instead of the table designer (which
> has about 50 other bugs, FWIW). Here are three issues relating to your
> problem:
> https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?Fe...
> https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?Fe...
> https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?Fe...
> The last one suggests the bug was being considered for SP2, but I don't
> think it made the bar. One of the others suggests that it has been fixed
> in
> an early Katmai CTP. Have you updated your client tools to SP2 or better?
> What does Help|About... in Management Studio tell you (the first row under
> the Versions column)?
> A
> "Richard Carpenter" <rumble...@.hotmail.com> wrote in message
> news:61b0eefa-15f3-475c-b173-76bf35e77c50@.p73g2000hsd.googlegroups.com...
>
>
> - Show quoted text -
|||Uri, I can reproduce this at will quite easily in Microsoft SQL Server
Management Studio 9.00.3042.00.
In any database, right-click "Tables" in Object Explorer, and select "New
Table..."
In Column Name type "foo", then hit Tab and in Data Type type "varchar(10)"
and hit Tab. varchar(10) should change to varchar(50).
A
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e1Xc5ZmiIHA.5780@.TK2MSFTNGP06.phx.gbl...
> Richard , I could not reproduce the bug , can you tell me what did you do
> step by step?
|||Thank you Aaron, I was able reproduce it too. As I see it is happening only
for the first time ( also Click change to varchar(50)) I mean if you go back
and change it again to VARCHAR(10) then SQL Server DOES save it, am I
right?
PS. Microsoft SQL Server Management Studio 9.00.3042.00.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eVHhfqoiIHA.4436@.TK2MSFTNGP02.phx.gbl...
> Uri, I can reproduce this at will quite easily in Microsoft SQL Server
> Management Studio 9.00.3042.00.
> In any database, right-click "Tables" in Object Explorer, and select "New
> Table..."
> In Column Name type "foo", then hit Tab and in Data Type type
> "varchar(10)" and hit Tab. varchar(10) should change to varchar(50).
> A
>
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:e1Xc5ZmiIHA.5780@.TK2MSFTNGP06.phx.gbl...
>
|||> Thank you Aaron, I was able reproduce it too. As I see it is happening
> only for the first time ( also Click change to varchar(50)) I mean if you
> go back and change it again to VARCHAR(10) then SQL Server DOES save it,
> am I right?
Yes, that seems right. But the user shouldn't have to override (50) twice.
|||> Yes, that seems right. But the user shouldn't have to override (50)
> twice.
100% , I hope to see they fixed it in SP3 , I so hope......
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:upTjzwoiIHA.2304@.TK2MSFTNGP05.phx.gbl...
> Yes, that seems right. But the user shouldn't have to override (50)
> twice.
>

Column Lenght

What is the query to find the maximum lenght used in the
row of a column. Ex: The column is defined to be varchar
(2500) and the longest used row size is 1852.
T.I.ATry something like this:
SELECT MAX(LEN(ColumnName)) FROM TableName
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Jim" <anonymous@.discussions.microsoft.com> wrote in message
news:084701c52a72$a20d8300$a501280a@.phx.gbl...
> What is the query to find the maximum lenght used in the
> row of a column. Ex: The column is defined to be varchar
> (2500) and the longest used row size is 1852.
> T.I.A|||SELECT MAX(DATALENGTH(column_name)) FROM Table
--
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Jim" <anonymous@.discussions.microsoft.com> wrote in message
news:084701c52a72$a20d8300$a501280a@.phx.gbl...
> What is the query to find the maximum lenght used in the
> row of a column. Ex: The column is defined to be varchar
> (2500) and the longest used row size is 1852.
> T.I.A|||Thanks.........
>--Original Message--
>Try something like this:
>SELECT MAX(LEN(ColumnName)) FROM TableName
>--
>Vyas, MVP (SQL Server)
>SQL Server Articles and Code Samples @.
http://vyaskn.tripod.com/
>
>"Jim" <anonymous@.discussions.microsoft.com> wrote in
message
>news:084701c52a72$a20d8300$a501280a@.phx.gbl...
>> What is the query to find the maximum lenght used in the
>> row of a column. Ex: The column is defined to be varchar
>> (2500) and the longest used row size is 1852.
>> T.I.A
>
>.
>

Column Lenght

What is the query to find the maximum lenght used in the
row of a column. Ex: The column is defined to be varchar
(2500) and the longest used row size is 1852.
T.I.A
Try something like this:
SELECT MAX(LEN(ColumnName)) FROM TableName
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Jim" <anonymous@.discussions.microsoft.com> wrote in message
news:084701c52a72$a20d8300$a501280a@.phx.gbl...
> What is the query to find the maximum lenght used in the
> row of a column. Ex: The column is defined to be varchar
> (2500) and the longest used row size is 1852.
> T.I.A
|||SELECT MAX(DATALENGTH(column_name)) FROM Table
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Jim" <anonymous@.discussions.microsoft.com> wrote in message
news:084701c52a72$a20d8300$a501280a@.phx.gbl...
> What is the query to find the maximum lenght used in the
> row of a column. Ex: The column is defined to be varchar
> (2500) and the longest used row size is 1852.
> T.I.A
|||Thanks.........

>--Original Message--
>Try something like this:
>SELECT MAX(LEN(ColumnName)) FROM TableName
>--
>Vyas, MVP (SQL Server)
>SQL Server Articles and Code Samples @.
http://vyaskn.tripod.com/
>
>"Jim" <anonymous@.discussions.microsoft.com> wrote in
message
>news:084701c52a72$a20d8300$a501280a@.phx.gbl...
>
>.
>

Column Lenght

What is the query to find the maximum lenght used in the
row of a column. Ex: The column is defined to be varchar
(2500) and the longest used row size is 1852.
T.I.ATry something like this:
SELECT MAX(LEN(ColumnName)) FROM TableName
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Jim" <anonymous@.discussions.microsoft.com> wrote in message
news:084701c52a72$a20d8300$a501280a@.phx.gbl...
> What is the query to find the maximum lenght used in the
> row of a column. Ex: The column is defined to be varchar
> (2500) and the longest used row size is 1852.
> T.I.A|||SELECT MAX(DATALENGTH(column_name)) FROM Table
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Jim" <anonymous@.discussions.microsoft.com> wrote in message
news:084701c52a72$a20d8300$a501280a@.phx.gbl...
> What is the query to find the maximum lenght used in the
> row of a column. Ex: The column is defined to be varchar
> (2500) and the longest used row size is 1852.
> T.I.A|||Thanks.........

>--Original Message--
>Try something like this:
>SELECT MAX(LEN(ColumnName)) FROM TableName
>--
>Vyas, MVP (SQL Server)
>SQL Server Articles and Code Samples @.
http://vyaskn.tripod.com/
>
>"Jim" <anonymous@.discussions.microsoft.com> wrote in
message
>news:084701c52a72$a20d8300$a501280a@.phx.gbl...
>
>.
>

Thursday, February 16, 2012

Column Data truncation , how to identify column?

Hi There

This one has bothered me ever since sql server 2000.

When you do an insert into a table with literally hundreds of char or varchar columns and you get the error that the insert failed due to data loss/truncation on a column.

Is there anyway in 2005 to actually find out what column ? Since there are hundreds is is literally a long process of going though each column 1 by 1 manually.

The database engine surely MUST know what column this occurred on so why can it not tell you which column the truncation occurred on ?

Can this be done in 2005 if not will this information be available in 2008 ?

Thanx

Nope. It is not available on any version (2000,2005 & 2008).

The SQL Server message will be more generic than the specific, bcs the error caused by the engine treat all the object same, there is no special error handler written for object based.

Here you want to throw an error – for the table with specific column name. These are data definition & data error. These can be controlled.

|||

Hi Manivannan

Thank you for the reply, please could you elaborate on

"Here you want to throw an error - for the table with specific column name"

How exactly would one do that, as far as i know the try catch metod will return the same error without a column name ?

Thanx

|||

You can check insert operations like in following example :

The test table:

CREATE TABLE [dbo].[Atable](

Angel [varchar](5) NULL,

[varchar](5) NULL

) ON [secondary]

use following sp :

create procedure CheckInsert

@.i varchar(5000),@.j varchar(5000)

BEGIN

DECLARE @.COLLENGTH int

declare @.GoodRow bit

set @.COLLENGTH=0

set @.goodrow=1

select @.COLLENGTH =(SELECT sys.columns.max_length

FROM sys.columns INNER JOIN

sys.tables ON sys.columns.object_id = sys.tables.object_id

WHERE (sys.tables.name = N'ATABLE') and (sys.columns.name='a'))

if len(@.i)>@.collength

begin

print 'a has a big value'

print @.i

set @.goodrow=0

end

-- ...

if @.goodrow=1

INSERT INTO [test].[dbo].[Atable](,) VALUES (@.i,@.j)

END

if you run :

exec checkinsert '12345678','12'

the output is:

a has a big value

12345678

|||

Hi ggciubuc

Thanx for the reply.

Correct me if i am wrong but your proc will only check if you are inserting a value bigger than the max length of the largest column of a table.

So for example if i have a table with a hundred varchar columns most if which are over 100 in length , but my insert is inserting a char(6) value in to 1 of 30 char(5) columns it will still be very difficult to find the problem column. And your sql will not pick it up.

You sql will only find the issue if you are exceeding the length of your max char lenghth column, not any columns smaller than the max char length.

Thanx

|||

First you can optimize my sp creating a function let's say LengthColumn that return the max length of the column,

before insert you can verify all your 30 parameters and you can write a string by concatenating message like

'a has a big value'

and finally raise an error that write in event log

I thought your problem is , I quote

"When you do an insert into a table with literally hundreds of char or varchar columns and you get the error that the insert failed due to data loss/truncation on a column.

In your last post you say

"You sql will only find the issue if you are exceeding the length of your max char lenghth column, not any columns smaller than the max char length."

I think is not a problem vis-a-vis "data loss/truncation on a column".

Anyway in my code you can verify that length:

if len(@.i)>@.collength

begin

...

using

if len(@.i) < @.collength

begin

...

|||

Yes you are correct, the stored proc can be modified to check column by column.

It is just time consuming, i guess my main point was i thought it would be a simple things for the DB engine to actually return the column that the truncation was hapeening on, or some sort of easy way to figure out the column.

Even with your code it would be different for each table, and i have the issue of a result set of thousands of rows , so i dont know which insert is causing the problem.Therefore i would not know what parameters of which insert to pass to the store proc.

So it is a bit more complicated then i originally explained, bottom line there is no way sql server will tell you which column insert exceeded the length of the column , i was hoping there was an easy work around.

Thanx

Tuesday, February 14, 2012

column as result

Hello all,
I have this procedure
declare @.column varchar(200)
declare @.sql varchar(200)
SELECT @.column = lastname FROM Employee WHERE ID_employee = "3"
SET @.sql ='select * from '+@.column
EXEC (@.sql)
I have this mistake:
Invalid object name '<@.column>'
Could someone tell me why?
Inatry to run these and find the result
declare @.column varchar(200)
declare @.sql varchar(200)
SELECT @.column = lastname FROM Employee WHERE ID_employee = "3"
and see if you have a table or view by that result name.|||Are you sure the expected dynamic statement is valid. i.e does the @.column
value pass a valid object name .
What valie is returened from this statement ?
SELECT @.column = 'sysdatabases' FROM sysdatabases WHERE dbid = '8'
I just ran the following as a test , and it worked fine .
declare @.column varchar (100)
declare @.sql varchar(200)
SELECT @.column = 'sysdatabases' FROM sysdatabases WHERE dbid = '8'
SET @.sql ='select * from ' + @.column
EXEC (@.sql)
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"ina" <roberta.inalbon@.gmail.com> wrote in message
news:1144079096.559619.115510@.j33g2000cwa.googlegroups.com...
> Hello all,
> I have this procedure
> declare @.column varchar(200)
> declare @.sql varchar(200)
> SELECT @.column = lastname FROM Employee WHERE ID_employee = "3"
> SET @.sql ='select * from '+@.column
> EXEC (@.sql)
> I have this mistake:
> Invalid object name '<@.column>'
> Could someone tell me why?
> Ina
>|||Step through it:
/*Declare varialbes */
declare @.column varchar(200)
declare @.sql varchar(200)
/*The statement below sets the value of @.column to the (hopefully one)
value of lastname for the employee with an ID_employee value of 3 */
SELECT @.column = lastname FROM Employee WHERE ID_employee = "3"
/*Suppose that the value stored in @.column is now 'williams' */
SET @.sql ='select * from '+ @.column
/* The above evaluates to 'select * from williams', and 'williams' is
probably
not an object in your database. You could use the PRINT statement to
see what you would be EXECing */
PRINT @.sql
EXEC (@.sql)
"ina" wrote:

> Hello all,
> I have this procedure
> declare @.column varchar(200)
> declare @.sql varchar(200)
> SELECT @.column = lastname FROM Employee WHERE ID_employee = "3"
> SET @.sql ='select * from '+@.column
> EXEC (@.sql)
> I have this mistake:
> Invalid object name '<@.column>'
> Could someone tell me why?
> Ina
>|||yes thank you I have the result I can see all the table but if :
declare @.column varchar(200)
declare @.sql varchar(200)
SELECT @.column = lastname FROM Employee WHERE ID_employee = "3"
SET @.sql ='select firstname from employee where lastname='+@.column
EXEC (@.sql)
but I have this error now: Invalid column name 'pittet' (it is the last
name of an employee)
@.column needs to give me the last name of the employee ID number 3 and
sql need to give me the first name of the employee which lastname is
pittet.
ina|||SET @.sql ='select firstname from employee where lastname=''' + @.column +
''''
EXEC (@.sql)
p|||On 3 Apr 2006 09:23:44 -0700, ina wrote:

>yes thank you I have the result I can see all the table but if :
>declare @.column varchar(200)
>declare @.sql varchar(200)
>SELECT @.column = lastname FROM Employee WHERE ID_employee = "3"
>SET @.sql ='select firstname from employee where lastname='+@.column
>EXEC (@.sql)
>but I have this error now: Invalid column name 'pittet' (it is the last
>name of an employee)
>@.column needs to give me the last name of the employee ID number 3 and
>sql need to give me the first name of the employee which lastname is
>pittet.
Hi Ina,
I see that Rogas69 already posted a reply to show the error in your
code. But he or she didn't address your bigger error - why are you even
using dynamic SQL here? Why not do it in a single query?
If the requirement is to show the first name of every employee who has
the same last name as employee 3, then you could use
SELECT firstname
FROM employee
WHERE lastname = (SELECT lastname
FROM employee
WHERE ID_employee = '3')
And if that was not your requirement, then the code you posted (with the
correction posted by Rogas69) won't produce the required results.
Hugo Kornelis, SQL Server MVP|||Thank you all for these answers :)
I'd prefer to use dynamic SQL because I would like to understand how to
declare variable in a sql code; with your help I could understand more.
:)|||On 3 Apr 2006 23:57:03 -0700, ina wrote:

>Thank you all for these answers :)
>I'd prefer to use dynamic SQL because I would like to understand how to
>declare variable in a sql code; with your help I could understand more.
Hi Ina,
Okay. Just make sure that you read (and understand) everything on this
page: http://www.sommarskog.se/dynamic_sql.html.
Hugo Kornelis, SQL Server MVP

Sunday, February 12, 2012

Collations Problem

I have recently migrated a SQL Server 6.5 DB to SQL 2000.

On a particular table i added a new varchar ( [field29] - see below)

now when changing a record in this table, the performance is greatly reduced.
In SQL Enterprise manager, doing a return all rows, and then amending a record here, i get the following message :

"the entire resultset must be returned before this row can be updated. This operation is in progress and may take a long time due to the size of the result set".

The table has 300,000 records. The update takes about 20secs.

After this has completed, the performance is ok, as long as the window remains open. SQL Server memory also grows significantly. It appears that the entire recordset is cached.

Is this related to Collations?
([Field1] is the Primary Key)

any ideas?

CREATE TABLE [dbo].[Tabletest]
(
[field1] [varchar] (9) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL ,
[field2] [smallint] NOT NULL ,
[field3] [datetime] NOT NULL ,
[field4] [datetime] NULL ,
[field5] [datetime] NULL ,
[field6] [datetime] NULL ,
[field7] [varchar] (30) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL ,
[field8] [varchar] (30) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[field9] [varchar] (30) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[field10] [varchar] (250) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[field11] [varchar] (6) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL ,
[field12] [smallint] NOT NULL ,
[field13] [varchar] (6) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[field15] [smallint] NULL ,
[field16] [smallint] NULL ,
[field17] [smallint] NULL ,
[field18] [smallint] NULL ,
[field19] [smallint] NULL ,
[field20] [smallint] NULL ,
[field21] [smallint] NULL ,
[field22] [varchar] (60) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[field23] [smallint] NOT NULL ,
[field24] [bit] NOT NULL ,
[field25] [datetime] NULL ,
[field26] [varchar] (9) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[field27] [int] NOT NULL ,
[field28] [smallint] NULL ,
[field29] [varchar] (50) COLLATE SQL_Latin1_General_CP850_CI_AS NULL
)Use WHERE clause, you do not need to see 300,000 records when you are changing one :)

Friday, February 10, 2012

Collation question....

I did a test,
create table #a (char_set1 varchar(50) collate Latin1_General_CI_AS)
create table #b(char_set2 varchar(50)collate Latin1_General_BIN)
insert into #a values ('collate')
insert into #b values ('collate')
go
select * from #a where char_set1='Collate'
go
select * from #b where char_set2='Collate'
I see the displayed execution plan side by side, Case insensitive is
actually achieving the same speed as case sensitive,
even though case sensitive collation query (#a) did not return any row. If
performance is not an issue, why is most of db programmers
use collation Latin1_General_BIN instead of Latin1_General_CI_AS then?
Actually,it might help the performance if we use case insenstive,
this way we don't need to upper the column to force insensitive search.What makes you think that most programmers use "Latin1_General_BIN"?
The default US/Western collation for SQL Server 2000 is
Latin1_General_CI_AS.
Choose the collation that best meets your requirements. If your
searches are case insensitive then you should consider using
case-insensitive collations.
David Portas
SQL Server MVP
--|||Someone told me that case sensitive database is faster than case insensitive
database in some cases.
of course when you do case insensitive search, we should use
Latin1_General_CI_AS so you don't have to do upper the column.
But assume there is a table called "table1" and we always input upper-cased
data onto table.
so when you do a search, run this query against both collations:
select * from table1 where column1 like 'JOHN%'
in this case, is case sensitive collation faster?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1109794003.686267.290350@.l41g2000cwc.googlegroups.com...
> What makes you think that most programmers use "Latin1_General_BIN"?
> The default US/Western collation for SQL Server 2000 is
> Latin1_General_CI_AS.
> Choose the collation that best meets your requirements. If your
> searches are case insensitive then you should consider using
> case-insensitive collations.
> --
> David Portas
> SQL Server MVP
> --
>|||IT IS (SLIGHTLY) FASTER IN SOME CASES. IT IS WAY MORE TROUBLE TO DEAL WITH
IN ALMOST ALL CASES.
SO YOU STORE ALL DATA UPPER CASE? GROSS. THAT IS THE ONLY WAY TO MAKE THE
CASE SENSITIVE STUFF WORK BETTER, BUT THEN ALL OF YOUR OUTPUT LOOKS LIKE YOU
ARE MAD AT THE USER. IT WILL BE A BIT FASTER, BUT BEFORE I SET MY ENTIRE
DATABASE TO CASE INSENSITIVE, I WOULD RECONSIDER
UNLESS THIS IS NOT A USER ORIENTED APPLICAITON, MY ADVICE IS TO STORE THE
DATA AS YOU NEED IT, IN THE BEST POSSIBLE FORMAT, AND CHOOSE A COLLATION
THAT WORKS BEST. CASE INSENSITIVE, I mean, case insensitve is clearly the
best.
You can choose a binary collation for a single column, which may or may not
be good enough for your usage
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:%23gqmeU2HFHA.3500@.TK2MSFTNGP14.phx.gbl...
> Someone told me that case sensitive database is faster than case
> insensitive
> database in some cases.
> of course when you do case insensitive search, we should use
> Latin1_General_CI_AS so you don't have to do upper the column.
> But assume there is a table called "table1" and we always input
> upper-cased
> data onto table.
> so when you do a search, run this query against both collations:
> select * from table1 where column1 like 'JOHN%'
> in this case, is case sensitive collation faster?
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1109794003.686267.290350@.l41g2000cwc.googlegroups.com...
>