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

by

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

More Related Contents:

  1. Is it possible to write single query on two table which are not connected to each other?
  2. T-SQL split string
  3. Insert results of a stored procedure into a temporary table
  4. Unpivot with column name
  5. How can I get column names from a table in SQL Server?
  6. Conditional WHERE clause in SQL Server
  7. How can I select the first day of a month in SQL?
  8. Multi-statement Table Valued Function vs Inline Table Valued Function
  9. SQL Server Insert if not exists
  10. Convert varchar into datetime in SQL Server
  11. Select statement to find duplicates on certain fields
  12. What is a stored procedure?
  13. Check if a row exists, otherwise insert
  14. T-SQL datetime rounded to nearest minute and nearest hours with using functions
  15. Split function in SQL Server 2008
  16. Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)
  17. Cannot use a CONTAINS or FREETEXT predicate on table or indexed view because it is not full-text indexed
  18. TransactSQL to run another TransactSQL script
  19. Store multiple bit values in a single table column
  20. Which SQL query is faster? Filter on Join criteria or Where clause?
  21. SQL Server equivalent to MySQL enum data type?
  22. Inserting rows into a table with one IDENTITY column only [duplicate]
  23. How can I group by date time column without taking time into consideration
  24. Using IF ELSE statement based on Count to execute different Insert statements
  25. When using GETDATE() in many places, is it better to use a variable?
  26. Eliminate and reduce overlapping date ranges
  27. Cumulative Total in MS Sql server [duplicate]
  28. SQL Server SELECT INTO @variable?
  29. COALESCE Function in TSQL
  30. SQL Server: +(unary) operator on non-numeric Strings

Leave a Comment