Script and Export (via BCP) SQL Tables

if exists (select * from sysobjects where id = object_id('usp_Export') and type = 'p')
drop procedure usp_Export
go

CREATE PROCEDURE usp_Export (
@iObjectName varchar(256) = '!@#$',
@iUNCPath varchar(256) = '!@#$',
@iExportType tinyint = 0,
@iScriptType int = 0,
@iDelim varchar(5) = '~',
@iOutName varchar(54) = '!@#$')
WITH RECOMPILE
AS
/********************************************************************************//* usp_Export - Script and Export (BCP) SQL Server Objects*//* *//* OLE Automation derived from Script @ http://www.umachandar.com/resources.htm*//* Combination w/BCP & finalization by Mike Mortensen (mrhappi@sbcglobal.net)*//********************************************************************************//*Version History                                                        *//*v1.8.0- 20010621 - first release, tables only*//*v1.8.1- 20010702 - removed dbname requirement*//*v1.8.2- 20010702 - integrated security*//*v1.8.3- 20011001 - add Native Type export*//*v1.8.4- 20011022 - add Script2Type = 8388608, prevent collation info*//*v1.9.0- 20021009 - change output string*//*v1.9.1- 20021126 - some changes*//*v1.9.2- 20030114 - add special handling for pipe (|) delimiter*//*v2.0.0- 20031108 - if mismatch count, run DBCC UPDATEUSAGE*//**//********************************************************************************//*Inputs*//*Object(table/view) (@iObjectName),*//*UNCPath (@iUNCPath), ExportType (@iExportType),*//*ScriptType (@iScriptType), SQLUser (@user), SQLPwd (@pwd),*//*Delimiter (@iDelim), OutputFileName (@iOutName)*//**
Method:Step 1 - Validate User Input
Step 2 - Create Script using OLE Automation (if applicable)
Step 3 - Export using xp_cmdshell bcp sending output to table
- report counts & errors if any

This part of documentation is incomplete - 20011001
Vars:Input:@iObjectName, @iUNCPath, @iExportType, @iScripType, @iDelim, @iOutName
Work:string: @vcInfo, @vcERRpos
**/
SET NOCOUNT ON
DECLARE @local int, @db int, @table int, @retcode int, @method varchar(255),
@ErrSource varchar(255), @ErrDesc varchar(255), @output varchar(1000),
@vcInfo varchar(800), @vcERRpos varchar(50), @OAoutput varchar(8000),
@vcScrFile varchar(128), @vcBCPFile varchar(128),
@vcScrOut varchar(384), @vcBCPOut varchar(384), @vcERROut varchar(384),
@vcTempTableName varchar(128),
@vcdbname varchar(128),
@vcBCPString varchar(384),
@vcSQL varchar(500),
--@vcTest varchar(8000),
@vcObjectRowCount varchar(20), @vcBCPRowCount varchar(20),
@SQLDMOScript_Drops int,
@SQLDMOScript_ObjectPermissions int,
@SQLDMOScript_Default int,
@SQLDMOScript_ToFileOnly int,
@SQLDMOScript_Indexes int,
@SQLDMOScript_DRI_All int,
@StartTime datetime,
@TotalTime int

-- set globals
SELECT@SQLDMOScript_Drops = 1,
@SQLDMOScript_ObjectPermissions = 2,
@SQLDMOScript_Default = 4,
@SQLDMOScript_ToFileOnly = 64,
@SQLDMOScript_Indexes = 73736,
@SQLDMOScript_DRI_All = 532676608,
@vcdbname = db_name()

SET @vcInfo ='
.-= usp_Export v2.0.0 - Script table =-.

Usage:usp_Export <table>, <Directory in UNC>, <ExportType>, <scriptType>, <Delimiter>, <TargetFilename>
Def:''!@#$'', ''!@#$'', 0, 0, ''~'', ''!@#$''

ExportType Reference (Default Setting = 0)
0 = Script and BCPOut
1 = Script and BCPOut Native Type
2 = Script Only
3 = BCPOut only

ScriptType Reference (Default Setting (0) = 73805):
@SQLDMOScript_Drops = 1
@SQLDMOScript_ObjectPermissions = 2
@SQLDMOScript_Default = 4
@SQLDMOScript_ToFileOnly = 64
@SQLDMOScript_Indexes = 73736
@SQLDMOScript_DRI_All = 532676608

