bcp temp table within a SP

  • I need bcp out/in chunck of data within a SP. The logic is like

    1. create local temp table

    2. select data into the above local temp table

    3. bcp out the data from the local temp table

     

    Is it possible to accomplish within a SP. It seems when bcp, it establish a new session that can't access that local temp table.

     

    Any input will be sppreciated.

     

  • Local temporary tables are visible only to the creating session but global temporary tables are visible to all sessions.

    To create a global temporary table, prefix the table name with two number signs such as

    Create table ##table_name

    You need to be carefull with the life span of Global temporary tables, as they are automatically dropped when the session that created the table ends AND every other connection stops its reference.

    This might be easier just to create a permanent table in tempdb and then drop it at the end of the process.

    SQL = Scarcely Qualifies as a Language

  • Global temp table won't work for me in my situation. As this SP need create/run parallel in multiple databases in a huge SQL server (SAN) for various clients.

     

  • Why even use a 'temp' table when you can bcp out the data directly through a view. This trick should work in your situation.

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • This is an issue I have also run across in SQL 2005. It works fine using BCP from SQL 2000 but BCP from 2005 seems to handle the temporary table scope differently. Just as an example BCPing out the following:

    Create a stored procedure

    CREATEPROCEDURE [dbo].[gsp_foo]

    AS

    BEGIN

    CREATE TABLE #dog(Col int)

    INSERT INTO #dog VALUES(1)

    SELECT * FROM #dog

    DROP TABLE #dog

    END

    BCP Queryout from the procedure in 2005

    c:\>BCP "EXEC dbc.gsp_foo" queryout "text.txt" -SRemoteServer -T

    Results

    SQLState = 42S02, NativeError = 208

    Error = [Microsoft][SQL Native Client][SQL Server]Invalid object name '#dog'.

    This code works just fine if you run the BCP from a SQL 2000 server.

    Puzzled,

    Michael Mockus

  • So, what is the solution to this problem. We are also facing this.

  • The following approach worked for us. Any drawback in this? Added 'fmtonly off' for BCP.

    create procedure test1

    as

    set nocount on

    IF(1 = 0)

    BEGIN

    select name = '' , id = ''

    set fmtonly off

    return

    end

    select top 10 name , id into #temp1 from sysobjects

    select * from #temp1

  • Hi, maybe this can help you. i did a similiar thing to what you are trying, just adapt your temp table and select query for the BCP...

    CREATE PROCEDURE [dbo].[PROC_NAME]

    AS

    BEGIN

    declare @CLOCKINGS varchar(100)

    declare @LogID varchar(100)

    declare @clock varchar (255)

    DECLARE @bcpCommand varchar(2000)

    declare @error varchar (100)

    -- Create temp table to store data

    create table Temp_Table (CLOCK varchar(255),

    [LogID] [nvarchar](32) NULL,)

    -- Create a new cursor

    declare temp_cursor cursor for

    select ssMainData.dbo.CLOCKINGS.[FILE], ssMainData.dbo.CLOCKINGS.LogID

    from ssMainData.dbo.CLOCKINGS

    -- Open the cursor and retrieve the first record

    open temp_cursor

    fetch next from temp_cursor

    into @CLOCKINGS, @LogID

    --- Loop through the recordset and add data to temp table

    while @@fetch_status = 0

    begin

    insert into Temp_Table

    values (@CLOCKINGS, @LogID)

    ---Fetch next record from the recordset

    fetch next from temp_cursor

    into @CLOCKINGS, @LogID

    end

    -- Close the cursor and release resources

    close temp_cursor

    deallocate temp_cursor

    begin

    --- Create import file using BCP through xp_cmdshell

    SET @bcpCommand = 'bcp "select ssMainData..TEMP_TABLE.CLOCK FROM ssMainData..TEMP_TABLE" queryout c:\dump.txt -T -S CVE\CVE -c -t'

    EXEC ssMainData..xp_cmdshell @bcpCommand

    END

    --- Drop temp_table

    begin

    drop table ssmaindata..temp_table

    end

    end

    hope it helps...

    Clive

  • Hi

    I need to know that if bcp import only text archive (*.txt)

    I neen to know if bcp imports *.DBF (Foxpro)

    Thank you very much

    Elena Susy Moya Huamanchumo

    email: elenamoya@pexport.com.pe

    elenina_blue@hotmail.com

    moya.es@pucp.edu.pe

  • BCP will not import .DBF files. You'll need to use DTS for that. Or, you can use a linked server or OpenRowSet with the .DBF provider to link directly to the files. I've not done that to .DBF files before so I can't walk you through it.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply