I am happy to announce Outlook Integration Update for Microsoft Dynamics NAV 5.0. This update is now available for you to download at PartnerSource login credentials are required off course 
.
The Outlook Integration update rollup corrects the issues that are described in KB article number 945879. Apply the Outlook Integration update, following recommendations in the Installation & Setup Whitepaper included in the package, only to systems that are experiencing these specific problems. This update applies to Microsoft Dynamics NAV for all countries and all language locales.
From the link above Microsoft has released:
- Outlook Add in installation program + set of corrected C/AL objects;
- Supplementary installation program, to deploy corrected dlls used by the C/AL objects;
- Installation & Setup Whitepaper (57 pages
!).
It is not too late ... but still late. I haven't said a word about Directions EMEA 2008 yet, while it has been announced already quite a long time.
Directions EMEA 2008 is a dedicated Microsoft Dynamics NAV event. No CRM. No Axapta. Only the best ERP product around: NAV 
. Furthermore, it's a partner-only event.
As you could already read in some of my previous blogposts, I have been to quite a few conventions already. Directions US last year in November was one of them. This was the US version of the Directions EMEA (EMEA = Europe, Middle-East and Afrika). It was one of the best conventions I attended ... and one of the cheapest . There were many things that I really liked about it:
- Many NAV addicts
- Many very good ISV add-ons
- Valuable real life information
- Shared experiences from other partners
- ...
It comes down to the fact that everyone that attended was a NAV believer, who eats, breaths and even sh**s NAV. It makes networking so easy.
And now there are some enthousiasts that want to organise the same event in the same way with the same focus in Europe. I think it's great and I really would like to motivate all of you to come over and feel what the NAV community is all about.
Thanks to Natalie, I added two more updates on 5.0. This is the new overview.
Recently, I had to do a small project. A client of us wanted to see the global inventory of all its companies. Furthermore, the companies were also spread over multiple databases. They wanted quick answers on questions like "What is the inventory of this item in any location in any company in any database". Now, I'm not going to tell you how I solved the case. I just wanted to highlight some interesting stuff that I used.
Some basics about using ADO in NAV.
ADO (ActiveX Data Objects) is described as "ADO is designed to provide a consistent way of accessing data regardless of how the data are structured. ". It's actually automation that you can use (should be installed together with windows - but I'm not sure) to access about any kind of database. It's generally used by developers in C/SIDE to access data in another database then the current database.
It's basically 3 steps:
-
Make a connection to the database, using a "connectionstring"
http://www.connectionstrings.com/ is a very helpful website to help you compose your connectionstring. Usually, I create a seperate setup table with the parameters needed to create a connectionstring.
In C/SIDE, you can create a connection like this:
IF ISCLEAR(lADOConnection) THEN CREATE(lADOConnection);
lADOConnection.ConnectionString:=GetConnectionString(precDBServer."Server Name", precDBServer."Database Name", precDBServer.Login, precDBServer.Password);
lADOConnection.Open;
LADOConnection is an automation variable of the type "'Microsoft ActiveX Data Objects 2.8 Library'.Connection". You also see that I compose my connectionstring in a seperate function:
GetConnectionString(...) : Text[1024]
ltxtConnectionString:='Driver={SQL Server};'
+ 'Server='+pcodServerName+';'
+ 'Database='+pcodDatabaseName+';'
+ 'Uid='+pcodUserID+';'
+ 'Pwd='+pcodPassword+';';
EXIT(ltxtConnectionString);
-
Do your thing in the database (read, write, loop data, call SP, grant permissions,...) ... usually using SQL commands.
We will go into this deeper further down the road.
-
Close your connection
This is how I close my connection:
lADOConnection.Close;
CLEAR(lADOConnection);
If you're a dedicated C/SIDE developer, you see that this is not usual. Creating a connection? Closing a connection?? In C/SIDE, you're already connected by just declaring a record variable - by sorts of speaking. That's because the native client connects with the database before you can start working - and therefore, you're always connected . It's logical, I know, but anyway ...
What could it be useful for?
So, what can we do in for example in the database. Well. A number of things. From the top of my head:
- Creating company-specific views when a new company is created
- Create data in a queue table
- Compose a SQL String, execute that SQL String and givie back the results ... .
- ...
It all comes down on executing a stored procedure in a SQL Server database. If you can do that, then the possibilities is limited to what you can do with Stored Procedures. And there is not much limitation to that . About everything you can do in T-SQL, you can do with SP's!
So, let me try to explain all this by using a few examples:
-
Creating company-specific views when a new company is created
First of all, I created a stored procedure in SSMS (SQL Server Management Studio) like this (I added some comments to make things clearer):
CREATE PROCEDURE [dbo].[SP_CreateView_ItemLocation]
@CompanyName VARCHAR(30)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SQLString NVARCHAR(MAX)
--If the view already exists, drop the view
SET @SQLString = 'IF OBJECT_ID (''['+@CompanyName+'$SP_ItemLocation]'', ''view'') IS NOT NULL DROP VIEW ['+@CompanyName+'$SP_ItemLocation]'
EXEC sp_executesql @SQLString
--assemble the SQLString (including the companyname)
SET @SQLString =
'CREATE VIEW [dbo].['+@CompanyName+'$SP_ItemLocation]
AS
SELECT DISTINCT ''ILE'' AS TableName, [Item No_] AS ItemNo, [Location Code] AS LocationCode,
['+@CompanyName+'$Location].Name AS LocationName, ['+@CompanyName+'$Location].[Main Location] AS MainLocation
FROM dbo.['+@CompanyName+'$Item Ledger Entry], ['+@CompanyName+'$Location]
where dbo.['+@CompanyName+'$Item Ledger Entry].[Location Code] = ['+@CompanyName+'$Location].[Code]'
print @SQLString
--this "print " is optionally - it is useful when you're debugging your SP
--in SSMS, because it shows the SQLString that you have been building.
exec sp_executesql @SQLString
END
Then, I wrote some C/AL to call this SP (I excluded the connection-stuff mentioned above. Note that a connection is still necessary):
CreateCompanyViews()
... <Open your connection here (see above)>
IF ISCLEAR(lADOCommand) THEN
CREATE(lADOCommand);
lvarActiveConnection := lADOConnection;
lADOCommand.ActiveConnection := lvarActiveConnection;
lADOCommand.CommandText := 'SP_CreateView_ItemLocation';
lADOCommand.CommandType := 4;
lADOCommand.CommandTimeout := 0;
lADOParameter:=lADOCommand.CreateParameter('@CompanyName', 200, 1, 30,COMPANYNAME);
lADOCommand.Parameters.Append(lADOParameter);
lADOCommand.Execute;
... <Close your connection here (see above)>
You see that I'm using LADOCommand, which is an automation variable of the type "'Microsoft ActiveX Data Objects 2.8 Library'.Command" and LADOParameter is a "'Microsoft ActiveX Data Objects 2.8 Library'.Parameter". Some explanation is required, I guess:
To conclude, I added this code in Codeunit 2 at the end of the OnRun trigger. The code in this codeunit is executed when a company is created.
cduMyADOSamplesMgt.CreateCompanyViews;
I added this after the COMMIT, because I want to make sure that my company is created, no matter if my view was successfully created or not.
-
Compose a SQL String, execute that SQL String and give back the results ... .
This time, I went a little bit further , because now I have an output result set .
First, my stored procedure:
CREATE PROCEDURE [dbo].[SP_LoadInventory]
@ItemNo AS VARCHAR(20)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @CompanyName VARCHAR(30)
DECLARE @OrigCompanyName VARCHAR(30)
DECLARE @SQLString NVARCHAR(MAX)
DECLARE @Counter INT
DECLARE curCompany CURSOR FOR SELECT [Name] as CompanyName from dbo.Company
OPEN curCompany
SET @Counter = 0
SET @SQLString=''
FETCH NEXT FROM curCompany INTO @CompanyName
-- Begin looping all companies in the database
WHILE @@FETCH_STATUS = 0
BEGIN
-- Converting the wonderful NAV-supported-but-best-not-used-in-SQL characters
SET @OrigCompanyName = @CompanyName
SET @CompanyName = REPLACE(@CompanyName,'.','_');
SET @CompanyName = REPLACE(@CompanyName,'"','_');
SET @CompanyName = REPLACE(@CompanyName,'\','_');
SET @CompanyName = REPLACE(@CompanyName,'/','_');
SET @CompanyName = REPLACE(@CompanyName,'''','_');
-- Only put the UNION in between of two SELECT statements
IF @Counter > 0
BEGIN
SET @SQLString = @SQLString + 'UNION'
END
SET @SQLString = @SQLString +
'
SELECT DISTINCT ''' + @OrigCompanyName + ''' AS CompanyName,ItemNo, LocationCode, LocationName, MainLocation
FROM dbo.[' + @CompanyName + '$SP_ItemLocation] a
WHERE ItemNo = ''' + @ItemNo + '''
'
FETCH NEXT FROM curCompany INTO @CompanyName
SET @Counter = @Counter + 1
END;
print @SQLString
EXEC sp_executesql @SQLString
CLOSE curCompany
DEALLOCATE curCompany
END
This is just a simple stored procedure that is going to UNION a SQL Statement for each company. In C/AL, you can call this Stored Procedure like:
... <Open your connection here>
IF ISCLEAR(lADOCommand) THEN
CREATE(lADOCommand);
lvarActiveConnection := lADOConnection;
lADOCommand.ActiveConnection := lvarActiveConnection;
lADOCommand.CommandText := 'SP_LoadInventory';
lADOCommand.CommandType := 4;
lADOCommand.CommandTimeout := 0;
lADOParameter:=lADOCommand.CreateParameter('@ItemNo', 200, 1, 20,pcodItemNo);
lADOCommand.Parameters.Append(lADOParameter);
lADOCommand.Execute;
IF ISCLEAR(lADORecordset) THEN
CREATE(lADORecordset);
lADORecordset.ActiveConnection := lvarActiveConnection;
lADORecordset.Open(lADOCommand);
WHILE NOT lADORecordset.EOF DO BEGIN
ptmpGlobalInventoryBuffer.INIT;
ptmpGlobalInventoryBuffer."Item No" := lADORecordset.Fields.Item('ItemNo').Value;
ptmpGlobalInventoryBuffer."Company Name" := lADORecordset.Fields.Item('CompanyName').Value;
ptmpGlobalInventoryBuffer."Location Code" := lADORecordset.Fields.Item('LocationCode').Value;
ptmpGlobalInventoryBuffer."Location Name" := lADORecordset.Fields.Item('LocationName').Value;
ptmpGlobalInventoryBuffer."Main Location" := lADORecordset.Fields.Item('MainLocation').Value;
ptmpGlobalInventoryBuffer.INSERT;
lADORecordset.MoveNext;
END;
... <Close your connection here>
May be here also some explanation:
- Note that we used lvarActiveConnection to load the active connection into our recordset variable as well.
- The execution of the SP is the same as before, but now you expect something back. That's why you're loading the resultset into a recorset ('Microsoft ActiveX Data Objects 2.8 Library'.Recordset) with the statement: lADORecordset.Open(lADOCommand);
- After the recordset is loaded, we can loop that recordset and put (in my case) the results into a temp table.
- Not that you can move through your recordset with MoveNext, MoveFirst, MoveFirst, MoveLast. You can check if you're at the end with EOF (End Of File).
-
Using an output parameter in a Stored Procedure
OK, taking a little step back. Suppose we want to create a SP with a simple output parameter like a decimal, boolean or whatever. So you won't get back a recordset like above, but just an output parameter. Not really creative, but a SP with an output parameter looks something like this:
CREATE PROCEDURE [dbo].[WALDO_test]
@Result AS VARCHAR(20) OUTPUT
AS
BEGIN
SET @Result = 'Test Output';
END
You define your outputparameter with the OUTPUT keyword.
In NAV it could be something like:
... <Open your connection here>
IF ISCLEAR(lADOCommand) THEN
CREATE(lADOCommand);
lvarActiveConnection := lADOConnection;
lADOCommand.ActiveConnection := lvarActiveConnection;
lADOCommand.CommandText := 'WALDO_test';
lADOCommand.CommandType := 4;
lADOCommand.CommandTimeout := 0;
lADOParameter:=lADOCommand.CreateParameter('@Result', 200, 2, 20,ltxtResult);
lADOCommand.Parameters.Append(lADOParameter);
lADOCommand.Execute;
//Get your result back from the command variable :
ltxtResult := FORMAT(lADOCommand.Parameters.Item('@Result').Value);
MESSAGE(ltxtResult);
... <Close your connection here>
You see that you need an extra statement to pull your result back from the command variable. It's not going to do that for you automatically (allthough you defined your parameter as being "output" (we defined the direction-argument in the CreateParameter-statement as "2")).
One comment might be that you want to use the return value in stead of an output parameter, but keep in mind that the return value of a SP must be "integer".
There are tons of other things you can do with ADO, Stored Procedures,... . I just wanted you to experience how you can use ADO, and what it might be useful for. I'm not saying that this is always the best solution in any case. It's just one of the possibilities where much is possible and flexible solutions can be built with.
Sorry for the somewhat bad editing. I had my hands full with it .
Any comment is highly appreciated.
Small remark: I created this blog using a live example (as mentioned above). I just copied / pasted / changed variables / rearranged code / ... . So I don't guarantee that everything just works by copy/paste/run it ... .