Example1: usp_Export mem_unq, ''c:\client\archive\fl''
Example2: usp_Export mem_unq, ''\\server\share\path''
Example3: usp_Export mem_unq, '''', 2, 5  <-- script to results window
'

/*================================================= Step 1 - Validation ====*/SELECT @StartTime = getdate()

/** give info if no params **/--if @vcdbname = '!@#$'  BEGINPRINT @vcInfo RETURN END
if @iObjectName = '!@#$' 
  BEGINPRINT @vcInfo RETURN END
if @iUNCPath = '!@#$' 
  BEGINPRINT @vcInfo RETURN END
if @iScriptType = 0 AND @iUNCPath = '!@#$' 
  BEGINPRINT @vcInfo RETURN END

/** check for table **/SET @vcERRpos = 'pos: Table Check'
EXEC('if not exists (select * from ' + @vcdbname + '..sysobjects where id = object_id(''' + @iObjectName + '''))
RAISERROR (''Object does not Exist --> ' + @vcdbname + '..' + @iObjectName + '...ABORTING...'', 11, 1 )')

if @@ERROR <> 0 BEGIN print @vcInfo+@vcERRpos RETURN END

/** check @iUNCPath format, set @vcScr/BCPFile, set @vcScr/BCPOut, set @vcTempTableName **/if @iOutName = '!@#$'
  begin
SET @iOutName = @iObjectName
  end

SET @vcScrFile = @iOutName + '.scr'
SET @vcBCPFile = @iOutName + '.txt'

if @iUNCPath <> '!@#$'
  begin
if right(@iUNCPath,1) <> '\' SET @iUNCPath = @iUNCPath + '\'
  end

SET @vcScrOut = @iUNCPath + @vcScrFile
SET @vcBCPOut = @iUNCPath + @vcBCPFile
SET @vcERROut = @iUNCPath + @iOutName + '.err'

SET @vcTempTableName = '##Export_' + @vcdbname + '_' + @iObjectName

/** build default scripttype number if not specified **/if @iScriptType = 0
  begin
set @iScriptType = @SQLDMOScript_Default + @SQLDMOScript_Drops + @SQLDMOScript_Indexes + @SQLDMOScript_ToFileOnly
  end

/** check scripttype/UNCPath relationship **/if (@iScriptType > 64 AND @iUNCPath = '!@#$')
  begin
PRINT 'INFO: The specified script type may require a UNCPath'
  end

/*================================================= Step 2 - Open OLE Automation ====*/if @iExportType <= 2
BEGIN
/** inform user what's happening **/set @vcInfo = 'usp_Export - Scripted - (' + @iObjectName + ')'
if @vcScrOut <> '' PRINT @vcInfo + ' --> (' + @vcScrOut + ')'
else PRINT @vcInfo

-- create Server object
EXEC @retcode = sp_OACreate 'SQLDMO.SQLServer', @local OUT
IF @retcode <> 0 GOTO ErrorHandler

-- connect to Server (local) (may change to @@SERVERNAME)
-- if using integrated sec, set SQLServer.LoginSecure = True
EXEC @retcode = sp_OASetProperty @local, 'LoginSecure', 'True'
IF @retcode <> 0 GOTO ErrorHandler

-- non-integrated security : SET @method = 'Connect("(local)", "' + @user + '", "' + @pwd + '")'
SET @method = 'Connect("' + @@SERVERNAME + '")'
EXEC @retcode = sp_OAMethod @local, @method
IF @retcode <> 0 GOTO ErrorHandler

-- connect to database (@vcdbname)
SET @method = 'Databases("' + @vcdbname + '")'
EXEC @retcode = sp_OAMethod @local, @method, @db OUT
IF @retcode <> 0 GOTO ErrorHandler

-- connect to table (@iObjectName)
SET @method = 'Tables("' + @iObjectName + '")'
EXEC @retcode = sp_OAMethod @db, @method, @table OUT
IF @retcode <> 0 GOTO ErrorHandler

-- build script method string
-- the 8388608 value for Script2Type removes collation crap from scripts
if @vcScrOut <> '!@#$'
SET @method = 'Script(' + ltrim(STR(@iScriptType)) + ', "' + @vcScrOut + '", ,8388608)'
else
SET @method = 'Script(' + ltrim(STR(@iScriptType)) + ', , ,SQLDMOScript2_NoCollation )'

EXEC @retcode = sp_OAMethod @table, @method, @OAOutput OUT
IF @retcode <> 0 GOTO ErrorHandler

/** print results if applicable **/if NOT (isnull(@OAOutput,'')='') PRINT char(13) + '/**=== Begin Results ==========' + char(13) + @OAOutput + '========== End Results ===**/'
GOTO Cleanup

/*================================================= ErrorHandler ====*/ErrorHandler:
EXEC @retcode = sp_OAGetErrorInfo @local, @ErrSource OUT, @ErrDesc OUT
IF @retcode = 0
  BEGIN
    SELECT @output = 'OLE Automation Error ' + char(13) +
 'Source: ' + @ErrSource + char(13) +
 'Description: ' + @ErrDesc
    PRINT @output
  END
ELSE
  BEGIN
PRINT '  sp_OAGetErrorInfo failed.'
    RETURN
  END


/*================================================= Step 2 - Cleanup ====*/Cleanup:
EXEC sp_OADestroy @local
END
/*================================================= Step 3 - Export ====*/SET @vcERRpos = 'pos: Step 3 Export'
if @iExportType IN (0,1,3)
BEGIN
-- create temp table for output
exec('if exists (select id from tempdb..sysobjects where name = ''' + @vcTempTableName + ''') DROP TABLE ' + @vcTempTableName)
exec('CREATE TABLE ' + @vcTempTableName + ' (id int identity(1,1), BCPRowCount varchar(255))')
if @@ERROR <> 0 BEGIN print @vcERRpos + '1.0 BCP Out' RETURN END
-- export, insert output to temp table
select @vcBCPString = 'master..xp_cmdshell ''''bcp ' + @vcdbname + '..[' + @iObjectName + '] out "' + @vcBCPOut + '" -e"' + @vcERROut + 
CASE @iExportType
WHEN 1 THEN '" -n'
ELSE '" -c -t' + CASE @iDelim
WHEN '|' THEN '"|"'
ELSE @iDelim
END
END +
' -T -S' + @@servername + ' -a8192'''''
exec('INSERT ' + @vcTempTableName + ' EXEC(''' + @vcBCPString + ''')')
if @@ERROR <> 0 BEGIN print @vcERRpos + '1.1 BCP Out' RETURN END

-- create temp table for data storage
if exists (select id from tempdb..sysobjects where name = '##Export_WrkTbl') DROP TABLE ##Export_WrkTbl
CREATE TABLE ##Export_WrkTbl (SPItem varchar(15) NULL, SPvc varchar(30) null, SPint int null)

-- get the # of rows exported, sql 2000 includes a null row
SET @vcSQL = 'INSERT INTO ##Export_WrkTbl SELECT ''BCPRowCount'', LEFT(BCPRowCount, charindex('' '', BCPRowCount) - 1), NULL from ' + @vcTempTableName + ' where id = (select max(id) - '
IF (select substring(@@version, 23, 4)) = '2000'
set @vcSQL = @vcSQL + '3 from ' + @vcTempTableName + ')'
ELSE
set @vcSQL = @vcSQL + '2 from ' + @vcTempTableName + ')'
exec(@vcSQL)
if @@ERROR <> 0 BEGIN print @vcERRpos + '1.2 BCP Out' RETURN END

-- get the # of rows in original table
set @vcSQL = 'INSERT INTO ##Export_WrkTbl SELECT ''ObjectRowCount'', CAST(Rows as varchar), NULL FROM sysindexes where id = object_id(''' + @iObjectName + ''') and indid IN (0,1)'
exec(@vcSQL)

-- get counts for output
select @vcBCPRowCount = SPvc FROM ##Export_WrkTbl WHERE SPItem = 'BCPRowCount'
select @vcObjectRowCount = SPvc FROM ##Export_WrkTbl WHERE SPItem = 'ObjectRowCount'

-- check row counts, display output
select @TotalTime = datediff(ms, @StartTime, getdate())


IF @vcBCPRowCount <> @vcObjectRowCount  -- if error, updates sysindexes with DBCC UPDATEUSAGE
  begin
exec('DBCC UPDATEUSAGE (0, ''' + @iObjectName + ''') WITH COUNT_ROWS, NO_INFOMSGS')
truncate table ##Export_WrkTbl
set @vcSQL = 'INSERT INTO ##Export_WrkTbl SELECT ''ObjectRowCount'', CAST(Rows as varchar), NULL FROM sysindexes where id = object_id(''' + @iObjectName + ''') and indid IN (0,1)'
exec(@vcSQL)
select @vcObjectRowCount = SPvc FROM ##Export_WrkTbl WHERE SPItem = 'ObjectRowCount'
  end
IF @vcBCPRowCount <> @vcObjectRowCount
  begin
set @vcInfo = 'Unmatched SQL and BCP counts. Refer to ' + @vcERROut
RAISERROR(@vcInfo, 11, 1)
set @vcInfo = 'SQLCount = ' + @vcObjectRowCount + ' : BCPCount = ' + @vcBCPRowCount
  end
ELSE
SET @vcInfo = 'usp_Export - Exported - (' + @iObjectName + ') --> (' + @vcBCPOut + ')  Time(sec) = ' + convert(varchar, convert(money, @TotalTime)/1000) + char(9) + 'Rows = ' + ltrim(STR(@vcBCPRowCount))

PRINT @vcInfo

-- cleanup
exec('if exists (select id from tempdb..sysobjects where name = ''' + @vcTempTableName + ''') DROP TABLE ' + @vcTempTableName)
DROP TABLE ##Export_WrkTbl

END
SET NOCOUNT OFF
go