SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dmoScriptServer]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[dmoScriptServer]
GO



create procedure dbo.dmoScriptServer
as
-- common
declare @dmoServer int,
	@path varchar(255),
	@dmoCmd varchar(255), 
	@dmoCollection varchar(255), 
	@scriptFile varchar(255),
	@hr int,
	@hrhex char(10),
	@errorSource varchar(255),
	@errorDescription varchar(1000),
	@scriptType int,
	@databaseName sysname,
	@procedureName sysname,
	@vssShare varchar(255),
	@vssUserName varchar(30),
-- backup devices
	@backupDeviceCount int,
	@curBackupDeviceNbr int,
	@backupDeviceName varchar(255),
-- logins
	@loginCount int,
	@curLoginNbr int,
	@loginName varchar(255),
-- alerts
	@alertCount int,
	@curAlertNbr int,
	@alertName varchar(255),
-- jobs
	@jobCount int,
	@curJobNbr int,
	@jobName varchar(255),
-- operators
	@operatorCount int,
	@curOperatorNbr int,
	@operatorName varchar(255)

set nocount on

-- init
set @databaseName = db_name() -- use the dbChangeControl Server Path
set @procedureName = @databaseName + '.' 
			+ user_name(objectproperty(@@procid,'OwnerId')) 
                	+ '.' + object_name(@@procid)

-- get file system path 
select @path = s.workingFolderRoot 
		+ p.projectName + '\'
		+ e.environmentName + '\'
		+ s.serverName + '\' 
		+ s.instanceName + '\',
	@vssUserName = n.vssUserName
