On Tuesday, January 8, 2013 4:00:38 AM UTC-8, Bob Barrows wrote:
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?