«

Print this Post

Stored Procedures on a NAV database?

Stored Procedures on a NAV database?
2 votes, 5.00 avg. rating (97% score)

Recently, I came across a customer, where a partner had implemented some Stored Procedures on a Microsoft Dynamics NAV 2017 database.

Now, for any change on SQL level that comes to my ears, my first reaction is always “Nooooooooo“. Or as a pictures tells more than a thousand words:

First thing I did was googling to see if the community shared my opinion. Apparently there is nothing to find about recommendations on this: nor good nor bad. I did find a few blogposts on how to call SPs from NAV (even from my own ;-)) .. . Well – time for me to start preaching about the disadvantages :-).

My recommendation is quite simple: don’t do any manual changes in SQL Server on a NAV database. And I mean ANY change. Like:

  • Altering indexes
  • Stored Procedures
  • Views
  • Table triggers

Well, may be not “any” change .. I’m not talking about maintenance jobs, of course – you have to maintain a NAV database like any other.

But let’s focus on Stored Procedures (although a lot of my arguments could count for any change on SQL). And yes, I expect lots of people not agreeing with this post. Before you do .. try to see it from my point of view: being responsible for +300 customer databases in a variety of NAV versions.. . “Repeatability” comes to mind .. . “No exotic, strange, unsupportable, … solutions” as well.

So, why don’t you like Stored Procedures on NAV databases?

For me, a solution based on Stored Procedures smells a lot like “I just do it like that because I can”. It’s the combination with NAV that doesn’t make it really interesting if you ask me. As you know, a lot in SQL Server is managed by the NAV environment, like:

  • Creation of indexes
  • VSIFT views
  • Creating/changing Tables
  • Creating/changing Fields

you doing some manual changes on top of that, means that it is NOT managed by NAV, which might result that (1) your change might just disappear because it’s overwritten by NAV, or (2) your change results in a conflict (error) with the NAV system.

Schema Sync:

Remember the PowerShell-command “Sync-NAVTenant” that is needed sometimes to sync the metadata with the SQL Schema. A Sync. Between two things: a metadata-table and the actual schema on SQL. Any manual change that disturbs the two might end up in a complete “un-ability” to ever synch the schema again. A “disturbance in the force” is easily and unintentionally done. To fix things like this can be very time-consuming. Ok, may be nothing to do with Stored Procedures – but something to think about nevertheless..

Separation of business logic

Any software doesn’t need to be just “working”. Things like “upgradability”, “maintainability”, “readability”, … are equally important! When you are introducing Stored Procedures as being part of your solution, you definitely are NOT making it more readable, maintainable, .. .

Just imagine that in a few years, when you’re enjoying your pension, other people have to extend your solution .. with parts in SPs, and parts in C/SIDE. You really think they are going to figure out that cryptic T-SQL? As business logic. Or part of it?

NST Cache

One of the biggest reason to NOT use Stored Procedures. The two things you do most when calling an SP: read and/or write.

When you just want to read data – just do it with queries and C/SIDE? You’ll use the NST cache, which even might speed up the process as well.. .

When you want to write data – then you actually should just go on a pension right away – you don’t belong to work on NAV databases. Sorry, but that is my opinion. People lose fingers over this in my company ;-).

Writing on SQL server without going through the NST means that everything you will touch, will not update the cache, even more your cache will be out of sync, resulting into showing different data in NAV than the actual data in the tables. Things like this lead to a very unstable and unreliable system and should be avoided by all times.

No Flowfields

You won’t have access to flowfields because these are calculated by the service tier. I have seen people “simulating” the business logic in T-SQL that results in the same result – but then again – what if I change the “business logic” (i.e. calculation) of the flowfield? What if Microsoft updates it after an upgrade?

Should I say more? Duplicating code or business logic is like code cloning. Don’t do it! It’s paving the road to disaster!

Enough alternatives

There are indeed enough alternatives! We can use queries in combination with ODATA web services to efficiently read data on SQL Server. We can use Web Services to call for business logic from 3rd party applications. We can even call a codeunit from PowerShell! There are many types of decent integrations – so please, always consider one that doesn’t require a change on the SQL Server database!

Only when it’s absolutely necessary AND when you know 100% what you’re doing

I rarely see a situation where there is no way around. Performance can be a reason. Like I have been truncating a table because a DELETEALL just took too long. But I see lots of situations where SPs might have been avoided, and were considered a good solution “because he/she was not familiar with the concept of web services”.

All I can say is: make sure you know what you’re doing. And if you do go ahead with it, make sure you document it to all parties that are involved with the database – because they might not understand why you’re doing it the way you’re doing it.. or even not realize that you did.

Conclusion

In my 15 years in the world of NAV-craft, I can only think of two examples where I have actually implemented something straight onto SQL Server. One of which was because there were no web services just yet.

The first reaction I have when I see solutions with Stored Procedures is “that must be ‘Job Protection'”, because usually, there is a much more elegant solution available. Please don’t make any solution even more complex as it already is – unless there is really no other way .. .

Disclaimer

Needless to say that this blogpost is completely and only based on my own opinion.

Permanent link to this article: http://www.waldo.be/2017/04/11/stored-procedures-on-a-nav-database/

7 comments

Skip to comment form

  1. Guido robben

    To delete the users from a database during conversion from 2009 to 2015…

    1. waldo

      All fine with conversions – the chance you test the conversion is quite high, and you probably will restart NST a few times as well ;-).

  2. Mark Brummel

    I know people use it for costing but I can say that this is not nessesairy. Recently I’ve updated that C/AL code for a super large customer to run paralel with their 24/7 business. There is no reason to run code on SQL, especially now that we have smart queries.

    1. waldo

      Agree!

  3. Alain Krikilion

    For indexes, I completely agree. For sp, I agree as a rule. But sometimes you need to break rules to get somewhere. Like bringing down a 36 hours process to less than 1 hour. I wrote to a dedicated table that was than read by NAV after I did a SELECTLATESTVERSION.

    With some changes, you can run the costing 24/7 without locking all for normal 24/7 business. Did that but costed me some time but now it is working as a charm. Ps: I also killed job queue. Still not working well. Using SQL Server Agent+PowerShell to call a webservice that does the job!

    1. waldo

      Well .. I assume you know what you’re doing 😉

  4. Kevin

    Fully agree (trying to maintain a NAV database full of SP’s here, originally built in NAV4 to introduce webservices, and it’s difficult), except for the upgradeability and the elegance. First of all, I don’t share the opinion that SQL is less maintainable than NAV, especially when you start to implement custom .NET DLLs etcetera. In the last case, it’s possible to lose the source and introduce the need for a decompiler… thát is what I’d call not elegant.
    Second, yes, SP’s in SQL aren’t needed anymore for NAV. But wouldn’t it be much more elegant if Microsoft had just made the link between SQL and NAV better? I think it would be truly elegant if the NST would also be triggered if a change was made on SQL level.

Leave a Reply

%d bloggers like this: