/***********************************************************************************

   Runs same query agsinst mutiple SQL server AND/OR Oracle Instances
   Execute this code against SQL server 2005,2008
------------------------------------------------------------------------------
  Written by Mohammed Mawla , The Pythian GROUP 
  TEST THIS CODE WELL

  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
***********************************************************************************/
USE master
GO
CREATE DATABASE mutliple_Instances_query
GO
USE mutliple_Instances_query
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd4Rem0te_query'
GO
CREATE ASYMMETRIC KEY Loop_over_instances AUTHORIZATION dbo WITH ALGORITHM = RSA_2048 
GO

--Create table to hold the servers informations
CREATE TABLE [dbo].[servers](
    [Ip_or_name] [varchar](100) NOT NULL,   -- IP address or instance name is unique
    [Describe] [nvarchar](300) NULL,
    [DB] [nvarchar](300) NULL CONSTRAINT [DF_servers_DB]  DEFAULT ('Master'),
    [RDMS] [varchar](10) NOT NULL,            -- Must be either "Oracle" or "SQL"
    [Auth_mode_sql] [tinyint] NULL,          -- Must be 0 or 1 in case RDMS ="SQL" , 0 Win auth , 1 SQL authen
    [SQL_uid] [varbinary](1024) NULL,        -- can't be null if "oracle" or "SQL" With SQL auth
    [SQL_pwd] [varbinary](1024) NULL,
    [valid] [bit] NOT NULL CONSTRAINT [DF_servers_valid]  DEFAULT ((1)), -- 0 invalid to run code against
 CONSTRAINT [PK__servers] PRIMARY KEY CLUSTERED
(    [Ip_or_name] ASC  ) ON [PRIMARY]

) ON [PRIMARY]
GO

ALTER TABLE [dbo].[servers]  WITH CHECK ADD  CONSTRAINT [CK_servers_credentials] 
CHECK  ((CASE WHEN [rdms]='Oracle' AND ([SQL_uid] IS NULL OR [SQL_pwd] IS NULL) THEN (1) WHEN [rdms]='SQL' AND [Auth_mode_sql]=(1) AND ([SQL_uid] IS NULL OR [SQL_pwd] IS NULL) THEN (1) ELSE (0) END=(0)))
GO
ALTER TABLE [dbo].[servers] CHECK CONSTRAINT [CK_servers_credentials]
GO
ALTER TABLE [dbo].[servers]  WITH CHECK ADD  CONSTRAINT [CK_servers_rdms] CHECK  (([Rdms]='SQL' OR [RDMS]='Oracle'))
GO
ALTER TABLE [dbo].[servers] CHECK CONSTRAINT [CK_servers_rdms]
GO
ALTER TABLE [dbo].[servers]  WITH CHECK ADD  CONSTRAINT [CK_servers_SQl_auth_mode] CHECK  ((CASE WHEN [rdms]='SQL' AND ([auth_mode_sql]>(1) OR [auth_mode_sql]<(0)) THEN (1) ELSE (0) END=(0)))
GO
ALTER TABLE [dbo].[servers] CHECK CONSTRAINT [CK_servers_SQl_auth_mode]

---------------------------- Example to populate an entry

INSERT INTO dbo.servers (Ip_or_name,Describe,DB,RDMS,Auth_mode_sql,SQL_uid,SQL_pwd,valid)
SELECT  '127.0.0.1','My local machine','Master','SQL',1 ,
EncryptByAsymKey(AsymKey_ID('Loop_over_instances'), 'sa'),
EncryptByAsymKey(AsymKey_ID('Loop_over_instances'), 'sa_P@ssw0rd'),1

---------------------------- Main code
USE mutliple_Instances_query
GO
SET NOCOUNT ON
GO
DECLARE @ip_or_name     VARCHAR (150)
DECLARE @describe       VARCHAR (150)
DECLARE @db             VARCHAR (150)
DECLARE @Rdms           VARCHAR (150)
DECLARE @mysql          NVARCHAR (MAX)
DECLARE @exec_sql       NVARCHAR (MAX) -- Holds CODE that will be run agsinst the instances
DECLARE @ErrorMessage   NVARCHAR(MAX)  -- Error message returned in case of failures
DECLARE @Error INT                      -- Error code returned in case of failures

-- This is the code that will be run against instances , must be systax errors free 
SELECT @exec_sql = CASE WHEN @Rdms ='SQL' THEN ' Select Getdate() ''Datetime'' ;' ELSE ' Select sysdate as datetime from Dual ; ' END
-- Set up cursor for your sql instances
DECLARE instances CURSOR FOR 
SELECT TOP 2 describe,ip_or_name,db,Rdms FROM dbo.servers 
WHERE valid=1 
AND ip_or_name <> 'some_seerver'
--and RDMS = 'Oracle'

OPEN instances
FETCH NEXT FROM instances INTO @describe,@ip_or_namee,@db,@Rdms
WHILE @@FETCH_STATUS = 0
BEGIN
-- Create a linked server
-- For creating an Oracle linked server please read here http://support.microsoft.com/kb/280106
SELECT @mySQL = '
			If not exists (select * from master..sysservers where srvname = '''+@ip_or_name+''')
			Begin
			EXEC master..sp_addlinkedserver @server = N'''+@ip_or_name+''','+ 
				   CASE RDMS 
				   WHEN 'SQL' THEN ' @srvproduct=N''SQL Server'';' 
				   WHEN 'ORACLE' THEN ' @srvproduct=N'''+@ip_or_name+''', @provider=N''MSDAORA'', @datasrc=N'''+@ip_or_name+'''; '
			END    + '
			Exec master..sp_addlinkedsrvlogin @rmtsrvname = N'''+@ip_or_name+''','+
			CASE  
				   WHEN (RDMS='SQL' AND Auth_mode_sql=1) OR RDMS='ORACLE'   THEN '@useself = false, 
			@rmtuser = '''+CONVERT(VARCHAR, DecryptByAsymKey(AsymKey_ID('Loop_over_instances'), sql_uid ))+''', 
			@rmtpassword = '''+CONVERT(VARCHAR, DecryptByAsymKey(AsymKey_ID('Loop_over_instances'), sql_pwd ))+''';'      

				   WHEN (RDMS='SQL' AND Auth_mode_sql=0 ) THEN'@useself=N''True'',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL ;'
			END    +' 
			End
'
FROM dbo.Servers WHERE ip_or_name = @ip_or_name
BEGIN try
   --print @mysql
   EXEC (@mySQL)
END try

BEGIN catch -- A problem happened while creating the linked server 

   IF @@Error <> 0
   BEGIN
       SET @Error = @@Error
       PRINT 'conn error has occurred while working on: [' + @ip_or_name + '] , '+ @describe
       PRINT 'The error number was: ' + @Error
   GOTO nexta
   END 

END catch
PRINT 'Linked server added , Instance: [' + @describe + ']'
BEGIN try
		-- Formulate the main query
		SELECT @exec_sql = 
		CASE WHEN @Rdms ='SQL' THEN -- Use sp_excutsql inside SQL server instance
				   '['+@ip_or_name +'].['+ @db +'].dbo.sp_executesql N'''
					   + REPLACE (@exec_sql,'''','''''') + 
				   ''''
		-- Use Openquery with oracle
		WHEN @Rdms ='Oracle' THEN 'SELECT * From openquery ('+ @ip_or_name +','+ REPLACE (@exec_sql,'''','''''')+')'
		END
		   --print @exec_sql
		   EXEC sp_executesql @exec_sql
END try
BEGIN catch -- an error occured while executing the Query  

   IF @@Error <> 0
   BEGIN
       SET @error = @@error       
       SELECT  @ErrorMessage = ERROR_MESSAGE()
       PRINT 'Error has occurred while working on: [' + @ip_or_name + '] , '+ @describe 
       PRINT 'The error number is: ' + CAST(@error AS VARCHAR(5)) +  ' ; Error message : '+ @ErrorMessage
   GOTO nexta -- Drop linked server and continue with next instance
   END 

END catch
PRINT 'Command Executed , instance: [' + @ip_or_name + '] , '+ @describe 
nexta:
		-- Drop the remote instance connection
		SET @mySQL = 'Exec sp_dropserver ''' + @ip_or_name + ''',''droplogins'' '
		EXEC (@mySQL)
		--print @mysql

		PRINT 'Linked server Dropped ,  instance: [' + @ip_or_name + '] , '+ @describe 
		FETCH NEXT FROM instances INTO @describe,@ip_or_name,@db,@Rdms
		IF @@FETCH_STATUS =0 PRINT '- - - - - - - - - - - - - - next server - - - - - - - - - - - - - -- - - - - - -'
END
CLOSE instances
DEALLOCATE instances