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

[Ken Moore]

Hi all.

Joel D Canfield has problems with this:

EXEC ('INSERT  INTO TASKS ' + @Columns
     + ' VALUES (' + @WO2 + @Values+ ')' )

Giving this error:
>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.

Assuming that you have counted the columns and that the fields match in 
number and are compatible in type, I have always found the problem to be 
data driven. Usually that means that there is a single or double quote that 
is is being interpreted as part of the command syntax rather than as part of 
the data.

For example a data item is as follows: Michael "Mike" Smith

This is hard to find because the SQL INSERT command does group operations 
rather than row by row by which you could discover the exact item that 
caused the error. Rather, the entire command will fail or work. Test the 
command on one item of known data and see if that works.

Ken

>
>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
>--
>
>* * Please support the community that supports you.  * *
>http://evolt.org/help_support_evolt/
>
>For unsubscribe and other options, including the Tip Harvester
>and archives of thelist go to: http://lists.evolt.org
>Workers of the Web, evolt !

_________________________________________________________________
Don’t miss your chance to WIN 10 hours of private jet travel from Microsoft 
Office Live http://clk.atdmt.com/MRT/go/mcrssaub0540002499mrt/direct/01/