Table Entry Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Table Entry Problem

Author

Topic

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2008-06-12 : 01:44:02

While i execute the below query ,Entry is put in status_report table.But the same if i put in scheduling (Sql Agent) no entry is made in the table and the message success is shown in "Agent Job Activity".

Purpose of the sp is to transfer data from One Server to Another Server.

But if see in the Destination Tables No entry is made.

CREATE procedure RBI_Control_sp
as
begin
set nocount on

begin try
BEGIN TRANSACTION
insert into fin_wh..Status_report
(
[object_name],
row,
st_date
)
select
'Data Tranfer',
'Inprogress--',
getdate()

--Data Transfer From the Live Server [RamcoFin02(scmdb)] to Dw-Server[Ramcovm392(fin_ods)]
exec fin_ods..RBI_Data_Transfer_sp

--temp table Table population,Fetching data from the fin_ods[erp Table]
exec FIN_wh..RBI_SPExecution_sp

Update fin_wh..Status_report
set row = 'Success',
[error_message] = 'No Error',
end_date = getdate()
where row = 'Inprogress--'

COMMIT TRANSACTION
end try

begin catch
ROLLBACK TRANSACTION

Update fin_wh..Status_report
set row = 'Failure',
end_date = getdate(),
[error_message]= cast(error_number() as varchar) + '***' + error_message()
where row = 'Inprogress--'
end catch

set nocount off
end

Txs in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-12 : 06:13:35

Are they linked servers?