Help with formating

Question

Help with formating

Viewing 14 posts - 16 through 28 (of 28 total)

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

Greg Snidow SSCoach Points: 16108 More actions · Answer 1

I never thought of that. I guess I would have to name the file something else every time right? That would mean I would have to save them. I will try it out, could be handy for other, less frequent emails I send. I guess that is why you get paid the big bucks. Thanks for the idea Jeff; it should keep me busy for a couple of days.

Greg

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

Or - you could run reporting services and just send them a link to the report (which wouldn't need to change)...

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Greg Snidow SSCoach Points: 16108 More actions · Answer 3

Matt Miller (2/15/2008)
Or - you could run reporting services and just send them a link to the report (which wouldn't need to change)...

Well, I do not want to have to save any files, since space may become an issue. I totally cheated to fix the column header alignment problem anyway. I simply inserted 1 dummy record with the names of the columns. Then, when I pad the fields, the dummy record gets padded as well. Then, when constructing the email I do SELECT the dummy UNION SELECT the rest. Probably not the best way to do it, but it works, and I do not have to save any files. Here is what it ended up looking like.

The following jobs have been released in BDMS and need to be scheduled

CO WO_ID RTC OSP_JOB_TYPE JOB_SCOPE

Arlington ARTNVAAR-ARTEST1 02/01/2008 DISTRIBUTION NULL

Braddock FRFXVABF-TEST1 02/01/2008 NULL NULL

Mount Vernon ALXNVAMV-MVTEST1 02/01/2008 DISTRIBUTION NULL

Springfield SPFDVASP-831TEST 02/01/2008 DISTRIBUTION NULL

CO WO_ID RTC OSP_JOB_TYPE JOB_SCOPE

Arcola 9SB-8705-BILTEST 02/01/2008 OTHER NULL

Arcola 9SB-8705-TEST01 02/01/2008 OTHER NULL

Arcola 9SB-8705-TEST02 02/01/2008 OTHER NULL

Arcola 9SB-8705-TESTEWOMOTT 02/01/2008 DISTRIBUTION NULL

Arcola 9SB-8705-TESTING 02/01/2008 DISTRIBUTION NULL

Ashburn ASBNVAAS-TEST1 02/01/2008 NULL

Not bad, for an amateur, huh? Anyhow, thank you all for your suggestions, and the time you took to consider my dilemma.

Greg

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

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

DOH! (making thumbsucking noises and triddling hair)... sometimes I just get balled up on things. Of course, Greg!... Nice simple solution like that is the way to go. Well done.

--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)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

Greg Snidow (2/15/2008)
Matt Miller (2/15/2008)
Or - you could run reporting services and just send them a link to the report (which wouldn't need to change)...
Well, I do not want to have to save any files, since space may become an issue.

I didn't say that very well - Reporting services would allow you to send them to somewhere that would generate (dynamically) the report they needed. No storage required.

That being said - you've already got a good solution. Should do well enough (as long as no one uses a proportional font for their preferred incoming e-mail font, which might introduce a LITTLE raggedness). Necessity is the Mother of invention after all....:)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

J-440512 SSCertifiable Points: 6285 More actions · Answer 6

The UNION SELECT column_titles is the first thing I should have thought of to solve my debugging problem in Query Analyzer.

Either you set the query mode to gridm and it is easy to paste the results in Excel but ** WITHOUT ** the stinkin' column headers

or you set the query mode to text and you do get the column headers but everytinh is separated by spaces, worthless to Export to Excel. If any field contains words separated by spaces, you can forget about REPLACING ' ' BY ' ' and REPLACING ' ' by the TAB character.

Rats.

So, I just tried this:

[font="Courier New"]SELECT 1 AS myOrder, 'groupcode', 'typeid'

UNION

SELECT TOP 10 2 AS myOrder, groupcode, typeid

FROM reservedtypes

ORDER BY myOrder

1groupcodetypeid

2ADRADADM

2ADRADBIL

2ADRADDEP

2ADRADDIS

2ADRADPAY

2ADRADSHP

2ASRARCPO

2ASRARDIS

2ASRARSHP

2ASRARSTR[/font]

Why did I not thunk of that?

BUT unfortunately

[font="Courier New"]SELECT 1 AS myOrder, 'groupcode', 'typeid' , 'sequenceno'

UNION

SELECT TOP 10 2 AS myOrder, groupcode, typeid , sequenceno

FROM reservedtypes

ORDER BY myOrder[/font]

Results in:

[font="Courier New"]Server: Msg 245, Level 16, State 1, Line 1

Syntax error converting the varchar value 'sequenceno' to a column of data type int.[/font]

Yes, I could have converted field sequence_no to varchar in the SELECT, but since I do this for debugging stored procedures, I want ALL columns without having to

1. list them all

2. examine each one to see if its type requires a conversion to varchar.

Some tables hold 30 columns and this is getting out of hand.

doh.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

So, maybe write some code that writes code for you?

Also, you can set the results in the text mode to use the TAB character to separate columns to make the whole ball of wax compatible with Excel. Then, you can just do a SELECT * from the table and forget about 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)

J-440512 SSCertifiable Points: 6285 More actions · Answer 8

Mr. Moden,

I just set the options of the query analyzer as you suggested. Thanks a million for both:

1. a very useful suggestion

2. not laughing at my dumb error.

Regards

Greg Snidow SSCoach Points: 16108 More actions · Answer 9

J (2/15/2008)
The UNION SELECT column_titles is the first thing I should have thought of to solve my debugging problem in Query Analyzer.
Either you set the query mode to gridm and it is easy to paste the results in Excel but ** WITHOUT ** the stinkin' column headers
or you set the query mode to text and you do get the column headers but everytinh is separated by spaces, worthless to Export to Excel. If any field contains words separated by spaces, you can forget about REPLACING ' ' BY ' ' and REPLACING ' ' by the TAB character.

J, if you are copying and pasting results into Excel, why don't you copy the query code into the SQL window of Excel's query builder? Or even better, save the query then choose it as the data source of a spreadsheet. No need to worry about inserting the column headers, because they will show up on their own. Once you build the DSN and store it in your data sources file, it will always be there. The only problem, though, is that it only works with views, not procedures, but it could save you some headache.

Greg

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 10

...It's a shame it took them all of the way until 2005 to hear the clamouring out there, so that they introduced the "include column headers into the query output and during copy/paste" (I'm paraphrasing - but that's what it does).

That was a rather big "umm - you missed a spot" in QA.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

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

J (2/15/2008)
Mr. Moden,
I just set the options of the query analyzer as you suggested. Thanks a million for both:
1. a very useful suggestion
2. not laughing at my dumb error.
Regards

Thanks for the feedback, J... and, it's not a dumb error at all... I know folks that have worked with SQL Server 2000 since it came out that didn't know that until recently. Besides, I've made some pretty dumb errors of my own... I just wouldn't laugh at someone else's "dumb error" unless it was the 3rd or 4th time they didn't try what was posted.

Here's another useful trick that a whole lot of people don't know about... in SQL Server 2000, get into Query Analyzer and press the {f4} funtion key in the top row of keys on your keyboard... it's real handy...

--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)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 12

Matt Miller (2/15/2008)
...It's a shame it took them all of the way until 2005 to hear the clamouring out there, so that they introduced the "include column headers into the query output and during copy/paste" (I'm paraphrasing - but that's what it does).
That was a rather big "umm - you missed a spot" in QA.

But, MS didn't really miss it... us users did... that "tab" delimited output of the text window does it just fine...

--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)

J-440512 SSCertifiable Points: 6285 More actions · Answer 13

Mr. Snidow,

Thanks for another idea. I guess like most people I only use a fraction of all features of any software. Never really thunk of that one. Typically, I work from the Query Analyzer, to get it as close as possible from the horse's moth. For instance how much time does a complicated query takes to run (without printing traces, which also takes time). I use Excel to collapse columns I am not interested in and to sort the results set in different ways.

Mr. Miller,

So, it seems I was not the only one suffering in silence...

Mr. Moden,

Yup, tab-delimited Query Analyzer window results works just fine. Why I never thunk of looking at QA setup options I beyond me...

And all,

I have had the same problem of not getting the column header when I used bcp to export data to a text file or an Excel spreadsheet. (Though, generating an Electronic Data Interchange text file, this was a blessing).

Never thought this could have generated so much interest, moving ever away from the initial topic.

Thanks and regards all.

J-440512 SSCertifiable Points: 6285 More actions · Answer 14

And about a code generator to list the columns, to belabour the point,

check

http://www.sqlservercentral.com/scripts/T-SQL/61812/

Don't forget the Title

By Absinthe, 2008/02/18