asked on

INNER JOIN from two servers with ASP?

Hello! I need to connect to two MS SQL servers from an ASP page and do a INNNER JOIN between them.
I would like to do all work in the ASP page not on the actual server.
If I do the question in Access I got the follwing SQL code:
SELECT dbo_SC010100.SC01060, dbo_SC010100.SC01042
FROM dbo_Product INNER JOIN dbo_SC010100 ON dbo_Product.ProductNumber = dbo_SC010100.SC01060;
dbo_Product.ProductNumber is on SERVER A and dbo_SC010100.SC01060 is on SERVER B.
Is that possible from an ASP page? Could anyone give me the syntax?
I have SA priviligies on SERVER A but only read on SERVER B.
TIA / Kent

derekkromm

No, its not possible. The easiest way to do this is to add a linked server on Server A to Server B.

sp_addlinkedserver 'SERVER B'

Then you can do the query as follows:

SELECT dbo_SC010100.SC01060, dbo_SC010100.SC01042
FROM dbo_Product INNER JOIN [SERVER B].databasename..dbo_SC010100 ON ProductNumber = SC01060;

svl_kent

ASKER

Oki - but how do I enter the user credentials for server B?
Do I use a normal connection string for SERVER A? Example:
set conn10 = server.createobject("adodb.connection")
conn10.open "akscrm","user","password
sp_addlinkedserver 'SERVER B'
sqlS "SELECT dbo_SC010100.SC01060, dbo_SC010100.SC01042
FROM dbo_Product INNER JOIN [SERVER B].databasename..dbo_SC010100 ON ProductNumber = SC01060;"
set ct5 = server.createobject("adodb.recordset")
ct5.Open sqlS, conn10

ASKER CERTIFIED SOLUTION

derekkromm

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

svl_kent

ASKER

Aha - NEWBIE :( - thought I should run the SP_addlinkedservr in the SP page.
Now I have run it on the SQL server with this string:
EXEC master.dbo.sp_addlinkedserver @server = N'MILLIWAYS', @srvproduct=N'''''', @provider=N'SQLNCLI', @datasrc=N'MILLIWAYS', @provstr=N'''DRIVER={SQL Server};SERVER=MILLIWAYS;UID=username;PWD=secretpassword;''', @catalog=N'ScalaDB'.
In my ASP page I run:
set conn10 = server.createobject("adodb.connection")
conn10.open "akscrm","sa","secretpassword"

sqlS "SELECT Product.ProductNumber, SC010100.SC01060 FROM Product INNER JOIN MILLIWAYS.ScalaDB..SC010100 ON Product.ProductNumber = SC010100.SC01060;"
set ct5 = server.createobject("adodb.recordset")
ct5.Open sqlS, conn10
When I run the page I got:
"Type mismatch" on the SELECT row??
Product.ProductNumber and SC010100.SC01060 is both text fields in the database!

Hmm - now I see - I got "Type mismatch" on the SELECT row whatever I write in it??
/Kent

svl_kent

ASKER

FORGET IT!
I must have entered something wrong - now I have got some more steps furthe but haverun in to an new problem - when I run my SELECT I got:
_________________________________
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot resolve the collation conflict between "SQL_SwedishPhone_Pref_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
_________________________________
These SQL servers is on one old MS SQL server and MS SQL 2005.

/Kent

svl_kent

ASKER

In my SQL 2005 server (where I have done the linked server) I change the COLLATION settings.
If I enter "Collation Compatible" to TRUE or FALSE and "Use Remote Collation" to FALSE I got:
Microsoft][ODBC SQL Server Driver][SQL Server]Cannot resolve the collation conflict between "Finnish_Swedish_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
If I enter "Collation Compatible" to TRUE or FALSE and "Use Remote Collation" to TRUE I got:
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot resolve the collation conflict between "SQL_SwedishPhone_Pref_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation

?? Any suggestion anyone?
/Kent