0 Comments

I have just enabled the MariaDB 10.x package and installed the famous Heidi SQL from the Microsoft Store (because of the auto update and roaming settings etc.)

So I tried to connect to it. The MariaDB 5.x uses port 3306, but the 10.x uses 3307 (minor detail) However, I could not connect, because by default only localhost connections are enabled.

So I had to SSH into the nas and fix it. But using SSH did not work how it used to. I used Ubuntu (also from the Microsoft store) to `ssh myadminname@localipofnas` but got a cypher not accepted/available message. It turns out that the Synology has disabled several old insecure ciphers. So I had to provide a –c and an algorithm to use in order to get it to work.

“no matching cipher found. Their offer: aes128-cbc,3des-cbc,aes192-cbc,aes256-cbc”

I used `-c aes128-cbc` because it was the first option in the list

Once logged in, I had to launch MariaDB mysql and give the user access within the lan and create a database:

  • Search mysql (/usr/local/mariadb10/bin/mysql)
  • Enter to mariadb with command line: ./mysql -u root –p
    • Use the password
  • Check user's:
    • SELECT User, Host FROM mysql.user WHERE Host <> 'localhost';
  • Granting User Connections From Remote Hosts:
    • GRANT ALL PRIVILEGES ON . TO 'root'@'192.168.1.%' IDENTIFIED BY 'my-new-password' WITH GRANT OPTION;

Found it on: https://stackoverflow.com/a/51277323/169714

You can also use localhost or % instead of the ip. It depends on what you are trying to do Smile

MariaDB is now ready for some .Net Core

(Only my Synology NAS does not support Docker to run .Net Core…)

I am having doubts if I should try to work around it or just use a Raspberry Pi for my .Net core code.


Good luck, hope it saved someone some time!

Pin on pinterest Plus on Googleplus Post on LinkedIn

Building reports is a common task for me and I often struggle putting images in Excel so this post is just a small reference for myself to easy find how I have done it before.

I have build SQL before which would build html to display product images.

SQL like:

select '<tr><td>' + productid + '</td></tr>' from products  

And would paste that in a text file which has <table>…</table> and would save it as .html

That’s ugly and hard to work with, but it would/could show images (if you use <img src=’http://www.google.com/’ etc.)

Copying from the SMSS result panel

image

and pasting in Excel is an option to work with the data (apply extra filters or ordering etc.)

The open source SQL Operations Studio even has a community contribution to export directly to Excel! I just found out searching for the download link that the product is renamed to Azure Data Studio

https://docs.microsoft.com/nl-nl/sql/azure-data-studio/download?view=sql-server-2017

But then you only have a filename or uri or (local) path to a file in a sheet

image

I stored the Excel file as one with macro’s enabled and enabled the developer menu on the lint.

  1. Click the File tab.
  2. Click Options.
  3. Click Customize Ribbon.
  4. Under Customize the Ribbon and under Main Tabs, select the Developer check box.

And I used this small VB sub:

Sub PlacePics()

    Dim Path As String, Pics As Range, Pic As Range
    
    Path = "https://www.ourcompany.com/images/"
    Set Pics = ActiveSheet.Range("F2:F2164")
        
    For Each Pic In Pics
        On Error Resume Next
        Pic.RowHeight = 100
        Pic.Offset(0, 0).Select
        ActiveSheet.Pictures.Insert(Path & Pic.Value).Select
    Next Pic
End Sub

It might take a while to process (depending on the amount of rows). But it gets the job done.

Good luck!

Pin on pinterest Plus on Googleplus Post on LinkedIn

I am currently working on a side-project with the code-first approach. I really like how the framework does migrations and you can use the .sql files in the migration folder etc.

But I am still wrapping my head around the model/poco classes I want to use and the data structure is still changing in this stage of development. I have moved from 1-n relations to n-n etc.

I sometimes stumbled on a situation where there were foreign key constraints which made it (almost) impossible to process a migration. I also did not found how to revert one or cancel one if you have a pending one and cannot update the database.

Because I was still starting with the project I could not bother to loose all the data and start over with a clean/empty database. I have read several blogs about how to do this, but I wanted to blog my situation. Perhaps it’s useful for someone, perhaps it’s just an online note to myself.

Here is how I did it:

  1. Delete the ‘__MigrationHistory’ table
  2. Delete all files in the ‘Migrations’ folder of your project except the ‘Configuration.cs’
  3. Delete all other tables (one by one) in the DB
  4. In the package manager console, select the correct project and ‘Add-Migration <name>’
  5. Update-Database (this will recreate all tables and use the initial data from the configuration file, if you had any)

A lot of blogposts are based on keeping your data. In that case, you should skip step 3 (obvious) and comment out the ‘Up’ method when you have added your migration and have the generated file open.

This is a slightly modified version of this blogpost which uses the brand new SQL Operations Studio 0.23.6


Good luck!

Pin on pinterest Plus on Googleplus Post on LinkedIn
0 Comments

The documentation for Azure SQL db performance tuning is great and accurate with great detail on docs.microsoft.com (instead of the old msdn location)

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-query-performance

I was looking at a SQL db in Azure and noticed some spikes. Here is my graph:

snip_20170215133957

I could drill into the Azure portal to see what was causing this spike and recognized the SQL statement, so I knew which product to update the SQL for. However this was not even necessary.

Here are the client stats when I copy pasted the query causing the spikes in SQL Management Studio Express. Check out the “Total execution time”

snip_20170215120138

My next step was to take a look at the execution plan of the query. There is this exclamation mark at the sort operation:

snip_20170215120257

When you hover it, you get this context popup:

snip_20170215124310

So it used tempdb. I still had no clue how to fix this, so I reached out to stack exchange. And a user named T.H. gave me the solution to create two rather simple indexes:

CREATE NONCLUSTERED INDEX TEST ON STOCKDEBUGTRIGGERED (ChangeDate)

CREATE NONCLUSTERED INDEX TEST ON STOCKDEBUG(ProductID, StockOld, StockNew)

Here is the query plan after the two new indexes:

snip_20170215124852

No more yellow exclamation mark! and fewer steps. The client statistics also prove that this is a lot faster/better:

snip_20170215124929

From an average of 4250 down to 50!

 

This is also backed by the dramatic drop in resource usage in the Azure Portal:

snip_20170215133957

So the lesson is: do not trust the Azure db perf advisor a 100% Smile

T.H. commented on Stack Exchange:

The automatic index advice is extremely limited, and often misleading, so can only be considered as a starting point.

 

Hope this info might help someone troubleshooting Azure Db perf!

 

Good luck!

Pin on pinterest Plus on Googleplus Post on LinkedIn
0 Comments
  •   Posted in: 
  • SQL

latlngHaving a store locator on my to do list, I started months back with storing the latitude and longitude for the stores in a database. I forked this repository in 2012 https://github.com/bjorn2404/jQuery-Store-Locator-Plugin in order to have something like a store locator on our website. The downside to that store locator is that it is all client side and puts a copy of all your customer data on the internet. It is great data for your competition. So to disclose as little as possible and still serve your valued customers, I decided to do the calculation on the backend. Closest to the data. So that would be on the SQL server.

SQL Server has datatypes like points and geometry since 2008. With this SQL statement you can get the distance between my location

lat 51.69917 lng 5.30417 and the locations in the stores table.
SELECT top 10 StoreID
      ,StoreName
      ,StoreLatitude
      ,StoreLongitude
      ,StoreCity
      ,round((geography::Point(StoreLatitude, StoreLongitude, 4326).STDistance(geography::Point(51.69917, 5.30417, 4326))) / 1000, 1) as km
FROM STORES
  where StoreLongitude < 150 and StoreLongitude > -150 and StoreLatitude < 90 and StoreLatitude > -90
order by (geography::Point(StoreLatitude, StoreLongitude, 4326).STDistance(geography::Point(51.69917, 5.30417, 4326)))

The third parameter for the point data type is 4326 which is an enumeration:

And about 4326 SRID. It also called WSG84(http://en.wikipedia.org/wiki/World_Geodetic_System) system. It's the most common system to represent point on spherical(not flat) earth. It uses degree,minute,second notation and it's x and y coordinates are usually called latitude and longitude.

source http://stackoverflow.com/a/405256/169714

SRID (Spatial Reference System Identifier) : https://en.wikipedia.org/wiki/SRID

This post is more of a reference for me to lookup how to calculate distances on the Microsoft SQL server in SQL. But maybe it helps others too.

Good luck!

Pin on pinterest Plus on Googleplus Post on LinkedIn