October 7, 2009 at 11:33 am
I'm getting this strange error in SQL Server. We are migrate data from one database to another and we are calling SPL's from SSIS packages. I've been recieving an error in the SSIs package so I ran the procedure from with SQL Server and recieved the following error
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns
This is the portion of code, the DRNO and ProvID are declared earlier in the SPL
BEGIN
DECLARE @LocationID int
DECLARE db_cursor CURSOR STATIC LOCAL FOR
SELECT DISTINCT [location_id] FROM [emr].[HPSITE].[LOCATION] l
WHERE
EXISTS (SELECT 1 FROM [DMZ].[conv].cac_chrt_header cch WITH (NOLOCK)
WHERE cch.drno = @DRNO
AND cch.faccode = l.[PMS_LOCATIONID])
or EXISTS (SELECT 1 FROM [DMZ].[Medications].PatientMedications pm WITH (NOLOCK)
WHERE pm.drno = @DRNO
AND pm.faccode = l.[PMS_LOCATIONID])
or EXISTS (SELECT 1 FROM [DMZ].[Immunizations].PatientImmunizations pimm WITH (NOLOCK)
WHERE pimm.drno2 = @DRNO
AND pimm.faccode = l.[PMS_LOCATIONID])
or EXISTS (SELECT 1 FROM [DMZ].[conv].cac_laborders cl WITH (NOLOCK)
WHERE cl.drordering = @DRNO
AND cl.fachome = l.[PMS_LOCATIONID])
OPEN db_cursor
FETCH db_cursor INTO @LocationID
WHILE @@FETCH_STATUS = 0
BEGIN
exec EMR.HPSITE.CAREGIVER_SaveLocation
@aCaregiverId=@ProvID,
@aLocationId=@LocationID,
@aDefaultFlag=0,
@aAuthorId=1
FETCH db_cursor INTO @LocationID
END
CLOSE db_cursor
DEALLOCATE db_cursor
END
If I run this code outside if the SPL it works, only from within the SPL I'm getting this issue.
October 7, 2009 at 11:49 am
It's a petty you didn't post the full sproc.
- Are you reusing cursor names ??
- what happens in sproc EMR.HPSITE.CAREGIVER_SaveLocation ?
Is it also using a cursor ?
What's that cursors name ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 7, 2009 at 3:01 pm
It appears that db_cursor is already opened with more than 1 output column names before executing query provided.
You will need to close db_cursor just before query block that you provided in post to resolve this.
[font="Verdana"]--www.sqlvillage.com[/size][/font]
October 7, 2009 at 3:30 pm
The error message you're referring to will show you the line in your proc that causes the error (even when calling it from within a proc):
Msg 16924, Level 16, State 1, Procedure myproc, Line 25
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.
Check the section the error message point at.
October 7, 2009 at 3:35 pm
Thanks for all the suggestions. I found the problem. I though it was this code I put in but it was code already in the SPL up higher in the code. Another cursor existed and the 1st fetch had 3 rows and the fetch next was only 2 rows. I'm just suprised it wasn't noticed before I added this code logic.
October 12, 2009 at 1:02 am
Thank you for the feedback.
That kind of mishaps happen to all of us every once in a while 😉
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 28, 2009 at 11:32 am
lmu92 (10/7/2009)
The error message you're referring to will show you the line in your proc that causes the error (even when calling it from within a proc):Msg 16924, Level 16, State 1, Procedure myproc, Line 25
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.
Check the section the error message point at.
Have the same error for a different reason, although I haven't found it yet. In my "short" stored procedure, it says the error is at line 340. When you modify the trigger, line 340 is in the middle of an insert statement. It isn't anything related to a cursor. (Also, commented out the insert and it didn't change the error.)
So, while having the line number is nice, in my case of a trigger losing it, it doesn't point at a place where the error is.
Terry
October 28, 2009 at 12:54 pm
steyaert (10/28/2009)
Have the same error for a different reason, although I haven't found it yet. In my "short" stored procedure, it says the error is at line 340. When you modify the trigger, line 340 is in the middle of an insert statement. It isn't anything related to a cursor. (Also, commented out the insert and it didn't change the error.)So, while having the line number is nice, in my case of a trigger losing it, it doesn't point at a place where the error is.
Terry
Did you try to run the code of the proc in question separately?
If yes, do you still get the error message?
If yes again: Did you try to just double click on the message in the Messages Window?
This usually will point directly at the section in question in the corresponding query window. This section would either be the statement itself or a the begin of the related sql code block (for example, if the error is within a CTE section it will point to the first CTE line). At least it can be used as an indicator for the related code section.
October 28, 2009 at 3:26 pm
No, I did not run the code separately. As a trigger, the "deleted" table would not exist, so the code would not function successfully as a stand-alone.
I did find the issue. Someone else had attempted to update the trigger. At the top is an creation and open of the cursor, then a fetch, and a while loop with another fetch at the end. In between are some inserts (yes, inserts on a delete trigger) and updates. Turns out the bottom fetch (about line 365, IIRC, not 340 as reported by SQL) didn't match the initial fetch. One column had been missed. Pain to find with 40 columns listed, but findable.
Wasn't really looking for help, just commenting that line numbers aren't necessarily correct when being reported.
Terry
October 28, 2009 at 3:46 pm
steyaert (10/28/2009)
No, I did not run the code separately. As a trigger, the "deleted" table would not exist, so the code would not function successfully as a stand-alone.I did find the issue. Someone else had attempted to update the trigger. At the top is an creation and open of the cursor, then a fetch, and a while loop with another fetch at the end. In between are some inserts (yes, inserts on a delete trigger) and updates. Turns out the bottom fetch (about line 365, IIRC, not 340 as reported by SQL) didn't match the initial fetch. One column had been missed. Pain to find with 40 columns listed, but findable.
Wasn't really looking for help, just commenting that line numbers aren't necessarily correct when being reported.
Terry
If I may, I'd suggest looking into rewriting that trigger to use set-based processing.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply