I am using the Extended Sp's sp_OACreate, sp_OAGetProperty,... to call 'MSXML2.ServerXMLHttp'.
This works fine when called from within EXEC Statements, other stored procedures, and any other means of calling it I can think of, recursivley, 100's of times in quick succession, multiple times simultaneousley all without a problem.
I then try to call the SP from outside SQL (from vb.net using ADO.net) and it works the first time perfectally, the next and subsequent calls return the helpful error msg: 'General network error. Check your network documentation. ' and MSSQL Server Service is terminated. I have trawled the logs, the onlything that crops up is:
2003-11-04 18:13:27.33 spid52 Using 'odsole70.dll' version '2000.80.760' to execute extended stored procedure 'sp_OACreate'.
2003-11-04 18:13:29.47 spid52 Using 'dbghelp.dll' version '4.0.5'
As you would expect calling odsole70.dll.
I have replicated this problem on 2 SQL2000 server boxes, both fully patched running sp3a, one on XP Professional the other on Win2K Server
Thanks SG, unfortunatley this has not had any effect, I have also tried to get the MDAC 2.8, but this has not had any effect. One point I forgot to mention was that it is running on an Insert trigger (which baffles me even more)
If you or anyone else has any suggestions I would be very grateful!
Originally posted by sqlguy7777
Sadly there seems to be a large number of issues with VB.NET and SQL 2000....I see quite a few people with similar problems.
I have to say I am a little confused how I am supposed to Lock down the operating lock step, my understanding of update triggers is that SQL Fires them when you have added a row to a table, which is totally sepearate to my vb calling code, if I take off the trigger the insert statement/stored procedure work fine, as soon as i put them back on I get one attempt and it !?*!s up!
Could it be permissions related? am I closing my connections properley?
By lockstep I mean running the app step by step and checking the response each step....that way you can step through the operation and determine what is going wrong.
Try simulating what the app is doing by using Query Analyser to generate the inserts etc ( instead of the VB code ) & see what SQL does in response.....that will give you a general idea of whether its SQL or app by effectively isolating the parts of the system.
Once you know whats what, post back and anyone on this site should be able to help.
Thanks for this, I have been through the steps now many times :-(
A quick analysis of my findings:
1: When http_get is called from an EXEC statement in QA it works fine, I can direct it to a REALLY slow page (takes 10 seconds to give a HTTP 200 response) and issue around 10 consecutive requests, and also call it multiple times in quick succession, all without a problem..works fine.
2: When the http_get SP is called from the Insert trigger it works fine when a record is inserted directly from Enterprise Manager, an insert statement in QA and when an insert SP is written and called from QA. However when I either use an Insert Statement, or the insert SP from vb.net it works the first time, and not any consecutive times. i have noted from Profiler that although this works on the first attempt, something is not happy as around 5 mins a full Stack dump is created. If i try to re-call the insert SP from vb within this 5 mins the entire SQL server is shut down, if it is called again after the 5 mins then I can re-call the SP from vb with no problems (as long as the SP is not called again within 5 mins)
3: I have tried calling the http_get SP directly from VB, same results as point 2.
4: I have removed the http_get EXEC statement from my insert trigger, this works fine, time and time again.
5: with http_get re-instated in the insert trigger, i have inserted one record from vb.net (works as in point 2) and then quickly followed it by numerous record additions from QA (using the same insert sp) all of these work fine with no problems.
In all these instances the only error messsage i can extract from vb.net is 'General network error. Check your network documentation.'
Any help much appreciated.
I'm now going to try and re-word and re-post this!
Just one thought....set the insert trigger to call a stored procedure that then calls the original stored procedure - I'm trying to create a level of isolation between the trigger execution and the strored procedures' operation.
If you have the trigger call a stored procedure, say, sp_calloriginal
and inside sp_calloriginal you have
Create Procedure dbo.sp_calloriginal
Try that - it should at least isolate where the problem is.