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

In some situation, you may need to check the SQL Server Instance connectivity from the application server to database server for a specific user especially when you don’t have access to SQL Servers on your farm based on your company policy.

You may try to perform Ping cmdlet for the SQL Server IP and Telnet to SQL TCP port.but that means There’s a network connectivity between two servers and you can reach the database server from the application server.

But what about if you need to test connectivity to SQL Server Instance for a specific user without using SQL Server Management Studio?

In this case, you can test connectivity to SQL Server Instance by creating a data link UDL file as I have mentioned below:

Steps: Continue reading “How to Check SQL Server Instance Connectivity from the application server to database server for a specific user without using SQL Server Management Studio”

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 hint, I will demonstrate How to Convert Decimal Numbers to Money with cents / without cents in SQL.

Example:

  • 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 ر.س

Steps: Continue reading “Convert Decimal Numbers to Money with cents / without cents in SQL”

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 fromat in SQL Server.

With respect to SQL Server 2008

SELECT convert(nvarchar(10),GetDate(),131)

Output

16/08/1435

if you need to show the month name you can replace 131 with 130

SELECT convert(nvarchar(15),GetDate(),130)

Output

16 شعبان 1435

Note : you should use nvarchar to show Arabic month name in correct.

With respect to 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”