22,209
社区成员
发帖
与我相关
我的任务
分享
if exists(select 1 from sysobjects where name = 'spSendMail' and type = 'P')
drop procedure spSendMail
go
create procedure spSendMail
@from varchar(100), --send by
@to varchar(100), --send to
@bcc varchar(500), --bcc(blind carbon copy)/cc(carbon copy)
@subject varchar(400) = ' ', --mail subject
@htmlBody varchar(8000) = ' ', --mail body content
@addAttachment varchar(100) --attachment,such as 'd:/fileName.xls',if there is no attachment,just input ''
as
declare @object int
declare @hr int
declare @source varchar(255)
declare @description varchar(500)
declare @output varchar(1000)
declare @smtpServer varchar(50)
declare @smtpUsername varchar(50)
declare @smtpPassword varchar(50)
set @smtpServer = 'mail.qq.com'
set @smtpUsername = ''
set @smtpPassword = ''
--@see http://msdn.microsoft.com/en-us/library/ms526227%28v=exchg.10%29.aspxd
--http://schemas.microsoft.com/cdo/configuration
exec @hr = sp_OACreate 'CDO.Message',@object out
set @htmlBody = '<body><h3><font col=Red>' + @htmlBody + '</font></h3></body>'
--change line
--set @htmlBody = replace(@htmlBody,char(10),'<br/>')
--exec @hr = sp_OASetProperty @object,'HTMLBodyPart.Charset','GBK'
exec @hr = sp_OASetProperty @object,'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
exec @hr = sp_OASetProperty @object,'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value',@smtpServer
exec @hr = sp_OASetProperty @object,'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport").Value','25'
exec @hr = sp_OASetProperty @object,'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").Value','1'
exec @hr = sp_OASetProperty @object,'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername").Value',@smtpUsername
exec @hr = sp_OASetProperty @object,'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").Value',@smtpPassword
exec @hr = sp_OAMethod @object,'Configuration.Fields.Update',null
exec @hr = sp_OASetProperty @object,'To',@to
exec @hr = sp_OASetProperty @object,'Bcc',@bcc
exec @hr = sp_OASetProperty @object,'From',@from
exec @hr = sp_OASetProperty @object,'Subject',@subject
exec @hr = sp_OASetProperty @object,'HtmlBody',@htmlBody
--exec @hr = sp_OASetProperty @object,'TextBody',@htmlBody(String content)
if @addAttachment <> ''
exec @hr = sp_OAMethod @object,'AddAttachment',null,@addAttachment
if @hr <> 0
select @hr
begin
exec @hr = sp_OAGetErrorInfo null,@source out,@description out
if @hr = 0
begin
select @output = ' Source: '+@source
print @output
select @output = ' Description: '+@description
print @output
end
else
begin
print ' sp_OAGetErrorInfo failure!'
return
end
end
exec @hr = sp_OAMethod @object,'Send',null
--check error
if @hr <> 0
select @hr
begin
exec @hr = sp_OAGetErrorInfo null,@source out,@description out
if @hr = 0
begin
select @output = ' Source: '+@source
print @output
select @output = ' Description: '+@description
print @output
end
else
begin
print ' sp_OAGetErrorInfo failure!'
return
end
end
print 'Send Mail Success!'
exec @hr = sp_OADestroy @object
use master
go
exec sp_addlogin 'test', 'test', 'dbName'
go
use dq
go
exec sp_adduser 'test', 'test', 'db_owner'
go
use master
go
exec sp_adduser 'test','test','db_ddladmin'
go
grant exec on xp_cmdshell to test
go
exec sp_addsrvrolemember 'test', 'sysadmin'
go
use master
go
exec sp_addlogin 'test', 'test', 'dbName'
go
use dbName
go
exec sp_adduser 'test', 'test', 'db_owner'
go
use master
go
exec sp_adduser 'test','test','db_ddladmin'
go
grant exec on xp_cmdshell to test
go
exec sp_addsrvrolemember 'test', 'sysadmin'
go
--setuser 'test'
--select suser_sname()
use dbName
go
exec master..xp_cmdshell 'bcp "select getdate()=''系统时间'',username=''姓名'' from dbName.dbo.tbName union select getdate,username from dbName.dbo.tbName " queryout c:\测试.xls -c -q -S"serverName" -U"userName" -P"passWord"';
use dbName
go
sp_dropuser 'test'
go
use master
go
sp_dropuser 'test'
go
sp_droplogin 'test'
go
declare
@userName sysname,
@passWord sysname,
@sql varchar(1000);
select @userName = N'xpcmdshell',
@passWord = N'@passWord',
@sql = 'net user '+@userName+' /delete';
select @sql
exec xp_cmdshell @sql
难道是test的sysadmin权限在master下,不在用户数据库下?
exec sp_configure 'show advanced options', 1;
reconfigure;
exec sp_configure 'xp_cmdshell', 1;
reconfigure;
EXEC master..xp_cmdshell 'bcp "select contact_info=''联系信息'' from schema.dbo.tb union all select contact_info from schema.dbo.tb " queryout D:/Install/Task/Rpt/Rpt.xls -c -q -S"" -U"" -P""';
作业“aa”: 第 1 步,“aa”: 开始执行 2013-05-09 17:29:17
已将配置选项 'show advanced options' 从 1 改为 1。请运行 RECONFIGURE 语句以安装。 [SQLSTATE 01000]
消息 15123,严重度 16: 配置选项 'xp_cmdshell' 不存在,也可能是高级选项。 [SQLSTATE 42000]
消息 0,严重度 16: [SQLSTATE 01000]
消息 15456,严重度 16: 有效的配置选项为: [SQLSTATE 01000]
name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
recovery interval (min) 0 32767 0 0
allow updates 0 1 0 0
user connections 0 32767 0 0
locks 5000 2147483647 0 0
open objects 0 2147483647 0 0
fill factor (%) 0 100 0 0
media retention 0 365 0 0
nested triggers 0 1 1 1
remote access 0 1 1 1
two digit year cutoff 1753 9999 2049 2049
default full-text language 0 2147483647 2052 2052
default language 0 9999 30 30
max worker threads 32 32767 255 255
remote proc trans 0 1 0 0
network packet size (B) 512 32767 4096 4096
index create memory (KB) 704 2147483647 0 0
priority boost 0 1 0 0
show advanced options 0 1 1 1
remote login timeout (s) 0 2147483647 20 20
remote query timeout (s) 0 2147483647 600 600
cursor threshold -1 2147483647 -1 -1
min memory per query (KB) 512 2147483647 1024 1024
query wait (s) -1 2147483647 -1 -1
set working set size 0 1 0 0
user options 0 32767 0 0
affinity mask -2147483648 2147483647 0 0
max text repl size (B) 0 2147483647 65536 65536
cost threshold for parallelism 0 32767 5 5
max degree of parallelism 0 32 0 0
min server memory (MB) 0 2147483647 0 0
max server memory (MB) 4 2147483647 2147483647 2147483647
query governor cost limit 0 2147483647 0 0
lightweight pooling 0 1 0 0
scan for startup procs 0 1 0 0
c2 audit mode 0 1 0 0
awe enabled 0 1 0 0
Cross DB Ownership Chaining 0 1 1 1
output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]无法打开 BCP 主数据文件
(null)
哎