Select Query on 2 tables, on different database servers

Question

I am trying to generate a report by querying 2 databases (Sybase) in classic ASP.

I have created 2 connection strings:

connA for databaseA
connB for databaseB

Both databases are present on the same server (don't know if this matters)

Queries:

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

followed by:

response.Write(rstsql) <br>
set rstSQL = CreateObject("ADODB.Recordset")<br>
rstSQL.Open q1, connA<br>
rstSQL.Open q2, connB

When I try to open up this page in a browser, I get error message:

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 (sp_help may produce lots of output).

Could anyone please help me understand what the problem is and help me fix it?

Thanks.

Answer 1

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.

Try changing it from Into #temp From to Into databaseA.dbo.#temp From in both statements.

Also, make sure that the connection strings have permissions on the other DB, otherwise this will not work.

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.