SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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



create procedure dbo.adminLoadScripts 
	@pServerName varchar(60),
	@pDatabaseName varchar(30),
	@pCollection varchar(30)
as
/****************************************************************************
Loads all scripts found from the local working folder defined by @scriptgroup 
of the specified server and database into that database on the local system. 
Notes: Both the local server and the specified server must be member servers
in the project that owns the database and bothe servers must already be added
to the local dbChangeControl metadata hierarchy. Will ot work for scripts 
located directly in the database's named folder (i.e. Admin\admin.sql)
*****************************************************************************/
declare @cmd nvarchar(1200),
	@returnstatus int,
	@procedureName varchar(100),
	@serverName varchar(50),
	@instanceName varchar(50),
	@workingFolder varchar(255),
	@vssHierarchy varchar(255),
	@vssShare varchar(255),
	@vssUserName varchar(30),
	@fileName varchar(255),
	@rowcount int

set nocount on

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

set @serverName = reverse(substring(reverse(@pServername), 
				charindex('\',reverse(@pServername)) + 1, 
                        	datalength(reverse(@pServername))))

set @instanceName = substring(@pServername, 
               	                charindex('\',@pServername) + 1, 
                                datalength(@pServername))

-- get file system path 
select 	@workingFolder = s.workingFolderRoot
			+ p.projectName + '\'
			+ e.environmentName + '\'
			+ s.serverName + '\' 
			+ s.instanceName + '\'
			+ @pDatabaseName + '\'
			+ @pCollection + '\',
	@vssUserName = n.vssUserName
from Admin.dbo.SqlDatabase d 
inner join Admin.dbo.ProjectNode n
on d.id = n.sqlDatabase_id
inner join Admin.dbo.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
inner join Admin.dbo.ProjectNode l -- local  
on (	n.project_id = l.project_id
	and n.sqlDatabase_id = l.sqlDatabase_id)
where d.databaseName = @pDatabaseName
and s.serverName = @serverName
and s.instanceName = @instanceName
and n.IsActive = 1
--and l.sqlServer_id = Admin.dbo.thisSQLServer_id() cannot use function in SQL Server 7
and l.sqlServer_id = (select id from Admin.dbo.SqlServer
        	                    	where serverName = reverse(substring(reverse(@@servername), 
                	                                            charindex('\',reverse(@@servername)) + 1, 
                        	                                    datalength(reverse(@@servername))))
					and instanceName = substring(@@servername, 
        	                               	                    charindex('\',@@servername) + 1, 
                                                	            datalength(@@servername)))
and l.IsActive = 1

-- valid project node must exist 
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))
		raiserror (59005,16,1, @procedurename, 	@vssUserName, @vssShare)
		goto ErrorHandler
	end

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

-- vss project must exist - must be created by vss admin 
-- NOTE! if you put a password on vss, this will hang (tries to prompt for password)
set @cmd = @vssShare + 'win32\ss Cp ' 
         + @vssHierarchy + ' -I-N -Y' + @vssUserName 
exec @returnstatus = master.dbo.xp_cmdshell @cmd, no_output
if @returnstatus <> 0 
	begin
		raiserror(59008,16,1,@procedureName, @vssHierarchy, @vssShare)
		goto ErrorHandler
	end

-- see if any files are checked out in this project
set @Cmd = @vssShare + 'win32\ss Status ' + @vssHierarchy 
	 + '\ -I-N -R -Y' + @vssUSerName
exec @returnstatus = master..xp_cmdshell @Cmd, NO_OUTPUT
if @returnstatus <> 0 
	begin
		if @returnstatus = 1
			raiserror(59010,16,1,@procedureName, @vssHierarchy)
		else
			-- something else must have happened
			goto ErrorHandler
	end


-- get list of all files in the working folder
create table #fileList (fileName varchar(255))
set @Cmd = 'dir ' + @WorkingFolder + ' /A /B'
insert #fileList exec @returnstatus = master.dbo.xp_cmdshell @Cmd
if @returnstatus <> 0
	goto ErrorHandler
-- a little clean up of the list
delete #fileList where fileName not like '%.sql' or fileName is null

update #fileList 
	set FileName = reverse(substring(reverse(fileName), 
			       1,	
		 	       datalength(filename) - charindex('\',reverse(fileName))))

-- run all scripts and log the results to a file
declare cur cursor for
select FileName from #fileList

open cur
fetch next from cur into @fileName
while @@fetch_status = 0
	begin		
		print 'echo processing script ' + @fileName +'...' 
		set @Cmd = 'osql -E -S ' + @@servername 
			 + ' -d' + @pDatabaseName 
			 + ' -i"' + @workingFolder + @fileName 
			 + '" -b -n'
		exec @returnstatus = master.dbo.xp_cmdshell @Cmd, no_output
		if @returnstatus <> 0
			begin
				-- try again and dump errors to screen
				exec @returnstatus = master.dbo.xp_cmdshell @Cmd--, no_output
				if @returnstatus = 1
					--audit
					insert Admin.dbo.ActivityLog (activity)
					select @procedureName + ' ' + @pDatabaseName 
						+ 'attempt to run script ' + @fileName 
						+ ' failed with returnstatus ' 
						+ cast(@returnstatus as varchar(10)) 
						+ ' at: ' + @cmd
			end	
			
		fetch next from cur into @FileName
	end

drop table #FileList
close cur
deallocate cur

return

ErrorHandler:
if (@Cmd is not null)
	begin
		-- audit
		insert Admin.dbo.ActivityLog (activity)
		select @procedureName + ' ' + @pDatabaseName + ' failed with returnstatus ' 
			+ cast(@returnstatus as varchar(10)) + ' at: 
			' + @cmd
		raiserror(59001,16,1,@procedureName)
	end
else
		raiserror(59000,16,1,@procedureName)

if (cursor_status('local','cur') >= 0)
	deallocate cur

return -1



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO