在不同数据库服务器上的2个表上选择“查询" [英] Select Query on 2 tables, on different database servers

问题描述

我试图通过在经典ASP中查询2个数据库(Sybase)来生成报告.

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

我创建了2个连接字符串:

I have created 2 connection strings:

数据库A的connA
数据库B的connB

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

其次:

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错误'80040e37'

Microsoft OLE DB Provider for ODBC Drivers error '80040e37'

找不到[DataDirect] [ODBC Sybase Wire Protocol驱动程序] [SQL Server] #temp.指定owner.objectname或使用sp_help检查对象是否存在(sp_help可能会产生大量输出).

[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?

谢谢.

推荐答案

对于这两个查询，您似乎都想插入#temp. #temp位于其中一个数据库上(出于参数考虑，databaseA).因此，当您尝试从databaseB插入#temp时，它报告它不存在.

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.

尝试将其从更改为 #temp 从更改为更改为 databaseA.dbo.#temp 从陈述.

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.

更新:与临时表超出范围有关-如果您有一个对两个数据库都具有权限的连接字符串，则可以将其用于两个查询(同时保持连接处于活动状态).在另一个数据库中查询表时，请确保在引用表时使用[DBName].[Owner].[TableName]格式.

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.

这篇关于在不同数据库服务器上的2个表上选择“查询"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！