I Need To Remove Blank Lines At End Of SQL Query

I am running the following OSQL command that outputs data to a file:
osql -dION_Data -E -ieaf8.sql -l -n -oeaf8.txt -s"<TAB>" -SMAPOW\ION -w500 -h-1 -m1000

This data file is read by another program (that I cannot change) and only expects rows of tab delimited data.
I am using the -m parameter to suppress the errors/warnings.
I am also using the "set nocount on;" command in the input file.

The problem is there is always 2 <CR><LF> at the end of the file.  Is there a way to get rid of this using OSQL, or within the SQL query?  If not is there an easy way to do this from DOS?

On a side note, is there a defined error # range to use for the -m parameter?  I just picked 1000 out of thin error...


sainiakAsked:
Who is Participating?
 
sainiakConnect With a Mentor Author Commented:
I was able to get rid of the "blank line" with the following DOS commands:
@Echo Off
For /F "tokens=* delims=" %%A in (%1) Do Echo %%A >> %2

where %1 is the source file and %2 the result file.
0
 
dbbishopCommented:
Not certain I completely follow, but your SQL Query could contain the code
WHERE mydata LIKE '%' + CHAR(9) + '%'

mydata is the column that contains the data and this will only select rows that contain a tab character (CHAR(9)). You could also use

WHERE RTRIM(mydata) <> ''

RTRIM should not be necessary if the datatype of mydata is VARCHAR or NVARCHAR.

0
 
sainiakAuthor Commented:
I don't think the blank lines (<CR><LF>) are being returned by the query, but by SQL Server itself.  I think the <CR><LF> are in place of the warning and the row count.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
dbbishopCommented:
-m parameter has to do with what severity of errors are displayed. Use -1 (-m-1) to not display any errors. See Error Message Severity Levels in SQL BOL. Certain severity levels are reserved by SQL and different levels have predefined manings to SQL. The higher the severity level the more severe the error, with levels of 19 and up stop the process and those above 20 will terminate the user connection.

From BOL:
Error messages with a severity level of 10 are informational. Error messages with severity levels from 11 through 16 are generated by the user and can be corrected by the user. Severity levels from 17 and 18 are generated by resource or system errors; the user's session is not interrupted.

0
 
sainiakAuthor Commented:
My problem is that -m-1 still displays warnings:
Msg 8153, Level 0, State 1, Server MAPOW\ION, Line 2
Warning: Null value is eliminated by an aggregate or other SET operation.
0
 
dbbishopCommented:
I cannot seem to get the two CR/LFs from the end of the output file either. Seems like SQL is going to put them there whether you want them or not.
0
 
dbbishopCommented:
What is the contents of ieaf8.sql
0
 
sainiakAuthor Commented:
It is a very convoluted query (see below), but I have the same problem (2 <CR><LF>) with this simple query:
set nocount on;
select getdate()

Here is the convoluted one...
set nocount on;
SELECT 'EAF8_READING_DATETIME',
'EAF8_TRANSFORMER_TAP_POSITION',
'EAF8_CURRENT_PHASE_A',
'EAF8_CURRENT_PHASE_B',
'EAF8_CURRENT_PHASE_C',
'EAF8_VOLTAGE_PHASE_A_B',
'EAF8_VOLTAGE_PHASE_B_C',
'EAF8_VOLTAGE_PHASE_C_A',
'EAF8_KILOWATTS_PHASE_A',
'EAF8_KILOWATTS_PHASE_B',
'EAF8_KILOWATTS_PHASE_C',
'EAF8_KVA_PHASE_A',
'EAF8_KVA_PHASE_B',
'EAF8_KVA_PHASE_C',
'EAF8_POWER_FACTOR_PHASE_A',
'EAF8_POWER_FACTOR_PHASE_B',
'EAF8_POWER_FACTOR_PHASE_C'
union all
SELECT convert(varchar(23),replace(convert( varchar(11), dateadd ( hour, -4, TimestampUTC), 113),' ','-')
+ ' ' + right(convert( varchar(20), dateadd ( hour, -4, TimestampUTC), 113),8) + '.00') AS "EAF8_READING_DATETIME",
convert(varchar(16),MAX(CASE WHEN SourceID=4 AND QuantityID=10017 THEN [Value] END))  AS "EAF8_TRANSFORMER_TAP_POSITION",
convert(varchar(16),MAX(CASE WHEN SourceID=4 AND QuantityID=515 THEN [Value] END))  AS "EAF8_CURRENT_PHASE_A",
convert(varchar(16),MAX(CASE WHEN SourceID=4 AND QuantityID=516 THEN [Value] END))  AS "EAF8_CURRENT_PHASE_B",
convert(varchar(16),MAX(CASE WHEN SourceID=4 AND QuantityID=517 THEN [Value] END))  AS "EAF8_CURRENT_PHASE_C",
convert(varchar(16),MAX(CASE WHEN SourceID=4 AND QuantityID=534 THEN [Value] END))  AS "EAF8_VOLTAGE_PHASE_A_B",
convert(varchar(16),MAX(CASE WHEN SourceID=4 AND QuantityID=535 THEN [Value] END))  AS "EAF8_VOLTAGE_PHASE_B_C",
convert(varchar(16),MAX(CASE WHEN SourceID=4 AND QuantityID=536 THEN [Value] END))  AS "EAF8_VOLTAGE_PHASE_C_A",
convert(varchar(16),MAX(CASE WHEN SourceID=4 AND QuantityID=518 THEN [Value] END))  AS "EAF8_KILOWATTS_PHASE_A",
convert(varchar(16),MAX(CASE WHEN SourceID=4 AND QuantityID=519 THEN [Value] END))  AS "EAF8_KILOWATTS_PHASE_B",
convert(varchar(16),MAX(CASE WHEN SourceID=4 AND QuantityID=520 THEN [Value] END))  AS "EAF8_KILOWATTS_PHASE_C",
convert(varchar(16),MAX(CASE WHEN SourceID=4 AND QuantityID=524 THEN [Value] END))  AS "EAF8_KVA_PHASE_A",
convert(varchar(16),MAX(CASE WHEN SourceID=4 AND QuantityID=525 THEN [Value] END))  AS "EAF8_KVA_PHASE_B",
convert(varchar(16),MAX(CASE WHEN SourceID=4 AND QuantityID=526 THEN [Value] END))  AS "EAF8_KVA_PHASE_C",
convert(varchar(16),MAX(CASE WHEN SourceID=4 AND QuantityID=664 THEN [Value] END))  AS "EAF8_KVA_PHASE_A",
convert(varchar(16),MAX(CASE WHEN SourceID=4 AND QuantityID=665 THEN [Value] END))  AS "EAF8_POWER_FACTOR_PHASE_B",
convert(varchar(16),MAX(CASE WHEN SourceID=4 AND QuantityID=666 THEN [Value] END))  AS "EAF8_POWER_FACTOR_PHASE_C"

FROM DataLog WITH (NOLOCK) INNER JOIN DataLogStamp WITH (NOLOCK) ON DataLog.DataLogStampID=DataLogStamp.ID

WHERE dateadd ( hour, -4, TimestampUTC) >= dateadd ( second, -datepart ( second, getdate()),
      dateadd ( minute, -datepart ( minute, getdate () ), dateadd ( hour, -2, getdate() ))) and
      dateadd ( hour, -4, TimestampUTC) < dateadd ( second, -datepart ( second, getdate()),
      dateadd ( minute, -datepart ( minute, getdate () ), dateadd ( hour, -1, getdate() ))) and
  ( (QuantityID=536 AND SourceID=4)
       OR

       (QuantityID=666 AND SourceID=4)
       OR

       (QuantityID=664 AND SourceID=4)
       OR

       (QuantityID=10017 AND SourceID=4)
       OR

       (QuantityID=520 AND SourceID=4)
       OR

       (QuantityID=518 AND SourceID=4)
       OR

       (QuantityID=515 AND SourceID=4)
       OR

       (QuantityID=665 AND SourceID=4)
       OR

       (QuantityID=516 AND SourceID=4)
       OR

       (QuantityID=534 AND SourceID=4)
       OR

       (QuantityID=524 AND SourceID=4)
       OR

       (QuantityID=517 AND SourceID=4)
       OR

       (QuantityID=525 AND SourceID=4)
       OR

       (QuantityID=519 AND SourceID=4)
       OR

       (QuantityID=526 AND SourceID=4)
       OR

       (QuantityID=535 AND SourceID=4)
)
GROUP BY TimestampUTC, FractionOfASecond
0
 
dbbishopCommented:
I do not think there is going to be a way to remove the two blank lines. I agree, SQL puts them there regardless of the settings you have. I was more interested in seeing if we could determine what was causing the error message. I've got to run to a meeting but either someone else on the list will take a look at it or I will when I get back.
0
 
dbbishopCommented:
Real quick(off subject) - looks like you could simlify your WHERE clause with:

WHERE dateadd ( hour, -4, TimestampUTC) >= dateadd ( second, -datepart ( second, getdate()),
      dateadd ( minute, -datepart ( minute, getdate () ), dateadd ( hour, -2, getdate() ))) and
      dateadd ( hour, -4, TimestampUTC) < dateadd ( second, -datepart ( second, getdate()),
      dateadd ( minute, -datepart ( minute, getdate () ), dateadd ( hour, -1, getdate() ))) AND
SourceID = 4 AND QuantityID IN (536, 666, 664, 10017, 520, 515, 518, 665, 516, 534, 524, 525, 519, 526, 535)
0
 
sainiakAuthor Commented:
The query was automatically generated by a 3rd party product, so it probably could be simplified.  I will try your suggestion and let you know.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.