Please watch the video in 1080p because it is recorded in High Resolution.
Please note the script of the presentation is below so you can read along with the demonstration if you like.
Hello and welcome back to our latest, how do I, blog post.
We are happy that you’ve come back to see our latest blog post.
My name is Sophie, and I’m your BIDA AI assistant.
I will be your host for this blog post today.
Today’s blog post is something really important that we wanted to share with you.
It is about a discovery we just made yesterday about something Meta five can do that we were not aware of.
And it’s a big deal so please allow me to first explain what it is we are going to show you today.
In the area of Direct Query we all know that to get useful and valuable results there is a lot of processing that has to happen on raw operational systems data.
We invented the idea of storing CTE queries in a central location and having Meta five read that SQL, then apply parameter resolution, and then execute it against the large operational system.
This was a huge step forward in being able to run direct query against large operational systems and then get the results into Excel.
Of course, there are limitations on CTE queries just like there are limitations on everything.
Yesterday, by accident, we discovered that it’s possible to execute multiple SQL statements in the SQL Entry Tool.
The accident was we opened up the actual tool that creates the workstation tool data dictionary rather than just run it.
And wouldn’t you know.
It has multiple SQL Statements in the text document to create the workstation tools data dictionary.
We just never looked inside it before to see how it does what it does.
So, having explained what we are about to show you?
On with the demonstration.
Here we are on our BIDA development oh one server.
This is our main development server.
You can see the SQL on the screen is three drop statements and three select insert statements.
These are obviously very simple statements just for the demonstration.
Now we will go across to the SQL Server.
You can see the tables in the database BIDA Host Services, Business Central twenty twenty three, Direct Query.
You can see that these temporary tables are not in the database.
Now.
We will go to the spreadsheet that is going to load the SQL statements in the test file into the SQL Server.
You can see the variable for the testing directory and you can see the file name and you can see that the load flag is set to yes.
Now.
We will go to the processing tab.
You can see that the variable convert semicolons to newlines is set to yes.
I don’t think that needs explaining.
Now.
We will go to the master database where the loaded SQL will be stored.
You can see from the load timestamps on the previously loaded versions of this file that we were working late last night in testing out what we are going to show you today.
Now.
We will go to the windows command line to run the command that is going to load the SQL file into the master database in the bida data warehouse sql statements table.
We just need to press the up arrow to get the command back.
Then we press enter.
And it only takes an instant.
Now we can go back to the master database and SQL statements table.
We can re-run the query.
We can see the newest load timestamp.
And we will scroll to the right.
We will copy the SQL statements.
And we will paste them into a text document for you to see.
Just so you know, we do have word wrap turned on so that it looks like there are lines of statements.
But each statement is one full statement on one line and then there are new lines between the statements.
Of course, previously there would be the semicolon at the end of each statement.
Now.
The SQL Server ODBC Driver can accept multiple statements separated by a semi colon on one line.
But you only get the return code for the last statement if you do that.
If another statement fails you can’t tell that from the return code.
So we did not want to do that.
Now.
We will go to our Meta five development oh one server.
You can see on the screen there is a get sql icon.
We will run the three icons so the finished SQL is in the SQL zero, zero, one file.
We will open up the file.
You will see the drop statements at the top.
You will see the three insert statements below.
Now we will close this file.
We will open up the SQL Entry tool that reads that file and sends the statements to SQL Server.
The SQL Entry Tool itself will split the statements by the new lines and then log the results.
You can see the statements on the screen.
Now I will close the SQL Entry Icon.
Now I will press run and the icon will run the SQL against the SQL Server database.
It will only take a second.
Now.
I will open up the SQL Entry Logs.
I have asked the tool to log all SQL Statements including the successful ones.
You can see in the top entry of the SQL Entry logs that each of the statements has been successfully executed.
Now.
We will go back to the SQL Server Management Studio.
We will refresh the tables in the direct query database.
And we will see the three new tables there.
Just to prove it all worked we will select the top thousand rows from each of the three new tables.
And there you have it.
So please allow me to summarize what you just saw demonstrated.
We wrote a piece of SQL in a file that could have comments and semicolons in it.
We loaded that SQL to a master database using a windows command.
The window command removed the comments and the newlines and the semicolons.
It replaced the semicolons with two newlines.
We then went to Meta five and we fetched the sql from that master database.
Then we sent the SQL into SQL Server and executed it.
This is a one step improvement on using CTE statements because you can persist results in tables with this new form of multiple statement processing.
And that, ladies and gentlemen, concludes our demonstration today.
We hope you liked this demonstration today.
With that I’d like to say thank you very much for watching.
We really appreciate your time and attention.
I wish you a nice day.
Sophie.
Your BIDA AI Assistant.
