ASP - ODBC - IIS6 2

[redsand23]

I am running a 2003 server, running iis6 and having difficulty with opening ASP pages that connect to either an sql database or Access via odbc. I have browsed the forum and have performed the following tasks
1) Made sure iusr had permissions to the db folder
2) Set execute scripts and executibles
3) Checked and validated system dsn's
4) Made sure all passwords were in sync

I get the following error when running this code
<%@ Language=VBScript %>
<%
option explicit

Dim conOP, rsOP, rsOP1,
Dim StartDate, EndDate, stopDate, wrongDate, enddate1
Dim sqlOP,
Dim Totall, Totallper, Unitotdop, PartTotop, UnitTotop, StartDateop, EndDateop, LtdPartop, LtdUnitop, Partotdop
Dim norec
Dim dataOPAvail

'--------------CREATE DATA CONNECTION AND RECORDSET OBJECTS

Set conOP = Server.CreateObject("ADODB.Connection")
Set rsOP = Server.CreateObject("ADODB.Recordset")

'------------SET UP SQL QUERY

sqlOP = "SELECT Parts1, Units1 FROM OTD"

'------------OPEN UP DATABASE AND RUN QUERY

conOP.open "dsn=Dashboard","sa","sa"


error '8007007f'
/index.asp, line 73
Line 73 being the conOp.open.

This is happening with any asp page accessing either an sql db or an access db on the 2003 server.

I copied the wwroot directory from an NT server running iis4

Any help would be greatly appreciated.

 

[a6m1n0]

Try:

oConn.Open "DSN=Dashboard;Uid=sa;Pwd=sa"

 

[a6m1n0]

OOPS! Didn't use the proper syntax in relation to your code.

The right thing to try:

conOP.open "DSN=Dashboard;Uid=sa;Pwd=sa"

 

[redsand23]

I tried the code, same result.
error '8007007f'
/index.asp, line 72

It errors on any of the asp pages as soon as I try to connect to the db.

 

[Sheco]

If you have physical access to the server this might be easier to test with a little .VBS script file.

Just make one in Notepad, give it the .vbs extension, and run it with a double-click. Something like this:

Set conOP = CreateObject("ADODB.Connection")
conOP.open "dsn=Dashboard","sa","sa"
IF conOP.State = 1 THEN
  MsgBox "Connection OK"
ELSE
  MsgBox "Connection Failed"
END IF

Set rsOP = CreateObject("ADODB.Recordset")
Set rsOP.ActiveConnection = conOP
rsOP.Open "SELECT Parts1, Units1 FROM OTD" 

IF rsOP.State = 1 THEN
  MsgBox "Recordset OK"
  rsOP.Close
Else
  MsgBox "Recordset failed"
End IF

Set rsOP = Nothing
conOP.Close
Set conOP = Nothing

 

[redsand23]

I know I am going to get it for this one. How can you run the vbs script without it closing the window. I can not see what the message is?

 

[Sheco]

No I mean paste that code into Notepad and save the file with a .vbs extension... or save it as a txt file and rename it so that it has the .vbs extension. Then just double click it to run it.

 

[a6m1n0]

If its failing at connecting to the db at the line you specify then the DSN name is incorrect, or the credentials are incorrect, or the connection string is not formatted properly. Just an educated guess.

 

[a6m1n0]

@redsand23

On the server that you are currently running these ASP pages that keep giving you the error '8007007f', was 2003 server installed 'clean' or was it an upgrade installation from NT4 or 2000 Server?

 

[redsand23]

It was a fresh install of 2003 server.

When I double click the .vbs it launches a dos window and it opens and closes within a second. I can never see what it says. Should I put in into a .bat file with some pauses?

Should the .vbs be associating with something other than a command window? I do not have front page or office installed on the 2003 server.

Thanks for being patient!!

 

[a6m1n0]

Try installing/reinstalling SP3a and then installing/reinstalling MDAC v2.8. [The reinstall of MDAC v2.8 is neccessary after applying SP3a--SP3a installs MDAC v2.7.]

 

[redsand23]

I don't think it is sp3(sql). I am having problems with an access database that is stored locally on the iis server locally.

I have re-installed mdac 2.8 with no luck.

Any more thoughts.
I am stumped.

 

[a6m1n0]

Ok, some more questions then.

What is the physical path to where your Access DB is stored? Example: C:\accessdb

What is the db's filename? Example: myaccessdb.mdb

Is the db located somewhere in your webroot or outside the webroot?

Can you open the db in Access? If so, does it prompt you for a username and/or password?

Do you have to use a ODBC(DSN) connection, or can you use an ADO(DSN-less) connection?

Just trying to get some additional info to help you a little better.

Answer those questions for me and I will throw a little ASP script together and post it for you to execute on the server.

Note: I do find it unusual that you are using 'sa' for the username and password to an Access db; those are default credentials for SQL, not Access. It doesn't mean however that you or someone else that has local access to the db didn't set up authentication that way. This is why throughout this thread I have stated that something's fishy about that connection string. I mean, you are getting the error at the line to open the connection.

 

[Sheco]

I am troubled by the fact that when you execute the VBS it just opens a DOS box and disappears without any messagebox.

Please add a messagebox as the first line of the script like this:

MsgBox "Starting VBS Script"

Or somesuch. Run it again and see if you at least get that to show.

 

[redsand23]

You are correct the code above is accessing a sql database. That is why I have the sa.sa

I have other pages that access an access database stored on f:\databases\phonedata.mdb. This one also errors as soon as it tries to connect.
I am using a system odbc(dsn connection). It is named Phone.

Just as a side note on my sql db's I test and do connect to the db's using the odbc administrator.

my root dir is f:\inetpub\

http://wwwroot\.

 

[Sheco]

If your DSN is named Phone then why do you have it as Dashboard in the code?

Or is Phone for the SQLServer and Dashboard for Access?

 

[redsand23]

Other way around. Dashboard is the sql connection. Phone is the access dsn

 

[redsand23]

I re-booted and I ran the script. Here is the error
script: f:\inetpub\

http://wwwroot\test.vbs

line: 2
char: 1
error: The specified procedure could not be found
code : 8007007f
source: adodb.connection

 

[Sheco]

Did you try adding a MsgBox as the first line of the VBS? I'm really puzzzled why the VBS just goes away like that... you should be getting some feedback about the success of the connection and recordset.

 

[a6m1n0]

Let's start with something simple. Open notepad, paste the following code, and save it in your webroot as testing123.asp.

testing123.asp

<%
dim connstr, conn, SQL

connstr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=f:\databases\phonedata.mdb"
set conn = server.create("adodb.connection")
conn.open connstr
response.write("It's open!")
conn.close connstr
set conn = nothing
%>