F.A.Q. for Dynamics AX: T-SQL Scripts

Showing posts with label T-SQL Scripts. Show all posts

Keep your Compiler level set to 3

This is just a helper I used over the years to reset my compiler level from 4 back to 3; I believe it's still going to help some of you with those long waiting minutes when compiling a project that has a significant number of objects, when the TFS policy changes the Compiler level automatically to 4.
I do this and then run Check BP from the Add-ins menu when I choose.

Obtain restore info for your database

Had to find out when a database was last restored (and from what locatgion) and looking online discovered this T-Sql script:

Obtain sql information about an object

Sometime you need to understand what takes up the space in your database, so a quite useful T-Sql script for discovering that is:

Rename a SQL Server database

Restoring databases frequently I realized I need a fast was to rename the databases on the fly. Here we have a rather fast (but manual - for the moment) way of doing this:

Disable and enable users in AX using T-Sql scripts

There are times when you need very fast to disable the users in AX, except of course one or two users logins. This is needed for instance when you are doing deployments of new code and you don't want users roaming free in your AX while you are not yet done.

Switch AX database to single mode with TSql statement

The other days I kept getting some SQL errors regarding my AX database. I wasn't sure why this started to happen, and trying to rename the database I realize I was also unable to perform any tasks in SSMS using the UI / contextual menu on it. So decided to switch the database to single user using scripts, which worked beautiful:

Change the SID / domain for a user

Basically you want to do this if you've just got a new set of databases from another site and you are locked out of AX. Just open SSMS, and run the following T-SQL.

Call method from .Net assembly in T-SQL

The topic is simple and has been blogged on many times, so, just adding my share of how I did it on my machine putting together multiple sources. One note is that the method in the assembly is calling a web service, so it must have external access. I'm starting of course with the basic MSDN page on how to declare an assembly in SQL and then create the stored procedure for calling a method this: https://msdn.microsoft.com/en-us/library/ms189524.aspx and https://msdn.microsoft.com/en-us/library/ms187926.aspx.

Data dictionary object is displayed as another type

Meaning you have a table, but something happens (installing a HF, doing an external compilation, etc) and suddenly it is listed as a view, or a map. Or, you have a map and it is showing now as a table. Or any other combination like this. Just got to experience this today on one of my instances.

Fastest way to reset AX SSRS setting in SSMS

Another useful snippet, just to make the deployment of a TEST to DEV database restore go faster: changing the SSRS settings in AX to match your machine's settings.

Fastest way to restore the AX databases in SSMS

I am doing a lot of AX databases restores weekly, as part of the TEST to DEV machine's update process, and I always use the UI approach, which tends to take several minutes (clicking your way through the screens). Not that it's too much time to loose, but, I figured this is much faster. So, the most simple way, the T-SQL statement:

F.A.Q. for Dynamics AX