Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Sybase ASP, 80004005 socket closed error

Posted on 2005-02-28
7
Medium Priority
?
1,665 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
  • 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
[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

 

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Choosing the right mix of apps is very much necessary for CPAs for making the most of the latest technology through which they can boost their growth.
Ready to kick start your career in 2018? Add app developer skills to your resume. January’s Course of the Month features Android App Development training with hands-on learning.  Read on to learn why these skills are important.
Screencast - Getting to Know the Pipeline
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

577 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