Using GUI, choose Oracle Provider for OLE DB, product name as OraOLEDB.Oracle(same as oracle provider for OLE DB), data source is the tnsname.
Going to Security and provide user and password in remote login section
In Server options, enable RPC and RPC out. Or for the provider itself, enable the Inprocess.
--this is an automatically generated script by SSMS
USE [master]
GO
/****** Object: LinkedServer [ORADB1] Script Date: 13/11/2013 2:47:47 PM ******/
EXEC master.dbo.sp_addlinkedserver @server = N'ORADB1', @srvproduct=N'OraOLEDB.Oracle', @provider=N'OraOLEDB.Oracle', @datasrc=N'ORADB1'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ORADB1',@useself=N'False',@locallogin=NULL,@rmtuser=N'scott',@rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption @server=N'ORADB1', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORADB1', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ORADB1', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORADB1', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORADB1', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ORADB1', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ORADB1', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORADB1', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ORADB1', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'ORADB1', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORADB1', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ORADB1', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ORADB1', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
Quick tips or notes that probably reflects 20 percent of knowledge that usually does 80 percent of job.
Wednesday, November 13, 2013
Wednesday, November 06, 2013
SQLAgent Properties
To view its properties, use
EXEC msdb..sp_get_sqlagent_properties
To set a SQLAgent property, use
EXEC msdb.dbo.sp_set_sqlagent_propertie @property=''
An example can be changing SQLAgent's log file directory:
To set a SQLAgent property, use
EXEC msdb.dbo.sp_set_sqlagent_propertie @property=''
An example can be changing SQLAgent's log file directory:
EXEC msdb.dbo.sp_set_sqlagent_properties
@errorlog_file=N'a dir\SQLAGENT.OUT'
Subscribe to:
Posts (Atom)