2台 服务器win2003+sql2005企业版
做的链接服务器,在用insert语句导数据时有一部分导过去之后,奇怪的变成负数如数量列(sl)3变成-164766,单价1,金额列也是-164766有一部分是这个错误数字,还有 -16519,-16518,-16517,-16516,804,803,802.。。等等
这是怎么回事?相同的表结构
CREATE TABLE [dbo].[yp_ck_zy](
[ckid] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[cfbh] [varchar](20) NOT NULL,
[ypbh] [varchar](20) NOT NULL,
[lsj] [money] NULL,
[sl] [money] NOT NULL,
[je] [money] NOT NULL,
[kb_je] [money] NULL,
[rq] [datetime] NOT NULL,
[gh] [varchar](20) NOT NULL,
[yfbh] [varchar](20) NULL,
[ykls] [varchar](20) NULL,
[ph] [varchar](50) NULL,
[zfbz] [int] NULL,
[zfczy] [int] NULL,
[zfrq] [datetime] NULL,
[zxbh] [varchar](20) NULL,
[cfmxid] [bigint] NULL DEFAULT ((0)),
[cbcy] [decimal](28, 12) NULL,
[dcbz] [tinyint] NULL CONSTRAINT [DF_yp_ck_zy_dcbz] DEFAULT ((0)),
[dcr] [varchar](20) NULL,
[dcrq] [datetime] NULL,
CONSTRAINT [PK_mzyf_ck_zy] PRIMARY KEY CLUSTERED
(
[ckid] ASC,
[rq] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Ps_yp_ck_zy]([rq])
)
--插入语句
insert into yp_ck_zy( cfbh, ypbh, lsj, sl, je, kb_je, rq, gh, yfbh, ykls, ph, zfbz, zfczy, zfrq, zxbh, cfmxid, cbcy,dcrq) select t1.cfbh, t1.ypbh, t1.lsj, t1.sl, t1.je, t1.kb_je, t1.rq, t1.gh, t1.yfbh, t1.ykls, t1.ph, t1.zfbz, t1.zfczy, t1.zfrq, t1.zxbh, t1.cfmxid, t1.cbcy,'2011-07-28 17:00:15' from [192.168.0.192].his_np.dbo.yp_ck_zy t1, [192.168.0.192].his_np.dbo.zy_brcf t2,[192.168.0.192].his_np.dbo.t_sflx t3,[192.168.0.192].his_np.dbo.t_czy t4 where isnull(t1.dcbz,0)=0 and t1.cfbh=t2.cfbh and t2.sflx=t3.bh and t2.czy=t4.gh and isnull(t4.isblr,0)=0 and t3.isqt=1
做的链接服务器,在用insert语句导数据时有一部分导过去之后,奇怪的变成负数如数量列(sl)3变成-164766,单价1,金额列也是-164766有一部分是这个错误数字,还有 -16519,-16518,-16517,-16516,804,803,802.。。等等
这是怎么回事?相同的表结构
CREATE TABLE [dbo].[yp_ck_zy](
[ckid] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[cfbh] [varchar](20) NOT NULL,
[ypbh] [varchar](20) NOT NULL,
[lsj] [money] NULL,
[sl] [money] NOT NULL,
[je] [money] NOT NULL,
[kb_je] [money] NULL,
[rq] [datetime] NOT NULL,
[gh] [varchar](20) NOT NULL,
[yfbh] [varchar](20) NULL,
[ykls] [varchar](20) NULL,
[ph] [varchar](50) NULL,
[zfbz] [int] NULL,
[zfczy] [int] NULL,
[zfrq] [datetime] NULL,
[zxbh] [varchar](20) NULL,
[cfmxid] [bigint] NULL DEFAULT ((0)),
[cbcy] [decimal](28, 12) NULL,
[dcbz] [tinyint] NULL CONSTRAINT [DF_yp_ck_zy_dcbz] DEFAULT ((0)),
[dcr] [varchar](20) NULL,
[dcrq] [datetime] NULL,
CONSTRAINT [PK_mzyf_ck_zy] PRIMARY KEY CLUSTERED
(
[ckid] ASC,
[rq] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Ps_yp_ck_zy]([rq])
)
--插入语句
insert into yp_ck_zy( cfbh, ypbh, lsj, sl, je, kb_je, rq, gh, yfbh, ykls, ph, zfbz, zfczy, zfrq, zxbh, cfmxid, cbcy,dcrq) select t1.cfbh, t1.ypbh, t1.lsj, t1.sl, t1.je, t1.kb_je, t1.rq, t1.gh, t1.yfbh, t1.ykls, t1.ph, t1.zfbz, t1.zfczy, t1.zfrq, t1.zxbh, t1.cfmxid, t1.cbcy,'2011-07-28 17:00:15' from [192.168.0.192].his_np.dbo.yp_ck_zy t1, [192.168.0.192].his_np.dbo.zy_brcf t2,[192.168.0.192].his_np.dbo.t_sflx t3,[192.168.0.192].his_np.dbo.t_czy t4 where isnull(t1.dcbz,0)=0 and t1.cfbh=t2.cfbh and t2.sflx=t3.bh and t2.czy=t4.gh and isnull(t4.isblr,0)=0 and t3.isqt=1
-- 1.先查询,看数据是否有变.
select t1.cfbh, t1.ypbh, t1.lsj, t1.sl, t1.je, t1.kb_je, t1.rq, t1.gh, t1.yfbh, t1.ykls, t1.ph, t1.zfbz,
t1.zfczy, t1.zfrq, t1.zxbh, t1.cfmxid, t1.cbcy,'2011-07-28 17:00:15'
from
[192.168.0.192].his_np.dbo.yp_ck_zy t1,
[192.168.0.192].his_np.dbo.zy_brcf t2,
[192.168.0.192].his_np.dbo.t_sflx t3,
[192.168.0.192].his_np.dbo.t_czy t4
where isnull(t1.dcbz,0)=0
and t1.cfbh=t2.cfbh and t2.sflx=t3.bh and t2.czy=t4.gh and isnull(t4.isblr,0)=0 and t3.isqt=12.如没变,查目标表yp_ck_zy上是否有insert触发器.
insert into yp_ck_zy
(
cfbh, ypbh, lsj, sl, je, kb_je,
rq, gh, yfbh, ykls, ph, zfbz, zfczy,
zfrq, zxbh, cfmxid, cbcy,dcrq
)
select t1.cfbh, t1.ypbh, t1.lsj, t1.sl, t1.je, t1.kb_je,
t1.rq, t1.gh, t1.yfbh, t1.ykls, t1.ph, t1.zfbz, t1.zfczy,
t1.zfrq, t1.zxbh, t1.cfmxid, t1.cbcy,'2011-07-28 17:00:15'
from [192.168.0.192].his_np.dbo.yp_ck_zy t1,[192.168.0.192].his_np.dbo.zy_brcf t2,
[192.168.0.192].his_np.dbo.t_sflx t3,[192.168.0.192].his_np.dbo.t_czy t4
where isnull(t1.dcbz,0)=0 and t1.cfbh=t2.cfbh and t2.sflx=t3.bh
and t2.czy=t4.gh and isnull(t4.isblr,0)=0 and t3.isqt=1 and sl is not null你的sl的数据源是不是有null值呀?
String s_sql
DateTime rq
rq = dcrq
wf_set_db()
//mz_brcf
st_1.Text = '正在导入..yp_ck_zy.'
s_sql = 'insert into yp_ck_zy( cfbh, ypbh, lsj, sl, je, kb_je, rq, gh, yfbh, ykls, ph, zfbz, zfczy, zfrq, zxbh, cfmxid, cbcy,dcrq)'&
+" select t1.cfbh, t1.ypbh, t1.lsj, t1.sl, t1.je, t1.kb_je, t1.rq, t1.gh, t1.yfbh, t1.ykls, t1.ph, t1.zfbz, t1.zfczy, t1.zfrq, t1.zxbh, t1.cfmxid, t1.cbcy,'"+string(rq)+"'"&
+' from '+lk_yk+'yp_ck_zy t1, '+lk_yk+'zy_brcf t2,'+lk_yk+'t_sflx t3,'+lk_yk+'t_czy t4 where isnull(t1.dcbz,0)=0 and t1.cfbh=t2.cfbh and t2.sflx=t3.bh and t2.czy=t4.gh and isnull(t4.isblr,0)=0 and t3.isqt=1'EXECUTE Immediate :s_sql Using sql_tg;
If chk_sql_sa(sql_tg) = -1 Then Return -1
。其中wf_set_db()内容定义为
string ss1
sql_tg.AutoCommit = True
ss1='SET ANSI_NULLS ON'
EXECUTE Immediate :ss1 Using sql_tg;
ss1='SET ANSI_warnings ON'
EXECUTE Immediate :ss1 Using sql_tg;
ss1='set xact_abort ON'
EXECUTE Immediate :ss1 Using sql_tg;
sql_tg.AutoCommit = false
sqlca.AutoCommit = True
ss1='SET ANSI_NULLS ON'
EXECUTE Immediate :ss1 Using sqlca;
ss1='SET ANSI_warnings ON'
EXECUTE Immediate :ss1 Using sqlca;
ss1='set xact_abort ON'
EXECUTE Immediate :ss1 Using sqlca;
sqlca.AutoCommit = false
插入语句 是我在事件探查器中跟踪得到了语句
--插入语句
insert into yp_ck_zy( cfbh, ypbh, lsj, sl, je, kb_je, rq, gh, yfbh, ykls, ph, zfbz, zfczy, zfrq, zxbh, cfmxid, cbcy,dcrq) select t1.cfbh, t1.ypbh, t1.lsj, t1.sl, t1.je, t1.kb_je, t1.rq, t1.gh, t1.yfbh, t1.ykls, t1.ph, t1.zfbz, t1.zfczy, t1.zfrq, t1.zxbh, t1.cfmxid, t1.cbcy,'2011-07-28 17:00:15' from [192.168.0.192].his_np.dbo.yp_ck_zy t1, [192.168.0.192].his_np.dbo.zy_brcf t2,[192.168.0.192].his_np.dbo.t_sflx t3,[192.168.0.192].his_np.dbo.t_czy t4 where isnull(t1.dcbz,0)=0 and t1.cfbh=t2.cfbh and t2.sflx=t3.bh and t2.czy=t4.gh and isnull(t4.isblr,0)=0 and t3.isqt=1