September 22, 2009 at 1:37 pm
I'm trying to use the following query in SSRS. I was testing it out in Mgmt Studio and I keep getting the multi-part identifier "MISGTA.GTA.dbo.trefOutcomeCodes.OutcomeCode" could not be bound. Here is the query -
select left(sccmdt,6) as 'Date', scoutc, sum(cnt) as 'Count', OutcomeDescription,
sum(case
when scoutc in ('BZ', 'CC', 'DP', 'DS', 'HP', 'LM', 'NA', 'NM', 'PH', 'PP', 'PS', 'RP', 'SK', 'TC', 'UC') then cnt
else 0
end) as 'Attempt',
sum(case
when scoutc in ('CC', 'DP', 'HP', 'PH', 'PP', 'PS', 'RP', 'TC', 'UC') then cnt
else 0
end) as 'Contact',
sum(case
when scoutc in ('CC', 'DP', 'HP', 'PH', 'PP', 'PS', 'RP', 'UC') then cnt
else 0
end) as 'RPC'
from MISProductivity.[GTFC\JOHNSOR].tbl_BEOUT_OutcomeCodesByPool join
MISGTA.GTA.dbo.trefOutcomeCodes
on MISProductivity.[GTFC\JOHNSOR].tbl_BEOUT_OutcomeCodesByPool.SCOUTC = MISGTA.GTA.dbo.trefOutcomeCodes.OutcomeCode
where scoutc in (@Code) and left(sccmdt,6) in (@Mnth)
group by left(sccmdt,6) , scoutc, OutcomeDescription
Is it because I'm joining tables from 2 different db's?
September 22, 2009 at 1:49 pm
Sounds like incorrect linked server setup. The 4 part naming in server.database.schema.object and I'm guessing it is looking for a linked server that either is not setup or is not setup correctly. If the 2 databases are on the same server remove the server name.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 22, 2009 at 2:16 pm
I think you are correct about the 4 part name. I tried this
select * from MISGTA.GTA.dbo.trefOutcomeCodes
and it works.
Can I use a temp table in SSRS?
If so then I can create a stored procedure that makes a temp table and then join to that table
September 22, 2009 at 2:39 pm
I don't think you need a temp table. You just need to take out any reference to the server name from the query.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 22, 2009 at 2:46 pm
I don't think the problem is the linked server, but the fact that you are not aliasing the tables. You can no longer use the full table reference for column references.
Not only should you be using aliases for your tables, but you should be using that alias for each column so you know what table the values are actually coming from.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 22, 2009 at 2:55 pm
Thanks Jeff, that was the issue. I aliased the tables and it works perfectly.
September 22, 2009 at 2:58 pm
That is good to know - glad I could help.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 22, 2009 at 3:25 pm
Jeffrey Williams-493691 (9/22/2009)
I don't think the problem is the linked server, but the fact that you are not aliasing the tables. You can no longer use the full table reference for column references.Not only should you be using aliases for your tables, but you should be using that alias for each column so you know what table the values are actually coming from.
While I believe you're absolutely correct on aliasing being his issue, were you referring to the inability to use 3 or 4 part name in the SELECT in SQL2008? I don't believe that particular causing his issue here, the query above fails even on SQL2000, albeit with a different message. I get the same error message below in every server version I've tried it on.
Server: Msg 117, Level 15, State 2, Line 17
The number name 'MISGTA.GTA.dbo.trefOutcomeCodes' contains more than the maximum number of prefixes. The maximum is 3.
I usually see the multi-part identifier message when I've mistyped a table name in one place and not the other, or used an alias wrong, etc. Sorry if I mistook you Jeff. Either way, seems the issue is all fixed.
September 22, 2009 at 5:10 pm
Seth,
Yes, that was part of what I was thinking - but, the original query did not have anything in the select and was trying to reference columns using the fully qualified name (4-part) which is not allowed.
Basically, you cannot reference a column using 4-part naming because it ends up as:
server.database.schema.object.column - which is more than 4-parts 😀
The error in this case was actually in the ON clause, and the only ways to avoid the issues are to either alias the tables or have unique columns in each table that can be referenced without using any aliases.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply