Remove extra CR-LF from end of SELECT output to Text / File
-
I have a SQL query that extracts table records based on a date criteria. The result is output to file and is also prefixed with a header and trailer record. The header contains descriptive details about the data extract (eg. effective date, job run timestamp, etc), while the trailer record contains the number of records extracted, etc.
The header and trailer records are output to file using PRINT, while the table data is output using SELECT. The problem with SELECT is that is outputs an extra blank line at the end of its output. Is there a way to remove the extra CR-LF from the end of SELECT?
I have provided a simplified output below for clarity:
<header record>
<data record 1>
<data record 2>
<data record 3>
< ... >
<data record n>
<trailer record>
How can I remove the blank line between <data record n> and <trailer record>?
NOTE: Removing the blank line after the file is written will be quite difficult because the file size can exceed 2.5GB
Any thoughts / suggestions?
-
It seems like you add the extra CR-LF within your trailer record statement. Hard to tell without actually seeing the code...
Probably something like
-- some code+'< trailer record > 'instead of
-- some code+ '< trailer record >' -
Run below query in Query Analyser. I have specified in Options | Results to output to Text, and do not print column headers. Also, I'm using SQL Server 2000.
SQL Query:
========
SET NOCOUNT ON
Print 'Header Record'
SELECT 'Data Records'
Print 'Trailer Record'
Result Output:
===========
Header Record
Data Records
Trailer Record
How do I remove the extra blank line between 'Data Records' and 'Trailer Record'?
-
yogesh.pancholi (5/3/2010)
Run below query in Query Analyser. I have specified in Options | Results to output to Text, and do not print column headers. Also, I'm using SQL Server 2000.SQL Query:
========
SET NOCOUNT ON
Print 'Header Record'
SELECT 'Data Records'
Print 'Trailer Record'
Result Output:
===========
Header Record
Data Records
Trailer Record
How do I remove the extra blank line between 'Data Records' and 'Trailer Record'?
How about showing us your code? That will make it much easier to provide you with an answer that you can work with.
-
The following code would solve the example you provided, but as Lynn already suggested, posting your code would probably help us help you with the "real issue"...;-)
SELECT 'Header Record'UNION ALLSELECT 'Data Records'UNION ALLSELECT 'Trailer Record' -
lmu92 (5/3/2010)
The following code would solve the example you provided, but as Lynn already suggested, posting your code would probably help us help you with the "real issue"...;-)SELECT 'Header Record'UNION ALLSELECT 'Data Records'UNION ALLSELECT 'Trailer Record'Precisely where I was going with this, but all three queries must have the same number of columns and data types (or nulls).
-
Lynn Pettis (5/3/2010)
Precisely where I was going with this, but all three queries must have the same number of columns and data types (or nulls).
... and that's exactly the case regarding the sample data provided.
Anything more detailed would have required a more detailed question, as both of us already stated...
But you're absolutely right, of course.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply