Insert multiple lines in a single row

Question

Insert multiple lines in a single row

shohelr2003

Hall of Fame

Points: 3535
More actions
January 14, 2013 at 1:44 am

#268710

Dear,
I want to insert multiple lines in a single row in SQL Server 2008 R2. I have tried. But it's not working. A table has only one column and it contains information like following.
Name: XYZ
Cell:1325646
Blood: B+
How is it possible? Please help me.

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

shohelr2003 (1/14/2013)
Dear,
I want to insert multiple lines in a single row in SQL Server 2008 R2. I have tried. But it's not working. A table has only one column and it contains information like following.
Name: XYZ
Cell:1325646
Blood: B+
How is it possible? Please help me.

Are you sure it "isn't working"? SSMS will display it in a single line if you have the output showing in a grid. If you change the output to text it will display multiple lines.

create table #MultiRow

(

SomeValue varchar(1000)

)

insert #MultiRow

select 'Name: XYZ

Cell:1325646

Blood: B+'

select * from #MultiRow

drop table #MultiRow

Ctrl+T = Results to Text

Ctrl+D = Results to Grid

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

shohelr2003 Hall of Fame Points: 3535 More actions · Answer 2

Thank you, Sir. Your code works.

Actually I wanna convey SMS from my database through mobile operator. That's why I want to insert data in my convenient format. If I use char(13) + char(10), it will work too, won't it?

Another query is when sms will be sent, do the format remain same or will be changed?

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

shohelr2003 (1/15/2013)
Thank you, Sir. Your code works.
Actually I wanna convey SMS from my database through mobile operator. That's why I want to insert data in my convenient format. If I use char(13) + char(10), it will work too, won't it?
Another query is when sms will be sent, do the format remain same or will be changed?

can you post the table defintion from which above data will be picked uo as SMS content.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

Actually I wanna convey SMS from my database through mobile operator. That's why I want to insert data in my convenient format. If I use char(13) + char(10), it will work too, won't it?

Did you try it? Yes it should work.

Another query is when sms will be sent, do the format remain same or will be changed?

That is up to the provider. I would guess that most of them will not reformat the input but I can't tell you what any given provider might do.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

shohelr2003 Hall of Fame Points: 3535 More actions · Answer 5

@Bhuvnesh,

Actually it is a distributed query. I wrote an AFTER TRIGGER that will fetch information from six tables that will be SMS content and insert into a remote database's VIEW. In addition to, I formatted the SMS body as business requirements from the database information.

@sean Lange,

By googling I came to know char(13)+char(10). I have tried it and it worked. We talked to mobile operator and they told me they are blind about the sms. They will just convey the sms.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 6

shohelr2003 (1/15/2013)
@Bhuvnesh,
Actually it is a distributed query. I wrote an AFTER TRIGGER that will fetch information from six tables that will be SMS content and insert into a remote database's VIEW. In addition to, I formatted the SMS body as business requirements from the database information.

then you would be experiencing performance issues soon .

your AFTER trigger approach(Working on 6 table) will be synchronous approach to play with data (plus remote database migration too), i will say to manage the load , you need to go with asynchronous approach like Service Broker. sooner or later you wil find it beneficial .

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

shohelr2003 Hall of Fame Points: 3535 More actions · Answer 7

@Bhuvnesh, I understood your point but right now I have to do in this way that I described. Management Decision.

shohelr2003 Hall of Fame Points: 3535 More actions · Answer 8

If I execute an insert query into linked server, it works. But when I use trigger to execute an insert query into linked server, it fails and generates the following error message.

"The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "Server2" was unable to begin a distributed transaction."

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 9

shohelr2003 (1/16/2013)
If I execute an insert query into linked server, it works. But when I use trigger to execute an insert query into linked server, it fails and generates the following error message.
"The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "Server2" was unable to begin a distributed transaction."

post the trigger code

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

shohelr2003 Hall of Fame Points: 3535 More actions · Answer 10

CREATE TRIGGER [dbo].[MyTrig]

ON [dbo].[Table1]

AFTER INSERT

AS

Declare @V1 varchar(50);

Declare @V2 varchar(50);

Declare @V3 varchar(50);

Declare @V4 varchar(50);

select @V1 = i.V1 from inserted i;

select @V2 = i.V2 from inserted i;

select @V3 = T2.V3 from Table2 T2 inner join Table3 T3 on bla bla bla

inner join Table4 T4 on bla bla bla

inner join inserted i on bla bla bla

where bla bla bla;

select @V4 = V4 from Table2 where ID=@V3;

Declare @D as varchar(50);

select @D = SUBSTRING(T5.Desc, 0, CHARINDEX(',', T5.Desc)) from Table T5 inner join inserted i on bla bla bla where bla bla bla

Declare @D2 as varchar(20);

select @D2 = SUBSTRING(T5.Desc, CHARINDEX(',', T5.Desc)+1, LEN(T5.Desc)) from Table T5 inner join inserted i on bla bla bla where bla bla bla

Declare @C as varchar(35);

select @C = T6.Name from Table5 T5 inner join Table6 T6 on bla bla bla

Declare @w as varchar(50);

select @w = i.ID from inserted i where bla bla bla

Declare @w2 as varchar(50);

select @w2 = SUBSTRING(T7.Name, 0, CHARINDEX(' ',T7.Name)) from Table7 T7 where ID=@w;

Declare @sb as varchar(160);

set @sb = @V2 + CHAR(13) + CHAR(10) + @D + CHAR(13) + CHAR(10) + @D2 + CHAR(13) + CHAR(10) +

@C + CHAR(13) + CHAR(10) + @w2 + CHAR(13) + CHAR(10)+ 'Thanks';

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for trigger here

if @V1 is null

return

else

insert into DB.dbo.TableName values(@sb,@V4);

SET XACT_ABORT ON;

BEGIN DISTRIBUTED TRANSACTION;

insert into [LinkedServer].[DB].[dbo].[TableName] values(@sb,@V4);

COMMIT TRANSACTION;

SET XACT_ABORT OFF;

END

Would you please tell me how to delete a post from this forum? I am sorry for cross posting

shohelr2003 Hall of Fame Points: 3535 More actions · Answer 11

In addition to,

I use Windows 7 Ultimate Edition & Microsoft SQL Server Standard Edition (64-bit) Version:10.50.1600.1

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 12

shohelr2003 (1/16/2013)
If I execute an insert query into linked server, it works. But when I use trigger to execute an insert query into linked server, it fails and generates the following error message.
"The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "Server2" was unable to begin a distributed transaction."

1) Have your sql server patched with CU2 ?

2) MSDTC is enabled ?

3) Check this too http://connect.microsoft.com/sqlserver/feedback/details/243725/linked-server-sourceserver-was-unable-to-begin-a-distributed-transaction

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

shohelr2003 Hall of Fame Points: 3535 More actions · Answer 13

1) Have your sql server patched with CU2 ?
2) MSDTC is enabled ?

I don't know what is CU2.

I have enabled MSDTC on both servers going to Component Services.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 14

shohelr2003 (1/17/2013)
I don't know what is CU2.

this is link http://support.microsoft.com/kb/2072493 , is it production server ? if ues then DOnt do this without proper discussion

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)