?
Solved

Sybase ASP, 80004005 socket closed error

Posted on 2005-02-28
7
Medium Priority
?
1,622 Views
Last Modified: 2012-05-05
Hi experts,

I have Sybase ASE 12.5.2 installed on a windows XP pro machine. I have a database which i am connecting to from an ASP page via the following code:

set conn = server.createobject("adodb.connection")
conn.Open "Provider=Sybase.ASEOLEDBProvider;Server Name=MYSERVER;Initial Catalog=DBNAME;User Id=USERID;Password=PASSWORD"

I can execute insert & update statements, create recordsets and loop through them, however when I try to create more than 5 recordsets I receive one of 2 error messages on the line where i try to open my 6th recordset. The error messages I get are:

ASE OLE DB Provider (0x80004005)
Socket closed.
/_sybaseTest.asp, line 27

AND

ASE OLE DB Provider (0x80004005)
Unexpected Network Error. ErrNum = %d1
/_sybaseTest.asp, line 29

for example when running an asp page with the following code, the error is thrown on the line ' set rs6 = conn.execute("select * from mytable order by id") '
(In reality i do not wish to create 6+ copies of the same recordset, but this drives home the fact that it's the number of sets that's causing the problem)

set rs1 = conn.execute("select * from mytable order by id")

counter = 0
while not rs1.EOF
      counter = counter + 1
      Response.Write(counter & "<br>")
      rs1.movenext
wEnd

set rs2 = conn.execute("select * from mytable order by id")
set rs3 = conn.execute("select * from mytable order by id")
set rs4 = conn.execute("select * from mytable order by id")
set rs5 = conn.execute("select * from mytable order by id")
set rs6 = conn.execute("select * from mytable order by id")          %error occurs here, all of the above runs fine
set rs7 = conn.execute("select * from mytable order by id")
set rs8 = conn.execute("select * from mytable order by id")


I am not sure if i need to configure something in sybase, in windows xp or in my code.

Advise would be greatly appreciated.
0
Comment
Question by:9471vas1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 13424394
Is there anything in the ASE errorlog (by default, in $SYBASE/$SYBASE_ASE/install)?

What edition of ASE is this? ie. the Developer's Edition, or the full commercial version?

I'm wondering if it's a server-side limit in ASE, or a client-side problem in ASP or OLE...

What's the currently configured number of users in ASE? (sp_configure "number of user connections")
0
 

Author Comment

by:9471vas1
ID: 13425134
The only log file i could find was sqlerror.log - which doesn't appear to have anything related to this, just a few stored procedure creation glitches.

This is the Developers Edition of ASE, v12.5.2 for windows NT/2000

The number of user connections is set to 5 (which is the min & max value, i am viewing this via the sybase central java app), however I don't believe this is the problem as several times i have now seen the asp page make it to rs6 & rs7 before throwing it's error (a few times it's even thrown it on rs4...). And surely it is the conn object which takes a connection, not each recordset !?

could it be that I just need to increase one of the memory / cache sizes in the server config list of attributes? I have tried a few but to no avail....

Any help / suggestions greatly appreciated.
0
 
LVL 24

Accepted Solution

by:
Joe Woodhouse earned 1500 total points
ID: 13425382
I forgot Windows was the platform here - Sybase ASE will be writing to the Windows Event Log.

Checking that will very quickly confirm or deny that it's a server-side connection issue. I'm getting suspicious because you said it dies after 5 connections, and 5 is the magic number for the Developer's Edition.

I agree that it would make a lot more sense for the connection object to hold the connection, and not just the recordset, but I'm wondering if there's something going on with connection caching. That could even explain you dying on rs4 or rs7, depending on how quickly timeouts were occuring.

The Sybase errorlog (in the Windows Event Log) is the key here. If you are exhausting Sybase user connections, there will be very clear messages there to that effect. And if that's the issue, then there's nothing you can do about it while remaining with the Developer's Edition.

If it isn't a server-side connection count (and I agree, it doesn't seem like it should be, but I want to rule it out), then I kinda doubt it could be anything else configured in ASE - but once more, the Sybase errorlog would show us if it was.
0
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 

Author Comment

by:9471vas1
ID: 13427518
nope, no sybase user connection messages in the error log, or anything that seems relevant.... and it now seems to be throwing the error on rs7 more frequently than any other line.

0
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 13428216
About the only other idea I have is to try issuing the same SQL directly to Sybase via the built-in "isql" utility. This will isolate it to just the Sybase client and Sybase server. If you don't see the same problem recur then it's pretty conclusive evidence that it's something in OLEDB or ASP.

If you haven't used isql before, you'd call it from the Windows command line:

isql  -U[user]  -S[server]  -P[password]   -i[input file]

You could then put seven SELECT statements in a row in the input file:

SELECT ...
go
SELECT ...
go
[etc]

These would be returning multiple result sets but through a single connection. I can't really believe ASE will have a problem with this, so my bet is that it will be something in the non-Sybase connectivity layers. (In which case I won't be able to do much for you as I don't know OLEDB or ASP 8-<)
0
 

Author Comment

by:9471vas1
ID: 13444964
After some hunting around, I have found sybase's event log, it is not in the windows event log. There is a txt file SERVERNAME.log in the "Sybase\ASE-12_5\install" dir, this confirms that my single asp page is using up all of the user connections - one for each recordset created. Also it appears that when you close a recordset you are not guarenteed to get your user connection back straight away, which is why the numbers of recordsets created have been jumping around. Although how we managed to keep getting 6 is a mystery, just shows how dodgy this area of sybase is.

In summary, sybase is horrific when it comes to asp if you don't have more user-connections than recordsets.

Thanks for your help on this one Joe, As there is no real solution and as you did point me towards the right area I am awarding you the points. At least with your help we now know what the exact nature of the problem is.

0
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 13445383
The limit of 5 user connections is a hard limit in the Developer's Edition of ASE. There's nothing you can do about it.

Note there is an Express Edition on Linux which is still free, and while it has some limits they are generally higher than those of the DE.

I strongly suspect there are timeouts that could be enforced in ASP to release connections more quickly. I wouldn't be surprised if there was some way of forcing all recordsets through the same connection object to use only a single connection in the database.

Beyond that I'm out of ideas. I'm glad I could at least help you find out what the problem was, and you have the Linux Express Edition as an upgrade path if this is a showstopper.
0

Featured Post

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We are witnesses that everyone is saying that our children shouldn't "play" with a technology because it is dangerous. This article is going to prove that they are wrong.
The top devops trends for 2017 are focused on improved deployment frequency, decreased lead time for change and decreased MTTR.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question