All geek questions in one place

Select Query in two tables on different database servers.

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

I created two connection strings:

connA for the database

connB for databaseB

Both databases are present on the same server (I don’t know if that matters)

Inquiries

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

and then:

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

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

Microsoft OLE DB Provider for ODBC Driver Error '80040e37'

[DataDirect] [Sybase Wire ODBC Protocol Driver] [SQL Server] #temp not found. Indicate owner.object or use sp_help to check if an object exists (sp_help can generate a lot of output).

Can someone help me understand what the problem is and help me fix it?

Thanks.

+4
source share
3 answers

In both queries, it looks like you are trying to embed in #temp. #temp is in one of the databases (for sake, databaseA arguments). Therefore, when you try to paste into #temp from databaseB, it reports that it does not exist.

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

Also, make sure that the connection strings have permissions for another database, otherwise this will not work.

Update: the temp table is out of scope - if you have one connection string that has permissions for both databases, then you can use it for both queries (keeping the connection alive). When querying a table in another database, be sure to use the [DBName] format. [Owner]. [TableName] when accessing a table.

+4
source

your temporary table goes out of scope, it is only "alive" during the first connection and will not be available in the second connection Just move all this in one block of code and execute it in one connector

+4
source

temp is out of scope in q2.

All your work can be done in one request:

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
+2
source

Source: https://habr.com/ru/post/1276371/

More articles:


All Articles