-- Name:
-- sp_ORAremotequery
-- Description:
-- Execute remote Oracle query via OPENQUERY. Can use an optional temp table.
-- Inputs:
-- @Server == Name of linked server
-- @SelectColumnList == Comma seperated list of columns to select from
-- @InsTable == An optional table can be inserted into
-- @InsColumnList == Comma separated list of columns to insert into (required if @InsTable is not null)
-- Security:
-- Internal
-- Returns:
-- Success/failure + optional temp table
-- Owner:
--
CREATE PROCEDURE sys.sp_ORAremotequery
(
@Server varchar(128),
@SelectColumnList nvarchar(2000) = NULL,
@InsTable sysname = NULL,
@InsColumnList nvarchar(2000) = NULL
)
AS
BEGIN
DECLARE @retcode int
DECLARE @dbname sysname
SET NOCOUNT ON
SELECT @retcode = 0
SELECT @dbname = db_name()
-- Verify publisher linked server login exists
EXEC @retcode = sys.sp_IHCheckPublisherLogin @publisher = @Server OUTPUT
IF @retcode != 0
BEGIN
RETURN (@retcode)
END
-- Validate #hquery table exists and no invalid entry
IF object_id('tempdb..#hquery', 'U') IS NULL
OR EXISTS
(
SELECT *
FROM #hquery
WHERE seq in (0, 1)
)
BEGIN
RAISERROR(8624, 16, -1)
RETURN (1)
END
IF (@SelectColumnList is NOT NULL and @InsColumnList is NULL) or (@InsColumnList is NOT NULL and @SelectColumnList is NULL)
BEGIN
RAISERROR(21619, 16, -1)
RETURN (1)
END
-- Prepare quotes for OPENQUERY
UPDATE #hquery
SET cmd = REPLACE(cmd, '''', '''''')
-- Enable identity insert to inject wrapper queries
SET IDENTITY_INSERT #hquery ON
IF @InsTable is NULL
BEGIN
INSERT INTO #hquery (seq, cmd)
VALUES (1, N'SELECT * FROM OPENQUERY(' + QUOTENAME(@Server) + ',''')
END
ELSE
BEGIN
IF @InsColumnList is NULL
BEGIN
INSERT INTO #hquery (seq, cmd)
VALUES (1, N'INSERT INTO ' + QUOTENAME(@InsTable)
+ ' SELECT * FROM OPENQUERY (' + QUOTENAME(@Server) + ',''')
END
ELSE
BEGIN
INSERT INTO #hquery (seq, cmd)
VALUES (1, N'INSERT INTO '+ QUOTENAME(@InsTable)
+ ' (' + @InsColumnList + ') SELECT ' + @SelectColumnList + ' FROM OPENQUERY('
+ QUOTENAME(@Server) + ',''')
END
END
-- Close open query
SET IDENTITY_INSERT #hquery OFF
INSERT INTO #hquery (cmd) VALUES (N''')')
-- Execute query
EXEC @retcode = sys.sp_IHexecresultset N'SELECT cmd FROM #hquery ORDER BY seq', @dbname
RETURN @retcode
END