SBX - Search With Button

SBX - Forum Post Title

SQL query used by navision

Microsoft Dynamics NAV Forum

Vishwas Kumar asked a question on 12 Oct 2017 4:15 AM

Question Status

Verified

Dear All,

I need to know about which SQL queries mostly used for navision to solved by problem regarding table and pages.

Reply
Zaid Tariq responded on 12 Oct 2017 4:19 AM
My Badges

Hi,

You mean the common queries used to solve common problems of NAV? Would be great if you can point out a specific problem.

Reply
Vishwas Kumar responded on 12 Oct 2017 4:31 AM

yes , provide all common sql queries name to solve common problem of NAV

Reply
Zaid Tariq responded on 12 Oct 2017 6:25 AM
My Badges
Verified Answer

Tip:

- To delete the partner range table you can delete them from the dbo.object table

- To remove all the permissions from a NAV database you can use these commands:

delete from [dbo].[User]
delete from [dbo].[Access Control]
delete from [dbo].[User Property]
delete from [dbo].[Page Data Personalization]
delete from [dbo].[User Default Style Sheet]
delete from [dbo].[User Metadata]
delete from [dbo].[User Personalization]
DELETE FROM [dbo].[Windows Login]
DELETE FROM [dbo].[Windows Access Control]

Will share more with you if found. Thanks

Reply
Suresh Kulla responded on 12 Oct 2017 9:49 AM
Suggested Answer

What problems are you referring ? Give some examples ? and why do you need SQL Queries ? Mostly all of the issues need to be solved by making changes in NAV but not running SQL queries.

Reply
Vishwas Kumar responded on 12 Oct 2017 11:26 PM

provide, which Sql queries used by professionals to handle the NAV despite of delete.

Reply
Manish Sharma responded on 12 Oct 2017 11:58 PM
My Badges

SQL is not the common problem solver for NAV. It is the background which stores data, for some specific issues you need to go to SQL.

Reply
Rubén Mejía responded on 11 Jan 2018 11:42 AM
My Badges

Hello,

The most common, is the one pointed by Zaid Tariq, and it is mostly used to grant access to any database when, for instance, you want to restore a customer's database on a local consultant's PC, then, as normally the customer´s db will have permissions for certain users, then, if you do not perform that deletion, you will not be able to even create a Server Instance in order to connect to the db with the application.

However, I might say that for that purpose I use the foloowing:

USE [DatabaseName];

TRUNCATE TABLE "User Personalization";

TRUNCATE TABLE "User Property";

TRUNCATE TABLE "Access Control";

TRUNCATE TABLE "User";

GO

And, as others has said on previous answers, to use SQL to solve problems is no the recommended way, but it is a great way of review the data. Or, depending on your needs, update some of it. I have use it very carefuly and with success to work on filling related tables, lists, charts, and so on. But never, NEVER, use it to modify records that were posted, or that just can be posted in the application using a process, because, this will cause several problems and malfunctions to your database.

Anyway, If you need to review, copy, analyze, or even update any information, I share with you this site, where I found and use many SQL commands, with good examples of how-to-use.

https://www.w3schools.com/sql/default.asp

Hope the information be useful.

Regards.

Reply
Zaid Tariq responded on 12 Oct 2017 6:25 AM
My Badges
Verified Answer

Tip:

- To delete the partner range table you can delete them from the dbo.object table

- To remove all the permissions from a NAV database you can use these commands:

delete from [dbo].[User]
delete from [dbo].[Access Control]
delete from [dbo].[User Property]
delete from [dbo].[Page Data Personalization]
delete from [dbo].[User Default Style Sheet]
delete from [dbo].[User Metadata]
delete from [dbo].[User Personalization]
DELETE FROM [dbo].[Windows Login]
DELETE FROM [dbo].[Windows Access Control]

Will share more with you if found. Thanks

Reply
Suresh Kulla responded on 12 Oct 2017 9:49 AM
Suggested Answer

What problems are you referring ? Give some examples ? and why do you need SQL Queries ? Mostly all of the issues need to be solved by making changes in NAV but not running SQL queries.

Reply

SBX - Two Col Forum

SBX - Migrated JS