carrier return + line feed in a varchar

Hi

how can I split a line into 2 lines in t-sql. etc
Set @.text1 = 'here's line one' + (carriere return + line feed) + 'here's line two'

I will be using it to send a mail from sql server (2000 sp4), so i can build a nice looking mail

> Set @.text1 = 'here's line one' + (carriere return + line feed) + 'here's

> line two'

Set @.text1 = 'here''s one line' + CHAR(13) + CHAR(10) + 'here''s line two';

|||thanks a lot

Carriage Contron in field

I am trying to split an address field which contains carriage controls. The address field can have 2 carriage controls in it, ie 3 address lines. I want to split the address into 3 seperate colomns based on the control carriage. I know how to combine 3 co
lumns into 1 but not how to split it out again.
Chard.
hi chard,
I assume you have carriage return in the string data which is represented by
char(13). See following example.
--sample data
create table t(col1 varchar(400))
insert into t values ('address1' + char(13)+ 'address2' + char(13) +
'address3')
insert into t values ('address4' + char(13)+ 'address5' + char(13) +
'address6')
insert into t values ('addressxyz4' + char(13)+ 'addressyrtyr5125455' +
char(13) + 'address6')
insert into t values ('addressxyz4' + char(13)+ 'addressyrtyr5125455' +
char(13) + 'address6xbbhghhw')
--query
select left (col1, (charindex (char(13), col1)-1) ) 'first_address',
substring (col1, charindex (char(13), col1) + 1,
(charindex ( char(13), col1, charindex (char(13), col1) + 1 ) - charindex
(char(13), col1))) '2nd_address',
right (col1, ((charindex (char(13), reverse(col1))) - 1) ) '3rd_address'
from t
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||Hi Vishal,
Exactly what I was after. Thanks very much.
Chard.
"Vishal Parkar" wrote:

> hi chard,
> I assume you have carriage return in the string data which is represented by
> char(13). See following example.
> --sample data
> create table t(col1 varchar(400))
> insert into t values ('address1' + char(13)+ 'address2' + char(13) +
> 'address3')
> insert into t values ('address4' + char(13)+ 'address5' + char(13) +
> 'address6')
> insert into t values ('addressxyz4' + char(13)+ 'addressyrtyr5125455' +
> char(13) + 'address6')
> insert into t values ('addressxyz4' + char(13)+ 'addressyrtyr5125455' +
> char(13) + 'address6xbbhghhw')
>
> --query
> select left (col1, (charindex (char(13), col1)-1) ) 'first_address',
> substring (col1, charindex (char(13), col1) + 1,
> (charindex ( char(13), col1, charindex (char(13), col1) + 1 ) - charindex
> (char(13), col1))) '2nd_address',
> right (col1, ((charindex (char(13), reverse(col1))) - 1) ) '3rd_address'
> from t
>
> --
> Vishal Parkar
> vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
>
>

capture error messages from dynamic tsql

How does one go about capturing error messages for a procedure that executes
dynamic sql...
for example ten lines out of 1000 produce an error... can the error be
captured and returned to the user?
--
Regards,
JamieI'm not sure what you are asking. Exceptions *are* returned to the user by default. See below:
USE tempdb
CREATE TABLE t(c1 int CHECK (c1 < 10))
GO
CREATE PROC p AS
EXEC('INSERT INTO t (c1) VALUES (20)')
GO
--Verify error
EXEC p
GO
--Capture using TRY and CATCH
BEGIN TRY
EXEC p
END TRY
BEGIN CATCH
DECLARE @.errStr nvarchar(4000)
SET @.errStr = ERROR_MESSAGE()
PRINT @.errStr
END CATCH
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:A075E032-9E58-4F19-A8E6-D66D5ED66357@.microsoft.com...
> How does one go about capturing error messages for a procedure that executes
> dynamic sql...
> for example ten lines out of 1000 produce an error... can the error be
> captured and returned to the user?
> --
> Regards,
> Jamie|||I should have added we are still using SQL 2000. Tested this in 2005 and it
works great. Thanks.
--
Regards,
Jamie
"Tibor Karaszi" wrote:
> I'm not sure what you are asking. Exceptions *are* returned to the user by default. See below:
> USE tempdb
> CREATE TABLE t(c1 int CHECK (c1 < 10))
> GO
> CREATE PROC p AS
> EXEC('INSERT INTO t (c1) VALUES (20)')
> GO
> --Verify error
> EXEC p
> GO
> --Capture using TRY and CATCH
> BEGIN TRY
> EXEC p
> END TRY
> BEGIN CATCH
> DECLARE @.errStr nvarchar(4000)
> SET @.errStr = ERROR_MESSAGE()
> PRINT @.errStr
> END CATCH
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:A075E032-9E58-4F19-A8E6-D66D5ED66357@.microsoft.com...
> > How does one go about capturing error messages for a procedure that executes
> > dynamic sql...
> > for example ten lines out of 1000 produce an error... can the error be
> > captured and returned to the user?
> > --
> > Regards,
> > Jamie
>
>