OLEDB TRANSACTION MAKING DIFFERENCE and error is raised

OLEDB transaction is making difference in my migrated application powerbuilder 10.5 which has new oledb driver which is replaced by MSS driver in previous Powerbuilder version.

1.)I am facing the below error on update/save this was not faced during my previous versino drive MSS in powerbuilder.This is oocure only after the change to oledb driver.

"Row change between retrieve and update.".

in front end of my application the error is poped as

"(STD1007) This row has been modified by another user. Save is canceled! ".

Please help to resolve this error.

Are you seeing this error when using Integration Services? If so, can you give more information on all the database access (e.g. ExecuteSQL, OLE DB Destination) in your package? If not, and this is specific to Powerbuilder, then I would start by looking at any settings available in your front end for handling database changes and transaction isolation level. If you want to focus on the provider itself, you can ask your question on the SQL Server Data Access forum, but they will likely have to rely on you to know how Powerbuilder is using the provider under the covers.

OLE DB provider MSDAORA was unable to begin a distributed transaction

Dear all,

I have been attempting to set up a linked server in SQL Server 2000 to point to an Oracle database (a very old Oracle database, v7!).

The linked server set up works fine. Then I created a database on the same SQL Server. Within that database I created a view which reads information from a view in the Oracle database (linked server). When I attempted to create this view in Enterprise Manager, I get the following error:

"The operation could not be performed because the OLE DB provider 'MSDAORA' was unable to begin a distributed transaction."

And so I created the view in Query Analyzer. This worked perfectly, no errors, and I can now go into Enterprise Manager and call up the view, which happily goes off to the view in the Oracle linked server and pulls back the info. BUT, when I attempt to do the same thing through an application on a different machine (using OLE Db, and a UDL to connect) the same error as above appears.

Does anyone know what this error means, and where I went wrong? Any help greatly appreciated. My view onto the linked server looks like this:

"CREATE VIEW dbo.NLPG_VIEW
AS
SELECT * from openquery(sadaslink, 'select * from NLPG_VIEW')"

Many thanks in advance for your wisdom!

P.S I wondered whether I need to check if the distributed transaction co-ordinator is running but don't know how.
Thanks,http://www.aspfaq.com/2455 fyi.|||Thanks, I've already had aread through this. I did create the view in query analyzer but the problem now is that I can't get it to work from a remote application. I think I need to look into the distributed transaction co-ordinator, but I don't know how or where. How do I see if it is running? How do I get it running?|||On the SQL Server, start the SQL Services Manager. Change the Service to Distributed Transaction Coordinator. Play to your heart's content!

FYI, with Oracle 7.3 I needed to use SET REMOTE_PROC_TRANSACTIONS (http://msdn.microsoft.com/library/en-us/tsqlref/ts_set-set_206r.asp) to make things happy.

-PatP|||Would SET_REMOTE_PROC_TRANSACTIONS have to be coded into the app, as I can't put it in the SQL in the view can I?|||http://www.microsoft.com/technet/prodtechnol/sql/2000/books/c08ppcsq.mspx a guide to the solution.
Also check whether compatible Oracle driver is being used to connect, try with latest drivers.|||When you said remote procedure transactions, is that the same as setting remote procedure call in the GUI in enterprise manager? RPC and RPC OUT?

Thanks,
Paula|||No Polly, the concepts are related, but definitely not the same.

The problem is that SQL Server uses many flavors of locks in order to acheive true data consistancy... Some locks prohibit writing, some prohibit any access at all, and some do things that are difficult to describe in English, but make the engine behave as well as possible.

I haven't tried this in a long time, and no longer have an Oracle 7.x server to even test with, but I think you could get by with:SET REMOTE_PROC_TRANSACTIONS OFF
GO
CREATE VIEW dbo.NLPG_VIEW
AS
SELECT * from openquery(sadaslink, 'select * from NLPG_VIEW')All I can suggest that you do is to try it... I don't see how the attempt could hurt anything.

-PatP|||Thanks very much, I'll try it. Can't test it until next week, but Il'l let you know the results.

Old transaction log is reported as missing during recovery

I've done this at least 50 times without issue on various databases, but this
one just doesn't want to play nice.
So I have a SQLServer2K database setup. I've got a home-grown disaster
recovery process in place, so that transactions are taken from one server and
migrated to a backup server every hour. Once it's up and running, the
process is pretty flawless. However, getting it setup can be tricky.
1) Set the recovery model to "Full".
2) Backup your transaction logs
3) Do a full backup
4) Copy the backup to the backup server
5) Restore in to a read only database
Transaction logs can now be backed up on the source database, copied over
and applied to the backup server.
This isn't working this time though. No matter what I've tried, I still get
a message during the restore of the transaction logs such as the following:
"Executed as user: dbo. The log in this backup set begins at LSN
83111000001460600001, which is too late to apply to the database. An earlier
log backup that includes LSN 83075000002839600001 can be restored. [SQLSTATE
42000] (Error 4305) RESTORE LOG is terminating abnormally. [SQLSTATE 42000]
(Error 3013). The step failed."
Given the large gap between the two LSN #'s, I'm assuming that the database
thinks there is a very old transaction I'm missing. However, that is sheer
guesswork on my part.
The source box has been cycled, I've tried turning the recovery back and
forth between Full and Simple. This error is from my latest attempt, when I
swear I've done everything by the book.
Any suggestions of what might be causing this or where I should go from here?
Fred...
SQL lost a log somehow, I often had problems similar to this w/ home grown
log shipping.
Solution mostly relied upon: re-execute full backup, then restore & fire up
log shipping again. Full backup restores should be done nightly if
affordable...
Also - transactional replication ultimately proved the best solution for us,
transactions replicated within seconds, if we ever had to failover to backup,
I am much more confident minimal data would be lost as opposed to hourly log
restores...
Regards,
ChrisB
MCDBA OCP
www.MyDatabaseAdmin.com
"fkutz" wrote:

> I've done this at least 50 times without issue on various databases, but this
> one just doesn't want to play nice.
> So I have a SQLServer2K database setup. I've got a home-grown disaster
> recovery process in place, so that transactions are taken from one server and
> migrated to a backup server every hour. Once it's up and running, the
> process is pretty flawless. However, getting it setup can be tricky.
> 1) Set the recovery model to "Full".
> 2) Backup your transaction logs
> 3) Do a full backup
> 4) Copy the backup to the backup server
> 5) Restore in to a read only database
> Transaction logs can now be backed up on the source database, copied over
> and applied to the backup server.
> This isn't working this time though. No matter what I've tried, I still get
> a message during the restore of the transaction logs such as the following:
> "Executed as user: dbo. The log in this backup set begins at LSN
> 83111000001460600001, which is too late to apply to the database. An earlier
> log backup that includes LSN 83075000002839600001 can be restored. [SQLSTATE
> 42000] (Error 4305) RESTORE LOG is terminating abnormally. [SQLSTATE 42000]
> (Error 3013). The step failed."
> Given the large gap between the two LSN #'s, I'm assuming that the database
> thinks there is a very old transaction I'm missing. However, that is sheer
> guesswork on my part.
> The source box has been cycled, I've tried turning the recovery back and
> forth between Full and Simple. This error is from my latest attempt, when I
> swear I've done everything by the book.
> Any suggestions of what might be causing this or where I should go from here?
> Fred...