Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_ORAloadscript

  No additional text.


Syntax


-- Name:
--		sp_ORAloadscript

-- Description:
--		Executes script on Oracle server

-- Inputs:
--		@publisher		== publisher name (TNS name)
--		@script			== script file
--		@security_mode	== 0 - Oracle security; 1 - NT integrated security
--		@login			== Oracle login to server as schema owner
--		@password		== Oracle password

-- Returns:
--		Return code (0 for success, 1 for failure)

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

-- Notes:
--		Generates SQL*Plus command line to execute script using auth info

create procedure sys.sp_ORAloadscript
(
    @publisher		sysname,
    @script			sysname,
    @security_mode	bit = 1,
    @login			sysname = NULL,
    @password		sysname = NULL
)
AS
BEGIN
    SET NOCOUNT ON
	
	DECLARE @retcode			int
    DECLARE @install_path		nvarchar(255)
    DECLARE @mssql_data_path		nvarchar(255)
    DECLARE @script_cmd			nvarchar(4000)

    -- Security Check: require sysadmin
    IF (ISNULL(IS_SRVROLEMEMBER('sysadmin'),0) = 0)
    BEGIN
        RAISERROR(21089,16,-1)
        RETURN (1)
    END

	-- Check to ensure a login is provided if security mode is SQL Server authentication.
	SELECT @login = RTRIM(LTRIM(ISNULL(@login, '')))
	IF @security_mode = 0 and @login = ''
	BEGIN
		-- @login cannot be null or empty when @security_mode is set to 0 (SQL Server authentication).'
		RAISERROR(21694, 16, -1, '@login', '@security_mode')
		RETURN (1)
	END

    IF @password = N''
    BEGIN
		SELECT @password = NULL
	END

    -- Get path to version specific INSTALL directory
    exec @retcode = sys.sp_MSget_setup_paths @sql_path = @install_path OUTPUT, @data_path = @mssql_data_path OUTPUT
    IF @retcode != 0 OR @install_path IS NULL OR @install_path = '' OR @mssql_data_path IS NULL OR @mssql_data_path = ''
    BEGIN
        RETURN (1)
    END

	-- Use SQL*Plus for loading scripts
	SELECT @script_cmd = '" "sqlplus.exe" "\"'
						+ sys.fn_escapecmdshellsymbolsremovequotes(@login) COLLATE DATABASE_DEFAULT + '\"/\"'
						+ sys.fn_escapecmdshellsymbolsremovequotes(@password) COLLATE DATABASE_DEFAULT + '\"@'
						+ sys.fn_escapecmdshellsymbols(@publisher) COLLATE DATABASE_DEFAULT + '" @"'
						+ sys.fn_escapecmdshellsymbolsremovequotes(@install_path) COLLATE DATABASE_DEFAULT + '\install\'
						+ sys.fn_escapecmdshellsymbolsremovequotes(@script) COLLATE DATABASE_DEFAULT +'.sql" > "'
						+ sys.fn_escapecmdshellsymbolsremovequotes(@mssql_data_path) COLLATE DATABASE_DEFAULT + '\log\'
						+ sys.fn_escapecmdshellsymbolsremovequotes(@script) COLLATE DATABASE_DEFAULT + '.out"'

	EXEC @retcode = master.dbo.xp_cmdshell @script_cmd, NO_OUTPUT
	
	IF @retcode != 0 OR @@ERROR != 0
	BEGIN

		SELECT @script_cmd = '" "sqlplus.exe [ORALOGIN]/[ORAPASSWORD]@"'
						+ sys.fn_escapecmdshellsymbols(@publisher) COLLATE DATABASE_DEFAULT + '" @"'
						+ sys.fn_escapecmdshellsymbolsremovequotes(@install_path) COLLATE DATABASE_DEFAULT + '\install\'
						+ sys.fn_escapecmdshellsymbolsremovequotes(@script) COLLATE DATABASE_DEFAULT +'.sql" > "'
						+ sys.fn_escapecmdshellsymbolsremovequotes(@mssql_data_path) COLLATE DATABASE_DEFAULT + '\log\'
						+ sys.fn_escapecmdshellsymbolsremovequotes(@script) COLLATE DATABASE_DEFAULT + '.out"'

		RAISERROR (25017, 16, -1, @script_cmd)
		RETURN (1)
	END

    RETURN(0)
END

 
Last revision 2008RTM
See also

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