Sybase ASP, 80004005 socket closed error

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.
9471vas1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Joe WoodhousePrincipal ConsultantCommented:
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
9471vas1Author Commented:
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
Joe WoodhousePrincipal ConsultantCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

9471vas1Author Commented:
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
Joe WoodhousePrincipal ConsultantCommented:
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
9471vas1Author Commented:
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
Joe WoodhousePrincipal ConsultantCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.