Solving tech problems

How to import Odoo database and browse locally

Databases

If you use Odoo CRM solution, you can login into your account and under it you will find My Databases. When you click on it, you will be able to export existing database (to make a dump of it). This will be used for import on your local machine. Download this dump archive to your PC. Now, you need to install PostgreSql Database Server on your machine. I am using Windows and I choose to install version 14. You need to have 64-bit Windows OS and you can download PostgreSql…read more

MySQL importing from dump file error 1118 – Row size too large

Databases

Hi, If you have faced a problem when importing your data to MySQL from a large file and it said: ERROR 1118 (42000) at line xxx: Row size too large (> 8126) This is a known bug for MySQL versions 5.6.20.0, 5.6.21.0 and I noticed that I didn’t have this problem at version 5.6.24. In order to make it work also on MySQL 5.6.21.0 edit your “my.ini” configuration file and increase the innodb log size like this: innodb_log_file_size = 256M Hope it solves you the problem, like it helped me,…read more

How to delete duplicated records in MS SQL Server?

Databases, Programming

First, create one demo table Let’s create one demo table first. It will be called Person and will hold some fictive person data. Now, fill the data Insert some fictive person data and do some duplicated records that have the same values for fields¬† firstname, lastname and email. Now the magic Use this subquery trick to extract duplicate records and then delete them. Notice the use of PARTITTION and WITH Transact-SQL keywords. All records that have RowNumber > 1 are deleted.

Using SQL Server APPLY command in queries

Databases

APPLY clause in Transact-SQL is interesting because it can be user in the following scenario: For example, you might create a query that returns a list of payments (in this case last 2 payment amounts and dates) for each subscriber inside your SaaS product. You get something like this 958 Usain Jordan 2014-06-01 299.00 958 Usain Jordan 2014-07-18 458.00 110 Seagal KungFu 2014-01-12 15.16 110 Seagal KungFu 2014-03-06 17.45

Using SQL Server PIVOT feature to transform rows into columns

Databases, Programming

PIVOT queries in SQL Server are useful when we need to transform data from row-level to column-level. Example: Imagine having the table named Invoice (payments per month) – for simplicity let’s say that we have PaidDate and Amount. What we wanted now is to get total money collected from the beginning of the year 2014, and in order to do this we can use this PIVOT query: And we finally get

How to find quickly specific table in DBML designer in Visual Studio

Databases

I spent a lot of time in my life working on complex web, mobile and desktop projects in Microsoft Visual Studio and dealing with databases and I always wanted and easy way to find specific SQL table in DBML designer in Visual Studio. This is how DBML designer looks like: This is only one prt of DBML and if ou don’t have 30″ screen it is really hard to find specific table. Usually, after changing just on column in specific table, or adding/deleting some column,¬† it is easier to delete…read more