Free SQL Server maintenance framework

Recently I was searching the internet for a good approach to perform database maintenance and index rebuilds on a scheduled basis. While searching for frameworks I found a nice project at https://ola.hallengren.com/ by Ola Hallengren.

A quote of the description from the authors page:

SQL Server Backup, Integrity Check, and Index and Statistics Maintenance

The SQL Server Maintenance Solution comprises scripts for running backups, integrity checks, and index and statistics maintenance on all editions of Microsoft SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, and SQL Server 2014. The solution is based on stored procedures, the sqlcmd utility, and SQL Server Agent jobs. I designed the solution for the most mission-critical environments, and it is used in many organizations around the world. The SQL Server Maintenance Solution has been voted as Best Free Tool in the 2013, 2012, 2011, and 2010 SQL Server Magazine Awards, and a top session at PASS Summit 2014. The SQL Server Maintenance Solution is free.

If you are looking for a method to perform easy database maintenance look no further and check out the provided page. This is a really nice set of scripts to put to good use in your own SQL environment! And in case you didn’t see that the description states it’s free this is your second chance at realizing this.

One of the nice features is that it contains a procedure to rebuild or reorganize indexes based on a fragmentation threshold.

All this indexation heaven is possible on a per-table-approach or even a complete database-at-once approach. Be aware to schedule this in your maintenance window time, otherwise this could slow down your server performance significantly when you start re-indexing large database tables in the middle of peak hours.

Tuna with Teriyaki Soy sauce

If you like roasted fish from the BBQ, I have a really easy and tasty recipe. Take a look at the picture of the result and decide for yourself if you want to give it a go.

BBQ_Tuna
BBQ Tuna in Teriyaki soy sauce marinade sprinkled with roasted sesame seeds.

What you need:
– Teriyaki sauce
– Soy sauce
– Roasted sesame seeds
– Tuna steaks (around 2/2,5cm thick and as fresh as possible)
– Temperature probe (i use a digital version, something like THIS.)
– Optional: wooden board for a smokey touch. Mine is Canadian western Red Cedar.
– A BBQ 🙂

Preparation:
If you are using a wooden board submerge the it in water for at least 45 to 60 minutes. It needs to be soaking wet when you put it on the BBQ.

Seasoning:
Seasoning dishes is not an exact science and you should always take this as a guideline not a strict rule. Play with the used ingredients until you have something to your own liking.

I mixed about 3 tablespoons of teriyaki sauce with 2 tablespoons of Soy sauce. Smear the mix on the first side of the tuna and sprinkle the roasted sesame seeds on the tuna. The teriyaki mix will glue the seeds to the tuna. Flip the tuna and repeat this with the other side. If you have leftover teriyaki mix keep this aside for later.

BBQ:
Heat up the BBQ with 2 zones. 1 Hot zone and 1 low heat zone. Put the wooden board on the hot zone to heat up. The side where the fish will be put on needs to be on the downside. After 2 to 3 minutes flip the board and move it to the border of the hot zone onto the low heat zone.

Put the tuna on the board and insert the probe into the middle of the tuna to monitor the temperature. Close the lid of the BBQ and wait until the core temperature of the tuna hits 50 degrees Celsius or even 46 degrees for very fresh Sashimi grade tuna. Sashimi grade tuna has been flash frozen to kill any parasites.

If you don’t use a wooden board sear the tuna for a couple of minutes on both sides. After that, move the tuna to the low heat zone and wait for the tuna to hit the desired temperature.

A couple degrees before the desired temperature is hit, sprinkle the rest of the teriyaki mix over the tuna for some extra flavor.

Enjoy! If you are going to try this give me some feedback on how you liked this.

FPV Mini quad racer

A couple of weeks ago i had a walk in the park and ran into two guys who where flying mini quads on a field with a lot of tree’s and obstacles. I found it quite impressive and I could not resist the urge to have a talk with them to check out the hardware they where using. To make an impression of what they where doing check out the youtube movie they made with a Go-Pro on one of the quads: I take no credit for this insane flying. This is one of the guys i met at the park! I had the opportunity to watch his flying with a second pair of FPV glasses. This made me very enthusiastic to get started with FPV flying myself. And i ordered myself a 250 frame from Hobbyking.com.

DSCN1873

This is the unbuild frame with the motors and BESC. Below you will find a build report of the 250 mini-frame and the soldering of the BECS to the integrated circuit board.

DSCN1875

I am using Multistar brushless motors and 12a afro BECS for this project. This was a set bought together.

DSCN1879 DSCN1883

Al the frame parts in the kit and the integrated power distribution board where i will be soldering the ESC’s on. But lets start with building the frame to get an impression of the spots and spaces where all the electronics will be placed. The pictures are quite self explaining so enjoy the ride.

DSCN1885 DSCN1886

 

DSCN1882 DSCN1888 DSCN1890 DSCN1891 DSCN1892 DSCN1893 DSCN1894 DSCN1895 DSCN1896 DSCN1897 DSCN1898 DSCN1899 DSCN1900 DSCN1901 DSCN1902 DSCN1904 DSCN1903 DSCN1910 DSCN1914 DSCN1917

