22,209
社区成员
发帖
与我相关
我的任务
分享SQL2000的备份还原到2008上，出现错误！
出错对象：TxjPanel
异常类型：Exception
错误信息:查询时发生错误：列名或所提供值的数目与表定义不匹配。
出现以上错误，查询后发现语句在2000上可以执行，在新的2008的服务器上执行就会报错，怎么解决？急死我了。
异常类型：Exception
错误信息:查询时发生错误：列名或所提供值的数目与表定义不匹配。
出现以上错误，查询后发现语句在2000上可以执行，在新的2008的服务器上执行就会报错，怎么解决？急死我了。
...全文
请发表友善的回复…
发表回复
gaoyan582 2014-09-22
- 打赏
- 举报
主要是原先的服务器很老了，读写很慢，查询费时，就买了太新的服务器。 新的服务器自带的就是2008. 现在不知道有什么好的办法。
Q315054403 2014-09-20
- 打赏
- 举报
具体问题具体分析，若SQL写死在前端EXE里，那就没办法，只有保留兼容级别80（SQL2000），数据库是可以升级的，最多2个版本号
若是在SP、TRIGGER，那可以修改、调整下SQL指令
肯定需要熟悉SQL SERVER的人跟进才对
升级前多点测试，切换后就少点麻烦
hleb231 2014-09-20
- 打赏
- 举报
你用2005试下，
不过你前端没升级，升级数据库有何意义。
hleb231 2014-09-20
- 打赏
- 举报
ERP不只是数据库呀，你强行升级，前端程序会报错的嘛。
gaoyan582 2014-09-20
- 打赏
- 举报
我公司用的ERP已经没有支持了。 2008不是能兼容低版本吗？还会有兼容问题？ 麻烦大了。
hleb231 2014-09-20
- 打赏
- 举报
国内这些用2000数据的ERP ，一般只支持到2005SP3
hleb231 2014-09-20
- 打赏
- 举报
你是直接升级了数据库？这样是不行的。
gaoyan582 2014-09-20
- 打赏
- 举报
TRUNCATE TABLE textreport
INSERT SHIPTEMPA
SELECT sStoreOutNo,tStoreOutTime ,sCustomerShortName,sOrderNo,sPatternName
,sMaterialCode,sDtlCustomerOrder,sSPUNo,isnull(sCustomerBoxNo,'0') sCustomerBoxNo,sskuno
,SUM(nStoreOutQty) AS nStoreOutQty,sUnit,sColorShade
FROM
(SELECT A.sStoreOutNo,A.sStoreOutType,A.sRemark,CONVERT(VARCHAR(10),A.tStoreOutTime,120) AS tStoreOutTime
,C.bInnerInOutType
,CASE S.sUnit
WHEN '码' THEN
--如果客户等级为1 就需要四舍无入
CASE
WHEN O.sCustomerLevel LIKE '%%1%%' THEN
CASE
WHEN D.nStoreOutQty/0.9144 - CAST((D.nStoreOutQty/0.9144) AS INT) >= 0.5 THEN
CAST(CAST((D.nStoreOutQty/0.9144 ) AS INT) + 1 as NUMERIC(9,1))
ELSE
CAST(CAST((D.nStoreOutQty/0.9144 ) AS INT) AS NUMERIC(9,1))
END
ELSE
--判断整数去除小数位
CASE
WHEN D.nStoreOutQty/0.9144 - CAST((D.nStoreOutQty/0.9144) as INT) < 0.05 THEN
CAST(CAST((D.nStoreOutQty/0.9144 ) AS INT) AS NUMERIC(9,1))
ELSE
CAST(CAST((D.nStoreOutQty/0.9144 ) AS NUMERIC(9,1)) AS NUMERIC(9,1))
END
END
WHEN '米' THEN
--如果客户等级为1 就需要四舍无入
CASE
WHEN O.sCustomerLevel LIKE '%%1%%' THEN
CASE
WHEN D.nStoreOutQty - CAST((D.nStoreOutQty) AS INT) >= 0.5 THEN
CAST(CAST((D.nStoreOutQty) AS INT) + 1 AS NUMERIC(9,1))
ELSE
CAST(CAST((D.nStoreOutQty) AS INT) as NUMERIC(9,1))
END
ELSE
--判断整数去除小数位
CASE D.nStoreOutQty - CAST(D.nStoreOutQty AS int)
WHEN 0 THEN
CAST(CAST((D.nStoreOutQty) AS INT) AS NUMERIC(9,1))
ELSE
CAST(CAST((D.nStoreOutQty) AS NUMERIC(9,1)) AS NUMERIC(9,1))
END
END
END AS nStoreOutQty
,dbo.fnpbGetSKUorSPU(H.sSKUNo) AS sskuno
,I.sPrimaryDefect,sColorShade
,K.sMaterialCode
,L.sOrderNo
,K.sPatternName
,O.sCustomerShortName
,Q.sSPUNo
,CAST(R.sCustomerBoxNo AS INT) AS sCustomerBoxNo
,S.sDtlCustomerOrder,S.sUnit
FROM WeavingERP.dbo.xjimOutHdr A WITH (NOLOCK)
INNER JOIN WeavingERP.dbo.xjimStoreInOutType C WITH (NOLOCK)
ON C.sStoreInOutType=A.sStoreOutType AND sStoreOutType='出货'
INNER JOIN WeavingERP.dbo.xjimOutDtl D WITH (NOLOCK)
ON A.uOutHdrId=D.uOutHdrId
INNER JOIN WeavingERP.dbo.xjimStoreKeepingUnit H WITH (NOLOCK)
ON H.uSKUId=D.uSKUId
INNER JOIN WeavingERP.dbo.xjmmProductMaterial I WITH (NOLOCK)
ON I.uProductMaterialId=H.uProductMaterialId
INNER JOIN dbo.xjsdOrderDtl S WITH (NOLOCK)
ON S.uOrderDtlId=I.uOrderDtlId
INNER JOIN WeavingERP.dbo.xjsdOrderMaterialWeaving K WITH (NOLOCK)
ON K.uOrderMaterialId=S.uOrderMaterialId
INNER JOIN WeavingERP.dbo.xjsdOrderHdr L WITH (NOLOCK)
ON L.uOrderHdrId=K.uOrderHdrId AND L.uOrderHdrId=S.uOrderHdrId
INNER JOIN dbo.xjsdCustomerEx O WITH (NOLOCK)
ON L.uCustomerId=O.uCustomerId
INNER JOIN dbo.xjimStorePackageUnitDtl P WITH (NOLOCK)
ON H.uSKUId = P.uSKUId
INNER JOIN dbo.xjimStorePackageUnitHdr Q WITH (NOLOCK)
ON P.uSPUHdrId=Q.uSPUHdrId AND Q.iType = 3
INNER JOIN dbo.xjpcCustomerBoxDtl R WITH (NOLOCK)
ON Q.uSPUHdrId=R.uSPUHdrId
WHERE C.bInnerInOutType=0 AND A.iStatus=2 --AND G.sStoreName='成品布仓'
) AS aView
WHERE (tStoreOutTime = '2014-09-19')
GROUP BY sStoreOutNo,tStoreOutTime ,sCustomerShortName,sOrderNo,sPatternName
,sMaterialCode,sDtlCustomerOrder,sSPUNo,sCustomerBoxNo,sskuno,sUnit,sColorShade
--B表是待有卷号的
INSERT SHIPTEMPB
SELECT sMaterialCode, sCustomerBoxNo ,sOrderNo ,nStoreOutQty,sColorShade FROM SHIPTEMPA
--表3是得到所有的包号
INSERT SHIPTEMPC
SELECT sStoreOutNo,tStoreOutTime,sCustomerShortName,sOrderNo,sPatternName
,sMaterialCode,sDtlCustomerOrder,sSPUNo,sCustomerBoxNO,sUnit FROM SHIPTEMPA
GROUP BY
sStoreOutNo,tStoreOutTime,sCustomerShortName,sOrderNo,sPatternName
,sMaterialCode,sDtlCustomerOrder,sSPUNo,sCustomerBoxNO,sUnit
DECLARE @sMaterialCode VARCHAR(20)
DECLARE @sCustomerBoxNo VARCHAR(20)
DECLARE @sOrderNo VARCHAR(20)
DECLARE @sColorShade VARCHAR(20)
DECLARE sCustomerBoxNo_VIEW CURSOR FOR
SELECT DISTINCT sOrderNo, sMaterialCode, sCustomerBoxNo,sColorShade FROM SHIPTEMPB
OPEN sCustomerBoxNo_VIEW
FETCH NEXT FROM sCustomerBoxNo_VIEW
INTO @sorderNo,@sMaterialCode, @sCustomerBoxNo,@sColorShade
WHILE @@FETCH_STATUS=0
BEGIN
DECLARE @sSqlText VARCHAR(8000)
DEClare @field VARCHAR(50)
DECLARE @j INT
DECLARE @i INT
SET @i=0
SET @field=''
SET @j=0
INSERT SHIPTEMPD
SELECT * FROM SHIPTEMPB
WHERE sOrderNo=@sOrderNo AND sMaterialCode=@sMaterialCode AND sCustomerBoxNo=@sCustomerBoxNo and sColorShade=@sColorShade
--当前一个包的卷数
SELECT @j=count(*) FROM SHIPTEMPD
SELECT @i=15-@j
WHILE 1<=@i
BEGIN
SET @field =@field+',0'
SET @i=@i-1
END
SELECT @sSqlText= ' INSERT INTO textreport SELECT sOrderNo,sMaterialCode ,sCustomerBoxNo,sColorShade '
SELECT @sSqlText=@sSqlText+', CASE nStoreOutQty WHEN '''+replace(nStoreOutQty,'''','''''')+''' THEN nStoreOutQty
ELSE 0 END AS '''+replace(nStoreOutQty,'''','''''')+''''
FROM SHIPTEMPD
SET @sSqlText=@sSqlText+ @field+' ,'+cast(@j as varchar(30))+''+' FROM SHIPTEMPD GROUP BY sOrderNo,sMaterialCode ,sCustomerBoxNo,nStoreOutQty,sColorShade '
EXEC(@sSqlText)
DELETE SHIPTEMPD
FETCH NEXT FROM sCustomerBoxNo_VIEW
INTO @sorderNo ,@sMaterialCode, @sCustomerBoxNo,@sColorShade
END
CLOSE sCustomerBoxNo_VIEW
DEALLOCATE sCustomerBoxNo_VIEW
SELECT uIden=newID(), sStoreOutNo,tStoreOutTime,sCustomerShortName,sPatternName
,sDtlCustomerOrder,sSPUNo, a.sorderno,a.sMaterialCode,a.sCustomerBoxNo,a.sColorShade
,case sum(qty1) when 0 then null else sum(qty1) end AS No1
,case sum(qty2) when 0 then null else sum(qty2) end AS No2
,case sum(qty3) when 0 then null else sum(qty3) end AS No3
,case sum(qty4) when 0 then null else sum(qty4) end AS No4
,case sum(qty5) when 0 then null else sum(qty5) end AS No5
,case sum(qty6) when 0 then null else sum(qty6) end AS No6
,case sum(qty7) when 0 then null else sum(qty7) end AS No7
,case sum(qty8) when 0 then null else sum(qty8) end AS No8
,case sum(qty9) when 0 then null else sum(qty9) end AS No9
,case sum(qty10) when 0 then null else sum(qty10) end AS No10
,case sum(qty11) when 0 then null else sum(qty11) end AS No11
,case sum(qty12) when 0 then null else sum(qty12) end AS No12
,case sum(qty13) when 0 then null else sum(qty13) end AS No13
,case sum(qty14) when 0 then null else sum(qty14) end AS No14
,case sum(qty15) when 0 then null else sum(qty15) end AS No15
,SUM(nTotal)AS nTotal,nTotalSKU,b.sUnit
FROM textreport a
INNER JOIN SHIPTEMPC b
ON a.sorderno=b.sorderno AND a.smaterialcode=b.smaterialcode AND a.sCustomerBoxNo=b.sCustomerBoxNo
GROUP BY sStoreOutNo,tStoreOutTime,sCustomerShortName,sPatternName
,sDtlCustomerOrder,sSPUNo, a.sorderno,a.sMaterialCode,a.sCustomerBoxNo,nTotalSKU,b.sUnit,a.sColorShade
DELETE SHIPTEMPA
DELETE SHIPTEMPB
DELETE SHIPTEMPC
gaoyan582 2014-09-20
- 打赏
- 举报
我的情况是公司升级了ERP服务器，新服务器装的是2008，把原服务器上整个备份还原到新服务器上。 语句都是一样的。 今天ERP就有几个查询虎出现以上错误。 问问是不是SQL 2000和2008会有兼容错误啊，在2000上能执行的语句，在2008上就报错。
hleb231 2014-09-20
- 打赏
- 举报
比较一下2008表内字段名和2000表内字段名 ，
检查你的语句，字段不是搞错了。
