Different MS SQL Connection Methods

[ethoemmes]

Hi,

I am a newbie to ASP and MS SQL - I normally develop in LAMP. I have inherited a site which I am trying to migrate to a new server for a client.

I am having trouble connecting to the database. As far as I can see there is two separate ways to connect through the site.
One is using a connection string stored in a conn.asp (config file) the connection string is then used by some code which calls
the below funtion. This method works correctly.

Function call:
Code:

Set rsmenu = RunSPReturnRS(MM_AccessAsia_STRING,"sp_GetMainMenuItems",param)



Function:
Code:

Function RunSPReturnRS(ByVal Connect,ByVal strSP,ByVal params)
Dim rs
Dim i
' Create the ADO objects
Set rs = Server.CreateObject("ADODB.Recordset")
Set cmd = Server.CreateObject("ADODB.Command")

' Init the ADO objects & the stored proc parameters
cmd.ActiveConnection = Connect
cmd.CommandText = strSP
cmd.CommandType = adCmdStoredProc
'Create the parameters
' if UBound(params)<>0 then
For i = LBound(params) To UBound(params)
If Not IsEmpty(params(i, 0)) Then
cmd.Parameters.Append cmd.CreateParameter(params(i, 0), params(i, 1), adParamInput, params(i, 2), params(i, 3))
End If
Next
' End if
' Execute the query for readonly
rs.CursorLocation = adUseClient
rs.Open cmd, , adOpenForwardOnly, adLockReadOnly

' Disconnect the recordset
Set cmd.ActiveConnection = Nothing
Set cmd = Nothing
Set rs.ActiveConnection = Nothing

' Return the resultant recordset
Set RunSPReturnRS = rs
Set rs = Nothing
End Function



The other way which is not working uses a connection string in global.asa (This is the same connection string as above). The below function
results in the following error message "Microsoft OLE DB Provider for SQL Server error '80040e4d' Login failed for user 'xxxx'. /includes/db.asp, line 134"

Function call:
Code:

Set rs= ExecSQLFo(Session("Know_ConnectionString"),"","",sqlstr)



Function:
Code:

Function ExecSqlFo(ByVal Connect,ByVal UserID ,ByVal Pwd , ByVal sql )

' Set up Command and Connection objects
Dim rs, db_Conn

Set db_Conn = CreateObject("ADODB.Connection")
db_Conn.Open Connect, UserID, Pwd ****LINE 134 - ERROR OCCURS HERE*****

Set rs = CreateObject("ADODB.Recordset")
rs.CursorLocation = 3
rs.Open sql, db_Conn, adOpenForwardOnly, , adCmdText

If rs.Fields.Count Then
Set ExecSqlFO = rs
Set rs.ActiveConnection = Nothing
Else
Set rs = Nothing
End If

db_Conn.Close
Set db_Conn = Nothing

End Function


Connection string used in both connections:
Code:

Session("Know_ConnectionString") = "Provider=SQLOLEDB.1;User ID=xxxx;Password=xxxx;Initial Catalog=xxxx;Data Source= xxxx;"



Could someone explain to me why one function works where the other doesn't?

Thanks in advance

Edgar