在不同的数据库服务器上选择 2 个表上的查询
我正在尝试通过在经典 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:
用于 ODBC 驱动程序的 Microsoft OLE DB 提供程序错误80040e37"
Microsoft OLE DB Provider for ODBC Drivers error '80040e37'
[DataDirect][ODBC Sybase 有线协议驱动程序][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.
尝试在两者中将其从 Into #temp From 更改为 Into databaseA.dbo.#temp From陈述.
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.
更新:与临时表超出范围有关 - 如果您有一个对两个数据库都具有权限的连接字符串，那么您可以将其用于两个查询(同时保持连接处于活动状态).查询其他DB中的表时，请务必使用[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.
相关文章