March 10, 2006 at 3:31 am
I have an extended stored procedure that does an HTTPPOST to an SMS service provider. I have been told by the provider that the code is relatively straightforward, and is thread-safe.
I have a cursor running through a recordset of between 1 and 750 records, and I call the extended sproc for each record. Whenever there is a large number of records, the SQL job falls over with:
Execute interrupted by exception: Access violation at address 0877307C in module 'HTTPSMS.DLL'. Read of address FFFFFFFC [SQLSTATE 01000]
When I put a TOP 100 in the cursor, the job runs through fine.
The extended sproc has been running for 2 years on another server without any problems, but that server calls the DLL only once at a time. I'm convinced this is not a problem with the DLL.
Is this a SQL Server architecture problem? Is there a limit to the number of times an extended sproc can be called in a single job?
Can anyone assist or recommend a solution? We're going to keep the TOP 100 in for now.
Thanks,
Wayne.
March 10, 2006 at 7:03 am
1) are the objects in the extended stored procedure properly released?
2) Sql server has a memtoleave area for extended procedures, perhaps it gets exhausted.
3) Is the cursor forward-only,read-only to minimise select locks (perhaps using the option with no_lock helps)
March 10, 2006 at 7:09 am
March 10, 2006 at 12:45 pm
Just for completeness you can tune the MemToLeave area with the -g startup parameter
Cheers,
* Noel
March 13, 2006 at 2:17 am
Hi. I forwarded your responses to our provider and received the following reply:
1) Yes, the objects are properly released.
2) Memtoleave exhaustion whould indicate a memory leak, something that has been tested for and is not the case.
3) The stored procedure accepts parameters, not a recordset containing cursors.
My stored procedure contains the cursor, which is an update cursor. The extended sproc is call for every loop of my cursor.
March 13, 2006 at 11:00 am
*You could add some debugging code in your stored procedure.
Like adding a new row containing some state info.
*How is your server built up? OS, sql server and patch level, single/multiple processors/hyperthreading?
March 14, 2006 at 12:45 am
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply