Random SQL Server Error

[NWChowd]

Hello All,
I am toward the end of building an ASP web app that uses a SQL Server 2000 database. I am getting these random errors and cannot figure out what is causing the problem. I mean random in that the errors occur sporadically on a specific page and at a specific line of code. Basically, they are completely random, but pretty much guaranteed to occur.

The two errors I am getting are:

· 1)Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC SQL Server Driver]Unspecified error occurred on SQL Server. Connection may have been terminated by the server.
/accentopto/codefiles/code_products.asp, line 140

· 2) Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionWrite (send()).
/accentopto/codefiles/code_products.asp, line 140

Don’t let the line 140 fool you, as, sometimes the line # or even the file name will change. In any case, here is the VBSCript code near line 140:
Function Products_GetImages(prodid)
Dim cmdGetImages
Dim intProductID
Dim rsTemp
intProductID = prodID
set cmdGetImages = Server.CreateObject("ADODB.Command&quot
cmdGetImages.ActiveConnection = MM_connAccentWeb_STRING
cmdGetImages.CommandText = "dbo.proc_View_ProductImages"
cmdGetImages.Parameters.Append cmdGetImages.CreateParameter("@ProductID",3,1,8,intProductID)
cmdGetImages.CommandType = 4 'type = stored procedure
cmdGetImages.CommandTimeout = 0 'no timeout
cmdGetImages.Prepared = true
set rsTemp = cmdGetImages.Execute ‘this is line 140
'return recordset
set Products_GetImages = rsTemp
End Function


And here is the Stored Procedure I am calling:

/* created by DMiller 4/15/2003 */
CREATE PROCEDURE [dbo].[proc_View_ProductImages]

@ProductID int

AS

SELECT *
FROM tblProductImages
WHERE (product_id = @ProductID)
ORDER BY productimage_vieworder
GO

------


Has anyone else ever experienced this? Any direction or thoughts are greatly appreciated. This is driving me bonkers because it all seems so straight forward. And like I mentioned, it doesn't happen all the time, but is gauranteed to happen at some point.


Much Thanks,
DMill






======================================
"I wish that I may never think the smiles of the great and powerful a sufficient inducement to turn aside from the straight path of honesty and the convictions of my own mind."
-David Ricardo, classical economist
======================================

 

[jackfl]

Do you need to close your connection to the DB? Is it possible that you are running out of connections, and that is creating random results?

 

[NWChowd]

hmmmmm...you may be on to something. However, I changed my code and put the SQL Statement in the code, rather than calling the stored procedure, and now I cannot reproduce the error I was getting before.

ASP Code...
cmdGetImages.CommandText = " SELECT * FROM tblProductImages WHERE (product_id = " & intProductID & &quot ORDER BY productimage_vieworder"
cmdGetImages.CommandType = 1 'type = text
cmdGetImages.CommandTimeout = 0 'no timeout
cmdGetImages.Prepared = true
set rsTemp = cmdGetImages.Execute

Also, this basic code style is used throughout the site and this was the only place I was having troubles.

I'll research the connection closing idea, though.

thanks,
DMilll




======================================
"I wish that I may never think the smiles of the great and powerful a sufficient inducement to turn aside from the straight path of honesty and the convictions of my own mind."
-David Ricardo, classical economist
======================================