BCP and DLL together
-
I need to automate the following and am having trouble trying to figure out how to do it.
We archive some of our data by bcping data into a bcp file and storing the data on another disk. This was an inherited process and VERY manual. We also ran into issues where we needed to restore one of the older BCP files and the schema had changed, so we had difficulty creating the table needed to bcp in that file.
I need to do the following
Somehow automate a task that will generate the schema for the table I intend to BCP from. I then need to perform the BCP and store both outputs in a certain location.
So here are my questions.
Is there a flag you set while BCPing that will automatically generate the schema need to recreate the table?
Would a format file help?
Should I be doing this via DTS?
Is there a way to automatically generate the schema outside the bcp?
The only thing I miss about SQL 6.5 was the 'backup table' feature..Not sure why they got rid of that one...
Thanks in advance to any help.
Susan
-
Hi Susan!
I've had this script in my arsenal for a while, but i think it's time to release to the world
. I deal with extremely large databases (> 200GB) in a batch processing environment where it is often easier to backup individual tables instead of the entire database. Below is a stored procedure that i've developed with help from http://www.umachandar.com. It will script, export, and check the count of exported file with the BCP output. If there is a difference, it will first try to update the sql server count using the DBCC UPDATEUSAGE statement (since SQL doesn't always update the system tables after inserts/deletes). It can take UNC or local paths. Scripting is also very flexible; you can choose to include indexes/constraints and even referential integrity. I don't have this posted to the script library here yet, so it has been pasted below. Hopefully, the line wrapping won't screw it up 
Also, please know that this scripts requires admin access since it uses xp_cmdshell inside of exec statements. I have not found a permission workaround for this.
Mike M
--==================================== BEGIN SCRIPT
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 - more 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
-
This is TOTALLLY AWESOME and EXACTLY what I was looking for!!
I've been trying it out this morning and am running into some user error on my part I'm sure..
Here is what I run
usp_Export 'lm_agent', '\\db5testdev\d$\bcp','','','',''
and here's the output I recieve
usp_Export - Scripted - (lm_agent) --> (\\db5testdev\d$\bcp\lm_agent.scr)
OLE Automation Error
Source: ODSOLE Extended Procedure
Description: [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
Server: Msg 50000, Level 11, State 1, Procedure usp_Export, Line 275
Unmatched SQL and BCP counts. Refer to \\db5testdev\d$\bcp\lm_agent.err
SQLCount = 67 : BCPCount = Error
There are no files in the directory and it's saying the sqlserver dosen't exsist. I'm running it via a Query Analyzer already connected to the DB, so I'm not sure what that's refering to. I'm sure the counts are off because I don't think the BCP is actually being executed (no files). Also I've tried using the user/password combo, but then get an error message stating that there are too many parameters for that proc.
If you would be so kind as to show me an example of how you execute it, it would be greatly appreciated. Here are my parameters...
Object(table/view) (LM_Agent),
UNCPath (\\db5testdev\d$\bcp'), ExportType (0(default)),
ScriptType (0), SQLUser (sa), SQLPwd (@pwd), Delimiter (?), OutputFileName (I would like to use the default)
Thanks in advance for all your help.
Susan
- Glad you like it!
Please note that you can run the procedure with no options and it will provide help and examples (forgot to mention that in the original post). I do this because i tend to forget all the options with several procedures in the database.
For you specific use, i suggest simply running the following:
usp_Export 'lm_agent', '\\db5testdev\d$\bcp'
Do not fill in blanks for the other params; it is not necessary. Default delimiter is tilde (~) since it is not normally found in data fields. If you need tab delimited data, try '/t' (i have not actually tried it, but it might work).
The full syntax with all params defined would be:
usp_Export 'lm_agent', '\\db5testdev\d$\bcp', '!@#$', '!@#$', 0, 0, '~', '!@#$'
If you look at the code, i fill in an odd default value (!@#$) for varchar params. The reason is that sometimes i need to know if the param was set by the user or not. I can then check user input if the default value has been changed.
Make sure the account the SQL Server Service uses has access to that file share. To test functionality, i would suggest using a local drive & folder first before using UNC. So if the above path is on the local server:
usp_Export 'lm_agent', 'd:\bcp'
Hope that helps!
Mike
-
Thanks for the quick response.. I was right.. it was user error
I ended up trying the proc on another server and it worked like a charm. I should have known better than to test it on a test development server..Lesson learned...
So it does EXACTLY what I need.. and I appreciate your help.
I bow in your presence and honor you...
Thanks again and again and again..
I had the question on the Microsoft website and they weren't able to help. Would you mind if I posted your solution and of course give you the credit you deserve? No worries if you don't.
AGAIN THANKS THANKS THANKS!!!!!
-
hehe...
thanks for the praise!Please feel free to share with others; it's better to make everyone's life easier instead of just mine
. It's been refined over the last 3 years so it should be good for general consumption. This is one of those things that 90% of DBAs need; i've always wondered why Microsoft didn't include this sort of functionality as part of BCP or some other command line utility.You may wish to include the link to this thread when you post to the MS site; it will show an alternate resource for answers to their SQL problems.
Also, would you please post the link to the microsoft website to which you are posting the solution? I scan the newsgroups every now and then, but didn't know MS hosted web forums as well.
Thanks and happy exporting!
Mike
-
Thanks.. I will find my post and send it on to you..
But the general link is
http://support.microsoft.com/newsgroups/default.aspx
then drill down to servers/SQLServers and you will find the topic breakdown..
One last question
I'm now trying to bcp the data back into another table in a different database but keep getting the
Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Unexpected EOF encountered in BCP data-file
I tried using the different parameters -c -n -w -N but to no avail.
Thanks again I will send you a private message with the link to my questions on the Microsoft site.
Susan
-
ahh... here is the bcp command line to import a file exported using the default method. It is setup for windows integrated security, so you may need to modify for your environment. Please omit the "<>" when executing
bcp <database>..<tablename> in <tablename>.txt -c -t~ -S<servername> -T
Below is a batch file i use that will both execute the default script file and import the data with bcp. Please note that the default script includes a "drop if exists" statement. You can run the batch file without options to get help. It takes server, database, and tablename as params; all are required.
<BEGIN sendin.bat>
@ECHO OFF
IF "%1" == "" goto ERROR
IF "%2" == "" goto ERROR
IF "%3" == "" goto ERROR
osql -e -n -S%1 -d%2 -E -i%3.scr
echo bcp %2..%3 in %3.txt %5 -c -t~ -S%1 -T
bcp %2..%3 in %3.txt %4 -c -t~ -S%1 -T
goto END
:ERROR
ECHO:
ECHO =-= Run Script and BCP IN =-= sendin.bat
ECHO:
ECHO Usage: sendin SERVER DBASE FILE
ECHO:
ECHO Script file must be same name as text file with .scr extention
:END
<END sendin.bat>
Mike
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply