Acumatica requirements for development

Hi everybody,

today I want to leave a short note regarding of what is needed, in order to be able to develop for Acumatica ERP.

REQUIREMENTS FOR DEV MACHINE

Display resolution: Minimum 1024 × 768, Typical 1920×1080

Adobe Reader: (to open Acumatica ERP PDF documents) 2019 or later

Microsoft Office: (to view documents exported from Acumatica ERP)

  • MS Office 2019
  • MS Office 2016
  • MS Office 2013
  • MS Office 2010
  • MS Office 2007
  • MS Office 2003 with the Microsoft Office 2007 compatibility pack

IIS

Web Browsers:

  • Microsoft Edge 44 or later
  • Mozilla Firefox 82 or later
  • Apple Safari 12 or later
  • Google Chrome 87 or later

As of June 15, 2022, Microsoft Internet Explorer is no longer supported by any version of Acumatica ERP as the browser has been retired by Microsoft who now recommends Microsoft Edge.

DATABASE REQUIREMENTS

Microsoft SQL Server: 2019, 2017, or 2016

MySQL Community Edition Server: 5.7 and 8.0 64-bit edition

MariaDB: Version 10

Memory: 8 GB RAM

CPU: 2 cores; 2 GHz

Hard Disk Space: For each database, 1 GB available hard disk space. Depending on the number of transactions, additional hard disk space may be required to store large numbers of transactions.

CODE AUTHORING ENVIRONMENTS

To create stand-alone applications with Acumatica ERP or develop customizations and add-on solutions on top of Acumatica ERP, you need one of the integrated development environments (IDEs) listed below.

Operating System

  • Windows 10
  • Windows Server 2019
  • Windows Server 2022

Microsoft Visual Studio with Microsoft Web Developer Tools:

  • 20xx: Community, Professional, and Enterprise editions ( xx stands for version numbers, 09, ..., 19, 22 )
  • (OR) Rider 

Summary

If to sum upp, if you want to develop Acumatica, you'll need Windows, IIS, Database and Visual Studio or Rider

New Sql Server 2016 Features To Use In Acumatica

 

Hello everybody,

today I want to write about new additions for SQL Server T-SQL language, which are there starting from 2016. It is create or alter syntax.

In the past, if you've made some custom SQL view, it was needed to have constructions like this ( pseudo code ):

if view ( stored procedure, function, trigger ) exists

     create view ( stored procedure, function, trigger )

else

   alter view ( stored procedure, function, trigger )

But startring from 2016 SP1 you can use following syntax ( pseudo code ):

Create or alter view ( stored procedure, function, trigger ).

Or in code form it may look like this:

create or alter procedure yourProcedure
as
begin
 print (1)
end;
go
create or alter function yourFunction()
returns int
as
begin
 return(1)
end;
go
create or alter view yourSqlView
as
 select 1 as col;
go
create or alter trigger yourTrigger
on Product 
after insert, update 

Summary

With those SQL features your code of customizations may become much simpler and less prone to errors

 

How To Deal With Read Commited Snapshot Error Message In Acumatica

 

Hello everybody,

today I want to leave SQL fix for error message:

"There are problems on database server side:

READ_COMMITTED_SNAPSHOT is not set for current database.

On your Acumatica instance it may look liike this:

For fixing run this SQL:

ALTER DATABASE Saddleback_staging SET READ_COMMITTED_SNAPSHOT ON

 

How To Create Unique Index In Ms Sql That Will Allow Null

 

Hello everybody,

today I want to leave a post about the following case: you need to index rows in your database by some unique value, which can be null. And you don't want index to include those values which are null. 

How to achieve it? Following T-SQL can do this:

CREATE UNIQUE INDEX idx_studentcardid_notnull ON dbo.Students(studentcardid) WHERE studentcardid IS NOT NULL;

 

in this example MS SQL will create Unique index, but that index will be applied only to non null values, while null values will be ignored by WHERE condition.

 

How To Make Back Up Of Db Smaller In Ms Sql

 

Hello everybody,

today I want to document one hack that preserved me plenty of space on my hard drive.

Recently I've tried to make back up of production database and was shocked to see that it had almost 200 Gb on my hard drive.  So it made me wonder how can I have smaller backups. 

And here is the solution.

  1. In Microsoft SQL Server Management Studio click on database, and choose new query
  2. enter there following command: sp_configure 'backup compression default', 1
  3. then this: reconfigure WITH OVERRIDE;

After such manipulations I've got back up that had only 27 Gb.

No Comments

 

Add a Comment
 

 

Hierarchy For Cte In Ms Sql

 

Hello everybdoy,

today I want to write some notes about hierarchy research in ms sql database. 

Imagine you have following structure in your table 

TableWithParentChild:

id               |           rootID   |     some additional fields

1                |            1          |      ----

2                |            1          |      ----

3                |            2          |      ----

4                |            2          |      ----

5                |            2          |      ----

and image that you need to get info like who is below 1. 

for this purpose following sql can be useful:

-- wrap up tree selection into tvf
create function HierarchyFunction(@parentID int)
returns table
as
return
with tree
as
(select id,  rootID from TableWithParentChild
         where id = @parentID
         union all
         select p.id, p.rootID from TableWithParentChild as P
         join tree on tree.id = P.rootID
         and P.id != P.rootID
)
select * from tree

select p.* from  TableWithParentChild as p
join HierarchyFunction(1) as tree
on p.id = tree.id

No Comments

Add a Comment
 

 

How To Clean Plan Query Cache

 

Hello everybody,

some sql notes:

dbcc freeproccache; this one will clean plan of cached of sql.

Another note of how to find Sql code and plan by id from dm_exec_cached_plans

create function SqlAndPlan(@handle varbinary(max))

returns table

as

return select sql.text, cp.usecounts,cp.cacheobjtype,

cp.objtype, cp.size_in_bytes,

qp.query_plan

 from

 sys.dm_exec_sql_text(@handle) as sql cross join

 sys.dm_exec_query_plan(@handle) as qp

 join sys.dm_exec_cached_plans as cp

 on cp.plan_handle = @handle;

then you can use it in the following way:

 select * from SqlAndPlan(0x06650D007852A00B40613C82000000000000000000000032)

And in order to see sql and plan simultanously you can use following view:

create view PlanCache

as

select sp.* from sys.dm_exec_cached_plans   as cp

cross apply SqlAndPlan(cp.plan_handle) as sp

No Comments

Add a Comment
 

 

Query Plan In Ms Sql

 

Hello everybody,

some MS SQL internals.

Imagine, you want to see what MS SQL cached. How you can do it? One of the ways is to look into sys.dm_exec_cached_plans. 

For example like this:

select * from sys.dm_exec_cached_plans

this code will return to you all what is cached in your MS-SQL database. Most probably result will puzzle you. What to do with all of it?

Take look at the screenshot :

the area of interest for us is column plan_handle. 

You can use it for another sql request. Like this:

select * from sys.dm_exec_query_plan(0x06000C002D51D1304021F8AE000000000000000000000000);

if you click at column with xml you'll see the following picture:

How to interpret those results? I hope to describe in one of other notes on my blog.

For now another question, how to see the text of cached value? 

You can do it with another sql 

for example like this:

select * from sys.dm_exec_sql_text(0x06000C002D51D1304021F8AE000000000000000000000000)

in the column text you'll see column text which will represent what MS SQL cached. In my case it is following text:

select [first name], [last name], dense_rank() over (order by [last name]) as rn from employees

No Comments

Add a Comment
 

 

Sql Formatting Tool

 

Few days ago I faced following challenge. SQL profiler gave me ugly SQL, which was unreadable for my eyes.

For example like this:

exec sp_executesql N'SELECT APAddress.AddressID, APAddress.VendorID, APAddress.VendorAddressID, APAddress.IsDefaultAddress, APAddress.RevisionID, APAddress.AddressLine1, APAddress.AddressLine2, APAddress.AddressLine3, APAddress.City, APAddress.CountryID, APAddress.State, APAddress.PostalCode, APAddress.IsValidated, APAddress.tstamp, APAddress.CreatedByID, APAddress.CreatedByScreenID, APAddress.CreatedDateTime, APAddress.LastModifiedByID, APAddress.LastModifiedByScreenID, APAddress.LastModifiedDateTime FROM APAddress APAddress WHERE (APAddress.CompanyID = 2) AND  ( APAddress.AddressID = @P0) ORDER BY APAddress.AddressID /* admin@AP.30.40.00 */',N'@P0 int',@P0=-2147483647

If you can read it, congratulations, you are genius and I'm envy of you so you can stop reading further. But if you are just programmer as me, then the next step is the following:

1. remove exec sp_executesql 

2. In some editor find and replace @P0 with value -2147483647

3. remove tail /* admin@AP.30.40.00 */',N'@P0 int',@P0=-2147483647

4. Navigate in your browser to http://www.dpriver.com/pp/sqlformat.htm

5. Copy/Paste in window and choose format mssql

6. Press format SQL.

Compare previous sql with the following:

SELECT apaddress.addressid,
       apaddress.vendorid,
       apaddress.vendoraddressid,
       apaddress.isdefaultaddress,
       apaddress.revisionid,
       apaddress.addressline1,
       apaddress.addressline2,
       apaddress.addressline3,
       apaddress.city,
       apaddress.countryid,
       apaddress.state,
       apaddress.postalcode,
       apaddress.isvalidated,
       apaddress.tstamp,
       apaddress.createdbyid,
       apaddress.createdbyscreenid,
       apaddress.createddatetime,
       apaddress.lastmodifiedbyid,
       apaddress.lastmodifiedbyscreenid,
       apaddress.lastmodifieddatetime
FROM   apaddress APAddress
WHERE  ( apaddress.companyid = 2 )
       AND ( apaddress.addressid = -2147483647 )
ORDER  BY apaddress.addressid 

IMHO the second option is easier to read.

4 Comments

  • Tim Rodman said

    Nice tip. I didn't know about that website.

    I use the SQL Prompt plug-in from Red Gate which installs as a menu in SQL Management Studio. You can also format SQL using this tool.

  • docotor said

    Nice tool. IMHO even better then web site. But according to http://www.red-gate.com/products/sql-development/sql-prompt/

    "SQL Prompt starts at $369 per user" For now I'm not ready to pay to redgate such amount

  • Tim Rodman said

    I agree, it's a little pricey, especially if you can't get your company to pay for it.

    It does save time when writing code because it auto-completes columns, tables, even table joins for you. But, if you're just looking for a formatting tool, the website works great.

  • anonymous said

    Freeware:
    Notepad++ has an add-in called "Poor Man's Sql Formatter"

    http://www.architectshack.com/PoorMansTSqlFormatter.ashx

Add a Comment