[thelist] MSSQL stored procedure - did my move break something?

Joel D Canfield joel at streamliine.com
Thu Feb 8 16:18:07 CST 2007


using a stored procedure which ran fine on our servers at the last
company, and I'm migrating it to my own servers now that aforementioned
company is closed.

I'm not aware that the stored procedure has changed, nor how I'm using
it, but it keeps returning the error 'you have more items than columns
in your insert statement' - I've checked, and as far as I can see, I
don't. It returns the same error whether run from the ASP page or Query
Analyzer.

Here are the SP and the code I had spewed back to the page (you'll note
what appear to be endless multiple single quotes; it's necessary because
of how the text strings are assembled into the input for the SP, but it
was working before, unless I've added or deleted one)

@Columns varchar(4000) 
, at Values text
, at WorkOrderNumber int = NULL OUTPUT 
AS
DECLARE 
  @WO2 varchar(64),
  @WOID int
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
UPDATE LASTHD SET LAST = LAST + 1
SELECT @WorkOrderNumber = LAST
FROM LASTHD
UPDATE LASTWOID SET LASTWOID = LASTWOID + 1
SELECT @WOID = LASTWOID FROM LASTWOID
SELECT @WO2 = convert(varchar(15), @WorkOrderNumber) + ',' +
convert(varchar(15), @WOID) + ','
+ convert(varchar(15), @WOID) + ',' 
EXEC ('INSERT  INTO TASKS ' + @Columns 
     + ' VALUES (' + @WO2 + @Values+ ')' )
COMMIT TRANSACTION
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET NOCOUNT OFF

---------------
and, according to the page, this is the query it's trying to run:
---------------
DECLARE @RC int; 
DECLARE @Columns varchar(4000); 
DECLARE @WorkOrderNumber int;
EXEC @RC = spAddTask '(WO_NUM, PARENTWOID, WOID, TASK, TYPE, REQUEST,
REQDATE, OPENBY, OPENDATE, MODIBY, MODIDATE, CLSDBY, CLSDDATE,
ELAPSETIME, ELAPSEMIN, PRIORITY, DUEDATE, RESPONS, ASSNDATE, COMPLETED,
HOURS, RATE, CHARGE, WS_NUM, DEPT_NUM, DEPT, PHONE, PHONE_EXT, LOCATION,
DESCRIPT, WO_TEXT1, WO_TEXT2, WO_TEXT3, WO_TEXT4, WO_TEXT5, WO_TEXT6,
WO_DATE1, WO_DATE2, WO_NUM1, WO_INT1, NOTE, FT, COMPFLAG, STATUS,
AGENTDATE, AGENTLEVEL, AWS_NUM, LOOKUP1, LOOKUP2, GUIDO, EMAILADDR,
USERID, WOTYPE2, WOTYPE3)', '''testing request form 2:05 20070207'',
''Other'', ''Joel Canfield'', ''2/7/2007 3:10:44 PM'', ''the Web Form'',
''2/7/2007 3:10:44 PM'', ''the Web Form'', ''2/7/2007 3:10:44 PM'',
NULL, NULL, NULL, NULL, ''2 - Medium'', ''2/12/2007'', ''HelpDesk'',
''2/7/2007 3:10:44 PM'', NULL, NULL, ''0'', ''0'', NULL, 5,
''Administration'', NULL, NULL, NULL, '' testing testing 1 2 3'',
''Production'', ''Request'', NULL, NULL, ''Average'', NULL, NULL, NULL,
0, 0, '''', ''0'', NULL, ''Pending'', NULL, NULL, NULL, ''01 -
Unassigned'', NULL, NULL, NULL, NULL, '''', ''''', @WorkOrderNumber
OUTPUT

Microsoft OLE DB Provider for SQL Server error '80040e14'

There are more columns in the INSERT statement than values specified in
the VALUES clause. The number of values in the VALUES clause must match
the number of columns specified in the INSERT statement.

/ii/demo/helpdesk/helpdeskresponse.asp, line 322 
----------------------------
I'm seriously considering starting the SP over from scratch, since this
was adapted from a third-party tool, not written by me, but I'd really
rather just get it working for now and fuss with tidiness later.

thanks

joel



More information about the thelist mailing list