无法解决"Latin1_General_CI_AI“和"SQL_Latin1_General_CP1_CI_AS”之间的排序规则冲突

Question

insert into @temp_traffic_reports(id, assetname,datestamp,messagetype,itemquantity)
(
    select 
        tf.ID as id, 
        ta.TrackingIdentityName as assetname, 
        DATEADD(hour,@TimeZoneValue,Datestamp) as datestamp ,
        'Poll for position report' as messagetype,
        '1' as  itemquantity
FROM trackmaps_WHB.dbo.MESForwardMessage tf
join @temp_assets ta 
    on tf.TerminalID = ta.TrackingIndentity
where 
    Datestamp between DATEADD(hour,@TimeZoneValue,@fDate) and DATEADD(hour,@TimeZoneValue,@tDate)
);

Answer 1

insert into @temp_traffic_reports(id, assetname,datestamp,messagetype,itemquantity)
(
    select 
        tf.ID as id, 
        ta.TrackingIdentityName COLLATE SQL_Latin1_General_CP1_CI_AS as assetname , 
        DATEADD(hour,@TimeZoneValue,Datestamp) as datestamp ,
        'Poll for position report' as messagetype,
        '1' as  itemquantity
FROM trackmaps_WHB.dbo.MESForwardMessage tf
join @temp_assets ta 
    on tf.TerminalID = ta.TrackingIndentity
where 
    Datestamp between DATEADD(hour,@TimeZoneValue,@fDate) and DATEADD(hour,@TimeZoneValue,@tDate)

Answer 2

您必须在比较中强制两个排序规则中的一个

Answer 3

insert into @temp_traffic_reports(id, assetname,datestamp,messagetype,itemquantity)
(
    select 
        tf.ID as id, 
        ta.TrackingIdentityName as assetname, 
        DATEADD(hour,@TimeZoneValue,Datestamp) as DATES_STAMP ,
        'Poll for position report' as messagetype,
        '1' as  itemquantity
FROM trackmaps_WHB.dbo.MESForwardMessage tf
join @temp_assets ta 
    on tf.TerminalID = ta.TrackingIndentity
CROSS APPLY (SELECT DATES_STAMP) AS A
 WHERE A.DATES_STAMP between DATEADD(hour,@TimeZoneValue,@fDate) and DATEADD(hour,@TimeZoneValue,@tDate)
)