Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSrepl_verify_oracle_client_version

  No additional text.


Syntax

-- Name: sp_MSrepl_verify_oracle_client_version

-- Descriptions: Verify that the version of SQL*PLUS that loads based upon the system path
--               is sufficiently current to support Oracle publishing.

-- Parameters: as defined in create statement

-- Security:
-- Requires Certificate signature for catalog access

-- Returns: 0 - success
--          1 - Otherwise

CREATE procedure sys.sp_MSrepl_verify_oracle_client_version
AS
	SET NOCOUNT ON

	DECLARE @retcode  int
	DECLARE @command nvarchar(4000)
	DECLARE @version nvarchar(100)
	DECLARE @release nvarchar(40)
	DECLARE @verstr nvarchar(3)
	DECLARE @indx int
	DECLARE @ver int

	SELECT @version = NULL
	SELECT @verstr = NULL
	SELECT @ver = NULL

	select @command = N'sqlplus.exe -?'
	select @release = N'R'

	create table #text_ret(cmdoutput nvarchar(255) collate database_default null)

	insert into #text_ret exec @retcode = master.dbo.xp_cmdshell @command
	IF @@error <> 0 or @retcode <> 0
	BEGIN
		-- if sqlplus with -? fails, try without -?
		select @command = N'sqlplus.exe'

		insert into #text_ret exec @retcode = master.dbo.xp_cmdshell @command

		IF @@error <> 0 or @retcode <> 0
		BEGIN
		    drop table #text_ret
		    RAISERROR(21617, 16, -1)
		    RETURN(1)
		END

		-- Simply return success.  SQL*PLUS executes but does not support -? switch.
		-- Since all older versions of SQL*PLUS support this switch, we must assume
		-- that the running version is newer and hence meets the minimum version requirement.
		drop table #text_ret
		RETURN(0)
	END

	-- Search the table for the first (only) non-null entry.
	SELECT @version = (
	    SELECT	TOP 1 cmdoutput
	    FROM	#text_ret
	    WHERE	cmdoutput IS NOT NULL )

	-- If @version is NULL, we didn't get the version string expected.  Assume that
	-- this is behavior that we don't understand and error on the side of caution.  Return success.
	if @version is NULL
	BEGIN
	    drop table #text_ret
	    RETURN(0)
	END

	-- Locate the substring 'R' in the version string (May be case isue with substring 'Release')
	SELECT @indx = charindex(@release, @version)

	-- If @indx = 0, we didn't get the version string expected.  Assume that
	-- this is behavior that we don't understand and error on the side of caution.  Return success.
	if @indx = 0
	BEGIN
	    drop table #text_ret
	    RETURN(0)
	END

	-- Beginning after the substring 'Release', extract the two characters preceeding the first '.'
	SELECT @version = substring(@version, @indx, 30)
	SELECT @indx = patindex('%__.%',@version)

	-- If @indx = 0, we didn't get the version string expected.  Assume that
	-- this is behavior that we don't understand and error on the side of caution.  Return success.
	if @indx = 0
	BEGIN
	    drop table #text_ret
	    RETURN(0)
	END

	SELECT @verstr = substring(@version, @indx, 2)
	SELECT @ver = convert(int, @verstr)

	-- If the conversion produces an error this is behavior that we don't understand.
	-- Also, the returned result here should never be NULL or less than 6.
	-- Error on the side of caution.  Return success.
	if @ver IS NULL OR @@ERROR <> 0 OR @ver < 6
	BEGIN
	    drop table #text_ret
	    RETURN(0)
	END

	-- We can be confident that we have correctly identified the SQL*PLUS release string.
	-- Fail if the client version is less than 9
	if @ver < 9
	begin
		drop table #text_ret
		RAISERROR(21620, 16, -1)
		RETURN(1)
	end

	drop table #text_ret
	RETURN (0)

 
Last revision 2008RTM
See also

  sp_MSdrop_replcom (Procedure)
sp_MSrepl_testconnection (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