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 query

Hi
I have a query as below:
select [transaction].[M_GIFTCARDNO], [T_GIFTCARDS].[M_SERIALNO],
[T_GIFTCARDS].[M_CARDNUMBER], [transaction].[req_login_time]
from [T_GIFTCARDS], [transaction]
where req_login_time between '5 October,2005' and '6 December, 2005'
and [T_GIFTCARDS].[M_CARDNUMBER]=[transaction].[M_GIFTCARDNO]
Explanation
[T_Giftcards].[M_Cardnumber] is a unique number,
[transaction].[M_GIFTCARDNO] is the matching filed in another table but this
transaction table has may hundreds of transactions per T_Giftcard number, Ho
w
can I just display 1 T_Giftcards number and one matching transaction'
Help much appreciatedIvo (Ivo@.discussions.microsoft.com) writes:
> I have a query as below:
> select [transaction].[M_GIFTCARDNO], [T_GIFTCARDS].[M_SERIALNO],
> [T_GIFTCARDS].[M_CARDNUMBER], [transaction].[req_login_time]
> from [T_GIFTCARDS], [transaction]
> where req_login_time between '5 October,2005' and '6 December, 2005'
> and [T_GIFTCARDS].[M_CARDNUMBER]=[transaction].[M_GIFTCARDNO]
> Explanation
> [T_Giftcards].[M_Cardnumber] is a unique number,
> [transaction].[M_GIFTCARDNO] is the matching filed in another table but
> this transaction table has may hundreds of transactions per T_Giftcard
> number, How can I just display 1 T_Giftcards number and one matching
> transaction'
select [T_GIFTCARDS].[M_SERIALNO], [T_GIFTCARDS].[M_CARDNUMBER],
MIN([transaction].[req_login_time])
from [T_GIFTCARDS], [transaction]
where req_login_time between '5 October,2005' and '6 December, 2005'
and [T_GIFTCARDS].[M_CARDNUMBER]=[transaction].[M_GIFTCARDNO]
GRUOP BY [T_GIFTCARDS].[M_CARDNUMBER], [T_GIFTCARDS].[M_SERIALNO]
Or use MAX() if you think that is better.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

Distinct Count in a new Measure Group

Here is the situation:

I have a cube that has transaction level data and there is a control number that goes with this data, but it isn't one for one. If I have 60 million rows in the fact table, I have 50 million control numbers in a control number dimension.

I would like to be able to slice the data by date (for instance) and then do a distinct count on the control number dimension. I can do a distinct count w.o having to do a join of course because I can count the distinct instances of the key to the control number dimension.

One more note - the control number dimension is not a degenerate dimension...it is not one to one on the fact table.

I would like to use the DISTINCT COUNT measure in SSAS, but it makes me create a new measure group to do so. All of my additive measures are in one group, the distinct count is forced into another group. This forces double the processing time.

Does anyone know why this behavour happens and can someone explain why a DISTINCT COUNT measure has to go in a seperate measure group?

Thanks,

Mark

Mark,

Do you already have a DISTINCT COUNT measure in that measure group? Analysis Services will only allow you to have one DISTINCT COUNT per measure group.

|||Maybe this MDX will overcome the limitation of one unqiue count.

Distinct(Filter([Customer].[Customer Name].Members,Not IsEmpty([Measures].[Sales Amount]))).Count

Timmy|||

Actually, I don't have one in the first measure group. The first measure group is just regular measures, but if I create a new measure that is a distinct count measure, it automatically puts it in another group.

I have now heard from other sources that this is an exptected behavior. So no biggie, just needed to know.

ps - sorry it took so long to respond.

Thanks,

Mark

http://spaces.msn.com/mgarnerbi

|||I noticed this too, does anyone know the underlying reason why this is? Is this going to be fixed in a future version?sql

Distinct Count in a new Measure Group

Here is the situation:

I have a cube that has transaction level data and there is a control number that goes with this data, but it isn't one for one. If I have 60 million rows in the fact table, I have 50 million control numbers in a control number dimension.

I would like to be able to slice the data by date (for instance) and then do a distinct count on the control number dimension. I can do a distinct count w.o having to do a join of course because I can count the distinct instances of the key to the control number dimension.

One more note - the control number dimension is not a degenerate dimension...it is not one to one on the fact table.

I would like to use the DISTINCT COUNT measure in SSAS, but it makes me create a new measure group to do so. All of my additive measures are in one group, the distinct count is forced into another group. This forces double the processing time.

Does anyone know why this behavour happens and can someone explain why a DISTINCT COUNT measure has to go in a seperate measure group?

Thanks,

Mark

Mark,

Do you already have a DISTINCT COUNT measure in that measure group? Analysis Services will only allow you to have one DISTINCT COUNT per measure group.

|||Maybe this MDX will overcome the limitation of one unqiue count.

Distinct(Filter([Customer].[Customer Name].Members,Not IsEmpty([Measures].[Sales Amount]))).Count

Timmy
|||

Actually, I don't have one in the first measure group. The first measure group is just regular measures, but if I create a new measure that is a distinct count measure, it automatically puts it in another group.

I have now heard from other sources that this is an exptected behavior. So no biggie, just needed to know.

ps - sorry it took so long to respond.

Thanks,

Mark

http://spaces.msn.com/mgarnerbi

|||I noticed this too, does anyone know the underlying reason why this is? Is this going to be fixed in a future version?

Distinct Count in a new Measure Group

Here is the situation:

I have a cube that has transaction level data and there is a control number that goes with this data, but it isn't one for one. If I have 60 million rows in the fact table, I have 50 million control numbers in a control number dimension.

I would like to be able to slice the data by date (for instance) and then do a distinct count on the control number dimension. I can do a distinct count w.o having to do a join of course because I can count the distinct instances of the key to the control number dimension.

One more note - the control number dimension is not a degenerate dimension...it is not one to one on the fact table.

I would like to use the DISTINCT COUNT measure in SSAS, but it makes me create a new measure group to do so. All of my additive measures are in one group, the distinct count is forced into another group. This forces double the processing time.

Does anyone know why this behavour happens and can someone explain why a DISTINCT COUNT measure has to go in a seperate measure group?

Thanks,

Mark

Mark,

Do you already have a DISTINCT COUNT measure in that measure group? Analysis Services will only allow you to have one DISTINCT COUNT per measure group.

|||Maybe this MDX will overcome the limitation of one unqiue count.

Distinct(Filter([Customer].[Customer Name].Members,Not IsEmpty([Measures].[Sales Amount]))).Count

Timmy
|||

Actually, I don't have one in the first measure group. The first measure group is just regular measures, but if I create a new measure that is a distinct count measure, it automatically puts it in another group.

I have now heard from other sources that this is an exptected behavior. So no biggie, just needed to know.

ps - sorry it took so long to respond.

Thanks,

Mark

http://spaces.msn.com/mgarnerbi

|||I noticed this too, does anyone know the underlying reason why this is? Is this going to be fixed in a future version?

distibuted transations in linked server

please solve this problem.

when i am executing any transaction from one server to update record in other linked server database.

create PROCEDURE [dbo].[aTest]

@.iNum as int

AS

BEGIN

SET NOCOUNT ON;

begin tran

update aaa set sName='Deepak'

where iNum=@.iNum

exec [REPORTSRVR\SQL2005].HDFCCUSTOMER.dbo.aProc @.iNum

if @.@.error <> 0

begin

rollback tran

return

end

else

begin

commit tran

end

SET NOCOUNT OFF;

END

it shows following error

The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "REPORTSRVR\SQL2005" was unable to begin a distributed transaction.

1. Check/change your server option "remote proc trans" via sp_configure or Connections tab in SQL Server Instance Properties window

2. If you need MS DTC for distributed transactions: http://blogs.msdn.com/mab/archive/2005/12/30/508273.aspx

|||

thanks for response.

i m using transaction statement. is it possible that single transaction can talk from two server?

Displaying previous transaction result

Hi Guys,

I want to display a report like this

Date Balance on Date Credit Debit Current Balance
02/05/06 0 1200 0 1200
05/05/06 1200 500 0 1700
10/05/06 1700 0 200 1500
15/05/06 1500 200 0 1700

I mean for the new date the balnace of the previous day transaction should be displayed.
How can it be done?
Any help is greately appreciated.
Thanks in advanceIs the day's balance a value you calculate, or read straight from the database?
If the latter then use the 'Previous' function. See help.|||or

Create a formul having the code
{Credit}-{Debit}

and create running total based on that formula and use that in report