data type problems - SQL Server Forums

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

Transact-SQL (2005)

data type problems

Author

Topic

jeff06
Posting Yak Master

166 Posts

Posted - 2008-06-13 : 13:27:57


select *, date as calldate,ndnis=cast(dnis as varchar(20))
into #targetcall
from tb_Calls
where DNIS in (1130,1012, 1011, 
               1139,1131, 1140, 
               1141,1028,1103) 
               and date>'10/1/2007'

--drop table #othercall
select np.*,  np.date as calldate,  ndnis='All Other DNIS' 
into #othercall
from tb_Calls np
left join #targetcall tc on tc.date=np.date and tc.clid=np.clid
where np.DNIS in (1001,1002,1003,1004,1005,1007) 
               and np.date>'10/1/2007'
               and tc.clid is null


--drop table #allcall
select * into #allcall
from 
(
   select *
   from #targetcall
   union all
   select *
   from #othercall
) as a

I get error msg from union two tables
Msg 457, Level 16, State 1, Line 1
Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.

It seems the Dnis in two tables does not match in type. How can I solve this problem?
Thanks.
Jeff

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-13 : 13:31:38

try specifying the collation using COLLATE clause

jeff06
Posting Yak Master

166 Posts

Posted - 2008-06-13 : 14:06:40

Thanks.
Can you give me more details on how to do that?
Jeff

jeff06
Posting Yak Master

166 Posts

Posted - 2008-06-13 : 14:12:30

I guess the key here is:
ndnis=cast(dnis as varchar(20))
ndnis='All Other DNIS'
how to let thoes two have same data type?
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-13 : 14:16:34

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1186718,00.html

jeff06
Posting Yak Master

166 Posts

Posted - 2008-06-13 : 16:58:43

Very strange!
My code worked in sql server 2000 very well. Our company just upgraded the system to 2005 and this code stops working.
Can someone give some explaination on what is happening with 2005?
Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources