Database connection from one server to another

[Adders]

Hi,

I'm having trouble connecting to a database on another server. I have setup the DSN which in testing works perfect. However when running the asp script with the follwing code:

<%
Dim rsGetInvoiceContact
Dim rsGetInvoiceContact_numRows

Set rsGetInvoiceContact = Server.CreateObject("ADODB.Recordset")
rsGetInvoiceContact.ActiveConnection = MM_conn_to_EFACs_STRING_ap
rsGetInvoiceContact.Source = "SELECT * from "& ActinicPersons &" p WHERE p.[Contact ID] = " + Replace(rsGetInvoiceContact__vInvoiceID, "'", "''") + ""
rsGetInvoiceContact.CursorType = 0
rsGetInvoiceContact.CursorLocation = 2
rsGetInvoiceContact.LockType = 1
rsGetInvoiceContact.Open()

rsGetInvoiceContact_numRows = 0
%>

With the below connection string:

Dim MM_conn_to_EFACs_STRING_ap
MM_conn_to_EFACs_STRING_ap = "dsn=DSNefacsdb2;uid=IUSR_RFS01;pwd=Splendid01;"

The error show after running the script:

[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB provider 'MSDASQL' reported an error. The provider did not give any information about the error.

/Sales/order_entry.asp, line 55

When running the script against the local database it works as I want it, I have adjusted all the permissions on the other database for the user IUSR_RFS01, but still no joy!

[yamaharuss]

Is there a reason you don't want to use a DSN-less connection?

[Adders]

No not at all, I just want a connection that will connect up to the database, using DSN or not just doesn't seem to work

[yamaharuss]

Set Conn=Server.CreateObject("ADODB.Connection")
Conn.Open "PROVIDER=SQLOLEDB;" & _
"DATA SOURCE=DATABASEIPADDRESS;DATABASE=DATABASENAME;" & _
"USER ID=DATABASEUSERNAME;PASSWORD=DATABASEPASSWORD"