Resolving the #temp not found Error in Cross-Database Queries with Classic ASP

When working with databases in Classic ASP, especially when trying to generate reports from multiple database sources, you may encounter some common errors. One such issue arises during cross-database queries in Sybase when utilizing temporary tables. In this blog post, we will address a specific problem encountered when querying two databases on the same server and provide a clear solution to resolve it.

The Problem

In the scenario presented, the user is attempting to query two different databases (databaseA and databaseB) on a Sybase server to generate a report. They are using two separate connection strings (connA for databaseA and connB for databaseB), and the primary issue arises from attempting to utilize a temporary table (#temp), which is being created in one database and referenced in another.

When executing the following SQL commands:

q1 = SELECT column1 INTO #temp FROM databaseA..table1 WHERE xyz="A"
q2 = SELECT columnA,columnB,...,columnZ FROM table2 a #temp b WHERE b.column1=a.columnB

An error message appears stating:

Microsoft OLE DB Provider for ODBC Drivers error ‘80040e37’ [DataDirect][ODBC Sybase Wire Protocol driver][SQL Server]#temp not found. Specify owner.objectname or use sp_help to check whether the object exists.

Here’s what’s happening: the temporary table #temp exists within the scope of the connection to databaseA, but when attempting to access it from databaseB, it doesn’t recognize its existence, leading to the error.

The Solution

To resolve this issue, follow these key steps:

1. Modify the Query Syntax

To make sure that #temp is recognized correctly across your SQL statements, explicitly specify the database name in your queries. Modify your existing commands to include the database name in the SELECT INTO statement as follows:

q1 = SELECT column1 INTO databaseA.dbo.#temp FROM databaseA..table1 WHERE xyz="A"
q2 = SELECT columnA,columnB,...,columnZ FROM table2 a, databaseA.dbo.#temp b WHERE b.column1=a.columnB

2. Confirm Permissions on Connection Strings

Make sure that the connection strings connA and connB have the necessary permissions set for each database. This ensures that when executing queries across databases, both connections can access the required tables without any permission errors. You may need to check the user credentials being used in those connection strings to ensure they have the appropriate access rights.

3. Manage Connection Scope Effectively

If both databases share the same user permissions, consider keeping the connection open between queries. Use the same connection (connA or connB) for both queries while referring to tables in the appropriate database using the format [DBName].[Owner].[TableName]. This helps to avoid scope issues that arise from temporary tables closing or going out of context.

Example Implementation

Here is an example code implementation taking these considerations into account:

set rstSQL = CreateObject("ADODB.Recordset")
rstSQL.Open q1, connA  ' First, open the connection to databaseA
rstSQL.Open q2, connA  ' Use the same connA for querying the temp table

Conclusion

By specifying the full database name in your query and ensuring that both connection strings have the right permissions, you can successfully execute cross-database queries without encountering the #temp not found error. By following these guidelines, you can effectively manage temporary tables and perform complex queries in Classic ASP with Sybase.

Thank you for reading, and happy coding! If you have any further questions or need assistance, feel free to leave a comment.