info
Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
can't connect to sql server database w/ asp classic
989 views
Skip to first unread message
skifo...@gmail.com
Jan 8, 2013, 12:13:25 AM1/8/13
to
I'm creating a new site using:
-asp classic
-vb script
-Windows 2003 Server
-SQL Server 2005
-IIS 6
(Server and db have been used before but are new to me)
I have the website up, DNS set and the page responds to html and vb script response.write. It site shows detailed server side errors correctly.
The trouble comes when I try to call a query from the SQL Server. Starting at the connection string, It simply returns a blank page with no error message. I have tried every combination of connection string possible. I can run the page from both the server and from the client but have the same problem on both. Finally I broke it down to the the simplest page possible: I dropped the include file and put the connection string in the same file as the query. I used a UDL file to produce the correct connection string. I created a simple test table with 3 columns and one row of data. I used a simple trouble shooting query (see code below). I have tried Windows (SSIP) and SQL server authentication for the connection string. I logged onto the Windows Server as Administrator and onto SQL server using Windows authentication. Just for trouble shooting purposes, I granted the user account in the connection string all of the SQL Server admin privelages (clicked every single box) Here are the two connection strings created by the UDL file, and the simple page I am trying to get to return the row of data. The table does returns the row of data when writing a query in SQL Management Studio. Any html and vbscript above the connection string will show on the page, but nothing below it (no data or html below it) - just blank
Any ideas? Please help! Thanks!
The name of the Database is "REPORTS"
The User Name (with all privelages) is "Reports"
Connection string 1:
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=REPORTS;Data Source=SERVERNAME
Connection string 2: (I have tried many other verions, including ones with the password and all versions on connectionstrings.com site)
Provider=SQLOLEDB.1;Persist Security Info=False;User ID=Reports;Initial Catalog=REPORTS;Data Source=SERVERNAME
Simple Test Page Code:
<%@ Language=VBScript%>
<html>
<body>
<%
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=REPORTS;Data Source=SERVERNAME;"
strSql = "SELECT * FROM TEST"
Set rs = Conn.Execute(strSql)
If not rs.eof then
Do while not rs.eof or rs.bof
Response.write(rs("AA") & "<br>")
rs.movenext
Loop
Else
Response.Write "No records found"
End If
Conn.Close
Set Conn = Nothing
%>
</body>
</html>
-asp classic
-vb script
-Windows 2003 Server
-SQL Server 2005
-IIS 6
(Server and db have been used before but are new to me)
I have the website up, DNS set and the page responds to html and vb script response.write. It site shows detailed server side errors correctly.
The trouble comes when I try to call a query from the SQL Server. Starting at the connection string, It simply returns a blank page with no error message. I have tried every combination of connection string possible. I can run the page from both the server and from the client but have the same problem on both. Finally I broke it down to the the simplest page possible: I dropped the include file and put the connection string in the same file as the query. I used a UDL file to produce the correct connection string. I created a simple test table with 3 columns and one row of data. I used a simple trouble shooting query (see code below). I have tried Windows (SSIP) and SQL server authentication for the connection string. I logged onto the Windows Server as Administrator and onto SQL server using Windows authentication. Just for trouble shooting purposes, I granted the user account in the connection string all of the SQL Server admin privelages (clicked every single box) Here are the two connection strings created by the UDL file, and the simple page I am trying to get to return the row of data. The table does returns the row of data when writing a query in SQL Management Studio. Any html and vbscript above the connection string will show on the page, but nothing below it (no data or html below it) - just blank
Any ideas? Please help! Thanks!
The name of the Database is "REPORTS"
The User Name (with all privelages) is "Reports"
Connection string 1:
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=REPORTS;Data Source=SERVERNAME
Connection string 2: (I have tried many other verions, including ones with the password and all versions on connectionstrings.com site)
Provider=SQLOLEDB.1;Persist Security Info=False;User ID=Reports;Initial Catalog=REPORTS;Data Source=SERVERNAME
Simple Test Page Code:
<%@ Language=VBScript%>
<html>
<body>
<%
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=REPORTS;Data Source=SERVERNAME;"
strSql = "SELECT * FROM TEST"
Set rs = Conn.Execute(strSql)
If not rs.eof then
Do while not rs.eof or rs.bof
Response.write(rs("AA") & "<br>")
rs.movenext
Loop
Else
Response.Write "No records found"
End If
Conn.Close
Set Conn = Nothing
%>
</body>
</html>
Bob Barrows
Jan 8, 2013, 7:00:38 AM1/8/13
to
my experience. I have always used sql server security and passed the
password and user id in the connection string. Your conection string example
above lacks the password attribute. Not sure if you left it out on purpose
due to posting on the internet or if you failed to realize it was required.
In your example code above, without "on error resume next", if the
connection attempt fails on the second line, an error should be raised that
aborts the script. Make sure you have "Friendly Errors" turned off in your
Internet Settings in IE so you can see the error returned.
http://www.aspfaq.com/show.asp?id=2109
Without an error, I would assume a connection is established. This can be
confirmed by using SQL Profiler or SSMS (run "sp_who" in a query window to
see a list of connected users, or use the Activity monitor). If no
connection is established from your application (adding the "application
name" attribute to your connection string would make the connection easier
to find), then you need to investigate why the "failed login" error that
should be getting raised by that second line is not being returned to the
browser. or add error-handling to your code:
<%
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=REPORTS;Data Source=SERVERNAME;"
if err<>0 then
Info=False;Initial Catalog=REPORTS;Data Source=SERVERNAME;"
response.write err.description
response.end
else
response.write "Connection established"
end if
skifo...
Jan 8, 2013, 2:06:56 PM1/8/13
to
On Tuesday, January 8, 2013 4:00:38 AM UTC-8, Bob Barrows wrote:
> skifo...@gmail.com wrote: > I'm creating a new site using: > > -asp classic > -vb script > -Windows 2003 Server > -SQL Server 2005 > -IIS 6 > > (Server and db have been used before but are new to me) > > I have the website up, DNS set and the page responds to html and vb > script response.write. It site shows detailed server side errors > correctly. > > The trouble comes when I try to call a query from the SQL Server. > Starting at the connection string, It simply returns a blank page > with no error message. I have tried every combination of connection > string possible. I can run the page from both the server and from the > client but have the same problem on both. Finally I broke it down to > the the simplest page possible: I dropped the include file and put > the connection string in the same file as the query. I used a UDL > file to produce the correct connection string. I created a simple > test table with 3 columns and one row of data. I used a simple > trouble shooting query (see code below). I have tried Windows (SSIP) > and SQL server authentication for the connection string. I logged > onto the Windows Server as Administrator and onto SQL server using > Windows authentication. Just for trouble shooting purposes, I granted > the user account in the connection string all of the SQL Server admin > privelages (clicked every single box) Here are the two connection > strings created by the UDL file, and the simple page I am trying to > get to return the row of data. The table does returns the row of data > when writing a query in SQL Management Studio. Any html and vbscript > above the connection string will show on the page, but nothing below > it (no data or html below it) - just blank > > Any ideas? Please help! Thanks! > > The name of the Database is "REPORTS" > The User Name (with all privelages) is "Reports" > > Connection string 1: > Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security > Info=False;Initial Catalog=REPORTS;Data Source=SERVERNAME > > Connection string 2: (I have tried many other verions, including ones > with the password and all versions on connectionstrings.com site) > Provider=SQLOLEDB.1;Persist Security Info=False;User > ID=Reports;Initial Catalog=REPORTS;Data Source=SERVERNAME > > Simple Test Page Code: > > <%@ Language=VBScript%> > > <html> > <body> > > <% > Set Conn = Server.CreateObject("ADODB.Connection") > Conn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist > Security Info=False;Initial Catalog=REPORTS;Data Source=SERVERNAME;" > > strSql = "SELECT * FROM TEST" > Set rs = Conn.Execute(strSql) > If not rs.eof then > Do while not rs.eof or rs.bof > Response.write(rs("AA") & "<br>") > rs.movenext > Loop > Else > Response.Write "No records found" > End If > > Conn.Close > Set Conn = Nothing > %> > </body> > </html> It's very difficult to get integrated security working from asp, at least in my experience. I have always used sql server security and passed the password and user id in the connection string. Your conection string example above lacks the password attribute. Not sure if you left it out on purpose due to posting on the internet or if you failed to realize it was required. In your example code above, without "on error resume next", if the connection attempt fails on the second line, an error should be raised that aborts the script. Make sure you have "Friendly Errors" turned off in your Internet Settings in IE so you can see the error returned. http://www.aspfaq.com/show.asp?id=2109 Without an error, I would assume a connection is established. This can be confirmed by using SQL Profiler or SSMS (run "sp_who" in a query window to see a list of connected users, or use the Activity monitor). If no connection is established from your application (adding the "application name" attribute to your connection string would make the connection easier to find), then you need to investigate why the "failed login" error that should be getting raised by that second line is not being returned to the browser. or add error-handling to your code: <% Set Conn = Server.CreateObject("ADODB.Connection") on error resume next Conn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=REPORTS;Data Source=SERVERNAME;" if err<>0 then response.write err.description response.end else response.write "Connection established" end if
-------------
> skifo...@gmail.com wrote: > I'm creating a new site using: > > -asp classic > -vb script > -Windows 2003 Server > -SQL Server 2005 > -IIS 6 > > (Server and db have been used before but are new to me) > > I have the website up, DNS set and the page responds to html and vb > script response.write. It site shows detailed server side errors > correctly. > > The trouble comes when I try to call a query from the SQL Server. > Starting at the connection string, It simply returns a blank page > with no error message. I have tried every combination of connection > string possible. I can run the page from both the server and from the > client but have the same problem on both. Finally I broke it down to > the the simplest page possible: I dropped the include file and put > the connection string in the same file as the query. I used a UDL > file to produce the correct connection string. I created a simple > test table with 3 columns and one row of data. I used a simple > trouble shooting query (see code below). I have tried Windows (SSIP) > and SQL server authentication for the connection string. I logged > onto the Windows Server as Administrator and onto SQL server using > Windows authentication. Just for trouble shooting purposes, I granted > the user account in the connection string all of the SQL Server admin > privelages (clicked every single box) Here are the two connection > strings created by the UDL file, and the simple page I am trying to > get to return the row of data. The table does returns the row of data > when writing a query in SQL Management Studio. Any html and vbscript > above the connection string will show on the page, but nothing below > it (no data or html below it) - just blank > > Any ideas? Please help! Thanks! > > The name of the Database is "REPORTS" > The User Name (with all privelages) is "Reports" > > Connection string 1: > Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security > Info=False;Initial Catalog=REPORTS;Data Source=SERVERNAME > > Connection string 2: (I have tried many other verions, including ones > with the password and all versions on connectionstrings.com site) > Provider=SQLOLEDB.1;Persist Security Info=False;User > ID=Reports;Initial Catalog=REPORTS;Data Source=SERVERNAME > > Simple Test Page Code: > > <%@ Language=VBScript%> > > <html> > <body> > > <% > Set Conn = Server.CreateObject("ADODB.Connection") > Conn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist > Security Info=False;Initial Catalog=REPORTS;Data Source=SERVERNAME;" > > strSql = "SELECT * FROM TEST" > Set rs = Conn.Execute(strSql) > If not rs.eof then > Do while not rs.eof or rs.bof > Response.write(rs("AA") & "<br>") > rs.movenext > Loop > Else > Response.Write "No records found" > End If > > Conn.Close > Set Conn = Nothing > %> > </body> > </html> It's very difficult to get integrated security working from asp, at least in my experience. I have always used sql server security and passed the password and user id in the connection string. Your conection string example above lacks the password attribute. Not sure if you left it out on purpose due to posting on the internet or if you failed to realize it was required. In your example code above, without "on error resume next", if the connection attempt fails on the second line, an error should be raised that aborts the script. Make sure you have "Friendly Errors" turned off in your Internet Settings in IE so you can see the error returned. http://www.aspfaq.com/show.asp?id=2109 Without an error, I would assume a connection is established. This can be confirmed by using SQL Profiler or SSMS (run "sp_who" in a query window to see a list of connected users, or use the Activity monitor). If no connection is established from your application (adding the "application name" attribute to your connection string would make the connection easier to find), then you need to investigate why the "failed login" error that should be getting raised by that second line is not being returned to the browser. or add error-handling to your code: <% Set Conn = Server.CreateObject("ADODB.Connection") on error resume next Conn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=REPORTS;Data Source=SERVERNAME;" if err<>0 then response.write err.description response.end else response.write "Connection established" end if
Thanks for the tips Bob. Here are the results:
-Friendly errors are turned off in browser (detailed error messages with line numbers do appear in my browser for other vbscript errors)
-I added the SQL Server authentication password and Application name to the connection string. It now looks like this:
Provider=SQLOLEDB.1;Password=MY_PASSWORD;Application Name=MyApp;Persist Security Info=True;User ID=Reports;Initial Catalog=REPORTS;Data Source=MY_SERVERNAME
-I confirmed the connection string does work when I test it in the Windows Data Link / UDL application.
-I added your error handling code (on error resume next) above the connection string.
- I monitored the connection activity in my reports database using activity monitor. I confirmed the activities DOES appear in the monitor when I run my web application in this case:
-when I ADD the line "on error resume next" (between "Set Conn" and "Conn.Open" lines, the activity DOES appear
in the activity monitor and the web application just churns with an hour glass indefinately.
-when I REMOVE the line "on error resume next" the activity does NOT appear in the activity monitor and the web application just stops (blank page and reports "done" at the bottom of the browser.
I assume this means that an error is coming from the line: Set Conn = Server.CreateObject("ADODB.Connection")...But for some reason the error
is not being produced on the browser. (detail error messages are produced for other simple vbscript errors)
I also added the other error handler you suggested below that, but it seems to make no difference - I assume because the application is getting hung at the above mentioned line.
Other info about my app:
- I granted ALL database permission types for user "Reports" at the Server Level and the Website level Properties (for temporary trouble shooting purposes only)
- Execute permissions are set to allow in the web site / IIS properties
Any suggestions on how to trouble shoot from this point?
Thanks again!
skifo...@gmail.com
Jan 8, 2013, 5:43:38 PM1/8/13
to
On Monday, January 7, 2013 9:13:25 PM UTC-8, skifo...@gmail.com wrote:
> I'm creating a new site using: -asp classic -vb script -Windows 2003 Server -SQL Server 2005 -IIS 6 (Server and db have been used before but are new to me) I have the website up, DNS set and the page responds to html and vb script response.write. It site shows detailed server side errors correctly. The trouble comes when I try to call a query from the SQL Server. Starting at the connection string, It simply returns a blank page with no error message. I have tried every combination of connection string possible. I can run the page from both the server and from the client but have the same problem on both. Finally I broke it down to the the simplest page possible: I dropped the include file and put the connection string in the same file as the query. I used a UDL file to produce the correct connection string. I created a simple test table with 3 columns and one row of data. I used a simple trouble shooting query (see code below). I have tried Windows (SSIP) and SQL server authentication for the connection string. I logged onto the Windows Server as Administrator and onto SQL server using Windows authentication. Just for trouble shooting purposes, I granted the user account in the connection string all of the SQL Server admin privelages (clicked every single box) Here are the two connection strings created by the UDL file, and the simple page I am trying to get to return the row of data. The table does returns the row of data when writing a query in SQL Management Studio. Any html and vbscript above the connection string will show on the page, but nothing below it (no data or html below it) - just blank Any ideas? Please help! Thanks! The name of the Database is "REPORTS" The User Name (with all privelages) is "Reports" Connection string 1: Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=REPORTS;Data Source=SERVERNAME Connection string 2: (I have tried many other verions, including ones with the password and all versions on connectionstrings.com site) Provider=SQLOLEDB.1;Persist Security Info=False;User ID=Reports;Initial Catalog=REPORTS;Data Source=SERVERNAME Simple Test Page Code: <%@ Language=VBScript%> <html> <body> <% Set Conn = Server.CreateObject("ADODB.Connection") Conn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=REPORTS;Data Source=SERVERNAME;" strSql = "SELECT * FROM TEST" Set rs = Conn.Execute(strSql) If not rs.eof then Do while not rs.eof or rs.bof Response.write(rs("AA") & "<br>") rs.movenext Loop Else Response.Write "No records found" End If Conn.Close Set Conn = Nothing %> </body> </html>
MORE INFO:
> I'm creating a new site using: -asp classic -vb script -Windows 2003 Server -SQL Server 2005 -IIS 6 (Server and db have been used before but are new to me) I have the website up, DNS set and the page responds to html and vb script response.write. It site shows detailed server side errors correctly. The trouble comes when I try to call a query from the SQL Server. Starting at the connection string, It simply returns a blank page with no error message. I have tried every combination of connection string possible. I can run the page from both the server and from the client but have the same problem on both. Finally I broke it down to the the simplest page possible: I dropped the include file and put the connection string in the same file as the query. I used a UDL file to produce the correct connection string. I created a simple test table with 3 columns and one row of data. I used a simple trouble shooting query (see code below). I have tried Windows (SSIP) and SQL server authentication for the connection string. I logged onto the Windows Server as Administrator and onto SQL server using Windows authentication. Just for trouble shooting purposes, I granted the user account in the connection string all of the SQL Server admin privelages (clicked every single box) Here are the two connection strings created by the UDL file, and the simple page I am trying to get to return the row of data. The table does returns the row of data when writing a query in SQL Management Studio. Any html and vbscript above the connection string will show on the page, but nothing below it (no data or html below it) - just blank Any ideas? Please help! Thanks! The name of the Database is "REPORTS" The User Name (with all privelages) is "Reports" Connection string 1: Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=REPORTS;Data Source=SERVERNAME Connection string 2: (I have tried many other verions, including ones with the password and all versions on connectionstrings.com site) Provider=SQLOLEDB.1;Persist Security Info=False;User ID=Reports;Initial Catalog=REPORTS;Data Source=SERVERNAME Simple Test Page Code: <%@ Language=VBScript%> <html> <body> <% Set Conn = Server.CreateObject("ADODB.Connection") Conn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=REPORTS;Data Source=SERVERNAME;" strSql = "SELECT * FROM TEST" Set rs = Conn.Execute(strSql) If not rs.eof then Do while not rs.eof or rs.bof Response.write(rs("AA") & "<br>") rs.movenext Loop Else Response.Write "No records found" End If Conn.Close Set Conn = Nothing %> </body> </html>
I set up more error handlers and isolated the problem to the "Execute Query" part of the code: Set rs = Conn.Execute(strSql)
Based on the code below, I successfully got "Set Conn established" "Connection Open Established", but I did not get "Execute completed"
I still got no error message and no data row of data returned.
I also checked the SQL Server Log file viewer and there is no log. I tested the log file viewer by putting the wrong password in the connection string and did get a log entry consistent for no password.
<%
Set Conn = Server.CreateObject("ADODB.Connection")
if err<>0 then
response.write err.description
response.end
else
response.write "Set Conn established"
response.write err.description
response.end
else
end if
%>
<br>
<%
Conn.Open "Provider=SQLOLEDB.1;Password=MY_PASSWORD;Application Name=MyApp;Persist Security Info=False;User ID=Reports;Initial Catalog=REPORTS;Data Source=MY_SERVER;"
if err<>0 then
response.write err.description
response.end
else
end if
strSql = "SELECT * FROM TEST"
Set rs = Conn.Execute(strSql)
<br>
<%
if err<>0 then
err1 = err.description
response.write err1
response.end
else
response.write "Execute completed"
end if
Bob Barrows
Jan 8, 2013, 7:24:40 PM1/8/13
to
skifo... wrote:
> - I monitored the connection activity in my reports database using
> activity monitor. I confirmed the activities DOES appear in the
> monitor when I run my web application in this case:
So a connection is being established.
> - I monitored the connection activity in my reports database using
> activity monitor. I confirmed the activities DOES appear in the
> monitor when I run my web application in this case:
>
> -when I ADD the line "on error resume next" (between "Set Conn" and
> "Conn.Open" lines, the activity DOES appear
> in the activity monitor and the web application just churns with an
> hour glass indefinately.
> -when I REMOVE the line "on error resume next" the activity does NOT
> appear in the activity monitor and the web application just stops
> (blank page and reports "done" at the bottom of the browser.
>
> I assume this means that an error is coming from the line: Set Conn =
> Server.CreateObject("ADODB.Connection")...
No, I don't understand that conclusion. If that line raised an error, the
> -when I ADD the line "on error resume next" (between "Set Conn" and
> "Conn.Open" lines, the activity DOES appear
> in the activity monitor and the web application just churns with an
> hour glass indefinately.
> -when I REMOVE the line "on error resume next" the activity does NOT
> appear in the activity monitor and the web application just stops
> (blank page and reports "done" at the bottom of the browser.
>
> I assume this means that an error is coming from the line: Set Conn =
> Server.CreateObject("ADODB.Connection")...
connection would not be established.
I suspect no error is being raised and that your code is either in an
infinite loop or processing so many records in your recordset that you run
out of patience. To verify this, remove the loop and simply write the value
of the field in the first record and close the recordset.
Bob Barrows
Jan 8, 2013, 7:43:24 PM1/8/13
to
work: the err object's value property will never be set to anything but 0.
How many records are there in TEST?
Can you use SQL Profiler to trace what the server is doing?
0 new messages