October 16, 2007 at 11:23 am
Hello -
This is my first attempt at a SP. I need to import external data to another table and also insert a sequence key from another table through another SP. I'm getting this wrong. The sequence key is updating correctly, but the insert is repeating the first line of the import table. I've tried a number of things, but I'm getting nowhere. I'm also getting the following message:
/*ERROR Server: Msg 16950, Level 16, State 2, Procedure PROC_WBGRDEMP, Line 142
The variable '@cSource' does not currently have a cursor allocated to it.
Server: Msg 16950, Level 16, State 2, Procedure PROC_WBGRDEMP, Line 143
The variable '@cSource' does not currently have a cursor allocated to it.
*/
Any help would be appreciated, and if there is a better way, please let me know.
CREATE PROCEDURE PROC_WBGRDEMP
AS
DECLARE
@ADDBY varchar(30),
@ADDDTTM datetime,
@CLASSDT datetime,
@CLGROUP varchar(8),
@COMMENTS varchar(100),
@CONTACTHRS float,
@EMPID varchar(12),
@GRIDKEY INT,
@MODBY varchar(30),
@MODDTTM datetime,
@NOTIFDATE datetime,
@QUALDATE datetime,
@QUALNO varchar(30),
@QUALTRAIN varchar(5),
@RENEWDATE datetime,
@TRNGHRS float,
@TRAINER varchar(1),
@NORENEW varchar(1)
BEGIN
DECLARE @cSource Cursor
Set @cSource = Cursor For
(Select *
From training$)
Begin
OPEN @cSource
Fetch Next From @cSource
INTO
@ADDBY,
@ADDDTTM,
@CLASSDT,
@CLGROUP,
@COMMENTS,
@CONTACTHRS,
@EMPID,
@GRIDKEY,
@MODBY,
@MODDTTM,
@NOTIFDATE,
@QUALDATE,
@QUALNO,
@QUALTRAIN,
@RENEWDATE,
@TRNGHRS,
@TRAINER,
@NORENEW
While @@Fetch_status <> -1 -- (I've also tried 0 here)
begin
Exec SequenceValue 346, @Gridkey Out
insert into wbgrdemp
(
ADDBY,
ADDDTTM,
CLASSDT,
CLGROUP,
COMMENTS,
CONTACTHRS,
EMPID,
GRIDKEY,
MODBY,
MODDTTM,
NOTIFDATE,
QUALDATE,
QUALNO,
QUALTRAIN,
RENEWDATE,
TRNGHRS,
TRAINER,
NORENEW
)
VALUES
(
'PROC_WBGRDEMP',
GETDATE(),
@CLASSDT,
@CLGROUP,
@COMMENTS,
@CONTACTHRS,
@EMPID ,
@GRIDKEY,
@MODBY,
@MODDTTM,
@NOTIFDATE,
@QUALDATE,
@QUALNO,
@QUALTRAIN,
@RENEWDATE,
@TRNGHRS,
@TRAINER,
@NORENEW
)
Close @cSource
Deallocate @cSource
End
End
End
Thanks very much for your time
October 16, 2007 at 11:28 am
Kenena,
What exactly does the procedure "SequenceValue 346, @Gridkey Out" do? I would assume this is giving you a key to use, but what else does it do?
The reason I ask is that you can most like accomplish this task "set-based" and not have to use a cursor, but it really depends on what goes on in SequenceValue.
And just to make sure, you are using SQL 2000 and not 2005 right?
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 16, 2007 at 11:34 am
Hi Jason -
Thanks for your reply. My application has a lot of these sequence tables. This SP can be used to call the next sequence from whichever table is needed. I will be using this format for a number of similar processes using different tables. However, as far as I can see, all it does is get the next number and update the sequence table +1.
Yes, SQL 2000. I saw how this SP was being used in another SP and have tried to emulate it, but it is actually quite different from what I am trying to do, so I'm not having much luck understanding the whole process. Never used a cursor before and would be happy to do without.
Future SPs coming after I figure this out will involve multiple sequence tables.
Thanks -
Kenena
October 16, 2007 at 11:51 am
Well, my suggestion would then be to alter your table and change the key to an IDENTITY column. Tie-ing yourself to a procedure that gets the next key for a given table and then updates a value somewhere is pretty darned redundant. SQL handles this just fine all by it's lonesome. And I'm pretty sure that changing the column to IDENTITY won't be a problem and SQL will automatically pick up the next key. This is all under the assumption that your keys are sequential and integers.
Once you do that, you can simply use one statement:
INSERT into wbgrdemp
SELECT
ADDBY,
ADDDTTM,
CLASSDT,
CLGROUP,
COMMENTS,
CONTACTHRS,
EMPID,
GRIDKEY,
MODBY,
MODDTTM,
NOTIFDATE,
QUALDATE,
QUALNO,
QUALTRAIN,
RENEWDATE,
TRNGHRS,
TRAINER,
NORENEW
FROM training$
Now, that being said. How are you getting the data into the training$ table? DTS? If that's the case then you can modify the DTS package to insert into your table directly.
IF you do decide to go this route, test it all out in a development environment and I'd feel safer if someone chimed in on the "altering the column to be an identity not causing a problem."
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 16, 2007 at 12:03 pm
Thanks, Jason.
That would be fine in the case of this particular table, as it was added by me as allowed by the application, but the other application tables cannot be changed by me and the sequence tables are used by the application. There are over 100 of them. We are attempting to gather field data to then insert into the DB, and the insert through DTS or SPs will need to get the next sequence number from several tables and ultimately update several tables.
This first SP is my starting point to try to understand how to do this. As I can't change the process for the sequence insert, is there something wrong in the sequence of the entries in my SP? Why am I retrieving only the first row of my import table but getting it over an over again?
Thanks again.
October 16, 2007 at 12:12 pm
1. Move your "FETCH NEXT ... INTO" just after your "BEGIN" in the WHILE loop.
2. You might also read up on Triggers. BEFORE UPDATE triggers would be another way to get this done.
As always, the best solution for your situation is dependant upon many variables, and ultimately you'll need to test and decide on your own.
Hope this all helps.... 😛
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 16, 2007 at 12:53 pm
Hello Jason -
I made the suggested change, but I am still getting multiple entries of the first row. Any other suggestions on that?
Thanks,
Kenena
October 16, 2007 at 1:02 pm
Are you positive that the data in the "first" row is unique?
What happens when you run the query commenting out the insert?
How many entries for the first row?
Now the bad news, I'm off of here for a few hours, so hopefully someone will take over.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 16, 2007 at 1:17 pm
The table TRAINING$ has only 60 rows, each has an employee id identifier and they happen to be unique here.
Thanks for your help, I appreciate it.
October 16, 2007 at 2:08 pm
I think what Jason meant was that you needed to add another fetch inside your while loop and close and deallocate outside the while loop. Try this
declare cSource cursor for Select * From training$
open cSource
fetch next from cSource into
@ADDBY,
@ADDDTTM,
@CLASSDT,
@CLGROUP,
@COMMENTS,
@CONTACTHRS,
@EMPID,
@GRIDKEY,
@MODBY,
@MODDTTM,
@NOTIFDATE,
@QUALDATE,
@QUALNO,
@QUALTRAIN,
@RENEWDATE,
@TRNGHRS,
@TRAINER,
@NORENEW
While @@Fetch_status <> 0
begin
Exec SequenceValue 346, @Gridkey Out
insert into wbgrdemp
(
ADDBY,
ADDDTTM,
CLASSDT,
CLGROUP,
COMMENTS,
CONTACTHRS,
EMPID,
GRIDKEY,
MODBY,
MODDTTM,
NOTIFDATE,
QUALDATE,
QUALNO,
QUALTRAIN,
RENEWDATE,
TRNGHRS,
TRAINER,
NORENEW
)
VALUES
(
'PROC_WBGRDEMP',
GETDATE(),
@CLASSDT,
@CLGROUP,
@COMMENTS,
@CONTACTHRS,
@EMPID ,
@GRIDKEY,
@MODBY,
@MODDTTM,
@NOTIFDATE,
@QUALDATE,
@QUALNO,
@QUALTRAIN,
@RENEWDATE,
@TRNGHRS,
@TRAINER,
@NORENEW
)
fetch next from cSource into
@ADDBY,
@ADDDTTM,
@CLASSDT,
@CLGROUP,
@COMMENTS,
@CONTACTHRS,
@EMPID,
@GRIDKEY,
@MODBY,
@MODDTTM,
@NOTIFDATE,
@QUALDATE,
@QUALNO,
@QUALTRAIN,
@RENEWDATE,
@TRNGHRS,
@TRAINER,
@NORENEW
end
Close cSource
Deallocate cSource
October 16, 2007 at 2:11 pm
October 16, 2007 at 2:19 pm
Jeff Gray just posted this on another topic which might help you out as well.
Another good trick for cursors:
Declare cursor foo....
Declare @someVariable INT
open foo
While (1=1)
BEGIN
Fetch foo into @someVariable
IF @@Fetch_Status <> 0 BREAK -- no more data
--do something here
END
This eliminates the need for two separate fetch statements.
October 16, 2007 at 2:48 pm
Thanks for the help, mrpolecat. I made the suggested changes and after changing to While @@Fetch_status <> -1, it ran successfully. I can't tell you what this means to me. Thanks so much.
Kenena
October 16, 2007 at 2:49 pm
Nothing you've posted so far justifies using a cursor. Looks to me that you need to update the temporary table with the gridkey value, and then run an insert to wbgrdemp at that point.
What's the contents of the stored proc outputting @gridkey?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 16, 2007 at 2:49 pm
Hello Jason -
mrpolecate was able to add to the assist you gave me this morning, and I just want to say thanks so much for your time. This will be very helpful to me.
Kenena
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply