Return records from multiple tables and databases

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

Transact-SQL (2000)

Return records from multiple tables and databases

Author

Topic

darthasshat
Starting Member

17 Posts

Posted - 2005-03-14 : 02:17:39

Hey everyone, I'm trying to get my feet wet with stored procedures and I'm having a heck of a time. I'm trying to return a set of records from mulitple tables in multiple databases. Can this be done? I wrote the procedure and the syntax checks out but when I run it in query analyzer, it craps out. I get the following error:

Server: Msg 208, Level 16, State 1, Procedure SalesTest, Line 9
Invalid object name 'RSID'.

The RSID table does indeed exist in the EMP database. Below is the proc I'm wokring on.


CREATE PROCEDURE SalesTest

@startDate SMALLDATETIME,
@endDate SMALLDATETIME,
@salesId INT

 AS

SELECT DISTINCT LNAME & ", " &  FNAME AS AGENT, SLM, ACCT, UDATE, prodGrpName
FROM ICOMS.DBO.COMPLETE, ICOMS.DBO.PRODGRP, EMP.DBO.RSID, EMP.DBO.MAI
INNER JOIN prodGrp ON COMPLETE.fkProductUID = prodGrp.fkProductUID
INNER JOIN COMPLETE ON RSID.VALUE = COMPLETE.SLM
INNER JOIN RSID ON RSID.FKEMPUID = MAI.PKEMPUID
AND UDATE >= @StartDate
AND UDATE <= @EndDate
AND SLM = @salesId
ORDER BY LNAME & ", " & FNAME, SLM, ACCT, UDATE, prodGrpName
GO

Here is my original query that works in Access,


PARAMETERS [KMA] Short, [START DATE] DateTime, [END DATE] DateTime;
SELECT mai.lname & ", " & mai.fname AS EMPLOYEE, COMPLETE.ACCT, COMPLETE.UDATE, prodGrp.prodGrpName
FROM (prodGrp INNER JOIN COMPLETE ON prodGrp.fkProductUID = COMPLETE.FKPRODUCTUID) INNER JOIN ((rsid INNER JOIN mai ON rsid.FKEMPUID = mai.PKEMPUID) INNER JOIN (ldp INNER JOIN pkDept ON ldp.FKDEPTUID = pkDept.PKDEPTUID) ON mai.PKEMPUID = ldp.FKEMPUID) ON (COMPLETE.SITE = rsid.SITE) AND (COMPLETE.SLM = rsid.VALUE)
WHERE (((COMPLETE.UDATE) Between [start date] And [end date]) AND ((prodGrp.fkProdGrpUID) In (13,15,28,36,47,48,70,52,71,73)) AND ((ldp.FKDEPTUID)=21) AND ((ldp.STARTDATE)<=Date()) AND ((ldp.ENDDATE)>=Date()))
ORDER BY mai.lname & ", " & mai.fname, prodGrp.prodGrpName;

If anyone can help me out, I would greatly appreciate it. Thanks!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-14 : 05:38:08

you'll have to put aliases with columns.


SELECT DISTINCT 
	LNAME & ", " &  FNAME AS AGENT, SLM, ACCT, UDATE, prodGrpName
FROM 	ICOMS.DBO.COMPLETE C  
	join ICOMS.DBO.PRODGRP P on C.fkProductUID = P.fkProductUID
	join EMP.DBO.RSID R on R.VALUE = C.SLM
	join EMP.DBO.MAI M on R.FKEMPUID = M.PKEMPUID
where 	UDATE >= @StartDate
	AND UDATE <= @EndDate
	AND SLM = @salesId
ORDER BY LNAME & ", " & FNAME, SLM, ACCT, UDATE, prodGrpName

Go with the flow & have fun! Else fight the flow

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-14 : 07:33:32

A side note...
LNAME + ', ' + FNAME

You have quite a few tables in the from clause...
I think something like this will fit the bill

FROM ICOMS.DBO.PRODGRP AS prodgrp
INNER JOIN ICOMS.DBO.COMPLETE AS complete ON prodgrp.fkProductUID = complete.fkProductUID
INNER JOIN EMP.DBO.RSID AS rsid ON complete.SLM = rsid.VALUE
INNER JOIN EMP.DBO.MAI AS mai ON rsid.FKEMPUID = mai.PKEMPUID

rockmoose

darthasshat
Starting Member

17 Posts

Posted - 2005-03-16 : 03:02:44

It worked beautifully. Thanks for the help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources