Formatted ASCII Results from a SELECT

Question

Formatted ASCII Results from a SELECT

bevanward

SSC Eights!

Points: 855
More actions
January 17, 2018 at 11:04 pm

#327207

Comments posted to this topic are about the item Formatted ASCII Results from a SELECT

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

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

andrew.ing SSC Eights! Points: 853 More actions · Answer 1

That's a brilliant idea. I often want to paste a small set of data into an email and it takes effort to make it readable even with SSMS's text output. I like the way that the column widths are compact.

I had to remove the IF EXISTS clause from the following block of code to make the install run on 2012. It's actually redundant so you might want to remove it from source.
if OBJECT_ID('dbo.sp_sqltable_to_text_format') is not null drop procedure if exists dbo.sp_sqltable_to_text_format

Was there a reason for creating persistent tables (temptxt1 etc) rather than temporary ones ? It would be nice that it left no permanent trace in the database (e.g. a utility database) if it fails.

Thanks for sharing - I will be giving it a try.
Andrew

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

andrew.ing - Thursday, January 18, 2018 4:15 AM
Was there a reason for creating persistent tables (temptxt1 etc) rather than temporary ones ? It would be nice that it left no permanent trace in the database (tempdb or a utility database) if it fails.
Andrew

Andrew

I think it's because temp tables created in dynamic SQL aren't visible to the calling procedure, although the converse is false. You can see that by running this:
-- This doesn't work EXEC ('CREATE TABLE #john (j int);'); SELECT * FROM #john;

-- This does CREATE TABLE #john (j INT); EXEC ('SELECT * FROM #john;');

The tables have to be created dynamically at runtime since their structure depends on the @sql parameter. Therefore a permanent (or global temp) table is the only option. That said, I'm not sure why the proc itself is created in tempdb - that will mean it will have to be recreated every time SQL Server starts.

John

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

I love it! Thanks for the article!

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

Eric M Russell SSC Guru Points: 125562 More actions · Answer 4

Jeff, you should run for president (of the American National Standards Institute).

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

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

Eric M Russell - Thursday, January 18, 2018 8:37 AM
Jeff, you should run for president (of the American National Standards Institute).

Heh... you should see my "ASCII ONLY" solution to data/file transmission to make XML and JSON look a little silly.

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

Eric M Russell SSC Guru Points: 125562 More actions · Answer 6

Jeff Moden - Thursday, January 18, 2018 10:16 AM
Eric M Russell - Thursday, January 18, 2018 8:37 AM
Jeff, you should run for president (of the American National Standards Institute).
Heh... you should see my "ASCII ONLY" solution to data/file transmission to make XML and JSON look a little silly.

You're talking about old school CSV or EDI ?

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

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

Eric M Russell - Thursday, January 18, 2018 12:37 PM
Jeff Moden - Thursday, January 18, 2018 10:16 AM
Eric M Russell - Thursday, January 18, 2018 8:37 AM
Jeff, you should run for president (of the American National Standards Institute).
Heh... you should see my "ASCII ONLY" solution to data/file transmission to make XML and JSON look a little silly.
You're talking about old school CSV or EDI ?

Seriously old school. EDI is even worse than XML.

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

bevanward SSC Eights! Points: 855 More actions · Answer 8

andrew.ing - Thursday, January 18, 2018 4:15 AM
That's a brilliant idea. I often want to paste a small set of data into an email and it takes effort to make it readable even with SSMS's text output. I like the way that the column widths are compact.
I had to remove the IF EXISTS clause from the following block of code to make the install run on 2012. It's actually redundant so you might want to remove it from source.
if OBJECT_ID('dbo.sp_sqltable_to_text_format') is not null drop procedure if exists dbo.sp_sqltable_to_text_format
Was there a reason for creating persistent tables (temptxt1 etc) rather than temporary ones ? It would be nice that it left no permanent trace in the database (e.g. a utility database) if it fails.
Thanks for sharing - I will be giving it a try.
Andrew

Hi Andrew

Thanks for your message. I tried various approaches to prevent using physical tables however they became complex and hard to explain so decided on this for my first article. I'll re-write soon and see if I can get the whole process working on temp tables.

I'll also change the test for the drop statements once I find how to change what has been submitted.

Thanks again
Bevan

bevanward SSC Eights! Points: 855 More actions · Answer 9

John Mitchell-245523 - Thursday, January 18, 2018 4:35 AM
andrew.ing - Thursday, January 18, 2018 4:15 AM
Was there a reason for creating persistent tables (temptxt1 etc) rather than temporary ones ? It would be nice that it left no permanent trace in the database (tempdb or a utility database) if it fails.
Andrew
Andrew
I think it's because temp tables created in dynamic SQL aren't visible to the calling procedure, although the converse is false. You can see that by running this:
-- This doesn't work EXEC ('CREATE TABLE #john (j int);'); SELECT * FROM #john;
-- This does CREATE TABLE #john (j INT); EXEC ('SELECT * FROM #john;');
The tables have to be created dynamically at runtime since their structure depends on the @sql parameter. Therefore a permanent (or global temp) table is the only option. That said, I'm not sure why the proc itself is created in tempdb - that will mean it will have to be recreated every time SQL Server starts.
John

Hi Andrew

Yes that is right - for ease of pulling the column definitions I went this way. I'll have to try again and write the whole thing dynamically and see if it can be done within temp tables.

I used the tempdb database so if an individual ran the script they wouldn't impact any of there databases and they could change as required. I normally have a utility database for this purpose.

Thanks for your comments
Bevan

bevanward SSC Eights! Points: 855 More actions · Answer 10

Jeff Moden - Thursday, January 18, 2018 8:07 AM
I love it! Thanks for the article!

Hi Jeff
Glad you are a fellow ASCII supporter! I have to say I too don't get the massive XML dumps people use for very simple structured data. Data that could be processed as delimited text. I make heavy use of bulk insert and it works fantastically after all these years.

Love the hat!

Thanks
Bevan

andrew.ing SSC Eights! Points: 853 More actions · Answer 11

Thanks Bevan and John for the explanation.

I had a chance to use it in a real scenario today but I hit a snag with Null values. At some point the code evaluates something like:
...CAST( ISNULL( [COLUMN], '' ) AS char(37) ) ...

The trouble arises if COLUMN value is Null. If COLUMN is a uniqueidentifer (but Null) then the ISNULL substitution fails because the string '' cannot be converted to uniqueidentifer. Adding another cast can fix this, albeit not very elegantly:
CAST( ISNULL( CAST([COLUMN] AS varchar(max)) , '' ) AS char(37) )

At least, that's what I did. A more difficult problem is if a date type is Null. The empty string can be converted to a date, but it becomes zero which in the old date types is Jan 1 1900. So Null date columns appear as Jan 1 1900 or parts thereof depending on the column width. I didn't have any Null date values in my sample so I didn't tackle this.

After that first small change the output was just what I wanted.

And more grist to the ASCII supporters' mill! In recent months I've seen code for representing output as (a) a CEEFAX (Teletext) screen, and (b) a Solari airport-style mechanical display.

bevanward SSC Eights! Points: 855 More actions · Answer 12

andrew.ing - Friday, January 19, 2018 6:27 PM
Thanks Bevan and John for the explanation.
I had a chance to use it in a real scenario today but I hit a snag with Null values. At some point the code evaluates something like:
...CAST( ISNULL( [COLUMN], '' ) AS char(37) ) ...
The trouble arises if COLUMN value is Null. If COLUMN is a uniqueidentifer (but Null) then the ISNULL substitution fails because the string '' cannot be converted to uniqueidentifer. Adding another cast can fix this, albeit not very elegantly:
CAST( ISNULL( CAST([COLUMN] AS varchar(max)) , '' ) AS char(37) )
At least, that's what I did. A more difficult problem is if a date type is Null. The empty string can be converted to a date, but it becomes zero which in the old date types is Jan 1 1900. So Null date columns appear as Jan 1 1900 or parts thereof depending on the column width. I didn't have any Null date values in my sample so I didn't tackle this.
After that first small change the output was just what I wanted.
And more grist to the ASCII supporters' mill! In recent months I've seen code for representing output as (a) a CEEFAX (Teletext) screen, and (b) a Solari airport-style mechanical display.

Hi Andrew

Thanks for this detail, completely missed it from how I had been using it so far.

However doesn't your fix also work for the null date column as well.
Simply casting to nvarchar(max) before the isnull ...?

For example:

if OBJECT_ID('tempdb..#t1') is not null drop table #t1 create table #t1 (ID int identity(1,1), Things nvarchar(10), DateAdded datetime default getdate())

insert into #t1 (Things) values ('round'),('square'),('ball') update #t1 set DateAdded = null where Things = 'square'

exec sp_sqltable_to_text_format 'select * from #t1'

The whole thing is a bit messy so not fussed with another cast to get the output consistent!

Thanks
Bevan

andrew.ing SSC Eights! Points: 853 More actions · Answer 13

Hello Bevan,

In my haste I didn't try a null date, but you're right of course. Converting to varchar first means that no date or other type is presented to IsNull.

The only other change I made in use was to ensure that the working tables are always dropped. Any error in the SQL parameter will prevent the original procedure from cleaning up, and it became annoying having to delete three tables manually each time.

Thanks again for this. It's a nice alternative to taking screenshots of datasets, which isn't helpful to the end recipient, or taking a detour via (the very un-cooperative) Excel to format columns for pasting.

Andrew

bevanward SSC Eights! Points: 855 More actions · Answer 14

andrew.ing - Monday, January 22, 2018 3:54 AM
Hello Bevan,
In my haste I didn't try a null date, but you're right of course. Converting to varchar first means that no date or other type is presented to IsNull.
The only other change I made in use was to ensure that the working tables are always dropped. Any error in the SQL parameter will prevent the original procedure from cleaning up, and it became annoying having to delete three tables manually each time.
Thanks again for this. It's a nice alternative to taking screenshots of datasets, which isn't helpful to the end recipient, or taking a detour via (the very un-cooperative) Excel to format columns for pasting.
Andrew

Thanks Andrew
Thanks for all of this.
Have edited the code to use your cast suggestion and now the execution does not hang up on the residual working table. I've resubmitted the edit so hopefully it will replace the original post.

Very glad you like it

Keep well - thanks for the ASCII collaboration!

Cheers
Bevan