Tag: tsql

Using the OVER clause in T-SQL to SELECT DISTINCT on all columns except one

I have a table with colA, colB, colC, colD. I need to SELECT DISTINCT on all columns except for colA. I’ve found some examples that demonstrate the use of the OVER clause in T-SQL to achieve this; however, they have been pretty specific, and as such, difficult to apply to my situation. Any insight anyone […]

Check if login already added in User Defined Database Role

I am working on a script to add a Login to a user-defined role, where it will take RoleName and Login as input parameters. Checking if already exist before adding, but the following check is returning NULL. USE [MYDatabase]; DECLARE @AddUser NVARCHAR(100) = ‘test’ DECLARE @RoleName NVARCHAR(100) = ‘MYDatabase_ReadOnly’ — User Defined DB ROle IF […]

How to avoid paradox of DDL trigger notifying of dropped triggers

This is more of a philosophical question than a programmatic one. I have been tasked with implementing various and sundry security enhancements to our MS SQL 2014 servers. I have a simple trigger as follows…. CREATE TRIGGER send_db_email_notification ON ALL SERVER AFTER CREATE_DATABASE, ALTER_DATABASE, DROP_DATABASE, CREATE_FUNCTION, ALTER_FUNCTION, CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER AS — do stuff to […]

Find JSON Schema in SQL Server

I have JSON in a field, but I need to check it’s schema before I process it. I need to know if anything has been added or removed from the schema. Is there a way to extract the JSON schema from a JSON string so I can compare it to a known schema? An online […]

Temp Table behavior with stored procedures

SQL Server 2008 I create a local temp table in a stored procedure that then calls another stored procedure that ALTERs the temp table by adding columns to it. SELECT * behaves as expected/desired in both the calling and the called stored procedure: the modified table columns are returned. However, if I attempt to SELECT […]

SQL Database Constraint | Multi-table Constraint

I need to make 2 database constraints that connect two different tables at one time. 1. The total score of the four quarters equals the total score of the game the quarters belong to. 2. The total point of all the players equals to the score of the game of that team. Here is what […]

Make sum of elements when case is lower than specific date

The problem is when the paymentDate is lower than 2005, it must make sum of all element in champs1 + champs2 with payment lower than 2005. But in this code, it makes sum of all champs1 + champs2 even if the payment is higher than 2005 and that is not what i am looking for. […]

Reading XML in SQL which has one to many combination

See below example where I am trying to get result as 2 rows having same ClaimNo with different aliasType and aliasName. Current result looks like this- enter image description here DECLARE @r TABLE (AliasesValue XML) INSERT INTO @r SELECT ‘<aliases> <ClaimsNotificationAddRq><Claim><ClaimNo>123</ClaimNo> <alias> <aliasType>AKA</aliasType> <aliasName>Pramod Singh</aliasName> </alias> <alias> <aliasType>AKA</aliasType> <aliasName>Bijoy Bora</aliasName> </alias></Claim></ClaimsNotificationAddRq> </aliases> ‘ SELECT c.query(‘data(Claim/ClaimNo)’), […]

Select part of duration that occurs in specific hour of day

In SQL Server, I have a table of processes with a starttime and an endtime, from which I can calculate a duration using DATEDIFF. Name StartTime EndTime ———————————————— process1 2016-10-10 11:10 2016-10-10 11:20 process2 2016-10-10 11:40 2016-10-10 12:30 How can I select the timespan of the process duration that occured in specific hours of the […]

Update fails for a particular Unicode character

I am a SQL Server DBA and I got a very strange complaint that an UPDATE statement is failing. create table #temp(id int, c nvarchar(10)) alter table #temp add constraint check_empty check (isnull(c,”)<>”) insert into #temp values(1,N’ABC’) –Success update #temp set c=N’䬠䏊’ where id=1 –Fail update #temp set c=N’䬠䏊.’ where id=1 –Success insert into #temp […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.