Soldering the ESC with short wires
Soldering the ESC with short wires onto the power distribution board.

DSCN1924 DSCN1929 DSCN1934 DSCN1935

Putting everything together with the esc’s soldered on. And ready to mount on the Naze32 flightcontroller, the receiver and the FPV circuit board.

DSCN1936

My first quad-copter!

Last year i was getting interested in quad-copters and the potential of those cool flying things! Starting to read about them made me decide to try and build one myself. And i started to search the internet for parts and how-to’s to build them.

Back then the quad-copters weren’t that popular yet and self build quads where the way to go if you where capable of basic soldering and following tutorials that is. I also wanted to make my first build as cheap as possible. That in mind i started to browse Chinese websites for quad-copter parts and electronics. And i managed to gather all the vital parts:

  • X525 frame
  • A2212/13T 1000kv motors (4x)
  • 1 x KK board 5.0 (better get the KK 2.1.5 with LCD Display)
  • 30a speed controllers (4x)
  • 3300mah 3 cell battery
  • IMAX B6 50W 5A Charger/Discharger 1-6 Cells
  • HXT Simple Lipoly Monitor 2S~3S
  • Turnigy 9x transmitter and reciever
  • Propellers (1045 CCW 2x & CW 2x) (Counter clockwise & Clockwise)

 

X525 Frame, motors, BECS and flight controller all bought from dealextreme. As a starter kit this has good value for its money. I would also suggest to buy additional propellers and spare parts if you are new to this hobby. You probably will crash a lot at the beginning and its a hard wait for spares to arrive from china when you crashed the quad.

Turnigy 9x transmitter. This transmitter has lots of possibilities for the low price. It is flashable with another firmware which brings this transmitter to another level. After that it competes with transmitters in a price range at least 3 to 6 times the price you pay for this transmitter.

Quad1

This is the finished build of my quad copter. As you could see on this picture the KK multicopter 5.0 board is exchanged for a KK Multicopter 2.1.5 board which has a display on board an is directly tweakable from the board itself.  This makes changing PID controller values much easier and is one of the reasons i exchanged my first flight controller board with this new one. Unfortunately i did built this before i was in the blogging mode resulting in no pictures of the build itself.

Altogether the initial price tag on all this together is around 260 euros. After building and tweaking it do i still say its worth it? Definitely yes, the fun you will be having while building an own quad-copter against the out of the box experience with other of the shelve quads is much bigger. One of the advantages of a self made quad is that if something breaks you will know how to repair it because you have build it. And another cool thing is you will feel like macgyver while building it!

Well uhh.. and now!?

Okay, i must admit it has been a while since i have posted some things on this blog, but i had a quite busy business life and in my spare time i did not have the urge to post some blogs about work related stuff. So the blog went kind of down the drain by the lack of time and energy like the titanic hitting the iceberg….

So why do i write this post you ask? Well, i am changing jobs and i suspect that i will be having a bit more time to do stuff like this. And somehow the irresistible urge is arising to do some blog posts. The only thing what will happen is that i will probably start blogging about random stuff that i find interesting and not limiting myself to BI or Arduino topics.

Second thing is that it will be a bit more informal. English is not my first language and writing quality blog posts in English takes a decent amount of extra energy for double checking the writings. I still try to deliver something readable but it may happen that i oversee errors or typos because of not reading it 5 times over and getting it checked by another person before i post something. Feedback is still appreciated and i will do my best to get the posts improved over time as i learn and blogging gets easier for me.

 

Now lets start to talk business…. So i am back and i want to start blogging again. But the big question is: “About what!?” Probably something i am interested in will be suitable. So to name some things i’m busy with is my baby daughter Sophie (7 months old as of writing), quadcopters, brewing beer and home automating aka domotica.

And with this teaser i will end this “first” post of hopefully more to come.

Stay tuned!

 

 

SSIS with stored procedures and temporary tables

Recently I was working on a stored procedure to accomplish a complex task in SQL Server 2008. So I spend some time working on the procedure. The procedure uses some temp tables created in the procedure itself.

After finishing this procedure it was time to use the procedure in a SSIS package. So I open the dataflow task is will be implemented in. Enter the Exec “<stored procedure name>” command and want to save…

HRESULT: 0xC020204A

