Execute SQL Query in nav 2013

jimmyf

Hi
I am aware about the new Query object in Nav 2013 however is it possible to run a sql query directly in nav 2013?
Or use a sql query to as the basis of a nav 2013 report?

ara3n

No not directly. You will need to use ADO if you want to directly execute a query.

MarkHamblin

I would use the 2013 query objects wherever possible. However, if you do want to run SQL directly, we have a nice little codeunit to do it (which I just ported to 2013). I'll post the code to downloads, but for now get it from http://www.dynms.com/tools/NAV2013_SQL_Query.txt - UPDATE: files now at http://mibuso.com/dlinfo.asp?FileID=1510

It allows you to populate any temporary table with the results of an SQL statement. Usage (can be seen in test page) is:

cuSQLQry.initialize(txtDBServer, txtDBName);
RecRef.GETTABLE(Rec);
cuSQLQry.getRecordFromSQL(txtSQL, Recref, FALSE);

Where "Rec" is any table (fun fact - you can create tables outside of the customer's purchased table range and use them if you only use them for temporary data - this means you can create your own data structures to fill up with queries, etc.).

You could run this codeunit from a report and use the temporary table to drive the report data.

See the documentation section in the codeunit for more usage details. (Also, this codeunit only does a few data types - you could check the universal XMLport for code to evaluate other data types).

jimmyf

thanks for this Mark
do you think that it would work with a sql view rather than a table?
I know I can front a sql view with a nav table but that would consume one of the customers table objects.

MarkHamblin

jimmyf wrote:

thanks for this Mark
do you think that it would work with a sql view rather than a table?
I know I can front a sql view with a nav table but that would consume one of the customers table objects.

If you use the linked table functionality in NAV, I think you're stuck using one of the customer's tables. If you use an SQL statement, you can populate a temp table of your design (outside the customer's table range) that won't consume a customer table. There are definitely pros and cons to each approach - if you're avoiding the view just to save a table object, it might not be worth the extra effort and config vs. the cost of one table. The SQL approach wins if it's the type of query that the NAV query objects are not suited to (complex joins, querying data from multiple companies, etc.)

(You should also be aware that if you do use a linked table and want to backup and restore the DB using the client backup, the table will have to exist in whatever SQL database you restore the backup into.)

kriki

[Topic moved from 'Navision Financials' forum to 'NAV Three Tier' forum]