ETL and Data Integration tools - Modern ETL software for everyone

I have a stored procedure we are executing as a sql statement it appears as though the SQL script tool is closing the connection prematurely. the sp works correctly when executed in management studio.

the sql statement has the following command 'Execute dbo.sp_guest_import_load_from_RoomMaster 1' which processes the loaded records.

We will do everything to assist you as usual

can we ask you to answer all following questions please
1.Version number
2.how long does it take to execute it using management studio
3.can you post procedure creation script please
3 can you post execution log please

Mike

as per your request. note the sp has some nested sp's below


execution log:
Information 2/15/2011 10:53:13 PM Preparing to execute: Run All
Information 2/15/2011 10:53:13 PM Package Started
Information 2/15/2011 10:53:13 PM Executing Package: process guests
Information 2/15/2011 10:53:13 PM Checking File(s): check for file
Information 2/15/2011 10:53:13 PM File(s) : \\VENUS\d$\ftpRoot\Bellstar\blackstone\ORguest*.csv
Information 2/15/2011 10:53:13 PM File(s) exists: \\VENUS\d$\ftpRoot\Bellstar\blackstone\ORguest*.csv
Information 2/15/2011 10:53:13 PM Executing Transformation: Guests
Information 2/15/2011 10:53:13 PM Source File(s): \\venus\d$\ftproot\Bellstar\blackstone\ORguest20101204.csv
Information 2/15/2011 10:53:14 PM Processed: 65 Records
Information 2/15/2011 10:53:14 PM Errors: 0
Information 2/15/2011 10:53:14 PM Rejected: 0
Information 2/15/2011 10:53:14 PM Records per second: 890.4
Information 2/15/2011 10:53:14 PM Execution of Transformation: Guests completed
Information 2/15/2011 10:53:14 PM Executing Sql Script: Processs Guests
Information 2/15/2011 10:53:15 PM Executing File Operation: Copy File to archive
Information 2/15/2011 10:53:15 PM Found: 1 File(s) to copy
Information 2/15/2011 10:53:15 PM Path: \\VENUS\d$\ftpRoot\Bellstar\blackstone\
Information 2/15/2011 10:53:15 PM Mask: ORguest*.csv
Information 2/15/2011 10:53:15 PM Copied file \\VENUS\d$\ftpRoot\Bellstar\blackstone\ORguest20110214.csv into \\VENUS\d$\ftpRoot\Bellstar\blackstone\Processed_guests\
Information 2/15/2011 10:53:15 PM Executing File Operation: delete file
Information 2/15/2011 10:53:15 PM Found: 1 File(s) to delete
Information 2/15/2011 10:53:15 PM Path: \\VENUS\d$\ftpRoot\Bellstar\blackstone\
Information 2/15/2011 10:53:15 PM Mask: ORguest*.csv
Information 2/15/2011 10:53:15 PM File is deleted: \\VENUS\d$\ftpRoot\Bellstar\blackstone\ORguest20110214.csv
Information 2/15/2011 10:53:15 PM Sending Email: Success
Information 2/15/2011 10:53:15 PM To: mike@ownerrelations.com, frank@OwnerRelations.com


Version Number: 4.2.2.5

sql server 2008
sp execution time in management studio was aprox 10sec

ALTER procedure [dbo].[sp_guest_import_load_from_RoomMaster](
@in_resort_id int = null
)
AS
declare
@v_resort_id int ,
@v_cur_file_date smalldatetime ,
@v_Imp_RoomMaster_Log_ID int

update Imp_RoomMaster_guest_inhouse set file_date = convert(smalldatetime, convert(varchar, file_date, 101))

declare cur_Imp_RoomMaster_guest_inhouse_main cursor
for select distinct
a.resort_id ,
a.file_date
from Imp_RoomMaster_guest_inhouse a
order by a.resort_id, a.file_date

open cur_Imp_RoomMaster_guest_inhouse_main
fetch next from cur_Imp_RoomMaster_guest_inhouse_main into
@v_resort_id ,
@v_cur_file_date
while ( @@fetch_status = 0 ) begin
/*
if not exists(select 1
from Imp_RoomMaster_Log
where Imp_Manager_ID = (select Imp_Manager_ID
from Imp_Manager
where import_name = 'Imp_RoomMaster_Guest_InHouse'
)
and resort_id = @v_resort_id
and import_date = @v_cur_file_date
)
Begin
*/

insert into Imp_RoomMaster_Log(
Imp_Manager_ID ,
hotel_id ,
resort_id ,
import_date ,
to_table_name ,
ftp_url ,
op_time
)
select
Imp_Manager_ID ,
null ,
@v_resort_id ,
@v_cur_file_date ,
'Imp_RoomMaster_Guest_InHouse' ,
null ,
getutcdate()
from Imp_Manager
where import_name = 'Imp_RoomMaster_Guest_InHouse'

select @v_Imp_RoomMaster_Log_ID = @@identity

-- print 'copy records to _His table'
insert into Imp_RoomMaster_Guest_InHouse_His(
record_id ,
Imp_RoomMaster_Log_ID ,
resort_id ,
NUMBER ,
CONFNUM ,
LASTNAME ,
ORIGROOM ,
CHECKIN ,
CHECKOUT ,
OUTDATE ,
PREVROOM ,
TRAVELID ,
RATE_REQ ,
create_date ,
file_date ,
valid_flag ,
RouteFolio
)
select
record_id ,
@v_Imp_RoomMaster_Log_ID,
resort_id ,
NUMBER ,
CONFNUM ,
LASTNAME ,
ORIGROOM ,
CHECKIN ,
CHECKOUT ,
OUTDATE ,
PREVROOM ,
TRAVELID ,
RATE_REQ ,
create_date ,
file_date ,
'0' ,
RouteFolio
from Imp_RoomMaster_Guest_inhouse
where resort_id = @v_resort_id
and file_date = @v_cur_file_date
order by record_id

delete Imp_RoomMaster_guest_inhouse where resort_id = @v_resort_id and file_date = @v_cur_file_date

if @v_cur_file_date is not null begin
-- print 'execute sp_Imp_RoomMaster_Guests_From_His'
exec dbo.sp_Imp_RoomMaster_Guests_From_His @v_resort_id, @v_cur_file_date, @v_cur_file_date
end

/*
end
*/

FEQNXT_MAIN:
fetch next from cur_Imp_RoomMaster_guest_inhouse_main into
@v_resort_id ,
@v_cur_file_date
end
close cur_Imp_RoomMaster_guest_inhouse_main
deallocate cur_Imp_RoomMaster_guest_inhouse_main

OK thank you

can also post actual sql execution log
and screenshot of sql editor so we can see how you call the procedure

Mike

here is a screenshot of the sql command
this is being executed via the sql native client

as for a execution log I don't have one. I can generate a log while executing the sp manually however that won't help diagnosing the issue with it being executed via ETL.

mike

image attached.