USE [TfsVersionControl]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create Procedure [dbo].[usp_AutoSendMailForCheckOutInfo] As
Begin
 /*
 Function : Check TFS For Lock / Check Out
 Author: LuoHP
 Date:20100226
 Modify Date:20100904
 Note: First Create SQLMail
 */
 declare @BodyStr nvarchar(max)
 Declare @QuerySQL nvarchar(max)
 Select @BodyStr = Convert(nvarchar(16),getdate(),120)+' List at attachment'
 --表说明 Begin
 --TfsVersionControl.dbo.tbl_lock  文件锁定信息
 --TfsVersionControl.dbo.tbl_workspace 用户工作区
 --TfsVersionControl.dbo.tbl_Identity 用户信息
 --TfsVersionControl.dbo.tbl_pendingchange 文件迁入迁出信息
 --表说明 End
 Set @QuerySQL= 'select Convert(nvarchar(10),''LOCK'') As Type,
 Convert(nvarchar(20),I.DisplayName) As [User],
 Convert(nvarchar(30),W.Computer) As Computer,
 Convert(nvarchar(150),L.FullPath) As FileName
  From tbl_lock L
 Left Join tbl_workspace W on L.workspaceID = W.workspaceID
 Left Join tbl_Identity I on I.IdentityId = W.OwnerID
 Union All
 select Convert(nvarchar(10),''CheckOut'') As Type,
 Convert(nvarchar(20),I.DisplayName) As [User],
 Convert(nvarchar(30),W.Computer) As Computer,
 Convert(nvarchar(150),C.TargetParentPath+C.TargetChildItem) As FileName 
  From tbl_pendingchange C
 Left Join tbl_workSpace W on C.WorkspaceID = W.workspaceID
 Left Join tbl_Identity I on I.IdentityId = W.OwnerID
 Where W.Computer Is Not Null
 Order By Type,[User],Computer'
 
 --Print @QuerySQL
 --取得收件人列表
 declare @Mail nvarchar(max)
 set @Mail = 'ReceiveMail@hotmail.com' --收件人地址
 select @Mail=@Mail+mail+';' from WeeklyReport.dbo.Users
 select @Mail = substring(@Mail,1,Len(@mail)-1)
 --print @mail

 EXEC msdb.dbo.sp_send_dbmail
  @profile_name = 'DBMailprofile' --数据库邮件配置,
  @recipients = @mail,
  @subject = '【TFS每日提示】TFS文件迁出/锁定列表，请检查是否有忘记迁入的文件',
  @body = @BodyStr,
  @attach_query_result_as_file =1,
  @query = @QuerySQL,
  @append_query_error=1,
  @execute_query_database  ='TfsVersionControl'
  
End