[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

I Need To Remove Blank Lines At End Of SQL Query

Posted on 2007-07-26
11
Medium Priority
?
702 Views
Last Modified: 2008-03-06
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...


0
Comment
Question by:sainiak
  • 6
  • 5
11 Comments
 
LVL 15

Expert Comment

by:dbbishop
ID: 19575403
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
 

Author Comment

by:sainiak
ID: 19575469
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
 
LVL 15

Expert Comment

by:dbbishop
ID: 19575534
-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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:sainiak
ID: 19575720
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
 
LVL 15

Expert Comment

by:dbbishop
ID: 19575804
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
 
LVL 15

Expert Comment

by:dbbishop
ID: 19575821
What is the contents of ieaf8.sql
0
 

Author Comment

by:sainiak
ID: 19576640
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
 
LVL 15

Expert Comment

by:dbbishop
ID: 19576936
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
 
LVL 15

Expert Comment

by:dbbishop
ID: 19576975
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
 

Author Comment

by:sainiak
ID: 19577824
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
 

Accepted Solution

by:
sainiak earned 0 total points
ID: 19582458
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

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question