Solved

sql select statement at user level

  • 14 January 2022
  • 9 replies
  • 254 views

Userlevel 1
Badge

Hello,

we are at evaluating Acumatica cloud subscription stage:

currently we have users who write/run sql select statement or view from SQL sever studio, server is on premise.

if migrate to AWS, will this be a concern for us and any additional cost we need to consider?

Z

icon

Best answer by DanielStarke 28 January 2022, 22:17

View original

9 replies

Userlevel 7
Badge +11

Hi @zoeymuff 

Access to SQL database using SQL management studio is not provided on Saas environments. You can use Generic inquiry functionality in Acumatica. No direct access to database will be provided either by using SQL management studio or ODBC connections. You can refer to our FAQ page : https://www.acumatica.com/acumatica-saas-faq/

Hope this clarifies,

Regards,

Userlevel 1
Badge

Thank you VKumar,

will we be able to get a database diagram.. or simply list of table and column/field names?

. it is very helpful to filter specific field existing in which table, and their relationship

 

-Zoey

 

Userlevel 7
Badge +17

Hi, @zoeymuff  You wanted to know the table/field names of existing Acumatica tables?

If yes, you can create a new instance on your local machine and get the table/fields from SQL Server.

 

Userlevel 1
Badge

thanks Naveen,

so if acumatica is hosted in AWS,

i can connect to aws database from local app SSMS (microsoft SQL server management studio) to run select statement?

Kumar up there just mentioned that SAAS environment does not allow us connect to aws database. i am totally confused

-z

 

 

Userlevel 7
Badge +11

Hi @zoeymuff 

You are correct. One can connect to AWS database using SSMS from your local system. However, on our Saas environment this functionality is not enabled as the SQL instance could have multiple Acumatica instance databases of different Saas clients.

With regards to database diagram, refer to DAC reference documentation guide. Here is the link:

https://help-2021r2.acumatica.com/(W(3))/Help?ScreenId=ShowWiki&pageid=177d968e-53c0-3d58-e93c-b8a55936635a

You can also request and get SQL database copy from production instance if needed. 

Hope this helps,

Regards,

Userlevel 1
Badge

Thank you VKumar

i just need to know if we could access our database in AWS via local ssms to write and run select statement..i.e below:

 

i am not IT scientific mind, don;t understand what you meant by “on our Saas environment this functionality is not enabled”.. 

we only care if we can write select statement by accessing to the database on AWS via local ssms and write our own report.

 

it is very important for us to know database diagram with table and columns.. ie. image2 below: say if i need to know all the tables have the attribute “GL_ACCOUNT_ID”, I can quickly narrow down to the table i need to get my information.

 

 

 

 

 

Userlevel 4
Badge

@zoeymuff - no, what you are wanting in the way of ssms access is not functionally possible. A local instance, as is mentioned above is not practical for live deployment, only testing. 

 

If your users have the ability to write sql, they'll need to reapply thier minds and learn how to make Generic inquires and business events, import scenarios... With these three tools learnt your team will be able to accomplish most of what historically u would do in ssms

Userlevel 1
Badge

Thank you Daniel, 

I did further research on the Generic inquiry and DACs as suggested by VKumar.

best

Zoey

Userlevel 1
Badge

DanielStarke @vkumar  @Naveen B 

related question: so how can we obtain a copy of our database periodically?

thanks 

Zoey

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved