view to select from different database

Question

view to select from different database

Nomvula

Hall of Fame

Points: 3778
More actions
February 17, 2009 at 4:26 am

#155283

hi experts,
i have two databases one is live the other is test db, on the test db i've created a view to select from the live db but there's something wrong with my select query, i'm getting an error "The multi-part identifier could not be bound." in the selection fields.
here it is:
SELECT astAssets.ID AS AssetID,
vw_astDepreciationTransactions.FinPeriodID AS PeriodID,
finPeriods.Code AS PeriodCode,
vw_astDepreciationLinks.StartDate AS StartDate,
finCostCentres.ID AS CostCentreID,
astTypes.SeqNum AS AssetFinancialClassSeqNum
FROM [ON_KEY_42R5_UDM2]..astAssets INNER JOIN [ON_KEY_42R5_UDM2]..vw_astDepreciationLinks ON [ON_KEY_42R5_UDM2]..astAssets.ID = [ON_KEY_42R5_UDM2]..vw_astDepreciationLinks.RecordID
INNER JOIN [ON_KEY_42R5_UDM2]..vw_astDepreciationTransactionsON [ON_KEY_42R5_UDM2]..vw_astDepreciationLinks.ID= [ON_KEY_42R5_UDM2]..vw_astDepreciationTransactions.DepreciationLinksID
INNER JOIN [ON_KEY_42R5_UDM2]..finPeriodsON [ON_KEY_42R5_UDM2]..finPeriods.ID = [ON_KEY_42R5_UDM2]..vw_astDepreciationTransactions.FinPeriodID
INNER JOIN [ON_KEY_42R5_UDM2]..finGLCodes ON [ON_KEY_42R5_UDM2]..finGLCodes.ID= [ON_KEY_42R5_UDM2]..vw_astDepreciationTransactions.GLID
INNER JOIN [ON_KEY_42R5_UDM2]..finCostCentresON [ON_KEY_42R5_UDM2]..finCostCentres.ID = [ON_KEY_42R5_UDM2]..vw_astDepreciationTransactions.CostCentreID
INNER JOIN [ON_KEY_42R5_UDM2]..astTypesON [ON_KEY_42R5_UDM2]..astTypes.ID = [ON_KEY_42R5_UDM2]..[dbo.fn_astTypeGetSecondHighestAssetTypeBelowRoot(astAssets.AssetTypeID)]

Viewing 5 posts - 1 through 4 (of 4 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

unless you add an alias to the the tables from the other db, you have to reference them with the whole 4 part identifier in the select:

[ON_KEY_42R5_UDM2]..astAssets.ID,

not just

astAssets.ID

much easier to just alias the joined tables with their plain names:

SELECT

astAssets.ID AS AssetID,

vw_astDepreciationTransactions.FinPeriodID AS PeriodID,

finPeriods.Code AS PeriodCode,

vw_astDepreciationLinks.StartDate AS StartDate,

finCostCentres.ID AS CostCentreID,

astTypes.SeqNum AS AssetFinancialClassSeqNum

FROM [ON_KEY_42R5_UDM2]..astAssets AS astAssets

INNER JOIN [ON_KEY_42R5_UDM2]..vw_astDepreciationLinks AS vw_astDepreciationLinks

ON [ON_KEY_42R5_UDM2]..astAssets.ID = [ON_KEY_42R5_UDM2]..vw_astDepreciationLinks.RecordID

INNER JOIN [ON_KEY_42R5_UDM2]..vw_astDepreciationTransactions AS vw_astDepreciationTransactions

ON [ON_KEY_42R5_UDM2]..vw_astDepreciationLinks.ID = [ON_KEY_42R5_UDM2]..vw_astDepreciationTransactions.DepreciationLinksID

INNER JOIN [ON_KEY_42R5_UDM2]..finPeriods AS finPeriods

ON [ON_KEY_42R5_UDM2]..finPeriods.ID = [ON_KEY_42R5_UDM2]..vw_astDepreciationTransactions.FinPeriodID

INNER JOIN [ON_KEY_42R5_UDM2]..finGLCodes AS finGLCodes

ON [ON_KEY_42R5_UDM2]..finGLCodes.ID = [ON_KEY_42R5_UDM2]..vw_astDepreciationTransactions.GLID

INNER JOIN [ON_KEY_42R5_UDM2]..finCostCentres AS finCostCentres

ON [ON_KEY_42R5_UDM2]..finCostCentres.ID = [ON_KEY_42R5_UDM2]..vw_astDepreciationTransactions.CostCentreID

INNER JOIN [ON_KEY_42R5_UDM2]..astTypes AS astTypes

ON [ON_KEY_42R5_UDM2]..astTypes.ID = [ON_KEY_42R5_UDM2]..[dbo.fn_astTypeGetSecondHighestAssetTypeBelowRoot(astAssets.AssetTypeID)]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Nomvula Hall of Fame Points: 3778 More actions · Answer 2

hi Lowell

thanks for the prompt response but i tried that and still getting the same error: "The multi-part identifier could not be bound."

the two databases are in the same server, maybe that will give a clue

Nomvula Hall of Fame Points: 3778 More actions · Answer 3

i used "[ON_KEY_42R5_UDM2]..astAssets.ID" etc. in the select then i removed the last inner join cause i was getting an error " The multi-part identifier "ON_KEY_42R5_UDM2..dbo.fn_astTypeGetSecondHighestAssetTypeBelowRoot(astAssets.AssetTypeID)" could not be bound."

this "dbo.fn_astTypeGetSecondHighestAssetTypeBelowRoot" is a function i'm joining and i think it's the way i used "ON_KEY_42R5_UDM2" in that line which gives an error

Ronald H SSCarpal Tunnel Points: 4053 More actions · Answer 4

I think [ON_KEY_42R5_UDM2]..[dbo.fn_astTypeGetSecondHighestAssetTypeBelowRoot(astAssets.AssetTypeID)]should be changed in [ON_KEY_42R5_UDM2].dbo.fn_astTypeGetSecondHighestAssetTypeBelowRoot(astAssets.AssetTypeID)or, alternatively

[ON_KEY_42R5_UDM2].[dbo].[fn_astTypeGetSecondHighestAssetTypeBelowRoot](astAssets.AssetTypeID)

Using [] defines an exact name. SQL Server uses it always when it scripts for you, because names aren't checked for spaces or any other non alphabet characters.

Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2