control over select output results

Hi All !

Is it possible to get rid of these dash symbols which are underlining
the column name when recordset is returned after query execution ?

For example, using isql.exe:

SELECT 'blah'
go

produces the following results:

--
blah

What I want to achieve is just
blah

I know that SET NOCOUNT ON switches the "X row affected" thing. But
how about column headers ?

Thanks for your time,

SeekerHi

I don't think there is a way to stop the dashed lines, although you could
send the output to a file and strip it out with findstr. To get rid of the
column headers (and lines) remove the check box for "print headers" in
tools/options/results or specify -h-1 when using isql or osql.

John
"." <seeker12@.subdimension.com> wrote in message
news:24693db3.0309012131.7cd01fe0@.posting.google.c om...
> Hi All !
> Is it possible to get rid of these dash symbols which are underlining
> the column name when recordset is returned after query execution ?
> For example, using isql.exe:
> SELECT 'blah'
> go
> produces the following results:
> --
> blah
> What I want to achieve is just
> blah
> I know that SET NOCOUNT ON switches the "X row affected" thing. But
> how about column headers ?
> Thanks for your time,
>
> Seeker|||seeker12@.subdimension.com (.) wrote in message news:<24693db3.0309012131.7cd01fe0@.posting.google.com>...
> Hi All !
> Is it possible to get rid of these dash symbols which are underlining
> the column name when recordset is returned after query execution ?
> For example, using isql.exe:
> SELECT 'blah'
> go
> produces the following results:
> --
> blah
> What I want to achieve is just
> blah
> I know that SET NOCOUNT ON switches the "X row affected" thing. But
> how about column headers ?
> Thanks for your time,
>
> Seeker

There is no way to remove this in an interactive session, as far as
I'm aware. If you're running from a script, you could parse the output
and remove the dashes, though.

Simon|||[posted and mailed, please reply in news]

.. (seeker12@.subdimension.com) writes:
> Is it possible to get rid of these dash symbols which are underlining
> the column name when recordset is returned after query execution ?
> For example, using isql.exe:
> SELECT 'blah'
> go
> produces the following results:
> --
> blah
> What I want to achieve is just
> blah

With ISQL and OSQL you can use -h-1 to turn off headers.

In Query Analyzer you can under Tools->Options->Results change results
to text, and select something else than column delimited.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi

There could be a cludge!

Use -h-1 or remove the print headers and do a union. That forces the column
headers as the first row retrieved. You will need a method to sort the
results which probably makes it as bad as having the underlines themselves!

select 'pub_id' as pub_id, 'pub_name' as pub_name, 0 as ' '
union all
select CONVERT(varchar,pub_id), pub_name, 1 from publishers
order by 3

John

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3f544438$0$249$ed9e5944@.reading.news.pipex.ne t...
> Hi
> I don't think there is a way to stop the dashed lines, although you could
> send the output to a file and strip it out with findstr. To get rid of the
> column headers (and lines) remove the check box for "print headers" in
> tools/options/results or specify -h-1 when using isql or osql.
> John
> "." <seeker12@.subdimension.com> wrote in message
> news:24693db3.0309012131.7cd01fe0@.posting.google.c om...
> > Hi All !
> > Is it possible to get rid of these dash symbols which are underlining
> > the column name when recordset is returned after query execution ?
> > For example, using isql.exe:
> > SELECT 'blah'
> > go
> > produces the following results:
> > --
> > blah
> > What I want to achieve is just
> > blah
> > I know that SET NOCOUNT ON switches the "X row affected" thing. But
> > how about column headers ?
> > Thanks for your time,
> > Seeker|||Thanks a heap to everyone who replied !

The -h-1 option is exactly what I was after.

What would we do without USENET !

Have fun!

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns93EAE80C9CB44Yazorman@.127.0.0.1>...
> [posted and mailed, please reply in news]
> . (seeker12@.subdimension.com) writes:
> > Is it possible to get rid of these dash symbols which are underlining
> > the column name when recordset is returned after query execution ?
> > For example, using isql.exe:
> > SELECT 'blah'
> > go
> > produces the following results:
> > --
> > blah
> > What I want to achieve is just
> > blah
> With ISQL and OSQL you can use -h-1 to turn off headers.
> In Query Analyzer you can under Tools->Options->Results change results
> to text, and select something else than column delimited.

control flow of execution of statement

is there a way to check to see if the previous sql statement has completely executed before executing the next statement?

I have a stored procedure that basically has several insert statements. At the end of the insert statements I call bcp to write the table to a text file. The first insert will write a header record into the table. Then it will insert a bunch of records that are selected from other tables and then lastly will write the footer record. My dilemna is that for some reason the first insert of the header record isn't actually happening until the middle of the second set of inserts where it inserts several records from another table. so basically my file ends up looking like this

payment record
payment record
payment record
Header Record
payment record
payment record
payment record
payment record
Footer Record

Can I tell it to wait for the first insert to complete before starting the other insert?

Can you post your sp, table structure and a actual sample of the data? SP's by their nature do not execute the next statement until the previous one has completed. I wonder if you have an index on your table that is causing the data to sort in the format that you have shown even though the insert is happening in the correct order...|||

Here is the stored procedure

The table I am inserting stuff into literally is one field. It is just a way to grab and format data from another table and then call bcp to write the data to a text file.

ALTER PROCEDURE [dbo].[PREPAREFILE]
@.DATE_PAID as char(8), @.HEADER as varchar(MAX), @.FOOTER as varchar(MAX)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

SET NOCOUNT ON;
END

BEGIN
DELETE FROM Temp_Formatted
END

BEGIN
INSERT INTO Temp_Formatted
(formattedRecord)
VALUES (@.HEADER)
END

BEGIN
INSERT INTO Temp_Formatted
(formattedRecord)

SELECT '6' + '000000000000001' + bill_number + installment + space(224) as stub
FROM dbo.Temp_Unformatted

END

BEGIN
INSERT INTO Temp_Formatted
(formattedRecord)

SELECT '7' + '000000000000001' + @.DATE_PAID + space(1) + replace(right('000000000' + rtrim(cast(amount as decimal(9,2))), 12),'.','') + space(224) as payment
FROM dbo.Temp_Unformatted

END

BEGIN
INSERT INTO Temp_Formatted
(formattedRecord)
VALUES (@.FOOTER)
END

Then I call bcp to write the Temp_formatted data to a text file.

What happens though in both the table and the file I get this:
Stub
Stub
Stub
Header
Stub
Stub
Payment
Payment
Payment
Payment
Payment
Footer

What I need is:
Header
stub
stub
stub
stub
stub
payment
payment
payment
payment
Footer

Of course my example output is scaled down. I have over 40,000 stub and payment records.

|||

Does your table have an index on the formattedRecord column?

Looks the first byte for a stub is always "6", first byte for a payment is "7". What is does the Header record look like, especially the first byte (you are passing as an arguement), what does the Footer record look like, especially the first byte (you are passing as an arguement)?

If a table does not have an index, it will store the data in the format that it receives it. The insert statements in your proc run sequentially (meaning each insert has to complete successfully before the next insert statement executes).

|||well, for testing purposes i've been just passing 'header' for @.header and 'footer' for @.footer. But it will always be different. No, there are no indexes on the the formatted table. No keys no indexes...nothing. I even ran a test with only writing header/payments/footer without stubs and it does basically the same thing. I will get a bunch of payments then the header and then the rest of the payments and then the footer. It is really really weird.|||

I'm at a loss. Do you know what the header and footer rows will look like (really what the first byte will be)? Will it always be the same?

You could get around this by using a query with an order by clause to load your bcp.

As an example, let's say your header will always start with 'h' and your footer will always start with 'f'

Select formattedRecord
From Temp_Formatted
Order by
Case left(formattedRecord, 1)
When 'h' then 1
When '6' then 6
When '7' then 7
When 'f' then 9
Else 8 -- This forces everything else to sort before the footer
End

Try the above query and see if that gives you the order you want.

|||yeah they are going to be different. Gee, you would think this would be pretty simple. I don't get why it is doing it this way. its really odd that it inserts between the stubs. You don't know of anyway to do the check to see if the header is there first?|||If you know what the header will look like (and it is formatted differently than the footer), you can probably modify the order by clause I posted. Only other thing I can suggest is to drop the table and recreate it. I've never seen this happen before.|||

SQL is a set-based language and tables are unordered set of rows. So even if you insert some rows in a particular order you will not be able to read it in the same order without specifying an ORDER BY clause in your SELECT statement. Any other assumption to the order of the rows based on index or query plan is incorrect. The easiest way to solve this problem is to add an identity column to the table and then modify your BCP to use queryout option & issue a SELECT on the table with the ORDER BY clause specifying the identity column. This will ensure that the you can retrieve rows in the order in which you inserted and this assumes that there is only one instance of SP inserting data into the table at any point in time.

Alternatively, you can do this without any table at all like below:

-- PrepareFile SP

SELECT t.Data

FROM (

SELECT 0, @.HEADER

UNION ALL
SELECT 1, '6' + '000000000000001' + bill_number + installment + space(224) as stub
FROM dbo.Temp_Unformatted

UNION ALL

SELECT 1, '7' + '000000000000001' + @.DATE_PAID + space(1) + replace(right('000000000' + rtrim(cast(amount as decimal(9,2))), 12),'.','') + space(224) as payment
FROM dbo.Temp_Unformatted

UNION ALL

SELECT 2, @.FOOTER

) as t(SortCol, Data)
ORDER BY t.SortCol

Now, change your BCP to just call this SP using queryout option.

Continue package execution on task failure

Hi,

I am developing an SSIS package and need the execution of the package to continue even if one of the tasks within the package fails. I have an OnError event handler for this task which fires when it fails but want the rest of the package to continue.

Any suggestions greatly appreciated.

Thanks

Hi PK2000

Can you tell me on what kind of package it fails on and what the next package is that you want to execute?

Kind Regards,

Joos Nieuwoudt

|||

Hi Joos,

I probably didn't explain the problem clearly......

I have one SSIS package with multiple tasks. I want the the package to continue even if the first task within the package fails.

Thanks

|||Hi

by default the arrows between your tasks on the control flow (precedence constraints) are green. It means that you require the task to be successful in order for the next task to be launched.

You can change this behaviour by double-clicking on the green arrow to change the constraint value from 'Success' (green) to 'Completion' (blue) - the next task will then be executed on both failure or success.

cheers

Thibaut|||

Hi PK2000,

Sorry, I understood your question wrong but Thibaut's answer is correct. Try it.

Kind Regards,

Joos Nieuwoudt.