connection string causes blank page

[Paul Robinson]

I am developing a website in ASP that connects to a Sybase database.
However, when I try to open a connection to the database the page will not
load. The script does not timeout, nor the connection. Further, the Sybase
server shows no connection. Because the page is hanging I am unable, with my
limited knowledge, to query the error collection.
After many tries I will get an error page ...HTTP 403.9 Access forbidden:
Too many users are connected
I have created a connection string using the native sybase ole db provider
'Provider=Sybase.ASEOLEDBProvider.2' in Visual InterDev which can view the
database with all its tables, data etc with no problem whatsoever.
I am using Sybase ASE 12.5 developer edition (the server that is available
free from the Sybase site), on a laptop running XP Professional.
I have also tried using a Data Source .ids file within the connection string
as well as without.
Can anyone give me a clue as to how I might progress in tracking the
error(s)?

 

[Steven Burn]

www.connectionstrings.com

--

Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!

 

[Aaron Bertrand [MVP]]

Can anyone give me a clue as to how I might progress in tracking the

error(s)?

First off, you could try showing us the actual code that InterDev produced.
If you are using any of its wizards, you might try opening notepad and
creating a file that does simply this:

<%
set conn = CreateObject("ADODB.Connection")
conn.open "<sybase connection string here>"
conn.close()
set conn = nothing
%>

If this hangs, try a page without an ADODB.Connection at all. Maybe you
have a much more generic problem. If the above just spins, try to set low
timeout values (see http://www.aspfaq.com/2066) and introduce some basic
error handling so you can catch the error message yourself instead of
relying on IIS to report it to you correctly (and your browser to not muck
it up).

 

[Paul Robinson]

Yes, Steve, I have tried these connection strings, also those detailed at
www.able-consulting.com ...all to no avail.
I have replicated the database in Access XP and using another connection
string, the .asp page can connect and show the data. Hence I believe the
problem is with the connection string. What confuses me are the following 2
points :
1) Visual InterDev can see the database no problem and swiftly shows any
data requested, yet the same connection string on the asp page produces
nothing
2) The asp page just hangs, with no error message at all. But something
must be happening because after so many attempts I generate the HTTP 403.9
error message, so there must be some kind of connection made, though not to
the Sybase server...

 

[Paul Robinson]

Aaron, thanks for your reply.
Looking at your advice in reverse order:

The trial connection hangs just like all my other attempts.

The code produced by InterDev for the connection string is detailed
below. As I have noted in my reply to advice from Steven Burn, with this
connection string InterDev rapidly accesses the database and shows the data
in the design ide. My asp page code is OK because I can open the page
without any delay or problem if I change the database to Access, where I
have replicated the tables. Further, if I comment out or remove any code
relevant to the Sybase connection, then the page loads OK. It is only when I
attempt to open the connection that the page hangs.I receive no error
message, just a blank page still trying to load, until I have tried to
connect enough times to generate the HTTP 403.9 error message. Sybase does
not show any connection coming through, so what is happening?

This is the connection string generated by InterDev
Provider=Sybase.ASEOLEDBProvider.2;Initial Catalog=EMD;User ID=sa;Data
Source=EMDhome;Extended Properties="";Server Name=SDT02;Network
Protocol=Winsock;Server Port Address=5001;HA Server Name="";HA Server Port
Address="";Character Set="";Language="";Application Name="";Optimize
Prepare=Partial;Select Method=Direct;Raise Error Behavior=MS
Compatible;Print Statement Behavior=MS Compatible;Extended
ErrorInfo=TRUE;Stored Proc Row Count=Last Statement Only;WorkStation
ID="";Row Cache Size=50;Enable Quoted Identifiers=0;Packet Size=1;Default
Length For Long
Data=1024;UseSybaseLDAP=False;SybaseLDAPURL="";SybaseServerName=389;UseLDAPH
AServer=0;Use SSL=0;Trusted Root File Name="";Interfaces File="";Interfaces
File Server Name="";EnableSPColumnTypes=True;TruncateTimeTypeFractions=1

These are the code extracts on my asp pages:

Dim objConn, strConnect, objRS, strSQLproc
Set objConn = server.CreateObject("ADODB.Connection")
' strConnect = Application("connEMD_ConnectionString")
' strConnect = "Provider=Sybase.ASEOLEDBProvider.2;Data Source=EMD;Server
Name=SDT02;Network Protocol=Winsock;Server Port Address=5001;User
Id=sa;Password="
Dim arrOperators, iCount strSQLproc = "SELECT * FROM tblOperator"
' strSQLproc = "EXECUTE procOperators1"
objConn.Open strConnect
Set objRS = objConn.Execute(strSQLproc)
arrOperators = objRS.GetRows
objConn.Close
Set objConn=Nothing
...

 

[Bob Barrows [MVP]]

Aaron, thanks for your reply.
Looking at your advice in reverse order:

The trial connection hangs just like all my other attempts.

The code produced by InterDev for the connection string is
detailed below. As I have noted in my reply to advice from Steven
Burn, with this connection string InterDev rapidly accesses the
database and shows the data in the design ide.

Different machine, different user account than the one that asp uses.

My asp page code is OK
because I can open the page without any delay or problem if I change
the database to Access, where I have replicated the tables. Further,
if I comment out or remove any code relevant to the Sybase
connection, then the page loads OK. It is only when I attempt to open
the connection that the page hangs.I receive no error message, just a
blank page still trying to load, until I have tried to connect enough
times to generate the HTTP 403.9 error message. Sybase does not show
any connection coming through, so what is happening?

It sounds as if your network is not finding the Sybase server (and is
failing to time out ... very unusual)

Have you tried searching the Sybase KB?
http://my.sybase.com/search_advanced

More below:

This is the connection string generated by InterDev
Provider=Sybase.ASEOLEDBProvider.2;Initial Catalog=EMD;User ID=sa;

Mistake #1 - never use the sa account for your applications! Create an
account with limited permissions to be used by your apps. Protect the sa
account as if your server/network/job depends on it - it does.

Data
Source=EMDhome;

Have you tried specifying the port number as described at able-consulting?
Is this quote from able-consulting relevant?
**********************************************************
Note: The Open Client 12 Sybase OLE DB Provider fails to work without
creating a Data Source .IDS file using the Sybase Data Administrator.
These .IDS files resemble ODBC DSNs.

Note: With Open Client 12.5, the server port number feature finally works,
allowing fully qualified network connection strings to be used without
defining any .IDS Data Source files.

***********************************************************

Extended Properties="";Server Name=SDT02;Network
Protocol=Winsock;Server Port Address=5001;HA Server Name="";HA Server
Port Address="";Character Set="";Language="";Application
Name="";Optimize Prepare=Partial;Select Method=Direct;Raise Error
Behavior=MS Compatible;Print Statement Behavior=MS Compatible;Extended
ErrorInfo=TRUE;Stored Proc Row Count=Last Statement Only;WorkStation
ID="";Row Cache Size=50;Enable Quoted Identifiers=0;Packet
Size=1;Default Length For Long
Data=1024;UseSybaseLDAP=False;SybaseLDAPURL="";SybaseServerName=389;UseLDAPH
AServer=0;Use SSL=0;Trusted Root File Name="";Interfaces
File="";Interfaces File Server
Name="";EnableSPColumnTypes=True;TruncateTimeTypeFractions=1

A lot of these extended properties look like the defaults, and they can
probably be eliminated from the connection string used by your application.

These are the code extracts on my asp pages:

Dim objConn, strConnect, objRS, strSQLproc
Set objConn = server.CreateObject("ADODB.Connection")
' strConnect = Application("connEMD_ConnectionString")
' strConnect = "Provider=Sybase.ASEOLEDBProvider.2;Data
Source=EMD;Server Name=SDT02;Network Protocol=Winsock;Server Port
Address=5001;User Id=sa;Password="

I hope this does not mean your sa password is an empty string - have you
never heard of the Slammer internet worm? It propogates by attack sql
servers where the sa password is blank. Hopefully, you've just censored it
for this newsgroup message.

 

[Paul Robinson]

I have made an 'interesting' development...
I have downloaded an evaluation copy of DataDirect's latest ODBC drivers. I
created a DSN connection and then allowed InterDev to access that connection
and create a connection string. I can now load my asp page with the new
connection string, which is...

"Provider=MSDASQL.1;Persist Security Info=False;User ID=sa;Data
Source=EMDDDodbc;Extended
Properties=""DSN=EMDDDodbc;UID=sa;NA=SDT02,5001;DB=EMD;"";User Id=sa;"

Your comments, Bob, about using the sa login are noted, and I particularly
appreciate the info about the slammer virus. Now that I am aware, I will
amend the login. When I have finished the website it will be moved to a
production server from my laptop and a new default login with password will
be assigned.

I am still left wondering why the Sybase OLE DB Provider does not work for
the asp page. Could it be a problem with my IIS settings?

Thanks for all the help and advice