in vs. hard coding problem

Hi,
I'm having an urgent problem that I'm having a hard time figuring out.
I extracted the code below from a stored procedure that gets different
results on two servers (identical data). The statement in question is
"SELECT MANAGERID FROM #MANAGER". When I use this select in the "in"
clause (there's definitely data in the temp table), very few rows are
returned. when I hard code the values (ie: in
('CRM0092706','CRM0591496') ), it works as expected.
Any help is GREATLY appreciated! (It's a production prob :) )
---
SELECT
events.type, events.repid,
label = rtrim(events.status), value = 1
FROM
contact
left outer join events on events.repid = contact.contno
and rtrim(events.status) in ('Completed', 'Confirmed', 'Pending',
'Cancelled', 'Hold')
and events.jobid in ('CMH0194903')
WHERE
contact.manager in
(select dm.contno from contact dm where dm.manager in
(select rm.contno from contact rm where rm.manager in (SELECT
MANAGERID FROM #MANAGER)))
and contact.contno in
(select multprod.contno from multprod where multprod.territory is not
null
and multprod.prodindid in (SELECT * from
udf_splitstring('''CMH0196845''',',')))not sure how the udf works, but why have the multiple quotes in the udf
call?
why not just, e.g.
udf_splitstring('CMH0196845', ',')
bassunddrum@.gmail.com wrote:
> Hi,
> I'm having an urgent problem that I'm having a hard time figuring out.
> I extracted the code below from a stored procedure that gets different
> results on two servers (identical data). The statement in question is
> "SELECT MANAGERID FROM #MANAGER". When I use this select in the "in"
> clause (there's definitely data in the temp table), very few rows are
> returned. when I hard code the values (ie: in
> ('CRM0092706','CRM0591496') ), it works as expected.
> Any help is GREATLY appreciated! (It's a production prob :) )
> ---
> SELECT
> events.type, events.repid,
> label = rtrim(events.status), value = 1
> FROM
> contact
> left outer join events on events.repid = contact.contno
> and rtrim(events.status) in ('Completed', 'Confirmed', 'Pending',
> 'Cancelled', 'Hold')
> and events.jobid in ('CMH0194903')
> WHERE
> contact.manager in
> (select dm.contno from contact dm where dm.manager in
> (select rm.contno from contact rm where rm.manager in (SELECT
> MANAGERID FROM #MANAGER)))
> and contact.contno in
> (select multprod.contno from multprod where multprod.territory is not
> null
> and multprod.prodindid in (SELECT * from
> udf_splitstring('''CMH0196845''',',')))
>|||"bassunddrum@.gmail.com" wrote:
> Hi,
> I'm having an urgent problem that I'm having a hard time figuring out.
> I extracted the code below from a stored procedure that gets different
> results on two servers (identical data).
Can you be sure that #MANAGERS, being a temporary table, is the same on both
servers?
Are contact.manager and #MANAGERS.MANAGERID the same datatype?|||same data, different servers.. i'd check the tempdb's collations:
http://msdn2.microsoft.com/ms190920.aspx
dean
"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:u5MtbFpJGHA.1760@.TK2MSFTNGP10.phx.gbl...
> not sure how the udf works, but why have the multiple quotes in the udf
> call?
> why not just, e.g.
> udf_splitstring('CMH0196845', ',')
>
> bassunddrum@.gmail.com wrote:|||If that were the case, wouldn't the statement abort with "Cannot resolve
coallation conflict" ?
"Dean" wrote:

> same data, different servers.. i'd check the tempdb's collations:
> http://msdn2.microsoft.com/ms190920.aspx
> dean
> "Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
> news:u5MtbFpJGHA.1760@.TK2MSFTNGP10.phx.gbl...
>
>|||huh, you're right..
dean
"Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:F6679E50-B055-4238-80D1-717FFAC3CD48@.microsoft.com...
> If that were the case, wouldn't the statement abort with "Cannot resolve
> coallation conflict" ?
> --
> "Dean" wrote:
>