Tag Archives: SQL

My post on Dynamics NAV 2013 and data in tables attached to SQL views not reflecting latest data changes.

Check out my post over at ABC Computers:

http://www.abc-computers.com/news-events/blog/is-a-dynamics-nav-2013-page-based-on-a-sql-view-not-updating-to-reflect-the-latest-data-changes-heres-a-solution/

SQL Reporting Services and Dynamics NAV Option Strings: Part 2

In Part 1 we created a table and a processing report in Dynamics NAV that would enable us to later access every Dynamics NAV option string value in a SQL Reporting Services report.  Now, I will show how I handled the query to get option string values into an SSRS report.  This is a little less complex than Part 1. :)

With all the steps of Part 1 complete, we now need a simple and consistent way to query for those values.  I solved this problem by created a User Defined Function in my database.

Which looks like:

CREATE FUNCTION [dbo].[OptionString] (@TableName AS NVARCHAR(30), @FieldName AS NVARCHAR(30), @FieldInteger AS INT)
RETURNS NVARCHAR(250) AS
BEGIN
RETURN (
SELECT [OptionString]
FROM [CRONUS USA, Inc_$Option Strings]
WHERE [TableName] = @TableName AND [FieldName] = @FieldName AND [FieldInteger] = @FieldInteger
)
END

Each time I want my SSRS report to return the option text value instead of the integer value, I insert this line into my query:

dbo.OptionString(‘Sales Header’,’Document Type’,[Document Type]) AS ‘Document Type Option String’

I’m passing into the UDF the table, column name (the first and second parameters and should be known if you’re writing the report) and the query itself is supplying the integer value which is required to return the option value string from the table we created in Part 1.

I’ve provided a SQL query in the zip file below that queries table 36, the Sales Header table.  The UDF is also included as well as the SSRS RDL.  The query can go directly into an SSRS report resulting in the picture below if everything has been put together correctly:

Screenshot12_5_20092_34_03PM.png

You now have a simple and consistent way to generate and query for NAV option string values.   Hope this helps someone!

Files:

Option Strings – SQL – BW1.00.zip

NAV Default Database File Locations

I’m frequently creating new databases in NAV.  One annoyance was NAV’s insistence on the default file location.  In my dev environment this was always where I had installed SQL.

Capture

Not where I want my databases. 

I spent some time investigating and after watching file system and registry access on both server and client I wasn’t able to see it grab any relevant values from those spots.  From what I can tell after watching packets between client and server with a packet sniffer, the NAV client is parsing the file location from the sp_helpfile stored procedure.  The solution?  NAV is essentially following the Master database. 

How to move system databases in SQL Server:
http://msdn.microsoft.com/en-us/library/ms345408.aspx

Just move the Master database to the spot you’d like the default file locations. Now when I specify Database Files… Yay! 

Capture2

The only downside is that is also will want to put the Transaction Log Files in the same file path.  Small price to pay. :)

Capture3

Does anyone else have a better way of doing this?  It would at least be nice if NAV could somehow use the default file locations specified within SQL.