bedbmsguru

DECLARE @dateval DATETIME

EXECUTE ('begin my1.spGetDate(?); end;', @dateval OUTPUT) AT ORA_DBLINK_NAME;

EXECUTE ('begin my1.spDeleteOldRecords(?); end;', @dateval) AT ORA_DBLINK_NAME;

--파라미터 여러개

EXECUTE ('begin my1.spProc(?,?,?,?); end;', @param_in_1, @param_in_2, @param_out_3 OUTPUT, @param_out_4 OUTPUT) AT DBLINK_NAME;

출처

https://stackoverflow.com/questions/13741175/calling-oracle-stored-procedure-with-output-parameter-from-sql-server

http://m.blog.daum.net/partsofmemory/110

SQL 2008에서 2000으로 링크드서버를 연결하기위해서는 2000의 master DB에 아래의 프로시져를 생성해 두어야 한다.

USE MASTER

GO

CREATE PROCEDURE SP_TABLES_INFO_ROWSET_64
      @TABLE_NAME SYSNAME
    , @TABLE_SCHEMA SYSNAME = NULL
    , @TABLE_TYPE NVARCHAR(255) = NULL
AS
BEGIN
    DECLARE @RESULT INT SET @RESULT = 0

    EXEC @RESULT = SP_TABLES_INFO_ROWSET @TABLE_NAME, @TABLE_SCHEMA, @TABLE_TYPE
END

CF)Linked Server 구성하는법

EXEC sp_addlinkedserver @server='DEVSERVER',@srvproduct='', -- 연결이름.
@provider='SQLOLEDB', @datasrc='목적지IP', @catalog='디비이름' (옵션임)
EXEC sp_addlinkedsrvlogin 'DEVSERVER', 'false', NULL, '아이디', '암호'