Dynamics AX Command Line Parameters

Axapta Command Line


Following are Axapta Command line parameters and its description

-allowunauth
When using Windows Authentication, this option enables users that do not pass the authentication process to be allowed logging in using user name and password (traditional Axapta logon sequence). If not enabled, users will be rejected if not authenticated.

-aol="s"
aol is an acronym for Application Object Layer. Valid layers are:
sys, syp, gls, glp, dis, dip, los, lop, bus, bup, var, vap, cus, cup, usr and usp.

-aolcode="s" Access code for aol.

-aos=host:port
Connect to the AOS running at given port number on specified host. Host is either a DNS hostname (for example server1.damgaard.com) or an IP address (192.88.253.41). The port number is the number specified for the AOS instance that should be connected to. No instance name needs to be specified because only one instance can be running at a given port on a given machine. Using this option will connect the client directly to the AOS using TCP traffic only and bypass the initial search for the AOS and thereby eliminates the need for networking that supports UDP traffic. This eases firewall configuration and NAT appliances

-aos=instance@host
Connect to the specified AOS instance running at the specified host machine. Instance is the name (for example 'Axapta'), and host is DNS name or IP address of the machine running the AOS. Specifying -aos=MyAOS@MyHost equals setting -servermask=MyAOS and -internet=MyHost (or specifying these in corresponding fields on the server tab in the Configuration Utility).

-aos=ad("adsn")
Use Active Directory integration. "adsn" is the "Active Directory Server Name to search the Active Directory for. Same as "By name - find a specific AOS" in the Axapta Configuration Utility

-aos=adbrowse
Use Active Directory integration. Search the Active Directory for "Active Directory Server Names" in User objects and Organizations Units. Same as "By organization - browse for per-user or per-organizational specific AOS" in the Axapta Configuration Utility.

-aos=ad
Use Active Directory integration - search the Active Directory for any AOS. Same as "Simple - find any AOS" in the Axapta Configuration Utility

-applexclusive
The application files are opened in exclusive mode. Note applexclusive" and applshare cannot both be given. If you do give both, the one given last on the command line will take effect. You will not get any error messages.

-application=s
Specify the name of the Axapta application. Default: Standard

-applshare
The application files are opened in shared mode. This is default. Note applexclusive" and applshare cannot both be given. If you do give both, the one given last on the command line will take effect. You will not get any error messages.

-broadcast= xx.xx.xx.xx
Specify a broadcast address to be used in CLIENT mode. A request is sent to all the broadcast addresses to obtain identification of the available application servers. The address consists of four decimal values between zero and 255 separated by dots.

-bwsim=speed:latency
where speed states the bandwidth simulated (in bytes per second). The latency is specified as a number designating number of ms spent for communication round trip (the fixed overhead (time used) the network applies to sending a package to the server and receive one back). To verify....

-client
Connect to an AOS and run as a three-tier thin client. Use -aos= to specify which AOS.
client = thin : Connect to an AOS and run as a three-tier thin client.
Use -aos= to specify which AOS.
client = fat : Connect to an AOS and run as a three-tier fat client.
Use aos= to specify which AOS.

-company=s Select initial company s. Default: dat

-connectionidletimeout=seconds
Set the time in seconds to leave an idle database connection open before closing it. Shorter idle time will decrease database server and Axapta memory usage, but will potentially cause time-consuming re-logins on the fly. Default: 60 seconds is the default for Microsoft SQL Server, 30 minutes the default for Oracle.

-createdsn=microsoftsqlserver or-createdsn=oracle
Have the data source created automatically in the ODBC manager.

-createdsn_tcpipport=integer
TCP/IP port number required for Oracle. This parameter is relevant only when
-createdsn=oracle is given. The parameter is ignored if given with
-createdsn=microsoftsqlserver.

-database=s
Use database s when connecting to the database server. Default: The default option is to use the database set in the ODBC driver.

-dbcli= [ODBC][OCI]
Runs Axapta in either ODBC or OCI mode. -DBCLI=ODBC is the default.

-dbserver=s
Use server s during login. Default: The default option is to use the server set in the ODBC driver.

-dbunicodeenabled=0 1 Initialize database for Unicode

-directory=s Specify the Axapta root-directory.

-doclanguage=s
Use this option if you would like to have the documentation in a different language than the one used in menus and dialogs.
Example:
-doclanguage=da : will give you the documentation in Danish.
Default: The default is that the documentation language is identical to the language used in the system. This is set by the -language option.

-dsn=s
Use ODBC driver data source s. Default: BMSDSN

-featurekeysystem
The 3.0 security system is ON by default.Use this parameter to enable the old featurekey system.
-fetchahead=n A maximum of records retrieved from the database at a time. Default: 100

-hint=n
Apply database dependent SQL hint(s). Default: Empty for default settings.

-internal=relaxedsyntax The 3.0 kernel defaults to strict X++ syntax checking.
For relaxed syntax checking, use this parameter to ease restrictions.

-internet=s
Specify an Internet address to be used in �client mode. A request is sent to all the Internet addresses to obtain identification of the available Object servers.

-job=s
Run external job s prior to any other database-related action during startup.
Default: The default is not to run a job.

-language=s
Select language s for the user interface. Default: Language must be selected during setup.

-log=s
Name the SQL error log file (may include a full drive and path specification).
Default: trcAxaptaError.log in the standard Axapta log-directory.

-logdir=s
Use an alternative directory for the log files generated when you compile, import or export in Axapta. Default: The default is that the log files are generated in the Log folder.

-noauto
Use this parameter to bypass system related application calls made by the Axapta kernel. This includes the ability to bypass startup code, and some timer based calls. This parameter will allow you to startup Axapta in order to fix problems that were introduced in the application code. Normally these problems would prevent you from starting Axapta. For example, if code is introduced in the startup method that causes Axapta to go into infinite loop, and therefore, never finishes the startup procedure, . To change this, start with the �NOAUTO switch, correct the code and restart without the �NOAUTO to have the startup code included again.

-opencursors=n
A maximum of n database cursors are kept open per connection for cursor reuse.
Default: 90 cursors

-port=integer : TCP port for the AOS

-preloadthresholdmsec=milliseconds Time used for preloading.
For example, -preloadthresholdmsec=3000 results in the issue of a warning whenever preloading exceeds 3000 milliseconds. This value can not be specified per user in Tools, Options, SQL, Warnings. This threshold is only activated when warnings are enabled.
-preloadthresholdrecords=records Number of records preloaded.
For example, -preloadthresholdrecords=300 results in the issue of a warning whenever preloading exceeds 300 records. This value can not be specified per user in Tools, Options, SQL, Warnings. This threshold is only activated when warnings are enabled.

-querytimelimit =[table:][milliseconds]
Save queries running longer than a given number of milliseconds to file. If the value of QuerytimeLimit is zero (0), which is the default, no queries are logged. This parameter supports directing output to a table, i.e. SysTraceTable (default is disk-file). Use -QuerytimeLimit=ms for tracing all SQL statements exceeding the ms milliseconds threshold and -QuerytimeLimit=table:ms to do the same to table.

-regconfig=name Use a Registry configuration called name.
A configuration can be created using the Axapta Configuration Utility.

-regimport=file name Import a configuration to the Registry.
The import is performed prior to the evaluation of any other options. This means that you can import a configuration using �regimport and then select it using �regconfig.

-repair
Any non-zero value will force a re-synchronization of SQL system tables during startup. The use of this command-line parameter is logged in the Event log. Use this option to handle situations when problems in SQL system tables prevent Axapta from starting, for example missing indexes.

-retry=n
Delay in seconds before re-executing after a deadlock. Default: 5 seconds

-securityprovider=s
Selects the security provider to use with Windows Authentication and is only relevant for Object Server configuration. For AOS running on Windows NT the only valid option is "NTLM" which provides authentication based on the NTLM security provider. For Windows 2000 systems 'Kerberos' is also a valid security provider. For Windows 2000 networks with solely Windows 2000 servers and clients 'Negotiate' is also an option. This will elect the best suitable security provider automatically.

-serveridletimeout=seconds
Specifies how long (in seconds) the AOS instance should be allowed to be running without servicing clients. When this timeout expires without having clients connected, the instance will be shut down automatically. This option is well suited to be combined with setting instance startup mode to OnDemand making the server auto-start upon request from client and shutdown when no clients need service for at given amount of time.

-servermask=s
Specify the mask s for selecting a subset of object servers when running in CLIENT mode. if this option is not specified, and multiple object servers are found, all available object servers will be presented in a selection box.

-share
Share label and identifier files between several applications. if not specified, the files will not be shared.

-singleuser
Run the program in single user mode.

-sqlbuffer=n Set the upper limit in Kbytes of the fixed internal data retrieval buffer. Default: 24 Kbytes

-sqlcomplexliterals=n
About literals and placeholders. Setting sqlcomplexliterals to the value 1 enables this feature, the value 0 disables this feature.

-sqlformliterals=n About literals and placeholders.
Setting sqlformliterals to the value 1 enables this feature, the value 0 disables this feature.

-sqloraclefirstrowsfix=n
Oracle Versions 8.05, 8.06 and 8.15 occasionally selects a poor query plan for queries using the Axapta keyword firstFast row. The symptom is that an index matching the order by specification is preferred, even though another index much better serves the where part and the number of rows returned is small. Axapta includes a workaround for this problem, which you should only enable if you have verified that the above problem is the cause for poor performance. The Axapta Query Analyzer can be used for detecting this. A value of 1 enables this work around, a value of 0 disables this feature.

-sqlparm=s
Add additional parameters s upon database login. The format follows the ODBC standard: key1=value1;key2=value2. An example: DIR=c:\db;ID=9. Default: The default is no additional parameters.

-sqlpwd=s
Use password s upon login to the SQL database. Default: bmssa_pwd

-sqltrace[=Table]
Invoke SQL statement tracing to log file or table. Use -sqltrace for tracing all generated SQL statements to file and -sqltrace=table to do the same to table. Default: No tracing.

-sqluser=s
Use user name s during login to the SQL database. Default: bmssa

-startupmsg=s
Text to be displayed during Axapta startup.

-startupcmd=MyCommand
A string that is passed to Axapta and can be used to have your own startup commands executed. The string is passed in the calls appl.startup(MyCommand) info.startup(MyCommand) appl and info are instantiated objects of the application classes Application and Info respectively. The application classes are inherited from the system classes xApplication and xInfo. Learn more in the Developer's Guide. You can access the guide from Axapta's Help menu.

-useis Use integrated security during SQL database login
and thus disabling values set by using parameters sqluser and sqlpwd.

-user=s Log on as user s.

-useserverprinters
Have the client direct all printing to the printer connected to the server.

-warnings[=table]
Enable various run-time warnings which are logged to a file, or table. Use -warnings to trace all developer warnings to file and -warnings=table to trace all warnings to a table. Default: No warnings.

-windowsauth={01}
This option disables/enables Windows Authentication which, when enabled, is providing Single Sign-On and Authentication of client machine account and the user logging in.

Dynamcis AX Macros

I wanted to know how i could use string substitution to change the code in X++ using Macros. As a part of playing around with macros below are the findings. Macors in AX can be define in two ways

1. #define.TableRead(select * from EmplTable where EmplTable.EmplCode == %1;)
#TableRead("E0006")
info( emplTable.EmplCode );

2. #localmacro.TableRead
select * from EmplTable where EmplTable.EmplCode == "%1";
#endmacro

#macrolib.training
#TableRead(E0006)
info( emplTable.EmplCode );

The second way is the preferred way of creating multi line macros also if the macro includes some special symbols like ) or " then the send method is preferred

#macroLib is used when the macros are defined in a macro definations node instead of the local method scope.

Inheritance and Macros
The macros defined in the class declaration of the base class are available in the sub classes however if one of the class in the hierarcy undefines the macro then that point onwards the macro are not available in the child classes.

Move Object Layer

This was one interesting excercise we did where we moved the objects from the VAR layer to the BUS layer for one of the live implementations. The challenge was that we wanted to retain all the data and all the references.

1. To begin with we had two installations once was the dev instance where the objects were in the bus layer and the other was the live instance where the objects were in the var layer.

2. We know that while the no sequence of the objects starts from 30000 onwards in the var layer it starts from 20000 onwards in the bus layer. So once we resoted the bus layer (aod) the existing object id's in the 30,000 series became irrelevant. So we deleted all the entries in the sql dictionary table between 30000 and 40000, backup the records from the dev environment where tableid was between 20000 and 30000and restored it on the live.

3. We also know that when the table names are more than 30 chars the synchronize routine replaces the last 4 chars of the table with the objectid. So we manully looked for such objects and changed the tableid suffix in such objects from the 30000 series to the 20000 series.

4. Once we did the above we were getting a duplicate record while entering data into most of the table it was no long before we realized that the systemsequences table contains the last record id and is used to generate the record if for the new record. In this case as the object ids changed a corresponding entry for them was required in the systemsequences table with the new object ids. I wrote a script to generate a script for the right updates.

select ' insert into systemsequences
( ID,NEXTVAL,MINVAL,MAXVAL,CYCLE,NAME,TABID,DATAAREAID,RECVERSION,RECID ) '
+ 'select '
+ ' -1, isnull( max( recid ) + 1, 1 ) , 1, 9223372036854775807, 0, ''SEQNO'', ' + cast( SD.TableID as varchar) + ',''dat'', 1, -1'
+ ' from ' + SD.SQLName
from sqldictionary SD
left join systemsequences SS
on SS.tabid = SD.tableID
where 1=1
and SS.Name is null
and SD.tableid between 20000 and 30000
and SD.fieldid = 0

Remote scan during linked table Update

Faced this issue while building an interface from a SQL Server to a remote MYSQL database. We had a set of insert, update statements to synchronize a set of table however the update statements were taking forever to execute. On analyzing the execution plan we got to know that there was a remote scan being performed for each update which was slowing down the update as for each row update a remote scan for 6500 rows was being performed. On investigating further i found that this is a documented behaviour and what MSDN says is:

For linked server DELETEs or UPDATEs, SQL Server retrieves data from the table, performs any filtering that is necessary, and then performs the deletes or updates through the OLEDB rowset. This processing can result in a round-trip to the remote server for each row that is to be deleted or updated

SQL Server 2000 adds the ability to send a DELETE or UPDATE to a linked server as a single SQL statement; however, this feature only covers linked servers to another SQL Server 2000 or SQL Server 7.0 instance

Please refer knowledge base article
http://support.microsoft.com/kb/309182

So the only option we have to update without any performance overhead on the remote server is if we use stored procedures for insert and udpates so that the actual filtering and update is actually performed on the remote server rather then in a rowset at the local server.

“System does not support setup 'continuous' of number sequence

Realized that the issue was that the getNewNum function was called without transaction processing

PYLEmplLeaveDetailsTable.LeaveAppNum = NumberSeq::newGetNum(HRMParameters::numRefLeaveAppNum()).num();

just got resolved by encapsulating the same within a transaction

ttsbegin;

PYLEmplLeaveDetailsTable.LeaveAppNum = NumberSeq::newGetNum(HRMParameters::numRefLeaveAppNum()).num();


ttscommit;

Deploying AOD files directly

One of the methods of deploying customizations onto an AX installation is by carrying the aod files and replacing them on the target installation. However there is one issue with this approach if the newly updated aod file contains some new security and configuration keys the same would not get registered in the security metadeta of AX which i believe is stored in the database.

So we might land up with a menu with missing menu items as the security is not recognized for them even for the Administrator.

the way to get around this issue is to disable and enable the concerned configuration key doing so will get it registered in the AX Security related tables

Macros

For a long time i wondered if string substitution is all that the macros offer for until i realized the power of macros when it comes to a programming language.

Macros are strings which are substituted by the pre-compiler before they reach the compiler and it is this behavior which makes macros extremely powerful.

Hence macro substitutes a string in the source code which is then compiled and processed by the compiler.

A classic example of this behavior is the pack unpack pattern applied in AX. It starts with the definition of a macro which refers to all the memory variables we need to maintain state for. The pack method returns a container effectively before this source reaches the compiler the macro is substituted and the container list contains all the memory variables which are processed by the compiler and the actual memory values are composed into the container.

similarly in the unpack method when we say
[version,#CurrentList] = _packedClass;
before the above statement is processed by the compiler the macro is substituted by all the memory variables which then get initialized as this statement is executed.


Ideally the source code written once should not be repeated elsewhere coz as per the best practices it should be reused however if for some reason a piece of code needs to be duplicated through a routine the macros are the ideal candidates to maintain a single central copy of this source.

MorphX has a built-in macro-preprocessor. The purpose of macros is to make statements easy to reuse and you can declare macros wherever you can write X++ statements.

There are basically three kinds of macros: macros in macro libraries, stand-alone macros and (local) macros inside methods. All three types of macros have identical syntax and functionality. The only differences are that

• A stand-alone macro is created using the macro-node of the Application Object Tree

• A macro library is a stand-alone macro, which contains (local) macros

• A local macro is declared within a method, or stand-alone macro

X++ has these macro constructs:

#DEFINE	This statement defines a macro variable, which can be used as a constant or to make macros perform differently according to the macro variable value.
#UNDEF	This statement cancels a macro variable.
#MACROLIB	Loads a macro library. Libraries must be loaded before the macros within them can be used.
#GLOBALMACRO	Declares a global macro (header). A global macro can be used everywhere in MorphX after declaration.
#LOCALMACRO	Declares a local macro (header).
#ENDMACRO	Signals the end of a macro declaration.
#IF	Tests if the macro variable following the #IF has as given value. Includes the statements between #IF and #ENDIF.
#IF.EMPTY()	Tests the argument within the brackets, and includes the statements between #IF and #ENDIF if the argument is empty.
#IFNOT.EMPTY()	Tests the argument within the brackets, and includes the statements between #IF and #ENDIF if the argument is not empty.
#ENDIF	Signals the end of a conditional macro declaration (#IF).
#LINENUMBER	Returns the current line number. Mainly useful for debugging

Using define

You can use the define macro construct to declare symbolic constants in your program as follows:

#Define.MaxLength(100)

int intarray[#MaxLength]

Macro declaration

Macros are declared by this syntax:

Macrodeclaration = #localmacro. Macroname { text } #endmacro

Where text can be anything. Within the macro declaration, it is possible to use parameters. The parameters are named %1, %2, %3 etc. All macros are considered as text, and are unfolded before compilation. This means that there is no means of checking syntax or functionality within macros (all that appears is a compiler error in the macro with no specification of error location).

A local macro, called AnExample is declared as follows:

#localmacro.AnExample

// Some statements or text

#endmacro

A more complex macro using parameters is declared as follows:

#localmacro.ComplexMacro

#DEFINE.ARG(%1); // Defines a macro variable ARG

#IF.EMPTY(%1) // If parameter1 is empty

print “The first parameter is empty (not used)”;

#ENDIF

#IFNOT.EMPTY(%1) // If it is not empty

print “The first parameter is NOT empty, it is %1”;

#ENDIF

%2 = %3 + %4; // param2 is assigned

// param3+param4

#IF.ARG(1) // IF parameter1 is 1

print “Parameter1 has the value 1”;

#ENDIF

#UNDEF.ARG // The macro variable ARG is undefined

#endmacro

Referencing macros

You reference macros by their name prefixed with a hash mark (#). To reference the complex macro shown above, you would write:

#complexMacro(parameter1, parameter2, parameter3, parameter4)

The parameters (1-4) have a story: Parameter 1 is optional, as it is only used in the macro, if it exists (#IFNOT.EMPTY) whereas parameters 2, 3 and 4 are used unconditionally (in the statement %2 = %3 + %4) - and therefore must be included in the call. Assuming you have declared a variable called A, a valid reference would be):

ComplexMacro(1,a,3,4);

Which would expand the macro into the statements below:

#DEFINE.ARG(1); // Defines a macro variable

#IF.EMPTY(1) // If parameter1 is empty

print “The first parameter is empty (not used)”;

#ENDIF

#IFNOT.EMPTY(1) // If it is not empty

print “The first parameter is NOT empty, it is 1”;

#ENDIF

A = 3 + 4; // param2 is assigned param3+param4

#IF.ARG(1) // IF parameter1 is 1

print “Parameter1 has the value 1”;

#ENDIF

#UNDEF.ARG // The macrovariable ARG is undefined

The results of the above statements are two prints and an assignment.

A is assigned the value 7 and two lines are printed on the screen: “The first parameter is NOT empty, it is 1” and “Parameter1 has the value 1”.

If you referenced the complex macro without parameters 2-4, the result would have been:

= + ; // param2 is assigned param3+param4

And hence a compilation error occurs though you cannot see that the macro is actually expanded in this way (which makes the error harder to find).

Using macro libraries

To use a macro library, you must create the library (say TestMe) using the Application Object Tree and then load the macro using the following construct:

#TestMe;

A more preferred way of referring macro libraries is using the #macrolib keyword the advantage of using this keyword is that you will be able to reference macors with special characters and multiple lines look at the example below where a special character (") doube quote had to be used in a macro
1. #define.TableRead(select * from EmplTable where EmplTable.EmplCode == %1;)
#TableRead("E0006")
info( emplTable.EmplCode );

2. #localmacro.TableRead
select * from EmplTable where EmplTable.EmplCode == "%1";
#endmacro

#macrolib.training
#TableRead(E0006)
info( emplTable.EmplCode );

The second way is the preferred way of creating multi line macros also if the macro includes some special symbols like ) or " then the second method is preferred if you notice in the first method the double quote are passed in the parameter to the macro as they could not be a part of the macro as in the macro definition the macro would end at the first instance of the double quote.

Inheritance and Macros
The macros defined in the class declaration of the base class are available in the sub classes however if one of the class in the hierarcy undefines the macro then that point onwards the macro are not available in the child classes.

37 "Signals" From 37 Signals

1) Great businesses have a point of view, not just a product or service.

2) Writing a plan makes you feel in control of things you don’t actually control.

3) You have the most information when you’re doing something, not before you've done it.

4) Stuff that was impossible just a few years ago is simple today.

5) Failure is not a prerequisite for success.

6) Don’t make assumptions about how big you should be ahead of time.

7) Don’t sit around and wait for someone else to make the change you want to see.

8) When you build what you need, you can assess quality directly instead of by proxy.

9) Solving your own problem lets you fall in love with what you’re making.

10) What you do matters, not what you think or say or plan.

11) When you want something bad enough, you make the time.

12) The perfect time to start something never arrives.

13) Start a business, not a startup.

14) You need a committment strategy, not an exit strategy.

15) Huge organizations talk instead of act, and meet instead of do.

16) Build half a product, not a half-assed product.

17) Getting to greatness starts by cutting out stuff that’s merely good.

18) The real world isn’t a place, it's an excuse. It's a justification for not trying.

19) The big picture is all you should be worrying about in the beginning. Ignore the details.

20) Decide. You’re as likely to make a great call today as you are tomorrow.

21) The longer it takes to develop, the less likely it is to launch.

22) It’s the stuff you leave out that matters.

23) Focus on substance, not fashion. Focus on what won't change.

24) When good enough gets the job done, go for it.

25) When you make tiny decisions, you can't make big mistakes.

26) Pour yourself into your product.

27) You rarely regret saying no but you often regret saying yes.

28) Better your customers grow out of your product, than never grow into them.

29) You can’t paint over a bad experience with good marketing.

30) All companies have customers. Fortunate companies have audiences too.

31) Instead of out-spending your competitors, out-teach them.

32) Let customers look behind the curtain.

33) Leave the poetry in what you make, there is beauty in imperfection.

34) Marketing is not a department, it's the sum total of everything you do.

35) Don’t hire for pleasure; hire to kill pain.

36) Don’t make up problems you don’t have yet.

37) A business without a path to profit is a hobby.

Unlock a protected excel sheet

I had this excel sheet which was a RFP document and needed to copy a part of it as a response but this sheet was protected and selection was disabled on the sheet for the desired columns

Came across this amazing macro which would disable the password and make the columns selectable.

http://mcgimpsey.com/excel/downloads/allinternalpasswords.xls

basically when this excel sheet was opened a new addin button is created which could be used to unlock the sheets.

SID in AX Security Tables

The problem arose where we are trying to restore the live production databases into a test environment

Historically DAX has had its own users when it was integrated to windows security model the windows user's SID (SID is a unique identifier for a user or a group in Windows) was used to act like a user.

SIDs are specific for a particular domain, i.e. domain admin accounts thus in two different domains the same user will have different SIDs.

The problem is that when the DAX database is moved from the production environment to a test environment it carries the SID of the production environment in the security tables which is different from a SID of an account that is being used in the test environment.

The solution is as below:

* find out the SID of the user you use to run a DAX client after DB migration
o you can use whoami tool shipped with Windows - just run whoami /user in a console and you'll see a long string like S-1-5-21-15052... next to your login;
o if you use WinXP or an earlier Windows version you'll have to install Windows XP Support Tools to get the tool and afair it has a slightly different command line params... you'll need to use /sid option instead of /user
* run a tool like Ms SQL Management Studio and connect to the restored DAX database
* navigate to the UserInfo table, open it in a grid and update the SID value for user "admin" with the one you've found out previously
* start DAX client - you should login as an admin now

SQL Server sp_execute

We commonly come across this statement when profiling SQL Server i have wondered a lot about what it is and there was some explanation i found for it in the BOL.

The ODBC API defines prepared execution as a way to reduce the parsing and compiling overhead associated with repeatedly executing a Transact-SQL statement. The application builds a character string containing an SQL statement and then executes it in two stages. It calls SQLPrepare once to have the statement parsed and compiled into an execution plan by the database engine. It then calls SQLExecute for each execution of the prepared execution plan. This saves the parsing and compiling overhead on each execution. Prepared execution is commonly used by applications to repeatedly execute the same, parameterized SQL statement.

thus sp_execute is a system stored procedure used with "prepared" statements from a client. The number you see is an internal pointer to the execution plan on the server. The values following that number are the parameters for a particular invocation of the prepared statement


Now when we see sp_execute in the profile and we need to know the text behind it then first thing we can do it to check if there is an "sp_prepare" with the same number in the thread, if there is then you include sql_handle in the trace and you look can up the text for the cache entry using the following statement

select text
from sys.dm_exec_requests
cross apply sys.dm_exec_sql_text(plan_handle)
where session_id = [spid]


you can also enable Prepare SQL event in the profiler to get the handle of the execution plan

I found this problem where a prepared sql was taking much longer to execute when i executed the same sql in the management studio it was actually fast so i am wondering what could make a unprepared sql faster then a prepared sql?

I thought maybe the statistics are out so created the statistics for this table
with a FULLSCAN option and that really helped still not convinced why? maybe i will have to study the execution plan in both the cases and spot the difference i believe that different execution plans are being used in these cases.

Bring a Standby Database online

When Transaction Log Shipment is setup a copy of the transaction database in maintained in read-only and standby mode on a pre-configured server.

If for any reason the primary server fails and the standby server needs to be promoted use the following command to make the database available

RESTORE DATABASE [databasename] WITH RECOVERY

Dynamics AX Application file types

There are lots of files with different file extensions in the Application\Appl\Std folder. Got these details from Harish Mohanbabu's blog quite a handy information

Yes. There are quite a lot of files in Axapta Application\Appl\Standard folder. Please note that all these files will be updated whenever a new version is released. Most important them are -

1. .aod - Acronym for Application Object Data file. Each Application object layer is saved in a separate file called
"Ax< layer >.aod". For example, Axsys.aod for the SYS layer, Axusr.aod for the USR layer and so on.

2. label files : If we look at our label files in the AOT we see 4 different extensions for our labels

· ALI Axapta Label Index

· ALC Axapta Label Comments

· ALT Axapta Label Temp, Store

· ALD Axapta Label Dictionary



The ALD file is readable/editable with a text editor. General speaking you only need the ALD file. When the AOS is restarted the ALI and ALC will be generated on the fly. (or updated when the time stamp of the ALD file is bigger than the timestamp of the ALC or ALI file)

Next, a developer creates new labels. These labels will be stored in the ALT file. Not yet in the ALD file. When the final AOS will stop. AX will update the ALD file this way. It will copy the ALD file to an ALB file. Next the changes in the ALT file will be stored in the ALB file. Finally this ALB file is placed back in the ALD file and the ALB file will be deleted. (HINT: make the ALD file read only and you will see it your self)

When your AOS has creased the changes are not stored in the ALD file. Even when you start and stop the AOS again the file is not updated. To solve this issue start an AX client search for the label in the Label editor. Next stop your client and the AOS. Now the label file is updated.

3. .udb - Acronym for Axapta User Database. As the acronym indicates, Axapta stores its user details in this file. Also this file is responsible for allocating session ID to users. If this file is deleted, then Axapta would regenerate whenever the system is started.

Tip : Some times in Axapta 3-tier installations, when you look at online users form, it could return false information. For example, let us assume 5 users are currently online. But this form, might show 7 or 8 users online. Or some times, though you have enough licenses, you might receive "maximum users reached" error message from Axapta.

In such cases, the solution would be deleting the "axdat.udb" file. But before deleting it, make sure that all users are exited and AOS is properly shut down. Once this is done and the file is deleted, as mentioned above, Axapta would regenerate the "Axdat.udb" file. Please note that you might not even come across this problem. But to be on the safer side, particularly on 3-tier installations, it is always advisable to regularly delete "axdat.udb" file.

But in some special conditions, online user form may be empty in Axapta ver 2.5. This condition may happen if your license has an expiry date. Even though your license may not have expired, you might still come across this condition. In such cases please go to Technet website and do a search with this key words "The online user form is empty". You would come across an Export file. Import that export file in your installation. That would solve the problem.

4. .ktd - Acronym for Kernel Text Data.

Tip : Some times when you upgrade your installation (say for example you have installed a new SP), you might not be able to see the new features of the installed SP after the upgrade is complete.

The solution for this would be manually copying the .ktd files from Client\Bin\ directory to Axapta Application\Bin directory. The reason being - when Service pack is installed, for some reasons files in Axapta Application\bin directory are not updated. Only the files that are there in Client\Bin are updated.

5. .aoi - Acronym for Application Object Index. As the name indicates this is an index file for the Application objects.

Tip : Some times you may get funny error messages. Though you might have hard disk space, sometimes the following error might popup -

Error in file: ...\standard\axapd.aoi while reading in record ...
Error code: 38 = hard disk is full

For all the error messages involving axapd.aoi, the solution would be deleting the "axapd.aoi" file from Axapta Application\Appl\Standard directory. When you restart the system again, Axapta would rebuild this index file.

If the problem persists even after deleting the file, then check whether "Open application files in exclusive mode" is enabled (Axapta Configuration Utility --> Under General Tab). If enabled, then disable it. This should solve the problem.


6. What is "Master.aoc" file and how can I create it ?

aoc stands for Axapta Object Cache. This functionality was introduced in Axapta ver 2.5 mainly intended for best performance possible.

When running a 3-Tier Navision Axapta 2.5 client, we will cache different objects as we use the application. All objects are cached in the memory on the client machine. When the client is shutdown, the cache objects are written to disk, in a file with the extension .aoc (Axapta Object Cache). The next time a user starts the client, the executable ax32.exe checks for the .aoc file and if one exists it is loaded into memory. This gives us a performance gain, as we do not have to cache these objects again.

How to create a preconfigured cache file?

This is very simple. We configure a 3-tier client and go through the parts of the application all clients will use. Ex - General Ledger, Sales Order and Accounts Receivable. Doing this will create an .aoc file, with the following naming convention - "ax_AOS [Instance name]@[Hostname]_[username].aoc". In my case, I got a file like this -
ax_Annai@Himalaya_Harish.aoc. This file, if used as the preconfigured cache file, must be renamed to "master.aoc". Please note that this naming convention of the .aoc file would be different if you have configured an Object Server Cluster. (Ex - ax[cluster name][username].aoc)

When installing the Navision Axapta 2.5 client software, the setup.exe program will look for the master.aoc file in the directory where setup.exe is located. After completing the installation of the client, the setup.exe program will, if a master.aoc file exists, copy this file to the \Log directory, located in the default directory structure of the installed client.

When a user starts the client software for the first time, the executable as32.exe, will look for a cache file that belongs to the user, who is currently logged in. If one exists then this cache file is loaded into memory. If none exists, then the ax32.exe will look in the \Log directory for the master.aoc file. If one exists, the objects in this cache file would be loaded into memory.

Dynamics AX unpack type mismatch

I had this strange problem where in when a form loaded it gave an error on the unpack method stating type mismatch. The code was find and was not able to find any issues with it until i realized that i had done a xpo import recently which means the pack was done on the previous version and now the system was trying an unpack on a new version after the import.

in this new version there was a new parameter added which was not packed thus i had to clear the cached data and the issue was resolved.

The cached data could be cleared using the link
Tools

Trigger to Stop Database Drop

CREATE TRIGGER [ddl_trig_Prevent_Drop_Database]
ON ALL SERVER
FOR DROP_DATABASE
AS

--log attempt to drop database
DECLARE @db VARCHAR(209)
SET @db = (SELECT 'Database Dropped Attempted by ' + CONVERT(nvarchar(100), ORIGINAL_LOGIN()) +
' executing command: '+ EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','VARCHAR(229)'))
RAISERROR(@db, 16, 1)WITH LOG

--prevent drop database
ROLLBACK
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

--turn on trigger
ENABLE TRIGGER [ddl_trig_Prevent_Drop_Database] ON ALL SERVER


--test trigger
CREATE DATABASE test1

DROP DATABASE test1

Msg 50000, Level 16, State 1, Procedure ddl_trig_Prevent_Drop_Database, Line 11
Database Dropped Attempted by TestSQLUser executing command: DROP DATABASE test1
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.


--turn off trigger
DISABLE TRIGGER [ddl_trig_Prevent_Drop_Database] ON ALL SERVER
GO


/****** Object: DdlTrigger [ddl_trig_Prevent_Drop_Database] Script Date: 01/11/2010 19:22:28 ******/
IF EXISTS (SELECT * FROM master.sys.server_triggers WHERE parent_class_desc = 'SERVER' AND name = N'ddl_trig_Prevent_Drop_Database')
DROP TRIGGER [ddl_trig_Prevent_Drop_Database] ON ALL SERVER
GO

--cleanup current errorlog
sp_cycle_errorlog

List of Actively running commands by SPID in SQL Server

This following code snippet would list all the actively running commands by SPID


select session_id, Text

from sys.dm_exec_requests r

cross apply sys.dm_exec_sql_text(sql_handle) t

SQL Server Transaction Log

DBCC LOG([,{0|1|2|3|4}])

0 - Basic Log Information (default)

1 - Lengthy Info

2 - Very Length Info

3 - Detailed

4 - Full

Example:

DBCC log (MY_DB, 4)

And it displays the following transaction log information:

* Current LSN
* Operation (string starts with LOP_XXXXX)
* Context (string starts with LCX_XXXX)
* Transaction ID
* Tag Bits (Hex)
* Log Record Fixed Length (int)
* Log Record Length (int)
* Previous LSN
* Flag Bits (Hex)
* Description (string)
* Log Record (Hex)



There is another command which is used to read the transaction log ::fn_dblog. I found an interesting hack by Paul S Randal on how to find which user dropped an object using the transaction log.

SELECT [Transaction Id], [Begin Time], [UID], [SPID]
FROM ::fn_dblog (NULL, NULL)
WHERE [Transaction Name] = 'DROPOBJ'

The (NULL, NULL) is the starting LSN and ending LSN to process - NULL means process everything available.


Now, this only shows us that a table was dropped, not which table it was. There's no way to get the name of the table that was dropped, only the object ID - so you'll need to have some other way to determine what the table ID is if there are multiple table drops and only one of them is malignant.

For SQL Server 2000, the code to find which object ID we're talking about is as follows (dropping the Transacation Id into the WHERE clause):

SELECT DISTINCT [Object Name] FROM ::fn_dblog (NULL, NULL)
WHERE [Transaction Id] = '0000:000000e0'
AND [Context] = 'LCX_IAM';
GO

Object Name
--------------------
(2009058193)

The object ID in parentheses is the ID of the table that was dropped.

For SQL Server 2005 and 2008, the code is as follows (with the same Transaction Id substitution):

SELECT TOP (1) [Lock Information] FROM ::fn_dblog (NULL, NULL)
WHERE [Transaction Id] = '0000:00000587'
AND [Lock Information] LIKE '%SCH_M OBJECT%';
GO

Lock Information
--------------------------------------------
ACQUIRE_LOCK_SCH_M OBJECT: 8:2073058421:0

The 8:2073058421 is the database ID and object ID of the table that was dropped.

PS If you find the you don't get enough info from ::fn_dblog, try turning on trace flag 2537. It allows the function to look at *all* possible log, not just the active log.

Install DAX without Domain Controller

The DAX installer compares current user's domain with the local machine netbios name. If they match the installer thinks that it has been run under a local user and throws an error

«You are logged on with a local computer account. You must be logged on with a domain account to run Microsoft Dynamics AX Setup».

To avoid this check change local machine netbios name in the registry (HKLM\System\CurrentControlSet\Control\ComputerName\ActiveComputerName) AND after that change UserDnsDomain environment variable to the same value prior to running the setup.

You can run cmd.exe, set the environment variable and run the setup from the command line so that it can "see" the changed value. After installation change ActiveComputerName in the registry back to its original value.

Transaction log not truncating

I landed into this strange problem where i was not able to truncate the log of the database i tried the backup log and then shrinkfile a no of times with no luck.

Then i remembered that the database was being replicated using transactional log shipments so maybe there was some issue there. Thus started to look for any open transactions which might be holding the transaction logs used the command
DBCC OPENTRAN(db_name)

Transaction information for database 'BATEELNAV5'.

Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (28941:199:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Got a message which confirmed that there was a replication transaction which was holding the database I got rid of the same using

use
go
sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1
go


I was then able to truncate the log successfully.

AX 2009 Document Management FilePath

We are doing this project where it is required that the attachments are stored into a folder on the HDD now thatz a default configurable parameter in AX the complication is that in this case we are expecting the attachment size to be huge so much so that it exceeds the storage capacity of the server.

Well in the modern world no one takes a decision of making a change in the system to accomodate for such a scenario as the easiest option is to increase the storage space but in this case it seems that we do not have an option to further expand the storage on the server and once the server disk is filled up the only option is to either get a new server which has more slots to push more HDD's or to backup the files onto a different server.

AX allows us to configure a folder path for each file type but the issue is that it only maintains the file name in the transaction tables and the path is maintained in the parameter table which means if the path is changed at a later date AX will not be able to trace the old attachements coz when those were made the path was different

I found where my attachments for a customer were being stored and realized that the path was being referred to from the parameters table and the full file name with path was being build in a function in the docuref table called completeFileName

select DV.FileName, DV.FileType, DR.* from docuref DR
inner join docuvalue DV
on DR.ValueRecId = DV.RecId
where RefTableId = 77
and PartyId = 191


so the solution in this case is that we decide to store the file path with the file when the attachments are made so that the system remembers multiple paths for the files.

Create Clustered Index Script

I had this requirement where we identified that a no of tables in AXapta were missing the clustered index which was resulting in some performance issues. It was then recommended by Microsoft to create clustered indexes on all these tables this is what we did.

--list of all tables missing an clustered index
select SO.name, SI.*
from sysobjects SO
left join sysindexes SI
on SI.id = SO.id
and SI.indid = 1
where SI.indid is null
and SO.type = 'U'



--script to generate the script
select 'create clustered index [IDX_' + SO.name + '] on [' + SO.name + '] (recid, dataareaid )'
from sysobjects SO
left join sysindexes SI
on SI.id = SO.id
and SI.indid = 1
where SI.indid is null
and SO.type = 'U'




another important query is to list all the columns in a index in this case clustered index
--list of columns in the clustered index
select OBJECT_NAME(SI.id) TableName, SI.name IndexName, SIK.indid IndexId, SC.name ColName
from sysindexes SI
inner join sysindexkeys SIK
on SI.id = SIK.id
and SI.indid = SIK.indid
inner join syscolumns SC
on SC.id = SIK.id
and SC.colid = SIK.colid
where SIK.id = OBJECT_ID( 'ContactMapping' )
and SIK.indid = 1
order by SIK.indid, SC.colorder

Space used by each Table in SQL

SQL 2005 Version
================
BEGIN try
DECLARE @table_name VARCHAR(500) ;
DECLARE @schema_name VARCHAR(500) ;
DECLARE @tab1 TABLE(
tablename VARCHAR (500) collate database_default
, schemaname VARCHAR(500) collate database_default
);
DECLARE @temp_table TABLE (
tablename sysname
, row_count INT
, reserved VARCHAR(50) collate database_default
, data VARCHAR(50) collate database_default
, index_size VARCHAR(50) collate database_default
, unused VARCHAR(50) collate database_default
);

INSERT INTO @tab1
SELECT t1.name
, t2.name
FROM sys.tables t1
INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id );

DECLARE c1 CURSOR FOR
SELECT t2.name + '.' + t1.name
FROM sys.tables t1
INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id );

OPEN c1;
FETCH NEXT FROM c1 INTO @table_name;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @table_name = REPLACE(@table_name, '[','');
SET @table_name = REPLACE(@table_name, ']','');

-- make sure the object exists before calling sp_spacedused
IF EXISTS(SELECT OBJECT_ID FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(@table_name))
BEGIN
INSERT INTO @temp_table EXEC sp_spaceused @table_name, false ;
END

FETCH NEXT FROM c1 INTO @table_name;
END;
CLOSE c1;
DEALLOCATE c1;
SELECT t1.*
, t2.schemaname
FROM @temp_table t1
INNER JOIN @tab1 t2 ON (t1.tablename = t2.tablename )
ORDER BY schemaname,tablename;
END try
BEGIN catch
SELECT -100 AS l1
, ERROR_NUMBER() AS tablename
, ERROR_SEVERITY() AS row_count
, ERROR_STATE() AS reserved
, ERROR_MESSAGE() AS data
, 1 AS index_size, 1 AS unused, 1 AS schemaname
END catch





Previous Versions of SQL
========================
DECLARE @table_name VARCHAR(500)
DECLARE @schema_name VARCHAR(500)
DECLARE @tab1 TABLE(
tablename VARCHAR (500) collate database_default
,schemaname VARCHAR(500) collate database_default
)

CREATE TABLE #temp_Table (
tablename sysname
,row_count INT
,reserved VARCHAR(50) collate database_default
,data VARCHAR(50) collate database_default
,index_size VARCHAR(50) collate database_default
,unused VARCHAR(50) collate database_default
)

INSERT INTO @tab1
SELECT Table_Name, Table_Schema
FROM information_schema.tables
WHERE TABLE_TYPE = 'BASE TABLE'

DECLARE c1 CURSOR FOR
SELECT Table_Schema + '.' + Table_Name
FROM information_schema.tables t1
WHERE TABLE_TYPE = 'BASE TABLE'

OPEN c1
FETCH NEXT FROM c1 INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @table_name = REPLACE(@table_name, '[','');
SET @table_name = REPLACE(@table_name, ']','');

-- make sure the object exists before calling sp_spacedused
IF EXISTS(SELECT id FROM sysobjects WHERE id = OBJECT_ID(@table_name))
BEGIN
INSERT INTO #temp_Table EXEC sp_spaceused @table_name, false;
END

FETCH NEXT FROM c1 INTO @table_name
END
CLOSE c1
DEALLOCATE c1

SELECT t1.*
,t2.schemaname
FROM #temp_Table t1
INNER JOIN @tab1 t2 ON (t1.tablename = t2.tablename )
ORDER BY schemaname,t1.tablename;

DROP TABLE #temp_Table

Inconsistencies in G/L Entry posting

The transaction cannot be completed because it will cause inconsistencies in the G/L Entry table. Check where and how the CONSISTENT function is used in the transaction to find the reason for the error. Contact your system manager if you need assistance. Parts of the program mark tables as inconsistent during very comprehensive tasks, such as posting. This prevent data from being updated incorrectly.


1. Rounding errors could be one of the issues making a line inconsistent.


Finding the transaction which cause the issue ? there is a solution got this from Rashed Amini courtesy mibuso

1. create the following CU


OBJECT Codeunit 50000 Single Instance CU
{
OBJECT-PROPERTIES
{
Date=10/11/07;
Time=[ 2:50:02 PM];
Modified=Yes;
Version List=MOD01;
}
PROPERTIES
{
SingleInstance=Yes;
OnRun=BEGIN
IF NOT StoreToTemp THEN BEGIN
StoreToTemp := TRUE;
END ELSE
FORM.RUNMODAL(0,TempGLEntry);
END;

}
CODE
{
VAR
TempGLEntry@1000000000 : TEMPORARY Record 17;
StoreToTemp@1000000001 : Boolean;

PROCEDURE InsertGL@1000000000(GLEntry@1000000000 : Record 17);
BEGIN
IF StoreToTemp THEN BEGIN
TempGLEntry := GLEntry;
IF NOT TempGLEntry.INSERT THEN BEGIN
TempGLEntry.DELETEALL;
TempGLEntry.INSERT;
END;
END;
END;

BEGIN
END.
}
}






2. And in CU 12 I add the following Code in function FinishCodeunit


FinishCodeunit()
WITH GenJnlLine DO BEGIN
IF GLEntryTmp.FIND('-') THEN BEGIN
REPEAT
GLEntry := GLEntryTmp;
IF GLSetup."Additional Reporting Currency" = '' THEN BEGIN
GLEntry."Additional-Currency Amount" := 0;
GLEntry."Add.-Currency Debit Amount" := 0;
GLEntry."Add.-Currency Credit Amount" := 0;
END;
GLEntry.INSERT;
//MOD01 Start
SingleCU.InsertGL(GLEntry);
//MOD01 End
IF NOT InsertFAAllocDim(GLEntry."Entry No.") THEN


3. Once you've made the changes. You run the SinleInstanceCU Once.
4. Then do what ever you do to get the consistency error.
5. Then Run the SingleInstanceCU again.
6. You'll see a list of GL lines. You will see why the transaction is not balanced.

CONTEXT_INFO Connection wide variable

Need for a connection wide variable at the database level arose when I was creating an interface between Navision and MSCRM

The requirement was that when an Item was created in Navision it was pushed to CRM and when any changes were made in CRM they were reflected back in Navision we used triggers and things were fine but due to a two way interface it was required that the trigger should ignore any updates which were being made during the synch process and only the entries which were being made by the user on the front end should be captured for sync.

This is what we did. The procedure which was updating the CRM item table set the context_info as shown below

--context_info is used in the target tables triggers to ignore updates
--else it results in endless updates
declare @CONTEXT_INFO varbinary(128)
select @CONTEXT_INFO = CAST ('SynchronizeNAV' as varbinary(128) )
set CONTEXT_INFO @CONTEXT_INFO


In the trigger on the Item table in CRM we checked if the update/ insert was being made by the user or by the sync procedure as below

declare @sourceProc varchar(20)
select @sourceProc = cast ( context_info() as varchar)
set @sourceProc = ltrim( rtrim( @sourceProc ) )
if ( @sourceProc = 'SynchronizeCRM' )
return


one thing to ensure while using the context_info is that once the use is done it should be reset to null else it will continue to exist with the set value
thus at the end of the process we user

set context_info null

Convert timestamp to String

select master.dbo.fn_varbintohexstr(@@DBTS)

Get a list of Objects modified after a given date from AOT

1. Goto AOT - Expand "System documentation" - Expand "Tables" - Tablebrowse "UtilElements" or Tablebrowse - "UtilIdElements"
2. Right click in field "Utillevel" - select Filter By Field - type - usr - press ok
3. Right click in field "RecordType" - select Filter By Field - type - SharedProject - press ok
4. Right click in field "CreatedDate" - select Filter By Field - type - 01-Jan-2010..

Oracle Linked Server

Following are the steps involved in creating a linked server and working with stored procedures on the Oracle Database

1. Install the Oracle Client software on the server
2. Create a linked server as follows
EXEC sp_addlinkedserver
'OracleLinkedServer', 'Oracle',
'MSDAORA', 'OracleServer'

The name of the linked server is Oracle-LinkedServer.

The second parameter, product name (Oracle),is optional.The third parameter specifies the OLE DB provider.

The third parameter MSDAORA is the name of the Microsoft OLE DB Provider for Oracle.

Thr fourth parameter is the data source name of the Oracle Server (This is the TNS name of Oracle server which should work when pinged using a utility called TNSping and the servername this utility is installed with the client software of Oracle.)

3. Add Login information for the linked server
EXEC sp_addlinkedsrvlogin '
OracleLinkedServer ', false,
'SQLuser', 'OracleUser',
'OraclePwd'

The first parameter, Oracle Linked Server, specifies the name of the linked server system that you created.

The second parameter determines the name of the login to be used on the remote system.A value of True indicates that the current SQL Server login will be used to connect to the linked server. This requires that the logins on the two database servers match, which is typically not the case.A value of False means you'll supply the remote login.

The third parameter specifiesthe name of a SQL Server login that this remote login will map to.A value of NULL indicates that this remote login will be used for all connections to the linked Oracle server. If the Oracle system uses Windows authentication, you can use the keyword domain\ to specify a Windows login.

The fourth and fifth parameters supply login and password values for the Oracle system.


3. To be able to use stored procedures on Oracle the RPC paramter should be enabled on the server which is done as follows:-
sp_serveroption 'ORALINK', 'rpc out', 'true'
sp_serveroption 'ORALINK', 'rpc', 'true'


4. To access the tables on a linked server, use a four-part naming syntax: linked_server_name.catalog_ name.schema_name.table_name.

For example, to query the sample Oracle Scott database, you'd enter the statement

SELECT * FROM OracleLinkedServer..SCOTT.EMP
(Please mark the double dots after the server name which means that the catalogue or database name is not required and used by default this is because in oracle each database is treated as a server with a TNS name)


5. Test a stored procedure
declare @lstatus varchar(240)
set @lstatus = ''
execute ( 'BEGIN JNC_RMS_API.CHECK_CONNECTIVITY( ? ); END;', @lstatus output) at ORALINK
print @lstatus

Please ensure that the strings being sent for output parameters are initialized with a default value ( set @lstatus = '' ) else the procedures don't execute.

AX Workflow Error

From Blog

1- Open cmd as administrator , Right click run as administrator
2- Go to folder C:\inetpub\AdminScripts>cscript adsutil.vbs get W3SVC/APPPOOLS/Enable32BitAppOnWin64
3- If this node is True just set it to False “C:\inetpub\AdminScripts>cscript adsutil.vbs set W3SVC/APPPOOLS/Enable32BitAppOnWin64 False”
4- Restart IIS iisreset and retry the installation.

Keyword Options for X++ select Statements Keyword

Description

firstfast
Fetches the first selected record faster than the remaining selected records.

firstonly
Returns only the first selected record.

forupdate
Selects records for updating.

nofetch
Specifies that the Dynamics AX runtime should not execute the statement immediately because the records are required only by some other operation.

forceplaceholders
Forces the Dynamics AX runtime to generate a query with placeholder field constraints. For example, the query generated for the preceding code example looks like this: select * from myTable where myField1=?. Database query plans are reused when this option is specified. This is the default option for select statements that do not join table records. This keyword cannot be used with the forceliterals keyword.

forceliterals
Forces the Dynamics AX runtime to generate a query with the specified field constraints. For example, the query generated for the preceding code example looks like this: select * from myTable where myField1='value'. Database query plans are not reused when this option is specified. This keyword cannot be used with the forceplaceholders keyword.

forceselectorder
Forces the Microsoft SQL Server query processor to access tables in the order in which they are specified in the query. (No effect on Oracle.)

forcenestedloop
Forces the SQL Server query processor to use a nested-loop algorithm for table join operations. Other join algorithms, such as hash-join and merge-join algorithms, are therefore not considered by the query processor.

reverse
Returns records in reverse of the select order.

Recover SQL 2005 Corrupt Database

1) Detach database and move your mdf to save location.
2) Create new databse of same name, same files, same file location and
same file size.
3) Stop SQL server.
4) Swap mdf file of just created DB to your save one.
5) Start SQL. DB will go suspect.
6) ALTER DATABSE SET EMERGENCY
ALTER DATABASE SET SINGLE_USER
7) DBCC CHECKDB (, REPAIR_ALLOW_DATA_LOSS)
8) ALTER DATABASE SET MULTI_USER
ALTER DATABSE SET ONLINE

AX TableName in AOT and SQL

Well ran into this problem of searching for a table in SQL. I find the table in AX and was not able to find the corresponding table in SQL Server. Finally managed to figure out that AOT does not create table names more than 30 characters in the backend.

This maybe because AX wanted to keep the data structure portable between SQL and Oracle or any other ERP. Thus what AOT does is that if the table name exceeds 30 char it truncated the table name in such a way that when clubbed with the table id it runs into 30 characters

hence, if a table is created in AOT with name = HRPPartyPositionTableRelationship and id = 2226 it would be physically be created as HRPPARTYPOSITIONTABLERELAT2226

Limit Navision User Sessions Per Company

Had this interesting requirement from one of our customers who had more than one companies in the same database. The total no of concurrent sessions were 50 but it was required that not more than a certain no of users are allowed to log on into one company.

ie. Not more then 15 users in Company 1
Not more then 10 users in Company 2
Not more then 25 users in Company 3

Below the solution :-)

//Following code goes into ApplicationManagement Codeunit 1 CompanyOpen

//changes to Company table to add a new field No of Users type Int
//a new table to be created to store the active connections as follows
//"Connection ID" int
//Company Text(50)
//"User Id" Code(20)

//check for any orphan sessions which may be lying due to navision crashes
//orphan sessions to be cleaned. This can also be used to drop inactive
//sessions before starting a new one.
ActiveConnections.RESET;
IF ActiveConnections.FIND('-') THEN REPEAT
Session.SETRANGE("Connection ID", ActiveConnections."Connection ID");
Session.SETRANGE("Application Name", 'Microsoft Dynamics NAV client');
IF Session.FIND('-') = FALSE THEN
ActiveConnections.DELETE;
UNTIL ActiveConnections.NEXT() = 0;

ActiveConnections.RESET;
Session.RESET;
Session.SETRANGE("My Session", TRUE);
IF Session.FIND('-') THEN BEGIN
ActiveConnections.SETRANGE("Connection ID", Session."Connection ID");
IF ActiveConnections.FIND('-') = FALSE THEN BEGIN
ActiveConnections.INIT;
ActiveConnections."Connection ID" := Session."Connection ID";
ActiveConnections.Company := COMPANYNAME;
ActiveConnections."User Id" := USERID;
ActiveConnections.INSERT;
END;
END;

Company.GET(COMPANYNAME);
ActiveConnections.RESET;
ActiveConnections.SETRANGE(Company, COMPANYNAME);
IF (ActiveConnections.COUNT > Company."No Of Users") THEN BEGIN
WHILE CONFIRM('Limit for no of users (%1) has reached. Do you wish to try again ?',TRUE, Company."No Of Users")
DO BEGIN
ActiveConnections.RESET;
ActiveConnections.SETRANGE(Company, COMPANYNAME);
IF (ActiveConnections.COUNT <= Company."No Of Users") THEN
EXIT;
END;

YIELD;
IF ISCLEAR(WshShell) THEN
CREATE(WshShell);

YIELD;
WaitForKeys := TRUE;
WshShell.SendKeys('%{F4}', WaitForKeys);
CLEAR(WshShell);
END;



//Following code goes into ApplicationManagement Codeunit 1 CompanyClose
Session.SETRANGE("My Session", TRUE);
IF Session.FIND('-') THEN BEGIN
ActiveConnections.SETRANGE("Connection ID", Session."Connection ID");
ActiveConnections.DELETEALL;
END;

Slow Navision Report Preview

Had this funny incident today we designed a report which was working perfectly well then i made my office printer as the default printer to test the printing of the report. Working from my home i noticed suddenly the report was very slow.

The issue is the default printer which is the network printer was taking the time to connect when the printer was changed to the local printer the issue was taken up.

sp_change_users_login

sp_change_users_login does not work for sa and dbo instead use the sp_changedbowner

GenJrnlLine.SetUpNewLine Dimension Lines Error

the SetUpNewLine function does the initialization of a new line when creating an automated Journal. I noticed when this function was used for some reason the Journal Line Dimension stopped getting populated. I did not get into the route of the issue however it solves when this function is called after a new line no has been assigned thus i had to change from code from:

LineNo += 10000;
GenJrnlLine.INIT;
GenJrnlLine."Journal Template Name" := GLSetup."Consumption JV Template";
GenJrnlLine."Journal Batch Name" := GLSetup."Consumption JV Batch";
GenJrnlLine.SetUpNewLine(GenJrnlLine, 0.00, FALSE);
GenJrnlLine."Line No." := LineNo;


to

LineNo += 10000;
GenJrnlLine.INIT;
GenJrnlLine."Journal Template Name" := GLSetup."Consumption JV Template";
GenJrnlLine."Journal Batch Name" := GLSetup."Consumption JV Batch";
GenJrnlLine."Line No." := LineNo;
GenJrnlLine.SetUpNewLine(GenJrnlLine, 0.00, FALSE);

Adjust Currency Exchange Rate

The currency exchange rate batch job looks into the customer and vendor sub ledgers and posts the difference to a gl account.

for the same to work there must be a posting type defined against the payables account mentioned on the vendor posting group, else the internal JV posting fails

Minimize Outlook to Tray

To minimize the Outlook as a tray icon instead of Taskbar add the following key in the registry

HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Outlook\Preferences

add a new DWORD value

MinToTray = 1

Not In Vs Not Exists

The Not in and Not exists clauses are quite similar except for one major difference which can be illustrated using the example below:

EMP_NBR	EMP_NAME	MGR_NBR
1	DON	5
2	HARI	5
3	RAMESH	5
4	JOE	5
5	DENNIS	NULL
6	NIMISH	5
7	JESSIE	5
8	KEN	5
9	AMBER	5
10	JIM	5

Now, the aim is to find all those employees who are not managers. Let’s see how we can achieve that by using the “NOT IN” vs the “NOT EXISTS” clause.

SQL> select count(*) from emp_master where emp_nbr not in ( select mgr_nbr from emp_master );

COUNT(*)
———-
0

SQL> select count(*) from emp_master T1 where not exists ( select 1 from emp_master T2 where t2.mgr_nbr = t1.emp_nbr );

COUNT(*)
———-
9

Now there are 9 people who are not managers. So, you can clearly see the difference that NULL values make and since NULL != NULL in SQL, the NOT IN clause does not return any records back.

There is no value entry within the Filter

Well this is the error we received at one of the customer sites when they were executing the famous adjust cost batch job. Did some real head breaking into this one and discovered that when the Inventory setup has been set as:
1. Average Cost Calc. Type = Item
2. Average Cost Period = Month

The error was something like:
There is no Value entry within the Filter
Filters : Item No: 1100053749, Valution Date: 01/01/09..31/12/08

On debugging we found the error was being caused by an FINDSET; statement which was issues in the following Codeunit:

Codeunit : 5895, Inventory Adjustment
function : AvgValueEntriesToAdjustExist(OutbndValueEntry;ExcludedValueEntry,AvgCostAdjmtEntryPoint)


Got around the issue after a detailed profiling of the code and found that when the valuation date changes and a new filter is being evaluation in the code it somehow caused caused a wrong filter on the Value Entry Table the code snipped was as shown below

IF "Valuation Date" > CalendarPeriod."Period End" THEN BEGIN
CalendarPeriod."Period Start" := "Valuation Date";
AvgCostAdjmtEntryPoint.GetValuationPeriod(CalendarPeriod);
END;

How i managed to get around the issue was to add one line here as shown below:
IF "Valuation Date" > CalendarPeriod."Period End" THEN BEGIN
CalendarPeriod."Period Start" := "Valuation Date";
AvgCostAdjmtEntryPoint."Valuation Date" := "Valuation Date";
AvgCostAdjmtEntryPoint.GetValuationPeriod(CalendarPeriod);
END;

I finally concluded that the problem was encountered when a value entry record was found for a new financial year (2009 in this case) and there were no records for the previous year end (December 2008).

If we look at the code GetValuationPeriod(CalendarPeriod) in Table 5804 "Avg. Cost Adjmt. Entry Point" at the end of the function the following lines were setting the values to be used to set the filter :

//*****************************************************************************
IF FiscalYearAccPeriod."Starting Date" IN [CalendarPeriod."Period Start"..CalendarPeriod."Period End"] THEN
IF "Valuation Date" < FiscalYearAccPeriod."Starting Date" THEN
CalendarPeriod."Period End" := CALCDATE('<-1D>',FiscalYearAccPeriod."Starting Date")
ELSE
CalendarPeriod."Period Start" := FiscalYearAccPeriod."Starting Date";
//*****************************************************************************
All we did was to set the valuation date so that it does not evaluate to less then the Financial Period."Start Date"

Navision Filters

We all know the basics of Filters now lets turn to some advanced ways of using and understanding filters.

? which can be used while building a filter to substitute one unknown character pretty much like the Wildcard Characters used in DOS days

@ which can be used to ignore the case of the text being searched for
thus @co* would search for anything beginning with Co , cO, CO or co followed by any character as indicated by an asterix.

There has been some other types of filters which are referred to in Navision code with commands like FIND('=><') OR FIND('=<>')

1. '=><' when the above three expressions are combined it would stand for. If equal '=' rec is not found, search for a record which is smaller '<' and if smaller rec is not found search for a rec which is bigger '>' basically means find anything which was used in olden days to check if the rec filter was empty or not.

2. '=<>' this would translate to find something "equal to" or "not equal to" which means "find anything" similar to the command above

Today we also have ISEMPTY commmand to determine if a recordset is empty.

Procedure to Update Statistics for All indexes of a Table

sp_updatestats (Transact-SQL)
Runs UPDATE STATISTICS against all user-defined and internal tables in the current database.


Find the user defined procedures to run it for a table.

/*
select 'usp_update_statistics [' + name + ']' + char(13) + char(10)
+ 'go' + char(13) + char(10)
from sysobjects where type = 'U'
and not ascii( right ( [name], 1 ) ) between 48 and 57
*/


Alter procedure usp_update_statistics
@Table_Name varchar(255)
as
declare @Index_Name varchar(255)
declare @SQL nvarchar(500)


declare index_cursor cursor for ---Getting index name
select name from sysindexes
where id = object_id(@Table_Name)
and indid > 0
and indid < 255
and (status & 64)=0
order by indid

open index_cursor

fetch next from index_cursor
into @Index_Name

while @@fetch_status=0
begin
set @SQL = ''
set @SQL = @SQL + 'update statistics [' + @Table_Name + '] ( [' + @Index_Name + '] )'
set @SQL = @SQL + ' WITH FULLSCAN, NORECOMPUTE'
exec sp_executesql @SQL

print @SQL
print ''

fetch next from index_cursor
into @Index_name
end
close index_cursor
deallocate index_cursor
print 'Complete.'

sp_cursorfetch

Had this terrible issue with Navision Value Entries form. Whenever the form was opened from the Item Card Navision would freeze did a profile in SQL for all the SQL statements and found that the system was taking time to execute a command which started like sp_cursorfetch.

Some RND and found that the sp_cursorfetch is implemented by the database library to manage the cursors at the server side. Before a cursor fetch can be issue a cursor open statement has to be declared which would contain the base statement for the cursor. Once i found the statement being used for the cursor i did a execution plan display and found that it was not using the correct index.

I then updated the statistics for the index using the Update Statistics command we all know that statistics decide the selectivity of an index once this was done the cursor started using this index and the issue was resolved.

Format Strings

Format function with Navision is really powerful all we need to do is to understand in detail how the format string is built

Below is one example what i wanted to do was to generate a file name in the format DDMMYY-HHMMSS.txt also i wanted DD (Date) to be two characters and MM (Month) to be padded with 1 zero if it was 1 character in length. Amazingly the entire thing could be achieved just by one amazing function FORMAT

FORMAT(CURRENTDATETIME,0,'<FillerCharacter,0><Day,2><Month,2><Year><Hours24,2><Minutes,2><Seconds,2>')

All the different parameters that can be used with FORMAT function are well documented in the online help.

Browse For Folder in Navision

Navisions implementation of Common Dialog Box does not cater to Browse for folder. There is a small workaround for this although it is not very neat but it does the work. Below is the code for the same this is a function which returns the FolderName and DefaultFolderName is the parameter to the function.


IF DefaultFolderName = '' THEN
DefaultFolderName := 'C:\Folder'
ELSE
DefaultFolderName := DefaultFolderName + '\Folder';

FolderName := CmmDlg.OpenFile('Select Folder'
, DefaultFolderName
, 4
, 'All File (*.*)|*.*'
, 0 );

//Truncate the file name from the path
Ctr :=STRLEN(FolderName);
WHILE Ctr > 0 DO BEGIN
IF COPYSTR(FolderName, Ctr, 1) = '\' THEN BEGIN
FolderName := COPYSTR(FolderName, 1, Ctr -1 );
EXIT;
END;
Ctr -= 1;
END

The only thing we are doing here is that we are providing a default filename in the browse window thus the open button is enabled without waiting for the user to select a file name.

Arabic Data

Came across this client who wanted to capture the item descriptions in Arabic in addition to the english descriptions. Changing the System Keyboard alone does not help for this the following steps are involved.

1. Install Supplemental Language Support for this go to Control Panel -> Regional and Language options -> Languages Tab select the Install files for complex script and right to left languages.
2. In the input languages click on the details button and add arabic keyboard support.
3. Change the Language for Unicode Programs this can be changed from Control Panel -> Regional and Language options -> Advanced Options Tab.
4. Once the language has been changed the database collation should be change to support the new code page. For this use the alter database option -> Collation Tab and select the collation for the arabic language.

Now you are all set to save english and arabic in the Navision database.

OnCreateHyperLink and OnHyperLink

Ever wondered about these triggers in navision and what they are meant for. Well Navision offers a facility to create links to the different objects within it. Using this feature a hyperlink could be created for a Form in Navision from the desktop. Creation of the hyperlinks is simple open the desired Form and then use the File -> Send to option to place a hyperlink on the desktop.

When the send to desktop option is used the first thing the option does is to call the OnCreateHyperlink trigger with the URL as the parameter. The URL parameter can be modified in the trigger giving one a control over what needs to be placed in the URL string. The OnHyperLink trigger is called when the form is accessed using this link and the URL is passed in the trigger.

PrintOnlyIfDetail Property in Navision Reports

Figured out that this propery does not work if for a Dataitem if the child DataItem does not have a section defined in the section view. The way around was to create a section and use CurrReport.ShowOutPut(False) to suppress the section as well as make the PrintOnlyIfDetail property work in the desired manner.