不同服务器数据库之间的数据操作&--创建链接服务器 exec sp_addlinkedserver
'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 ' exec sp_addlinkedsrvlogin
'ITSV ', 'false ',null, '用户名 ', '密码 ' &--查询示例 select * from ITSV.数据库名.dbo.表名 &--导入示例 select * into 表 from ITSV.数据库名.dbo.表名 &--以后不再使用时删除链接服务器 exec sp_dropserver
'ITSV ', 'droplogins ' &--连接远程/局域网数据(openrowset/openquery/opendatasource) --1、openrowset &--查询示例 select * from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名) &--生成本地表 select * into 表 from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名) &--把本地表导入远程表 insert openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名) select *from 本地表 &--更新本地表 update b set b.列A=a.列A
from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)as a inner join 本地表 b on a.column1=b.column1 &--openquery用法需要创建一个连接 &--首先创建一个连接创建链接服务器 exec sp_addlinkedserver
'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 ' --查询 select * FROM openquery(ITSV,
FROM 数据库.dbo.表名 ') --把本地表导入远程表 insert openquery(ITSV,
FROM 数据库.dbo.表名 ') select * from 本地表 --更新本地表 update b set b.列B=a.列B FROM openquery(ITSV,
'SELECT * FROM 数据库.dbo.表名 ') as a
inner join 本地表 b on a.列A=b.列A &--3、opendatasource/openrowset SELECT
opendatasource( 'SQLOLEDB ',
'Data Source=ip/ServerNUser ID=登陆名;Password=密码 ' ).test.dbo.roy_ta --把本地表导入远程表 insert opendatasource( 'SQLOLEDB ',
'Data Source=ip/ServerNUser ID=登陆名;Password=密码 ').数据库.dbo.表名 select * from 本地表
阅读(...) 评论()管家婆：用户 'sa' 登录失败。原因: 未与信任 SQL Server 连接... 求远程操作QQ:谢谢了表明SQL_百度知道211553人阅读
在与 SQL Server 建立连接时出现与网络相关的或特定于实例的错误。未找到或无法访问服务器。请验证实例名称是否正确并且 SQL Server 已配置为允许远程连接。 (provider: 命名管道提供程序, error: 40 - 无法打开到 SQL Server 的连接)
说明: 执行当前 Web 请求期间，出现未处理的异常。请检查堆栈跟踪信息，以了解有关该错误以及代码中导致错误的出处的详细信息。 异常详细信息: System.Data.SqlClient.SqlException: 在与 SQL Server 建立连接时出现与网络相关的或特定于实例的错误。未找到或无法访问服务器。请验证实例名称是否正确并且 SQL Server 已配置为允许远程连接。 (provider: 命名管道提供程序, error: 40 - 无法打开到 SQL Server 的连接)
当出现这个问题时,其实很简单,是不同的数据库版本,地连接方法个一样;注意以下红字部分。我以前是用Server=.;database=User ID=Password=
改成了Server==.//SQLEXPRESS;Initial Catalog=User ID=Password=
折腾了好久才搞好。
&1.开启sql2005远程连接功能，开启办法如下：&&&&&&& 配置工具-&Sql Server外围应用配置器-&服务和连接的外围应用配置器-&打开MSSQLSERVER节点下的Database Engine节点，先择&远程连接&，接下建议选择&同时使用TCP/IP和named pipes&，确定后重启数据库服务就可以了。
&&&&&&& 2.登陆设置改为：Sql Server&和 Windows 身份验证模式，具体设置如下:&&&&&&& SQL Server Management Studio管理器-&Windows 身份验证连接服务器-&对象资源管理器中选择你的数据服务器－&右键－&属性－&安全性－&Sql Server&和 Windows 身份验证模式选中。
&&&&&&&&3.设置一个Sql Server方式的用户名和密码，具体设置如下:&&&&&&& （1）SQL Server Management Studio管理器-&Windows 身份验证连接服务器－&对象资源管理器中选择你的数据服务器－&展开服务器上的&安全性&-&登陆名－&在sa帐号上点右键－&&选择页&选择常规-&更改sa登陆帐号的密码。这样就设置了一个用户名为sa，密码为:sa123456的用户。
&&&&&&&&（2）&选择页&选择状态-&登陆修改为启用
&&&&&&& 4.数据库连接字符串
&&&&&&& 数据库连接字符串有好几种，相信你自己也见过，都被搞晕了
&&&&&&& Data Server=./SQLEXPRESS;Initial Catalog=NUser ID=Password=sa123456
&&&&&&& Data Server=服务器名/SQLEXPRESS;Initial Catalog=NUser ID=Password=sa123456
&&&&&&& Data Server=localhost/SQLEXPRESS;Initial Catalog=NUser ID=Password=sa123456
&&&&&&&&Data Server=.;Initial Catalog=NUser ID=Password=sa123456
&&&&&&&&Data Server=服务器名;Initial Catalog=NUser ID=Password=sa123456
&&&&&&& &&
&&&&&&&&到底那种正确？这跟数据库版本有关系，如果是Sql Server 2005 Express版本，则必须要有&/SQLEXPRESS&。而且如果这个字符串是定义为一个变量的时候，VS2005还会在&/&的下面加个红色的波浪线提示你&/S是无法识别的转义序列&，因此如果字符串是定义为一个变量的时候应该写成Server=.//SQLEXPRESS
&&&&&&& 5.注册Sql Server数据库
&&&&&&& 在路径&C:/Windows/Microsoft.NET/Framework/v2.0.50727&下运行&ASPNET_REGSQL&指令，就会出现ASP.NET SQL Server Setup Wizard向导，连续按下两个下一步后就会出现Sql Server注册界面，填入你要注册的数据库参数就注册好了。注册完后会在你的数据库里多几个表（如图）：
&&&&&&& 5.设置数据库连接字符串
&&&&&&& 打开IIS－&在默认网站或是网站所在的虚拟目录点击右键选择属性－&选择ASP.NET选项卡－&编辑配置－&在&常规&页签编辑&LocalSqlServer&数据库连接字符串：
&&&&&&& Data Server=./SQLEXPRESS;Initial Catalog=NUser ID=Password=sa123456
&&&&&&& 6.设置web.config文件
&&&&&&&&在web.config文件添加如下程序：
&connectionStrings&&&&add name="LocalSqlServer" connectionString="Data Source=./SQLEXPRESS;Initial Catalog=NUser ID=Password=sa123456" providerName="System.Data.SqlClient"/&&&/connectionStrings&
&&&&&&& 这样便大功告成了。下面来测试
&&&&&&& 1.宽松的连接测试
&&&&&&& 宽松的连接测试使用SqlDataSource
&&&&&&&&VS2005服务器资源管理器选中数据库点击右键－&选择修改连接－&填入服务器名－&使用Sql Server身份验证填入用户名sa及密码sa123456－&选择或输入一个数据库名：Northwind－&点击测试连接
&&&&&&& 宽松的连接测试连接成功并不能说明数据库就连接OK了，一开始我就是挂在这里，宽松的连接测试测试连接成功，可是始终无法读出数据库的内容到网页里头。
&&&&&&& 2.严谨的连接测试
&&&&&&& 在VS2005里选择网站菜单－&ASP.NET配置进入ASP.NET网站管理工具－&选择提供程序－&点击AspNetSqlProvider的测试，若测试成功，说明数据库设置完全正确，否则得从头来过好好查查问题在哪里。
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：406600次
积分：3113
积分：3113
排名：第8176名
原创：94篇
转载：18篇
评论：82条
(1)(1)(1)(1)(1)(3)(2)(1)(4)(3)(1)(3)(1)(7)(5)(2)(3)(2)(2)(1)(3)(3)(1)(2)(1)(1)(3)(7)(4)(1)(2)(3)(7)(2)(7)(3)(2)(17)笔记49 SQL2005使用数据库邮件的步骤
1 --SQL2005使用数据库邮件的步骤
5 在任何数据库中启用 SQL Server Service Broker 都需要数据库锁。如果在
中停用了 Service Broker，则若要启用数据库邮件，应首先停止 SQL Server 代理，以使 Service Broker 可以获取所需的锁。
8 sqlserver没有搭建SMTP服务器，所以在创建帐户的时候写的email地址是发件人地址
10 如果在帐户里写xxxxx@163.com
13 在存储过程里面写
14 EXEC [msdb].[dbo].[sp_send_dbmail]
@profile_name = 'joe_profile' , -- sysname
--设置了配置文件也没有用monitorGPOSDB，内容还是跟没有设置配置文件一样
@recipients = '', -- varchar(max)
18 那么登录163之后会看到邮件标题是
19 即sqlserver 发邮件到smtp.163.com
然后smtp.163.com 自己发翻俾自己
20 因为帐户里的邮箱地址是xxxxi@163.com
存储过程里的邮箱地址是xxxx@163.com
23 如果在帐户里写xxxx@qq.com
25 在存储过程里面写
26 EXEC [msdb].[dbo].[sp_send_dbmail]
@profile_name = 'joe_profile' , -- sysname
--设置了配置文件也没有用monitorGPOSDB，内容还是跟没有设置配置文件一样
@recipients = '', -- varchar(max)
32 那么登录163之后会看到邮件标题是
33 即sqlserver 发邮件到
然后通过 发到xxx@163.com
34 因为帐户里的邮箱地址是xxxx@qq.com
存储过程里的邮箱地址是xxxx@163.com
36 net send 使用信使服务 在XP 或者win2003才有messager服务跟这个数据库邮件一点关系
37 都没有，在局域网内弹出一个对话框，里面有消息内容跟标题
40 --在以前的版本中，SQL Server通过Sql Mail来实现对电子邮件的收发等功能。
41 --在Sql Server2005中新增了数据库邮件功能，并且在今后的版本中将不再支持Sql Mail（我看sql2008还有这个选项在外围应用配置器里，是不是表示还可以使用sql mail）。
42 --原因在于SQL Mail使用外部电子邮件应用程序（例如Microsoft Outlook）中的扩展MAPI 客户端组件来发送和接收电子邮件。
43 --因此，若要使用SQL Mail，必须在运行SQL Server 的计算机上安装支持扩展MAPI 的电子邮件应用程序。
44 --而数据库邮件则是一种通过Microsoft SQL Server 2005 Database Engine 发送电子邮件的企业解决方案。
45 --通过使用数据库邮件，数据库应用程序可以向用户发送电子邮件。
49 --数据库邮件（Database Mail）设计为与SMTP 服务器配合使用，具有可靠性，灵活性，安全性，兼容性。考虑到Sql Server2005是基于.Net Framework 2.0，而在后者中新增了System.Net.Mail命名空间，相信数据库邮件也是利用了其中的一些功能。
50 --接下来就如何启用和配置数据库邮件进行介绍。
52 --出于安全性的考虑，默认地是不开启数据库邮件功能。
53 --启用数据库邮件功能有两种方式：
54 --方式一：使用SQL Server 外围应用配置器工具
55 --方式二：数据库邮件配置向导显式启用数据库邮件。
57 --方式一：
58 --sqlserver外围应用配置器-》功能的外围应用配置器-》数据库右键-》启用数据库邮件存储过程
60 --方式二：
61 --在SSMS里的对象资源管理器中的实例下，打开【管理】，然后在【数据库邮件】上右键后点击【配置数据库邮件】
68 点击【下一步】后出现【新建配置文件】：
74 然后点击【添加】添加帐户（账户名可以随便命名）：
75 这里的帐户不是sql帐户，是发送邮件的帐户，每个帐户对应一个邮箱地址，即是选择一个帐户就代表选择了一个邮箱，删除一个帐户就代表删除一个邮箱
77 SSL连接只能用于163邮箱，QQ邮箱不能使用SSL连接
79 在163邮箱里都显示&我&
85 EXEC msdb..sysmail_add_account_sp @account_name = 'joe'
-- 邮件帐户名称（SQL Server 使用）
, @email_address = ''
-- 发件人邮件地址
, @display_name = N'桦仔'
-- 发件人姓名
@description = 'QQ邮箱'
90 点击【下一步】后管理配置文件安全性，将其设置为默认配置文件，一般创建一个配置文件就够了
91 可以创建多个配置文件，多个帐户，设置配置文件为专用还是公共
98 然后点击【下一步】完成配置，日志记录级别改为&详细&：
101 所有的配置选项都在这个界面
120 测试的时候一定要写邮箱地址不能写账户名 例如：joe
124 使用警报来发送电子邮件，一般发生下图下面的错误的时候才会发送电子邮件
125 除了发送电子邮件之外也可以执行作业（作业本身已经预留了一些操作例如：数
126 据库维护，引擎优化顾问等作业，用户选择就可以了），也可以作业里自己写
127 TSQL， 不过前提都是在出现事件警报定义的错误的时候才触发
157 Sql脚本：
159 --注意：附近路径必须要先建好附件不然会报附件文件C:\Users\Administrator\Desktop\Results.txt 无效
160 --注意：系统必须至少有一个配置文件否则会报错：未配置全局配置文件。请在@profile_name 参数中指定配置文件名
161 因为配置文件里已经指定了某些选项，所有觉得[sp_send_dbmail]存储过程的部分参数非常多余！！！
163 EXEC [msdb].[dbo].[sp_send_dbmail]
@profile_name = 'joe_profile' ,
@recipients = '', -- varchar(max)
@copy_recipients = '', -- varchar(max)
@subject = N'GPOSDB数据库systempara表数据', -- nvarchar(255)
@body = N'你好，GPOSDB数据库systempara表数据如下', -- nvarchar(max)
@body_format = 'html', -- varchar(20)
正文格式可选值：text
@importance = 'HIGH', -- varchar(6)
重要等级邮件重要性必须为LOW、NORMAL 或HIGH
@sensitivity = 'NORMAL', -- varchar(12)
敏感度邮件敏感度必须为NORMAL、PERSONAL、PRIVATE 或CONFIDENTIAL。
@file_attachments = N'', -- nvarchar(max)
可以写上C:\Users\Administrator\Desktop\Results.txt
@query = N'SELECT * from systempara', -- nvarchar(max)
@execute_query_database = gposdb, -- sysname
@attach_query_result_as_file = 0, -- bit
@query_attachment_filename = N'C:\Users\Administrator\Desktop\Results.txt', -- nvarchar(260)
@query_result_separator = ',', -- char(1)
@append_query_error = 1, -- bit
@query_result_no_padding = 0 --bit
182 使用DBCC CHECKDB([GPOSDB])检查GPOSDB数据库
结果不保存到附件的txt文件
184 EXEC [msdb].[dbo].[sp_send_dbmail]
@profile_name = 'qw', -- sysname
--设置了配置文件也没有用monitorGPOSDB，内容还是跟没有设置配置文件一样
@recipients = '', -- varchar(max)
@subject = N'GPOSDB数据库运行状况', -- nvarchar(255)
@body = N'GPOSDB数据库运行状况', -- nvarchar(max)
@importance = 'HIGH', -- varchar(6)
重要等级邮件重要性必须为LOW、NORMAL 或HIGH
@sensitivity = 'NORMAL', -- varchar(12)
敏感度邮件敏感度必须为NORMAL、PERSONAL、PRIVATE 或CONFIDENTIAL。
@file_attachments = N'', -- nvarchar(max)
可以写上C:\Users\Administrator\Desktop\Results.txt
@query = N'DBCC CHECKDB([GPOSDB])', -- nvarchar(max)
@execute_query_database = gposdb, -- sysname
@attach_query_result_as_file = 0, -- bit
@query_attachment_filename = N'', -- nvarchar(260)
@query_result_separator = ',', -- char(1)
@append_query_error = 1, -- bit
@query_result_no_padding = 0 --bit
207 -------------------------------------------------------------------------------------------------------------------------------
209 网易博客
210 数据库邮件是从 SQL Server 数据库引擎中发送电子邮件的企业解决方案。通过使用数据库邮件，数据库应用程序可以向用户发送电子邮件。邮件中可以包含查询结果，还可以包含来自网络中任何资源的文件。 无需 Microsoft Outlook 或扩展消息处理应用程序编程接口（扩展 MAPI）。数据库邮件使用标准的简单邮件传输协议 (SMTP) 发送邮件。无须在运行 SQL Server 的计算机上安装扩展 MAPI 客户端便可以使用数据库邮件。
215 对于真正的 DBA 来说，数据库邮件是必不可少的。 例如，数据库发生了警报（alert）， DBA 希望得到邮件通知，以便即时排除故障。 或者是监控数据库作业（SQL Server Job）的运行状况，当检查到失败的作业时， 就发送数据库邮件报告给 DBA。
216 在 SQL Server 2000 中 配置 &SQL Mail&，需要安装 Outlook，配置过程比较麻烦。 在 SQL Server 2005 中配置 &Database Mail& 就相对容易多了。 主要是理清思路。
217 SQL Server 并没有内置邮件服务器（Mail Server）， 它跟我们发送邮件一样，需要用户名和密码通过 SMTP（Simple Message Transfer Protocol） 去连接邮件服务器。我们想让 SQL Server 来发送邮件，首先要告诉它用户名称，密码， 服务器地址，网络传送协议，邮件服务器的端口。。。等信息。这是通过 SQL Server 系统 存储过程 sysmail_add_account_sp 来实现的。
218 exec sysmail_add_account_sp
220 这样，在 SQL Server 2005 中就添加了一个发送邮件的帐户。 道理上讲，有了这个邮件帐户，SQL Server 就可以发送邮件了。 如：
222 sp_send_dbmail @account_name = 'mail_account'
224 但是，SQL Server 考虑的更周全。试想：如果这个邮件帐户发生故障 （比如：用户密码过期，或者邮件服务器宕机）那岂不是发送不了邮件了？ 为了应对这种情况，SQL Server 2005 引入了 mail profile 这个东东。 一个 profile 中可以包含多个 account （邮件帐户），这样，SQL Server 发邮件的时候会依次尝试 profile 中的多个邮件帐户，如果发送成功，则退出， 否则，利用下一个邮件帐户发送邮件。其中，添加 profile 和 在 account 和 profile 建立映射是通过下面两个系统存储过程实现的：
226 sysmail_add_profile_sp
227 sysmail_add_profileaccount_sp
229 这时候，SQL Server 发送邮件，就采用下面的方法了：
231 sp_send_dbmail @profile_name = 'profile_name'
233 下面是具体的配置邮件步骤
235 在 sa 系统帐户下运行。
启用 SQL Server 2005 邮件功能。
238 use master
241 exec sp_configure 'show advanced options',1
244 reconfigure
247 exec sp_configure 'Database mail XPs',1
250 reconfigure
252 --启动数据库邮件服务
253 EXEC [msdb].[dbo].[sysmail_start_sp]
257 2在 SQL Server 2005 中添加邮件帐户（account）
259 EXEC msdb..sysmail_add_account_sp @account_name = 'joe'
-- 邮件帐户名称（SQL Server 使用）
, @email_address = ''
-- 发件人邮件地址
, @display_name = N'桦仔'
-- 发件人姓名
@description = 'QQ邮箱'
,@mailserver_name = ''
-- 邮件服务器地址
, @mailserver_type = 'SMTP'
-- 邮件协议（SQL 2005 只支持SMTP），而且区分大小写，只能填这个
, @port = 25
-- 邮件服务器端口
, @username = 'xxxxx'
, @password = 'xxxxxx'
, @use_default_credentials = 0, @enable_ssl = 0 --QQ邮箱不能使用SSL
, @account_id = NULL
274 3在 SQL Server 2005 中添加 profile
276 EXEC msdb..sysmail_add_profile_sp @profile_name = '监控GPOSDB '
-- profile 名称
, @description = '监控GPOSDB ' -- profile 描述
, @profile_id = NULL
282 4. 在 SQL Server 2005 中映射 account 和 profile
284 EXEC msdb..sysmail_add_profileaccount_sp @profile_name = '监控GPOSDB ' -- profile 名称
, @account_name = 'joe'
-- account 名称
, @sequence_number = 1
-- account 在profile 中顺序
290 5. 利用 SQL Server 2005 Database Mail 功能发送邮件。
292 EXEC [msdb].[dbo].[sp_send_dbmail]
@profile_name = 'joe_profile' ,
@recipients = '', -- varchar(max)
@copy_recipients = '', -- varchar(max)
@subject = N'GPOSDB数据库systempara表数据', -- nvarchar(255)
@body = N'你好，GPOSDB数据库systempara表数据如下', -- nvarchar(max)
@body_format = 'html', -- varchar(20)
正文格式可选值：text
@importance = 'HIGH', -- varchar(6)
重要等级邮件重要性必须为LOW、NORMAL 或HIGH
@sensitivity = 'NORMAL', -- varchar(12)
敏感度邮件敏感度必须为NORMAL、PERSONAL、PRIVATE 或CONFIDENTIAL。
@file_attachments = N'', -- nvarchar(max)
可以写上C:\Users\Administrator\Desktop\Results.txt
@query = N'SELECT * from systempara', -- nvarchar(max)
@execute_query_database = gposdb, -- sysname
@attach_query_result_as_file = 0, -- bit
@query_attachment_filename = N'C:\Users\Administrator\Desktop\Results.txt', -- nvarchar(260)
@query_result_separator = ',', -- char(1)
@append_query_error = 1, -- bit
308 @query_result_no_padding = 0 &bit
310 ---------------监控GPOSDB-------------------------------------------
311 EXEC [msdb].[dbo].[sp_send_dbmail]
@profile_name = '监控GPOSDB '
@recipients = 'xxxxx@', -- varchar(max)
@subject = N'GPOSDB数据库运行状况', -- nvarchar(255)
@body_format = 'text', -- varchar(20)
正文格式可选值：text
@importance = 'HIGH', -- varchar(6)
重要等级邮件重要性必须为LOW、NORMAL 或HIGH
@sensitivity = 'NORMAL', -- varchar(12)
敏感度邮件敏感度必须为NORMAL、PERSONAL、PRIVATE 或CONFIDENTIAL。
@query = N'DBCC CHECKDB([GPOSDB])', -- nvarchar(max)
@execute_query_database = gposdb, -- sysname
@query_result_separator = ',', -- char(1)
@append_query_error = 1, -- bit
@query_result_no_padding = 0 --bit
326 创建好之后放入维护计划！！！！！！！
331 6. 查看邮件发送情况：
333 use msdb
335 select * from sysmail_allitems
336 select * from sysmail_mailitems
337 select * from sysmail_event_log
341 如果不是以 sa 帐户发送邮件，则可能会出现错误：
342 Msg 229, Level 14, State 5, Procedure sp_send_dbmail, Line 1
343 EXECUTE permission denied on object 'sp_send_dbmail', database 'msdb', schema 'dbo'.
345 这是因为，当前 SQL Server 登陆帐户（login），在 msdb 数据库中没有发送数据库邮件的权限， 需要加入 msdb 数据库用户，并通过加入 sp_addrolemember 角色赋予权限。假设该SQL Server 登陆帐户 名字为 &dba&
347 use msdb
349 create user dba for login dba
351 exec dbo.sp_addrolemember @rolename
= 'DatabaseMailUserRole',
@membername = 'dba'
359 此时，再次发送数据库邮件，仍可能有错误：
360 Msg 14607, Level 16, State 1, Procedure sp_send_dbmail, Line 119
361 profile name is not valid
362 虽然，数据库用户 &dba& 已经在 msdb 中拥有发送邮件的权限了， 但这还不够，他还需要有使用 profile：&dba_profile& 的权限
365 USE msdb
368 EXEC sysmail_add_principalprofile_sp @principal_name = 'dba',
@profile_name = 'joe_profile', @is_default = 1
372 从上面的参数 @is_default=1 可以看出，一个数据库用户可以在多个 mail profile 拥有发送权限。
376 只有 sysadmin 固定服务器角色的成员才能使用&发送测试电子邮件&对话框。如果用户不是 sysadmin 固定服务器角色的成员，可以使用 sp_send_dbmail 过程来测试数据库邮件。
377 JOE\Administrator是sysadmin角色
阅读(...) 评论() &