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!