stored procedure not executing

[rrajarat]

I have a stored procedure that basically delete all the data from a table and inserts some new data into the same table. I call this procedure in powerbuilder 7 and I am passing 2 datetime variables and a string to the procedure.
For some reason my procedure isn't executeing. when I run the delete and the insert in query analyzer they work. I even tried a commit after the delete to see if the tables gets emptied. Nothing happens. the procedure is below.


ALTER PROCEDURE [dbo].[msp_mpprode]
(@start datetime, @end datetime, @location varchar(25))
as
begin
truncate table mpprode
commit
Insert into [dbo].[mpprode](
part, machine, qty, downtime, pph, stdcrew, stdrun, actcrew, actrun)
SELECT audit_trail.part,
audit_trail.from_loc,
SUM(audit_trail.quantity),
(SELECT SUM(downtime.down_time) FROM downtime WHERE (downtime.trans_date BETWEEN @start AND @end) AND downtime.part = audit_trail.part),
part_machine.parts_per_hour,
part_machine.crew_size,
SUM(audit_trail.quantity)/part_machine.parts_per_hour,
0.0001,
0.0001
FROM {oj audit_trail LEFT OUTER JOIN part_machine ON audit_trail.part = part_machine.part AND audit_trail.from_loc = part_machine.machine}
WHERE audit_trail.date_stamp >= @start AND
audit_trail.date_stamp <= @end AND
audit_trail.type = 'J' AND
part_machine.machine IN (SELECT location.code FROM location WHERE location.group_no = @location)
GROUP BY audit_trail.part,
audit_trail.from_loc,
part_machine.parts_per_hour,
part_machine.crew_size
end







GO

 

[MeanGreen]

You might set up a cursor to truncate table and then do the insert. Maybe the table is not done truncating before you try the insert. You might also qualify the table name within your truncate statement.

This is a quote from BOL:

sql_statement Limitations
Any SET statement can be specified inside a stored procedure except SET SHOWPLAN_TEXT and SET SHOWPLAN_ALL, which must be the only statements in the batch. The SET option chosen remains in effect during the execution of the stored procedure and then reverts to its former setting.

Inside a stored procedure, object names used with certain statements must be qualified with the name of the object owner if other users are to use the stored procedure. The statements are:

ALTER TABLE

CREATE INDEX

CREATE TABLE

All DBCC statements

DROP TABLE

DROP INDEX

TRUNCATE TABLE

UPDATE STATISTICS


Hope this helps.

 

[rrajarat]

I tried it with:

delete from mpprode

which does need to be qualified but it didn't work either

 

[MeanGreen]

Rather then commit after truncate statement, try to use a GO statement.

Just a thought.

 

[SQLBill]

Can you provide the portion of the script where you are calling this procedure? That might have something to do with it.

-SQLBill

 

[rrajarat]

Here is the part of the code where I declare and call the procedure. Also using GO after the truncate makes the @start, @end, and @location variables invalid in the insert and select.

DECLARE proc PROCEDURE FOR msp_mpprode
@start = :idtDate,
@end = :idtEndDate,
@location = :isLocation;
SetPointer (HourGlass!)
execute proc;
SetPointer (Arrow!)

 

[SQLBill]

I don't know Powerbuilder, but in SQL Server I believe you have to have the parameters as part of the execute statement. So, since you declared @start = :idtDate, @end = :idtEndDate, and @location = :isLocation
I think it needs to be:

execute proc @start, @end, @location

And that might be causing confusion with the same variable names in the actual procedure.

(This is quessing on my part)

-SQLBill

 

[rrajarat]

when you declare a stored procedure in powerbuilder you have to assign the values of the parameters as well. That is @start, @end, and @location are variables in the stored procedure and they will be given the values of idtDate, idtEndDate and isLocation at the time of the execute proc command.

 

[y2kprawn]

Just 2 things here, can you execute the stored procedure in Query analyser using 'exec sp_name'

Also it might be a good idea to check the permissions on the stored procedure and in certain cases stored procedures that may be called from within.