create table #t1(rkey char(20))
create table #t2(key1 char(40))
insert #t1 values('aa')
insert #t2 values('bb')select rkey as aa from #t1
union all
select key1 as aa from #t2
这样连接是没有问题,但是我下面的连接就有问题,如下:
select inv_part_description as aa from data0017
union all
select [description] as aa from data0072
其中inv_part_description是char(20),description是char(40)。在查询分析器里面执行就有这样的
错误出现
Server: Msg 457, Level 16, State 1, Line 1
Implicit conversion of char value to char cannot be performed because the collation of the value is unresolved due to a collation conflict.
实在是不明白。
create table #t2(key1 char(40))
insert #t1 values('aa')
insert #t2 values('bb')select rkey as aa from #t1
union all
select key1 as aa from #t2
这样连接是没有问题,但是我下面的连接就有问题,如下:
select inv_part_description as aa from data0017
union all
select [description] as aa from data0072
其中inv_part_description是char(20),description是char(40)。在查询分析器里面执行就有这样的
错误出现
Server: Msg 457, Level 16, State 1, Line 1
Implicit conversion of char value to char cannot be performed because the collation of the value is unresolved due to a collation conflict.
实在是不明白。
解决方案 »
- 请教sql server2000作业失败,发送邮件问题
- 请问如果数据库中的某个表没有时间字段,通过SQL语句有办法把查询出最新按最新插入数据的时间排序显示结果吗?
- 请问,数据表有一列是系名,存的是小写字符,怎么把它转换成大写输出?
- 试图从数据库 'usermis' 中提取的逻辑页 (1:2909) 属于对象 'FPNO',而非对象 'KPLOG'。
- VB6+SQL2008存储过程疑难问题,求帮助
- sql2005数据库连接问题
- 把从字段名以"|"号间隔的test.txt导入到SQLSERVER表中,提示错误,谢谢大家!!
- 看一下这个语句怎么实现
- 关于sql语句的问题
- 如何列出所有可用的ms sql server数据库服务器?(包括其他机器上的)
- 请问用select top 100 * from tables 后想在一个SQL语句中得到最大值该怎么做啊?谢谢
- 一个查询SQL的问题。
最好把其实定义为varchar
Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.
create table data0072([description] char(40))
insert data0017 values('aa')
insert data0072 values('bb')select inv_part_description as aa from data0017
union all
select [description] as aa from data0072drop table data0017,data0072
ith 列的数据类型 结果表中 ith 列的数据类型
非数据兼容类型(数据转换不由 Microsoft® SQL Server™ 隐性处理)。 SQL Server 返回的错误。
长度为 L1 和 L2 的固定长度 char。 长度与 L1 和 L2 中较长的那个相等的固定长度 char。
长度为 L1 和 L2 的固定长度 binary。 长度与 L1 和 L2 中较长的那个相等的固定长度 binary。
任一列或两列都是可变长度 char。 长度与为 ith 列指定的最大长度相等的可变长度 char。
任一列或两列都是可变长度 binary。 长度与为 ith 列指定的最大长度相等的可变长度 binary。
两列都是数字数据类型(如 smallint、int、float、money)。 与两列的最大精度相等的数据类型。例如,如果表 A 的 ith 列是 int 类型,表 B 的 ith 列是 float 类型,则 ith 列在结果表中的数据类型是 float,因为 float 比 int 更精确。
两列的描述都指定 NOT NULL。 指定 NOT NULL。 联机帮助里面是这样子说的,但是好像问题还是没有解决。
你把出错提示贴出来来啊,就你举的那个例子是没有问题的
把字段以及类型都贴出来
drop table [dbo].[DATA0017]
GOCREATE TABLE [dbo].[DATA0017] (
[RKEY] [decimal](10, 0) IDENTITY (1, 1) NOT NULL ,
[INV_PART_NUMBER] [char] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[INV_PART_DESCRIPTION] [char] (40) COLLATE Chinese_PRC_CI_AS NULL ,
[MANUFACTURER_CODE] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[MANUFACTURER_NAME] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[CYCLE_CODE] [char] (2) COLLATE Chinese_PRC_CI_AS NULL ,
[BATCH_SERIAL_CONTROL] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[TTYPE] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
[P_M] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
[S_B_N] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
[INSPECT] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
[STOP_PURCH] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
[MIX_PARTS] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
[PHY_COUNT_METHOD] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
[BOM_FLAG] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
[COST_METHOD] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
[MODIFY_ENGG_RTE_CODE] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
[MODIFY_PROD_RTE_CODE] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
[G_L_CODE_SELL_PTR] [decimal](10, 0) NULL ,
[G_L_CODE_BUY_PTR] [decimal](10, 0) NULL ,
[ENGG_ROUTE_PTR] [decimal](10, 0) NULL ,
[PROD_ROUTE_PTR] [decimal](10, 0) NULL ,
[NOTE_PAD_PTR] [decimal](10, 0) NULL ,
[ALTERNATE_PART_PTR] [decimal](10, 0) NULL ,
[PROD_CODE_SELL_PTR] [decimal](10, 0) NULL ,
[PROD_CODE_BUY_PTR] [decimal](10, 0) NULL ,
[AUTHORIZED_BUYER_PTR] [decimal](10, 0) NULL ,
[STOCK_UNIT_PTR] [decimal](10, 0) NULL ,
[PURCH_UNIT_PTR] [decimal](10, 0) NULL ,
[QUOTE_UNIT_PTR] [decimal](10, 0) NULL ,
[SELLING_UNIT_PTR] [decimal](10, 0) NULL ,
[PREF_SUPPLIER_PTR] [decimal](10, 0) NULL ,
[STOCK_PURCH] [decimal](20, 7) NULL ,
[STOCK_QUOTE] [decimal](20, 7) NULL ,
[STOCK_SELL] [decimal](20, 7) NULL ,
[STOCK_BASE] [decimal](20, 7) NULL ,
[STD_COST] [decimal](20, 7) NULL ,
[ACTUAL_COST] [decimal](20, 7) NULL ,
[SELLING_PRICE_1] [decimal](20, 7) NULL ,
[SELLING_PRICE_2] [decimal](20, 7) NULL ,
[SELLING_PRICE_3] [decimal](20, 7) NULL ,
[SELLING_PRICE_4] [decimal](20, 7) NULL ,
[SELLING_PRICE_5] [decimal](20, 7) NULL ,
[SELLING_VOL_1] [decimal](10, 0) NULL ,
[SELLING_VOL_2] [decimal](10, 0) NULL ,
[SELLING_VOL_3] [decimal](10, 0) NULL ,
[SELLING_VOL_4] [decimal](10, 0) NULL ,
[DISC_1] [decimal](20, 7) NULL ,
[DISC_2] [decimal](20, 7) NULL ,
[DISC_3] [decimal](20, 7) NULL ,
[DISC_4] [decimal](20, 7) NULL ,
[DISC_5] [decimal](20, 7) NULL ,
[FAILURE_RATE] [decimal](20, 7) NULL ,
[PROD_LEAD_TIME] [decimal](10, 0) NULL ,
[SHELF_LIFE] [decimal](10, 0) NULL ,
[COUNT_INTERVAL_TRANS] [decimal](10, 0) NULL ,
[QUAN_ON_HAND] [decimal](20, 7) NULL ,
[QUAN_ALLOCATED] [decimal](20, 7) NULL ,
[QUAN_ON_ORDER] [decimal](20, 7) NULL ,
[QUAN_IN_INSP] [decimal](20, 7) NULL ,
[YTD_USAGE] [decimal](20, 7) NULL ,
[YTD_PO_REC_D] [decimal](20, 7) NULL ,
[YTD_WO_REC_D] [decimal](20, 7) NULL ,
[YTD_XFER_REC_D] [decimal](20, 7) NULL ,
[YTD_REJECTED] [decimal](20, 7) NULL ,
[YTD_RETURNED] [decimal](20, 7) NULL ,
[TOTAL_PRODUCED] [decimal](20, 7) NULL ,
[TOTAL_SCHEDULED] [decimal](20, 7) NULL ,
[EST_SCRAP] [decimal](20, 7) NULL ,
[LYR_USAGE] [decimal](20, 7) NULL ,
[LYR_PO_REC_D] [decimal](20, 7) NULL ,
[LYR_WO_REC_D] [decimal](20, 7) NULL ,
[LYR_XFER_REC_D] [decimal](20, 7) NULL ,
[LYR_REJECTED] [decimal](20, 7) NULL ,
[LYR_RETURNED] [decimal](20, 7) NULL ,
[ACTIVE_SALES_ORDERS] [decimal](10, 0) NULL ,
[ACTIVE_PROD_ORDERS] [decimal](10, 0) NULL ,
[QUAN_IN_TRANSIT] [decimal](20, 7) NULL ,
[QUAN_BACKLOG] [decimal](20, 7) NULL ,
[FIXED_SCRAP_RATE] [decimal](20, 7) NULL ,
[NEW_STD_COST] [decimal](20, 7) NULL ,
[CATALOG_NUM] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[OPT_LOT_SIZE] [decimal](10, 0) NULL ,
[AVL_FLAG] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
[LAYUP_FOR_IES] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[CURRENT_ACT_ACTIVITY] [decimal](20, 7) NULL ,
[CURRENT_ACT_MATERIAL] [decimal](20, 7) NULL ,
[LAST_CURNT_COST_UPDT] [datetime] NULL ,
[LAST_STD_COST_UPDT] [datetime] NULL ,
[ALLOW_EDIT_FLAG] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
[WAREHOUSE_PTR] [decimal](10, 0) NULL ,
[STORAGE_COND_PTR] [decimal](10, 0) NULL ,
[SHELF_LIFE_PTR] [decimal](10, 0) NULL ,
[STORG_PRECATION_PTR] [decimal](10, 0) NULL ,
[CONSIGN_ONHAND_QTY] [decimal](20, 7) NULL ,
[CONSIGN_QTY_IN_INSP] [decimal](20, 7) NULL ,
[REPORT_VALUE1] [decimal](20, 7) NULL ,
[REPORT_VALUE2] [decimal](20, 7) NULL ,
[REPORT_VALUE3] [decimal](20, 7) NULL ,
[IES_SETUP_COST] [decimal](20, 7) NULL ,
[IES_PROCESS_COST] [decimal](20, 7) NULL ,
[IES_INDIRECT_MATL1] [decimal](20, 7) NULL ,
[IES_INDIRECT_MATL2] [decimal](20, 7) NULL ,
[IES_DIRECT_MATL_COST] [decimal](20, 7) NULL ,
[IES_OVERHEAD_COST] [decimal](20, 7) NULL ,
[MIN_LOT_SIZE_AUTOREL] [decimal](20, 7) NULL ,
[AUTO_RELEASE_FLAG] [decimal](10, 0) NULL ,
[AUTO_PURCHASE_FLAG] [decimal](10, 0) NULL ,
[CRITICAL_MATL_FLAG] [decimal](10, 0) NULL ,
[AUTO_RELEASE_HOLDFLG] [decimal](10, 0) NULL ,
[HOLD_BY_EMPL_PTR] [decimal](10, 0) NULL ,
[ONHOLD_DATE] [datetime] NULL ,
[CUR_MONTH_OPEN_BAL] [decimal](20, 7) NULL ,
[CONSIGNMENT_FLAG] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
[ACTIVE_FLAG] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
[LOT_NUMBER_COUNT] [decimal](10, 0) NULL ,
[ANALYSIS_CODE1] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[ANALYSIS_CODE2] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[ANALYSIS_CODE3] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[ANALYSIS_CODE4] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[ANALYSIS_CODE5] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[DAYS_EARLY_SCHEDULE] [decimal](10, 0) NULL ,
[PLAN_INNER_LAYER] [char] (1) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
drop table [dbo].[DATA0072]
GOCREATE TABLE [dbo].[DATA0072] (
[RKEY] [numeric](10, 0) IDENTITY (1, 1) NOT NULL ,
[POPTR] [numeric](10, 0) NULL ,
[DESCRIPTION] [char] (40) COLLATE Chinese_PRC_BIN NULL ,
[QUAN_ORD] [numeric](20, 7) NULL ,
[QUAN_INVD] [numeric](20, 7) NULL ,
[UNIT_PRICE] [numeric](20, 7) NULL ,
[FED_TAX] [numeric](20, 7) NULL ,
[STATE_TAX] [numeric](20, 7) NULL ,
[CITY_TAX] [numeric](20, 7) NULL ,
[COUNTY_TAX] [numeric](20, 7) NULL ,
[GL_PTR] [numeric](10, 0) NULL ,
[QUANTITY_RECEIVED] [numeric](20, 7) NULL ,
[DESCRIPTION2] [char] (40) COLLATE Chinese_PRC_BIN NULL ,
[DEL_DATE] [datetime] NULL ,
[UNIT_PTR] [numeric](10, 0) NULL ,
[DESCRIPTION3] [char] (40) COLLATE Chinese_PRC_BIN NULL ,
[DESCRIPTION4] [char] (40) COLLATE Chinese_PRC_BIN NULL ,
[PRINTED_DATE] [datetime] NULL ,
[QTY_RETURN] [numeric](20, 7) NULL ,
[PRICE_INCLUDES_TAX] [numeric](18, 0) NOT NULL ,
[DISCOUNT] [numeric](20, 7) NOT NULL
) ON [PRIMARY]
GO
Server: Msg 457, Level 16, State 1, Line 1
Implicit conversion of char value to char cannot be performed because the collation of the value is unresolved due to a collation conflict.
公司用的都是正版软件
之类的
要指定为相同
排序规则冲突Chinese_PRC_CI_AS
之类的
要指定为相同
有什么解决办法没有?这个是用别的公司的软件,不能改表结构的。
再union 试试
用CAST或者CONVERT?
Server: Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for UNION operation.
go
create table DATA0017
(INV_PART_DESCRIPTION char(40) COLLATE Chinese_PRC_CI_AS)
go
if object_id('DATA0072') is not null drop table DATA0072
go
create table DATA0072
([DESCRIPTION] char(40) COLLATE Chinese_PRC_BIN)
go
insert into data0017 select 'aaa' union select 'bbb'
insert into data0072 select 'ccc' union select 'ddd'
go
alter table DATA0072 alter column [DESCRIPTION] char(40) COLLATE Chinese_PRC_CI_AS
go
select INV_PART_DESCRIPTION from data0017 union select [DESCRIPTION] from data0072
/*
INV_PART_DESCRIPTION
aaa
bbb
ccc
ddd
*/
go
drop table DATA0017
drop table DATA0072
go
create table DATA0017
(INV_PART_DESCRIPTION char(40) COLLATE Chinese_PRC_CI_AS)
go
if object_id('DATA0072') is not null drop table DATA0072
go
create table DATA0072
([DESCRIPTION] char(40) COLLATE Chinese_PRC_BIN)
go
insert into data0017 select 'aaa' union select 'bbb'
insert into data0072 select 'ccc' union select 'ddd'
go
alter table DATA0072 alter column [DESCRIPTION] char(40) COLLATE Chinese_PRC_CI_AS --修改一个表的排序规则
go
select INV_PART_DESCRIPTION from data0017 union select [DESCRIPTION] from data0072
/*
INV_PART_DESCRIPTION
aaa
bbb
ccc
ddd
*/
go
drop table DATA0017
drop table DATA0072
[INV_PART_DESCRIPTION] [char] (40) COLLATE Chinese_PRC_CI_AS NULL )
CREATE TABLE [dbo].[DATA0072] (
[DESCRIPTION] [char] (40) COLLATE Chinese_PRC_BIN NULL )select inv_part_description COLLATE Chinese_PRC_CI_AS as aa from data0017
union all
select [description] COLLATE Chinese_PRC_CI_AS as aa from data0072drop table [dbo].[DATA0017],[dbo].[DATA0072]
select INV_PART_DESCRIPTION as aa from data0017 union
select [DESCRIPTION] COLLATE Chinese_PRC_CI_AS from data0072