Stored Procedure Insert not inserting... 1

[deepsheep]

I have a table with column entry for every hour (TableA) and I'm converting it to a table with a row for every hour (TableB). I'm creating a stored procedure to help, beacuse every day there will be a new entry in TableA to convert to the TableB. I've chosen to do it with 24 insert statements, one after another, with a cursor reading out of TableA.

Now the problem is that when I exceute the stored procedure, the inserts don't insert. But I don't get any errors either. It does read the data, gets to the right code, completes the code, and go on to any other entries in the cursor, but no inserts. If I run the exact same code in query, it works fine and does all the inserts.

I'm using mssql 2005 express for testing purposes but will eventually move to 2005 Standard.

 

[gmmastros]

Can you show some sample data and expected results? It'll make it easier for us to help you.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[deepsheep]

Sorry it took so long... Couldn't get to my code until now.
here's the stored procedure. It's pretty straight forward...

ALTER PROCEDURE [dbo].[ScaleSchedule_Contractor_Prep]
	-- Add the parameters for the stored procedure here
@currentdate as datetime 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

declare  
--@currentdate as datetime,
@enddate as datetime,
@equipmentID nchar(20),
@loaddate datetime,
@equipmeintNum int,
@loadtype int,
@minutesperload int,
@notes nchar(50),
@cycletime int,
@H0 int,@H1 int,@H2 int,@H3 int,@H4 int,@H5 int,@H6 int,@H7 int,@H8 int,@H9 int,@H10 int,
@H11 int,@H12 int,@H13 int,@H14 int,@H15 int,@H16 int,@H17 int,@H18 int,@H19 int,@H20 int,
@H21 int,@H22 int,@H23 int

--set @currentdate='Apr  6 2009 12:00AM'
print @currentdate
set @enddate=dateadd(d,1,@currentdate)

--  populate the loader hourly thing with data
declare HourlyEquip cursor for 
select [LoadDate] ,[EquipmentID],e.[LoadType],notes,[H0],[H1],[H2],[H3],[H4],[H5],[H6],[H7],[H8],[H9],[H10],[H11],[H12],
[H13],[H14],[H15],[H16],[H17],[H18],[H19],[H20],[H21],[H22],[H23],e.isn, minutesperload from hourlyschedule hs inner join equipment e on e.loadername=hs.equipmentid and e.loadtype=hs.loadtype
where loaddate between @currentdate and @enddate

OPEN HourlyEquip
FETCH NEXT FROM HourlyEquip
INTO @loaddate, @equipmentID, @loadtype,@notes , @H0  ,@H1  ,@H2  ,@H3  ,@H4  ,@H5  ,@H6  ,@H7  ,@H8  ,@H9  ,@H10  ,
@H11  ,@H12  ,@H13  ,@H14  ,@H15  ,@H16  ,@H17  ,@H18  ,@H19  ,@H20  ,@H21  ,@H22  ,@H23  ,@equipmeintNum,@minutesperload
WHILE @@FETCH_STATUS = 0
BEGIN
set @cycletime=2400
select @cycletime=isnull(cycletime,2400) from mill1.dbo.blockmaster where blockid=@notes and products=@loadtype  
set @notes=ltrim(rtrim(@notes))
print @notes
print @cycletime
print @loadtype

INSERT INTO  [dbo].[LoaderHourly] ([LoaderID],[Product],[Block],[LoadingTime],[Quantiy],[CycleTime])
VALUES(@equipmeintNum,@loadtype,@notes,dateadd(hh,0,@loaddate),@H0,@cycletime)
print 'inserting'
INSERT INTO  [dbo].[LoaderHourly] ([LoaderID],[Product],[Block],[LoadingTime],[Quantiy],[CycleTime])
VALUES(@equipmeintNum,@loadtype,@notes,dateadd(hh,1,@loaddate),@H1,@cycletime)
INSERT INTO  [dbo].[LoaderHourly] ([LoaderID],[Product],[Block],[LoadingTime],[Quantiy],[CycleTime])
VALUES(@equipmeintNum,@loadtype,@notes,dateadd(hh,2,@loaddate),@H2,@cycletime)
INSERT INTO  [dbo].[LoaderHourly] ([LoaderID],[Product],[Block],[LoadingTime],[Quantiy],[CycleTime])
VALUES(@equipmeintNum,@loadtype,@notes,dateadd(hh,3,@loaddate),@H3,@cycletime)
INSERT INTO  [dbo].[LoaderHourly] ([LoaderID],[Product],[Block],[LoadingTime],[Quantiy],[CycleTime])
VALUES(@equipmeintNum,@loadtype,@notes,dateadd(hh,4,@loaddate),@H4,@cycletime)
INSERT INTO  [dbo].[LoaderHourly] ([LoaderID],[Product],[Block],[LoadingTime],[Quantiy],[CycleTime])
VALUES(@equipmeintNum,@loadtype,@notes,dateadd(hh,5,@loaddate),@H5,@cycletime)
INSERT INTO  [dbo].[LoaderHourly] ([LoaderID],[Product],[Block],[LoadingTime],[Quantiy],[CycleTime])
VALUES(@equipmeintNum,@loadtype,@notes,dateadd(hh,6,@loaddate),@H6,@cycletime)
print 'inserting'
INSERT INTO  [dbo].[LoaderHourly] ([LoaderID],[Product],[Block],[LoadingTime],[Quantiy],[CycleTime])
VALUES(@equipmeintNum,@loadtype,@notes,dateadd(hh,7,@loaddate),@H7,@cycletime)
INSERT INTO  [dbo].[LoaderHourly] ([LoaderID],[Product],[Block],[LoadingTime],[Quantiy],[CycleTime])
VALUES(@equipmeintNum,@loadtype,@notes,dateadd(hh,8,@loaddate),@H8,@cycletime)
INSERT INTO  [dbo].[LoaderHourly] ([LoaderID],[Product],[Block],[LoadingTime],[Quantiy],[CycleTime])
VALUES(@equipmeintNum,@loadtype,@notes,dateadd(hh,9,@loaddate),@H9,@cycletime)
INSERT INTO  [dbo].[LoaderHourly] ([LoaderID],[Product],[Block],[LoadingTime],[Quantiy],[CycleTime])
VALUES(@equipmeintNum,@loadtype,@notes,dateadd(hh,10,@loaddate),@H10,@cycletime)
INSERT INTO  [dbo].[LoaderHourly] ([LoaderID],[Product],[Block],[LoadingTime],[Quantiy],[CycleTime])
VALUES(@equipmeintNum,@loadtype,@notes,dateadd(hh,11,@loaddate),@H11,@cycletime)
INSERT INTO  [dbo].[LoaderHourly] ([LoaderID],[Product],[Block],[LoadingTime],[Quantiy],[CycleTime])
VALUES(@equipmeintNum,@loadtype,@notes,dateadd(hh,12,@loaddate),@H12,@cycletime)
INSERT INTO  [dbo].[LoaderHourly] ([LoaderID],[Product],[Block],[LoadingTime],[Quantiy],[CycleTime])
VALUES(@equipmeintNum,@loadtype,@notes,dateadd(hh,13,@loaddate),@H13,@cycletime)
INSERT INTO  [dbo].[LoaderHourly] ([LoaderID],[Product],[Block],[LoadingTime],[Quantiy],[CycleTime])
VALUES(@equipmeintNum,@loadtype,@notes,dateadd(hh,14,@loaddate),@H14,@cycletime)
print 'inserting'
INSERT INTO  [dbo].[LoaderHourly] ([LoaderID],[Product],[Block],[LoadingTime],[Quantiy],[CycleTime])
VALUES(@equipmeintNum,@loadtype,@notes,dateadd(hh,15,@loaddate),@H15,@cycletime)
INSERT INTO  [dbo].[LoaderHourly] ([LoaderID],[Product],[Block],[LoadingTime],[Quantiy],[CycleTime])
VALUES(@equipmeintNum,@loadtype,@notes,dateadd(hh,16,@loaddate),@H16,@cycletime)
INSERT INTO  [dbo].[LoaderHourly] ([LoaderID],[Product],[Block],[LoadingTime],[Quantiy],[CycleTime])
VALUES(@equipmeintNum,@loadtype,@notes,dateadd(hh,17,@loaddate),@H17,@cycletime)
INSERT INTO  [dbo].[LoaderHourly] ([LoaderID],[Product],[Block],[LoadingTime],[Quantiy],[CycleTime])
VALUES(@equipmeintNum,@loadtype,@notes,dateadd(hh,18,@loaddate),@H18,@cycletime)
INSERT INTO  [dbo].[LoaderHourly] ([LoaderID],[Product],[Block],[LoadingTime],[Quantiy],[CycleTime])
VALUES(@equipmeintNum,@loadtype,@notes,dateadd(hh,19,@loaddate),@H19,@cycletime)
INSERT INTO  [dbo].[LoaderHourly] ([LoaderID],[Product],[Block],[LoadingTime],[Quantiy],[CycleTime])
VALUES(@equipmeintNum,@loadtype,@notes,dateadd(hh,20,@loaddate),@H20,@cycletime)
INSERT INTO  [dbo].[LoaderHourly] ([LoaderID],[Product],[Block],[LoadingTime],[Quantiy],[CycleTime])
VALUES(@equipmeintNum,@loadtype,@notes,dateadd(hh,21,@loaddate),@H21,@cycletime)
INSERT INTO  [dbo].[LoaderHourly] ([LoaderID],[Product],[Block],[LoadingTime],[Quantiy],[CycleTime])
VALUES(@equipmeintNum,@loadtype,@notes,dateadd(hh,22,@loaddate),@H22,@cycletime)
INSERT INTO  [dbo].[LoaderHourly] ([LoaderID],[Product],[Block],[LoadingTime],[Quantiy],[CycleTime])
VALUES(@equipmeintNum,@loadtype,@notes,dateadd(hh,23,@loaddate),@H23,@cycletime)

	FETCH NEXT FROM HourlyEquip
INTO @loaddate, @equipmentID, @loadtype,@notes , @H0  ,@H1  ,@H2  ,@H3  ,@H4  ,@H5  ,@H6  ,@H7  ,@H8  ,@H9  ,@H10  ,
@H11  ,@H12  ,@H13  ,@H14  ,@H15  ,@H16  ,@H17  ,@H18  ,@H19  ,@H20  ,@H21  ,@H22  ,@H23  ,@equipmeintNum,@minutesperload

end

close HourlyEquip
deallocate HourlyEquip
update  dbo.loaderhourly set allowedquantity=quantiy where LoadingTime between @currentdate and @enddate

END

If I put the declaration and assignment in for @currentdate and just run the code, (NOT as a stored procedure) it works as expected and puts the following (correct) outupt:

Apr 6 2009 12:00AM
123457
12000
1
(1 row(s) affected)
inserting
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
inserting
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
inserting
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
123457
12000
2
(1 row(s) affected)
inserting
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
inserting
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
inserting
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(484 row(s) affected)


If I run it via the stored procedure I don't affect any rows, (doesn't print (1 row(s) affected)) they really aren't inserted into the database (checked that) but all the other print statements are correct. So I know it's pulling the right data and it's getting though the code.

I'm pretty certain it's something really dumb, but I don't think it's in my stored procedure, I think it's somewhere in the database setup that's telling it not to do it. And I've poked around and can't find it. But I KNOW I've done this kind of thing before on this test machine!

Where's that "bang head here" sign?

 

[gmmastros]

If I run it via the stored procedure I don't affect any rows, (doesn't print (1 row(s) affected))

In the stored procedure you have SET NOCOUNT ON. This prevents those messages.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[deepsheep]

Damn it!

I KNOW when I was testing yesterday, I was checking to ensure it didn't get inserted into the table and just wan't printing. Must have checked the table 20 different times! I really did and it really wasn't inserting. It's the only reason I posted...

Now it's actually inserting. (Some days I hate computers...)
Thanks!

 

[markros]

Can you do this without using a cusror with one INSERT using SELECT with UNION ?

Just a thought, I could not actually read the whole code, it's a bit too complex for me without comments.