469,950 Members | 1,902 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,950 developers. It's quick & easy.

Temporary table problem (maybe)

Hi,

I have a stored procedure, that works perfectly when run from the query
analyser, however if I run it through access vba, (using exec) I get a
runtime error 208: invalid object name '#tmpContact'.

Any ideas why this happens? The temporary table #tmpContact is used in
the procedure, but as I say, it all works fine from the analyser.

Thanks,
Chris
Jul 20 '05 #1
4 2474

"Not Me" <No***********@here.com> wrote in message
news:ci**********@ucsnew1.ncl.ac.uk...
Hi,

I have a stored procedure, that works perfectly when run from the query
analyser, however if I run it through access vba, (using exec) I get a
runtime error 208: invalid object name '#tmpContact'.

Any ideas why this happens? The temporary table #tmpContact is used in the
procedure, but as I say, it all works fine from the analyser.

Thanks,
Chris


It's not really possible to say without seeing the procedure code. Is the
table created in the procedure or outside it? If outside, then has the
Access connection created it before executing the procedure? Normal temp
tables are only visible to the connection that created them.

Simon
Jul 20 '05 #2
Simon Hayes wrote:
"Not Me" <No***********@here.com> wrote in message
news:ci**********@ucsnew1.ncl.ac.uk...
I have a stored procedure, that works perfectly when run from the query
analyser, however if I run it through access vba, (using exec) I get a
runtime error 208: invalid object name '#tmpContact'.

Any ideas why this happens? The temporary table #tmpContact is used in the
procedure, but as I say, it all works fine from the analyser.
It's not really possible to say without seeing the procedure code. Is the
table created in the procedure or outside it? If outside, then has the
Access connection created it before executing the procedure? Normal temp
tables are only visible to the connection that created them.


Thanks for the reply (and sorry for replying to your email! easily done
I'm afraid)

I won't copy&paste the whole thing in here, as I know it works perfectly
from the analyser.. all the necessary code is within the procedure, all
VBA does is run it.

The gist of the procedure is as follows:

create temp table
insert data into temp table
drop temp table

OK there are a few other things going on but relatively simple and can
be commented out whilst still producing the problem.
I get the error on the 2nd part of the procedure, when inserting the
data into the temp table (create/drop work fine). As I say it all works
fine anyway when run from the query analyser...

Cheers,
Chris
Jul 20 '05 #3

"Not Me" <No***********@here.com> wrote in message
news:ci**********@ucsnew1.ncl.ac.uk...
Simon Hayes wrote:
"Not Me" <No***********@here.com> wrote in message
news:ci**********@ucsnew1.ncl.ac.uk...
I have a stored procedure, that works perfectly when run from the query
analyser, however if I run it through access vba, (using exec) I get a
runtime error 208: invalid object name '#tmpContact'.

Any ideas why this happens? The temporary table #tmpContact is used in
the procedure, but as I say, it all works fine from the analyser.

It's not really possible to say without seeing the procedure code. Is the
table created in the procedure or outside it? If outside, then has the
Access connection created it before executing the procedure? Normal temp
tables are only visible to the connection that created them.


Thanks for the reply (and sorry for replying to your email! easily done
I'm afraid)

I won't copy&paste the whole thing in here, as I know it works perfectly
from the analyser.. all the necessary code is within the procedure, all
VBA does is run it.

The gist of the procedure is as follows:

create temp table
insert data into temp table
drop temp table

OK there are a few other things going on but relatively simple and can be
commented out whilst still producing the problem.
I get the error on the 2nd part of the procedure, when inserting the data
into the temp table (create/drop work fine). As I say it all works fine
anyway when run from the query analyser...

Cheers,
Chris


Assuming there's nothing like dynamic SQL or nested procedures involved ,
then it sounds like it should work fine. I don't know much about Access, but
some client libraries need metadata about the result set of a query. With
OLE DB, I believe it uses SET FMTONLY ON behind the scenes to get this
information, and that would cause your error:

/* Make a dummy proc */
create proc p
as
begin
create table #t (col1 int)
select * from #t
end
go

/* Test it */
print 'This will work'
exec p

/* SET FMTONLY ON - the EXEC should now fail with error 208 */
print 'This will not work'
set fmtonly on
exec p
go

/* Clean up */
set fmtonly off
drop proc p
go
This is just a guess, but it would explain why it works in QA but not from
another client. You could test by using Profiler to trace the SQL sent by
your Access application - if you see SET FMTONLY ON, then that's the likely
cause. If so, you could use a table-valued UDF as an alternative to a temp
table, or you might get other suggestions in an Access/ADO/OLE DB forum.

If this doesn't help, then I suggest you post the simplest code you can that
demonstrates the problem, to see if others can recreate the error.

Simon
Jul 20 '05 #4
Simon Hayes wrote:
"Not Me" <No***********@here.com> wrote in message
news:ci**********@ucsnew1.ncl.ac.uk...
The gist of the procedure is as follows:

create temp table
insert data into temp table
drop temp table

OK there are a few other things going on but relatively simple and can be
commented out whilst still producing the problem.
I get the error on the 2nd part of the procedure, when inserting the data
into the temp table (create/drop work fine). As I say it all works fine
anyway when run from the query analyser...
Assuming there's nothing like dynamic SQL or nested procedures involved ,
then it sounds like it should work fine. I don't know much about Access, but
some client libraries need metadata about the result set of a query. With
OLE DB, I believe it uses SET FMTONLY ON behind the scenes to get this
information, and that would cause your error: <snip> This is just a guess, but it would explain why it works in QA but not from
another client. You could test by using Profiler to trace the SQL sent by
your Access application - if you see SET FMTONLY ON, then that's the likely
cause. If so, you could use a table-valued UDF as an alternative to a temp
table, or you might get other suggestions in an Access/ADO/OLE DB forum.


Excellent, thanks that's sorted my problems.. (creating a duplicate
problem at the same time, but at least now I know what's going on!)

Cheers,
Chris
Jul 20 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by airkart | last post: by
2 posts views Thread by Thomas T. Thai | last post: by
6 posts views Thread by gimme_this_gimme_that | last post: by
4 posts views Thread by sandeep.iitk | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.