How to drop SQL Server database currently in use and in Single user mode, MS SQL.

Hi All,

Working in MS SQL, you must have faced below error at least once. 

Issue: How to drop SQL Server database currently in use and in Single user mode.

Error:

"changes to the state or options of database 'AxDB' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it."

Solution:

This built-in stored procedure shows all connections to a database

exec sp_who2

And this query kills a connection to the database, for example connection SPID #53

kill 53

Thank you!

Avoiding "Divided by zero" error, X++

Hi,

Two ways to avoid the error "Divided by Zero".

int    a,b,c;

a = 2;

b = 0;

if(b)

    c = a / b;

else

    print "Cannot divide by zero"

//Output is "Cannot divide by zero" because "b" is zero so else statement will run.

(OR)

c  = a / minOne(b); 

//Output is C = 2, reason given below.

Function minOne() returns non zero values. If "b" is zero then it returns one.

Thank you,

Add or Minus Days in DateTime or Date Datatype, X++

Hi,

below you will find ways to add/minus days in "DateTime" or "Date" Datatype.

Example 1: Days add/minus in "DateTime" Datatype.

{

 utcDateTime         todayLessOneDay;

// Get the actual UTCDateTime based on the current system

todaysDateTime = DateTimeUtil::utcNow();

// Convert it to a string, just to show in on the info log

info(DateTimeUtil::toStr(todaysDateTime));

// Now less a day

todayLessOneDay = DateTimeUtil::addDays(todaysDateTime, -1);

// And Info it out again

info(DateTimeUtil::toStr(todayLessOneDay));

}

Example 2: Days add/minus in "Date" Datatype.

{

  TransDate transDate = today();

  ;

  transDate++;

  print transDate - 2;

  pause;

}

Add multiple Query Value to one Query Range - X++

 Hello,

In this post you will find a code sample to run multiple Query Value to one Query Range.

You have two options:

Option 1: Add multiple ranges.

QueryBuildDataSource qbds = q.dataSourceTable(BOMTable);

QueryBuildRange qbr;

while (...)

{

    qbr = qbds.addRange(fieldNum(BOMTable, BOMId));

    qbr.value(queryValue(BOMVersion.BOMId));

}

Option 2: Add multiple values to one range separated by comma.

QueryBuildRange qbr = q.dataSourceTable(BOMTable).addRange(fieldNum(BOMTable, BOMId));

container c;

while (...)

{

    c+= queryValue(BOMVersion.BOMId);

}

qbr.value(con2str(c));

SSRS Date time Expression, AX/D365FO

SSRS Date Time Expressions

Date Time Functions of SSRS:

Below are Data time functions which are used in SSRS expressions:

To get current date and time according to system: 

Time:

=Format(CDate(Globals!ExecutionTime), "MM-dd-yyyy hh:mm.ss") 

Date:

=FORMAT(Cdate(today), "dd-MM-yyyy")

Today() - Returns or sets a Date value containing the current date according to your system:

=Today()

="Tomorrow is " & DateAdd("d",1,Today())

="Tomorrow is " & DateAdd(DateInterval.Day,1,Today())

Now() - Returns or sets a Date value containing the current date according to your system.

=Now()

="This time tomorrow is " & DateAdd("d",1,Now())

="This time tomorrow is " & DateAdd(DateInterval.Day,1,Now())

MonthName - Returns a String value containing the name of the specified month.

=MonthName(10,True)

=MonthName(Month(Fields!BirthDate.Value),False)

="The month of your birthday is "&MonthName(Month(Fields!BirthDate.Value))

WeekDayName - Returns a String value containing the name of the specified weekday.

=WeekdayName(2,True,0)

=WeekDayName(DatePart("w",Fields!BirthDate.Value),True,0)

=WeekDayName(DatePart(DateInterval.Weekday,Fields!BirthDate.Value),True,FirstDayOfWeek.System)

Year  - Returns an Integer value from 1 through 9999 representing the year.

=Year(Fields!BirthDate.Value)

To format Date values through expressions:

=Format(Today(),"dd-MM-yyyy") --- 23-10-2015

=Format(today(),"dd/MM/yyyy") --- 23/10/2015

=Format(today(),"MMM-dd-yyyy") --- Oct-23-2015

=Format(today(),"MMM-dd-yy") --- Oct-23-15

=FORMAT(Today(),"M/d/yy") ---10/23/15

=FORMAT(Today(),"MM-dd-yyyy") ---10-23-2015

=FORMAT(Today(),"MMM-dd-yyyy") Oct-23-2015 

=FORMAT(Today(),"MMMM dd, yyyy") ---October 23, 2015 =FORMAT(DateField,"MMM dd, yyyy hh:mm:ss") ---Oct 23, 2015 01:43:33 =FORMAT(DateField,"MMM dd, yyyy HH:mm:ss") ---Oct 23, 2015 13:43:33 =FORMAT(DateField,"MMM dd, yyyy HH:mm:ss.fff") ---Oct 23, 2015 13:43:33.587

=FORMAT(DateField,"MMM dd, yyyy hh:mm:ss tt") ---Oct 23, 2015 01:43:33 PM

To add/substract date or time(day, month, year, sec etc.) with given date field we can use DateADD function in SSRS expression, which returns a Date value containing a date and time value to which a specified time interval has been added:

=DateAdd(DateInterval.Month, 6, Today())

=DateAdd("d",3,Fields!BirthDate.Value)

=DateAdd(DateInterval.Day,3,Fields!BirthDate.Value)

DateDiff - function returns a Long value specifying the number of time intervals between two Date values.

=DateDiff("yyyy",Fields!BirthDate.Value,"1/1/2010")

=DateDiff(DateInterval.Year,Fields!BirthDate.Value,"1/1/2010")

DatePart - Returns an Integer value containing the specified component of a given Date value.

=DatePart("q",Fields!BirthDate.Value,0,0)

=DatePart(DateInterval.Quarter,Fields!BirthDate.Value,FirstDayOfWeek.System,FirstWeekOfYear.System)

To get first day of current Week:

=DateAdd("d",-DatePart(DateInterval.WeekDay,Today,0,0)+1,Today)

To get first day of current Month:

=DateAdd("d",-(Day(today)-1), Today)

=DateSerial( year(today()), month(today()), 1)

Error: The report server isn’t configured properly, SSRS report deployment error.

Error:

The service is not available.

The report server isn’t configured properly. Contact your system administrator to resolve the issue. 

System administrators: The report server can’t connect to its database because it doesn’t have permission to do so. 

Solution:

Use Reporting Services Configuration Manager to update the report server database credentials.

This issue "The report server can’t connect to its database because it doesn’t have permission" might occur if the account that the report server uses to connect to the report server database is:

• Disabled or Locked.
• Its password has changed recently.
• It doesn't have owner permission on the Report Server databases (its permission has revoked).
• This Report Server account can be one of the below types:
• Windows domain user account.
• SQL Server account.
• The same service account that used to run the Power BI Report Server service.

And, you can find the current report server account login type at

Report Server Configuration Manager > Database > current report server database credential:

Update your current credential or add admin credential.

Row Number or Serial Number for grid - SSRS Report expression

Below expression will give you the serial number to the grid:

=RunningValue(CountDistinct("YourTableName"),Count,"YourTableName")

or

=RowNumber(Nothing)