Combine multiple columns into one

I have a table Venues
ID int
Location1 char(10),
Location2 char(10),
Location3 char(10),
Location4 char(10)
and would like to have a query that returns a single column of
Locations i.e
for the record ID=2,Location1=Boston,Location2=NewYork,Location3= London,Location4=Paris
I would get the following result
Locations
Boston
NewYork
London
Paris
Is it possible to merge the values from columns location1,location2
etc into a new column?
SELECT
ID
,'Location 1 = ' + Location1 + ',Location 2 = ' + Location2 ...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"michael" <michael.l.obrien@.ul.ie> wrote in message news:6dcedfaf.0404220202.1a24b6af@.posting.google.c om...
> I have a table Venues
> ID int
> Location1 char(10),
> Location2 char(10),
> Location3 char(10),
> Location4 char(10)
> and would like to have a query that returns a single column of
> Locations i.e
> for the record ID=2,Location1=Boston,Location2=NewYork,Location3= London,Location4=Paris
> I would get the following result
> Locations
> --
> Boston
> NewYork
> London
> Paris
> Is it possible to merge the values from columns location1,location2
> etc into a new column?
|||On 22 Apr 2004 03:02:07 -0700, michael wrote:

>I have a table Venues
>ID int
>Location1 char(10),
>Location2 char(10),
>Location3 char(10),
>Location4 char(10)
>and would like to have a query that returns a single column of
>Locations i.e
>for the record ID=2,Location1=Boston,Location2=NewYork,Location3= London,Location4=Paris
>I would get the following result
>Locations
>--
>Boston
>NewYork
>London
>Paris
>Is it possible to merge the values from columns location1,location2
>etc into a new column?
SELECT Location1 AS Locations
FROM Venues
WHERE ID = 2
UNION ALL
SELECT Location2
FROM Venues
WHERE ID = 2
UNION ALL
SELECT Location3
FROM Venues
WHERE ID = 2
UNION ALL
SELECT Location4
FROM Venues
WHERE ID = 2
By the way, your design is not properly normalized. The
Venue-Locations should be in a seperate table.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||In response to "BTW, your design is not properly normalized..."
We really do not know what is being stored in the LOCATION columns.
If the 4 locations are somehow different - like location 1 is primary,
location 2 is secondary - then this design is fine in my book. If most of
the time the app in front of this table shows the 4 locations on one row,
then it's fine by me also. Normalization can and is often taken to way to
far a level.
I've seen "college" admin systems with 500 tables - so obsur that only the
original implementors have a clue as to what is going on.
In our K-12 student applications, we store all 4 marking period marks in one
row of a table. Each student/class has only one row, with all 4 marking
period marks within that row. In my book, they are different "entities",
thus this is properly normalized. I've had debates with other programmers
that they should be separated into a MARK table, underneath the
STUDENT/CLASS table. The STUDENT/CLASS table already typically has 50000+
rows per school per year - creating a sub-table with each marking period
mark, 200,000+ rows per year hurts my head.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:3n8f80548bs1ataiu9kunrtm5n008duuk6@.4ax.com... [vbcol=seagreen]
> On 22 Apr 2004 03:02:07 -0700, michael wrote:
ID=2,Location1=Boston,Location2=NewYork,Location3= London,Location4=Paris
> SELECT Location1 AS Locations
> FROM Venues
> WHERE ID = 2
> UNION ALL
> SELECT Location2
> FROM Venues
> WHERE ID = 2
> UNION ALL
> SELECT Location3
> FROM Venues
> WHERE ID = 2
> UNION ALL
> SELECT Location4
> FROM Venues
> WHERE ID = 2
>
> By the way, your design is not properly normalized. The
> Venue-Locations should be in a seperate table.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks to all for the help. Maybe I can clear up why I
have the table the way it is and apologise for not giving
enough details about what I am trying to do. I had a sit
down and figured out what I was trying to do after I
posted the first message.
My venues table is linked (VenueID) to a events table. The
venues table has a min of 30 locations i.e
location1...location30 and would like to have a sproc to
return all the non null locations for a particular event
ID without having to have at least 30 "select union"
sections with tests for null values
Here is my first draft of what I am thinking
--The variable have been declared @.inti=1,@.intj=2
--@.Column1 @.Column2 (both char(5)
--and have not added the null test yet
While @.intj <=35
Begin
Set @.Column1 ='Location'+ (cast(@.inti as char(2)))
Set @.Column2 ='Location'+ (cast(@.intj as char(2)))
SELECT @.Column1 Locations FROM Venues where
Venues.LocationID='200'
UNION
SELECT @.Column2 FROM Venues where Venues.LocationID='200'
Set @.inti =@.inti + 2
Set @.intj =@.intj + 2
End
--This only results in the following
Locations
Location1
Location2
Locations
Location3
Location4
Any suggestions
>--Original Message--
>In response to "BTW, your design is not properly
normalized..."
>We really do not know what is being stored in the
LOCATION columns.
>If the 4 locations are somehow different - like location
1 is primary,
>location 2 is secondary - then this design is fine in my
book. If most of
>the time the app in front of this table shows the 4
locations on one row,
>then it's fine by me also. Normalization can and is
often taken to way to
>far a level.
>I've seen "college" admin systems with 500 tables - so
obsur that only the
>original implementors have a clue as to what is going on.
>In our K-12 student applications, we store all 4 marking
period marks in one
>row of a table. Each student/class has only one row,
with all 4 marking
>period marks within that row. In my book, they are
different "entities",
>thus this is properly normalized. I've had debates with
other programmers
>that they should be separated into a MARK table,
underneath the
>STUDENT/CLASS table. The STUDENT/CLASS table already
typically has 50000+
>rows per school per year - creating a sub-table with each
marking period
>mark, 200,000+ rows per year hurts my head.
>"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in
message[vbcol=seagreen]
>news:3n8f80548bs1ataiu9kunrtm5n008duuk6@.4ax.com.. .
column of
>ID=2,Location1=Boston,Location2=NewYork,Location3 =London,L
ocation4=Paris[vbcol=seagreen]
location1,location2
>
>.
>
sqlsql

Combine multiple columns into one

I have a table Venues
ID int
Location1 char(10),
Location2 char(10),
Location3 char(10),
Location4 char(10)
and would like to have a query that returns a single column of
Locations i.e
for the record ID=2,Location1=Boston,Location2=NewYork,
Location3=London,Loca
tion4=Paris
I would get the following result
Locations
--
Boston
NewYork
London
Paris
Is it possible to merge the values from columns location1,location2
etc into a new column?SELECT
ID
,'Location 1 = ' + Location1 + ',Location 2 = ' + Location2 ...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"michael" <michael.l.obrien@.ul.ie> wrote in message news:6dcedfaf.0404220202.1a24b6af@.postin
g.google.com...
> I have a table Venues
> ID int
> Location1 char(10),
> Location2 char(10),
> Location3 char(10),
> Location4 char(10)
> and would like to have a query that returns a single column of
> Locations i.e
> for the record ID=2,Location1=Boston,Location2=NewYork,
Location3=London,Lo
cation4=Paris
> I would get the following result
> Locations
> --
> Boston
> NewYork
> London
> Paris
> Is it possible to merge the values from columns location1,location2
> etc into a new column?|||On 22 Apr 2004 03:02:07 -0700, michael wrote:

>I have a table Venues
>ID int
>Location1 char(10),
>Location2 char(10),
>Location3 char(10),
>Location4 char(10)
>and would like to have a query that returns a single column of
>Locations i.e
>for the record ID=2,Location1=Boston,Location2=NewYork,
Location3=London,Loc
ation4=Paris
>I would get the following result
>Locations
>--
>Boston
>NewYork
>London
>Paris
>Is it possible to merge the values from columns location1,location2
>etc into a new column?
SELECT Location1 AS Locations
FROM Venues
WHERE ID = 2
UNION ALL
SELECT Location2
FROM Venues
WHERE ID = 2
UNION ALL
SELECT Location3
FROM Venues
WHERE ID = 2
UNION ALL
SELECT Location4
FROM Venues
WHERE ID = 2
By the way, your design is not properly normalized. The
Venue-Locations should be in a seperate table.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||In response to "BTW, your design is not properly normalized..."
We really do not know what is being stored in the LOCATION columns.
If the 4 locations are somehow different - like location 1 is primary,
location 2 is secondary - then this design is fine in my book. If most of
the time the app in front of this table shows the 4 locations on one row,
then it's fine by me also. Normalization can and is often taken to way to
far a level.
I've seen "college" admin systems with 500 tables - so obsur that only the
original implementors have a clue as to what is going on.
In our K-12 student applications, we store all 4 marking period marks in one
row of a table. Each student/class has only one row, with all 4 marking
period marks within that row. In my book, they are different "entities",
thus this is properly normalized. I've had debates with other programmers
that they should be separated into a MARK table, underneath the
STUDENT/CLASS table. The STUDENT/CLASS table already typically has 50000+
rows per school per year - creating a sub-table with each marking period
mark, 200,000+ rows per year hurts my head.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:3n8f80548bs1ataiu9kunrtm5n008duuk6@.
4ax.com...
> On 22 Apr 2004 03:02:07 -0700, michael wrote:
>
ID=2,Location1=Boston,Location2=NewYork,
Location3=London,Location4=Paris[vbcol=s
eagreen]
> SELECT Location1 AS Locations
> FROM Venues
> WHERE ID = 2
> UNION ALL
> SELECT Location2
> FROM Venues
> WHERE ID = 2
> UNION ALL
> SELECT Location3
> FROM Venues
> WHERE ID = 2
> UNION ALL
> SELECT Location4
> FROM Venues
> WHERE ID = 2
>
> By the way, your design is not properly normalized. The
> Venue-Locations should be in a seperate table.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks to all for the help. Maybe I can clear up why I
have the table the way it is and apologise for not giving
enough details about what I am trying to do. I had a sit
down and figured out what I was trying to do after I
posted the first message.
My venues table is linked (VenueID) to a events table. The
venues table has a min of 30 locations i.e
location1...location30 and would like to have a sproc to
return all the non null locations for a particular event
ID without having to have at least 30 "select union"
sections with tests for null values
Here is my first draft of what I am thinking
--
--The variable have been declared @.inti=1,@.intj=2
--@.Column1 @.Column2 (both char(5)
--and have not added the null test yet
While @.intj <=35
Begin
Set @.Column1 ='Location'+ (cast(@.inti as char(2)))
Set @.Column2 ='Location'+ (cast(@.intj as char(2)))
SELECT @.Column1 Locations FROM Venues where
Venues.LocationID='200'
UNION
SELECT @.Column2 FROM Venues where Venues.LocationID='200'
Set @.inti =@.inti + 2
Set @.intj =@.intj + 2
End
--This only results in the following
Locations
Location1
Location2
Locations
Location3
Location4
Any suggestions
>--Original Message--
>In response to "BTW, your design is not properly
normalized..."
>We really do not know what is being stored in the
LOCATION columns.
>If the 4 locations are somehow different - like location
1 is primary,
>location 2 is secondary - then this design is fine in my
book. If most of
>the time the app in front of this table shows the 4
locations on one row,
>then it's fine by me also. Normalization can and is
often taken to way to
>far a level.
>I've seen "college" admin systems with 500 tables - so
obsur that only the
>original implementors have a clue as to what is going on.
>In our K-12 student applications, we store all 4 marking
period marks in one
>row of a table. Each student/class has only one row,
with all 4 marking
>period marks within that row. In my book, they are
different "entities",
>thus this is properly normalized. I've had debates with
other programmers
>that they should be separated into a MARK table,
underneath the
>STUDENT/CLASS table. The STUDENT/CLASS table already
typically has 50000+
>rows per school per year - creating a sub-table with each
marking period
>mark, 200,000+ rows per year hurts my head.
>"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in
message
> news:3n8f80548bs1ataiu9kunrtm5n008duuk6@.
4ax.com...
column of[vbcol=seagreen]
> ID=2,Location1=Boston,Location2=NewYork,
Location3=London,L
ocation4=Paris
location1,location2[vbcol=seagreen]
>
>.
>

Combine multiple columns into one

I have a table Venues
ID int
Location1 char(10),
Location2 char(10),
Location3 char(10),
Location4 char(10)
and would like to have a query that returns a single column of
Locations i.e
for the record ID=2,Location1=Boston,Location2=NewYork,Location3=London,Location4=Paris
I would get the following result
Locations
--
Boston
NewYork
London
Paris
Is it possible to merge the values from columns location1,location2
etc into a new column?SELECT
ID
,'Location 1 = ' + Location1 + ',Location 2 = ' + Location2 ...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"michael" <michael.l.obrien@.ul.ie> wrote in message news:6dcedfaf.0404220202.1a24b6af@.posting.google.com...
> I have a table Venues
> ID int
> Location1 char(10),
> Location2 char(10),
> Location3 char(10),
> Location4 char(10)
> and would like to have a query that returns a single column of
> Locations i.e
> for the record ID=2,Location1=Boston,Location2=NewYork,Location3=London,Location4=Paris
> I would get the following result
> Locations
> --
> Boston
> NewYork
> London
> Paris
> Is it possible to merge the values from columns location1,location2
> etc into a new column?|||On 22 Apr 2004 03:02:07 -0700, michael wrote:
>I have a table Venues
>ID int
>Location1 char(10),
>Location2 char(10),
>Location3 char(10),
>Location4 char(10)
>and would like to have a query that returns a single column of
>Locations i.e
>for the record ID=2,Location1=Boston,Location2=NewYork,Location3=London,Location4=Paris
>I would get the following result
>Locations
>--
>Boston
>NewYork
>London
>Paris
>Is it possible to merge the values from columns location1,location2
>etc into a new column?
SELECT Location1 AS Locations
FROM Venues
WHERE ID = 2
UNION ALL
SELECT Location2
FROM Venues
WHERE ID = 2
UNION ALL
SELECT Location3
FROM Venues
WHERE ID = 2
UNION ALL
SELECT Location4
FROM Venues
WHERE ID = 2
By the way, your design is not properly normalized. The
Venue-Locations should be in a seperate table.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||In response to "BTW, your design is not properly normalized..."
We really do not know what is being stored in the LOCATION columns.
If the 4 locations are somehow different - like location 1 is primary,
location 2 is secondary - then this design is fine in my book. If most of
the time the app in front of this table shows the 4 locations on one row,
then it's fine by me also. Normalization can and is often taken to way to
far a level.
I've seen "college" admin systems with 500 tables - so obsur that only the
original implementors have a clue as to what is going on.
In our K-12 student applications, we store all 4 marking period marks in one
row of a table. Each student/class has only one row, with all 4 marking
period marks within that row. In my book, they are different "entities",
thus this is properly normalized. I've had debates with other programmers
that they should be separated into a MARK table, underneath the
STUDENT/CLASS table. The STUDENT/CLASS table already typically has 50000+
rows per school per year - creating a sub-table with each marking period
mark, 200,000+ rows per year hurts my head.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:3n8f80548bs1ataiu9kunrtm5n008duuk6@.4ax.com...
> On 22 Apr 2004 03:02:07 -0700, michael wrote:
> >I have a table Venues
> >ID int
> >Location1 char(10),
> >Location2 char(10),
> >Location3 char(10),
> >Location4 char(10)
> >
> >and would like to have a query that returns a single column of
> >Locations i.e
> >for the record
ID=2,Location1=Boston,Location2=NewYork,Location3=London,Location4=Paris
> >I would get the following result
> >
> >Locations
> >--
> >Boston
> >NewYork
> >London
> >Paris
> >
> >Is it possible to merge the values from columns location1,location2
> >etc into a new column?
> SELECT Location1 AS Locations
> FROM Venues
> WHERE ID = 2
> UNION ALL
> SELECT Location2
> FROM Venues
> WHERE ID = 2
> UNION ALL
> SELECT Location3
> FROM Venues
> WHERE ID = 2
> UNION ALL
> SELECT Location4
> FROM Venues
> WHERE ID = 2
>
> By the way, your design is not properly normalized. The
> Venue-Locations should be in a seperate table.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks to all for the help. Maybe I can clear up why I
have the table the way it is and apologise for not giving
enough details about what I am trying to do. I had a sit
down and figured out what I was trying to do after I
posted the first message.
My venues table is linked (VenueID) to a events table. The
venues table has a min of 30 locations i.e
location1...location30 and would like to have a sproc to
return all the non null locations for a particular event
ID without having to have at least 30 "select union"
sections with tests for null values
Here is my first draft of what I am thinking
--
--The variable have been declared @.inti=1,@.intj=2
--@.Column1 @.Column2 (both char(5)
--and have not added the null test yet
While @.intj <=35
Begin
Set @.Column1 ='Location'+ (cast(@.inti as char(2)))
Set @.Column2 ='Location'+ (cast(@.intj as char(2)))
SELECT @.Column1 Locations FROM Venues where
Venues.LocationID='200'
UNION
SELECT @.Column2 FROM Venues where Venues.LocationID='200'
Set @.inti =@.inti + 2
Set @.intj =@.intj + 2
End
--This only results in the following
Locations
Location1
Location2
Locations
Location3
Location4
Any suggestions
>--Original Message--
>In response to "BTW, your design is not properly
normalized..."
>We really do not know what is being stored in the
LOCATION columns.
>If the 4 locations are somehow different - like location
1 is primary,
>location 2 is secondary - then this design is fine in my
book. If most of
>the time the app in front of this table shows the 4
locations on one row,
>then it's fine by me also. Normalization can and is
often taken to way to
>far a level.
>I've seen "college" admin systems with 500 tables - so
obsur that only the
>original implementors have a clue as to what is going on.
>In our K-12 student applications, we store all 4 marking
period marks in one
>row of a table. Each student/class has only one row,
with all 4 marking
>period marks within that row. In my book, they are
different "entities",
>thus this is properly normalized. I've had debates with
other programmers
>that they should be separated into a MARK table,
underneath the
>STUDENT/CLASS table. The STUDENT/CLASS table already
typically has 50000+
>rows per school per year - creating a sub-table with each
marking period
>mark, 200,000+ rows per year hurts my head.
>"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in
message
>news:3n8f80548bs1ataiu9kunrtm5n008duuk6@.4ax.com...
>> On 22 Apr 2004 03:02:07 -0700, michael wrote:
>> >I have a table Venues
>> >ID int
>> >Location1 char(10),
>> >Location2 char(10),
>> >Location3 char(10),
>> >Location4 char(10)
>> >
>> >and would like to have a query that returns a single
column of
>> >Locations i.e
>> >for the record
>ID=2,Location1=Boston,Location2=NewYork,Location3=London,L
ocation4=Paris
>> >I would get the following result
>> >
>> >Locations
>> >--
>> >Boston
>> >NewYork
>> >London
>> >Paris
>> >
>> >Is it possible to merge the values from columns
location1,location2
>> >etc into a new column?
>> SELECT Location1 AS Locations
>> FROM Venues
>> WHERE ID = 2
>> UNION ALL
>> SELECT Location2
>> FROM Venues
>> WHERE ID = 2
>> UNION ALL
>> SELECT Location3
>> FROM Venues
>> WHERE ID = 2
>> UNION ALL
>> SELECT Location4
>> FROM Venues
>> WHERE ID = 2
>>
>> By the way, your design is not properly normalized. The
>> Venue-Locations should be in a seperate table.
>> Best, Hugo
>> --
>> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
>.
>

Column max size for String

Hello I currently am using the nvarchar type which has a maximum size of 4000 char's. Is there any other type that I can use which can store a greater amount of char's ?

Many thanks

GrantVarchar can go up to 8000. You can usetext orntext fields which have no practical upper limit on the number of chars. Withtext orntext fields fields you lose some flexibility such as the ability to use thetext orntext field in a where clause.

text is for variable-length non-Unicode data.

ntext is for variable-length Unicode data.|||text datatypes can hold upto 2GB bytes of data ... And ntext also used the same limitation ... The only difference being text datatypes use 1 bytes for 1 character and ntext types use 2 bytes for storing single unicode character ...

Column datatype change

We have an 18 GB table in a 39 GB database. In this table,
4 of the columns declared as char(255) and we want to
change these 4 columns to a varchar(255) to save some
space (It saves about ~3 GB).
We also have only 8 GB left on the hard drive. When I
tryed to run it, it ran out of disk space. There are 3
other columns in the table and primary key and an index on
these columns (Not the ones we are trying to change).
How can I change the datatypes of these columns without
running out of space ?. We will add more space but not
immediatelly.
I did put the database on 'Simple' mode before I ran the
query but no help.
Thanks for any suggestions.I do not think there is much you can do unless you get more disk
space...Perhaps you could copy it to another server?
Drop all indexes and do the work, then re-create the indexes... That should
give you some space... ( what a pain tho)
"Andy" <anonymous@.discussions.microsoft.com> wrote in message
news:8c6f01c3e9bb$b04df100$a501280a@.phx.gbl...> We have an 18 GB table in a
39 GB database. In this table,

quote:

---

> 4 of the columns declared as char(255) and we want to
> change these 4 columns to a varchar(255) to save some
> space (It saves about ~3 GB).
> We also have only 8 GB left on the hard drive. When I
> tryed to run it, it ran out of disk space. There are 3
> other columns in the table and primary key and an index on
> these columns (Not the ones we are trying to change).
> How can I change the datatypes of these columns without
> running out of space ?. We will add more space but not
> immediatelly.
> I did put the database on 'Simple' mode before I ran the
> query but no help.
> Thanks for any suggestions.

---

Column datatype change

We have an 18 GB table in a 39 GB database. In this table,
4 of the columns declared as char(255) and we want to
change these 4 columns to a varchar(255) to save some
space (It saves about ~3 GB).
We also have only 8 GB left on the hard drive. When I
tryed to run it, it ran out of disk space. There are 3
other columns in the table and primary key and an index on
these columns (Not the ones we are trying to change).
How can I change the datatypes of these columns without
running out of space ?. We will add more space but not
immediatelly.
I did put the database on 'Simple' mode before I ran the
query but no help.
Thanks for any suggestions.I do not think there is much you can do unless you get more disk
space...Perhaps you could copy it to another server?
Drop all indexes and do the work, then re-create the indexes... That should
give you some space... ( what a pain tho)
"Andy" <anonymous@.discussions.microsoft.com> wrote in message
news:8c6f01c3e9bb$b04df100$a501280a@.phx.gbl...> We have an 18 GB table in a
39 GB database. In this table,
> 4 of the columns declared as char(255) and we want to
> change these 4 columns to a varchar(255) to save some
> space (It saves about ~3 GB).
> We also have only 8 GB left on the hard drive. When I
> tryed to run it, it ran out of disk space. There are 3
> other columns in the table and primary key and an index on
> these columns (Not the ones we are trying to change).
> How can I change the datatypes of these columns without
> running out of space ?. We will add more space but not
> immediatelly.
> I did put the database on 'Simple' mode before I ran the
> query but no help.
> Thanks for any suggestions.

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](

[varchar](5) NULL,

[varchar](5) NULL

) ON [secondary]

use following sp :

create procedure CheckInsert

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

as

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

...

|||

Hi

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

Collation problem

Personally I would do a Cyrillic_General_CI_AS to Unicode,
then Unicode to the english translastion.
I would make up a new database with all your char, varchar
and text set to nchar, nvarchar and ntext then copy it
over using DTS.
Then you use the data in the new table to copy over to the
english collation.
Warning though with Unicode, it takes double the space, so
the maximum you can have is 4000 characters as opposed to
8000, so check the maximum sizes before you do it.
Peter
"Age is an issue of mind over matter. If you don't mind,
it doesn't matter."
Mark Twain

>--Original Message--
>Hi,
>I have database in russia with collation:
>Cyrillic_General_CI_AS
>Now I would like to transport data to English database
with english
>collation.
>I would like to convert cyrillic text to latin text, that
can be rad from
>english program.
>What is the best way to do that?
>Thank you,
>Simon
>
>.
>Hi, Peter
I don't want to copy cyrilic text into my database - so, I don't need
nvarchar data type.
I would like to convert cyrlic to latin and then insert into varchar type.
So, that is some kind of translation of data.
Do you have any idea?
lp,S
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:5a0c01c523d0$b46eb0d0$a401280a@.phx.gbl...
> Personally I would do a Cyrillic_General_CI_AS to Unicode,
> then Unicode to the english translastion.
> I would make up a new database with all your char, varchar
> and text set to nchar, nvarchar and ntext then copy it
> over using DTS.
> Then you use the data in the new table to copy over to the
> english collation.
> Warning though with Unicode, it takes double the space, so
> the maximum you can have is 4000 characters as opposed to
> 8000, so check the maximum sizes before you do it.
> Peter
> "Age is an issue of mind over matter. If you don't mind,
> it doesn't matter."
> Mark Twain
>
> with english
> can be rad from|||Hello,
The reason I sugested nchar, nvarchar and ntext is that it
is collation independant, basically its designed to what
you want in converting to different collations.
Have a look at 'Using Unicode Data' in BOL.
So the plan was to convert it into Unicode, then convert
than into Latin.
Your numbers should remain the same regardless of which
collation.
Peter
"There is no such thing as public opinion. There is only
published opinion."
Winston Churchill

>--Original Message--
>Hi, Peter
>I don't want to copy cyrilic text into my database - so,
I don't need
>nvarchar data type.
>I would like to convert cyrlic to latin and then insert
into varchar type.
>So, that is some kind of translation of data.
>Do you have any idea?
>lp,S
>"Peter The Spate" <anonymous@.discussions.microsoft.com>
wrote in message
>news:5a0c01c523d0$b46eb0d0$a401280a@.phx.gbl...
Unicode,
varchar
the
so
to
that
>
>.
>|||Hi Peter,
for example.
In russian database I have something like this:
-'
Now I would like to convert it to latin:
4y mozhete vy chytatypo-ukrayins'ky?
I won't have nVarchar type.
I would like to read from russian database and than convert it to latin (or
to unicode first like you suggest) and than insert into english database as
latin text into varchar column (I don't need nVarchar here because now I
have text in latin.)
But which function converts the text from cyrilic to unicode and than to
latin?
Regards,
Simon
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:5a4301c523d8$ea562770$a401280a@.phx.gbl...
> Hello,
> The reason I sugested nchar, nvarchar and ntext is that it
> is collation independant, basically its designed to what
> you want in converting to different collations.
> Have a look at 'Using Unicode Data' in BOL.
> So the plan was to convert it into Unicode, then convert
> than into Latin.
> Your numbers should remain the same regardless of which
> collation.
> Peter
> "There is no such thing as public opinion. There is only
> published opinion."
> Winston Churchill
>
>
> I don't need
> into varchar type.
> wrote in message
> Unicode,
> varchar
> the
> so
> to
> that