How to find sizes of tables in Acumatica

Hello everybody,

today I want to share with you SQL statement that may help you to find sizes of tables occupied by Acumatica. It goes below:

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00 / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceGB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    --t.Name
	TotalSpaceMB desc, t.Name

with that sql info you can check and see if you need to split some data or clean it

Operator IN in BQL

Hello everybody,

today I want to write a few words about operator in which was presented in SQL for long ago, but weren't available in Acumatica BQL. But time goes on and now you can use it. For example like this:

Object[] values = new String[] { "BXW000004""BXW000005" };
 
                POOrder item = PXSelect<POOrder,
                    Where<POOrder.orderNbrIn<Required<POOrder.orderNbr>>>>.Select(Base, values);

that code will generate following sql statement:

Select * from POOrder POOrder Where POOrder.OrderNbr In ('BXW000005', 'BXW000004')
	Order by POOrder.OrderNbr

I can say that such approach simplifies some tasks that require dynamic passing of arguments.

How to add sitemap into your customization

Hello everybody,

today I want to describe some simple steps of how to add into your customizaiton sitemap. As you already know, sitemap of Acumatica is saved in database. And if you make customization of your sitemap you may need to add to your project.xml something like this:

<Customization level="0" description="" product-version="5.30">
    <SiteMapNode>
        <data-set>
            <relations version="3" main-table="SiteMap" />
            <layout>
                <table name="SiteMap" />
            </layout>
            <data>
                <SiteMap>
					<row Position="72" Title="Warehouse" 
Url="~/Frames/Default.aspx" Expanded="0" IsFolder="0" 
ScreenID="TC000000" 
NodeID="95A4548D-CA01-4485-992F-FADE1597DC95" ParentID="00000000-0000-0000-0000-000000000000" /> <row Position="1" Title="Producing" Url="" Expanded="0" IsFolder="0" ScreenID="" 
NodeID="42C9E271-EC9D-483E-9A8F-2912E3E7C2ED" ParentID="95A4548D-CA01-4485-992F-FADE1597DC95" /> <row Position="2" Title="Food Administratio" Url="" Expanded="0" IsFolder="0" ScreenID="" NodeID="D9CA98E4-73CC-4DEB-BFED-35EFFD72274C" 
ParentID="95A4548D-CA01-4485-992F-FADE1597DC95" />         <!-- Pages  --> <row Position="1" Title="Management of materials" Url="~/Pages/TC/TC100000.aspx" Expanded="0" IsFolder="0" ScreenID="TC100000" 
NodeID="4083D0D7-3FC1-412F-A3FA-E7D730F3EBFA" ParentID="42C9E271-EC9D-483E-9A8F-2912E3E7C2ED" />                 </SiteMap>             </data>         </data-set>     </SiteMapNode>

For now I'd like to talk only about sitemap node. After I created sitemap node once, soon I got request to modify it and for me it was a bit painful from standpoint of regenerating of xml nodes.

So I decided to write some simple sql statement that allows me to to create sitemap manually in Acumatica, and then read it from db in convenient way for me. 

Below goes that SQL:

select '<row Position="'+ CAST(s.Position as nvarchar(MAX)) +'" Title="' + s.Title  
	+ '"'
	 + ' Url="'  + COALESCE(s.Url, '' ) + '"'
	+ ' Expanded="' + CAST(s.Expanded as nvarchar(1)) + '"'
	+ ' IsFolder="' + CAST(s.IsFolder as nvarchar(1)) + '"'
	+ ' ScreenID="' + COALESCE(s.ScreenID, '') + '"' 
	+ ' NodeID="' + CAST(s.NodeID as nvarchar(MAX)) + '"'
	+ ' ParentID="' + CAST(s.ParentID as nvarchar(MAX)) + '"'
	+ ' Description="' + COALESCE(s.Description, '') + '"' + ' />' 
	as SiteMapRow
	from SiteMap s where CreatedDateTime > '2017-09-12 12:00:00' order by Position

with that SQL you can simply copy/paste generated result into your customization

How to make yourself as Administrator in Acumatica with SQL

Hello everybody,

today I want to share some piece of SQL code that can help you to add yourself to Administrators group in Acumatica:

INSERT INTO [dbo].[UsersInRoles]
           ([CompanyID]
         ,[Username]
         ,[Rolename]
         ,[ApplicationName]
         ,[CompanyMask]
         ,[CreatedByID]
         ,[CreatedByScreenID]
         ,[CreatedDateTime]
         ,[LastModifiedByID]
         ,[LastModifiedByScreenID]
         ,[LastModifiedDateTime])
   VALUES
           (2
         ,'yourAccountName'
         ,'Administrator'
         ,'/'
         ,0xAA
         ,'B5344897-037E-4D58-B5C3-1BDFD0F47BF9'
         ,'00000000'
         ,'2010-07-27 17:25:00.000'
         ,'B5344897-037E-4D58-B5C3-1BDFD0F47BF9'
         ,'00000000'
         ,'2010-07-27 17:25:00.000')

With this sql you can make yourself as administrator in Acumatica instance.

Also if you wish you can reset password of admin with following sql:

UPDATE Users SET Password = 'abc', LockedOutDate =null WHERE Username = 'admin'

and log in into Acumatica. There you can add to your account additional permissions

Create new column if it doesn't exist

Hello everybody,

today I just want to preserve simple sql which creates column, if it is not created yet in ms sql db. Important feature of this code is indempotency.

IF Col_length('APPayment', 'usrPOSkipped') IS NULL
  BEGIN
      PRINT '[usrPOAllocated] will be created'

      ALTER TABLE appayment
        ADD usrposkipped BIT
  END
ELSE
  BEGIN
      PRINT '[usrPOAllocated] already exists'
  END 

Few explanations. This code will create column usrPOSkipped in table APPayment.

Saving changes are not permitted. Because the changes you have made require the listed tables to be dropped and re-created

Small hack.

If you modify table in Microsoft SQL Server Management Studio, it sometimes says something like 

Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be recreated or enabled the option Prevent saving changes that require the table to be re-created. 

Do you know where mentioned option is hidden?

Tools -> Options -> Designers -> Table and DAtabase Designers. Find there checkbox Prevent saving changes that require table re-creation, remove checkbox and enjoy results.

Get all columns of specific table

Today I needed to get all columns of specifict table. 

SQL which you can use for this purpose is the following:

SELECT COLUMN_NAME 'All_Columns' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='Specific table'