Implicit conversion error - 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 2008 Forums

Transact-SQL (2008)

Implicit conversion error

Author

Topic

dharm280
Starting Member

1 Post

Posted - 2012-06-08 : 04:44:25

Hi,
I am working on a dynamic query for updated data check and its work fine for 145 tables but in for some table erros occured like
"Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict."

When i add keyword "COLLATE DATABASE_DEFAULT" with Varchar datatype column name its run, but problem is that how can i add this dynamically in query,

Query is like this :-
select Count(*) from (

SELECT MIN(PlazaDatabase) as TableName,a.CplazaId,a.[ContactMeansID],a.[ContactMeansType],a.[Value],a.[Comments],a.[Active],a.[ClientID]

FROM
(
select 'PlazaDatabase'as PlazaDatabase, 2 'CPlazaID',a.[ContactMeansID],a.[ContactMeansType],a.[Value],a.[Comments],a.[Active],a.[ClientID] from [192.168.1.10].[TollSongir].[dbo].[ContactMeans] a
union all
select 'CentralDatabase'as CentralDatabase, a.CplazaId,a.[ContactMeansID],a.[ContactMeansType],a.[Value],a.[Comments],a.[Active],a.[ClientID] from [DPTLNEWTEST].[dbo].[ContactMeans] a where a.cPlazaid=2
)a
group by a.CPlazaId,a.[ContactMeansID],a.[ContactMeansType],a.[Value],a.[Comments],a.[Active],a.[ClientID]
HAVING COUNT(*) = 1)a where a.TableName='PlazaDatabase'

CAn any one give me permanent solution

its urgent for me

Thnaks & Regards
Dharmendra Kuamr

Dharmendra Kumar

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-08 : 05:58:48

Why not add that to all varchar column compares.
You could also check the collation when building the dynamic sql and add the collate if necessary,

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-08 : 12:11:56

you can add COLLATE part inside dynamic string where you're doing varchar comparisons

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources