SQL Server Performance

bcp warning

Discussion in 'General DBA Questions' started by DanCassell, Sep 23, 2005.

  1. DanCassell New Member

    When running the bcp command I get the following warning:

    SQLState = 01S05, NativeError = 0
    Warning = [Microsoft][ODBC SQL Server Driver]Cancel treated as FreeStmt/Close

    It appears to be completing successfully though. I was just wondering what this warning meant and if it was anything to worry about?
  2. satya Moderator

    Oh, this is the first time I heard to that warning.
    Try to using BCP from another client machine or another server to check any issues with the drivers on the current server.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. DanCassell New Member

    I am running a number of bcp commands scheduled on the same server, but there is only one that is outputting this warning. I believe that it is the only bcp command that is using -b to specify a batch size of 1000. What happens exactly when you specify -b?
  4. satya Moderator

    -b takes care of batch size by importing 1000 rows at a time and even though if you don't mention by default SQL would choose that fashion.

    Can you post the BCP statement used here and account privileges too.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. DanCassell New Member

    A scheduled job is running the following using sa...

    EXEC master..xp_cmdshell 'bcp "USE ConfusedLive SET NOCOUNT ON SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT ProcessNumber,Activity_ID AS CustomerActivityID,BobNumber AS TargetSite_ID,StartTime,EndTime,DateDiff(second, StartTime, EndTime) AS Duration FROM dbo.BobsRunning WHERE EndTime IS NOT NULL AND StartTime > (SELECT MAX(StartTime) FROM DataWarehouse.dbo.BobsRunning)" queryout "E:BobsRunningLOAD.txt" -b1000 -k -c -SSQL02 -e"E:ERRORSBobsRunning.txt"'

    The output on the job step is...

    Password:
    (null)
    Starting copy...
    SQLState = 01S05, NativeError = 0
    Warning = [Microsoft][ODBC SQL Server Driver]Cancel treated as FreeStmt/Close
    SQLState = 01000, NativeError = 5701
    Warning = [Microsoft][ODBC SQL Server Driver][SQL Server]Changed database context to 'ConfusedLive'.
    1000 rows successfully bulk-copied to host-file. Total received: 1000
    1000 rows successfully bulk-copied to host-file. Total received: 2000
    1000 rows successfully bulk-copied to host-file. Total received: 3000
    ... The step succeeded.
  6. Akthar New Member

    Hi,

    Seems to be problem with your ODBC Connection Timeout i guess, try modifying your timeout. I think the default is 10 secs



    AKTHAR DILMOHAMUD
    65 BENARES ST
    PORT LOUIS
    MAURITIUS
  7. satya Moderator

    BOL refers BCP using with -c

    Performs the bulk copy operation using a character data type. This option does not prompt for each field; it uses char as the storage type, no prefixes, (tab character) as the field separator, and
    (newline character) as the row terminator.

    Try without this field and the BCP should work.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page