1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51
| USE [DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[ExportCsvFile]
(
@ID_KEY INT = -1
)
AS
BEGIN
declare @prevAdvancedOptions int
declare @prevXpCmdshell int
select @prevAdvancedOptions = cast(value_in_use as int) from sys.configurations where name = 'show advanced options'
select @prevXpCmdshell = cast(value_in_use as int) from sys.configurations where name = 'xp_cmdshell'
if (@prevAdvancedOptions = 0)
begin
exec sp_configure 'show advanced options', 1
reconfigure
end
if (@prevXpCmdshell = 0)
begin
exec sp_configure 'xp_cmdshell', 1
reconfigure
end
exec master..xp_cmdshell 'sqlcmd -S MYSERVER\SQLSERVER -U sa -P Pswd -Q "set nocount on; select * from [DB].[dbo].[TABLE_A]()" -o "C:\Table_a.csv" -W -w 1024 -s,'
exec master..xp_cmdshell 'sqlcmd -S MYSERVER\SQLSERVER -U sa -P Pswd -Q "set nocount on; select * from [DB].[dbo].[TABLE_B]()" -o "C:\Table_b.csv" -W -w 1024 -s,'
if (@prevXpCmdshell = 0)
begin
exec sp_configure 'xp_cmdshell', 0
reconfigure
end
if (@prevAdvancedOptions = 0)
begin
exec sp_configure 'show advanced options', 0
reconfigure
end
END |
Partager