| Author |
Topic |
|
xazos79
Starting Member
7 Posts |
 Posted - 2009-02-05 : 18:53:16
|
| Hi all,I've been lumped with getting a legacy system running on a Windows 2000 Server / Sql Server 2000 environment to run on Windows 2003 Server / Sql Server 2000.The current problem i'm having is in relation to Sql Batch Jobs. They fail The job i'm focusing on at the moment calls an underlying stored procedure (sp_gen_ContinuingApprovalExpiration).Within this stored procedure, a call is made to another sp (sp_utl_CreateNotification)Within sp_utl_CreateNotification, there's a call to a com+ application:EXEC @ReturnCode = sp_OAMethod @object, 'CreateNotification', @ErrorMessage OUT, @NotificationCode, @UserName, @KeyId, @UserType, @dbname,@Parameters,@wwwroot, 1When i run sp_gen_ContinuingApprovalExpiration, or debug it....the server just hangs indefinitely (eventually coming back with a timeout error message). To stop it earlier, i have to restart the mssql service.Now the strange part, if i call sp_utl_CreateNotification with the exact same parameters, it all runs fine! I'm not sure what the issue is if sp_utl_CreateNotification is called from within sp_gen_ContinuingApprovalExpiration.Does anyone have any potential advice/clues as to what could be causing this? I'm mimicked my com+ security settings from production, and even tinkered with them...to no avail Cheers,Sam |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-02-06 : 06:59:10
|
Hi xazos79.why not put some debug statements into sp_gen_ContinuingApprovalExpiration? Add some RAISERROR('foo', 10, 10) WITH NOWAITstatements before and after the call to sp_utl_CreateNotification inside the parent stored proc. That way you can find out where the time out happens.I think the problem will actually be in sp_gen_ContinuingApprovalExpiration rather than the com+ object.NB : Edits for typo(s)Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
xazos79
Starting Member
7 Posts |
Posted - 2009-02-08 : 18:28:09
|
| I had been debugging the stored proc step by step and it definitely hangs on the sp_OAMethod call (and the debug writes you mentioned confirm this).Next step is to see which line of VB code the issue occurs. |
|
|
|
xazos79
Starting Member
7 Posts |
Posted - 2009-02-08 : 19:47:43
|
| I've found exactly where the error occurs. In the VB code, where the crystal report is to be generated:crystalReport.Export Falseis the line. I'm stumped as to why generation is fine when sp_utl_CreateNotification is called directly vs when its called from within the sp_gen_ContinuingApprovalExpiration.I'll continue with my investigation! |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-02-09 : 06:21:52
|
| Is it possible that you are in some strange transactional state in normal use which is not the same when you call the stored proc directly?or that you hold a lock with the parent stored proc which you are not holding when you call the com+ object direct?I think it's probably something like that.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
xazos79
Starting Member
7 Posts |
Posted - 2009-02-09 : 23:23:28
|
| Well, i've found out what's causing the problem! sp_gen_ContinuingApprovalExpiration uses a cursor to loop through data and pass it through. When i comment out all the cursor related code, it works! Transaction related code is commented out during this process too so i'm not sure whether its the cursor or transaction stuff causing the issue.More investigation required! |
|
|
|
xazos79
Starting Member
7 Posts |
Posted - 2009-02-10 : 00:16:29
|
| Ok, its not the cursor...its the transaction! Why it happens on win server 2003 and not on win server 2000 is beyond me. I removed all the transaction related processing and its all fine. I do remember that there was some sort of deadlock being caused in SQL when i was monitoring the sql processes....but i didn't think it was related. Now i just need to figure out how to get transactions working..... |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-02-10 : 05:23:28
|
| what's the com+ object do? -- is it supposed to just report success / fail of the operation?If so you could move it out of the transaction (is your transaction inside the cursor?) so you have a loop of transactional operations?You can assign a value to a variable inside a transaction and it will survive rollback so you could use that after the end of the transaction to decide whether to call the com+ object or not.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
xazos79
Starting Member
7 Posts |
Posted - 2009-02-10 : 17:49:50
|
| The COM Object creates a crystal report and sends it to an end user with data from each loop of the cursor. The Correspondence table is then updated with a 'NotificationSent=true' statement for that notification. This is where the transaction's scope is as they want to roll back the table update should report creation fail.We are now looking at potential ways to do what you mentioned. The thing is, i'm looking at only one of about 25 batch jobs. So i hope we can separate the Transaction code from the Com object calling code.Cheers again for the help. |
|
|
|
xazos79
Starting Member
7 Posts |
Posted - 2009-02-10 : 22:19:10
|
| Its sorted! I found the SP that was waiting for a lock to be released (sp_rpt_ContinuingApprovalExpirations), and used WITH (NOLOCK) command on all the tables in the FROM statements within that SP. This worked and allowed the queries to run!I then delved back into Com services as i remembered some transaction settings within each com component. For the properties of the Coms we were using, under the Transactions tab there's a 'Transaction Isolation Level' setting. This was set to serialized. I changed both com components to 'Read Uncommitted' and this worked! No need to change the above stored proc.Thanks for your help, and i'm glad this ride appears over for now. |
|
|
|
|
Stored Proc Com+ call failing in some cases