Understanding shrinking the log If you need to recover disk space from the transaction log file, consider shrinking the log file. Shrinking logs helps after you perform an action that creates a large number of logs. You can only shrink the log if there is free space on the log file. Shrink the transaction log … Continue reading sql server – Free disk SPACE USING SHRINK
Category: SQL Server
AX / D365FO – Create index with “Included columns”
If you want to create an Index with Included columns just create the index, add the field and set the "Included column" property to YES
SQL SERVER – Concatenates rows fields into a single string with STRING_AGG()
The STRING_AGG() is an aggregate function that concatenates rows of strings into a single string, separated by a specified separator. It does not add the separator at the end of the result string. This example uses the STRING_AGG() function to generate lists of emails of customers by the city: SELECT city, STRING_AGG(email,';') email_list FROM sales.customers GROUP BY city; This is the … Continue reading SQL SERVER – Concatenates rows fields into a single string with STRING_AGG()
SQL SERVER – DROP TABLE IF EXISTS
SQL Server 2016 introduced a simply way of dropping a table with DROP IF EXISTS. Instead of having to look and see if whether or not the table exists with one T-SQL statement then running DROP TABLE if it does, and ignored if not, executing DROP TABLE IF EXISTS will do both for you in … Continue reading SQL SERVER – DROP TABLE IF EXISTS
SQL SERVER – enlarge FOnts results in SSMS
From the SSMS Tools menu, choose Options. Then in the search box type 'FON', choose 'Font and Colors' and from the drop down list labeled 'Show settings for : ' choose 'Grid Results'. Enlarge the font size. Restart SSMS and your view will be much better.
AX / D365FO – How to identify the SQL Server version and edition
To identify the SQL Server version and edition running on your machine open SSMS, access your Master DB and write the following statement select @@version You'll get a result like that. In my case I have a Developer Edition Microsoft SQL Server 2016 Microsoft SQL Server 2016 (SP2-CU16) (KB5000645) - 13.0.5882.1 (X64) Jan 25 2021 … Continue reading AX / D365FO – How to identify the SQL Server version and edition
AX / D365FO – SQL Server password for user “axdbadmin” in locally deployed D365 for Operations environments
By default the password for user axdbadmin is AOSWebSite@123
AX / D365FO – How TO find a text inside a stored procedure – SQL SERVER
To search for stored procedures containing a specific text just launch this select statement SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%MyText%' AND ROUTINE_TYPE='PROCEDURE' or better you can try this SELECT [Scehma] = schema_name(o.schema_id), o.Name, o.type FROM sys.sql_modules m INNER JOIN sys.objects o ON o.object_id = m.object_id WHERE m.definition like '%MyText%' This will be … Continue reading AX / D365FO – How TO find a text inside a stored procedure – SQL SERVER
AX – D365FO – Connect to UAT database through SSMS (SQL Server management studio)
The only way to get access to UAT Sandbox SQL Server database throught SSMS is to make a request from LCS. This is access is only for 8 hours, after that time you need to request access again. So Login to LCS Go to UAT environment -> Full details. You should see request access controls … Continue reading AX – D365FO – Connect to UAT database through SSMS (SQL Server management studio)
SQL SERVER – How to : “The database could not be exclusively locked to perform the operation”
If you are facing this error : "The database could not be exclusively locked to perform the operation. (Microsoft SQL Server, Error: 5030)" when you try to rename SQL server database you need to temporary switch from Multi User mode to single user mode, then rename the database and then set it back to Multi … Continue reading SQL SERVER – How to : “The database could not be exclusively locked to perform the operation”
AX / Dynamics 365 For Finance and Operations blog 