create procedure usp_bulkcopy
    @sourceserver  varchar(255)  = null,
    @sourcedb      varchar(255)  = null,
    @sourcetable   varchar(255)  = null,
    @sourcefile    varchar(1000) = null,
    @destserver    varchar(255)  = null,
    @destdb        varchar(255)  = null,
    @desttable     varchar(255)  = null,
    @destfile      varchar(1000) = null,
    @formatfile    varchar(1000) = null,
    @numrows       int           out 
    

as
set nocount on
/******************************************************************************
*    Locals
******************************************************************************/
declare
    @oSQLServer int,             --SQL Server Object
    @oBCP       int,             --BulkCopy Object
    @oResults   int,             --Results Object
    @HRESULT    int,             --sp_oa* return value
    @verify     int
  
declare
    @error          varchar(8000),
    @datafilepath   varchar(1000),
    @exportmethod   varchar(255),
    @importmethod   varchar(255),
    @method         varchar(255)

select
    @error          = 'unknown error',
    @datafilepath   = coalesce(@sourcefile,@destfile),
    @exportmethod   = 'Databases("' + @sourcedb + '").' +
                      'Tables("' + @sourcetable + '").' +
                      'ExportData',
    @importmethod   = 'Databases("' + @destdb + '").' +
                      'Tables("' + @desttable + '").' +
                      'ImportData'

--error handler
if (@datafilepath is null and
       (@exportmethod is null or @importmethod is null)) or
   (@datafilepath is not null and @formatfile is null)
begin
    set @error = 'Illegal call to usp_bulkcopy: missing params'
    goto ErrorCondition
end

/******************************************************************************
*    Create the SQLServer object
******************************************************************************/
exec @HRESULT = sp_OACreate 'SQLDMO.SQLServer', @oSQLServer out
if @HRESULT <> 0
begin
    exec usp_displayoaerrorinfo @oSQLServer, @HRESULT, @error out
    goto ErrorCondition
end

-- Set the login process to use NT Authentication
exec @HRESULT = sp_OASetProperty @oSQLServer, 'LoginSecure', -1
if @HRESULT <> 0
begin
    exec usp_displayoaerrorinfo @oSQLServer, @HRESULT, @error out
    goto ErrorCondition
end

-- Connect to server using NT Authentication
exec @HRESULT = sp_OAMethod @oSQLServer, 'Connect', NULL,  @@SERVERNAME
if @HRESULT <> 0
begin
    exec usp_displayoaerrorinfo @oSQLServer, @HRESULT, @error out
    goto ErrorCondition
end

-- Verify the connection
exec @HRESULT = sp_OAMethod @oSQLServer, 'VerifyConnection', @verify out
if @HRESULT <> 0
begin
    exec usp_displayoaerrorinfo @oSQLServer, @HRESULT, @error out
    goto ErrorCondition
end
if @verify = 0
begin
    set @error = 'Unable to verify connection'
    goto ErrorCondition
end

/******************************************************************************
*    Create the BCP Object
******************************************************************************/
exec @HRESULT = sp_OACreate 'SQLDMO.BulkCopy', @oBCP OUT 
if @HRESULT <> 0
begin
    exec usp_displayoaerrorinfo @oBCP, @HRESULT, @error out
    goto ErrorCondition
end

-- Set the file path
exec @HRESULT = sp_OASetProperty @oBCP, 'DataFilePath', @datafilepath
if @HRESULT <> 0
begin
    exec usp_displayoaerrorinfo @oBCP, @HRESULT, @error out
    goto ErrorCondition
end

-- Set the format file
exec @HRESULT = sp_OASetProperty @oBCP, 'FormatFilePath', @datafilepath
if @HRESULT <> 0
begin
    exec usp_displayoaerrorinfo @oBCP, @HRESULT, @error out
    goto ErrorCondition
end

/******************************************************************************
*    Do the bulk copy
******************************************************************************/
set @method = coalesce(@importmethod,@exportmethod)
exec @HRESULT = sp_OAMethod @oSQLServer,
                @method,
                @NumRows OUTPUT,
                @oBCP
if @HRESULT <> 0
begin
    exec usp_displayoaerrorinfo @oSQLServer, @HRESULT, @error out
    goto ErrorCondition
end

/******************************************************************************
*    Destroy the objects
******************************************************************************/
exec @HRESULT = sp_OADestroy @oSQLServer
if @HRESULT <> 0
begin
    exec usp_displayoaerrorinfo @oSQLServer, @HRESULT, @error out
    goto ErrorCondition
end

exec @HRESULT = sp_OADestroy @oBCP
if @HRESULT <> 0
begin
    exec usp_displayoaerrorinfo @oSQLServer, @HRESULT, @error out
    goto ErrorCondition
end
return(0)

/******************************************************************************
*    ErrorCondition
******************************************************************************/
ErrorCondition:
RAISERROR(@error,16,1) WITH LOG
return(1)
go

quote:

---

Server: Msg 50000, Level 16, State 1, Line 0
OLE Error--HRESULT: 0x800412ff Source: Microsoft SQL-DMO (ODBC SQLState: 42000)
Description: [Microsoft][ODBC SQL Server Driver][SQL Server]Bulk insert data conversion error (truncation) for row 1, column 56 (Product_Expand9).

---