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

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: 148790 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: 1002552 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: 125446 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: 1002552 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: 125446 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: 1002552 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!