January 17, 2018 at 11:04 pm
Comments posted to this topic are about the item Formatted ASCII Results from a SELECT
January 18, 2018 at 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
January 18, 2018 at 4:35 am
andrew.ing - Thursday, January 18, 2018 4:15 AMWas 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
January 18, 2018 at 8:07 am
I love it! Thanks for the article!
--Jeff Moden
Change is inevitable... Change for the better is not.
January 18, 2018 at 8:37 am
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
January 18, 2018 at 10:16 am
Eric M Russell - Thursday, January 18, 2018 8:37 AMJeff, 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
Change is inevitable... Change for the better is not.
January 18, 2018 at 12:37 pm
Jeff Moden - Thursday, January 18, 2018 10:16 AMEric M Russell - Thursday, January 18, 2018 8:37 AMJeff, 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
January 18, 2018 at 1:28 pm
Eric M Russell - Thursday, January 18, 2018 12:37 PMJeff Moden - Thursday, January 18, 2018 10:16 AMEric M Russell - Thursday, January 18, 2018 8:37 AMJeff, 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
Change is inevitable... Change for the better is not.
January 19, 2018 at 3:40 pm
andrew.ing - Thursday, January 18, 2018 4:15 AMThat'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_formatWas 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
January 19, 2018 at 3:45 pm
John Mitchell-245523 - Thursday, January 18, 2018 4:35 AMandrew.ing - Thursday, January 18, 2018 4:15 AMWas 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
January 19, 2018 at 3:49 pm
Jeff Moden - Thursday, January 18, 2018 8:07 AMI 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
January 19, 2018 at 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.
January 20, 2018 at 3:07 pm
andrew.ing - Friday, January 19, 2018 6:27 PMThanks 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
January 22, 2018 at 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
January 22, 2018 at 7:32 am
andrew.ing - Monday, January 22, 2018 3:54 AMHello 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
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply