Insert/Update on SQL SERVER Database

reddy · Post by **reddy** » Wed Jul 27, 2005 8:58 am

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

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Wed Jul 27, 2005 9:03 am

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

reddy · Post by **reddy** » Wed Jul 27, 2005 9:15 am

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

please help me.

Thanks for prompt reply.

ArndW · Post by **ArndW** » Wed Jul 27, 2005 9:32 am

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...

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Wed Jul 27, 2005 9:47 am

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

shawn_ramsey · Post by **shawn_ramsey** » Wed Jul 27, 2005 10:20 am

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.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Wed Jul 27, 2005 10:56 am

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

pnchowdary · Post by **pnchowdary** » Wed Jul 27, 2005 12:33 pm

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.

chulett · Post by **chulett** » Wed Jul 27, 2005 1:25 pm

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.

shawn_ramsey · Post by **shawn_ramsey** » Wed Jul 27, 2005 1:43 pm

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

chulett · Post by **chulett** » Wed Jul 27, 2005 5:32 pm

Well, that's too darn bad.

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

shawn_ramsey · Post by **shawn_ramsey** » Wed Jul 27, 2005 9:25 pm

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.

DSXchange

Insert/Update on SQL SERVER Database

Insert/Update on SQL SERVER Database

Re: Insert/Update on SQL SERVER Database