Stored Procedure to pull data from two databases

[jbehrne]

Hi all,

I wrote a stored procedure that pulls data from three tables. Two of the tables are in one database and a third table is in another (database is called coa and the table name in the database is COA). These databases are on the same server running SQL Server 7.0. When I run the stored procedure in vb.net I get the following error:

Invalid Object Name 'COA'.

Can anyone help me with this? What am I doing wrong in this stored procedure? Thanks,

jbehrne

CREATE PROCEDURE ReturnPBCharges 
AS
SELECT Total, Upcharge, AccountCode, JobName, Pieces, PRs.Internat, coa.dbo.COA.Description
FROM coa.dbo.COA, WeeklyData LEFT JOIN PostageRates ON WeeklyData.JobName = PostageRates.ClassCode
INNER JOIN COA ON WeeklyDataImport.AccountCode = COA.AccountNumber

If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations

 

[gmmastros]

You have a WeeklyData Table but are also inner joining on a WeeklyImportData table. Is this a typo?

I suggest you use a table alias to simplify the query (it at least makes it easier to read).

Does this work for you?

SELECT Total, 
       Upcharge, 
       AccountCode, 
       JobName, 
       Pieces, 
       PostageRates.Internat, 
       COA.Description
FROM   coa.dbo.COA COA
       Inner Join WeeklyData On COA.AccountCode = WeeklyData.AccountCode
       LEFT JOIN PostageRates ON WeeklyData.JobName = PostageRates.ClassCode

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom