Problem using a SP with temp tables as a record source for a Report in Access 2000

[Chris Strug]

Hi,

I have an Access 2000 ADP project.

I also have a Stored Procedure. The SP contains a temporary table (I know I
know but I haven't been able to think of another way of doing what I what
without them).

When I try to use this SP as a record source for a report I get an error:

<ADP Name> can't find the object '<ADP Name> can't find the object
'spTempTest.'.'

* You misspelled the object name. Check for missing underscores ( _ ) or
other punctuation, and make sure you didn't enter leading spaces.
* You tried to open a linked table, but the file containing the table isn't
on the path you specified. Use the linked table manager to update the link
and point to the correct path.

I also get this error if I attempt to use the SP as a record source for a
form.

The SP runs fine if I open from the Database Window or from Query Analyser.

Simply, does the use of temp tables (SELECT * INTO #temp FROM blah" SELECT *
FROM #temp) in a stored procedure prevent that SP from being used as a
record source?

Is this the case and if it is there a work-around for this? I've spent ages
writing this SP and if I have to rewrite it before I can use it as a record
source I'll be upset to say the least.

Any and all advice is gratefully received.

Chris.

 

[BJ Freeman]

check to see of the user has permissions.

 

[SFAxess]

You may need to precede the sp name with "dbo." when
referring to it as a recordsource or from code.
i.e.
dbo.spTempTest

 

[Chris Strug]

You may need to precede the sp name with "dbo." when
referring to it as a recordsource or from code.
i.e.
dbo.spTempTest

Thanks for both the replies. I have explicity granted myself permission to
execute the SP in question with no luck. I have also tried stating the name
of the SP with the DBO. prefix. Again, no luck.

Any further ideas?

Chris.

 

[SFAxess]

I had a similiar issue before, but I was not using a temp
table in the sproc. The temp table me be causing trouble.
The only thing I can think of you don't have permission
to create the temp table on the server, but it sounds
like you gave yourself dbo permissions and it still
doesn't work. Im sorry I can't be of more help.

 

[Daran Johnson]

Make sure that NOCOUNT is set to OFF.

I had a similiar issue before, but I was not using a temp
table in the sproc. The temp table me be causing trouble.
The only thing I can think of you don't have permission
to create the temp table on the server, but it sounds
like you gave yourself dbo permissions and it still
doesn't work. Im sorry I can't be of more help.