Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Thumbs up Unanswered: Bcp with QA : TABLOCK option

    User <Brett Kaiser SQLTeam Scrub> a few days earlier
    gave me the TRICK to launch BCP through the QA :


    DECLARE @cmd varchar(8000) ;


    SET @cmd = 'bcp ' + 'TablePerson'
    + ' out ' + '“C:\Program files\MyExportFile.dat”'
    + ' -c -t ' + '¤'
    + ' -r ' + '¤\n'
    + ' -U ' + 'DBA' +' -P ' + 'SQL' +' -S ' + @@servername


    EXEC master..xp_cmdshell @cmd ;


    It works GREAT but I'm trying to add the Tablock option at the end to have better performances

    + ' –h ' + '"TABLOCK"'

    But then I have this error :

    Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Impossible de trouver la procédure stockée 'TABLOCK'.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: Bcp with QA : TABLOCK option

    Originally posted by Karolyn
    <SQLTeam Scrub>
    Hey....

    You can't, and shouldn't

    Why do you want to?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: Bcp with QA : TABLOCK option

    Hem, ... to lock the table while loading the data (?!?)


    I use it with the bulk insert and it cuts by 5 the time to load the data

    Bulk Insert DB..Tbl
    From 'MyPath\MyFile'
    With (FieldTerminator = '¤',
    RowTerminator = '¤\n',
    CodePage = 'ACP',
    TabLock) ;

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    Are you trying to output the ss data to a file (which is what you are doing) ?

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Ok, now we've changed direction...

    I would use BULK INSERT...and bcp out...

    BOL:

    TABLOCK

    Specifies that a table-level lock is acquired for the duration of the bulk copy operation. A table can be loaded concurrently by multiple clients if the table has no indexes and TABLOCK is specified. By default, locking behavior is determined by the table option table lock on bulk load. Holding a lock only for the duration of the bulk copy operation reduces lock contention on the table, significantly improving performance.
    Which I interprete to mean that you're using the default locking behavior...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Feb 2002
    Posts
    2,232
    Hem, ... to lock the table while loading the data (?!?)
    Change the "out" to "in" if you are loading the data into sql server.

  7. #7
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    The BULK INSERT works fine with the TABLOCK option

    but the BCP command doesn't recognize this option


    OK >>> Bulk Insert ... With (Tablock)
    KO >>> EXEC master..xp_cmdshell BCP ... -h "Tablock";

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •