Script and Export (via BCP) SQL Tables
Script via OLE Automation and Export via BCP. Check for existance of object, checks count of table with count of exported file (reads BCP output). Will do DBCC UPDATEUSAGE if at first the SQL record count does not match BCP count. Full options for scripting (with w/o indexes, Referential integrity, etc). REQUIRES ADMIN ACCESS - no workaround yet. Will accept UNC and Local path. Current version only tested on SQL 2000; might work on 7.0. Thanks to Umachander (http://www.umachandar.com) for the original OLE Automation piece.
extra notes:
default is tilde (~) delimited text file
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 = '!@#$' BEGIN PRINT @vcInfo RETURN END
if @iObjectName = '!@#$'
BEGIN PRINT @vcInfo RETURN END
if @iUNCPath = '!@#$'
BEGIN PRINT @vcInfo RETURN END
if @iScriptType = 0 AND @iUNCPath = '!@#$'
BEGIN PRINT @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
