.NET Core 2.0 brings a lot of improvements to the system, and it brings a lot of pain to the developers as well. I had a lot of problems upgrading .NET Core 1.x apps to .NET Core 2.0; I yet have to feel the promise it makes about performance and so, but let us see how to upgrade our existing applica

T-SQL

C#7.0

SQL

.NET-Core

ASP.NET-Core

entity-framework

Dev

FOR XML Basics (AUTO Mode): 2 of 4

2 Jun 2017 by AhsanAhmed

A brief introduction on how to use FOR XML clause in AUTO mode in Microsoft SQL Server to return data in XML format

Debug Stored Procedures in Visual Studio and SSMS

18 Feb 2016 by Kuv Patel

Debug stored procedures in Visual Studio and SQL Server Management Studio.

How do I use "between" in SQL Case ?

29 Sep 2015 by Wendelius

You can't use case just like that but there are several ways to do the comparison. For example:SELECT COUNT (DISTINCT Table.users) FROM Table WHERE Table.Age BETWEEN CASE @cat WHEN 1 THEN 18 ELSE 21 END AND CASE @cat WHEN 1 THEN 20 ELSE 42 END;ADDITION:If...

How to Publish ASP.NETCore MVC Web Application to IIS in Less Than 5 Minutes

1 Nov 2016 by O.Nasri

A good tutorial in how you can publish your ASP.NETCore MVC web application to IIS.

How to extract overlapped polygons and calculate the overlapped area and write an API for it?

21 Oct 2017 by mahmood kabi

extract overlapped polygons and calculate the overlapped area and write an API for it

Converting a DataTable to an INSERT script for SQL in C#

7 Mar 2011 by Orcun Iyigun

SO here is what you need to do;You can do it one row at a time in to the database.Mainly this method is just opening a connection to the access db, going through each row in the gridview datasource (if you create the datasource on the fly you might need to recreate the table at the...

Displaying price with currency symbol

23 Nov 2011 by Dylan Morley

Don't do this in SQL, it's not the job of the database to format data. Instead, format the data in whatever medium you are presenting this information. If you're presenting the information in a web app, you could apply the formatting at that stage. It's the job of the presenter to format...

T-SQL

SQL

Using SCOPE_IDENTITY with CommandBuilder and DataAdapter

23 Nov 2011 by Antonino Porcino

How to use T-SQL function SCOPE_IDENTITY() to retrieve inserted rows identity values with CommandBuilders and DataAdapters

The Stored Procedure Framework Now Supports Dynamic Fields within Multiple Recordsets

1 Aug 2016 by dibley1973

The stored procedure framework now supports dynamic fields within multiple recordsets

Automating the SQL Database Mirroring Setup

30 Dec 2019 by Dathuraj Pasarge

Database mirroring setup without any DBA effort - on every new database creation

Improving Backup Performance

1 Oct 2018 by Satnam Singh CapGemini

Improving backup performance

ASP.NET Web Component for editing SQL tables

29 Nov 2012 by RovenetBill

ASP.NET c# component for editing SQL tables with plug-in column format adapter architecture.

How to Pass Calculated Columns in the WHERE & GROUP BY clause in T-SQL..?

13 Jun 2013 by Bhushan W. Juare

Passing calculated columns in the Where and Group by clause in T-SQL

T-SQL

How to Calculate the Length of the Largest Text Value in Every Table Column

22 Nov 2015 by Daniel Miller

This tip shows how to display the length of the longest string value in every table and column in your database.

T-SQL

SQL

COALESCE in SQL Server

15 Nov 2015 by sandeepmittal11

COALESCE in SQL Server

T-SQL

SQL

Unattended installation of SQL Server 2012

11 Jul 2016 by Jesus Carroll

Despite the references of several sources of unattended installation of Microsoft SQL Server Database Engine, in this article describes a script tested in real production environments and it can be used with minimal or - perhaps - no modifications.

TrimChar to Remove Desired Character

30 Sep 2016 by Wendelius

TrimChar function which removes occurences of the desired character from both ends of a string.

T-SQL

SQL-Server

Dev

Data Version Control on MS SQL Server (using a generic trigger)

4 Oct 2016 by Martin Lachman

How to create a system on MS SQL Server for detection of actual changed records in a database table.

Amount to Words

18 Dec 2016 by NightWizzard

Convert amounts to their spoken equivalents

FOR XML Basics (EXPLICIT Mode): 3 of 4

1 Jun 2017 by AhsanAhmed

A brief introduction on how to use FOR XML clause in EXPLICIT mode in Microsoft SQL Server to return data in XML format

spSearchTables: Search and Find Tables or Columns by Name or Value

21 Nov 2023 by Federico Di Marco

spSearchTables: a helper T-SQL stored procedure for digging into (large) databases

Default keyword in SQL Server

12 Jun 2011 by Mel Padden

Default keyword in SQL Server

T-SQL

SQL-Server

Parameter

Simple TSQL and C# Entity Generator for S#arp Architecture

20 Sep 2011 by Raymund Macaalay

Simple TSQL and C# entity generator for S#arp architecture

T-SQL

Enter formated data in sql like 001,003,087,234

31 Aug 2012 by Wendelius

A bit different answer. If you're actually storing numbers, don't format them in a table, never! Instead store them as numbers and handle all the formatting requirements on the client side.The database is meant to store... well the data. Not the formatted values. The best results are...

T-SQL

Invalid Column Name TSQL..

1 Nov 2012 by fjdiewornncalwe

SELECT * FROM Users WHERE Username = 'kimS'Literal strings always need to be enclosed in single quotes. Cheers.

SQL

T-SQL

ASP.NET

How to eliminate XML Escape Characters in "FOR XML PATH"?

17 Feb 2013 by gvprabu

Hi All,At last I find the solution for this....SELECT STUFF((SELECT ','+Name AS [text()] FROM #Test FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'') AS 'NameList'Check this :-)Regards,GVPrabu

T-SQL

How to Get Words Separated in a String Concatenated by Delimiter

14 Apr 2014 by Ankur .K. Vishwakarma

This tip shows you a T/SQL function to split a string concatenated by a delimiter.

How to compare 2 records of same table?

22 Nov 2014 by Tomas Takac

As Andre pointed out your input data seems to be incorrect. I took the liberty of changing the modified time stamps for the last two records. Now to get the results you need, first assign a sequence number based on the time stamp. Descending order will help you identify the current schedule...

T-SQL

SQL

SQL-Server-2008R2

How Do I Loop Over Every Row Of A Sql Query Result Set In Sql Server?

11 Feb 2015 by Abhinav S

You can use a cursor - https://msdn.microsoft.com/en-IN/library/ms180169.aspx[^].

T-SQL

SQL

SQL-Server-2008R2

separate multiple values for the same column

21 Apr 2015 by CHill60

First you need a function to split that column - there are loads out there but I like this one from sqlservercentral.com[^]CREATE FUNCTION [dbo].[fnSplitString] ( @string NVARCHAR(MAX), @delimiter CHAR(1) ) RETURNS @output TABLE(splitdata NVARCHAR(MAX) ) BEGIN ...

T-SQL

SQL-Server-2008R2

INNER JOIN two views from SQL SERVER

28 Jul 2015 by Suvendu Shekhar Giri

I don't see any problem doing that.Something like following should work.Your first viewCREATE VIEW [TestView1]ASSELECT 1 AS Id, 'Test 1' AS ValueUNIONSELECT 2,'Test 2'GOOUTPUT 1Id Value______________1 Test 12 Test 2Your second viewCREATE VIEW...

T-SQL

SQL

SQL-Server-2012

Technical pan in query of sql server

29 Jul 2015 by Kornfeld Eliyahu Peter

To open up you WHERE clause...STATION_ID = 'AIN SEBOU' AND SENSORNAME = 'NIVEAU')OR(STATION_ID = 'AIN SEBOU' AND SENSORNAME = 'PLUIE1HR')OR(STATION_ID = 'AZZABA' AND SENSORNAME = 'NIVEAU')OR(STATION_ID = 'AZZABA' AND SENSORNAME = 'PLUIE1HR')Where the last line actually not as you...

How do I use "between" in SQL Case ?

29 Sep 2015 by Suvendu Shekhar Giri

You can use a tricky solution as follows-SELECT COUNT (DISTINCT Table.users) FROM Table WHERE (@cat1 OR (Tabl.age between 18 and 20))AND (@cat=1 OR (Tabl.age between 21 and 42))For the first condition,It will check if @cat not equals to 1 then skip the 2nd part of the expression...

How do I use "between" in SQL Case ?

29 Sep 2015 by Maciej Los

If you want to count users based on age category, you have to create helper-table to define age categories. Then you'll be able to join data from users table. Have a look at example:DECLARE @users TABLE(UserId INT IDENTITY(1,1), UName VARCHAR(30), BirthDate DATE)INSERT INTO @users(UName,...

Decrypt SQL Server Objects

7 Dec 2016 by Kuv Patel

Decrypt Stored Procedures, Views, Functions, and Triggers in SQL Server

OVERachieving Progressive Sums in T-SQL

21 Apr 2016 by Emmanouil Gimissis MSc ICSD

Calculating progressive sums in T-SQL

T-SQL

SQL

SQL-Server

SQL Server Grant Permission to Particular Table

27 May 2016 by Shashangka Shekhar

In this post, we will create a new user in SQL Server Management Studio (SSMS) & allow access to that user to perform a specific operation.

T-SQL

Always Encrypted Feature in SQL Server 2016

19 Oct 2016 by Manjuke Fernando

Always encrypted feature in SQL Server 2016

Create Query Shortcuts in SQL Server Management Studio

31 Jan 2017 by saddam_msp

Shortcuts to execute your query faster while working or debugging T-SQL statement

T-SQL

SQL

SSMS

R-script Migration to SQL Server 2016

13 May 2017 by Evgeniy Sukhikh

Issues and solutions taken while moving R from cmd to sp_execute_external_script

T-SQL

SQL

How do I catch the line with error data while bulk insert operation?

12 Sep 2017 by OriginalGriff

No, there isn't an indication of which row is at fault: you would have to look for yourself and find it. Or better, read the data yourself, process it into valid data only, and pass it as a datatable or similar: SqlBulkCopy.WriteToServer Method (DataTable) (System.Data.SqlClient)[^]

T-SQL

Pay Attention to What "Script...To" Generates

22 Sep 2017 by #realJSOP

It doesn't always happen the way you expect.

Issues after Upgrade to SQL Server 2017 – Cannot Bulk Load Data Into the Table with Primary Key Constraint on it

11 Jul 2018 by Satnam Singh CapGemini

Cannot Bulk Load Data Into the Table with Primary Key Constraint on it

How to Create and Use a Self-referencing Hierarchical Table

8 Jan 2020 by E. Scott McFadden

This article explains how to create and use a self referencing key in a SQL Server Table.

Sql CTE recursive or cursor ?

30 Apr 2020 by Richard Deeming

There might be a cleaner way to do this, but this seems to work: WITH cteRawData As ( SELECT [From], [To], '/' + CAST([From] As varchar(max)) + '/' + CAST([To] As varchar(max)) + '/' As [Path], 1 As Depth ...

T-SQL

SQL

Dismantling Reggie

8 Nov 2021 by honey the codewitch

Deep dive some advanced source generation in a real world application

How do I use a set-based approach to a complicated SQL insert

24 Nov 2021 by Richard Deeming

Something like this should work: DECLARE @Date date = GETDATE(); WITH cteStageCounts (stage_count) As ( SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 ), cteStages (NextStage,...

T-SQL

SQL

Error the index entry of length 1890 bytes for the index 'IDX_estrat' exceeds the maximum length of 1700 bytes for nonclustered indexes

25 Nov 2021 by OriginalGriff

Read the error message: Quote: Warning! The maximum key length for a nonclustered index is 1700 bytes. The index 'IDX_EStrat' has maximum length of 5000 bytes. For some combination of large values, the insert/update operation will fail. The...

T-SQL

SQL

SQL-Server

T-SQL Trigger

14 Dec 2010 by rawilken

CREATE TRIGGER Rodd.trgCharterUpdateON CHARTERAFTER INSERT, UPDATE, DELETENOT FOR REPLICATIONASIF @@ROWCOUNT > 0 BEGIN DECLARE @ActionType int , @RowCount int , @AC_NUMBER nvarchar(50) , @FuelCharge money = 0.79 -- VARIABLE PER GALLON...

T-SQL

a dynamic way to extract the number of categories from a table

12 Jan 2011 by Espen Harlinn

Use Common Table Expressions:http://www.4guysfromrolla.com/webtech/071906-1.shtml[^]orBuilding hierarchy using Recursive CTE[^] by Hiren SolankiRegardsEspen Harlinn

SQL Server 2008 Select and Filtering data

18 Oct 2011 by kiran dangar

Hi, RANK() might be useful in this scenario.Please try below query..SELECT dbo.Owners.HaulierId, dbo.Vehicles.VehicleId, dbo.Vehicles.Code, dbo.Owners.Name, dbo.VehicleOwnershipHistory.OwnedFromDate, RANK() over(partition by...

T-SQL

Displaying price with currency symbol

23 Nov 2011 by Nazmi Altun

I've an Item table like;Item Name Price Currency CurrencySymbol--------- ----- -------- --------------ItemA 500 USD $ItemB 100 EURO €ItemC 150 USD ...

T-SQL

SQL

ALTER SP WHILE running

6 Dec 2011 by Wendelius

Well basically nothing until the procedure is not in use anymore. After that the ALTER command is actually carried out and the procedure is changed.Yes, you can issue the ALTER statement even if the procedure is in use at the moment.

T-SQL

SQL

SQL-Server

ALTER SP WHILE running

6 Dec 2011 by Mehdi Gholam

Yes, new calls will go to the changed procedure and the current executing procedure will continue as the older version.

T-SQL

SQL

SQL-Server

The SQL Server Rand Function

8 Feb 2012 by Pascal Ganaye

Reproducing the SQL RAND() function in C#

T-SQL

SQL-Server

updating column data in sql server

14 Mar 2012 by Herman<T>.Instance

assuming admin1code is of datatype (n)varchar Update Countryset admin1code = right('00'+ admin1code, 2)

Divide By Zero Error when passing a parameter in Sql Server

31 Jul 2012 by Sandeep Mewara

You can handle it using NULLIFThis is where NULLIF comes to the rescue. In the query below, the divisor will be changed to NULL if it was 0, setting the result of the entire calculation to NULL instead of causing a runtime error.SELECT IdCol, Col1 / NULLIF(Col1 + Col2, 0) AS Col1Ratio FROM...

T-SQL

SQL-Server-2008

SQL-Server

Create a Trigger in insert operation

9 Aug 2012 by Mohamed Mitwalli

Hi , Check this create trigger newcall on callbookafter insertasbegindeclare @new int --here what you missedselect @new=(select max(new) from tblMRM where month=month(getdate()) and year=year(getdate()))update tblMRM SET new=@new+1 where month=month(getdate()) and...

T-SQL

MS-SQL Update Trigger (multiple rows), without a Cursor ?

26 Sep 2012 by Santhosh Kumar Jayaraman

why cant you just say insert into newtable( field1,field2) select field1,field2 from inserted

Need to reorder a table!

16 Oct 2012 by fjdiewornncalwe

It is good that you are friends with your instructor. :)Since you have no logical basis for ordering the data then you are correct that an order by will not work, unless there is another column in the table that you have neglected to show us.You could examine the UNION statement as a...

T-SQL

Build a Date Function

26 Nov 2012 by André Kraak

This is an alternative for "Build a Date Function"

T-SQL

SQL

Find the first Day (01) and the Last Day (28) for a specified month in SQL Function (UDF), as an example (20091207)?

3 Mar 2013 by gvprabu

Hi ,Try like this...DECLARE @Date VARCHAR(10)='20120404', @FirstDate VARCHAR(10), @FirstDay INT, @LastDay INTSELECT @FirstDate=SUBSTRING(@Date,1,6)+'01'SELECT @FirstDay=1, @LastDay=DATEDIFF(DAY,@FirstDate,DATEADD(MONTH,1,CONVERT(DATETIME,@FirstDate,103)))SELECT...

Find the first Day (01) and the Last Day (28) for a specified month in SQL Function (UDF), as an example (20091207)?

6 Mar 2013 by Amol_B

you can even do it in one lineSELECT DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)SELECT DATEADD(DAY, -(DAY(DATEADD(MONTH, 1, GETDATE()))),DATEADD(MONTH, 1, GETDATE()))

SUM on a column query

17 Apr 2013 by Maciej Los

Try it:SELECT SUM(P.ITEMAMOUNT) AS AMOUNT, P.COMPANYID AS COMPANY, SUBSTR(P.ACCOUNTID, 1,5) AS ACCOUNTFROM PRODUCTS P INNER JOIN ACCOUNTS A ON P.COMPANY = A.COMPANY AND P.ACCOUNTID = A.ACCOUNTIDWHERE P.ACCOUNTID LIKE '1%'GROUP BY P.COMPANY, SUBSTR(P.ACCOUNTID, 1,5)ORDER BY P.COMPANY,...

T-SQL

Oracle

How to insert the correct range in sql server?

25 Jun 2013 by _Amy

The overlap can be find using the following query:IF EXISTS (SELECT 1 FROM ODRange WHERE 31 = Minimum) print 'Overlaps'ELSE print 'Does not overlap'--Amit

T-SQL

SQL-Server

how to call t-sql stored procedure in c#

27 Apr 2014 by OriginalGriff

Try:using (SqlConnection con = new SqlConnection(strConnect)) { con.Open(); using (SqlCommand cmd = new SqlCommand("SelectRecord", con)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@lastname", "Smith"); ...

C#5.0

T-SQL

SQL

Split a String with Specific Separator Character

18 Jun 2014 by Ngo Thanh Tung - Softech

T-SQL scripts: Write a table function to split a string with specific separator character

T-SQL

sqlite Last_Insert_Rowid() returns 0 always

17 Oct 2014 by OriginalGriff

As the SQLite documentation[^] says:The last_insert_rowid() function returns the ROWID of the last row insert from the database connection which invoked the function. The last_insert_rowid() SQL function is a wrapper around the sqlite3_last_insert_rowid() C/C++ interface function.Which...

T-SQL

SQLite

c# ado.net sqlparameter fails

3 Nov 2014 by PIEBALDconsult

I'm thinking it's this:} finally { dt = null;(Experimentation has ruled that out.)Let's try to simplify your code then -- and use using statements as well.DataTable dt = null ;using ( SqlCommand cmd = conn.CreateCommand() ){ cmd.commandtext = "Select * from...

Find progress of a database restore using sys.dm_exec_requests

15 Jan 2015 by Liju Sankar

Find progress of a database restore using sys.dm_exec_requests

T-SQL

SQL

server

How Do I Loop Over Every Row Of A Sql Query Result Set In Sql Server?

11 Feb 2015 by Maciej Los

Transact SQL knows: While[^] loop and CTE[^] also.Now, you need to build and execute dynamic queries.Building Dynamic SQL In a Stored Procedure[^]Execute Dynamic SQL commands in SQL Server[^]

T-SQL

SQL

SQL-Server-2008R2

How can I pivot this query?

23 Feb 2015 by CHill60

Something like this (caveat - I haven't been able to test this properly so there might be the odd typo - used a temp table to mimic your query dets)WITH dets as(select sc.SubmissionPkId , sn.Name , rs.Label , rs.Value from ...

How to do a log using a trigger on update?

10 Mar 2015 by John C Rayan

CREATE TRIGGER [dbo].[EventUpdate] ON [dbo].[Events] AFTER UPDATEASBEGIN SET NOCOUNT ON; INSERT INTO [dbo].Logs([date],[user],[changes],[eventId]) SELECT getdate(), 'john', I.columnname + '-->' + D.columnname, I.id FROM Inserted I ...

T-SQL

SQL

How to change date format to french

15 Apr 2015 by OriginalGriff

That's because it's a bad idea! :laugh:Firstly, you shouldn't store dates as strings: you should always store them as DATE or DATETIME instead - and they don't have a format, they are stored as the number of microseconds since an arbitrary point in time. Storing dates as strings is both...

T-SQL

SQL

SSMS

Template for Managing Microsoft T-SQL Nested Transactions

24 Jun 2015 by brian_eckert

A proposed template for writing nested stored procedures that perform updates (inserts, updates, deletes)

T-SQL

transaction

Microsoft

How to display unique comments based on certain conditions in SQL Server?

1 Jul 2015 by Maciej Los

Have a look at sample:DECLARE @tmp TABLE(CustomerID INT, SequenceNo INT, Comments VARCHAR(MAX))INSERT INTO @tmp (CustomerID, SequenceNo, Comments)VALUES(1, 1, 'ABC D'),(1, 2, 'CDE'),(1, 3, 'ABC'),(1, 4, 'ABC D'),(1, 5, 'CDE'),(1, 6, 'abc'),(2, 7, 'ABC DEF'),(2, 8,...

T-SQL

SQL

SQL-Server-2012

Installing a SQL Server CLR Stored Procedure as a Hex String

30 Nov 2015 by Sarkis Matossian

Encoding a .NET assembly as a hex string to install in SQL Server.

INNER JOIN two views from SQL SERVER

28 Jul 2015 by OriginalGriff

Name the views:SELECT a.Col1, b.Col1, a.Col2, b.Col2, a.Col3 FROM View1 aJOIN View2 bON a.Col1 = b.Col1

T-SQL

SQL

SQL-Server-2012

Why isn't my left outer join working?

29 Oct 2015 by Tomas Takac

What you do there is basically cross join within url_id group: mr x prev. You need to restrict the join condition to get the correct result:left outer join moz prev on mr.url_id = prev.url_id and prev.num = 2where mr.num = 1

T-SQL

How to Check Opened Connection in SQL Server

13 Nov 2015 by Najmul Hoda

Here is the code snippet to check how many database connections are opened in SQL Server.

T-SQL

SQL

ADO.NET

QQ: identify the first record by date

27 Nov 2015 by Richard Deeming

You can't have a parameterized view, but a table-valued function would work.Try something like this:CREATE FUNCTION dbo.TVF_dashboard_list( @StartDate As date, @EndDate As date)Returns TableAsReturn( WITH records As ( SELECT ...

T-SQL

Where is the Invalid syntax near the comma in this TSQL?

14 Jan 2016 by Wendelius

The error is raised because you have mismatched parenthesis in the condition so from syntactical point of view the comma in the IN clause is outside the IN clause. Instead of thisand ItemCode in (Select ItemCode from UnitProducts where Unit='BIG RED ONE')),'X')cosider thisand ItemCode...

T-SQL

SQL

How can I combine two SELECT-based TSQL Insertions into one?

15 Jan 2016 by Wendelius

As an alternative to solution 1, you can also use simply UNION operation. In other wordsINSERT INTO #temp1 (MemberNo, MemberItemCode, NCDescription, Week1Usage, Week1Price)SELECT MEMBERNO, ITEMCODE, DESCRIPTION, QTYSHIPPED, PRICEFROM INVOICEDETAILWHERE UNIT=@UnitAND INVOICEDATE...

T-SQL

SQL

Pivoting on SQL Server Varchar Data Types

22 Feb 2016 by Sifiso W Ndlovu

This tip illustrates a workaround to SQL Server pivoting on variable character data types.

How to calculate session length

20 Apr 2016 by OriginalGriff

I'd be very tempted to add info to the DB - a value which identifies a session, so they are "automatically" grouped.If you add a GUID field to the DB, then when you record an action you can do a trivial search for the previous session entry: it's the same user, and with a time stamp less than...

T-SQL

LINQ

How do I tackle this problem on T-SQL. I am a beginner and have little knowledge on T-SQL

29 Jun 2016 by Richard MacCutchan

Start by writing the mathematical formula for each required calculation, checking for duplicates. Then you just need to write the T_SQL statements to include the formula where necessary, as described at Transact-SQL Reference (Transact-SQL)[^].

T-SQL

Tsql how to use HAVING function properly

25 Aug 2016 by phil.o

If you want to exclude 0 values also, then you have to provide this constraint in your query:SELECT DISTINCT ProjectNr, SUM(GWVS) - SUM(GWVS2) AS GWValue1, SUM(ABVS) - SUM(GWVS2) AS GWValue2, SUM(TSVS) - SUM(GWVS2) AS GWValue2FROM TBL1WHERE RB IN (North) GROUP BY ProjectNr...

T-SQL

SQL

How to add a column in a table using stored procedure ?

25 Nov 2016 by manu_dhobale

Modify Proc as belowdeclare @statement as nvarchar(100);set @statement='alter table emp add adrs varchar(20) null'exec sp_executeSQL @statement

T-SQL

SQL-Server-2008R2

How do I get value closest to after dividing two integer in SQL server

9 Feb 2018 by Richard Deeming

When you divide two integers, the result will always be truncated. If you want to round the result instead, then one of the values needs to be a floating-point type. ROUND(CONVERT(real, SUM([turnoverSM (%)])) / COUNT(DISTRICT), 0) ROUND (Transact-SQL) | Microsoft Docs[^] Also, in case you...

T-SQL

SQL

Monthly sum in pivot table

6 Mar 2018 by Maciej Los

Check this: --define fiscal year start and end DECLARE @fiscalYearStart DATE = CONVERT(DATE, '2017-04-01') DECLARE @fiscalYearEnd DATE = DATEADD(MM, 12, @fiscalYearStart) --variable to store column names: [2017-04-01],[2017-05-01],[2017-06-01],... DECLARE @cols NVARCHAR(2000) = N''; --recursive...

T-SQL

SQL

SQL Server Brute Force Attack Detection: Part 4

30 Apr 2018 by Ryan G Conrad

Using Powershell and Windows Task Scheduler API to detect malicious login attempts on a remotely accessible SQL Server database

Move Database File from One Drive to Another Drive

17 Oct 2018 by User-12551084

Moving database file from one drive to another drive

Based on SQL cte query results, count total no. Of customers who have paid the installment

24 Dec 2018 by Wendelius

It would help if you've posted some sample data. But let's try a few variations. If only a single payment can exist per customer then to list customers with payment, perhaps something like SELECT c.Cust_Id FROM user_detail c INNER JOIN installments i ON i.Cust_Id = c.Cust_Id WHERE c.Status =...

T-SQL

SQL

WHILE Loops in T-SQL Stored Procedures

13 Mar 2019 by essentialSQL

In this lesson, we’re going to learn about the While Loop. So, what is a While Loop? Well, While Loop is set up using a While statement. While statements are used to repeatedly execute a block of SQL statements.

T-SQL

SQL