在多个数据库之间加入 [英] Joining between multiple databases

问题描述

更新我得到DataBaseA.dbo.templates的无效对象名称错误

我希望Inner连接来自独立数据库的两个表在同一个服务器（本地）。

但我似乎无法得到它的工作。另外我不明白我应该如何处理我的连接串，因为我目前正在设置当前连接到我的数据库的连接串。但在这个例子中，我试图连接到同一查询中的两个不同的数据库。

这是我的SQL代码到目前为止：

 SELECT a.template。*，b.c_template。* FROM DatabaseA.dbo.templates一个INNER JOIN DatabaseB.dbo.c_template b ON a.id = b .template_id
  

这是我的连接字符串：

 < add name =UnitconnectionString =Data Source =（LocalDB）\v11.0; AttachDbFilename = | DataDirectory | \DatabaseA.mdf; Integrated Security = True 
 providerName =System.Data.SqlClient/> 
< add name =ContentconnectionString =Data Source =（LocalDB）\v11.0; AttachDbFilename = | DataDirectory | \DatabaseB.mdf; Integrated Security = True
 providerName = System.Data.SqlClient/> 
  

解决方案

你知道如果表模板模式所有者为dbo或者它是别的东西。你可以执行下面的查询，看看如果你找到表？

  SELECT * FROM Unit.dbo.templates。 
  

如果没有找到表，应该使用以下命令找出模式所有者： -

 使用[单位] 
 Go 
 sp_help模板
  

在下面的示例中，模式所有者是dbo。当你执行你的，你会看到一些其他模式所有者（例如xyz）。使用该模式所有者。

  SELECT * FROM Unit.xyz.template 
  pre> 
 
 
 > 
 
 
 
如果你仍然找不到表，这听起来像是来自应用程序的查询的默认数据库被设置到表tenplate不存在的数据库。你可以尝试使用不同形式的连接字符串。在下面的连接字符串中的myUsername应该对DatabaseA和DatabaseB具有相同的所需特权： -  
  Server = myServerAddress; myDataBase; User Id = myUsername; 
 Password = myPassword; 
  
示例： -  
  Server = localDB\v11.0; Database = DataBaseA; User Id = UserA; Password = UserAPassword; 
 
创建名为UserA的数据库用户，并具有DatabaseA和DatabaseB的适当访问权限。
 
Update I get an Invalid object name error for the DataBaseA.dbo.templates

I wish to Inner join two tables from seperate databases on the same server (Local).

But i can't seem to get it work. Also i don't understand how i should be handling my connectionstring, as i'm currently setting the current connection to the connectionstring of my database. But in this example i'm trying to connect to two different databases in the same query.

This is my SQL code so far:
"SELECT a.template.*, b.c_template.* FROM DatabaseA.dbo.templates a INNER JOIN DatabaseB.dbo.c_template b ON a.id = b.template_id"
And this is my connectionstrings:
<add name="Unit" connectionString="Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\DatabaseA.mdf;Integrated Security=True"
        providerName="System.Data.SqlClient" />
    <add name="Content" connectionString="Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\DatabaseB.mdf;Integrated Security=True"
        providerName="System.Data.SqlClient" />

解决方案
Do you know If the table "templates" has the schema owner as "dbo" or It is something else. Can you execute the following query and see If you find the table? 
SELECT * FROM Unit.dbo.templates.
If you don't find the table, you should use the the following command to find out the schema owner:-
Use [Unit]
Go
sp_help templates
In the below example, the schema owner is dbo. When you execute yours, you would see some other schema owner (e.g xyz). Use that schema owner.
SELECT * FROM Unit.xyz.template


If you are still not able to find the table, It sounds like the default database for the query from the application is set to the database where the table "tenplate" does not exist. Can you try using a different form of connection string. "myUsername" in the below connection string should have same required privileges to both DatabaseA And DatabaseB:- 
Server=myServerAddress;Database=myDataBase;User Id=myUsername;
Password=myPassword; 
Example :-
Server=localDB\v11.0;Database=DataBaseA;User Id=UserA;Password=UserAPassword; 
Create a database user named "UserA" and have appropriate access to both DatabaseA and DatabaseB.

                        
这篇关于在多个数据库之间加入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！