October 30, 2007 at 4:43 pm
I have a stored proc that gathers XML data and inserts into tables. I have been using this with no problem for at least 6 months. I have not changed any syntax in the sp, but now if I "check syntax", I get the following error:
"Insert Error: Column name or number of supplied values does not match table definition".
I added two columns to one table about 2 months ago, and added them to my sp, and it has worked continuously fine until now.
Anyone got any ideas what might cause this?
Thanks in advance!
CREATE PROCEDURE dbo.sp_Insert_Projects_Openxml
@strXML ntext, @strLoc varchar (10), @retMsg varchar(150)= NULL OUTPUT, @retCode int = NULL OUTPUT
AS
SET NOCOUNT ON
DECLARE @iDoc int
BEGIN TRAN
DELETE FROM _allproj WHERE ABC_Office =@strLoc
EXEC sp_xml_preparedocument @iDoc OUTPUT,@strXML
INSERT INTO _allproj(ProjID,Yr,Title,Bud_Cat,Bud_Cat2, Bud_Cat3,RPM,PADM,ABC,Team,Proj_grp,Prg_Goal,Class,Notes,Last_Chg,RevCalc,ABC_Office,Calc_True)
(SELECT * FROM OpenXML(@iDoc, 'dataroot/temp_allproj',3) WITH _allproj )
IF @@ERROR <> 0 GOTO PROBLEM
INSERT INTO _allLabor(ProjID,Yr,Pen,Days)
(SELECT * FROM OpenXML(@iDoc, 'dataroot/temp_AllLabor',3)WITH _allLabor)
IF @@ERROR <> 0 GOTO PROBLEM
INSERT INTO _allrev(ProjID,Yr,FunderID,FndrName,RevCode,Revenue)
(SELECT * FROM OpenXML(@iDoc, 'dataroot/temp_AllRev',3) WITH _allrev)
IF @@ERROR <> 0 GOTO PROBLEM
INSERT INTO _allLines(ProjID,Yr, Acct,LineTitle,Units,Price,Amount)
(SELECT * FROM OpenXML(@iDoc, 'dataroot/qry_AllLines_Value',3) WITH _allLines)
IF @@ERROR <> 0 GOTO PROBLEM
IF @@ROWCOUNT = 0
BEGIN
COMMIT TRANSACTION
SELECT @RetMsg = 'UPDATE COMPLETE'
EXEC sp_xml_removedocument @iDoc
END
ELSE
BEGIN
ROLLBACK TRANSACTION
SELECT @RetMsg = 'UPDATE NOT COMPLETED '
EXEC sp_xml_removedocument @iDoc
END
RETURN
PROBLEM:
BEGIN
ROLLBACK TRANSACTION
SELECT @RetCode = @@ERROR
EXEC sp_xml_removedocument @iDoc
END
GO
October 30, 2007 at 4:56 pm
Weird, I'm not sure what you mean by Check syntax. When I Copy your procedure into a query window and parse it, it looks fine.
But I'm sure your getting your error in your poorly written insert statements.
(SELECT * FROM OpenXML(@iDoc, 'dataroot/temp_allproj',3) WITH _allproj )
INSERT INTO _allLabor(ProjID,Yr,Pen,Days)
(SELECT * FROM OpenXML(@iDoc, 'dataroot/temp_AllLabor',3)WITH _allLabor)
INSERT INTO _allrev(ProjID,Yr,FunderID,FndrName,RevCode,Revenue)
(SELECT * FROM OpenXML(@iDoc, 'dataroot/temp_AllRev',3) WITH _allrev)
INSERT INTO _allLines(ProjID,Yr, Acct,LineTitle,Units,Price,Amount)
(SELECT * FROM OpenXML(@iDoc, 'dataroot/qry_AllLines_Value',3) WITH _allLines)
I'll bet if you check your select * is not returning the correct number of columns. So it is failing because of the XML document your passing in.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply