I'm trying to execute a couple of TSQL commands in SQL Server 2012, over a database link from Oracle 11g. The Oracle code is:
DECLARE
trunc_tbl INTEGER;
insert_tbl INTEGER;
BEGIN
trunc_tbl := DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@godaddytest
('truncate table rics_project');
insert_tbl := DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@godaddytest
('insert into rics_project select * from project');
commit;
END;
The commands do execute, but it looks like it's leaving the session hanging in SQL Server. (The reason I'm doing this is to build a temporary table in SQL Server that I then query from Oracle, and I thought if I could run these two statements from the Oracle procedure, I could avoid having jobs and timing issues in both databases.)
I'm fairly new at SQL Server and TSQL, so it's possible/likely that I'm doing something here that isn't advisable, but is there something I should be explicitly doing to end the session once the second command is finished?
Thanks,
Harry