problem with temporary table.

Question

problem with temporary table.

shams-179074

SSC Enthusiast

Points: 186
More actions
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

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

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

brij_rauthan SSCommitted Points: 1521 More actions · Answer 1

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

shams-179074 SSC Enthusiast Points: 186 More actions · Answer 2

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

Jules Bonnot SSCertifiable Points: 5488 More actions · Answer 3

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]

brij_rauthan SSCommitted Points: 1521 More actions · Answer 4

brij_rauthan

SSCommitted

Points: 1521

May 25, 2006 at 7:45 am

#640038

I agree with Jules.

Brij

shams-179074 SSC Enthusiast Points: 186 More actions · Answer 5

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

brij_rauthan SSCommitted Points: 1521 More actions · Answer 6

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

Jules Bonnot SSCertifiable Points: 5488 More actions · Answer 7

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]

shams-179074 SSC Enthusiast Points: 186 More actions · Answer 8

Hi all,

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

Cheers!"

Shams