Select Query on 2 tables, on different database servers,
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)
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
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 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?
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.
That’s the answer Select Query on 2 tables, on different database servers, Hope this helps those looking for an answer. Then we suggest to do a search for the next question and find the answer only on our site.
The answers provided above are only to be used to guide the learning process. The questions above are open-ended questions, meaning that many answers are not fixed as above. I hope this article can be useful, Thank you