USE [database] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- P_AutoInspect_LogToFilePath 'F:\1111\', 'database','sa','psw' -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= ALTER PROCEDURE [dbo].[P_AutoInspect_LogToFilePath] @DirPath varchar(255), --导出的文件路径 @databaseName varchar(255), --数据库名字 @User varchar(100), --用户名 @Psw varchar(100) --密码 AS BEGIN exec sp_configure 'show advanced options',1 --开启配置 reconfigure exec sp_configure 'xp_cmdshell',1 --开启配置 reconfigure declare @FileName varchar(128); --导出的文件名 declare @TableName varchar(128); --数据库表名 declare @exec_sql varchar(1024); --sql语句 set @TableName = 'D_Log' + SUBSTRING(convert(varchar(20), getdate(), 112), 1,6) set @FileName ='Log' + CONVERT(varchar(100), GETDATE(), 112)+replace(CONVERT(varchar(100), GETDATE(), 108),':','') + '.csv' set @exec_sql = 'bcp "select top 1 * from + @databaseName + '.dbo.' + @TableName ' + '" queryout "'+ @DirPath + @FileName + '" -c -t, -U "'+@User+'" -P "'+@Psw + '"' --print @exec_sql; EXEC master..xp_cmdshell @exec_sql; END GO