Post reply

problem with temporary table.

  • May 25, 2006 at 6:37 am

    #112419

    Hi all,

    I have a stored procedure (let's call SP_A) that creates a temporary table to store the resultant rows and pass them back.

    Now I am creating a job (another stored procedure, say SP_B) that would run periodically and that would be calling SP_A with different parameters.

    I am getting back the following error when I run SP_B:

    Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '#tempTable'.

    I realize that these temporary tables are scoped for the stored procedure, but wouldn't I still be able to return the data to the calling procedure?

    Thanks

    Shams

  • May 25, 2006 at 7:02 am

    #640020

    It would help if you paste your DDL.

    If #temptable is created inside Sp_A, and it returns data, then SP_B will run with no issues..There might be some other issue inside sp_A

    thanks

    Brij

  • May 25, 2006 at 7:16 am

    #640024

    Hi Brij,

    This temporary table's created, accessed and destroyed in the first procedure. Rather than returning the data from the temporary table to the calling ASP recordset, I am trying to use it in the second stored procedure.

    Like .. first SP: (sp_web_JustDialDestinationAnalysisReport)

    Create table #tempTable (...)

    Insert #tempTable (.....)

    Select * from #tempTable

    Drop table #tempTable

    Second SP:

    bcp "exec web.dbo.sp_web_JustDialDestinationAnalysisReport" queryout "<path>" -c -t <server details>

    Thanks

    Shams

  • May 25, 2006 at 7:22 am

    #640026

    I think the problem is using BCP and the second sp is a red herring. BCP opens a new connection which cant see temp tables on the sp which executes it. 'Openrowset' should offer a way around this.

    www.sql-library.com[/url]

  • May 25, 2006 at 7:45 am

    #640038

    I agree with Jules.

    Brij

  • May 25, 2006 at 7:51 am

    #640044

    Hi guys,

    I agree, the same can be done using "SET FMTONLY OFF" before calling the SP, thanks!

    A minor issues:

    1. I am specifying "," as field terminator. How do I stop a column from splitting into 2 if there's comma there? like Spain, Mobile (should be 1 column, not 2 in file)

    2. How do I get the column names in the csv file?

    Thanks again,

    Shams

  • May 25, 2006 at 7:57 am

    #640047

    you can use quotes around the values where you think data have "," in between.

    e.g:

    Select Id, '"' + isNull(Location) + '"', col2.. from table....

    This will result in something like

    1,"Spain, Mobile", 2332.... and will work for csv files.

    Thanks

    Brij

  • May 25, 2006 at 7:59 am

    #640048

    1. I am specifying "," as field terminator. How do I stop a column from splitting into 2 if there's comma there? like Spain, Mobile (should be 1 column, not 2 in file)

    square bracketts should so it [Spain, Mobile],

    2. How do I get the column names in the csv file?

    not sure what you mean here.

    www.sql-library.com[/url]

  • May 25, 2006 at 8:55 am

    #640075

    Hi all,

    Great it worked out!! All you guys are stars!

    Cheers!"

    Shams

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

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