January 14, 2013 at 1:44 am
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.
January 14, 2013 at 8:13 am
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: XYZCell: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/
January 15, 2013 at 2:00 am
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?
January 15, 2013 at 3:28 am
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;-)
January 15, 2013 at 8:25 am
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/
January 15, 2013 at 9:35 pm
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.
January 15, 2013 at 10:12 pm
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;-)
January 16, 2013 at 3:20 am
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."
January 16, 2013 at 3:31 am
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;-)
January 16, 2013 at 10:48 pm
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
January 16, 2013 at 11:12 pm
In addition to,
I use Windows 7 Ultimate Edition & Microsoft SQL Server Standard Edition (64-bit) Version:10.50.1600.1
January 16, 2013 at 11:57 pm
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;-)
January 17, 2013 at 1:11 am
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.
January 17, 2013 at 1:19 am
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;-)
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply