Tag: Database

Restoring database and create an instance.

Posted on by Ed Padilla

Hi guys, I want to share on how you will restore an instance from an existing database file. We usually use the snapshot function of Acumatica, if the system has too many tenants, you will make a snapshot every single out of it, which will make it very tiring task for you also added if the database data is so large. Note: This is only good if you have access on the database itself with management tools, like SQL Management Studio for SQL Server, or Any GUI for MySQL/MariaDB Servers which will produce a .bak File or a .SQL File for you to restore. How to Create an Instance from… Read more

Depersonalize Acumatica Database

Posted on by Sergey Marenich

Hi All, If you need to give an Acumatica Database to someone but if you care about data privasy, you may a way to crear some sensetive data from the database. Here I want to share with you a script example that creares and depersonalise some of private information from database: View the code on Gist. Basicall this script resets all passwords, clears all contact and address details, remove files attached. Feel free to improve and extend it for other objects. Hope it helps!

Order of Tenants On Login Screen.

Posted on by Sergey Marenich

Hi Everyone. You may notice sometimes that order of tenants on logins screen might be broken like on this screenshot. That usually happens because the order of companies is broken in the database (where Acumatica stores it). On this screens you can see select of data from Companies table and you see mess in “Sequence” column. Why it can happened? The most common case is snapshot restoration. When you create a snapshot it stores the position of the company to correctly restore it if you need. But if you restore the same snapshot in the different company you may have a situation that 2 companies have the same Sequence number.… Read more

Access Rights Database Reference

Posted on by Sergey Marenich

Hi All, Want to share with you reference about architecture of Access rights in Acumatica. This knowledge you can use for Sharing data between companies Copy data between companies for faster implementations Mass update of access rights with database Just for fun 🙂 All acumatica access rights are located in the 6 different tables: Users, Roles, UsersInRoles, RolesInGraph, RolesInCache, RolesInMember. Lets check all of them. Users – main entity that stores information about user. User management is done though This table includes such user configurations as password, name, email, password settings and some statistics. Roles – the main table that stores names and description of roles. Roles do not have… Read more

Synchronize User Passwords between Companies with SQL Trigger

Posted on by Sergey Marenich

Hi Everybody, Here want to share with you one easy way how to easily sync passwords between users in all companies in Acumatica – we can use SQL triggers. A trigger is a special kind of code that automatically executes when an event occurs in the database server. Database Engine execute trigger when a user tries to modify data through a data using Update/Delete/Insert commands. In our case we can catch the event when password is changed in specific company and distribute it to all other companies we need. Code: View the code on Gist. Have a nice configurations.

SQL In <> Operator in BQL

Posted on by Sergey Marenich

Hi There, Today want to show you example of amazing and very new BQL Operator – In<>. This operator was added just recently with Acumatica version 2017R2, so now you can pass there an array of values and Acumatica core will convert it to SQL IN ( … ) statement. Here is a code example: Object[] values = new String[] { “AC”, “IN” }; InventoryItem item = PXSelect<InventoryItem,        Where<InventoryItem.itemStatus, In<Required<InventoryItem.itemStatus>>>>.Select(Base, values); Console.WriteLine(item.Descr); This will be converted to following SQL Select * from InventoryItem InventoryItem Where InventoryItem.Status In (‘AC’, ‘IN’) Order by InventoryItem.InventoryCD Please note that In<> operator is available only with Required<> parameter and you need to pass array of possible values manually to Select(…) method parameters. Have… Read more

Rates per Day in the Report

Posted on by Sergey Marenich

Hi All, Today I would like to share you my experience in building report that needs to translate currency rate on the fly. The main challenge in this task as you may not have rates for every day. So that mean if your document is for 20th of Jan but there is no rate for that date, you need to find the latest available from list of the rates. Unfortunately this task is solvable just with standard Joins and standard DACs as we need to have little bit more tricky logic. So to fix that I have created an SQL view that can find last available rate for the particular… Read more

Database Timeouts

Posted on by Sergey Marenich

Hi Everyone, As you may know all databases queries (like, Insert, Update, Delete, Select) should have timeout to prevent deadlock and prevent over-usage of hardware resources. In Acumatica we have 2 timeout for Select query: Report Timeout – is used only for reports, as it may need some time to retrieve data from database for big report. To configure it you can use reportQueryTimeout=”100″ in PXSqlDatabaseProvider section of Web.config file. Parameter is defined in seconds and has default value as 30 seconds. You can read more here. Querry Timeout – is used only for inquires and other selects, as some inquiries also might take more time than allowed by default. To… Read more

Database Mapped vs Virtual Fields

Posted on by Sergey Marenich

Hi All, Today want to share with you how Database vs Virtual fields are working in Acumatica. You may notice that some of the fields are linked to DB, some of them not.The difference is in the Attributes are used on it – PXDB<Type>Attribute is mapped on database field, PX<Type>Attribute is not. So from user standpoint it is just prefix “DB” or “”. In Acumatica there are many fields are mapped to database and most of them have a clone, that is not mapped. Here you can see list of most common attributes. Description Database Mapped Field Virtual Field String Value PXDBStringAttribute PXStringAttribute Byte Value PXDBByteAttribute PXByteAttribute Short Value PXDBShortAttribute… Read more

Copy Dashboard using SQL

Posted on by Sergey Marenich

Hi There In case you need it, in Acumatica 6.0 and later all dashboards are located in just 2 tables – Dashboard and Widget. We planning to add ability to copy dashboard soon, but until it is not available, you can use that SQL stored procedure. There is procedure and code to execute it. SQL Script: View the code on Gist. Have a nice dashboard designing!

SQL Formating Tools

Posted on by Sergey Marenich

Hi There, If you are working with Generic Inquiries or Reports or doing a lot of development on Acumatica you most probably have seen already such big and nice queries. And that one is just simple invoice form with one record 🙂 Even if you copy it to SQL Management Studio, it will not be better. But fortunately there are few tools that can help you a lot! 1) Apex SQL Refactor ( https://www.apexsql.com/sql_tools_refactor.aspx ) Even better free plugin to SQL Management Studio that can immediately format query right in place with one button click. 2) Instant SQL Formatter ( http://www.dpriver.com/pp/sqlformat.htm ) Great free cloud app that can format SQL query right in the browser for… Read more

Maintain Table Schema within Customization Project

Posted on by Sergey Marenich

Hi There, Today I want to stop your attention on how Acumatica handles updates for custom tables/columns. In Case you doing medium to complex customization to Acumatica, you most probably will have a lot of custom columns. You know you can add all these custom columns to the standard tables in Acumatica, but in this case you several issues Each new column has to have an Usr prefix to each column. That is required to split standard and custom columns in one table You columns depend on Acumatica table. In case Acumatica have any changes in the tables, you columns may also be affected. But do not worry, they are… Read more

Two way to rebuild all indexes in Database

Posted on by Sergey Marenich

Hi All, Today I want to share with you 2 scripts that you can use for rebuilding all indexes in your Acumatica database. First way by using ALTER INDEX ALL ON [TABLE] REBUILD: View the code on Gist. Second what by using DBCC DBREINDEX command: View the code on Gist. Have a nice maintenance!

Database Maintenance

Posted on by Sergey Marenich

Hi Everyone, Today i want to share with you some ideas of how you should take care of you database. Database is really highly loaded system: you doing a billions of operations,creating of millions of records every day/week/month. And all of these things should go together with highest security, performance and redundancy. To ensure a health of your database you should take case of it regularly, the same like you do with your own health. Otherwise, some day you will see something like this: This really means that something wrong happened with your database. It might be hardware, software, network or other failure. To ensure you database health, just schedule… Read more

Renaming Currency

Posted on by Sergey Marenich

Hi All, Today want to share with you one way, how you can prepare Acumatica database for more personal demo. If you use demo data, you may find that your base currency is in USD. But sometime you want to change it and show all transactions in local currency of your country. But there is no way to do it from the user interface. But Currency code in Acumatica is just a string value that can be renamed on database level. But the tricky this is that it should be renamed in all tables at once, otherwise you will have a data inconsistency. In this article will provide you the… Read more

Clearing lost files and old revisions

Posted on by Sergey Marenich

Hi All, When you use any database for long time, it become big, heavy and full of different garbage and test data. And Acumatica is not exception here. So every time when you want to back up it and restore on different server for testing/investigation, you have to wait for long time. In this article I want to share with you one script that can help you free some extra space in your database. This will minimize database size, backups size and backup/restoration speed. To free some space we want to clear some unused, extra or unnecessary files in the database. This script will: Delete all files what is not… Read more

Reset User Password

Posted on by Sergey Marenich

Hi Everyone, In Acumatica all passwords are stored as a Hash, so even if you get database, you are not able to login with user password. But in case of trouble you can reset it to new one directly in the database. Just execute this query against the sql: UPDATE Users SET Password = ‘123’, LockedOutDate =null WHERE Username = ‘admin’ Don’t worry about clear text password – Acumatica will automatically calculate new hash on first login operation. LockedOutDate is field where system stores last unsuccessful login attempt date-time. So if your user is locked out you can use this statement to unlock it. Thank you!

Analyze Running Queries on Database

Posted on by Sergey Marenich

Hi Everyone. If you face database performance issues, you might need some way to get running queries from there. SQL profiler is good, but some times you do not have it or you don’t have access to it. But if you can run SQL queries, you can use this script. It will show you immediate snapshot of all running queries. Script: View the code on Gist. If you are searching for statistical information you can use that query. This one will show you to 30 heaviest queries on the sever. Script: View the code on Gist. Have a nice development!

Analyse Size of Acumatica Database

Posted on by Sergey Marenich

Hi Everyone, Sometimes, when you have big Acumatica database, you might want to know how much space does it taken by what table. This script can help you: View the code on Gist. The result of this script on my demo database is here: You can see that I have highlighted some tables that contains a lot of data. Actually some of them does not contains important information. Let me point you on some tables that you can archive and clear if you aware about space. This can be helpful if you use cloud hosting and the size of database is not cheep. Logs Tables LoginTrace – all login, logout, screen opening, session expired,… Read more

Script to Select Audit Data from Database

Posted on by Sergey Marenich

Hi Everyone Have you seen this when you select data from Audit table in Acumatica? Acumatica stores all audit in one table Audit History. Keys and Changed fields are saved to database in combined mode. Data is concatenated with symbol , thats why you are not able to read this fields with just normal SQL server management studio. To resolve it, you can replace special symbol just with normal colon. Use this script: View the code on Gist.