CODEWITHSUNDEEP
sql-serverlinqpad
B. Clay Shannon-B. Crow Raven
B. Clay Shannon-B. Crow Raven

Reputation: 10237

How can I relate two tables that are in different databases (SQL Server)?

I need to grab a value from a table in another database; if the tables were in the same database, it would be pretty easy, but since they are not, it complicates matters. I've tried this:

SELECT TOP 8 prosql05.sa.cpsdata.M.membername, 
             P.* 
FROM   prosql05.sa.bidata.priceexceptionhistorycsr P 
       JOIN prosql05.sa.cpsdata.members M 
         ON P.memberno = M.memberno 
WHERE  invoicedate BETWEEN '2016-09-04' AND '2016-09-10' 
ORDER  BY invoicedate

...but get the err msg (in LINQPad),

Error 208: Invalid object name 'sa.BIData.PriceExceptionHistoryCSR'.

I'm pretty sure this is possible (since they are both on PROSQL05), but how?

The Connection selected in LINQPad is "PROSQL05.sa.BIData"

UPDATE

I've changed my attempt to this:

select top 8 M.MemberName, P.* 
from PriceExceptionHistoryCSR P, PROSQL05.sa.CPSData M
where InvoiceDate between '2016-09-04' and '2016-09-10'
and M.MemberNo = P.MemberNo
order by invoicedate

...and now get, "Error 208: Invalid object name 'PROSQL05.sa.CPSData'."

That makes more sense to me, but I still need to know how to make the table from the other database (Schema, IOW CPSData) accessible from the BIData schema/database.

UPDATE 2

According to LINQPad's Properties dialog Server = PROSQL05, User name = sa, and Database = BIData.

So how does that knowledge affect how the SQL should be structured?

Upvotes: 3

Views: 275

Answers (4)

Ajay Dwivedi
Ajay Dwivedi

Reputation: 328

To make your code clean, use synonyms for Objects of another db.

For example, if you create a synonym sa.priceexceptionhistorycsr for target object prosql05.bidata.sa.priceexceptionhistorycsr, you can refer the target with synonym only.

Upvotes: 0

Jamiec
Jamiec

Reputation: 136094

When addressing a database you always have to use this form

<servername>.<databasename>.<schemaname>.<tablename>

If your connection is on the same server, you can omit the first part, leaving you with

<databasename>.<schemaname>.<tablename>

If your connection is to the same database you can omit the first part again

<schemaname>.<tablename>

But lets back up, you're trying to access another database on the same server. Which means that one of the databases you can omit and the other you must specify. I'm going to assume all your tables are in the schema dbo which is probably the case.

You should be querying like this if your connection is directly to bidata

SELECT TOP 8 M.membername, 
             P.* 
FROM   dbo.priceexceptionhistorycsr P 
       JOIN cpsdata.dbo.members M 
         ON P.memberno = M.memberno 
WHERE  invoicedate BETWEEN '2016-09-04' AND '2016-09-10' 
ORDER  BY invoicedate

Upvotes: 6

swe
swe

Reputation: 1455

googeling your errormessage shows, that this is not a linqpad message, but an sql-server-error. So, there seems to be an error in your query. I think, there is one of

  • a typo
  • an acl-problem

Are you sure your user is allowed to access both databases/schemata?

Assuming your "connection" is not what it seems to be (and obviously, according to your last comment to the question, IS not), try that:

SELECT TOP 8 M.membername, 
             P.* 
FROM   priceexceptionhistorycsr P 
       JOIN prosql05.cpsdata.dbo.members M 
         ON P.memberno = M.memberno 
WHERE  invoicedate BETWEEN '2016-09-04' AND '2016-09-10' 
ORDER  BY invoicedate

Upvotes: 1

vamsi
vamsi

Reputation: 352

SELECT TOP 8 prosql05.cpsdata.sa.M.membername, 
             P.* 
FROM   prosql05.bidata.sa.priceexceptionhistorycsr P 
       JOIN prosql05.cpsdata.sa.members M 
         ON P.memberno = M.memberno 
WHERE  invoicedate BETWEEN '2016-09-04' AND '2016-09-10' 
ORDER  BY invoicedate

Can you try this.Looks like your schema name is sa and you are putting your schema name before your database name in the original query

Upvotes: 0

Related Questions