<p>I am trying to generate a report by querying 2 databases (Sybase) in classic ASP.</p>
<p>I have created 2 connection strings:<br></p>
<blockquote>
<p>connA for databaseA<br>
connB for databaseB</p>
</blockquote>
<p>Both databases are present on the same server (don't know if this matters)<br></p>
<p>Queries:</p>
<p><code>q1 = SELECT column1 INTO #temp FROM databaseA..table1 WHERE xyz=&quot;A&quot;</code></p>
<p><code>q2 = SELECT columnA,columnB,...,columnZ FROM table2 a #temp b WHERE b.column1=a.columnB</code></p>
<p>followed by:</p>
<pre><code>response.Write(rstsql) &lt;br&gt;
set rstSQL = CreateObject(&quot;ADODB.Recordset&quot;)&lt;br&gt;
rstSQL.Open q1, connA&lt;br&gt;
rstSQL.Open q2, connB
</code></pre>
<p>When I try to open up this page in a browser, I get error message:</p>
<blockquote>
<p>Microsoft OLE DB Provider for ODBC Drivers error '80040e37'</p>
<p>[DataDirect][ODBC Sybase Wire Protocol driver][SQL Server]#temp not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output).</p>
</blockquote>
<p>Could anyone please help me understand what the problem is and help me fix it?</p>
<p>Thanks.</p>

[{'answer_id': 16461, 'author': 'SQLMenace', 'author_id': 740, 'author_profile': 'https://Stackoverflow.com/users/740', 'pm_score': 2, 'selected': False, 'text': "<p>your temp table is out of scope, it is only 'alive' during the first connection and will not be available in the 2nd connection\nJust move all of it in one block of code and execute it inside one conection</p>\n"}, {'answer_id': 16465, 'author': 'Yaakov Ellis', 'author_id': 51, 'author_profile': 'https://Stackoverflow.com/users/51', 'pm_score': 3, 'selected': True, 'text': '<p>With both queries, it looks like you are trying to insert into #temp. #temp is located on one of the databases (for arguments sake, databaseA). So when you try to insert into #temp from databaseB, it reports that it does not exist.</p>\n\n<p>Try changing it from <em>Into <strong>#temp</strong> From</em> to <em>Into <strong>databaseA.dbo.#temp</strong> From</em> in both statements. </p>\n\n<p>Also, make sure that the connection strings have permissions on the other DB, otherwise this will not work.</p>\n\n<p>Update: relating to the temp table going out of scope - if you have one connection string that has permissions on both databases, then you could use this for both queries (while keeping the connection alive). While querying the table in the other DB, be sure to use [DBName].[Owner].[TableName] format when referring to the table.</p>\n'}, {'answer_id': 16474, 'author': 'Forgotten Semicolon', 'author_id': 1960, 'author_profile': 'https://Stackoverflow.com/users/1960', 'pm_score': 2, 'selected': False, 'text': "<h1>temp is out of scope in q2.</h1>\n<p>All your work can be done in one query:</p>\n<pre><code>\nSELECT a.columnA, a.columnB,..., a.columnZ\nFROM table2 a\nINNER JOIN (SELECT databaseA..table1.column1 \n            FROM databaseA..table1\n            WHERE databaseA..table1.xyz = 'A') b\n  ON a.columnB = b.column1\n</code></pre>\n"}]
['https://Stackoverflow.com/questions/16447', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/1311/']