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)
set rstSQL = CreateObject("ADODB.Recordset")
rstSQL.Open q1, connA
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 sphelp to check whether the object exists (sphelp may produce lots of output).

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

Thanks.

Answer 1

your temp table is out of scope, it is only 'alive' during the first connection and will not be available in the 2nd connection Just move all of it in one block of code and execute it inside one conection

Answer 2

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.

Answer 3

@SQLMenace

your temp table is out of scope, it is only 'alive' during the first connection and will not be available in the 2nd connection Just move all of it in one block of code and execute it inside one conection

How do I do that?
The second table (table2) is only accessible from databaseB. Could you give me an example?

Answer 4

temp is out of scope in q2.

All your work can be done in one query:

SELECT a.columnA, a.columnB,..., a.columnZ
FROM table2 a
INNER JOIN (SELECT databaseA..table1.column1 
            FROM databaseA..table1
            WHERE databaseA..table1.xyz = 'A') b
  ON a.columnB = b.column1

Answer 5

@Yaakov Ellis and Forgotten Semicolon:

Thanks! I will try out you suggestions and let you know how it works out.

Answer 6

Okay, I tried changing all references to the temporary table #temp to databaseA..#temp and now I see this:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[DataDirect][ODBC Sybase Wire Protocol driver]Connection refused. Verify Host Name and Port Number.

Does this mean databaseB does not have the necessary permissions to access databaseA?