Stored procedure that exports data into csv files only exports to one file

I have a fun script:

DECLARE @StartDT DATE
DECLARE @MinDOS DATE
DECLARE @TableName VARCHAR(50)
SET @TableName = 'ViewAccountDetail'
SELECT @MinDOS = MIN(dos) FROM accn_demographics
SELECT @StartDT = 
    CAST(CAST(datepart(YYYY,@MinDOS) AS varchar) + '-' + CAST(datepart(mm,@MinDOS) AS varchar) + '-' + CAST('01' AS varchar) AS DATETIME)
DECLARE @FileLocation VARCHAR(50)

WHILE @StartDT < '20110901'
BEGIN
    SET @FileLocation='C:\test\'+@TableName+cast(@StartDT as varchar)+'.csv'
    EXEC BCP_Text_File @TableName, @FileLocation    
    SET @StartDT = DATEADD(MONTH,1,@StartDT)
END

It is supposed to do is export data into csv files. The names of the files should be:

C:\test\ViewAccountDetail2011-01-01.csv
C:\test\ViewAccountDetail2011-02-01.csv
C:\test\ViewAccountDetail2011-03-01.csv
C:\test\ViewAccountDetail2011-04-01.csv
C:\test\ViewAccountDetail2011-05-01.csv
C:\test\ViewAccountDetail2011-06-01.csv
C:\test\ViewAccountDetail2011-07-01.csv
C:\test\ViewAccountDetail2011-08-01.csv

but it stores all the data into the same one:

C:\test\ViewAccountDetail2011-01-01.csv

i did a print @FileLocation and confirmed that it correctly updates this variable.

is there any apparent, OBVIOUS thing that i am missing here?

FYI this line:

EXEC BCP_Text_File @TableName, @FileLocation

calls this procedure:

  ALTER PROCEDURE [dbo].[BCP_Text_File]
    @table    NVARCHAR(255),  
    @filename VARCHAR(100)  
AS
BEGIN
  SET NOCOUNT ON;

  IF OBJECT_ID(@table) IS NOT NULL
  BEGIN
    DECLARE 
        @sql NVARCHAR(MAX), 
        @cols NVARCHAR(MAX) = N'';

    SELECT @cols += ',' + name
      FROM sys.columns
      WHERE [object_id] = OBJECT_ID(@table)
      ORDER BY column_id;

    SELECT @cols = STUFF(@cols, 1, 1, '');

    SET @sql = N'EXEC master..xp_cmdshell ''bcp "SELECT ''''' 
        + REPLACE(@cols, ',', ''''',''''') + ''''' UNION ALL SELECT ' 
        + 'RTRIM(' + REPLACE(@cols, ',', '),RTRIM(') + ') FROM ' 
        + DB_NAME() + '..' + @table + '" queryout "' + @filename + '" -c -T''';  

    EXEC sp_executesql @sql;
  END
  ELSE
  BEGIN
    SELECT 'The table '+@table+' does not exist in the database';
  END
END

GO

thank you so much for your help and guidance!!

Answers


Seems to work fine for me. I have a few suggestions:

(1) stop doing all that string concatenation to build a date. You can do the same thing much easier as in:

SELECT @StartDT = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @MinDOS), '19000101');

(2) stop declaring varchar without length. And to ensure the right output, I prefer convert:

SET @FileLocation = 'C:\test\' + @TableName
   + CONVERT(CHAR(10), @StartDT, 120) + '.csv';

(3) instead of "debugging" the code by running the stored procedure and inspecting the output in the folder, why not sanity-check your input first? Also, why use two variables for the date?

DECLARE 
   @StartDT DATE, 
   @TableName NVARCHAR(50), 
   @FileLocation VARCHAR(255);

SET @TableName = N'ViewAccountDetail';

SELECT @StartDT = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', MIN(dos)), '19000101')
   FROM dbo.accn_demographics;

   PRINT @StartDT;
-- ^^^^^ debugging 101 - what month do we think we're starting at?

WHILE @StartDT < '20110901'
BEGIN
    SET @FileLocation = 'C:\test\' + @TableName
      + CONVERT(CHAR(10), @StartDT, 120) + '.csv';

      PRINT @FileLocation;
    --^^^^^ again, debugging 101 - what does the filename currently look like?

    --EXEC BCP_Text_File @TableName, @FileLocation    
    SET @StartDT = DATEADD(MONTH, 1, @StartDT);
END

Need Your Help

Architecture for multiple web apps and databases

php database postgresql architecture

We used to have only one web app, but now we are breaking it down into multiple ones. Each one will be packaged as separate product (web app) Some have things in common some do not.

Soundcloud Stratus Player - Cross-platform compatibility

android ios soundcloud stratus

I'm using Soundcloud's stratus player on the following page - http://www.naturesoundmap.com/