Replace C/AL code with Transact-SQL?

pdj

I was just browsing a SQL-NAV course and noticed this sentence:

...After completing this course, students should be able to:
...
* Replace inefficient C/AL code with highly efficient Transact-SQL commands executed directly on SQL Server via the automation server functionality in NAV
...

Source: http://download.microsoft.com/download/8/d/0/8d0a3a30-e0af-46b5-b17e-b6fd250b847f/NAV_p%C3%A5_SQL_05_06_07%20-%20ny.pdf

Could anyone please explain when and how this is to be used, or a link to more information? (Besides taking the course ;-))

kine

Try to search for ADO on this forum... ADO allows you to connect to any MS SQL and execute SQL commands...

pdj

Hi Kine, thanks. That was also my first thought, but have you (or anybody else) ever done it in a live environment instead of using C/AL when it is the same db? I would think the overhead of establishing a new connection and working with a dataset would eat the benefit of using Transact-SQL instead of C/AL. Well, maybe it's time for a test :-)

pdj

Seems to be quite simple if the db is using NT authentication.
I just adjusted the example by Miklos at http://www.mibuso.com/dlinfo.asp?FileID=589

ADOConnection.ConnectionString(
  STRSUBSTNO(
    'Provider=SQLOLEDB;Data Source=%1;Initial Catalog=%2;Integrated Security=SSPI;',
    GetContextUrlPart('servername'),GetContextUrlPart('database')));
...
GetContextUrlPart(Param : Text[50]) : Text[100]
TmpText := COPYSTR(CONTEXTURL,STRPOS(CONTEXTURL,Param + '=') + STRLEN(Param) + 1);
EXIT(COPYSTR(TmpText,1,STRPOS(TmpText,'&') - 1));

Now I just need a situation where it would be a good idea to use it :-)

pdj

pdj wrote:

Now I just need a situation where it would be a good idea to use it :-)

Now I have found such a situation,, but I would like to hear if anybody has used the above method without problems in a live database? The questions is both about using my code above, but also if anyone has experience using an ADO connection to the same DB as the NAV client or NAS is using.

DenSter

I've used ADO for an integration with a warehouse management system, and I was very happy with the result.

Forget about the part where it says 'replace C/AL code with T-SQL', that is not something you want to do, unless you are going to hire an absolute NAV expert AND an absolute SQL expert, both with not too much ego to work together (which seems to be near impossible). It is just not a good idea to run NAV business logic in T-SQL.

Use ADO to interface and move data back and forth to staging tables, and have a NAV component process this data into the core NAV system. That is the best way to ensure data integrity.

ara3n

On my last project we had to use ADO.
We were dealing with millions of records, and calculation those records with NAV wasn't possible. Well it would be possible but would take hours to run the routine,
and they would have 5-10 people running this daily.

So a combination of T-Sql and C/AL made it possible.

pdj

ara3n wrote:

So a combination of T-Sql and C/AL made it possible.

Just to clarify. You are using ADO to the NAV database that your ADO C/AL code is executing, right? I'm mainly worried if there could be any conflicts in having multiple connections to the same DB.
And out of even more curiosity: Are you using the above code to find the servername and db?

DenSter:
I understand your concern, but for pure reading I don't see any risk. As long as it is controlled I think even inserting and updating in NAV tables is ok. It is not much worse than C/AL code that inserts, modifies or deletes without executing table triggers.

ara3n

pdj wrote:

ara3n wrote:

So a combination of T-Sql and C/AL made it possible.

Just to clarify. You are using ADO to the NAV database that your ADO C/AL code is executing, right? I'm mainly worried if there could be any conflicts in having multiple connections to the same DB.
And out of even more curiosity: Are you using the above code to find the servername and db?

The servername and user name are part of Setup in a setup table. There is no conflict in having multiple connection. Also They can change the setup and query a different server and database for performance purposes.

pdj

ara3n wrote:

There is no conflict in having multiple connection.

Great thanks.

ara3n wrote:

The servername and user name are part of Setup in a setup table.

I used to do this as well, but after my customer once made a restore of a live DB to test DB and forgot to change it, well, I'll spare you the detail just say I won't do it again

ara3n

The Process looks at millions of historical transactions displays the calculated data in the batch for them to look. Won't make that much of difference.
You could add some code to check the setup to the virtual tables for server info etc. But it doesn't make much of difference.

Fommo

I don't know if this should be in a separate thread, but since the discussion seems to fit already I put it here.

I'm doing an integration between a Navision system running on Native DB (3.70) and another system running on SQL Server 2005.
Everything works fine and runs as fast as the wind, except for those times when my T-SQL execution gives an error. I can't find any way to fetch that error in Navision.

I'm running this code:

ASResults := ASDatabase.ExecuteWithResultsAndMessages2(Command, ResultMsg, STRLEN(Command));

Where ASDatabase is an automation of 'Microsoft SQLDMO Object Library'.Database2.
In the help description for the execute method it says that messages from the run should be put into ResultMsg, but still I get an runtime error dialog that stops the program.

Does anyone know if it's possible to catch errors from the execution (for example the run of a stored procedure) and handle it without stopping the program run?

ara3n

You have to write a com wrapper that will catch the error.
You can't catch a COM exception in navision.

Fommo

ara3n wrote:

You have to write a com wrapper that will catch the error.
You can't catch a COM exception in navision.

Is it easy to do that for only my execute method?
I'm not so experienced in writing COM, I've just started to use that cool automation server technology in NAV.
I do know .Net though so I guess I have to write such a COM wrapper in VS, right?