DSXchange

Hi Guys,

Based on Customer type i would like to update or insert records in to a sqlserver database table.

I created Transform with 2 links one for update and another for Insert

I am getting database locked error.

It works fine for Informix database.

Please help me out how to solve this problem.

Thanks
Narasa

Check whether some other person / application is modifying the table.

I am sorry guys it's not locking error it's not updating correctly.

please help me.

Thanks for prompt reply.

reddy,

not updating correctly

? Does this mean when you enter a "3" it actually updates a "7"? Or that records are being deleted instead of inserted? Or that updates are not being performed? You will need to tell us a bit more...

As ArndW has pointed out, you need to supply some examples for others to understand and answer.

Sainath.Srinivasan wrote:Check whether some other person / application is modifying the table.

Actually it is most likely his own job that is causing the problem. When you have the stage in the job twice it is opening 2 separate sessions to SQL Server.

If this is the case you can usually fix the issue by setting the commit point to 1. Another option would be to have the stage do an insert/update itself instead of doing it in the transform.

The user had 'insert or update rows' in a single link and not 2 different links. So that cannot be the reason for lock.

Hi Sai,

reddy wrote:Hi Guys,

Based on Customer type i would like to update or insert records in to a sqlserver database table.

I created Transform with 2 links one for update and another for Insert

I am getting database locked error.

It works fine for Informix database.

Please help me out how to solve this problem.

Thanks
Narasa

From the OPs post, it seems like he did create two links from Transformer, one for update and another for insert, unless I am misinterpreting his words.

It's not about the links, it's about the stages. Two links into two stages = possible locks. Two links into one stage = no locks.

chulett wrote:It's not about the links, it's about the stages. Two links into two stages = possible locks. Two links into one stage = no locks.

With SQL Server 2000 you will have the same issue going into one stage since SQL Server does not support multiple open statements against a single session. The OLEDB API simulates this by opening multiple sessions.

http://msdn.microsoft.com/library/defau ... nSQL05.asp

Well, that's too darn bad.

I was speaking from my Oracle experience, should have figured SQL Server would work differently...

chulett wrote:Well, that's too darn bad.

I was speaking from my Oracle experience, should have figured SQL Server would work differently...

Unfortunately I am speaking from experience on this one. I guess the good news is that SQL 2005 is supposed to correct that issue.