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()

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 – 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”