运行一个SP,错误
Cannot resolve the collation conflict between "Chinese_PRC_CI_AS" and "Chinese_Taiwan_Stroke_CI_AS" in the equal to operation.请问什么原因
server 2005
Cannot resolve the collation conflict between "Chinese_PRC_CI_AS" and "Chinese_Taiwan_Stroke_CI_AS" in the equal to operation.请问什么原因
server 2005
解决方案 »
- 安装SQL SERVER 2000个人版时,出现错误,小弟急啊!
- 求SQL语句
- sql2000 中,通过cast 把text ntext 字段改成了varchar8000 ,会不会影响原表的功能?
- 求一个存储过程
- 急,求一语句,各位大哥帮帮忙!!
- 求助:sql语句可以给图片重命名吗?
- ActiveX 的问题
- join 效率问题。我要比较2张表里面的某个字段比如:cartonID,A表有一列,B表有1列。当然要完全配对才可以。数据表数据在500w上下。
- 復雜一些的篩選列重復記錄SQL語句
- install repository intance(ORACLE Designer)时出错,该怎么解决?
- 如何通过Sql语句取得账户的权限
- 请教一下恢复数据库的时候杀死数据库进程的问题
如:
Chinese_PRC_CS_AI_WS
前半部份:指UNICODE字符集,Chinese_PRC_指针对大陆简体字UNICODE的排序规则。
排序规则的后半部份即后缀 含义:
_BIN 二进制排序
_CI(CS) 是否区分大小写,CI不区分,CS区分
_AI(AS) 是否区分重音,AI不区分,AS区分
_KI(KS) 是否区分假名类型,KI不区分,KS区分
_WI(WS) 是否区分宽度 WI不区分,WS区分
Cannot resolve the collation conflict between "Chinese_PRC_CI_AS" and "Chinese_Taiwan_Stroke_CI_AS" in the equal to operation.
Msg 266, Level 16, State 2, Procedure CMM_SP_CARD_PRIZE, Line 59
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.
set ANSI_NULLS OFF
set QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[CMM_SP_CARD_PRIZE]
@datebegin datetime,
@dateend datetime,
@outparam int outputAS
create table #tt (id int identity(1,1), product_id numeric(9),psid varchar(50),sn varchar(50),prizecode int)
declare @amount int
declare @p1 int
declare @p2 int
declare @p3 int
declare @prizenum int
declare @error int
declare @sql varchar(4000)declare @dtbegin varchar(50)
declare @dtend varchar(50)
begin transaction
if @datebegin is null or @dateend is null
begin
set @error=-1
if @error!=0 goto error_handler
end select @amount=count(product_id) from jsales_register_userinfo
where register_date>@datebegin and register_date<=@dateend and register_type=5 select @p1=floor(@amount*0.00002222)
select @p2=floor(@amount*0.0002)
select @p3=floor(@amount*0.0033333) select @prizenum=@p1+@p2+@p3
select @dtbegin=convert(varchar,@datebegin,101)
select @dtend=convert(varchar,@dateend,101) select @sql='insert into #tt (product_id,psid,prizecode,sn)'
+' '
+'select top '+str(@prizenum)+'product_id,psid,0,sn from jsales_register_userinfo '
+ ' where register_date>'''+@dtbegin+''' and register_date<='''+@dtend+''' and register_type=5 and mobilephoneno is not null order by newid()'
print @sql
exec (@sql)
set @error=@@error
if @error!=0 goto error_handler update #tt set prizecode=1 where id>=1 and id<1+@p1
update #tt set prizecode=2 where id>=1+@p1 and id<1+@p1+@p2
update #tt set prizecode=3 where prizecode=0 set @error=@@error
if @error!=0 goto error_handler delete from #tt where product_id in (
(select a.product_id from #tt a inner join cmm_club_prize_lucky_users b on a.psid=b.psid)
union all (select e.product_id from #tt e inner join cmm_lucky_users c on e.psid=c.psid )
union all (select f.product_id from #tt f inner join cmm_card_prize_lucky_users d on f.psid=d.psid))
or (psid is null)or (sn is null)
set @error=@@error
if @error!=0 goto error_handler
delete from cmm_card_prize_temp
insert into cmm_card_prize_temp (product_id,psid,sn,prizecode,datebegin,dateend )
select product_id,psid,sn,prizecode,@dateBegin,@dateend from #tt
set @error=@@error
if @error!=0 goto error_handler
commit transaction
error_handler:
drop table #tt
if @@trancount!=0 rollback transaction
set @outparam=@error
return @error
可选择出所有排序规则,你在出错的表上右击 所有任务中选择create 到新查询编辑器窗口,检查一下建库脚本是否有错。
比如:
USE [test]
GO
/****** 对象: Table [dbo].[datable] 脚本日期: 02/01/2007 10:47:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[datable](
[startip] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[endip] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[province] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[city] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[scompip] [decimal](18, 0) NULL,
[ecompip] [decimal](18, 0) NULL
) ON [PRIMARY]GO
SET ANSI_PADDING OFF
将其中的某一字段改为 COLLATE Chinese_Taiwan_Stroke_CI_AS 则会出错
set ANSI_NULLS OFF
set QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[CMM_SP_CARD_PRIZE]
@datebegin datetime,
@dateend datetime,
@outparam int outputAS
create table #tt (id int identity(1,1), product_id numeric(9),psid varchar(50),sn varchar(50),prizecode int)
declare @amount int
declare @p1 int
declare @p2 int
declare @p3 int
declare @prizenum int
declare @error int
declare @sql varchar(4000)declare @dtbegin varchar(50)
declare @dtend varchar(50)
begin transaction
if @datebegin is null or @dateend is null
return 1
-- begin
-- set @error=-1
-- if @error!=0 goto error_handler
-- end
select @amount=count(product_id) from jsales_register_userinfo
where register_date>@datebegin and register_date<=@dateend and register_type=5
select @p1=floor(@amount*0.00002222)
select @p2=floor(@amount*0.0002)
select @p3=floor(@amount*0.0033333) select @prizenum=@p1+@p2+@p3
select @dtbegin=convert(varchar,@datebegin,101)
select @dtend=convert(varchar,@dateend,101) select @sql='insert into #tt (product_id,psid,prizecode,sn)'
+' '
+'select top '+str(@prizenum)+'product_id,psid,0,sn from jsales_register_userinfo '
+ ' where register_date>'''+@dtbegin+''' and register_date<='''+@dtend+''' and register_type=5 and mobilephoneno is not null order by newid()'
print @sql
exec (@sql)
-- set @error=@@error
-- if @error!=0 goto error_handler update #tt set prizecode=1 where id>=1 and id<1+@p1
update #tt set prizecode=2 where id>=1+@p1 and id<1+@p1+@p2
update #tt set prizecode=3 where prizecode=0-- set @error=@@error
-- if @error!=0 goto error_handler delete from #tt where product_id in (
(select a.product_id from #tt a inner join cmm_club_prize_lucky_users b on a.psid=b.psid)
union all (select e.product_id from #tt e inner join cmm_lucky_users c on e.psid=c.psid )
union all (select f.product_id from #tt f inner join cmm_card_prize_lucky_users d on f.psid=d.psid))
or (psid is null)or (sn is null)
-- set @error=@@error
-- if @error!=0 goto error_handler
delete from cmm_card_prize_temp
insert into cmm_card_prize_temp (product_id,psid,sn,prizecode,datebegin,dateend )
select product_id,psid,sn,prizecode,@dateBegin,@dateend from #tt
-- set @error=@@error
-- if @error!=0 goto error_handler
commit transaction
drop table #tt
--error_handler:
--drop table #tt
--if @@trancount!=0 rollback transaction
--set @outparam=@error
--return @error
判断存储过程是否执行成功可用
EXEC @return_value = [dbo].[roc_test]
@Param1 = value1,
@Param2 = value2,
select @return_value