[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

INNER JOIN from two servers with ASP?

Posted on 2007-07-22
8
Medium Priority
?
1,307 Views
Last Modified: 2008-09-08
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
0
Comment
Question by:svl_kent
  • 4
  • 2
6 Comments
 
LVL 15

Expert Comment

by:derekkromm
ID: 19543121
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
 

Author Comment

by:svl_kent
ID: 19543310
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
 
LVL 15

Accepted Solution

by:
derekkromm earned 500 total points
ID: 19544287
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:svl_kent
ID: 19545667
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
 

Author Comment

by:svl_kent
ID: 19545724
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
 

Author Comment

by:svl_kent
ID: 19545797
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

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question