Showing posts with label tsql2sday. Show all posts
Showing posts with label tsql2sday. Show all posts

Tuesday, September 11, 2018

Pride and Prejudice... and Triggers

https://voiceofthedba.com/2018/09/03/t-sql-tuesday-106-trigger-headaches-or-happiness/
This post is part of #tsql2sday event :)
I know, in the world of the database administrators and developers, the triggers are the ugly duck of the database development.

They are hard to track, hard to maintain, can have a weird transaction behavior if you are not careful and can lead to blocking your entire database if they are poorly programmed (yes, this is the prejudice).


But as anything, if treated well and programmed carefully they can be handy in a lot of special situations, but read again... I put SPECIAL SITUATIONS, let me tell you 2 histories I have with triggers, a good one and a bad one:


The Good


Some years ago, more than I want to admit, I was working on a local bank,  there was a requirement from the banking regulation authority, they ask each bank to report any currency exchange operation in near real-time (a maximum of 30 minutes to report an operation, I cannot remember exactly, but around these times) and the implementation time was some kind of short.

From a database design, there was a lot of tables involved from different types of banking transactions, also, for changing all the source code for all transactions and test all of them to provide a good solution could take a long time to complete, so instead, we choose to use triggers.

Since everybody wants their money, and want it fast, my solution was to just insert the raw data via triggers from all involved tables to a single staging table without performing any validation, so additional overhead can be avoided to any transaction. Then a second process scheduled to run every 5 or 10 minutes collects all the data from the staging table and perform the required validations and formatting, then the transactions were processed and sent to the reporting interface asynchronously.

This process executed for years and was deployed in a fairly quick time without disrupting the existing program logic.


The Bad


Because not everything goes as expected, in another job, we have to migrate a SQL Server from an old version to a newer one, and we had enough time to optimize/change stored procedures and user code to use new features and run faster on the new server.
Everything went smoothly during development and user testing... until we go live... We did not include in the release some triggers that were on some tables on the original server, these triggers perform some extra validation for some special cases on business transactions, since we optimize some of the tables and processes, just deploying the scripts won't work.

That time I spent almost 4 or 5 days living in the office, first solving the issues for already existent transactions and then trying to implement the trigger logic on the stored procedures itself, luckily our team could implement the special cases without using triggers and we weren't fired after that :)



As with the Jane Austin novel... several love/hate histories can be written about triggers, and the only way you can determine if you love or hate them, is via personal experience, get rid of the prejudice and give them a try, maybe they can work for you!

Tuesday, July 10, 2018

Display SELECT query or table as HTML

https://bertwagner.com/2018/07/03/code-youd-hate-to-live-without-t-sql-tuesday-104-invitation/
This post is part of #tsql2sday event :)
For my current job I support a lot of SQL Servers, they come in all the flavors and sizes (old, new, patched, unpatched, big, small, dedicated, shared, built by our team, built by others... etc). In the ocean of different possible configurations, having a centralized monitoring solution is sometimes not possible for the 100% of the servers, because of different reasons (unsupported features for older versions, located in a different domain or datacenter, some weird security restrictions).

Even when we have already in place a custom inventory and monitoring solution (BTW done using C#, asp.net, SQL Server and Powershell), a couple of rogue servers, needs their own special treat, most of the cases this has to do with some kind of monitoring.

So, for these serves, the solution is to implement local jobs to monitor different kinds of stuff and send via email the monitoring and usage results.
We could perfectly send the info in plain text, but reading this in an email, especially when your boss is included in the distribution list is just awful, so we had to format the data first to make it look better...

That's why I created sp_TabletoHTML, a Stored Procedure to convert any table or SELECT query to an HTML table, so you just have to paste the output in your email body.

Before this stored procedure, we had to do the HTML conversion based on each table columns, and after 3 or 4 times of adapting the same code, I was tired and ended up doing this stored procedure.

How it works?


  • Create the stored procedure and remember where you left it (can be the master database, it really doesn't matter as long as you can access it properly).
  • Then you pass in @stTable (yes, Unicode varchar in case you have weird characters in your object names) the name of the table or view containing the data using the schema.name convention or you can also pass a SELECT query sentence, giving you the ability of formatting or cleaning up your data before converting it to HTML.
  • You can specify if want to include column names or not (included by default) or the predefined style you want or no style at all (or leave the parameters at their defaults)
  • Execute the stored procedure and use the output string as your HTML <table> result.
  • Enjoy!

Sample execution:

SET @SQLSentence = 'SELECT 
name,
state_desc,
create_date,
collation_name 
FROM sys.databases'

EXEC sp_TabletoHTML @stTable = @SQLSentence,
 @TableStyle = 3,
 @RawTableStyle = @st OUTPUT

And you will obtain a beautiful looking HTML table like this one:

Formats can vary...

What is the logic involved?


Just tweaking a little bit the FOR XML functionality, and a lot of column reformatting.
This is the core of the formatting, all the columns with the same name of TD that will translate into <td></td> and the rows that will translate into elements named <tr></tr>:


SELECT [COL1] as TD, [COL2] as TD...
FROM ##rowstablePreHTML 
FOR XML RAW('TR'), ELEMENTS


Since some of the queries are build dynamically, I use sp_executesql, that could be a security risk for untrusted sources.

For internal use, we did not check for malicious code, because... I trust my own code, but before releasing it into Github I added a very basic malicious code check (just check for keywords that has nothing to do with table results) and raise an error for any occurrence encountered. As a recommendation, always validate your queries first.

Null values are not converted to an empty string (because of the unique way XML handle their nulls).  Before using it, remove null values from your data.

Some special datatypes like geography, timestamp, XML, image are not supported, if you try to use them, an error will raise, remove these columns before using it.

Where is the code?

Fortunately for you, it is free!

I offer this stored procedure as open source in GitHub, you can check it here.
The link is also in the Free Tools section on this blog.

The full documentation is also available on the Github page, so you can check the parameter usage and some other examples there!

Also, if any of you can improve this SP, please do it, you are more than welcome!

How I Built RAG for My Accounting App on Bedrock and pgvector

The first version of my accounting assistant could not find a single purchase of "comida" in a database full of lunches, misse...