Stored Procedure problem!

[pradeep]

Hi

There is a strange problem happening with stored procedure.

I have written a stored procedure for my application to update the database.
This stored procedure is working fine in my machine(local server).
But when I hosted it on the Live server. It doesn't work. What may be the problem. Is there anything related to SQL Server used in the Live site.
Awaiting for the response.


Thanks & Regards
Pradeep

[MAK]

Needed more details like what kind of error message you are getting.

1. Did you give GRANT exec permissions on the stored procedure for the user?
2. Does the connection used to run the procedure have enough permissions to access the objects used in the stored procedure?

[pradeep]

Hi Mak

Thanks for your quick reply.

My application is a ASP application for hotel reservation. let me tell in brief. There are 5 types of Rooms. Using stored procedure I am increasing the avaliable rooms for each room type for a period say from 12 feb 2004 to 12 march 2004. But it is updating only until 3 march 2004. I had a doubt whether the stored procedure is wrong. But same stored procedure is working fine in My machine(local server).
there is no error messages displayed.

Is this stored procedure dependend on SQl Server to Server.

Thanks & Regards
Pradeep

[MAK]

post the SP code here and also the related tables with sample data

[pradeep]

Hi MAK

Quoting down is the Stored procedure


CREATE procedure incntrl_create (@hotel_id as int, @roomtypeid as int, @start_date as datetime, @end_date as datetime,@status as varchar(20), @inc as int , @dec as int, @output as varchar(20)) as
declare @max_rooms as int;
declare @inventoryid as int;
declare @inet_avail as int;
declare @i_avail as int;
declare @a_avail as int;
declare @cur_avail as int;
declare @invid as int;
declare @c_avail as int;
declare @countStr as int;
declare @inStr as varchar(20);
declare @updStr as varchar(20);
declare @index as int;
set @countStr = 0;
set @i_avail =0;
set @c_avail = 0;
set @inet_avail = 0;
set @cur_avail = 0;
set @index=0;
select @max_rooms = max_rooms from rd_roomtype
where hotel_id = @hotel_id
and roomtypeid = @roomtypeid
/*print @max_rooms */

while (@start_date <= @end_date)
BEGIN
print @index;
set @invid = 0
set @inet_avail = 0
set @cur_avail = 0
set @i_avail = 0
set @c_avail = 0
set @countStr = @countStr + 1
select @invid = inventoryid, @inet_avail = inet_avail, @cur_avail = cur_avail from rd_inventory
where hotel_id = @hotel_id
and roomtypeid = @roomtypeid
and start_date = @start_date
if @inet_avail is NULL set @inet_avail = 0
if @cur_avail is NULL set @cur_avail = 0

if @inc > 0
BEGIN
select @i_avail = @inet_avail + @inc;
select @c_avail = @cur_avail + @inc;
END

if @dec > 0
BEGIN
select @i_avail = @inet_avail - @dec;
select @c_avail = @cur_avail - @dec;
END

if @dec = 0 and @inc = 0
BEGIN
select @i_avail = @inet_avail;
select @c_avail = @cur_avail;
END

if @i_avail < 0
set @i_avail = 0
if @i_avail > @max_rooms
set @i_avail = @max_rooms
if @c_avail < 0
set @c_avail = 0;
if @c_avail > @max_rooms
set @c_avail = @max_rooms;

if @invid <> 0
begin
/* print @start_date
print @i_avail
print @c_avail */
Update rd_inventory set inet_avail=@i_avail, cur_avail=@c_avail, status=@status where hotel_id=@hotel_id and roomtypeid=@roomtypeid and start_date=@start_date
end
else
begin
Insert into rd_inventory (hotel_id, roomtypeid,inet_avail,cur_avail, start_date,status) values( @hotel_id,@roomtypeid,@i_avail,@c_avail,@start_dat e,@status)
end
set @start_date = DATEADD(day,1,@start_date)
set @index=@index+1;
END


IF @@ERROR <> 0
BEGIN
RETURN(3)
END


the table is rd_inventory
fields are
hotel_id,roomtypeid
inventoryid
inet_avail
cur_avail
start_date
end_date
status

the data is attached with this post.
Thanks
Prithvi

[MAK]

Please run this proc in debug mode and see.

BTW what is the difference between you machine and the server. 6.5 /7.0 and 2000?

and what SP you are on?

Also see the difference in DATA comparing your machine and on the server.