SQL 跨库查询

　　　　执行成功后，在左侧可以看到一个链接对象

2，创建登录信息（两种方式任选一种）

　  2.1 以windows认证的方式登录

<span style="color: #0000ff">EXEC</span><span style="color: #000000"> sp_addlinkedsrvlogin 
</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">DB_Name</span><span style="color: #ff0000">‘</span>,  <span style="color: #008080">--</span><span style="color: #008080">链接服务器别名</span>
<span style="color: #ff0000">‘</span><span style="color: #ff0000">true</span><span style="color: #ff0000">‘</span>      <span style="color: #008080">--</span><span style="color: #008080">以windows认证的方式登录</span>
<span style="color: #0000ff">GO</span>

　 2.2 以SQL认证的方式登录

<span style="color: #0000ff">EXEC</span><span style="color: #000000"> sp_addlinkedsrvlogin
</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">DB_Name</span><span style="color: #ff0000">‘</span>,               <span style="color: #008080">--</span><span style="color: #008080">链接服务器别名</span>
<span style="color: #ff0000">‘</span><span style="color: #ff0000">false</span><span style="color: #ff0000">‘</span>,                 <span style="color: #008080">--</span><span style="color: #008080">以SQL认证的方式登录</span>
 <span style="color: #0000ff">NULL</span><span style="color: #000000">,
</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">sa</span><span style="color: #ff0000">‘</span>,                    <span style="color: #008080">--</span><span style="color: #008080">要访问的数据库的用户              </span>
<span style="color: #ff0000">‘</span><span style="color: #ff0000">sasa</span><span style="color: #ff0000">‘</span>                   <span style="color: #008080">--</span><span style="color: #008080">要访问的数据库用户的密码</span>
<span style="color: #0000ff">GO</span>

3，链接服务器查询示例

SELECT * FROM [链接服务器别名].[数据库名].[架构名].[表名]

<span style="color: #008080">--</span><span style="color: #008080"> 示例</span>
<span style="color: #0000ff">SELECT</span> <span style="color: #808080">*</span> <span style="color: #0000ff">FROM</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">DB_Name</span><span style="color: #ff0000">]</span>.<span style="color: #ff0000">[</span><span style="color: #ff0000">ReportServer</span><span style="color: #ff0000">]</span>.<span style="color: #ff0000">[</span><span style="color: #ff0000">dbo</span><span style="color: #ff0000">]</span>.<span style="color: #ff0000">[</span><span style="color: #ff0000">tb_Festivals</span><span style="color: #ff0000">]</span>

SQL 跨库查询

标签：gpo   height   架构   分享图片   密码   建立数据库   链接服务器   com   查询