SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


create procedure dbo.dmoScriptDatabase
	@pDatabaseName varchar(255),
	@pTempFolder varchar(255) = null,
        @pInstanceName varchar(30) = null  
as
/**********************************************************************************
Script database collections using DMO object model granularity. By default, scripts 
are placed in the dbChangeControl working folders for this database on this SQL 
Server instance. By providing an alternate valid path to the cloaked tempFolder parameter
the scripts can be placed in another location. Additionally, by providing an instance
name to the cloaked Instance Parameter, another instance on the local machine can be 
scripted provided it is established in the metadata tables. 

Notes. Use with dmoScriptTables to get table collections. The specified database on 
the local server must be a member of a healthy project. If an attempt is made to 
script to the working folders of a checked in project, the scripting will fail 
because the existing files are read only. Procedure will allow 
***********************************************************************************/
-- common
declare @dmoServer int,
	@path varchar(255),
	@workingFolder varchar(255),
	@cmd varchar(1200),
	@returnstatus int,
	@dmoMethod varchar(255), 
	@dmoProperty varchar(255), 
	@dmoCollection varchar(255), 
	@scriptFile varchar(255),
	@hr int,
	@hrhex char(10),
	@OleErrorSource varchar(255),
	@OleErrorDescription varchar(1000),
	@scriptType int,
	@databaseScriptType int,
	@procedureName sysname,
	@vssUserName varchar(30),
	@vssShare varchar(100),
-- defaults
	@defaultCount int,
	@curDefaultNbr int,
	@defaultName varchar(255),
-- full text catalog
	@catalogCount int,
	@curCatalogNbr int,
	@catalogName varchar(255),
-- roles
	@roleCount int,
	@curRoleNbr int,
	@roleName varchar(255),
	@isFixedRole bit,
-- rules
	@ruleCount int,
	@curRuleNbr int,
	@ruleName varchar(255),
-- stored procedures
	@storedProcedureCount int,
	@curStoredProcedureNbr int,
	@storedProcedureName varchar(255),
	@isSystemStoredProcedure bit,
	@procedureScriptType int,
-- user data types
	@dataTypeCount int,
	@curDataTypeNbr int,
	@dataTypeName varchar(255),
-- user functions
	@functionCount int,
	@curFunctionNbr int,
	@functionName varchar(255),
-- users
	@userCount int,
	@curUserNbr int,
	@userName varchar(255),
	@loginName varchar(255),
	@loginScriptType int,
	@userScriptType int,
-- views
	@viewCount int,
	@curViewNbr int,
	@viewName varchar(255),
	@isSystemView bit,
	@viewScriptType int

set nocount on

-- init
set @procedureName = db_name() + '.' 
			+ user_name(objectproperty(@@procid,'OwnerId')) 
                	+ '.' + object_name(@@procid)

-- get file system path 
select @workingFolder = s.workingFolderRoot 
			+ p.projectName + '\'
			+ e.environmentName + '\'
			+ s.serverName + '\' 
			+ case when (@pInstanceName is null) then s.instanceName 
                                else @pInstanceName
                                end
                        + '\'
			+ @pDatabaseName + '\',
	@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 = @pDatabaseName
--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 = case when (@pInstanceName is null) 
                        then substring(@@servername, 
       		       	                charindex('\',@@servername) + 1, 
               		                datalength(@@servername))
                        else @pInstanceName
                        end
and n.IsActive = 1
if @@rowcount = 0
	begin
		raiserror(59002,16,1,@procedureName, @pDatabaseName)
		goto ErrorHandler
	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

-- file system project working folder must exist
set @cmd = 'dir ' + @workingFolder
exec @returnstatus = master.dbo.xp_cmdshell @CMD, no_output
if @returnstatus <> 0 
	begin
		raiserror (59007,16,1,@procedureName, @pTempFolder,@@servername)
		goto ErrorHandler
	end

if (@pTempFolder is not null)
	begin
		-- file system temporary working folder must exist
		set @cmd = 'dir ' + @pTempFolder
		exec @returnstatus = master.dbo.xp_cmdshell @CMD, no_output
		if @returnstatus <> 0 
			begin
				raiserror (59007,16,1,@procedureName, @pTempFolder,@@servername)
				goto ErrorHandler
			end

		select @path = case when (substring(@pTempFolder,
							datalength(@pTempFolder),
							1) = '\')
					then @pTempFolder + @pDatabaseName + '\'
					else @pTempFolder + '\' + @pDatabaseName + '\'
					end 
		
		-- clear the temp area and copy the place empty hierarchy folders 
		set @cmd = 'rmdir /S /Q ' + @path 
		exec @returnstatus = master.dbo.xp_cmdshell @CMD, no_output
		-- if real error it will be handles in next step

		-- replicate working folder hierarchy to temp area
		set @CMD = 'xcopy ' + @workingFolder + '*.* ' 
			 + @path + ' /T /E /Y'
		exec @returnstatus = master..xp_cmdshell @CMD, NO_OUTPUT
		if @returnstatus <> 0 
			goto ErrorHandler
		
	end 
else
	begin
		set @path = @workingFolder

	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

-- database script type - non destructive
set @databaseScriptType 	= 4		-- primary object
				+ 64		-- to file only
				+ 4096		-- if not exists

-- new file, script create object only
set @loginScriptType           =  4		-- primary object
			        + 64		-- to file only
			        + 4096		-- if not exists

-- new file, script create object only
set @userScriptType             = 1             -- drop
                                + 4		-- primary object
			        + 64		-- to file only
                                + 256           -- append (login script will create)     
			        + 4096		-- if not exists

-- script drop object, create object, and permissions
set @procedureScriptType        = 1		-- drop 
        			+ 2		-- object permissions
	        		+ 4		-- primary object
		        	+ 32		-- database permissions
			        + 64		-- to file only
        			+ 4096		-- if not exists
	        		+ 262144	-- owner qualify

-- script drop object, create object, and permissions
set @viewScriptType 	        = 1		-- drop 
        			+ 2		-- object permissions
	        		+ 4		-- primary object
		        	+ 32		-- database permissions
			        + 64		-- to file only
        			+ 4096		-- if not exists
	        		+ 262144	-- owner qualify

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

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

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

-- script each object to a separate file
-- database
select @dmoMethod 	= 'Databases("' 
			+ @pDatabaseName 
			+ '").Script'
select @scriptFile 	= @path 
			+ @pDatabaseName 
			+ '.sql'
exec @hr = master.dbo.sp_OAMethod 	@dmoServer,
					@dmoMethod,
					NULL,
					@databaseScriptType,
					@scriptFile
if @hr <> 0 
	goto ErrorHandler

-- defaults
select @dmoProperty 	= 'Databases("' 
			+ @pDatabaseName 
			+ '").Defaults.Count'
exec @hr = master.dbo.sp_OAGetProperty 	@dmoServer,
					@dmoProperty,
					@defaultCount OUT
if @hr <> 0 
	goto ErrorHandler
set @curDefaultNbr = 1
while @curDefaultNbr <= @defaultCount 
	begin
	        -- get the name 
	        select @dmoProperty 	= 'Databases("' 
					+ @pDatabaseName 
					+ '").Defaults.Item(' 
					+ cast(@curDefaultNbr as varchar(10))
					+ ').Name'
		exec @hr = master.dbo.sp_OAGetProperty 	@dmoServer,
							@dmoProperty,
							@defaultName OUT
        	if @hr <> 0 
			goto ErrorHandler
	        select @dmoMethod 	= 'Databases("' 
					+ @pDatabaseName 
					+ '").Defaults("' 
					+ @defaultName 
					+ '").Script'
	        select @scriptFile 	= @path 
					+ 'Defaults\' 
					+ @defaultName 
					+ '.sql'
        	exec @hr = master.dbo.sp_OAMethod 	@dmoServer,
							@dmoMethod,
							NULL,
							@scriptType,
							@scriptFile
        	if @hr <> 0 
			goto ErrorHandler
		select @curDefaultNbr = @curDefaultNbr + 1
      end

-- full text catalogs
select @dmoProperty 	= 'Databases("' 
			+ @pDatabaseName 
			+ '").FullTextCatalogs.Count'
exec @hr = master.dbo.sp_OAGetProperty 	@dmoServer,
					@dmoProperty,
					@catalogCount OUT
if @hr <> 0 
	goto ErrorHandler
set @curCatalogNbr = 1
while @curCatalogNbr <= @catalogCount 
	begin
	        -- get the name 
	        select @dmoProperty 	= 'Databases("' 
					+ @pDatabaseName 
					+ '").FullTextCatalogs.Item(' 
					+ cast(@curCatalogNbr as varchar(10))
					+ ').Name'
		exec @hr = master.dbo.sp_OAGetProperty 	@dmoServer,
							@dmoProperty,
							@catalogName OUT
        	if @hr <> 0 
			goto ErrorHandler
	        select @dmoMethod 	= 'Databases("' 
					+ @pDatabaseName 
					+ '").FullTextCatalogs("' 
					+ @catalogName 
					+ '").Script'
	        select @scriptFile 	= @path 
					+ 'FullTextCatalogs\' 
					+ @catalogName 
					+ '.sql'
        	exec @hr = master.dbo.sp_OAMethod 	@dmoServer,
							@dmoMethod,
							NULL,
							@scriptType,
							@scriptFile
        	if @hr <> 0 
			goto ErrorHandler
		select @curCatalogNbr = @curCatalogNbr + 1
      end

-- roles
select @dmoProperty 	= 'Databases("' 
			+ @pDatabaseName 
			+ '").DatabaseRoles.Count'
exec @hr = master.dbo.sp_OAGetProperty 	@dmoServer,
					@dmoProperty,
					@roleCount OUT
if @hr <> 0 
	goto ErrorHandler
set @curRoleNbr = 1
while @curRoleNbr <= @RoleCount 
	begin
		-- fixed roles cannot be removed so don't try to script
	        select @dmoProperty 	= 'Databases("' 
					+ @pDatabaseName 
					+ '").DatabaseRoles.Item(' 
					+ cast(@curRoleNbr as varchar(10))
					+ ').IsFixedRole'
		exec @hr = master.dbo.sp_OAGetProperty 	@dmoServer,
							@dmoProperty,
							@isFixedRole OUT
        	if @hr <> 0 
			goto ErrorHandler
	        -- get the name 
       		select @dmoProperty 	= 'Databases("' 
					+ @pDatabaseName 
					+ '").DatabaseRoles.Item(' 
					+ cast(@curRoleNbr as varchar(10))
					+ ').Name'
		exec @hr = master.dbo.sp_OAGetProperty 	@dmoServer,
							@dmoProperty,
							@roleName OUT
		if @hr <> 0 
			goto ErrorHandler
		if @isFixedRole = 0 and @roleName <> 'Public'
			begin

			        select @dmoMethod 	= 'Databases("' 
							+ @pDatabaseName 
							+ '").DatabaseRoles("' 
							+ @roleName
							+ '").Script'
			        select @scriptFile 	= @path 
							+ 'DatabaseRoles\' 
							+ @roleName 
							+ '.sql'
        			exec @hr = master.dbo.sp_OAMethod 	@dmoServer,
									@dmoMethod,
									NULL,
									@scriptType,
									@scriptFile
		        	if @hr <> 0 
					goto ErrorHandler
			end
		select @curRoleNbr = @curRoleNbr + 1
      end

-- rules
select @dmoProperty 	= 'Databases("' 
			+ @pDatabaseName 
			+ '").Rules.Count'
exec @hr = master.dbo.sp_OAGetProperty 	@dmoServer,
					@dmoProperty,
					@ruleCount OUT
if @hr <> 0 	
	goto ErrorHandler
set @curRuleNbr = 1
while @curRuleNbr <= @RuleCount 
	begin
	        -- get the name 
	        select @dmoProperty 	= 'Databases("' 
					+ @pDatabaseName 
					+ '").Rules.Item(' 
					+ cast(@curRuleNbr as varchar(10))
					+ ').Name'
		exec @hr = master.dbo.sp_OAGetProperty 	@dmoServer,
							@dmoProperty,
							@ruleName OUT
        	if @hr <> 0 
			goto ErrorHandler
	        select @dmoMethod 	= 'Databases("' 
					+ @pDatabaseName 
					+ '").Rules("' 
					+ @ruleName 
					+ '").Script'
	        select @scriptFile 	= @path 
					+ 'Rules\' 
					+ @ruleName 
					+ '.sql'
        	exec @hr = master.dbo.sp_OAMethod 	@dmoServer,
							@dmoMethod,
							NULL,
							@scriptType,
							@scriptFile
        	if @hr <> 0 
			goto ErrorHandler
		select @curRuleNbr = @curRuleNbr + 1
      end

-- stored procedures
select @dmoProperty 	= 'Databases("' 
			+ @pDatabaseName 
			+ '").StoredProcedures.Count'
exec @hr = master.dbo.sp_OAGetProperty 	@dmoServer,
					@dmoProperty,
					@storedProcedureCount OUT
if @hr <> 0 
	goto ErrorHandler
set @curStoredProcedureNbr = 1
while @curStoredProcedureNbr <= @StoredProcedureCount 
	begin
		select @dmoProperty 	= 'Databases("' 
					+ @pDatabaseName 
					+ '").StoredProcedures.Item(' 
					+ cast(@curStoredProcedureNbr as varchar(5)) 
					+ ').SystemObject'        
		exec @hr = master..sp_OAGetProperty 	@dmoServer,
							@dmoProperty,
							@isSystemStoredProcedure OUT
	        if @hr <> 0 
			goto ErrorHandler
		if @isSystemStoredProcedure = 0
			begin
			        -- get the name 
			        select @dmoProperty 	= 'Databases("' 
							+ @pDatabaseName 
							+ '").StoredProcedures.Item(' 
							+ cast(@curStoredProcedureNbr as varchar(10))
							+ ').Name'
				exec @hr = master.dbo.sp_OAGetProperty 	@dmoServer,
									@dmoProperty,
									@storedProcedureName OUT
		        	if @hr <> 0 
					goto ErrorHandler
	        		select @dmoMethod 	= 'Databases("' 
							+ @pDatabaseName 
							+ '").StoredProcedures("' 
							+ @StoredProcedureName 
							+ '").Script'
	        		select @scriptFile 	= @path 
							+ 'StoredProcedures\' 
							+ @StoredProcedureName 
							+ '.sql'
		        	exec @hr = master.dbo.sp_OAMethod 	@dmoServer,
									@dmoMethod,
									NULL,
									@ProcedureScriptType,
									@scriptFile
        			if @hr <> 0 
					goto ErrorHandler
			end

		select @curStoredProcedureNbr = @curStoredProcedureNbr + 1
      end

-- user data types
select @dmoProperty 	= 'Databases("' 
			+ @pDatabaseName 
			+ '").UserDefinedDataTypes.Count'
exec @hr = master.dbo.sp_OAGetProperty 	@dmoServer,
					@dmoProperty,
					@dataTypeCount OUT
if @hr <> 0 
	goto ErrorHandler
set @curDataTypeNbr = 1
while @curDataTypeNbr <= @dataTypeCount 
	begin
	        -- get the name 
	        select @dmoProperty 	= 'Databases("' 
					+ @pDatabaseName 
					+ '").UserDefinedDataTypes.Item(' 
					+ cast(@curDataTypeNbr as varchar(10))
					+ ').Name'
		exec @hr = master.dbo.sp_OAGetProperty 	@dmoServer,
							@dmoProperty,
							@dataTypeName OUT
        	if @hr <> 0 
			goto ErrorHandler
       		select @dmoMethod 	= 'Databases("' 
					+ @pDatabaseName 
					+ '").UserDefinedDataTypes("' 
					+ @dataTypeName 
					+ '").Script'
       		select @scriptFile 	= @path 
					+ 'UserDefinedDataTypes\' 
					+ @dataTypeName 
					+ '.sql'
        	exec @hr = master.dbo.sp_OAMethod 	@dmoServer,
							@dmoMethod,
							NULL,
							@scriptType,
							@scriptFile
     		if @hr <> 0 
			goto ErrorHandler
		select @curDataTypeNbr = @curDataTypeNbr + 1
      end


-- user functions (sql2000 or greater)
/*
select @dmoProperty 	= 'Databases("' 
			+ @pDatabaseName 
			+ '").UserDefinedFunctions.Count'
exec @hr = master.dbo.sp_OAGetProperty 	@dmoServer,
					@dmoProperty,
					@functionCount OUT
if @hr <> 0 
	goto ErrorHandler
set @curFunctionNbr = 1
while @curFunctionNbr <= @functionCount 
	begin
	        -- get the name 
	        select @dmoProperty 	= 'Databases("' 
					+ @pDatabaseName 
					+ '").UserDefinedFunctions.Item(' 
					+ cast(@curFunctionNbr as varchar(10))
					+ ').Name'
		exec @hr = master.dbo.sp_OAGetProperty 	@dmoServer,
							@dmoProperty,
							@functionName OUT
        	if @hr <> 0 
			goto ErrorHandler
       		select @dmoMethod 	= 'Databases("' 
					+ @pDatabaseName 
					+ '").UserDefinedFunctions("' 
					+ @functionName 
					+ '").Script'
       		select @scriptFile 	= @path 
					+ 'UserDefinedFunctions\' 
					+ @functionName 
					+ '.sql'
        	exec @hr = master.dbo.sp_OAMethod 	@dmoServer,
							@dmoMethod,
							NULL,
							@scriptType,
							@scriptFile
     		if @hr <> 0 
			goto ErrorHandler
		select @curFunctionNbr = @curFunctionNbr + 1
      end
*/

-- users
select @dmoProperty 	= 'Databases("' 
			+ @pDatabaseName 
			+ '").Users.Count'
exec @hr = master.dbo.sp_OAGetProperty 	@dmoServer,
					@dmoProperty,
					@userCount OUT
if @hr <> 0 
	goto ErrorHandler
set @curUserNbr = 1
while @curUserNbr <= @userCount 
	begin
	        	-- get the name 
	 	select @dmoProperty 	= 'Databases("' 
					+ @pDatabaseName 
					+ '").Users.Item(' 
					+ cast(@curUserNbr as varchar(10))
					+ ').Name'
		exec @hr = master.dbo.sp_OAGetProperty 	@dmoServer,
							@dmoProperty,
							@userName OUT
	        	if @hr <> 0 
			goto ErrorHandler
		if @userName <> 'guest'
			begin
				-- get the login name
			        select @dmoProperty 	= 'Databases("' 
							+ @pDatabaseName 
							+ '").Users.Item(' 
							+ cast(@curUserNbr as varchar(10))
							+ ').Login'
				exec @hr = master.dbo.sp_OAGetProperty @dmoServer, 
									@dmoProperty, 
									@loginName OUT
		        		if @hr <> 0 
					goto ErrorHandler
				-- start the file with the login script but do not drop existing
				if @loginName is not null
					begin
       						select @scriptFile 	= @path 
									+ 'Users\' 
									+ replace(@userName ,'\','~')
									+ '.sql'
				       		select @dmoMethod 	= 'Logins("' 
									+ @loginName 
									+ '").Script'
					        	exec @hr = master.dbo.sp_OAMethod 	
										@dmoServer, 
										@dmoMethod,
										NULL,
										@loginScriptType,
										@scriptFile
		     				if @hr <> 0 
							goto ErrorHandler
					end
				-- append the user script
	       			select @dmoMethod 	= 'Databases("' 
							+ @pDatabaseName 
							+ '").Users("' 
							+ @userName 
							+ '").Script'
	       			select @scriptFile 	= @path 
							+ 'Users\' 
							+ replace(@userName ,'\','~')
							+ '.sql'
			        	exec @hr = master.dbo.sp_OAMethod
									@dmoServer,
									@dmoMethod,
									NULL,
									@userScriptType,
									@scriptFile
		     		if @hr <> 0 
					goto ErrorHandler
			end
		select @curUserNbr = @curUserNbr + 1
	end


-- views
select @dmoProperty 	= 'Databases("' 
			+ @pDatabaseName 
			+ '").Views.Count'
exec @hr = master.dbo.sp_OAGetProperty 	@dmoServer,
					@dmoProperty,
					@viewCount OUT
if @hr <> 0 
	goto ErrorHandler
set @curViEwNbr = 1
while @curViewNbr <= @viewCount 
	begin
		select @dmoProperty 	= 'Databases("' 
					+ @pDatabaseName 
					+ '").Views.Item(' 
					+ cast(@curViewNbr as varchar(5)) 
					+ ').SystemObject'        
		exec @hr = master..sp_OAGetProperty 	@dmoServer,
							@dmoProperty,
							@isSystemView OUT
	        if @hr <> 0 
			goto ErrorHandler
		if @isSystemView = 0
			begin
			        -- get the name 
			        select @dmoProperty 	= 'Databases("' 
							+ @pDatabaseName 
							+ '").Views.Item(' 
							+ cast(@curViewNbr as varchar(10))
							+ ').Name'
				exec @hr = master.dbo.sp_OAGetProperty 	@dmoServer,
									@dmoProperty,
									@viewName OUT
		        	if @hr <> 0 
					goto ErrorHandler
       				select @dmoMethod 	= 'Databases("' 
							+ @pDatabaseName 
							+ '").Views("' 
							+ @viewName 
							+ '").Script'
		       		select @scriptFile 	= @path 
							+ 'Views\' 
							+ @viewName 
							+ '.sql'
        			exec @hr = master.dbo.sp_OAMethod 	@dmoServer,
									@dmoMethod,
									NULL,
									@viewScriptType,
									@scriptFile
		     		if @hr <> 0 
					goto ErrorHandler
			end
		select @curViewNbr = @curViewNbr + 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 + ' ' + @pDatabaseName 
	+ isnull(@pTempFolder,'') + ' completed normally'

return

ErrorHandler:
if (@hr is not null)
	begin	
		exec master.dbo.sp_OAGetErrorInfo 	@dmoServer, 
							@OleErrorSource OUT, 
							@OleErrorDescription OUT
		insert Admin.dbo.ActivityLog (activity)
		select @procedureName + ' ' + @pDatabaseName 
			+ ' ended with error: ' + cast(@hr as varchar(20)) + '
			OLE ERROR: ' 
		--+ isnull(Admin.dbo.binToHex (@hr),'not defined') no function in 7 so skip conversion of error number
		+ cast(@hr as varchar(20)) + '
			Source: ' + isnull(@OleErrorSource,'unknown') + '
			Description: ' + isnull(@OleErrorDescription,'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
	if @cmd is not null
		begin
			insert Admin.dbo.ActivityLog (activity)
			select @procedureName + ' ' + @pDatabaseName 
				+ ' ' + isNull(@pTempFolder,'') 
				+ ' failed with returnstatus ' 
				+ cast(@returnstatus as varchar(10)) + ' at: ' + @cmd
			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

