Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Stored Procedure Insert not inserting... 1

Status
Not open for further replies.
deepsheep

deepsheep

Programmer
Joined
Sep 13, 2002
Messages
154
Location
CA
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.
 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
Sorry it took so long... Couldn't get to my code until now.
here's the stored procedure. It's pretty straight forward...
Code: 
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?
 
Upvote 0 Downvote
If I run it via the stored procedure I don't affect any rows, (doesn't print (1 row(s) affected))
Click to expand...

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
 
Upvote 0 Downvote
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!
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

WomanPro
  • Locked
  • Question Question
Insert into query adds records to unreadable format, how do I change that?
Replies
5
Views
815
Chris Miller
Chris Miller
bmacbmac
  • Locked
  • Question Question
Inserting XML data into SQLServer returns only NULL values
Replies
3
Views
545
Andrzejek
Andrzejek
Cpreston
  • Locked
  • Question Question
Will not show row with Line Type 2
Replies
17
Views
1K
Cpreston
Cpreston
Cpreston
  • Locked
  • Question Question
Field change as caused issues to store procedure
Replies
19
Views
1K
Andrzejek
Andrzejek
ahmedsa2018
  • Locked
  • Question Question
How to give same number if it exist on table or new number if it not exist based on part value?
Replies
8
Views
756
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top