January 27, 2011 at 6:16 am
Hi,
I have a requirement to write some sp's which will retrieved rows from two different server. I do not want to use Link server or open query. Is there any other way? My plan is to create two different sp in different server and then access both in data in front end and from front end i will write the logic. I think it will be complicated. Please suggest..
Regards,
Ashish
January 27, 2011 at 7:01 am
Guess, that the best option if you dont want to use linked server or open query. All you need to do is get the data from both the servers in to 2 different datasets and then you can join using a loop.
January 27, 2011 at 7:02 am
without linked servers it would be difficult, is there a reason you don't want to use linked servers?
January 27, 2011 at 11:24 am
Hi Steve,
We are now migrating some part of one vb 6 application to .net. The database for vb 6 is sql 2000 and it will be in one server. In .net the database will be in sql 2008. Now we have created some tables in sql server 2008 which were avilable in sql 2000. We need to call same sp's of the vb application from .net. now because of some table has been changed from sql server 2000 to 2008 i am getting problem. Please suggest. Let me know if you clearly understood the requirement.
January 27, 2011 at 11:30 am
Hi Pravasis,
thanks for reply. can u tell me how to do that in two dataset. Because there are loop, temp tables, insert, update, delte in some sp's. Is it possible to do that in front end level with two datasets.
January 28, 2011 at 7:38 am
siluctc (1/27/2011)
Hi Pravasis,thanks for reply. can u tell me how to do that in two dataset. Because there are loop, temp tables, insert, update, delte in some sp's. Is it possible to do that in front end level with two datasets.
What ever we can do in database, can also be done in .net and finally we can put the resultset into the required database. The constraint is, in database it can be done very efficiently and with less amount of code. if you are talking about joins, we can write two while loops to get the desired value. We can create a new dataset to replace a temp table. Similarly we can tryout different things to achieve the desired result. But the best option is to get it done where is can be done effciently and with very less amount of code.
February 1, 2011 at 3:41 am
You could use LINQ to fetch the records from both the server, then write the similar queries as in sql, LINQ queries to join or modify the data as per your requirement.
February 1, 2011 at 4:06 am
@lalit sir.. the plan is now to use linq or clr store procedure. Can u suggest which one is better and why.
February 2, 2011 at 6:41 am
siluctc (2/1/2011)
@lalit sir.. the plan is now to use linq or clr store procedure. Can u suggest which one is better and why.
DO NOT USE LINQ TO SQL, and avoid linked servers and remote joins. They are HORRID for performance. if you need data from a different sql server I think you had it right to begin with - execute local sprocs to process the data locally and return to other sql server.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 3, 2011 at 3:24 pm
TheSQLGuru (2/2/2011)
siluctc (2/1/2011)
@lalit sir.. the plan is now to use linq or clr store procedure. Can u suggest which one is better and why.DO NOT USE LINQ TO SQL, and avoid linked servers and remote joins. They are HORRID for performance. if you need data from a different sql server I think you had it right to begin with - execute local sprocs to process the data locally and return to other sql server.
I have seen this sort of join used with Crystal Reports at several sites. One of them ran for several hours. (In Crystal you can join 2 tables on different servers.)
Handling this was fairly simple. I wrote a stored procedure on Server1 that would return just the records I wanted for the over all report. Then I wrote the actual report procedure on Server 2. It went something like this:
-- Temp table to match record set returned from Server1
CREATE TABLE #Server1Data
( Column1, Column2.....)
INSERT INTO #Server1Data
EXEC Server1.DatabaseName.dbo.Server1Proc
-- The actual report procedure went here
-- Use the temp table to join to Server2 tables.
The execution time went from several hours to around 10 seconds.
Todd Fifield
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply