在不同数据库服务器上的2个表上选择Query

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

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

I have created 2 connection strings:

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

connA for databaseA
connB for databaseB

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

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

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'

Microsoft OLE DB Provider for ODBC Drivers错误'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).

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

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

谁能帮助我了解问题所在并帮助我解决问题?

Thanks.

3 个解决方案

#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.

对于这两个查询,看起来您正试图插入#temp。 #temp位于其中一个数据库上(为了参数,databaseA)。因此,当您尝试从databaseB插入#temp时,它会报告它不存在。

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

尝试将它从Into #temp From更改为Into databaseA.dbo。#temp来自两个语句。

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.

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

#2

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

你的临时表超出了范围,在第一次连接时它只是“活着”而在第二个连接中不可用只需在一个代码块中移动所有它并在一个连接内执行它

#3

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