BCP (OUT) via SP und SQLDMO.BulkCopy

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Thomas Hase (tohas_at_freenet.de)
Date: 08/26/04 

Date: Thu, 26 Aug 2004 21:23:02 GMT

Hallo NG,

ich bekomme unter MSSQL2k in einer SP kein BCP mehr gebacken.
Alles läuft durch, in der Logdatei steht, dass 53 Datensätze
exportiert wurden, nur in meiner Tabelle ist nichts drin.
Ich kann die Tabelle sogar löschen und die Proc meldet immer noch,
dass 53 DS eingeladen wurden.
Ich habe ein VB6-Bsp das funktioniert.

Hat vielleicht jemand eine SP unter MSSQL2k, die läuft.

Ich habe eine alte 6.5er umgeschrieben:
(Nativ hardcodiert)

/***************************************************************************/

create proc p_bulkimport @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 import dat into */
                         @input_path varchar(255), /* Input
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 imports data into a single table using the OLE
**/
/** Automation functionality of SQL Server 6.5. The purpose is
**/
/** to give users the ability to import data without giving them
**/
/** access to xp_cmdshell. To 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/6/99
**/
/**
**/
/**
**/
/** 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 imported
**/
/**
**/
/**
**/
/**
**/
/*********************************************************************/

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

DECLARE @dummy 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 'SQLDMO.SQLServer', @object OUT
    IF @hr <> 0
     BEGIN
        Exec sp_displayoaerrorinfo @object, @hr
        Return
     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
     END

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

    /* Create a Bulk Copy object */
    EXEC @hr = sp_OACreate 'SQLDMO.BulkCopy', @BCobject OUT
    IF @hr <> 0
     BEGIN
        Exec sp_displayoaerrorinfo @object, @hr
        Print 'Create BCP object error'
        Return
     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 = 4 --nativ geändert

    /* Set output file path parameter */
    EXEC @hr = sp_OASetProperty @BCobject, 'DataFilePath', @input_path
    IF @hr <> 0
     BEGIN
        Exec sp_displayoaerrorinfo @object, @hr
        PRINT 'Set DataFilePath parameter error'
        Return
     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
     END

    /* Set data SetCodePage parameter default codepage

        SELECT @dummy = -1
   EXEC @hr = sp_OASetProperty @BCobject, 'SetCodePage', @dummy

    IF @hr <> 0
     BEGIN
        Exec sp_displayoaerrorinfo @object, @hr
        PRINT 'Set SetCodePage -1 parameter error'
        Return
     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
     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
     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
     END

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

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

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

RETURN
GO

GRANT EXECUTE ON dbo.p_bulkimport TO public
GO

Relevant Pages

  • Re: Datum einer Datei ermitteln
    ... Sprachinstallation einen andern Namen haben können. ... > Declare @sekunden1 bigint ...
    (microsoft.public.de.sqlserver)
  • Re: Tabellenzeile in T-SQL kopieren
    ... > DECLARE @schemaOwner sysname ... > DECLARE curColumns CURSOR FAST_FORWARD ... > BEGIN ... > END ...
    (microsoft.public.de.sqlserver)
  • Re: ttyS0-Unit
    ... Text file assigned may be opened, written to or read from with standard ... IF waiting> 0 THEN BEGIN ... END; ...
    (de.comp.lang.pascal)
  • Re: in serielle Schnittstelle schreiben
    ... var disp: file of string; ... END. ... zu dem möglicherweise das BEGIN verlorengegangen ist? ... Text file assigned may be opened, written to or read from with standard ...
    (de.comp.lang.pascal)
  • Re: MouseLeave - Mouseenter oder Enter - Exit
    ... Integer read FBorderWidth write SetBorderWidth ... Begin ... If FocusControl Nil Then Begin ...
    (de.comp.lang.delphi.misc)