Two databases - same instance - query both

Question

Two databases - same instance - query both

Bron Tamulis

Ten Centuries

Points: 1090
More actions
November 22, 2011 at 11:02 am

#390926

I have 2 databases.......NBXWEB and RBI......I wrote this select statement....which is my first attempt to write across 2 databases.
select
NBXWEB.dbo.RBI_POReceipts.PONUM,
NBXWEB.dbo.RBI_POReceipts.Vendor,
NBXWEB.dbo.RBI_POReceipts.RecDate,
NBXWEB.dbo.RBI_POReceipts.SKU,
NBXWEB.dbo.RBI_POReceipts.QtyReceived,
NBXWEB.dbo.RBI_POReceipts.ReadByGP,
RBI.dbo.BT_POP_QTY_REMAIN.REMAIN
from NBXWEB.dbo.RBI_PORECEIPTS FirstAlias
Left outer Join RBI.dbo.BT_POP_QTY_REMAIN SecondAlias
ON FirstAlias.PONUM = SecondAlias.PONUMBER and FirstAlias.SKU = SecondAlias.ITEMNMBR
When I run it - every data element returns a 4104 message about unable to be bound.......
Question - the RBI.dbo.BT_POP_QTY_REMAIN is a SQL view not a physical table - does this make a difference?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

What is the exact error message?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Bron Tamulis Ten Centuries Points: 1090 More actions · Answer 2

Actually - I took out the alias reference and this query works:

select

NBXWEB.dbo.RBI_POReceipts.PONUM,

NBXWEB.dbo.RBI_POReceipts.Vendor,

NBXWEB.dbo.RBI_POReceipts.RecDate,

NBXWEB.dbo.RBI_POReceipts.SKU,

NBXWEB.dbo.RBI_POReceipts.QtyReceived,

NBXWEB.dbo.RBI_POReceipts.ReadByGP,

RBI.dbo.BT_POP_QTY_REMAIN.REMAIN

from NBXWEB.dbo.RBI_POReceipts

Left outer Join RBI.dbo.BT_POP_QTY_REMAIN

ON NBXWEB.dbo.RBI_POReceipts.PONUM = RBI.dbo.BT_POP_QTY_REMAIN.PONUMBER

and NBXWEB.dbo.RBI_POReceipts.SKU = RBI.dbo.BT_POP_QTY_REMAIN.ITEMNMBR

I'm kind of stymied that the alias reference didn't work......any thoughts?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

She asked for the EXACT error message for a reason ;-).

drew.allen SSC Guru Points: 76997 More actions · Answer 4

Bron Tamulis (11/22/2011)
select
NBXWEB.dbo.RBI_POReceipts.PONUM,
NBXWEB.dbo.RBI_POReceipts.Vendor,
NBXWEB.dbo.RBI_POReceipts.RecDate,
NBXWEB.dbo.RBI_POReceipts.SKU,
NBXWEB.dbo.RBI_POReceipts.QtyReceived,
NBXWEB.dbo.RBI_POReceipts.ReadByGP,
RBI.dbo.BT_POP_QTY_REMAIN.REMAIN
from NBXWEB.dbo.RBI_PORECEIPTS FirstAlias
Left outer Join RBI.dbo.BT_POP_QTY_REMAIN SecondAlias
ON FirstAlias.PONUM = SecondAlias.PONUMBER and FirstAlias.SKU = SecondAlias.ITEMNMBR

Once you define an alias on the table any references to the table MUST USE THE ALIAS. You are referring to the actual table name rather than the alias.

select

FirstAlias.PONUM, --replace table reference with alias NBXWEB.dbo.RBI_POReceipts.PONUM,

FirstAlias.Vendor, --replace table reference with alias NBXWEB.dbo.RBI_POReceipts.Vendor,

FirstAlias.RecDate, --replace table reference with alias NBXWEB.dbo.RBI_POReceipts.RecDate,

FirstAlias.SKU, --replace table reference with alias NBXWEB.dbo.RBI_POReceipts.SKU,

FirstAlias.QtyReceived, --replace table reference with alias NBXWEB.dbo.RBI_POReceipts.QtyReceived,

FirstAlias.ReadByGP, --replace table reference with alias NBXWEB.dbo.RBI_POReceipts.ReadByGP,

SecondAlias.REMAIN, --replace table reference with alias RBI.dbo.BT_POP_QTY_REMAIN.REMAIN

from NBXWEB.dbo.RBI_PORECEIPTS FirstAlias

Left outer Join RBI.dbo.BT_POP_QTY_REMAIN SecondAlias

ON FirstAlias.PONUM = SecondAlias.PONUMBER and FirstAlias.SKU = SecondAlias.ITEMNMBR

Drew

Edit to clarify meaning.

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Bron Tamulis Ten Centuries Points: 1090 More actions · Answer 5

Okay - that makes sense. learning point - either use alias all the time or not at all.

The alias sure saves typing.

Is alias equivalent to synonym (which I have seen referenced in my search for a solution before I posted)?

I love SQL - however, it's tough to learn everything if you don't work with it everyday.

This forum is great - folks really do want to knowledge share with folks like myself.

Thank you very much for your contributions.

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 6

Alias' and Synonyms are similar, but not the same.

An alias only lives as long as your query. Synonyms live as long as the database lives OR they are dropped.

There are more differences, but judging by your post, this answers your question. I suggest reading BOL on Synonyms as there is much more there than I feel like typing. 😀

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg