Strange Connections held
-
1) I have a vb6 app that calls a simple stored procedure.
... With adoCommand
.ActiveConnection = CONNECTION (OLEDB-SQL SERVER)
.CommandText = "sp1"
.CommandType = adCmdStoredProc
.Execute
.ActiveConnection.Close
End With
...
the goal is clear ASP_NET cache.
2) sp1 calls sp2 that do an http_request (via sp_OAmethod and ServerMsXML....) to localhost.....NO ERROR WAS FOUND!!!!
3) When I run program, with sp_who2 I discovered that the connection is truly closed, but in 30 sec....ANOTHER CONNECTION IS MADE !!!!!
52 RUNNNABLE saHOST12 . TEST AWAITING COMMAND50508/22 17:05:40Visual Basic 52
... This NEW connection is kill safe
) !!!!!!!!!! ie"SPID 52: transaction rollback in progress.
Estimated rollback completion: 100%. Estimated time remaining: 0 seconds"
-------------------------------------------------------------
THE ONLY WAY TO KILL process 52 IS RESTART SQL SERVER !!!!!!!
-------------------------------------------------------------
... I have also try with debugger to be sure that the request is ONE....same result...
ANY suggestion?????
THANKS IN ADVANCE
-
This was removed by the editor as SPAM
-
Have you run profiler to see if there is a connection from somewhere else?
-
Just throwing ideas around...
Can it be connection pooling?
Or yourself not closing a connection somewhere (EM, QA, APP)
-
Sorry for my delay..
No other connection ....
1) Connection pooling? what see?
2) I've seen that the problem is related with the use of this procedure (GetUrl).
All is working well but, after having closed connection....AFTER a while,....I have a new connection.....
I'm very confused.
THANKS..
here is the code.....
CREATE PROCEDURE GetUrl
@url varchar(1024)
AS -- WITH ENCRYPTION AS
SET NOCOUNT ON
DECLARE @xml int, @hr int, @src varchar(255), @desc varchar(255)
EXEC @hr = master.dbo.sp_OACreate 'MSXML2.ServerXMLHTTP', @xml OUTPUT
IF @hr <> 0
BEGIN
EXEC master.dbo.sp_OAGetErrorInfo @xml, @src OUTPUT, @desc OUTPUT
SELECT convert(varbinary(4),@hr) hr, @src [Src], @desc [desc]
RETURN 0
END
EXEC @hr = master.dbo.sp_OAMethod @xml, 'Open', NULL, 'GET', @url, 0
IF @hr <> 0
BEGIN
EXEC master.dbo.sp_OAGetErrorInfo @xml, @src OUTPUT, @desc OUTPUT
SELECT convert(varbinary(4),@hr) hr, @src [Src], @desc [desc]
RETURN 0
END
EXEC @hr = master.dbo.sp_OAMethod @xml, 'Send', NULL
IF @hr <> 0
BEGIN
EXEC master.dbo.sp_OAGetErrorInfo @xml, @src OUTPUT, @desc OUTPUT
SELECT convert(varbinary(4),@hr) hr, @src [Src], @desc [desc]
RETURN 0
END
-- this returns a resultset with the webpage
EXECUTE master.dbo.sp_OAGetProperty @xml, 'responseText'
EXEC @hr = master.dbo.sp_OADestroy @xml
IF @hr <> 0
BEGIN
EXEC master.dbo.sp_OAGetErrorInfo @xml, @src OUT, @desc OUT
SELECT convert(varbinary(4),@hr) hr, @src [Src], @desc [desc]
RETURN 0
END
RETURN 0
-
Probably bugs in the sp_OA not releasing resources.
The same can happen in other xps such as xp_sendmail.
For example, prior to hotfix (922, 923 or 913 for SP3a?), xp_sendmail coudl hang and a restart of SQL was needed, say if you exchange server dropped offline or was rebooted hil SQL was reusing it.
sp_OA... are notoiously buggy...
-
thanks for response....
if there is someone that use sp_OA with this problem (or similar) please post here
In the meanwhile I have resolved moving all the logic in a script ie
using "xp_cmdshell 'ResetCache.vbs'"
-
I've learned to stay away from those sp_oa, seen too many problems caused by this.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply