Advertisements

Archive

Posts Tagged ‘SQL’

The client and server cannot communicate, because they do not possess a common algorithm


I recently faced an interesting issue when trying to fetch data from third-party API. When trying to connect to the API endpoint, I received the following error message:

“An error occurred while making the HTTP request to https://<API endpoint>. This could be due to the fact that the server certificate is not configured properly with HTTP.SYS in the HTTPS case. This could also be caused by a mismatch of the security binding between the client and the server.” Inner exception was “Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host.”

Solution :

The issue came up when we setup a Thycotic Secret Server on a hardened OS. On the OS TLS 1.0 was disabled for security reasons, however at the moment the used Microsoft SQL server didn´t speak TLS 1.1 or TLS 1.2. So the error message:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 – The client and server cannot communicate, because they do not possess a common algorithm.)

After TLS 1.0 was enabled on the Thycotic Server the installation could be performed without issues. So make sure that your Microsoft SQL environment is up to date and supports TLS 1.1/1.2 if you wish to disable TLS 1.0.

Advertisements

How to use Variable in MySQL Like Clause ?

October 24, 2018 Leave a comment

Here is an example to use SQL variable in the mysql select query like clause

SET @name = 'advancewebsoftware';

SELECT * from `user` WHERE name LIKE CONCAT('%', @name, '%');

Hope this help!

 

Categories: Sql Server Tags: , ,

SQL : Paging in Stored Procedure

November 1, 2017 Leave a comment

Create simple Student table using following script

CREATE TABLE [dbo].[Student]( [ID] [int] NOT NULL, [Name] [varchar](50) NOT NULL) ON [PRIMARY]GO

The OFFSET-FETCH clause is used to fetch specif records. OFFSET-FETCH can be used only with the ORDER BY clause.

Stored Procedure :

SP

Limitations in Using OFFSET-FETCH

  • ORDER BY is mandatory to use OFFSET and FETCH clause.
  • OFFSET clause is mandatory with FETCH. You can never use, ORDER BY … FETCH.
  • TOP cannot be combined with OFFSET and FETCH in the same query expression.
  • The OFFSET/FETCH rowcount expression can be any arithmetic, constant, or parameter expression that will return an integer value. The rowcount expression does not support scalar sub-queries.
Categories: Sql Server Tags: , ,

Function to find dates in current week in mysql


MySQL YEARWEEK() returns year and week number for a given date.

Syntax: YEARWEEK(date_value,Mode)

Parameters or Arguments

date_value
A date or datetime value from which to extract the year and week.
mode
Optional. It is used to specify what day the week starts on. It can be one of the following:

mode Explanation Week Value
0 First day of the week is Sunday 0-53
1 First day of the week is Monday and the first week has more than 3 days 0-53
2 First day of the week is Sunday 1-53
3 First day of the week is Monday and the first week has more than 3 days 1-53
4 First day of the week is Sunday and the first week has more than 3 days 0-53
5 First day of the week is Monday 0-53
6 First day of the week is Sunday and the first week has more than 3 days 1-53
7 First day of the week is Monday 1-53

Example : select YEARWEEK(now());

The following statement will return the year and week number of the date 2016-05-19.

SELECT YEARWEEK(‘2016-05-19’);

 

mysql-yearweek-function

 

Hope this help!

SQL: Condition in Count()

July 7, 2015 1 comment

Condition in count()

SELECT
count(case when EventTypeID =1 then 1 else null end) as TotalTournamentsAttended,
count(case when EventTypeID =2 then 1 else null end) as TotalLeaguesAttended

FROM

[Tables]

 

 

Difference Between Primary Key and Unique Key


PRIMARY KEY UNIQUE KEY
NULL It doesn’t allow Null values.
Because of this we refer
PRIMARY KEY = UNIQUE KEY + Not Null CONSTRAINT
Allows Null value. But only one Null value.
INDEX By default it adds a clustered index By default it adds a UNIQUE non-clustered index
LIMIT A table can have only one PRIMARY KEY Column[s] A table can have more than one UNIQUE Key Column[s]
CREATE SYNTAX Below is the sample example for defining a single column as a PRIMARY KEY column while creating a table:CREATE TABLE dbo.Customer
(
Id INT NOT NULL PRIMARY KEY,
FirstName VARCHAR(100),
LastName VARCHAR(100),
City VARCHAR(50)
)Below is the Sample example for defining multiple columns as PRIMARY KEY. It also shows how we can give name for the PRIMARY KEY:

CREATE TABLE dbo.Customer
(
Id INT NOT NULL,
FirstName VARCHAR(100) NOT NULL,
LastName VARCHAR(100),
City VARCHAR(50),
CONSTRAINT PK_CUSTOMER PRIMARY KEY(Id,FirstName)
)

Below is the sample example for defining a single column as a UNIQUE KEY column while creating a table:CREATE TABLE dbo.Customer
(
Id INT NOT NULL UNIQUE,
FirstName VARCHAR(100),
LastName VARCHAR(100),
City VARCHAR(50)
)Below is the Sample example for defining multiple columns as UNIQUE KEY. It also shows how we can give name for the UNIQUE KEY:

CREATE TABLE dbo.Customer
(
Id INT NOT NULL,
FirstName VARCHAR(100) NOT NULL,
LastName VARCHAR(100),
City VARCHAR(50),
CONSTRAINT UK_CUSTOMER UNIQUE(Id,FirstName)
)

ALTER SYNTAX Below is the Syntax for adding PRIMARY KEY CONSTRAINT on a column when the table is already created and doesn’t have any primary key:ALTER TABLE dbo.Customer
ADD CONSTRAINT PK_CUSTOMER PRIMARY KEY (Id)
Below is the Syntax for adding UNIQUE KEY CONSTRAINT on a column when the table is already created:ALTER TABLE dbo.Customer
ADD CONSTRAINT UK_CUSTOMER UNIQUE(Id)
DROP SYNTAX Below is the Syntax for dropping a PRIMARY KEY:ALTER TABLE dbo.Customer
DROP CONSTRAINT PK_CUSTOMER
Below is the Syntax for dropping a UNIQUE KEY:ALTER TABLE dbo.Customer
DROP CONSTRAINT UK_CUSTOMER

MySQL : coalesce in mysql


DESCRIPTION

The MySQL COALESCE function returns the first non-null expression in the list.

SYNTAX

The syntax for the MySQL COALESCE function is:

COALESCE( expression1, expression2, ... expression_n )

e.g.

SELECT COALESCE(t.col,1) FROM Table t

Above query will return 1 if t.col is null.

Hope this help !

Categories: Sql Server Tags: , , ,