Connecting Two Database to perform a search query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Connecting Two Database to perform a search query

Author

Topic

Yonkouturko
Yak Posting Veteran

59 Posts

Posted - 2013-04-22 : 02:26:40

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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-22 : 02:32:25

--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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-22 : 04:36:57

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

Yonkouturko
Yak Posting Veteran

59 Posts

Posted - 2013-04-22 : 08:44:21

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-22 : 08:57:30

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-22 : 09:13:17

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-22 : 09:20:10

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.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-22 : 09:21:15

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-22 : 11:56:50

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources