January 22, 2018 at 1:47 pm
bevanward - Monday, January 22, 2018 7:32 AMandrew.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
FWIW, I've been able to get it to work with GUIDs, DateTimes and Decimal data types without an extra cast, just by putting the cast inside the ISNULL rather than the other way around.
I changed the relevant line to.
select @sql = '''|'''+stuff((select '+'' ''+isnull(cast(['+COL+'] as char('+cast(WIDTH as varchar(10))+')) collate database_default,'''')+''|''' from
I do have one question. There is no order by in the string of unions that you use to output your result.
select @top Output union all
select @columnnames union all
select @top x union all
select Body from temptxt3 union all
select @top
In times past I've trusted this sort of thing to work and it does almost the time but then when I least expect it it returns a different order and everything is messed up. I've always resorted to something like this
SELECT Output FROM (
select Output = @top, Ord = 1 union all
select @columnnames,2 union all
select x = @top, 3 union all
select Body, 4 from temptxt3 union all
select @top, 5) x
ORDER BY Ord
January 22, 2018 at 11:50 pm
tstoneberg - Monday, January 22, 2018 1:47 PMbevanward - Monday, January 22, 2018 7:32 AMandrew.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
BevanFWIW, I've been able to get it to work with GUIDs, DateTimes and Decimal data types without an extra cast, just by putting the cast inside the ISNULL rather than the other way around.
I changed the relevant line to.
select @sql = '''|'''+stuff((select '+'' ''+isnull(cast(['+COL+'] as char('+cast(WIDTH as varchar(10))+')) collate database_default,'''')+''|''' fromI do have one question. There is no order by in the string of unions that you use to output your result.
select @top Output union all
select @columnnames union all
select @top x union all
select Body from temptxt3 union all
select @topIn times past I've trusted this sort of thing to work and it does almost the time but then when I least expect it it returns a different order and everything is messed up. I've always resorted to something like this
SELECT Output FROM (
select Output = @top, Ord = 1 union all
select @columnnames,2 union all
select x = @top, 3 union all
select Body, 4 from temptxt3 union all
select @top, 5) x
ORDER BY Ord
Hi tstoneberg
Yes - looks like you are right, makes sense - thanks I will make the change and save the cast!!
To get a sort working you would have to use a two stage sort - something like
/* return stuff */
select Output from
(select @top Output, 1 Base, 1 Sec union all
select @columnnames, 2, 2 union all
select @top, 3, 3 union all
select Body, 4, row_number() over (order by (select null)) from temptxt3 union all
select @top, 5, 1) x
order by Base, Sec
Does that make sense?
I was hoping the union all would save me from a silly order but is probably to do with how it is arranged on the disk so sorting is probably the most safe.
Thanks
Bevan
January 23, 2018 at 6:59 am
bevanward - Monday, January 22, 2018 11:50 PMtstoneberg - Monday, January 22, 2018 1:47 PMbevanward - Monday, January 22, 2018 7:32 AMandrew.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
BevanFWIW, I've been able to get it to work with GUIDs, DateTimes and Decimal data types without an extra cast, just by putting the cast inside the ISNULL rather than the other way around.
I changed the relevant line to.
select @sql = '''|'''+stuff((select '+'' ''+isnull(cast(['+COL+'] as char('+cast(WIDTH as varchar(10))+')) collate database_default,'''')+''|''' fromI do have one question. There is no order by in the string of unions that you use to output your result.
select @top Output union all
select @columnnames union all
select @top x union all
select Body from temptxt3 union all
select @topIn times past I've trusted this sort of thing to work and it does almost the time but then when I least expect it it returns a different order and everything is messed up. I've always resorted to something like this
SELECT Output FROM (
select Output = @top, Ord = 1 union all
select @columnnames,2 union all
select x = @top, 3 union all
select Body, 4 from temptxt3 union all
select @top, 5) x
ORDER BY OrdHi tstoneberg
Yes - looks like you are right, makes sense - thanks I will make the change and save the cast!!To get a sort working you would have to use a two stage sort - something like
/* return stuff */
select Output from
(select @top Output, 1 Base, 1 Sec union all
select @columnnames, 2, 2 union all
select @top, 3, 3 union all
select Body, 4, row_number() over (order by (select null)) from temptxt3 union all
select @top, 5, 1) x
order by Base, SecDoes that make sense?
I was hoping the union all would save me from a silly order but is probably to do with how it is arranged on the disk so sorting is probably the most safe.Thanks
Bevan
Yes that does make sense. In my case I wasn't worried about the order of the input selection only header and footer.
January 23, 2018 at 2:31 pm
tstoneberg - Tuesday, January 23, 2018 6:59 AMbevanward - Monday, January 22, 2018 11:50 PMtstoneberg - Monday, January 22, 2018 1:47 PMbevanward - Monday, January 22, 2018 7:32 AMandrew.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
BevanFWIW, I've been able to get it to work with GUIDs, DateTimes and Decimal data types without an extra cast, just by putting the cast inside the ISNULL rather than the other way around.
I changed the relevant line to.
select @sql = '''|'''+stuff((select '+'' ''+isnull(cast(['+COL+'] as char('+cast(WIDTH as varchar(10))+')) collate database_default,'''')+''|''' fromI do have one question. There is no order by in the string of unions that you use to output your result.
select @top Output union all
select @columnnames union all
select @top x union all
select Body from temptxt3 union all
select @topIn times past I've trusted this sort of thing to work and it does almost the time but then when I least expect it it returns a different order and everything is messed up. I've always resorted to something like this
SELECT Output FROM (
select Output = @top, Ord = 1 union all
select @columnnames,2 union all
select x = @top, 3 union all
select Body, 4 from temptxt3 union all
select @top, 5) x
ORDER BY OrdHi tstoneberg
Yes - looks like you are right, makes sense - thanks I will make the change and save the cast!!To get a sort working you would have to use a two stage sort - something like
/* return stuff */
select Output from
(select @top Output, 1 Base, 1 Sec union all
select @columnnames, 2, 2 union all
select @top, 3, 3 union all
select Body, 4, row_number() over (order by (select null)) from temptxt3 union all
select @top, 5, 1) x
order by Base, SecDoes that make sense?
I was hoping the union all would save me from a silly order but is probably to do with how it is arranged on the disk so sorting is probably the most safe.Thanks
BevanYes that does make sense. In my case I wasn't worried about the order of the input selection only header and footer.
Hi tstoneberg
For those pieces of the output they won't change order due to how union all functions. Union would be anyone's guess as that is resolving unique between all sets however union all is safe from my findings
Cheers
Bevan
February 5, 2018 at 5:45 pm
Where is the procedure? The "Conclusion" section indicates it is attached, but I don't see a download link. Any help is appreciated.
February 10, 2018 at 11:56 am
mike.mihalik - Monday, February 5, 2018 5:45 PMWhere is the procedure? The "Conclusion" section indicates it is attached, but I don't see a download link. Any help is appreciated.
Hi Mike
No idea what has happened to the procedure that was attached. I will ask the admin who can hopefully reattach - hopefully it is a quick fix.
Thanks
Bevan
February 10, 2018 at 12:03 pm
mike.mihalik - Monday, February 5, 2018 5:45 PMWhere is the procedure? The "Conclusion" section indicates it is attached, but I don't see a download link. Any help is appreciated.
Mike in the mean time the code is as follows:
Not sure if somehow I messed up however it was attached...
Cheers
Bevan
if OBJECT_ID('dbo.sp_sqltable_to_text_format') is not null
drop procedure dbo.sp_sqltable_to_text_format
go
create procedure dbo.sp_sqltable_to_text_format @sql nvarchar(max)
as
/*
Author: Bevan Ward
Date: 9-Dec-2017
Purpose: The following stored procedure turn SQL from a statement into ASCII format table
Copyright: Bevan Ward
Version: v2 added cast(['+COL+'] as nvarchar(max) to convert each field from original so that dates do not convert to Jan 1 1900, etc (big thanks to Andrew.ing)
Syntax: exec dbo.sp_sqltable_to_text_format @sql = '<valid SQL Select statement>'
Example: exec dbo.sp_sqltable_to_text_format @sql = 'select name, type_desc, CONVERT(VARCHAR(10), create_date, 112) create_date from sys.objects where year(create_date) = year(getdate()) and type = ''U'''
Example: ---- Using replicate to create a dotted line for editing
exec dbo.sp_sqltable_to_text_format @sql = 'select name, type_desc, CONVERT(VARCHAR(10), create_date, 112) create_date, replicate(''.'', 100) Comment from sys.objects where year(create_date) = year(getdate()) and type in (''U'',''V'')'
Form of output:
+--------+-------+ ---> -top line '+'( one dash, width of dashes,'+')
| Field | field | ---> -column names
+--------+-------+ ---> -base header line
| value | value | ---> -body
| value | value |
+--------+-------+ ---> -bottom
* make sure format text as non-scaled - courier new for example
*/
set nocount on
declare @top nvarchar(max)=''
declare @columnnames nvarchar(max)=''
create table temptxt3 (ID int identity(1,1), Body nvarchar(max))
/* pump SQL into set table name */
set @sql = 'select * into temptxt1 from ('+@SQL+') x'
exec (@SQL)
/* clean out char-9,10,13 */
set @sql = ''
select @sql = @sql + ' update temptxt1 set [' + COLUMN_NAME + '] = replace([' + COLUMN_NAME + '],char(9),'' '') where [' + COLUMN_NAME + '] like ''%''+char(9)+''%'''
+ ' update temptxt1 set [' + COLUMN_NAME + '] = replace([' + COLUMN_NAME + '],char(10),'' '') where [' + COLUMN_NAME + '] like ''%''+char(10)+''%'''
+ ' update temptxt1 set [' + COLUMN_NAME + '] = replace([' + COLUMN_NAME + '],char(13),'' '') where [' + COLUMN_NAME + '] like ''%''+char(13)+''%'''
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'temptxt1' and DATA_TYPE in ('nvarchar','nvarchar','char')
exec (@SQL)
--
/* build table of ORDINAL_POSITION, COLUMN_NAME, and WIDTH (if null then 2) */
set @sql = ''
select @sql = Stuff((select ' ' + SEL
from (select 'union all select ' + Cast(ORDINAL_POSITION as varchar(10)) + ' POS, isnull(max(len([' + COLUMN_NAME + '])),2)+1 WIDTH, ''' + COLUMN_NAME + ''' COL from temptxt1' SEL
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'temptxt1') x
FOR XML PATH ('')), 1, 11, '')
set @sql='select POS, COL, case when len(COL) > WIDTH then len(COL)+1 else WIDTH end WIDTH into temptxt2 from ('+@SQL+')x'
exec (@SQL)
/*
Construct output parts...
*/
/* top line/base header line/bottom */
select @top = '+'+stuff((select '-'+replicate('-',WIDTH)+'+' from
(select top 100 percent * from temptxt2) x
order by POS asc
FOR XML PATH ('')), 1, 0, '')
/* column names */
select @columnnames = '|'+stuff((select ' '+COL+replicate(' ',WIDTH-len(COL))+'|' from
(select * from temptxt2) x
order by POS asc
FOR XML PATH ('')), 1, 0, '')
/* body */
set @sql = ''
select @sql = '''|'''+stuff((select '+'' ''+isnull(cast(['+COL+'] as char('+cast(WIDTH as varchar(10))+')),'''') collate database_default+''|''' from
(select * from temptxt2) x
order by POS asc
FOR XML PATH ('')), 1, 0, '')
/* write body into one a single field */
set @sql = 'insert into temptxt3 (Body) select * from (select '+@SQL+' Body from temptxt1) Text'
exec(@SQL)
/* return stuff */
select Output from
(select @top Output, 1 Base, 1 Sec union all
select @columnnames, 2, 2 union all
select @top, 3, 3 union all
select Body, 4, row_number() over (order by (select null)) from temptxt3 union all
select @top, 5, 1) x
order by Base, Sec
/* clean up */
if OBJECT_ID('dbo.temptxt1') is not null
drop table dbo.temptxt1
if OBJECT_ID('dbo.temptxt2') is not null
drop table dbo.temptxt2
if OBJECT_ID('dbo.temptxt3') is not null
drop table dbo.temptxt3
if OBJECT_ID('dbo.temptxt3') is not null
drop table dbo.temptxt3
go
February 11, 2018 at 4:26 pm
Thank you
February 28, 2019 at 10:03 am
Thanks, Bevan. Some thing we can all use quite a bit.
Rick
Disaster Recovery = Backup ( Backup ( Your Backup ) )
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply