IIS7 SQL ODBC and Server 2008 R2 (converted site from IIS6 2003 to 2008 R2 IIS7)

Question

Situation :

I have a website currently running on IIS6 (srv2003). I have installed a new server with server 2008 R2 and IIS7.5 (version 7.5)

Step by step I am converting the website to the new IIS7.5, there is only one part still not working.

Problem :

The following script (not modified yet, the one copied from IIS6 environment) wont use the SQL ODBC connection on the server (server 2008 R2). I have read some articles about this and in these articles they refer to a different code, but non of them start with my old code. So I tried, but failed. (unfortunately)

<%
'---- CursorTypeEnum ----
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3

'---- LockTypeEnum  ----
Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3
Const adLockBatchOptimistic = 4

Function SetConn()
Dim objConn 
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString="PDM"
objConn.open
set SetConn=objConn
set objconn=nothing
end function

function SetRecord (oConn,strSQL,sCursorType,sLockType)
dim objRS
set objRS=Server.CreateObject("ADODB.Recordset")
objRS.Open strSQL,objConn,sCursorType,sLockType
set SetRecord=objRS
end function
%>

How can point me to the right direction, (or solve the puzzle) I have searched the website multiple times and have found very useful information but unfortunately I haven't found my solution yet.

Thank you and looking forward to the information.

Answer 1

There is no puzzle.

Replace

objConn.ConnectionString="PDM"
objConn.open

with

objConn.Open 
   "Provider=SQLOLEDB.1;Data Source=adress of server; Initial Catalog=name of used database",
   "user-name",
   "user-password"

I use this line of code without any problems (Windows 7, IIS 7.5 and SQL-Server 2005). Because ODBC does not exists anymore, I am using OLE instead.

Answer 2

So I have the solution, we are still using the odbc(32bit version) on the local machine. But we did some small changes to the code, see below the new and working code. Thanks for helping reporter

<%
'---- CursorTypeEnum ----
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3

'---- LockTypeEnum  ----
Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3
Const adLockBatchOptimistic = 4

Function SetConn()
Dim objConn 
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.open "DSN=PDM;UID=sa;PWD=password"
set SetConn=objConn
set objconn=nothing
end function

function SetRecord (oConn,strSQL,sCursorType,sLockType)
dim objRS
set objRS=Server.CreateObject("ADODB.Recordset")
objRS.Open strSQL,objConn,sCursorType,sLockType
set SetRecord=objRS
end function
%>