"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