0

I would like to know what is the best way for use linkserver.

I have a database in SQL Server called Sales and another database called Menus. Both are on the same instance of JOSEPH-PC.

My question: is it necessary to use linked server for it?

Or can I simply write a query like this:

select * 
from Sales.dbo.table1 a 
inner join Menus.dbo.accces b on a.code = b.code

How could I store data in 2 tables from different databases for example a master-detail from my app in C#?

Please let me know how I can make this.

freedomn-m
  • 27,664
  • 8
  • 35
  • 57
Joseph Marchena
  • 39
  • 6
  • you can't created link server having same instance, try using view. – Ragul Nov 17 '15 at 07:00
  • @Ragul Actually you can :) – Arvo Nov 17 '15 at 07:25
  • To author: best is to avoid dealing with two databases in single application - you can't have proper relations, you'll have user access problems, you'll have support problems, you need some nasty synchronization in case of backup restoration after failure etc etc. You have tables, you have schemas - enough granularity. – Arvo Nov 17 '15 at 07:28
  • Yes you can do this without a linked server in the way you describe, but I don't think it is a good idea - think of your query performance – jazza1000 Nov 17 '15 at 10:03

2 Answers2

1

i dont think you can create the linked server for same sql server instance. that has to be different instance or different server.

if its about the best way to access the different database in same server same instance?? the synonyms is here for that purpose.

create the synonyms for access from Menus in sales then you can use the synonym like local table on sales database.

in sales database run the following command to create the synonyms for access so now in sales you can use menus_access as table, which will refer the access inside the menus database

CREATE SYNONYM menus_access 
FOR Menus.dbo.access;

now in sales you can access the table as

 select * from menus_access
Deepak Sharma
  • 4,124
  • 1
  • 14
  • 31
0

You will need to create view on one of the two databases and make sure your connection string points to that database. You can create a view within SQL Server that connects to both databases like Create view across multiple databases

You might also like to view how to retrieve data from two different database using join in SQL Server 2005 the way you have tried to do it.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mohit S
  • 13,723
  • 6
  • 34
  • 69