Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jul 2006
    Posts
    28

    Unanswered: can we have temporary table in store procedure when using BCP ?

    hi, good day,

    when i run query in query analyzer
    Code:
    exec sp_test '2006-07-21'
    it show result , however when i run it using BCP tools

    Code:
    exec master..xp_cmdshell 'bcp "exec sp_test '2006-07-21' " queryout c:\test.txt -c '
    it give me error which is something like "invalid object #temp_tbl",
    i do using temporary table in store procedure

    how to we solve it ? seem that bcp not support temporary table ? really need guidance from expert here ,

    thank you

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Hmph. I would have thought that would work. Post the code for sp_test.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jul 2006
    Posts
    28
    thanks for reply, the query was constructed in this way
    Code:
    CREATE PROCEDURE [SP_TEST]
    
    -------------------------CREATE TEMP TABLE-----------------------------
    SELECT 
    * 
    INTO #TMP_TBL1
    FROM 
    TABLE A, TABLE B
    
    SELECT 
    * 
    INTO #TMP_TBL2
    FROM 
    TABLE C, TABLE D
    
    ------------------------------BEGINNING OF PROCESS----------------
    
    SELECT * 
    FROM 
    (
          SELECT * FROM 
           #TMP_TBL1
          UNION
          #TMP_TBL2
    
    )

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You are posting skeleton code. Post the actual code you are using, and the actual error message you receive.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jul 2006
    Posts
    28
    hi, blindman, sorry , i can't expose my company query and it would be few page query, but i use this simple store procedure to test on , it doesn't work as well

    Code:
    CREATE PROCEDURE [SP_TEST]
    
     AS
    
    SELECT * 
    INTO #TMP_CUSTOMER
    FROM O_CUSTOMER
    
    -------------------------------------------------------------------------
    SELECT DISTINCT 
    COUNT(CUST_CD)
    
    FROM 
    #TMP_CUSTOMER
    GO
    when i run in query analyzer , using "exec SP_TEST" , it work

    but when run
    Exec Master..xp_CmdShell 'bcp "exec mydbtest..SP_TEST " queryout C:\TEST.TXT -c -Slocalhost -Usa -Ppassword'

    it give error "SQLState = S0002, NativeError = 208"
    and show
    Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '#TMP_CUSTOMER'.


    do u have any idea on it ?

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I tried you code and was able to duplicate your error. I tried a few work-arounds, but none of them were succesful. BCP seems to be failing to recognize any table created within the procedure, temporary or permanent.

    I have not run into this before, but I have not used BCP very frequently. I am surprised, none the less.

    Wait until Monday, and lets see if any other forum members recognize this error.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Did a quick RTFM:
    Quote Originally Posted by BoL
    Copying Data To or From a Temporary Table

    When using bcp or BULK INSERT to bulk copy data using a global temporary table, the table name must be specified at the command prompt, including initial number signs (##). For example, to bulk copy data from the global temporary table ##temp_authors to the Temp_authors.txt data file, execute at the command prompt:
    bcp ##temp_authors out temp_authors.txt -c -Sservername -Usa -PpasswordHowever, do not specify the database name when using global temporary tables because temporary tables exist only in tempdb. It is possible to use a local temporary table (for example, #temp_authors) only when bulk copying data using the BULK INSERT statement.
    The key point appears to be the last line. A rather interesting turn of phrase that says (in a roundabout way) - "you can't".

    HTH

    EDIT - in fact Nakata that is so poorly written (and assuming English isn't your first language - believe it or not it is my first language and I had to read it a couple of times) it basically says that local temp tables work with BULK INSERT but not (by implication) bcp.
    Last edited by pootle flump; 07-22-06 at 17:31.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jul 2006
    Posts
    28
    thanks pootle flump and blindman,
    this is bad for me with the answer, i may need to re-write the whole query, my god , that really killing me ...any other alternative i can have if possible no need to re-write all query that involve temporary table

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Not a big user of bcp.... I wonder if it has the same objection to Table variables....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Feb 2004
    Posts
    88
    Hi,

    This is a shortcoming of the way that bcp works, particularly when a stored procedure is used as the command for a "queryout" operation.

    bcp needs to understand the format of the results it is about to receive, before it issues the query. If you use the "out" operation, giving a table name, it issues the following command:

    SET FMTONLY ON
    SELECT * FROM <table name>
    SET FMTONLY OFF

    If you use the "queryout" operator with an exec statement, it issues the following command:

    SET FMTONLY ON
    exec ...
    SET FMTONLY OFF

    Unfortunately, this technique only works with the simplest of stored procedures, where the format of the ouput can be determined WITHOUT ACTUALLY RUNNING THE CODE IN THE PROCEDURE. Your case does not work because #TMP_CUSTOMER does not exist unless you actually run the code.

    FYI, a solution with a table variable WILL work, e.g.

    <code>
    create proc dbo.bills_test
    as
    begin

    declare @output table (col1... , col2 ... , ....)

    insert @output
    select ....

    select col1, col2 ...
    from @output

    end
    <\code>

    you can play with this in query analyser, to get a stored proc that will work with bcp...

    HTH,

    Bill

  11. #11
    Join Date
    Jul 2006
    Posts
    28
    thanks everyone for contribution , i really learn a lot from this issue , i finally found that if i replace #tmp_tbl to tempdb..tmp_tbl , it work

    will tempdb object will causing any performance issue , i really not sure ..


  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    That may cause problems if more than one user executes your process simultaneously. You should consider using thompbil's suggestion and implementing a table variable.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by blindman
    but I have not used BCP very frequently.

    Oh, for the love of god.....

    In any case just put the results of the sproc into a table and bcp that out
    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.

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Brett Kaiser
    Oh, for the love of god.....
    You got a problem wit dat, joisey-boy?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    tell me you use DTS...come on, you can tell us
    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.

Posting Permissions

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