from Admin.dbo.SqlDatabase d 
inner join Admin.dbo.ProjectNode n
on d.id = n.sqlDatabase_id
inner join .SQlServer s
on s.id = n.sqlServer_id
inner join Admin.dbo.Environment e
on e.id = n.environment_id
inner join Admin.dbo.Project p 
on p.id = n.project_id
where d.databaseName = @databaseName
--and s.id = Admin.dbo.thisSQLServer_id() cannot use function in SQL Server 7
and s.serverName = reverse(substring(reverse(@@servername), 
				charindex('\',reverse(@@servername)) + 1, 
                        	datalength(reverse(@@servername))))
and s.instanceName = substring(@@servername, 
               	                charindex('\',@@servername) + 1, 
                                datalength(@@servername))
and n.IsActive = 1

if @@rowcount = 0
	begin
		raiserror(59002,16,1,@procedureName, @databaseName)
		return -1
	end

-- wrong user asking
if substring(system_user,charindex('\', system_user) + 1, datalength(system_user)) <> @vssUserName
	begin
		set @vssUserName = substring(system_user,charindex('\', system_user) + 1, datalength(system_user))
		set @vssShare = '' -- don't tell em
		raiserror (59005,16,1, @procedurename, 	@vssUserName, @vssShare)
		goto ErrorHandler
	end

-- new file, script drop object and create object
set @scriptType 	= 1		-- drop 
			+ 4		-- primary object
			+ 64		-- to file only
			+ 4096		-- if not exists
			+ 262144	-- owner qualify

-- open an in-process COM/DMO connection to this server
set @dmoCmd = 'SQLDMO.SQLServer'
exec @hr = master..sp_OACreate @dmoCmd, @dmoServer OUT
if @hr <> 0 goto ErrorHandler

-- set the security context to integrated 
set @dmoCmd = 'loginSecure'
exec @hr = master..sp_OASetProperty @dmoServer,@dmoCmd,1
if @hr <> 0 goto ErrorHandler

-- connect to the specified server 
set @dmoCmd = 'Connect'
exec @hr = master..sp_OAMethod @dmoServer,@dmoCmd,NULL,@@servername
if @hr <> 0 goto ErrorHandler

-- Backup Devices
select @dmoCmd 	= 'BackupDevices.Count'
exec @hr = master.dbo.sp_OAGetProperty 	@dmoServer,
					@dmoCmd,
					@backupDeviceCount OUT
if @hr <> 0 goto ErrorHandler
set @curBackupDeviceNbr = 1
while @curBackupDeviceNbr <= @backupDeviceCount 
	begin
	        -- get the name 
	        select @dmoCmd 	= 'BackupDevices.Item(' 
					+ cast(@curBackupDeviceNbr as varchar(10))
					+ ').Name'
		exec @hr = master.dbo.sp_OAGetProperty 	@dmoServer,
							@dmoCmd,
							@backupDeviceName OUT
        	if @hr <> 0 goto ErrorHandler
	        select @dmoCmd 	= 'BackupDevices("' 
					+ @backupDeviceName 
					+ '").Script'
	        select @scriptFile 	= @path 
					+ 'BackupDevices\' 
					+ @backupDeviceName 
					+ '.sql'
        	exec @hr = master.dbo.sp_OAMethod 	@dmoServer,
							@dmoCmd,
							NULL,
							@scriptType,
							@scriptFile
        	if @hr <> 0 goto ErrorHandler
		select @curBackupDeviceNbr = @curBackupDeviceNbr + 1
      end

-- Logins
select @dmoCmd 	= 'Logins.Count'
exec @hr = master.dbo.sp_OAGetProperty 	@dmoServer,
					@dmoCmd,
					@loginCount OUT
if @hr <> 0 goto ErrorHandler
set @curLoginNbr = 1
while @curLoginNbr <= @loginCount 
	begin
	        -- get the name 
	        select @dmoCmd 	= 'Logins.Item(' 
					+ cast(@curLoginNbr as varchar(10))
					+ ').Name'
		exec @hr = master.dbo.sp_OAGetProperty 	@dmoServer,
							@dmoCmd,
							@loginName OUT
        	if @hr <> 0 goto ErrorHandler
	        select @dmoCmd 	= 'Logins("' 
					+ @loginName 
					+ '").Script'		
	        select @scriptFile 	= @path 
					+ 'Logins\' 
					+ replace(@loginName,'\','-')
					+ '.sql'
        	exec @hr = master.dbo.sp_OAMethod 	@dmoServer,
							@dmoCmd,
							NULL,
							@scriptType,
							@scriptFile
        	if @hr <> 0 goto ErrorHandler
		select @curLoginNbr = @curLoginNbr + 1
      end

-- alerts
select @dmoCmd 	= 'JobServer.Alerts.Count'
exec @hr = master.dbo.sp_OAGetProperty 	@dmoServer,
					@dmoCmd,
					@alertCount OUT
if @hr <> 0 goto ErrorHandler
set @curAlertNbr = 1
while @curAlertNbr <= @alertCount 
	begin
	        -- get the name 
	        select @dmoCmd 	= 'JobServer.Alerts.Item(' 
					+ cast(@curAlertNbr as varchar(10))
					+ ').Name'
		exec @hr = master.dbo.sp_OAGetProperty 	@dmoServer,
							@dmoCmd,
							@alertName OUT
        	if @hr <> 0 goto ErrorHandler
	        select @dmoCmd 	= 'JobServer.Alerts("' 
					+ @alertName 
					+ '").Script'
	        select @scriptFile 	= @path 
					+ 'Jobserver\Alerts\' 
					+ replace(
					  replace(
					  replace(	
					  replace(	
					  replace(	
					  replace(	
					  replace(	
					  replace(	
					  replace(	
						@alertName,
						'\','~'),
						'/','~'),
						':','~'), 
						'*','~'),
						'?','~'),
						'"','~'),
						'<','~'),
						'>','~'),
						'|','~') 
					+ '.sql'
        	exec @hr = master.dbo.sp_OAMethod 	@dmoServer,
							@dmoCmd,
							NULL,
							@scriptType,
							@scriptFile
        	if @hr <> 0 goto ErrorHandler
		select @curAlertNbr = @curAlertNbr + 1
      end

-- Jobs
select @dmoCmd 	= 'JobServer.Jobs.Count'
exec @hr = master.dbo.sp_OAGetProperty 	@dmoServer,
					@dmoCmd,
					@jobCount OUT
if @hr <> 0 goto ErrorHandler
set @curJobNbr = 1
while @curJobNbr <= @jobCount 
	begin
	        -- get the name 
	        select @dmoCmd 	= 'JobServer.Jobs.Item(' 
					+ cast(@curJobNbr as varchar(10))
					+ ').Name'
		exec @hr = master.dbo.sp_OAGetProperty 	@dmoServer,
							@dmoCmd,
							@jobName OUT
        	if @hr <> 0 goto ErrorHandler
	        select @dmoCmd 	= 'JobServer.Jobs("' 
					+ @jobName 
					+ '").Script'
	        select @scriptFile 	= @path 
					+ 'JobServer\Jobs\' 
					+ @jobName 
					+ '.sql'
        	exec @hr = master.dbo.sp_OAMethod 	@dmoServer,
							@dmoCmd,
							NULL,
							@scriptType,
							@scriptFile
        	if @hr <> 0 goto ErrorHandler
		select @curJobNbr = @curJobNbr + 1
      end

-- operators
select @dmoCmd 	= 'JobServer.Operators.Count'
exec @hr = master.dbo.sp_OAGetProperty 	@dmoServer,
					@dmoCmd,
					@operatorCount OUT
if @hr <> 0 goto ErrorHandler
set @curOperatorNbr = 1
while @curOperatorNbr <= @operatorCount 
	begin
	        -- get the name 
	        select @dmoCmd 	= 'JobServer.Operators.Item(' 
					+ cast(@curOperatorNbr as varchar(10))
					+ ').Name'
		exec @hr = master.dbo.sp_OAGetProperty 	@dmoServer,
							@dmoCmd,
							@operatorName OUT
        	if @hr <> 0 goto ErrorHandler
	        select @dmoCmd 	= 'JobServer.Operators("' 
					+ @operatorName 
					+ '").Script'
	        select @scriptFile 	= @path 
					+ 'JobServer\Operators\' 
					+ @operatorName 
					+ '.sql'
        	exec @hr = master.dbo.sp_OAMethod 	@dmoServer,
							@dmoCmd,
							NULL,
							@scriptType,
							@scriptFile
        	if @hr <> 0 goto ErrorHandler
		select @curOperatorNbr = @curOperatorNbr + 1
      end

-- close and cleanup the COM/DMO database connection 
exec @hr = master.dbo.sp_OAMethod @dmoServer,'DisConnect'
if @hr <> 0 goto ErrorHandler

exec @hr = master.dbo.sp_OADestroy @dmoServer
if @hr <> 0 goto ErrorHandler

-- audit completion
insert Admin.dbo.ActivityLog (activity)
select @procedureName + ' completed normally'

return

ErrorHandler:
if @hr is not null
	begin
		exec master.dbo.sp_OAGetErrorInfo 	@dmoServer, 
							@errorSource OUT, 
							@errorDescription OUT
		insert Admin.dbo.ActivityLog (activity)
		select @procedureName + ' ended in error: ' + cast(@hr as varchar(20)) + '
			OLE ERROR: ' 
		--+ isnull(Admin.dbo.binToHex (@hr),'not defined') no function in 7 so forget conversion
		+ @dmoCmd + '
			Source: ' + isnull(@errorSource,'unknown') + '
			Description: ' + isnull(@errorDescription,'unknown')

		-- still need to cleanup
		exec master.dbo.sp_OAMethod @dmoServer,'DisConnect'
		exec master.dbo.sp_OADestroy @dmoServer

		raiserror (59001,16,1,@procedureName)
	end
else
	raiserror(59000,16,1,@procedureName) 

return -1


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

