473,320 Members | 1,900 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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 2613

"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Corrine | last post by:
Hi, I am creating a global temporary table that is session-specific. I insert a BLOB into this table, and then select the BLOB from this table into a ResultSet. The ResultSet sees this BLOB...
2
by: airkart | last post by:
Hello, I've scoured groups and the web, and haven't found a question like mine answered. I'm using Visual Studio 2003 with the Crystal Reports it comes bundled with and SQL Server 2000....
11
by: randi_clausen | last post by:
Using SQL against a DB2 table the 'with' key word is used to dynamically create a temporary table with an SQL statement that is retained for the duration of that SQL statement. What is the...
2
by: Thomas T. Thai | last post by:
I would like to select a random record from a group of records so I'd end up with one random record per group: CREATE TABLE randtest ( catnum int, title varchar(32) ); INSERT INTO randtest...
30
by: btober | last post by:
Whenever I create a temporary table, with something like CREATE TEMPORARY TABLE temptable1 AS SELECT * FROM paid.ad_hoc_query; New schemas appear, with names like "pg_temp_1". I guess the...
6
by: gimme_this_gimme_that | last post by:
I'm new to DB2 ... The following statement results in a SQL1585N message : "A system temporary table space with sufficient page size does not exist" Note that the column FIELD_DETAIL is a...
3
by: pinney.colton | last post by:
I would like to create a stored procedure which creates a temp table to store some XML. The # of fields of XML is dependent upon the contents of another table in the application, so the first part...
4
by: sandeep.iitk | last post by:
Hi, One query is failing on the database and its a long query which was running fine earlier. One possible reason we can think of is increase in data in tables in query. It is failing with...
6
by: Troels Arvin | last post by:
Hello, I have recently run a rather large data import where the imported data i pumped through some updatable views equipped with INSTEAD OF triggers. For various reasons, the exact same data...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.