April 28, 2003 at 12:14 pm
For the past 6-8 months we have been experiencing a problem with an application that our company developed for internal use that utilizes MS SQL2000, MS Host Integration Server and an AS400 (DB2). We have been working with Microsoft , but to no avail. There does not seem to be anyone at MS with expertise in all areas of this problem.
SQL Specs -
Clustered SQL 2000 Servers - Both nodes are Compaq Prolient ML370 - 3.5 GB of memory - dual 1.2 GHZ Xeon Processors - Windows Advanced Server SP3 - SQL 2000 SP3
Problem -
We are executing a VB6 program every ½ hour on the ½ hour that fires off a stored procedure on SQL Server. This stored procedure is primarily cursor driven and as the main cursor loops through the available records entered by our data entry department - it will call subsequent stored procedures that also use cursors to loop through records. Ultimately we are gathering values and storing them in host variables used to build insert statements across platforms to an AS400 DB2 environment. In short, our data entry unit now writes to SQL Server only and we backfill our legacy AS400 through this automated program. In an attempt to give this automated backfill process a degree of commitment control, we employ an HIS Server and the provider used in the VB6 program is SQLOLEDB.1. We use linked servers configured on SQL Server to the AS400 and the OPENQUERY function when writing data to the AS400.Our success rate is intermittent and confusing. As we filter through our business logic, we can implicitly rollback when necessary, but we seem to be plagued by rollbacks that are not called implicitly. When the program reattempts these records, as it will, they can be committed at some later point in the day with no change to the data. No change to the data types of the host variables, no change in length, no changes whatsoever.
Does anyone have any idea what could be causing these rollbacks? Any help would be appreciated.
Thanks,
Greg
May 1, 2003 at 8:00 am
This was removed by the editor as SPAM
May 1, 2003 at 8:32 am
Could you paste the chunk of vb code that is rolling back, I need to see in order to understand this scenario.
Darren
Darren
May 1, 2003 at 9:02 am
Do you have any nested transactions?
I ask, because if the outer transaction is rolled back, all the nested transactions are rolled back even if they have been 'committed'.
I guess that some records fail the business logic but what happends to these? Do users correct these records so that they pass the validation the next time?
Jeremy
May 8, 2003 at 3:04 pm
I apologize for taking so long to respond. It looks like we have finally fixed this issue. Because we are using Host Integration server, there is a HIS piece on each of the two SQL servers in our cluster. I applied HISSP1 on our HIS server a while ago, but never applied it to the SQL servers. Once I applied the SP and a couple of post SP1 fixes. All of our issues disappeared. Thanks for everyone's input.
Greg
May 8, 2003 at 3:06 pm
I apologize for taking so long to respond. It looks like we have finally fixed this issue. Because we are using Host Integration server, there is a HIS piece on each of the two SQL servers in our cluster. I applied HISSP1 on our HIS server a while ago, but never applied it to the SQL servers. Once I applied the SP and a couple of post SP1 fixes. All of our issues disappeared. Thanks for everyone's input.
Greg
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply