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
| BEGIN
SET NOCOUNT ON;
DECLARE @L_CURRENTDATE DATETIME
DECLARE @L_OUT_PATH VARCHAR(100)
DECLARE @L_OUT_FILENAME VARCHAR(100)
DECLARE @QUERY NVARCHAR(4000)
SET @L_CURRENTDATE = GETDATE()
SET @L_OUT_PATH = 'C:\Users\XXXX\'
SET @L_OUT_FILENAME =@Service+'_Test_'+@QuarterYear+'.csv'
SET @QUERY = 'select row_number() OVER (order by Direct_Participant_BIC),
Direct_Participant_BIC+'STEP2' AS '2' ,
Total_Payments_Sent AS '3',
Daily_Average_Payments_Sent AS '4',
Total_Payments_Received AS '5',
Daily_Average_Payments_Received AS '6',
Sending_First_XXXX AS '7',
Sending_Remaining AS '8',
Min_Charging_For_Sending AS '9',
Charging_Remaining AS '10',
Charging_Receiving AS '11',
Total_Tier_Charges_To_Be_Billed AS '12',
Average_Price_For_Transaction_Sent AS '13',
Average_Price_For_Transaction_Received AS '14'
from [BDD]..['+@Table_Name+']'
SET @QUERY = 'bcp "'+@QUERY+'" queryout ' + @L_OUT_PATH + @L_OUT_FILENAME + ' -c -t";" -T '
PRINT @QUERY
SET @QUERY = 'execute master.dbo.xp_cmdshell '+''''+@QUERY+''''
EXECUTE master.dbo.sp_executesql @QUERY,N'@L_CURRENTDATE DATETIME',@L_CURRENTDATE
RAISERROR( '', 0, 1) WITH NOWAIT
END |
Partager