0

I have a procedure which is for exporting data from table to .DAT file. There's a job running on it which calls SP every 10 Min. .DAT file contains H= column names, B= table data, T= No of rows processed(.SIG file appends this to .DAT file). Following is the stored procedure :

CREATE PROCEDURE [dbo].[Test_Export] @count2 VARCHAR(10)
AS 
BEGIN

    SET NOCOUNT ON;

    --Alerts_Balance
    DECLARE @bcp_sql VARCHAR(1000)
    DECLARE @filename VARCHAR(200), @count1 VARCHAR(10)=20000


    SET @filename = 'C:\Test'+'.DAT';

    SELECT @bcp_sql = 'bcp "SELECT ''H'',''Name'',''City'',''State'',''Country'' union all SELECT top  10 ''B'', [Name],[City],[State],[Country]  FROM Test_Table" queryout ' + @filename + ' -c -t, -T -S '+ @@servername

    EXEC master.sys.xp_cmdshell @bcp_sql

    SELECT @bcp_sql = 'bcp "SELECT ''T'', '+cast(IIF(cast(@count1 as int)>cast(@count2 as int), @count2+2, @count1+2) as varchar(10))+'" queryout '+ @filename + '.sig -c -t, -T -S '+ @@servername

    EXEC master.sys.xp_cmdshell @bcp_sql

    SELECT @bcp_sql = 'type ' + @filename + '.sig >> "' + @filename + '"'
    EXEC master.sys.xp_cmdshell @bcp_sql

    SELECT @bcp_sql = 'copy nul: ' + @filename + '.sig'
    EXEC master.sys.xp_cmdshell @bcp_sql


    EXEC (@bcp_sql);

    END

It was working fine before with proper data format in .dat. From couple of days, sometimes .dat file contain no "B" (body) data. Sometimes SIG file appends T 2 times to .DAT file on PROD environment. Sometimes it gives correct output also. This issue is coming only on PROD environment Any idea why it is behaving like this?

Priyanka
  • 23
  • 1
  • 6
  • 3
    And... can you post your script and query you are using? If you run the query alone in SSMS, does it have duplicate data? "Sometimes" isn't a good term to be used. Can you post your data as well? – SS_DBA Nov 10 '17 at 13:45
  • @WEI_DBA please find the detailed explanation ..please let me know if you have any idea – Priyanka Nov 11 '17 at 06:53

0 Answers0