Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSrepl_get_Oracle_publisher_table

  No additional text.


Syntax
CREATE PROCEDURE sys.sp_MSrepl_get_Oracle_publisher_table
(
	@query				nvarchar(2000),
	@SelectColumnList 	nvarchar(2000),
	@temptablename		sysname,
	@InsColumnList		nvarchar(2000),
	@LocalTableName		nvarchar(2000),
	@publisher 			sysname,
	@database			sysname
)
AS
BEGIN
    SET NOCOUNT ON

	DECLARE @retcode		int
    DECLARE @cmd			nvarchar(2000)
    DECLARE @select			nvarchar(2000)
    DECLARE @queryqu		nvarchar(2000)
    DECLARE @sev			int
    DECLARE @state			int
    DECLARE @msg			nvarchar(max)

    -- Define sp_ORAremotequery support table
    create table #hquery
    (
		seq	int identity(2,1),
		cmd	nvarchar(4000)
    )

    SELECT @queryqu = sys.fn_replreplacesinglequote(@query)

    -- Populate #hquery table
	EXEC @retcode = sys.sp_executesql @query
	IF @retcode != 0 OR @@error != 0
	BEGIN				
		SELECT @cmd = N'Unable to populate #hquery table: Query = ' + @queryqu
		EXEC sys.sp_log_repository_message N'Failure', @cmd, @database
		RETURN(0)
    END

    EXEC @retcode = sp_ORAremotequery
					@Server				= @publisher,
					@SelectColumnList	= @SelectColumnList,
					@InsTable			= @temptablename,
					@InsColumnList		= @InsColumnList

    IF @@error <> 0 OR @retcode <> 0
    BEGIN
		SELECT @cmd = N'Query of publisher ' + @publisher + N' to populate table ' + @temptablename + N' failed: Query = ' + @queryqu
		EXEC sys.sp_log_repository_message N'Failure', @cmd, @database
		RETURN(0)
    END

    EXEC sys.sp_log_repository_message N'Success', @queryqu, @database
				
    SELECT @cmd = N'DROP TABLE ' + @LocalTableName

    -- Ignore drop error
    BEGIN TRY
		EXEC @retcode = sys.sp_executesql @cmd
    END TRY
    BEGIN CATCH
		SELECT @retcode = 0
    END CATCH

    SELECT @select = N'SELECT * INTO ' + @LocalTableName + N' FROM ' + quotename(@temptablename)

	EXEC @retcode = sys.sp_executesql @select
	IF @retcode != 0 OR @@error != 0
	BEGIN				
		SELECT @cmd = N'SELECT from table ' + @temptablename + N' to populate table ' + @LocalTableName +
		              N' failed: Query = ' + @select
		EXEC sys.sp_log_repository_message N'Failure', @cmd, @database
		RETURN(0)
    END

    EXEC sys.sp_log_repository_message N'Success', @select, @database

    drop table #hquery

    RETURN(0)

END

 
Last revision 2008RTM
See also

  sp_MSdrop_repltran (Procedure)
sp_MSrepl_get_Oracle_publisher_metadata (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash