Distributed insert on oracle DB does not work from within a transaction.

Dear All,

I have MS SQL 2000 on Windows 2000 box and Oracle 9i on RH Linux 9.

I wanna write a trigger on MS SQL which inserts a record in the Oracle table.

So I have configured a Linked server on MS SQL using Microsoft OLE DB provider MSDAORA and am able to insert into (Single insert st) oracle table successfully from the Query Analyzer.

However the insert does not work when I code it in the trigger. Basically insert on the Oracle DB does not work from a transaction.
I receive the foll error when the trigger executes -

Server: Msg 7391, Level 16, State 1, Procedure AIN_sql2ora, Line 14
The operation could not be performed because the OLE DB provider 'MSDAORA' was unable to begin a distributed transaction.

Can anyone help me on this ?

Regards,
Azhar.set implicit transactions off

distinct name and email.

hey guys

.

you see how fenix.sn has a couple of entries BUT the same email and DIFFErENT BET IDS.

okay this is what i wanna do.

i wanna send an email to fenix.sn (just ONE email) saying you have won the following bets: xxx, xxx, xxx, xxx

then i wanna delete the entreis from the table.

masfenix,

There are several ways to accomplish this. Let me give you one of them here.

SELECT
[Username],
[Useremail],
WonBetIDs = REPLACE(
(
SELECT
WonBetID AS [data()]
FROM
tblUser tIn
WHERE
tIn.[Username] = tOut.[Username]
ORDER BY
WonBetID
FOR XML PATH ('')
), ' ', ',')
FROM
tblUser tOut
GROUP BY
[Username], [Useremail]

Hope this helps.|||

thanks for that ,but i am not gonna lie. i do not understand htat code at all. i am looking for a function.

|||It's good to be frank. Here is another implementation that may help you to understand easily. Since there isn't any function that does what you had mentioned, I have UDF to do the same for you here. Hope this helps.

Function
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION dbo.getWonBetIdsOfUser
(
@.Username VARCHAR(20)
)
RETURNS VARCHAR(2048)
AS
BEGIN
DECLARE @.WonBetIds VARCHAR(2048)

SELECT
@.WonBetIds = COALESCE(@.WonBetIds + ',', '') + [WonBetID]
FROM
tblUser
WHERE
[Username] = @.Username

RETURN @.WonBetIds

END
GO

Calling function
SELECT [Username], dbo.getWonBetIdsOfUser([Username])
FROM tblUser
GROUP BY [Username]|||

how do i actually call that stored proceudre?

i dont understand the "calling function".

note: i just wanna make a button called "send email" and it should automatically go through the list and send the emails. (but once for each user)

|||Use the query specified under Calling Function part in SqlCommand in your .NET app to get a dataset/datatable containing all username/email and bet ids. You can loop thru each records in the table and mail them from within your .NET application.

Distinct issue

Hi

I wanna write a proc that returns Distinct CustomerID's in a table and returns result in output parameter

When i try this, i get error - incorrect syntax near distinct. Any ideas??

ALTER PROCEDURE proc_Report_CountCustomers_Sept
(
@.CustCount int OUTPUT
)
AS
SET NOCOUNT ON
select
@.CustCount = distinct(CustomerID)
from
Orders
Where
OrderDate > '2006-09-01' and OrderDate < '2006-10-01'

hi,

what you want to return, count of distinct ids or distinct ids itself ?

regards,

satish

|||

hehe.. er ofcourse. I wanted to count the distinct ids, but i didnt count

i worked it out

cheers brother

ALTER PROCEDURE proc_Report_CountCustomers_Sept
(
@.CustCount int OUTPUT
)
AS
SET NOCOUNT ON
SELECT
@.CustCount =Count(DISTINCT(CustomerID))
FROM
Orders
WHERE
OrderDate > '2006-09-01' and OrderDate < '2006-10-01'

|||

good you got it working :),

cheers ,

satish

Displaying data in HTML format

hi there,
jux wanna check if there is any1 out there also encounter the same problem
as mi?
my data display in the html format does not display properly and the display
is different from the report from the reporting service.
for example.
aaaa
1234
hhhh
the html display will display like this aaaa1234hhhh.
I used table, detail to retrieve the data frm the dataset. Is there any way
or setting to set the IE or solve this issue?
regards
AngelaYou may have overlapping textboxes. Or are you saying you have encoded HTML
in a single textbox? Or perhaps the whitespace is being stripped out. All of
these are possible reasons.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Angela" <Angela@.discussions.microsoft.com> wrote in message
news:57C71713-3D53-45AA-9C21-00C0238BBE1C@.microsoft.com...
> hi there,
> jux wanna check if there is any1 out there also encounter the same problem
> as mi?
> my data display in the html format does not display properly and the
> display
> is different from the report from the reporting service.
> for example.
> aaaa
> 1234
> hhhh
> the html display will display like this aaaa1234hhhh.
> I used table, detail to retrieve the data frm the dataset. Is there any
> way
> or setting to set the IE or solve this issue?
> regards
> Angela