How to Check SQL Server Instance Connectivity from the application server to database server for a specific user

In this article, I will explain

Check the connectivity between Application Server and SQL Server Instance in Database Server.


Scenario

If you are working in an implementation team, you will walk through the following:

  • Check the connectivity between the application server and database server.
  • Make sure that the SQL Server port (default is 1433) is opened and accessible through the application server.
  • Make sure that the web site application pool account can access a specific database.
  • Make sure that the SQL Server account can access a specific database.

Check the connectivity between the application server and database server.

Continue reading “How to Check SQL Server Instance Connectivity from the application server to database server for a specific user”

Advertisements

SSRS Subscription Error: SQL Server Agent was unable to start

When I tried to start SSRS subscription to send weekly reports by e-mail where the prerequisites for SSRS subscription is to be sure that SQL Server Agent is working,so that I opened SQL Server Management Studio to check SQL Server Agent status but unfortunately it stopped !

sqlagent

It’s ease of cake  to start SQL Server Agent by right click and select start ! but In my case when I tried to start it I got the following information dialog

unabletostartagent

Cause: Continue reading “SSRS Subscription Error: SQL Server Agent was unable to start”

Convert Decimal Numbers to Money with cents / without cents in SQL

In this article, I will demonstrate

How to Convert Decimal Numbers to Money with cents / without cents in SQL.


Scenario

I have a decimal number like  71567536.100000, I need to convert it to money format

  • with cents to be like this 71,567,536.10 ر.س
  • without cents to be like this  71,567,536 ر.س

Convert Decimal To Money With Cents In SQL Server

You can use one of the below quires


SELECT STUFF( convert(varchar, convert(money,71567536.100000),1) , 1, 0,'.') + N' ر.س ' as 'Budget'


SELECT convert(varchar(100), cast(71567536.100000 as money), 1)+ N' ر.س ' as 'Budget'

Output

Convert Decimal To Money With Cents In SQL Server

Convert Decimal To Money Without Cents In SQL Server

You can use one of the below quires


SELECT parsename( convert(varchar(100), cast(71567536.100000 as money), 1),2) + N' ر.س ' as 'Budget'

 SELECT replace( convert(varchar, cast(floor(71567536.100000) as money),1), '.00', '') + N' ر.س ' as 'Budget' 

Note: The second statement faster than the first with 21 NS.

Convert Decimal To Money Without Cents In SQL Server


Applies To
  • SQL Server.

How to convert Gregorian dates to Hijri date in SQL Server 2008 – 2012

In this hint, I will explain How to

Convert Gregorian dates to Hijri dates format in SQL Server.


Convert Gregorian dates to Hijri dates format in SQL Server 2012 Continue reading “How to convert Gregorian dates to Hijri date in SQL Server 2008 – 2012”

Format the date column with AM/PM and without second in SQL Server

At previous hint, I have explained How to convert Gregorian dates to Hijri dates format in SQL Server.

 At this hint, I will explain How to show the date field with data and time format with the following :

  • I need to format the time section to show AM/PM.
  • I need to show the time section without seconds.
dd/MM/yyyy HH:mm AM/PM

SQL_R4.PNG

Steps Continue reading “Format the date column with AM/PM and without second in SQL Server”

Saving Table Changes is not permitted in SQL Server Management Studio

I am trying to save changes in a table via SQL Management Studio.

CreateTable

But unfortunately, I got the following error:

The changes you have made require the following tables to be dropped 
and re-created.You have either made changes to a table that can’t be re-created 
or enabled the option Prevent saving changes that require the table 
to be re-created

3

Cause: Continue reading “Saving Table Changes is not permitted in SQL Server Management Studio”

The database could not be exclusively locked to rename a database in SQL Server Management Studio

I am trying to rename a database in SQL Server Management Studio.but unfortunately, I got the following error:

The database could not be exclusively locked to perform the operation

RenameDB

Cause: Continue reading “The database could not be exclusively locked to rename a database in SQL Server Management Studio”