SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Connecting Two Database to perform a search query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Yonkouturko
Yak Posting Veteran

Philippines
59 Posts

Posted - 04/22/2013 :  02:26:40  Show Profile  Reply with Quote
i have DATABASE1 with ProductTaggtable
with items..
SupplierID : Category : Description : Brand

and then DATABASE2 with SupplierTable
with Items..
SupplierID : SupplierName : Contact Person

how will i Show

SupplierID : SupplierName : Contact Person from DATABASE2:SupplierTable

While Using DATABASE1 ProductTaggTable as SEARCH REFERENCES:
Category or Description Or Brand

Thank you for your Help!!! :) it will bring peace to my MIND!!!! :)

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 04/22/2013 :  02:32:25  Show Profile  Reply with Quote
--this?
DECLARE @category varchar(10), @description varchar(10), @Brand varchar(10) ,
SELECT distinct s.*
FROM DATABASE2..SupplierTable s
JOIN DATABASE1..ProductTaggtable p ON s.SupplierID = p.SupplierID
WHERE Category = @category
or Description = @description
Or Brand = @brand

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/22/2013 :  04:36:57  Show Profile  Reply with Quote
depends on whether databases are on same server or not.
If yes, you can use what Bandi suggested

if not, you need to first create a linked server between them

see

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=164892

then use query like

DECLARE @category varchar(10), @description varchar(10), @Brand varchar(10) , 
SELECT distinct s.*
FROM DATABASE2..SupplierTable s
JOIN LINKEDSERVER.DATABASE1..ProductTaggtable p ON s.SupplierID = p.SupplierID
WHERE Category = @category
or Description = @description
Or Brand = @brand


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Yonkouturko
Yak Posting Veteran

Philippines
59 Posts

Posted - 04/22/2013 :  08:44:21  Show Profile  Reply with Quote
does it have to have double dots like this
"DATABASE1..ProductTaggtable"
yes the two databases is on the same server
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3760 Posts

Posted - 04/22/2013 :  08:57:30  Show Profile  Reply with Quote
You don't necessarily have to have the double-quotes, unless you are using some special characters or reserved keywords. But there is no harm in using them either. But the sytnax is to double-quote each piece of the name as in "DATABASE1"."dbo"."ProductTaggtable". You can also use square brackets [DATABASE1].[dbo].[ProductTaggtable], which is what you will see more often.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/22/2013 :  09:13:17  Show Profile  Reply with Quote
quote:
Originally posted by Yonkouturko

does it have to have double dots like this
"DATABASE1..ProductTaggtable"
yes the two databases is on the same server



yes
it needs to have double dots

as the order is
server.database.schema.table


here server being same you'll use db.schema.table

schema you'll leave as blank to assume default schema

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3760 Posts

Posted - 04/22/2013 :  09:20:10  Show Profile  Reply with Quote
Visakh, thanks! Must be that Monday morning thing, I read "double dots" as "double quotes"!!

Note to self: Refrain from driving or operating any heavy machinery until fully awake.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 04/22/2013 :  09:21:15  Show Profile  Reply with Quote
You can give
DATABASE1..ProductTaggtable
DATABASE1.dbo.ProductTaggtable -- dbo is default schema in SQL Server
DATABASE1.SchemaName.ProductTaggtable -- SchemaName is explicit schema name in which you have that table



--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/22/2013 :  11:56:50  Show Profile  Reply with Quote
quote:
Originally posted by bandi

You can give
DATABASE1..ProductTaggtable
DATABASE1.dbo.ProductTaggtable -- dbo is default schema in SQL Server
DATABASE1.SchemaName.ProductTaggtable -- SchemaName is explicit schema name in which you have that table



--
Chandu


and they always dont mean the same based on what the logged users default schema is

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.47 seconds. Powered By: Snitz Forums 2000