I’ve created a stored procedure to push data from one of our SQL Server 2005
tables to Oracle 10.1 via a MSDAORA Linked Server. The Linked Server works
as tested in the Editor, but INSERTs are silently failing when the sp is run
from the right-click menu on the Object Explorer.
First, here’s the sp (or something very close to it):
CREATE PROCEDURE [dbo].[apex_push] AS
PRINT ‘Truncating table…’;
EXEC (‘BEGIN ORASCHEMA.TRUNCATE_MERGE_TABLE; END;’) AT APEX;
PRINT ‘Inserting rows into MERGE table…’;
INSERT INTO OpenQuery(APEX,‘SELECT compname, login, domain, computermodel,
audit_date FROM ORASCHEMA.MERGE_TABLE’) SELECT compname, login, domain,
computermodel, audit_date FROM DifferentSSDB.dbo.Workstation;
When I right-click on this sp from the Object Explorer and choose “Run”, it
generates this script:
DECLARE @return_value int;
EXEC @return_value = MYSSDB.dbo.apex_push;
SELECT @return_value as N’@Return Value’;
It looks fine, but the sp does not work. The EXEC statement completes
successfully, but while the INSERT appears to run (given the execution
time), there are no rows INSERTed into the Oracle table. I checked this
using Toad for Oracle. However, if I alter the generated script to simply:
…it works fine! Huh???
I suspect I’m missing some SQL Server trick here, but I’m then wondering how
to determine if I can use the “Run” option from the right-click menu or not.