Kriki’s Dynamics NAV blog

Have you been at a customer where ONLY Windows logins are allowed?

So launching NAV (or SQL Server Management Studio) to connect to their database is impossible.

Can you connect to their database if you don’t have a remote terminal or citrix to some computer in their domain?

Yes, you can!

Microsoft has a little (sysinternals) tool (shellrunas.exe) that makes it possible.

You can download it from http://technet.microsoft.com/en-us/sysinternals/cc300361.aspx.

To install it, just run “Shellrunas /regnetonly”.

This adds the option “Run as different user (netonly)” under the rightmouse click on a file in explorer. So when you want to run you finsql.exe, fin.exe, Ssms.exe, … use this trick and you can use Windows-logins from your own portable even if you are NOT in your customers domain!

Filed under: NAVISION, Varia, WINDOWS | 1 Comment »

I just read something very interesting:

Backup compression is available in SQL Sever 2008 R2 Standard!  Read more

That is very good news. No more need for Enterprise version for backup compression!

This is the feature I like the most in SQL 2008!

It speeds up backups (most processors are idle anyway) and it takes less time to move the backups over the network.

Filed under: Uncategorized | 3 Comments »

And now that I am busy with recursion:

-- recursive CTE to generate numbers like the NAV date table

SELECT DATEADD(DAY,-1,[The Date])
FROM DateTable
WHERE DATEADD(DAY,-1,[The Date]) >= @from
)
SELECT *
FROM DateTable
ORDER BY [The Date]
OPTION (MAXRECURSION 0)
See also the forum

Filed under: NAVISION, SQL | 1 Comment »

If we want to loop on integers in NAV, we can use the integer table and put some filters on it.
The problem is that in SQL, we don’t have such a table.

It is possible to create the table with a recursive CTE (Common Table Expression).

If you want, you can also put the select into a stored procedure and use it.

This is the select:
-- recursive CTE to generate numbers like the NAV Integer table

SELECT [The Integer] - 1
FROM numbertable
WHERE [The Integer] - 1 >= @from
)
SELECT *
FROM numbertable
ORDER BY [The Integer]
OPTION (MAXRECURSION 0)

See also the forum.

Filed under: NAVISION, SQL | Make a Comment »

I just got some strange error using STRSUBSTNO:

The text ‘…’ in STRSUBSTNO parameter no. 1 is too long.

Surprise : Is the string I give already longer than 1024 chars? But shouldn’t I have got an error when assigning that value to my variable that I sent into STRSUBSTNO?????

I was wondering if STRSUBSTNO was still limited to 250 chars like the older versions of Navision.

So created a global variable of type text of  1024 chars.

And I run this code :

t := strsubstno(padstr(”,300,’x'),’z');

=> No error. Was the error I got in some other place. Weird….

t := strsubstno(padstr(”,1024,’x'),’z');

=> ERROR. At least I am sure it was not in some other place, but why 300 was ok?

So there is some limit. Lets search for it.

t := strsubstno(padstr(”,1000,’x'),’z');

=> ERROR

t := strsubstno(padstr(”,900,’x'),’z');

=> ERROR.

To make a long story short :

t := strsubstno(padstr(”,511,’x'),’z');

=> This works

t := strsubstno(padstr(”,512,’x'),’z');

=> ERROR.

Conclusion : If your input string in parameter 1 is longer then 511 characters, you get an error!

But why is this the case??????

Now I can understand that if you put in a string of 1024 chars and substitute something to a longer string will give an error. But why already giving an error at 512 characters?

Specially if the error would be this:

String %1%1%1%1%1%1%1%1%1%1%1%1%1%1%1%1%1%1%1%1%1 is too small for insertion of %# fields.

This error message appeared because:
No.: 1
Value: …….

Filed under: C/AL, NAVISION | Make a Comment »

A lot of times there are questions about the license in SQL. How to import it? At what scope serves the “Save license in database” toggle in Tab integration?

Here a short explanation:

*File=>Database=>Alter=>Tab Integration=>Save license in database.

A license can be cut to be used at server level (this means the same license and the same number of users for ALL databases on the server). For example if you have a license for 5 users and you have 10 databases, ONLY 5 users can work at the same moment. In this case you CANNOT put “Save license in database” to TRUE.

A license can be cut to be used per database (you pay for this option!) (this means a different license can be used for each database on the server). For example if you have a license for 5 users and you have 10 databases, 5 users PER DATABASE can work at the same moment. In this case you MUST put “Save license in database” to TRUE, otherwise you don’t have this advantage.

In short, if you are an partner and have a server with all DB’s of your customers to work on, you need to load your developer license and you need to enable “Save license in database”. Otherwise only 5 (in my example) developers can work at the same time. With the toggle, 5 developers PER database can work.

A customer that has only 1 company does NOT need that kind of license.

If you have an international customer with 1 server and 1 DB per country, you need the “Save license in database”-possibility (granule 2020). This is needed because with a Belgian license you can’t use Italian objects.

*Some information on the buttons you find in “Tools=>License”.

-Upload: (does NOT exist on native DB!). Serves to ‘Upload’ a license in SQL. It depends on the toggle “Save license in database” if it is uploaded in the current database or in the SQL server-instance (saved in the master DB).

-Import: saves the license in the subdirectory where the finsql.exe (or fin.exe) is. This option was only useful on native when you worked on a database WITHOUT using a databaseservice. If you open a native database locally, this license is used.

-Export:  This is the opposite of the “Import” and is only useful for native working with a local database. Copies the license from the subdirectory where the finsql.exe (or fin.exe) is to a given path+name.

-Change: This serves to change the license ONLY for current client UNTIL you close the client. After this you can connect another database (SQL DB, native DB in server mode or local mode) but it will use the license you just “changed” into. When you close the client and restart it, the original license will be taken. This button is only useful for developers that have to connect to a customerdatabase and do some maintenance that the customer license does not permit.

REMEMBER to NOT leave your developer license at the customer. Some people collect stamps, others collect developer-license files!

Filed under: NAVISION, NAVISION-DB, SQL | Make a Comment »

As we all (should) know, SQL is based on sets and NAV works iterative with records.

Sometimes it is possible to do set based programming also in NAV.

I just found an example in which we can speed up something thinking in sets (1 record is also a set!) instead of iterative. The beautiful thing is: it also is valid for a native DB.

For example: we need to get (with GET or FINDFIRST) 1 record and change 1 field in it without do any testing on it. So get the record and throw some value in some field.

The classic method would be:

recTheTable.LOCKTABLE;

recTheTable.RESET;

recTheTable.SETCURRENTKEY(….);

recTheTable.SETRANGE(”Field 1″,’Value 1′);

recTheTable.SETRANGE(”Field 2″,’Value 2′);

…

IF recTheTable.FINDFIRST THEN BEGIN // or even worse would be if there are multiple records

recTheTable.”Some Field” := ‘Some Value’;

recTheTable.MODIFY(FALSE);

END;

What happens: NAV requests a record and locks it. The server gets the record and sends it to the client. The client changes it and sends it back to the server. And if there are multiple records, the server and the client start playing ping pong with the records.

Now the SET based way (this works for 0, 1 or multiple records!):

recTheTable.RESET;

recTheTable.SETCURRENTKEY(”Field 1″,”Field 2″,…);

recTheTable.SETRANGE(”Field 1″,’Value 1′);

recTheTable.SETRANGE(”Field 2″,’Value 2′);

recTheTable.MODIFYALL(”Some Field”,’Some Value’,FALSE);

What happens: NAV sends the request to the server and the server processes it. BUT the record does NOT do a trip between server and client and back again!

Of course if you need to change multiple fields in the same record, this set based method might become slower then the classic NAV method.

Filed under: C/AL, NAVISION, NAVISION-DB, PERFORMANCE, SQL | 3 Comments »

I receive quite often this question. Now someone answered it really well: Goals, obsessions, and aspirations: becoming an MVP

Filed under: MVP, Varia | Make a Comment »

A little trick for all codefields on which there is a tablerelation to a table (Customer No., Vendor No., Items No.,…).

You can always use F6 to select the value you want but there is a shortcut:

For example: you have all your customers coded by “C000001″..”C999999″ and you know the code of the customer (e.g. “C000013″, you can put the following value into the field (E.g. “Sell-To Customer No.” in “Sales Header):”*013″. NAV will search the first value that respects the filter and put “C000013″ it in your field. If there are multiple values for the filter, NAV will take the first value it finds.

It works for the classic client. I didn’t check if it works for the RTC.

Filed under: CLASSICCLIENT, NAVISION, RTC | 1 Comment »

Why would someone want to use the Stand-By function on Windows 2008 Server?

You even have to search for it (With Start =>  …) you don’t find it! You need taskmanager (or ProcessExplorer to be able to use the Stand-By function on Windows 2008 server.

Ok, but why use the Stand-by functions. I know it sounds quite illogical.

Well, it is NOT if you use it on your portable like I do. My project manager suggested me to use Windows 2008 server 64bit on my new portable (I was already thinking about doing it anyway, so he didn’t have a hard time to convince me).

I already hear you: But Windows 2008 is based on Vista-technology (Windows 2008 R2 is based on Windows 7). Yes, true, but it is definitely better than Vista! The 64-bit version of Windows 2008 uses around 450MB of memory and the 32-bit version about 380 MB both a clean install. Both are less then a clean 32-bit Vista!

The only problem I found is that I can’t use pre 4.0SP3 + hotfix NAV-versions on it. (Windows 7 has no problems with it! And neither do I because I have ’some’ VM-Ware virtual machines on it. One in which all my SQLserver-DB’s are put (Windows 2003 server) and another for older NAV-versions (Windows XP). With 4GB of memory it is not a problem at all.

The BIGGEST advantage of Windows 2008 Server is that the Stand-By function is a lot faster and better. With XP I had to reconnect to the DB each time. With Windows 2008 Server I don’t need to do that. I remain connected!

This can help you setting up Windows 2008 as a workstation : Why would someone want to use the Stand By function on Windows 2008 Server?

You even have to search for it (With Start =>  …) you don’t find it! You need taskmanager (or <a href=”http://www.sysinternals.com”>ProcessExplorer</a> to be able to use the Stand By function on Windows 2008 server.

Ok, but why use it. I know it sounds quite illogical.

Well, it is NOT if you use it on your portable like I do. My project manager suggested me to use Windows 2008 server 64bit on my new portable (I was already thinking about doing it anyway, so he didn’t have a hard time to convince me).

I already hear you: But Windows 2008 is based on Vista-technology (Windows 2008 R2 is based on Windows 7). Yes, true, but it is definitely better than Vista! The 64-bit version of Windows 2008 uses around 450MB of memory and the 32-bit version about 380 MB both a clean install. Both are less then a clean 32-bit Vista!

The only problem I found is that I can’tt use pre 4.0SP3 + hotfix NAV-versions on it. (Windows 7 has no problems with it! And neither do I because I have ’some’ VM-Ware virtual machines on it. One in which all my SQLserver-DB’s are put (Windows 2003 server) and another for older NAV-versions (Windows XP). With 4GB of memory it is not a problem at all.

The BIGGEST advantage of Windows 2008 Server is that the Stand-By function is a lot faster and better. With XP I had to reconnect to the DB each time. With Windows 2008 Server I don’t need to do that. I remain connected!

This can help you setting up Windows 2008 as a workstation.

Filed under: WINDOWS | Make a Comment »