BulkExport - export data from TSQL

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.sp_bulkexport    Script Date: 6/25/2004 6:26:52 PM ******/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_bulkexport]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_bulkexport]
GO


CREATE    proc sp_BulkExport

@server varchar(255) = null, -- Leave this blank for local server
@trusted char(4) = 'TRUE', -- TRUE is for trusted connections, FALSE if you want to pass a ID and PW
@login varchar(30) = null, -- ID for standard login
@passwd varchar(30) = null, -- PW for standard login
@dbname varchar(255) = 'PUBS', -- Database name
@tblname varchar(255) = 'Authors', -- Table to be exported
@datafile varchar(255) = 'C:\Authors.DAT', -- Output path of data file 
@filetype int = 4, -- Filetype - 1 = Comma Delim, 2 = Tab Delim, 3 = User defined, 4 = Native, 5 = Use format file
@fmtfile varchar(255) = null, -- Format file path, only valid with file type 5
@coldelim char(1) = ',', -- Cloumn Delimiter, only valid with file type 3
@rowdelim char(2) = null, -- Row Delimiter, only valid with file type 3, defaults to cr + lf
@logfile varchar(255) = 'C:\BCPLOG.LOG', -- Output file path for Bulk Operations
@errfile varchar(255) = 'C:\BCPERR.ERR', -- Output file path for Bulk Operation Errors
@maxerr int = 0 -- Max errors before abort

AS

-- sp_BulkExport - Jack Kress 5/4/04 v1.0
-- sp_BulkExport @trusted = 'FALSE', @server = 'SQLSERVER33', @login = 'sa', @passwd = 'topsecret', @filetype = 1
-- sp_BulkExport @datafile = 'C:\Authors.DAT', @filetype = 5, @fmtfile = 'C:\Authors.fmt'
-- sp_BulkExport @server = 'SQLSERVER33', @datafile = 'E:\Authors.DAT', @filetype = 5, @fmtfile = 'C:\Authors.fmt'
-- sp_BulkExport @server = 'SQLSERVER33', @dbname = 'Pubs', @tblname = 'Employee', @datafile = 'c:\temp\Employee.txt', @filetype = 3, @coldelim = '|'
-- sp_BulkExport @datafile = 'c:\Authors.DAT'
-- sp_BulkExport @dbname = 'Pubs', @TBLNAME = 'Authors', @datafile = 'C:\TEMP\Authors.DAT', @LOGFILE = 'C:\TEMP\Authors.LOG', @ERRFILE = 'C:\TEMP\Authors.ERR'
--
-- Trap for errors
-- DECLARE @return_status INT
-- EXEC @return_status  = sp_BulkExport
-- SELECT @return_status 


DECLARE @SQLServerObj int
DECLARE @BulkCopyObj int
DECLARE @TableObj int
DECLARE @NameObj varchar(255)
DECLARE @hr int
DECLARE @source varchar(255)
DECLARE @description varchar(255)
DECLARE @propstr varchar(255) 
DECLARE @result int

-- Check some of the parameters for validity

IF @filetype NOT BETWEEN 1 AND 5
BEGIN
  PRINT 'sp_BulkExport Failed, invalid export filetype ' 
  RETURN 1
END

IF @filetype = 5
BEGIN
  IF @fmtfile IS NULL
  BEGIN
    PRINT 'sp_BulkExport Failed, invalid @fmtfile ' 
    RETURN 1
  END
  EXEC master..xp_fileexist @fmtfile, @result output 
  IF @result = 0
  BEGIN
    PRINT 'sp_BulkExport Failed, invalid @fmtfile path ' 
    RETURN 1
  END
END

-- Create SQL Server Object
EXECUTE @hr = sp_OACreate 'SQLDMO.SQLServer', @SQLServerObj OUT 
IF @hr <> 0 
 BEGIN
   PRINT 'SQLDMO.SQLServer create failed'
   RETURN 1
 END

-- Create Bulk Copy Object
EXECUTE @hr = sp_OACreate 'SQLDMO.BulkCopy', @BulkCopyObj OUT 
IF @hr <> 0 
 BEGIN
   PRINT 'SQLDMO.BulkCopy create failed'
   RETURN 1
 END

-- Create Table Object
EXECUTE @hr = sp_OACreate 'SQLDMO.Table', @TableObj OUT 
IF @hr <> 0 
 BEGIN
   PRINT 'SQLDMO.Tables create failed'
   RETURN 1
 END

-- Enable BCP
EXEC @hr = sp_OASetProperty @SQLServerObj, 'EnableBcp', 'True'
IF @hr <> 0
 BEGIN
   EXEC sp_OAGetErrorInfo @SQLServerObj, @source OUT, @description OUT
    select @description, @source
    PRINT 'sp_OASetProperty @SQLServerObj EnableBcp Failed'
   RETURN 1
 END

-- Set for trusted connection
IF @trusted = 'TRUE'
 BEGIN
  EXEC @hr = sp_OASetProperty @SQLServerObj, 'LoginSecure', 'True'
   IF @hr <> 0
    BEGIN
     EXEC sp_OAGetErrorInfo @SQLServerObj, @source OUT, @description OUT
      select @description, @source
      PRINT 'sp_OASetProperty @SQLServerObj LoginSecure Failed'
     RETURN 1
 END
END

-- If connection not trusted, pass server, ID and PW
EXEC @hr = sp_OAMethod @SQLServerObj, 'Connect', NULL, @server, @login, @passwd
IF @hr <> 0
 BEGIN
   EXEC sp_OAGetErrorInfo @SQLServerObj, @source OUT, @description OUT
    select @description, @source
    PRINT 'sp_OASetProperty @SQLServerObj Connect Failed'
   RETURN 1
 END

-- Set output file type
EXEC @hr = sp_OASetProperty @BulkCopyObj, 'DataFileType', @filetype
IF @hr <> 0
 BEGIN
   EXEC sp_OAGetErrorInfo @BulkCopyObj, @source OUT, @description OUT
    select @description, @source
    PRINT 'sp_OASetProperty @BulkCopyObj DataFileType Failed'
   RETURN 1
 END

-- Set format file path if necessary
IF @filetype = 5
 BEGIN
  EXEC @hr = sp_OASetProperty @BulkCopyObj, 'FormatFilePath', @fmtfile
  IF @hr <> 0
   BEGIN
     EXEC sp_OAGetErrorInfo @BulkCopyObj, @source OUT, @description OUT
      select @description, @source
      PRINT 'sp_OASetProperty @BulkCopyObj FormatFilePath Failed'
     RETURN 1
   END
END

-- Set column delimiter if necessary
IF @filetype = 3
 BEGIN
  EXEC @hr = sp_OASetProperty @BulkCopyObj, 'ColumnDelimiter', @coldelim
  IF @hr <> 0
   BEGIN
     EXEC sp_OAGetErrorInfo @BulkCopyObj, @source OUT, @description OUT
      select @description, @source
      PRINT 'sp_OASetProperty @BulkCopyObj ColumnDelimiter Failed'
     RETURN 1
   END


-- Set row delimiter if necessary
 IF @rowdelim IS NULL SELECT @rowdelim = CHAR(13) + CHAR(10)
  EXEC @hr = sp_OASetProperty @BulkCopyObj, 'RowDelimiter', @rowdelim
   IF @hr <> 0   
    BEGIN
     EXEC sp_OAGetErrorInfo @BulkCopyObj, @source OUT, @description OUT
      select @description, @source
      PRINT 'sp_OASetProperty @BulkCopyObj RowDelimiter Failed'
     RETURN 1
    END
END

-- Use existing connection
EXEC @hr = sp_OASetProperty @BulkCopyObj, 'UseExistingConnection', 'True'
IF @hr <> 0
 BEGIN
   EXEC sp_OAGetErrorInfo @BulkCopyObj, @source OUT, @description OUT
    select @description, @source
    PRINT 'sp_OASetProperty @BulkCopyObj UseExistingConnection Failed'
   RETURN 1
 END

-- Set data file path
EXEC @hr = sp_OASetProperty @BulkCopyObj, 'DataFilePath', @DataFile
IF @hr <> 0
 BEGIN
   EXEC sp_OAGetErrorInfo @BulkCopyObj, @source OUT, @description OUT
    select @description, @source
    PRINT 'sp_OASetProperty @BulkCopyObj DataFilePath Failed'
   RETURN 1
 END


-- Set max errors before abort
EXEC @hr = sp_OASetProperty @BulkCopyObj, 'MaximumErrorsBeforeAbort', @maxerr
IF @hr <> 0
 BEGIN
   EXEC sp_OAGetErrorInfo @BulkCopyObj, @source OUT, @description OUT
    select @description, @source
    PRINT 'sp_OASetProperty @BulkCopyObj MaximumErrorsBeforeAbort Failed'
   RETURN 1
 END


-- Set output log file path
EXEC @hr = sp_OASetProperty @BulkCopyObj, 'LogFilePath', @logfile
IF @hr <> 0
 BEGIN
   EXEC sp_OAGetErrorInfo @BulkCopyObj, @source OUT, @description OUT
    select @description, @source
    PRINT 'sp_OASetProperty @BulkCopyObj LogFilePath Failed'
   RETURN 1
 END


-- Set error log file path
EXEC @hr = sp_OASetProperty @BulkCopyObj, 'ErrorFilePath', @errfile 
IF @hr <> 0
 BEGIN
   EXEC sp_OAGetErrorInfo @BulkCopyObj, @source OUT, @description OUT
    select @description, @source
    PRINT 'sp_OASetProperty @BulkCopyObj ErrorFilePath Failed'
   RETURN 1
 END

-- Create table object
SELECT @propstr = 'Databases(' + @dbname + ').tables(' + @tblname + ')'
EXEC @hr = sp_OAGetProperty @SQLServerObj, @propstr , @TableObj OUT
IF @hr <> 0
 BEGIN
   EXEC sp_OAGetErrorInfo @TableObj, @source OUT, @description OUT
    select @description, @source
    PRINT 'sp_OAGetProperty @TableObj @propstr Failed'
   RETURN 1
 END


-- Export the data
EXEC @hr = sp_OAMethod @TableObj, 'ExportData', NULL, @BulkCopyObj
IF @hr <> 0
 BEGIN
   EXEC sp_OAGetErrorInfo @TableObj, @source OUT, @description OUT
    select @description, @source
    PRINT 'sp_OAMethod @TableObj ExportData Failed'
   RETURN 1
 END

RETURN 


GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO