Author: Latheesh NK

SQL Azure

Azure Data Studio

Introduction

Azure Data Studio (ADS) is a cross-platform database tool for those who work in On-Premise or Cloud platforms on Windows, macOS, and Linux.

To download: https://docs.microsoft.com/en-us/sql/azure-data-studio/download-azure-data-studio?view=sql-server-ver16

Developer Friendly Features of ADS

Problems section of ADS provides insights into the query and prompts us with the resolutions in many cases.

The below screenshot is a comparison between ADS and SSMS and it is clearly visible, while you write the code itself, ADS “Problem” section provided information that there is a KEY word missing in the 5th row and 9th column.

SQL

ANY, ALL & SOME Operators in SQL Server

ANY is a SQL operator that used to compare a scalar value with a set of single column resultset from a subquery. If any one of the value in the resultset is satifying the operator condition to the scalar value, then it return TRUE, else FALSE.

--Table Creation
create Table EmpDetails
(
	EmployeeID int,
	EmployeeName Varchar(200),
	Grade Int
)
CREATE tABLE EmpYears
(
	EmployeeID int,
	YearOfExp Int
)

--Data Population
Insert into EmpDetails Values 
(1,'Employee1',3),(2,'Employee1',4),(3,'Employee1',5),
(4,'Employee1',6),(5,'Employee1',7),(6,'Employee1',8),
(7,'Employee1',9)

Insert into EmpYears Values 
(1,3),(2,5),(3,7),(4,10),
(5,15),(6,20),(7,25)

--Get the employees with experiences more than 10 years
Select * From EmpDetails 
    where EmployeeID = ANY(Select EmployeeID From EmpYears where YearOfExp>10)

Drop Table EmpDetails, EmpYears

SOME operator is also working similar to ANY, the below returns same output as ANY.

--Get the employees with experiences more than 10 years
Select * From EmpDetails 
    where EmployeeID = SOME(Select EmployeeID From EmpYears where YearOfExp>10)

ALL operator in SQL Server returns TRUE if *all* the resultset from subquery is true to the operator condition to the scalar value.

If you enjoyed this blog post, feel free to share it with your friends!

Errors and Exceptions, SQL, SQL Server

Exception: “Invalid parameter 1 specified for datediff.” in SQL Server

Problem Statement:

One of the view creation scripts in our deployment failed continuously with the below exception:

Invalid parameter 1 specified for datediff.
Incorrect syntax near the keyword ‘AS’.

The root cause of this error is as below.

FLOOR((DATEDIFF("W",ml.column1,ML.column2)+1)/7) AS aliasname

Solution:

FLOOR((DATEDIFF(W,ml.column1,ML.column2)+1)/7) AS aliasname

The change is that the datepart has been modified to remove the double quote.

NO SQL, Python

How to identify duplicate lines in a text file using Python

Here is a short program in Python to identify the count of duplicate lines in a text file.

import tkinter as tk
from tkinter import filedialog
from collections import defaultdict
import pandas as pd
import collections
from pathlib import Path
import os

root= tk.Tk()

canvas1 = tk.Canvas(root, width = 800, height = 300)
canvas1.pack()

label1 = tk.Label(root, text='Log Analyser')
label2 = tk.Label(root, text='Import a file...')
label1.config(font=('Arial', 20))
label2.config(font=('Arial', 10))
canvas1.create_window(400, 50, window=label1)
canvas1.create_window(200, 180, window=label2)

def getLogFile ():
      global df

      import_file = filedialog.askopenfilename()
      Counter = 0

      with open(import_file, "r+") as f:
            d = f.readlines()
            f.seek(0)
            entries = Path(import_file)
            fileabspath = os.path.abspath(import_file)
                        
            fw= open(fileabspath.replace(entries.name,"Duplicate_Log_Info.txt"),"w+")
            
            counts = collections.Counter(l.strip() for l in f)
            for line, count in counts.most_common():
                #print (line, "|"+str(count))
                fw.write(line + "|"+str(count) + "\n")
            label3 = tk.Label(root, text=entries.name + ": Import is successful, Please check the output file - "+ fw.name + ".")
            label3.config(font=('Arial', 10))
            canvas1.create_window(400, 220, window=label3)
            f.close()
            fw.close()

            
browseButton_Excel = tk.Button(text='Choose a file...', command=getLogFile, bg='green', fg='white', font=('helvetica', 12, 'bold'))
canvas1.create_window(400, 180, window=browseButton_Excel)

button3 = tk.Button (root, text='Close', command=root.destroy, bg='green', font=('helvetica', 11, 'bold'))
canvas1.create_window(500, 180, window=button3)

root.mainloop()

Output:

If you enjoyed this blog post, feel free to share it with your friends!

Linked Server, SQL

Error Message: Server is not configured for RPC in SQL Server

Recently one of my colleague reached out to me with an error message as : “Server is not configured for RPC”

This a typical issue with Linked Server. When we configure a linked server, we need to set up the right values for RPC & RPC Out . We can see these values in SSMS -> Right click on Linked Server -> Properties -> Server Options as in the below screen shot.

RPC / RPC OutRPC stands for Remote Procedure Call and allows you to run stored procedures on the Linked Server.  RPC enables Remote Procedure Calls from the specified server and RPC OUT enables Remote Procedure Calls to the specified server.

Hope this helps; If you enjoyed this blog post, feel free to share it with your friends!