stored procedures not working 1

[rrajarat]

I have a stored procedure in SQL Server 2000 that basically deletes 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 (recieved as datetime and varchar(25).
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 table 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

 

[rt63]

There could be some problems as follows
1) The connection you are using in PB may not have rights to execute the procedure.
2) There may be some regional date setting issues which may be resulting in the procedure receiving date in incorrect format - @start and @end may not be what u want the procedure to receive.
3) You may have set autocommit property to false and not commiting after the procedure call.

I suggest you check this out in the debug mode. After the execute of the procedure - check the sqlca object properties
a) is the sqlca.sqlcode = 0 (success)
b) anything in the sqlca.sqlerrtext - give an idea what the problem could be.

As I can see from the code, truncate is the first step in proc. So even if the matching records are not found, at least the table should have got truncated. Most likely the problem is in getting the proc to execute - which debug mode may help in discovering.
RT

 

[rrajarat]

the error message in sqlca.sqlerrtext is:

Invalid character value for cast specification

What does this mean? Iget the same message when i try to debug the procedure in sql 2000 Query Analyzer.

 

[rt63]

The problem is that what you are passing as paremeters to procedure are not being interpretted as the same datatype by the procedure and it is not able to do an implicit type conversion. The other issue could be the location parameter which may be getting passed as null and your code is not able to handle it. I suggest you do the following:

In your PB code have you declared the corresponding @start and @end as date or datetime variables. If they are date, then they may not be getting converted properly when being sent to the proc. So declare them as datetime instead.
If they are already datetime, then there could be some regional setting issues - try declaring them as string and store the date value in them after converting to a yyyy/mm/dd format.

ls_start = string(ld_start,'yyyy/mm/dd')

and pass these string variables instead. Implicit conversion from string to datetime will not be a problem.

RT