April 16, 2012 at 10:44 am
I am not sure why this is happening, but I am trying to execute this store procedure, and I am receiving the error:
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.
The strange thing is, I have the exact same number of variables!
This is the stored procedure... Am I missing something?
USE [TransactionalData]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[usp_next_day_del]
AS
DECLARE
@xml varchar(MAX)
,@value int
,@CUST_CD varchar(20)
,@TRK_NUM varchar(20)
,@EMAIL_ADDR varchar(40)
,@DELIVERY_DATE datetime
,@EST_ARV_TIME int
,@EST_ARRV_TIME_START varchar(6)
,@EST_ARRV_TIME_END varchar(6)
,@DEL_DOC_NUM varchar(20)
,@STOP_NUMBER int
,@DELIVERY_SEQ_NUMBER int
,@ITM_CD varchar(10)
,@EXT_QTY int
,@ORD_TP_CD varchar(10)
,@SHIP_TO_F_NAME varchar(40)
,@SHIP_TO_L_NAME varchar(40)
,@SHIP_TO_ADDR1 varchar(40)
,@SHIP_TO_ADDR2 varchar(40)
,@SHIP_TO_CITY varchar(40)
,@SHIP_TO_ST_CD varchar(5)
,@SHIP_TO_ZIP_CD varchar(10)
,@SHIP_TO_H_PHONE varchar(20)
,@SHIP_TO_B_PHONE varchar(20)
,@VSN varchar(40)
,@DES varchar(40)
,@body varchar(MAX);
DECLARE Contacts CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
distinct(CUST_CD)
,TRK_NUM
,EMAIL_ADDR
,DELIVERY_DATE
,EST_ARV_TIME
,EST_ARRV_TIME_START
,EST_ARRV_TIME_END
,DEL_DOC_NUM
,STOP_NUMBER
,DELIVERY_SEQ_NUMBER
,ITM_CD
,EXT_QTY
,ORD_TP_CD
,SHIP_TO_F_NAME
,SHIP_TO_L_NAME
,SHIP_TO_ADDR1
,SHIP_TO_ADDR2
,SHIP_TO_CITY
,SHIP_TO_ST_CD
,SHIP_TO_ZIP_CD
,SHIP_TO_H_PHONE
,SHIP_TO_B_PHONE
,VSN
DES
FROM dbo.NEXT_DAY_DELIVERY
WHERE dbo.emailFilter(EMAIL_ADDR) = 0
OPEN Contacts;
WHILE 1 = 1
BEGIN
FETCH NEXT FROM Contacts INTO
@CUST_CD,
@TRK_NUM,
@EMAIL_ADDR,
@DELIVERY_DATE,
@EST_ARV_TIME,
@EST_ARRV_TIME_START,
@EST_ARRV_TIME_END,
@DEL_DOC_NUM,
@STOP_NUMBER,
@DELIVERY_SEQ_NUMBER,
@ITM_CD,
@EXT_QTY,
@ORD_TP_CD,
@SHIP_TO_F_NAME,
@SHIP_TO_L_NAME,
@SHIP_TO_ADDR1,
@SHIP_TO_ADDR2,
@SHIP_TO_CITY,
@SHIP_TO_ST_CD,
@SHIP_TO_ZIP_CD,
@SHIP_TO_H_PHONE,
@SHIP_TO_B_PHONE,
@VSN,
@DES
IF @@FETCH_STATUS = -1 BREAK;
SET @value = (select COUNT(itm_cd) as itm from TransactionalData.dbo.NEXT_DAY_DELIVERY where cust_cd = @cust_cd)
IF @value > 0
BEGIN
SET @xml = CAST(( SELECT itm_cd AS 'td','',vsn AS 'td','',des AS 'td','',ext_qty AS 'td'
FROM TransactionalData.dbo.NEXT_DAY_DELIVERY WHERE cust_cd = @cust_cd GROUP BY cust_cd, email_addr, itm_cd, vsn, des, ext_qty, del_doc_num order by del_doc_num FOR XML PATH('tr'), ELEMENTS ) AS varchar(MAX))
SET @body = 'email body'
EXECUTE msdb.dbo.sp_send_dbmail
@subject = 'Your Current Order Status',
--@recipients = @email,
@recipients = 'donato@somemailplace.com',
@body_format = 'HTML',
@body = @body,
@profile_name ='SQL Server Agent';
END
END
CLOSE Contacts;
DEALLOCATE Contacts;
RETURN;
I have no idea why it would throw that error...
April 16, 2012 at 10:47 am
Missing a comma!
DECLARE Contacts CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
distinct(CUST_CD)
,TRK_NUM
,EMAIL_ADDR
,DELIVERY_DATE
,EST_ARV_TIME
,EST_ARRV_TIME_START
,EST_ARRV_TIME_END
,DEL_DOC_NUM
,STOP_NUMBER
,DELIVERY_SEQ_NUMBER
,ITM_CD
,EXT_QTY
,ORD_TP_CD
,SHIP_TO_F_NAME
,SHIP_TO_L_NAME
,SHIP_TO_ADDR1
,SHIP_TO_ADDR2
,SHIP_TO_CITY
,SHIP_TO_ST_CD
,SHIP_TO_ZIP_CD
,SHIP_TO_H_PHONE
,SHIP_TO_B_PHONE
,VSN
, DES
FROM dbo.NEXT_DAY_DELIVERY
WHERE dbo.emailFilter(EMAIL_ADDR) = 0
April 16, 2012 at 10:54 am
Lynn Pettis (4/16/2012)
Missing a comma! ...
And this is when I go outside and walk in front of oncoming traffic... If I may, I would like to use the fact that I am on a pretty severe lack of sleep, three young girls, I am sick and basically work three jobs. So I am tired. Very tired.
But then again, I am missing a comma... Although, the SQL parser didn't catch that. Hmm...
Thank you very VERY much!
April 16, 2012 at 10:57 am
donato1026 (4/16/2012)
Lynn Pettis (4/16/2012)
Missing a comma! ...And this is when I go outside and walk in front of oncoming traffic... If I may, I would like to use the fact that I am on a pretty severe lack of sleep, three young girls, I am sick and basically work three jobs. So I am tired. Very tired.
But then again, I am missing a comma... Although, the SQL parser didn't catch that. Hmm...
Thank you very VERY much!
It took DES as the column alias for the column that preceded it. Not an error in terms of the parser.
Understand the tired, three girls (although my oldest is married and on here own now).
April 16, 2012 at 11:03 am
donato1026 (4/16/2012)
But then again, I am missing a comma... Although, the SQL parser didn't catch that. Hmm...
No, because this is completely valid (run it and you'll see what happens)
SELECT 1
'a',
2
'b'
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 16, 2012 at 2:20 pm
I understand now... thank you.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply