-- Stored Procedure: dbo.ScriptLoad
-- Bill WUnder

use admin
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


CREATE PROCEDURE [dbo].[ScriptLoad] 
 @pServerName varchar(128),
 @pDatabaseName varchar(128),
 @pWorkingFolder varchar(128),
 @pScriptName varchar(128) = null
AS
/*******************************************************************************************************
* admin.dbo.ScriptLoad 
* Creator:      Bill Wunder
* Date:   2-21-2003
*
* Project:      
* Project Mgr: 
* Dev Contact: 
*
* Description: Load one specified file or all files in the specified folder if no file name
   is specified into the specified SQL Server and database as a t-sql script
* Notes:      
*
* Usage:
      EXECUTE ScriptLoad 'DEV_ZEUS','admin','h:\vss\ZEUS\admin\Procedures','dbo.ScriptLoad.PRC'
*
*
* Modifications:   
*         Developer Name     Date     Brief Description
*         ------------------ -------- ------------------------------------------------------------
*
********************************************************************************************************/

---------------------------------------------
-- declare variables
---------------------------------------------
declare @Cmd nvarchar(1200),
 @FileName varchar(128),
 @ReturnStatus int

---------------------------------------------
-- create temp tables
---------------------------------------------
create table #fileList (fileName varchar(255))

---------------------------------------------
-- set session
---------------------------------------------
SET NOCOUNT ON

---------------------------------------------
-- body of stored procedure
---------------------------------------------

-- file system working folder for the database must exist
set @Cmd = 'dir ' + @pWorkingFolder
exec @ReturnStatus = master.dbo.xp_cmdshell @CMD, no_output
if @ReturnStatus <> 0 
 begin
  print 'Folder ' + @pWorkingFolder + ' not found'
  return - 1
 end

--load all files in folder as scripts
if @pScriptName is null
 begin
  -- get list of all files in the working folder
  set @Cmd = 'dir ' + @pWorkingFolder + ' /A /B'
  insert #filelist exec @ReturnStatus = master.dbo.xp_cmdshell @Cmd
  if @ReturnStatus <> 0
   begin
    print 'Error building list of files to load'
    return - 1
   end
  -- 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"' + @pWorkingFolder + @FileName 
      + '" -b -n'
    exec @ReturnStatus = master.dbo.xp_cmdshell @Cmd
    if @ReturnStatus <> 0
     begin
      -- try again and dump errors to screen
      exec @ReturnStatus = master.dbo.xp_cmdshell @Cmd--, no_output
      if @ReturnStatus = 1
       select 'Attempt to run script ' + @FileName 
        + ' failed with returnstatus ' 
        + cast(@ReturnStatus as varchar(10)) + char(13) + char(10) 
        + 'command line: ' + @Cmd
     end 
   
    fetch next from cur into @FileName
   end

  close cur
  deallocate cur

 end
else -- just one script file
 begin
  -- file must exist
  if substring(@pWorkingFolder,datalength(@pWorkingFolder),1) <> '\'
   set @pWorkingFolder = @pWorkingFolder + '\'
  select @Cmd = 'dir ' + @pWorkingFolder + @pScriptName
  exec @ReturnStatus = master.dbo.xp_cmdshell @Cmd, no_output
  if @ReturnStatus <> 0 
   begin
    print 'Folder ' + @pWorkingFolder + ' not found'
    return - 1
   end

  print 'echo processing script ' + @FileName +'...' 
  set @Cmd = 'osql -E -S ' + @@servername 
    + ' -d' + @pDatabaseName 
    + ' -i"' + @pWorkingFolder + @FileName 
    + '" -b -n'
  exec @ReturnStatus = master.dbo.xp_cmdshell @Cmd
  if @ReturnStatus <> 0
   begin
    select 'Attempt to run script ' + @FileName 
     + ' failed with returnstatus ' 
     + cast(@ReturnStatus as varchar(10)) + char(13) + char(10)
     + 'command line: ' + @Cmd
   end 
 end

return






GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON 
GO