Crashing SQL Boxes

Hi,

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

Any help much appreciated

TIAHowdy

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.

Try updating to MDAC 2.7 SP1 refresh 1

Cheers,

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

Cheers

ED

Originally posted by sqlguy7777
Howdy

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.

Try updating to MDAC 2.7 SP1 refresh 1

Cheers,

SG.|||Howdy,

Try altering the VB code to open & close the connection between each firing of the trigger...it will make the app slow but at least may show where the problem is.....

Cheers,

SG|||Originally posted by sqlguy7777
Howdy,

Try altering the VB code to open & close the connection between each firing of the trigger...it will make the app slow but at least may show where the problem is.....

Cheers,

SG

How exactly do you do that?

There's no control over a trigger except the code that's in it or some parameters on bulk insert or the ability to disable one...

No?|||And I thought crashing SQL serves meant...you take 2 servers and fling them at each other at 55 mph down the highway...

great sport that is...

oh the days...|||Howdy Brett, Ed,

Ok Ok......

I was talking in general terms... Ed will have to alter his code to break the code down into chunks so its operating "lock step" while diagnosing the problem.

As to 55 mph........try 95 mph around the M25 in the morning when late for work.....great way to wake up....

Cheers,

SG|||Cheers Guys,

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?

Any help much appreciated!|||Howdy,

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.

Cheers,

SG.|||SG,

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.

Many thanks

Ed

I'm now going to try and re-word and re-post this!|||Howdy,

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
AS
exec <the_original_insert_stored_procedure_name>
GO
-----

Try that - it should at least isolate where the problem is.

Cheers,

SG.