Mutli part identifier error - help

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

Analysis Server and Reporting Services (2005)

Mutli part identifier error - help

Author

Topic

jbphoenix
Yak Posting Veteran

68 Posts

Posted - 2009-09-22 : 11:53:46

Below is the query I have for my dataset. However when I run the code I get The multi-part identifier MISGTA.GTA.dbo.trefOutcomeCodes.OutcomeCode could not be bound. I'm not sure what the problem is.


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

I created a test dataset with this query

 select * from misgta.gta.dbo.trefOutcomeCodes

and it works fine.

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-09-22 : 12:08:21

try a test SQL query of

Select OutcomeCode from misgta.gta.dbo.trefOutcomeCodes

Is still working fine?

John

"The smoke monster is just the Others doing barbecue"

jbphoenix
Yak Posting Veteran

68 Posts

Posted - 2009-09-22 : 12:12:31

I just tried that and it works fine. I also tried
select outcomeDescription from misgta.gta.dbo.trefOutcomeCodes
and that also works.

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-09-22 : 12:18:19

I get that when I leave an extra comma or something else very small. Wish I saw something else. Even though in Buffalo, have my AZ Cards jersey on today :)

John

"The smoke monster is just the Others doing barbecue"

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-09-22 : 12:21:50

I know this will sound wierd but are you creating the query in the reporting services BI development studio? I have had queries that worked in SQL Server Management Studio but did not work in the BI Dev Studio Report Wizard.

John

"The smoke monster is just the Others doing barbecue"

jbphoenix
Yak Posting Veteran

68 Posts

Posted - 2009-09-22 : 12:35:35

Yes, I know exactly what you are talking about. That's why I'm stuck. The query works in SQL Management Studio but does not work in BI Dev Studio.

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-09-22 : 12:45:45

OK - her is how I got around it. If you like to use the Wizard, this is more difficult. Build your code as best you can in the Wizard. Click OK and take the options to leave the wizard. Click the data tab and paste your original query THERE and it works exactly like in Server Studio. Wierdest thing I ever saw.

John

"The smoke monster is just the Others doing barbecue"

jbphoenix
Yak Posting Veteran

68 Posts

Posted - 2009-09-22 : 13:45:34

I'd try that but since my tables are in different databases I don't know how. I wonder if that is my problem to begin with. I tried querying both tables seperately and it works fine. But when I join them, I get the multi part identifier error (error 4104). Could it be a naming issue?

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-09-22 : 13:55:30

Sorry, never did the "use two different databases" thing yet.

You may want to post over in the T-SQL forum at http://www.sqlteam.com/forums/forum.asp?FORUM_ID=30 since this may not really be a Reporting Services issue.

John

"The smoke monster is just the Others doing barbecue"

jbphoenix
Yak Posting Veteran

68 Posts

Posted - 2009-09-22 : 13:59:29

Thanks for your help John.

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-09-22 : 14:03:20

Your very welcome. Hope you are also a Cards fan!

John

"The smoke monster is just the Others doing barbecue"

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-09-22 : 14:43:31

Crazy thought... What about creating a stored procedure that returns to reporting services the data you ned without the requirement of multiple datasources? Just a thought.

John

"The smoke monster is just the Others doing barbecue"

jbphoenix
Yak Posting Veteran

68 Posts

Posted - 2009-09-22 : 16:18:13

I gave that a try but to my dismay it didn't work. I can't get the tables to join correctly. Do you know if it's possible to use temp tables in SSRS?

jbphoenix
Yak Posting Veteran

68 Posts

Posted - 2009-09-22 : 16:56:06

I figure it out. I needed to alias the tables. Now it works.

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-09-23 : 08:13:14

Can you post what the resulting code was for future "just in case"?

Also, temp tables appear to give an error when developing in BI studio but when deployed appear to work. So as not to have problems in future, both of my queries that require temp tables I placed into stored procedures.

Glad you got it working.

John

"The smoke monster is just the Others doing barbecue"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources