How can you supress the dash line in the output

Question

Post reply

How can you supress the dash line in the output

Steve Berry

SSC Veteran

Points: 296
More actions
January 26, 2009 at 8:50 am

#196473

Hi all,
This is probbaly a really daft question...but I can't find an answer, no matter where I look.
How do you supress the dash line at the end of the query result....
I have a SP with multiple queries in it that I want to send to a file (a report in effect), and I want to drop the extra dash lines between each of the queries (just for appearances sake).
The column headings being underlined is fine, but the dash line at the end of the query is the problem.
How do you get rid of it?
Thanks for any assistance....
Steve.

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 1

Where are you doing this at? No such dashed line appears in SMS in the Text Mode.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Berry SSC Veteran Points: 296 More actions · Answer 2

Its part of a scheduled job, that runs overnight and outputs to a text file...which then gets picked up by another package and sent out to remote locations as a report.

Bit like a, how did we do last week....

Thanks for the response.

Steve.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Steve, can you post the following please -

The statement from the sproc which writes out the text file

A sample of the text file to show exactly what you are describing

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 4

Steve Berry (1/27/2009)
Its part of a scheduled job, that runs overnight and outputs to a text file...which then gets picked up by another package and sent out to remote locations as a report.
Bit like a, how did we do last week....
Thanks for the response.
Steve.

But, that doesn't answer the question... what are you using to create the files that put's the dashed line in where you don't want it?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Berry SSC Veteran Points: 296 More actions · Answer 5

The output is created by a series of select statements, as sample is shown bellow:

--Output Report Master Header

set @output = 'Weekly Stock Report - Year '+ @yearcode + ' Period '+ @period

select @output

--Output Report sub header

set @output ='Branch ' + @branch + ' ' +(select title from sqs.m_account where account =@branch and ledger ='Branch')

select @output

:NB: Variables are defined else where in the script

which then outputs the following text to a text file as part of a sql scheduled task...

Job 'Weekly Stock Reports' : Step 1, 'Branch 001' : Began Executing 2009-01-27 13:43:42

----------------------------------------------------------------------------------------------------

Weekly Stock Report - Year 2009 Period 01

----------------------------------------------------------------------------------------------------

Branch 023 Warrington Central

----------------------------------------------------------------------------------------------------

All I want to do is drop the ----------- lines.

Thanks for any assistance....

Mike - CI SSCarpal Tunnel Points: 4146 More actions · Answer 6

If what you have above is the only sort of thing you are doing (meaning you are printing off individual statements and not record sets) then you can substitute the

select @output

with

print @output

and you will not get the dashes.

Steve Berry SSC Veteran Points: 296 More actions · Answer 7

i tried that it puts a SQLSTATE 0100 on the of each print line...

i'll the the osql route

thanks any way.

Ron McCullough SSC Guru Points: 63877 More actions · Answer 8

Best I could come up with does output the lines at the Top of the report but does NOT print lines between the report header and sub header:

set @output = 'Weekly Stock Report - Year '+ @yearcode + ' Period '+ @period + CHAR(10) + CHAR(13) -- line feed and carriage return

--Output Report sub header

set @output =@output + ' Branch ' + @branch

select RTRIM(@output) AS 'Weekly Report'

note I shortened the sub header rather than spend time creating a table and loading it with data.

By the way the number of lines appearing before the header is dependent upon the declared size of your variable @output and if using a Word doc as your printing source the orientation effects the result as well.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 9

Steve Berry (1/27/2009)
The output is created by a series of select statements, as sample is shown bellow:
--Output Report Master Header
set @output = 'Weekly Stock Report - Year '+ @yearcode + ' Period '+ @period
select @output
--Output Report sub header
set @output ='Branch ' + @branch + ' ' +(select title from sqs.m_account where account =@branch and ledger ='Branch')
select @output
:NB: Variables are defined else where in the script
which then outputs the following text to a text file as part of a sql scheduled task...
Job 'Weekly Stock Reports' : Step 1, 'Branch 001' : Began Executing 2009-01-27 13:43:42
----------------------------------------------------------------------------------------------------
Weekly Stock Report - Year 2009 Period 01
----------------------------------------------------------------------------------------------------
Branch 023 Warrington Central
----------------------------------------------------------------------------------------------------
All I want to do is drop the ----------- lines.
Thanks for any assistance....

But that STILL doesn't tell me what's running the SELECTs! Are you using DTS, SSIS, OSQL, ISQL, SMS, Hammer and Chisel? What? 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 10

Jeff Moden (1/27/2009)
... Hammer and Chisel? What? 😉

lol - cuneiform?

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 11

jcrawf02 (1/29/2009)
Jeff Moden (1/27/2009)
... Hammer and Chisel? What? 😉
lol - cuneiform?

Heh... :hehe: obviously, a lost language, as well. The OP just refuses to answer a simple question... too bad... there might be a much simpler answer than what the OP has apparently settled for. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Berry SSC Veteran Points: 296 More actions · Answer 12

Good morning all....

Hammer and chisell......I ask you......its a nice funny way to sart the week...thanks 🙂

The sql is run by me running a sql 2005 automated job through Sql server management studio.

The job step is a T-SQL type task, which has been told to output the result to a text file.

Some sample sql text was shown ealier in the thread....with the result it gave me.

I am not sure what else you are asking for....

Its a very basic query....I just wanted to know if there was any way to switch off the dash lines that SQL throws out before and after each query result set.

Thanks for your time.

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 13

I don't know much about SSIS... there may be a simpler way to do this, like applying some sort of filter to the output before routing it to the file. But don't know.

Here's a "brute force" method ...

If you turn your SQL Task code into a stored procedure, then you could have your SQL Task call xp_CmdShell and call OSQL to run the proc. In OSQL, you would use the "-h-1" setting to suppress the header, altoghether. Of course, you'd need to modify your T-SQL a bit to provide the headers as the first part of a unioned select with the meat of your query.

Alternative to SQL might be BCP but both of those require a trip through xp_CmdShell.

Like I said, there should be a simpler way in SSIS, but I don't know what it is.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Berry SSC Veteran Points: 296 More actions · Answer 14

OK cheers.....

I'm already investigating the OSQL route...so I'll add your suggestion to the mix.

Thanks.

Bob Berry:cool: