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
svl_kentAsked:
Who is Participating?
 
derekkrommCommented:
Here's the MSDN for sp_addlinked server for exact syntax/options. http://msdn2.microsoft.com/en-US/library/aa259589(SQL.80).aspx

It is only run one-time. Once you add a linked server, it will be there permanently until you remove it. So you can open up query analyzer on server A, run the sp_addlinked server, and then reference it from then on out.

Then on your ASP page you open a connection to server a and run the statement from my original comment:

SELECT dbo_SC010100.SC01060, dbo_SC010100.SC01042
FROM dbo_Product INNER JOIN [SERVER B].databasename..dbo_SC010100 ON ProductNumber = SC01060;
0
 
derekkrommCommented:
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;
0
 
svl_kentAuthor Commented:
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
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
svl_kentAuthor Commented:
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
0
 
svl_kentAuthor Commented:
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
0
 
svl_kentAuthor Commented:
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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.