create proc p_bulkexport @server varchar(255) = null,        /* Server name where data exists, defualts to the current server*/
                         @uname varchar(30),                 /* Login name that has select permission on the table being bcp'd */
                         @pwd varchar(30),                   /* Password for the login name specified */
                         @dbname varchar(128) = null,        /* Database name, defaults to the current database */
                         @tbl_name varchar(255),             /* Table name to export */
                         @output_path varchar(255),          /* Ouput file name and path */
                         @cdelimiter char(1) = null,         /* user defined column Delimiter, defaults to comma delimited */
                         @format_file varchar(255) = null,   /* Optional format file  */
                         @err_file varchar(255) = null,      /* Optional error file path, defaults to C:\bcp_err.txt*/
                         @log_file varchar(255) = null,      /* Optional log file path, defaults to C:\bcp_log.txt */
                         @max_errors int = 0                 /* Maximum error allowed before BCP is aborted, defaults to never abort */
                         
AS


/*********************************************************************/
/**  Designed for SQL Server 6.5                                    **/
/**  This procedure exports data from a single table using the OLE  **/
/**  Automation functionality of SQL Server 6.5.  The purpose is    **/
/**  to give users the ability to export data without giving them   **/
/**  access to xp_cmdshell.  The create and run this stored         **/
/**  procedure you will need to create the stored procedures        **/
/**  listed in the BOL under the topic "HRESULT Return Codes"       **/
/**                                                                 **/
/**  Create by: Buddy Ackerman (buddy__a@hotmail.com)               **/
/**  Created on: 5/4/99                                             **/
/**  Modified on:  5/6/99 Added error and log file parameters       **/
/**  Modified on:  5/10/99 Added support for exporting views        **/
/**                                                                 **/
/**  Tables/Views Used:  NONE                                       **/
/**                                                                 **/
/**  Stored Procs Used: sp_OACreate                                 **/
/**                     sp_OAMethod                                 **/
/**                     sp_OASetProperty                            **/
/**                     sp_OAGetProperty                            **/
/**                     sp_displayoaerrorinfo                       **/
/**                                                                 **/
/**                                                                 **/
/**  Local Variables:                                               **/
/**    @object           SQL Server Object variable                 **/
/**    @hr               HRESULT from OLE stored procedures         **/
/**    @BCobject         BulkCopy object variable                   **/
/**    @Tobject          Table object variable                      **/
/**    @bcp_type         Specifies the BCP file type                **/
/**    @rdelimiter       Row deliniter                              **/
/**    @property_string  String used for creating the table object  **/
/**                                                                 **/
/**                                                                 **/
/**                                                                 **/
/**  Returns: Number of row exported                                **/
/**                                                                 **/
/**                                                                 **/
/**                                                                 **/
/*********************************************************************/





DECLARE @object int
DECLARE @hr int
DECLARE @BCobject int
DECLARE @Tobject int

DECLARE @bcp_type int
DECLARE @err_source varchar(255)
DECLARE @err_desc varchar(255)
DECLARE @msg varchar(255)
DECLARE @rdelimiter char(2)
DECLARE @property_string varchar(255)
DECLARE @rows_exported int



    /* Sets the server to BCP from */
    IF @server is NULL
     BEGIN
        SELECT @server = @@servername
     END

    /* Sets the database to BCP from */
    IF @dbname is NULL
     BEGIN
        SELECT @dbname = db_name()
     END


    /* Create a SQL Server object */

    EXEC @hr = sp_OACreate 'SQLOLE.SQLServer', @object OUT
    IF @hr <> 0
     BEGIN
        Exec sp_displayoaerrorinfo @object, @hr
        Return 1
     END



    /* Call Connect method */
    EXEC @hr = sp_OAMethod @object, 'Connect', NULL,  @server, @uname, @pwd
    IF @hr <> 0
     BEGIN
        Exec sp_displayoaerrorinfo @object, @hr
        PRINT 'Connect Error'
        Return 1 
     END






/*******************************************************************************************************/
/***************  Create a BulkCopy object and set some of the parameters  *****************************/
/*******************************************************************************************************/

    /* Create a Bulk Copy object */
    EXEC @hr = sp_OACreate 'SQLOLE.BulkCopy', @BCobject OUT
    IF @hr <> 0
     BEGIN
        Exec sp_displayoaerrorinfo @object, @hr
        Print 'Create BCP object error'
        Return 1 
     END


    /* Set the bcp_type to be used when setting the DatFileType property of the BulkCopy object */
    /* Set the @bcp_type to 2 initially to default to TAB delimited                             */
    SELECT @bcp_type = 1

    IF @cdelimiter is NOT NULL
     BEGIN
        SELECT @bcp_type = 3  /* User defined column delimiter */
     END


    IF @format_file is NOT NULL
     BEGIN
        SELECT @bcp_type = 5  /* User defined format file */
     END



    /* Set output file path parameter */
    EXEC @hr = sp_OASetProperty @BCobject, 'DataFilePath', @output_path
    IF @hr <> 0
     BEGIN
        Exec sp_displayoaerrorinfo @object, @hr
        PRINT 'Set DataFilePath parameter error'
        Return 1
     END


    /* Set data file type parameter */
    EXEC @hr = sp_OASetProperty @BCobject, 'DataFileType', @bcp_type

    IF @hr <> 0
     BEGIN
        Exec sp_displayoaerrorinfo @object, @hr
        PRINT 'Set DataFileType parameter error'
        Return 1
     END




    /* Set column delimiter and format file parameters if necessary */
    IF @bcp_type = 3
     BEGIN
        
        /* Set column delimiter parameter */
        EXEC @hr = sp_OASetProperty @BCobject, 'ColumnDelimiter', @cdelimiter

        IF @hr <> 0
         BEGIN

            Exec sp_displayoaerrorinfo @object, @hr
            PRINT 'Set ColumnDelimiter parameter error'
            Return 1
         END

        /* Set row delimiter parameter */
        SELECT @rdelimiter = CHAR(13) + CHAR(10)
        EXEC @hr = sp_OASetProperty @BCobject, 'RowDelimiter', @rdelimiter

        IF @hr <> 0
         BEGIN
            Exec sp_displayoaerrorinfo @object, @hr
            PRINT 'Set ColumnDelimiter parameter error'
            Return 1
         END


     END

    ELSE 
     BEGIN
        IF @bcp_type = 5
         BEGIN 

            /* Set format file path parameter */
            EXEC @hr = sp_OASetProperty @BCobject, 'FormatFilePath', @format_file

            IF @hr <> 0
             BEGIN
                Exec sp_displayoaerrorinfo @object, @hr
                PRINT 'Set FormatFilePath parameter error'
                Return 1
             END

         END
     END



 
    /* Set max errors parameter */
    EXEC @hr = sp_OASetProperty @BCobject, 'MaximumErrorsBeforeAbort', @max_errors
    IF @hr <> 0
     BEGIN
        Exec sp_displayoaerrorinfo @object, @hr
        PRINT 'Set DataFilePath parameter error'
        Return 1
     END


    IF @log_file = null
        SELECT @log_file = 'C:\bcp_log.txt'

    /* Set LogFilePath parameter */
    EXEC @hr = sp_OASetProperty @BCobject, 'LogFilePath', @log_file
    IF @hr <> 0
     BEGIN
        Exec sp_displayoaerrorinfo @object, @hr
        PRINT 'Set LogFilePath parameter error'
        Return 1
     END


    IF @err_file = null
        SELECT @err_file = 'C:\bcp_err.txt'

    /* Set ErrorFilePath parameter */
    EXEC @hr = sp_OASetProperty @BCobject, 'ErrorFilePath', @err_file 
    IF @hr <> 0
     BEGIN
        Exec sp_displayoaerrorinfo @object, @hr
        PRINT 'Set LogFilePath parameter error'
        Return 1
     END

/*****************************************************************************************************/
/*****************  Create a table object pointing to table       ************************************/
/*****************  hm_parameters and execute it's export method  ************************************/
/*****************************************************************************************************/

    /* Create a Table/View object */
    SELECT @property_string = 'Databases(' + @dbname + ').tables(' + @tbl_name + ')'
    EXEC @hr = sp_OAGetProperty @object, @property_string , @Tobject OUT
    IF @hr <> 0
     BEGIN
        SELECT @property_string = 'Databases(' + @dbname + ').views(' + @tbl_name + ')'
        EXEC @hr = sp_OAGetProperty @object, @property_string , @Tobject OUT
        IF @hr <> 0
         BEGIN
            Exec sp_displayoaerrorinfo @object, @hr
            PRINT 'Create Table/View object error'
            Return 1
         END
     END




        /* Call table object's ExportData method and pass the BulkCopy object as the argument */
        EXEC @hr = sp_OAMethod @Tobject, 'ExportData', NULL, @BCobject
        IF @hr <> 0
         BEGIN
            Exec sp_displayoaerrorinfo @object, @hr
            PRINT 'Export data error'
            Return 1
         END



RETURN 0
GO

GRANT  EXECUTE  ON dbo.p_bulkexport  TO public
GO