只举几个例子,防止工作中用到找不着
1、存储过程
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: <j>
-- Create date: <2013-3-15>
-- Description: <每天从CrbtInfo表中导出前一天更新的彩铃信息>
-- =============================================
ALTER PROCEDURE [dbo].[Job_ExportCrbtInfo]
@servername varchar(64),
@username varchar(32),
@password varchar(32),
@filepath varchar(128)
AS
BEGIN
DECLARE @FileName varchar(128)
--文件名:CrbtInfo_2013-03-15.txt (生成的是前一天的日期)
SET @FileName = @filepath + 'CrbtInfo_' + Convert(varchar(10), Dateadd(day, -1, GETDATE()), 23) + '.txt'
DECLARE @QuerySQL varchar(1024), @ExportSQL varchar(2048)
--前一天更新的数据
SET @QuerySQL = 'SELECT CASE EventType WHEN ''1'' THEN ''1'' WHEN ''2'' THEN ''3'' WHEN ''5'' THEN ''3'' WHEN ''3'' THEN ''2'' WHEN ''4'' THEN ''2'' WHEN ''6'' THEN ''2'' END AS EventType, ToneID, ToneName, CmsSingerName, SpName, Price, ToneValidDay, PlayFilePath, AuditFilePath, Type, ''0'' AS Status FROM BaseResource.dbo.CrbtInfo WHERE Convert(varchar(10), UploadedTime, 23) = Convert(varchar(10), Dateadd(day, -1, GETDATE()), 23)'
PRINT @QuerySQL
SET @ExportSQL = 'BCP "' + @QuerySQL + '" queryout ' + @FileName + ' -c -q -t"|" -S"' + @servername + '" -U"' + @username + '" -P"' + @password + '" '
PRINT @ExportSQL
EXEC master..xp_cmdshell @ExportSQL
END
2、执行存储过程
USE [DBName]
GO
DECLARE@return_value int
EXEC@return_value = [dbo].[Job_ExportCrbtInfo]
@servername = N'',
@username = N'',
@password = N'',
@filepath = N''
SELECT'Return Value' = @return_value
GO
2、有的需要更改配置后才能执行
-- 允许配置高级选项
EXEC sp_configure 'show advanced options', 1
GO
-- 重新配置
RECONFIGURE
GO
-- 启用xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 1
GO
--重新配置
RECONFIGURE
GO
DECLARE @SqlALL VARCHAR(8000)
DECLARE @FILEPATH VARCHAR(80)
set @FILEPATH='e:/'
SET @SqlALL = 'bcp "SELECT ISNULL(UserNoAndPersonalRadioNo.NickName,'''') ,[RadioFileName],UserRadioInfo.CreatedTime,UserRadioInfo.ReplyNo FROM MusicSearching40.dbo.[UserRadioInfo] LEFT JOIN MusicSearching40.dbo.UserNoAndPersonalRadioNo ON UserRadioInfo.UserNo = UserNoAndPersonalRadioNo.UserNo WHERE (Status = ''1'' OR Status = ''4'') and UserNoAndPersonalRadioNo.RadioType=''1'' and len(UserRadioInfo.ReplyNo)>0 and CreatedTime>(getdate()-1) ORDER BY UserNoAndPersonalRadioNo.NickName DESC,CreatedTime DESC" queryout '+@FILEPATH+'留言文件_'+CONVERT(varchar(10),GETDATE(),112)+'.txt -c -t"\t" -T -U''sa'' -P''ms40@dev'' '
EXEC master..xp_cmdshell @SqlALL
3、作业 可以在sqlserver中建个作业,定时进行数据导出等工作
注意:在执行sql时有时会遇到下面的问题:
output
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Native Client]无法打开 BCP 主数据文件
NULL
这个问题是因为导出文件的路径不存在或着不正确导致的