Error: ” Error at Combined Job [Get Data[1]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: “Microsoft SQL Server Native Client 10.0” Hresult: 0x80004005 Description: “Invalid object name ‘<tablename>’.”.

Error at Combined Job [Get data[1]]: Unable to retrieve column information from the data source. Make sure your target table in the database is available.

 

Additional information:
Exception from HRESULT: 0xc020204A (Microsoft.SqlServer.DTSPipelineWrap)

But then this error occurs. Very annoying,  but after doing some research the problem is easily solved. SSIS actually does not handle temporary tables how it should. So with an easy workaround you will be able to use the stored procedure in no time.

The solution is to fake the columns it returns so it will pick up the names from the procedure. So we add the following code to our procedure:

IF 1=2
BEGIN
SELECT
CAST(NULL AS INT) AS ID,
CAST(NULL AS NVARCHAR(50) AS Surname,
CAST(NULL AS DECIMAL(18,2) AS Salary,

END”

The code will never become executed because of the IF 1=2 but the column names will get loaded and used as return columns. So you want to enter all the columns of the #tmp table in the code.

Instead of casting al the columns it is possible to select them from an existing table, and the properties will get loaded automatically. But if the table structure of the temp table is non-existent then the description above is the way to go.

T-SQL Cursor

Transact-SQL cursors are used mainly in stored procedures, triggers, and Transact-SQL scripts in which they make the contents of a result set available to other Transact-SQL statements.

The typical process for using a Transact-SQL cursor in a stored procedure or trigger is:

  1. Declare Transact-SQL variables to contain the data returned by the cursor. Declare one variable for each result set column. Declare the variables to be large enough to hold the values returned by the column and with a data type that can be implicitly converted from the data type of the column.
  2. Associate a Transact-SQL cursor with a SELECT statement using the DECLARE CURSOR statement. The DECLARE CURSOR statement also defines the characteristics of the cursor, such as the cursor name and whether the cursor is read-only or forward-only.
  3. Use the OPEN statement to execute the SELECT statement and populate the cursor.
  4. Use the FETCH INTO statement to fetch individual rows and have the data for each column moved into a specified variable. Other Transact-SQL statements can then reference those variables to access the fetched data values. Transact-SQL cursors do not support fetching blocks of rows.
  5. When you are finished with the cursor, use the CLOSE statement. Closing a cursor frees some resources, such as the cursor’s result set and its locks on the current row, but the cursor structure is still available for processing if you reissue an OPEN statement. Because the cursor is still present, you cannot reuse the cursor name at this point. The DEALLOCATE statement completely frees all resources allocated to the cursor, including the cursor name. After a cursor is deallocated, you must issue a DECLARE statement to rebuild the cursor.

Advantages of Cursors:

1. Row by Row operations can be successfully executed.

Disadvantages of Cursors:

1. SQL Server by default have a direct performance degradation on the usage of cursors. SQL Server Engine processes records or data in such a way it will have a performance impact when cursor is used.
2. As you add overhead to the Server / engine, it will eat up your memory and hence other parallel processes will suffer too.
3. Cursors are the SLOWEST way to access data inside SQL Server as it does row by row operations and cursors are over thirty times slower than set based alternatives.

The following information may vary depending on the specific database system.

Fetching a row from the cursor may result in a network round trip each time. This uses much more network bandwidth than would ordinarily be needed for the execution of a single SQL statement like DELETE. Repeated network round trips can severely impact the speed of the operation using the cursor. Some DBMSs try to reduce this impact by using block fetch. Block fetch implies that multiple rows are sent together from the server to the client. The client stores a whole block of rows in a local buffer and retrieves the rows from there until that buffer is exhausted.

Cursors allocate resources on the server, for instance locks, packages, processes, temporary storage, etc. For example, Microsoft SQL Server implements cursors by creating a temporary table and populating it with the query’s result-set. If a cursor is not properly closed (deallocated), the resources will not be freed until the SQL session (connection) itself is closed. This wasting of resources on the server can not only lead to performance degradations but also to failures.

Considerations before using a Cursor:

1. Think of all possible alternatives to solve your problem and if you didnt got any then create a cursor.
2. Consider Temp tables as alternatives to usage of cursors.

Example:

This is a simple example of a cursor witch prints to the message screen of SQL Server.

USE AdventureWorksLT2008
— Declare a variable to save the product id.
DECLARE @ProductID int

— Declare the Cursor
DECLARE cursornaam CURSOR FOR
— SELECT statement for getting a cursor record set
SELECT ProductID FROM SalesLT.Product WHERE Color = ‘Black’

— Open cursor
OPEN cursornaam

— fetch the first ID from the cursor
FETCH NEXT FROM cursornaam INTO @ProductID

— While there is something to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN

— Sub querys & Sub Declares
DECLARE @nameVar varchar(250)
SET @nameVar = (SELECT Name FROM SalesLT.Product Where ProductID = @ProductID )
Print @nameVar

— try to fetch the next ID from the cursor
FETCH NEXT FROM cursornaam INTO @ProductID

— end the actions
END
— Close the cursor, this can be reopened.
CLOSE cursornaam
–Deallocate all the resources of the cursor to free up space
DEALLOCATE cursornaam

Sources:

WIKIPEDIA

MSDN Cursor

SSIS ForEach loop container

In this article I will describe how the For Each loop container works and how to make path references variable.

We have the following situation:

  • A source directory with flat files which we want to import. (source1.txt, source2.txt, source3.txt)
  • A destination directory to import the data. (destination.txt)

Source1.txt contains:

John;Doe;41
Jane;Smith;23
Peter;Pan;16

Source2.txt contains:

Jill;Valentine;23
Alex;Anderson;32

Source3.txt contains:

Mark;Vandenberg;54
Samantha;Smith;28

Destination.txt is empty. Continue reading SSIS ForEach loop container