Dynamic creation of an ASCII "banner"

Question

Dynamic creation of an ASCII "banner"

Irish

Hall of Fame

Points: 3958
More actions
January 7, 2019 at 11:19 am

#381119

I've been searching around the Internet and have not found anything for this, therefore it is possible that it does not exist already. I don't know that I want to create it, but I could probably figure it out (maybe).
Here's the ask:
Given the text from something like:
DECLARE @banner VARCHAR(20); SET @banner = (SELECT @@SERVERNAME)
The output of the Server Name would be SQLSRVR-01 in this instance, I would like to output something like this:
I realize that I can bury this in my script to output as a print as I have done so many times, but the scripting that I am working on could be run on different Servers by users who are not DB Operators or DB types. If I can build this type of banner dynamically that would be ideal.
Anyone who might have seen a solution for this already, please let me know.
Thanks for your consideration and time.

Regards, Irish

Viewing 10 posts - 1 through 9 (of 9 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Jeffrey Irish - Monday, January 7, 2019 11:19 AM
I've been searching around the Internet and have not found anything for this, therefore it is possible that it does not exist already. I don't know that I want to create it, but I could probably figure it out (maybe).
Here's the ask:
Given the text from something like:
DECLARE @banner VARCHAR(20); SET @banner = (SELECT @@SERVERNAME)
The output of the Server Name would be SQLSRVR-01 in this instance, I would like to output something like this:
I realize that I can bury this in my script to output as a print as I have done so many times, but the scripting that I am working on could be run on different Servers by users who are not DB Operators or DB types. If I can build this type of banner dynamically that would be ideal.
Anyone who might have seen a solution for this already, please let me know.
Thanks for your consideration and time.

Just curious, but what is this for?

drew.allen SSC Guru Points: 76944 More actions · Answer 2

It shouldn't be that hard to create, but I also have to ask why. For any kind of report that might need this, I would use reporting software where you can set the font size to be as large as you want or is practical with minimal hassle.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Irish Hall of Fame Points: 3958 More actions · Answer 3

Hello Lynn and Drew,
You have both asked a fair question.
The query will need to be run on multiple Servers and the query checks a password hashed value against the "master" value across hundreds of databases on each Server. I want this banner to make it obvious which Server the result set comes from so that there is no confusion when the values do not match on one of the databases.

I'm also trying to build this and make it obvious because I'm going to be heading out on an extended leave and the operation in question needs to be reasonably easy to deal with for a wide skill range.

I already use banner type messages for check conditions. If there is a problem the result displays something like this:

Along with "helper" text indicating the condition that needs to be corrected.

Hopefully, this is enough of an explanation.

Regards, Irish

drew.allen SSC Guru Points: 76944 More actions · Answer 4

It reminds me of ASCII art from the 80s (70s?) and it definitely works best with a fixed-width font. I still think it's more trouble than it's worth, but here is how I would approach it.

DECLARE @input_string VARCHAR(100) = 'ERROR', @num_lines TINYINT = 7

CREATE TABLE #ASCII_Art
(
    Chr            CHAR(1)
,    Line_Num    TINYINT
,    Art            CHAR(11)
)
;

INSERT #ASCII_Art(Chr, Line_Num, Art)
VALUES
   ('E', 1, 'EEEEEEEE')
,    ('E', 2, 'EE')
,    ('E', 3, 'EE')
,    ('E', 4, 'EEEEEE')
,    ('E', 5, 'EE')
,    ('E', 6, 'EE')
,    ('E', 7, 'EEEEEEEE')
,    ('O', 1, ' OOOOOOO')
,    ('O', 2, 'OO     OO')
,    ('O', 3, 'OO     OO')
,    ('O', 4, 'OO     OO')
,    ('O', 5, 'OO     OO')
,    ('O', 6, 'OO     OO')
,    ('O', 7, ' OOOOOOO')
,    ('R', 1, 'RRRRRRRR')
,    ('R', 2, 'RR     RR')
,    ('R', 3, 'RR     RR')
,    ('R', 4, 'RRRRRRRR')
,    ('R', 5, 'RR   RR')
,    ('R', 6, 'RR    RR')
,    ('R', 7, 'RR     RR')
;

WITH Base AS (SELECT n FROM (VALUES(0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) v(n) )
, Tally AS ( SELECT ROW_NUMBER() OVER(ORDER BY @@VERSION) AS n FROM Base A, Base B )
, Art AS
(
    SELECT t.n AS Chr_Num, aa.Line_Num, aa.Art
    FROM Tally t
    CROSS APPLY ( VALUES(SUBSTRING(@input_string, t.n, 1)) ) v(chr)
    INNER JOIN #ASCII_Art aa
        ON v.chr = aa.Chr
)
SELECT TOP(@num_lines) art
FROM Tally ln
CROSS APPLY
(
    SELECT a.Art AS [text()]
    FROM Art a
    WHERE a.Line_Num = ln.n
    FOR XML PATH('')
) Art(art)
ORDER BY a.Line_Num

DROP TABLE #ASCII_Art

NOTE: I have assumed a CASE INSENSITIVE approach. If you want CASE SENSITIVE, you'll need to some extra work.

Drew

Edit: Added an ORDER BY clause to ensure that the lines print in the correct order.

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Irish Hall of Fame Points: 3958 More actions · Answer 5

Yeah. This is like the types of headers used in the giant "green-bar" printers that they still run in some hospitals.

It likely is more trouble than what it is worth, but that's how I like it.:hehe:

I think your approach will help me get started, I just never thought I'd want or need to do something like this.

Thanks for taking the time.

Regards, Irish

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

My concern is that T-SQL isn't an interactive environment so really not sure how you are using it.

GaryV Hall of Fame Points: 3307 More actions · Answer 7

drew.allen - Tuesday, January 8, 2019 9:43 AM
It reminds me of ASCII art from the 80s (70s?) and it definitely works best with a fixed-width font. I still think it's more trouble than it's worth, but here is how I would approach it.

I remember that - vaguely - and yes, Drew's approach was how we set it up. (Although I think we put it in a FORTRAN array structure, cause what the heck was a database?)

We had 2 different formats - the single width like Drew showed, and a double-width stroke that probably used up about 12 lines to print.

And the reason you can't find a reference on the Internet - wasn't no stinkin' interwebs back when this was popular to use.

(Aside: I receive a series of transaction and error reports from a vendor that shall remain nameless to protect the guilty. I was shocked to see the line-printer 0's and 1's in the first column of the text files. Really?)

Thanks for the stroll down memory lane.

xsevensinzx One Orange Chip Points: 25560 More actions · Answer 8

I'm a multi-user dungeon (MUD) player since the late 90's. This is how I got into the technical field as MUD's are what World of Warcraft is today. A massively multiplayer online role-playing game (MMORPG), just text-based on Unix systems coded in C.

In that, we use a lot of ASCII to help translate text to pictures. If you refine your search to ASCII generators with MUD's, you will likely find a few examples of how we coded dynamic ascii in C/C++ for these games. For example, Auto-Mapping systems where we dynamically generate a full colored ascii map when a character moves into a single room. The other is the banners. Every MUD has an ascii banner.

Below is the banner for my game.

Irish Hall of Fame Points: 3958 More actions · Answer 9

Lynn Pettis - Tuesday, January 8, 2019 2:55 PM
My concern is that T-SQL isn't an interactive environment so really not sure how you are using it.

You're right, it is not interactive. However, if you run the same query in different windows/tabs in SSMS and look at the result set it could be hard to recall which Server one was looking at. I'm just using this banner as a means to indicate what Server the results came from. It is more or less spoon-feeding the information to the user.

Regards, Irish