Click here to Skip to main content

Submit your article

SQL Server 2016

SQL-Server-2016

Great Reads

MsSql.RestApi - The Easiest Way to Build the ASP.NET REST API

by Jovan Popovic(MSFT)

Create powerful REST API with just few lines of C# code using MsSql.RestApi package

FOR XML Basics (AUTO Mode): 2 of 4

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

Streamline Log Shipping Failovers

by Micah Nikkel

SQL script that dynamically generates the DR scripts for failing over/back all Log Shipped databases. While it makes even a single database failover/failback a more streamlined process, it's most helpful for servers with multiple databases, such as SharePoint, consolidated SQL Servers, etc.

A Practical Approach of Time Travel Concept in Business Intelligence Scenarios using Temporal Tables on Data Warehouses

by Jesus Carroll

SQL Server 2016 introduces support for system-versioned temporal tables as a database feature that brings built-in support for providing information about data stored in the table at any point in time rather than only the data that is correct at the current moment in time.

Latest Articles

UkrGuru.SqlJson Your Link Between SQL Server and .NET 5

by Oleksandr Viktor (UkrGuru)

Minimally simple UkrGuru.SqlJson package for modern data manipulation

How to Create and Use a Self-referencing Hierarchical Table

by E. Scott McFadden

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

SQL Server Performance Issue After An Upgrade

by Kanishka Basak

Analysis and troubleshooting a database performance bottleneck

SQL Server: Find Table Order/Generation And Relation Details

by DiponRoy

A utility query to find table generations in SQL Server relational database

All Articles

SQL Server 2016

A Practical Approach of Time Travel Concept in Business Intelligence Scenarios using Temporal Tables on Data Warehouses

10 Feb 2016 by Jesus Carroll

SQL Server 2016 introduces support for system-versioned temporal tables as a database feature that brings built-in support for providing information about data stored in the table at any point in time rather than only the data that is correct at the current moment in time.

SQL-Server-2016

Always Encrypted Feature in SQL Server 2016

19 Oct 2016 by Manjuke Fernando

Always encrypted feature in SQL Server 2016

SQL-Server-2016

Attach SQL server 2016 database to SQL server 2012

27 Feb 2018 by webmail123

I am trying to copy the database from SQL Server 2016 to SQL Server 2012. I am getting the version compatibility error. What I have tried: I even tried the command: ALTER DATABASE VJ_DATABASE SET COMPATIBILITY_LEVEL = 110; GO But still I am getting same error. Can anyone please help me?

SQL-Server-2016

SQL-Server-2012

Attach SQL server 2016 database to SQL server 2012

27 Feb 2018 by OriginalGriff

You can't: it's a different format database, and 2012 doesn't want to risk damaging the database so it can't be opened by the higher version. You can back it up and try restoring it into 2012 as a copy, but you can;t directly use the later version file on an earlier version app.

SQL-Server-2016

SQL-Server-2012

Best practice to save user image

20 Oct 2020 by Moses_k

Hello EveryOne, I have a question, What is the best way to save users Image profile? My scenario is, this system has about 100,000 users and each of them could load their Image (Just one Image). What is better to do this, save it in database...

SQL-Server-2016

Best practice to save user image

20 Oct 2020 by RickZeeland

See advice here: Storing images in SQL Server? - Stack Overflow[^]

SQL-Server-2016

Best practice to save user image

20 Oct 2020 by OriginalGriff

While you can save images in a DB, that's only a good idea if the image is small and you have only a few of them. 100,000 images of any size is going to take significant amounts of DB space - even a 170x200 image will average around 25K, so you...

SQL-Server-2016

Building a Basic Test Data Generator for Books in SQL

13 Nov 2017 by Haroon Ashraf

This article helps beginners to generate over million records of random test data for a database object like books in SQL.

SQL-Server-2012

SQL-Server-2014

SQL-Server-2016

Changing SQL server database collation

13 Apr 2018 by Chandugitameeee

Hi experts, Can you please tell me if there is a way to change the collation of an existing SQL Server database in which we have some data already ? I googled and got to know that the only way is to delete the database and recreate and re-load the data. But our client don’t want to do...

SQL-Server-2016

Changing SQL server database collation

13 Apr 2018 by #realJSOP

The only way you can guarantee the order of data is to use the order by clause when you perform a query. How it is ordered in the table is inconsequential.

SQL-Server-2016

Com.microsoft.sqlserver.jdbc.sqlserverexception: the datediff function requires 3 argument(s).

22 Jan 2020 by Kurt Jimenez

String car_id = txtcarid.getText(); pst = con.prepareStatement("SELECT Car_ID,Cust_ID,Rental_Due,DATEDIFF(GETDATE(),Rental_Due) as Days_Elapse FROM CarRental WHERE Car_ID =? "); pst.setString(1, car_id); rs = pst.executeQuery(); ...

SQL-Server-2016

Com.microsoft.sqlserver.jdbc.sqlserverexception: the datediff function requires 3 argument(s).

22 Jan 2020 by Kris Lantz

Your datediff function requires three arguments in the format: DATEDIFF(interval, date1, date2) and you've only supplied two. Take a look here at some examples. SQL Server DATEDIFF() Function[^]

SQL-Server-2016

Com.microsoft.sqlserver.jdbc.sqlserverexception: the datediff function requires 3 argument(s).

22 Jan 2020 by Richard MacCutchan

Quote: What is wrong with the statement? Exactly what it says. Go to the documentation for DATEDIFF to see what parameters are required: DATEDIFF (Transact-SQL) - SQL Server | Microsoft Docs[^]

SQL-Server-2016

Com.microsoft.sqlserver.jdbc.sqlserverexception: the datediff function requires 3 argument(s).

22 Jan 2020 by MadMyche

The error is in your actual SQL statement. The DateDiff function requires 3 parameters; and is in the format DATEDIFF(datepart, startdate, enddate ) Based on the alias you have assigned to the function (Days_Elapse), I am guessing you would want to be using something like this:SELECT Car_ID , ...

SQL-Server-2016

Com.microsoft.sqlserver.jdbc.sqlserverexception: the datediff function requires 3 argument(s).

22 Jan 2020 by Patrice T

Quote: What is wrong with the statement? Exactly what the message said: the datediff function requires 3 argument(s). and your code only provide 2 arguments. You need to read documentation to see how datediff works. SQL Server DATEDIFF() Function[^]

SQL-Server-2016

Convert SQL query to linq with like operator

8 Oct 2019 by Harsh.Shah.ifour

Hi I have a SQL query with LIKE operator. Now I want to convert this SQL query in Linq. can anyone help me to solve this ? Thanks in advanced. What I have tried: This is my SQL query: select designation_name, salary from dbo.Designation as d inner join empSalary as eb on d.salary_id =...

SQL-Server-2014

SQL-Server-2016

Convert SQL query to linq with like operator

8 Oct 2019 by Maciej Los

Like operator in SQL is equal to Contains()[^] in Linq. var query = from d in Designation join eb in empSalary on d.salary_id equals eb.salary_id where eb.salary > 17500 and d.designation_name.Contains("Dev") select new {...};

SQL-Server-2014

SQL-Server-2016

Copy table to another with condition and join

1 Mar 2020 by mohammed mqi

I'm trying to copy from table to another with condition and join and when I created it I get an error. INSERT INTO [ax].[WaitingKitchenStatus] ( [TRANSACTIONID]--1 , [LineNote]--2 , [Item]--3 , [CreationDate]--4 ,...

SQL-Server-2014

SQL-Server-2016

SQL-Server-2008R2

Copy table to another with condition and join

1 Mar 2020 by OriginalGriff

Your INSERT lists 7 fields: INSERT INTO [ax].[WaitingKitchenStatus] ( [TRANSACTIONID]--1 , [LineNote]--2 , [Item]--3 , [CreationDate]--4 , [CreationTime]--5 , [active]--6 ,[Gruop]--7 ) But the actual SELECT that...

SQL-Server-2014

SQL-Server-2016

SQL-Server-2008R2

Deadlock error in MSSQL 2012

10 Sep 2020 by Prashant Sharma

I am facing "Transaction (Process ID 426) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction." also some time get this error "Violation of UNIQUE KEY constraint...

SQL-Server-2016

Delete SQL table parent child detail

6 Mar 2021 by kasimmohamed

Hi, I have table like below. I need to delete only the first group child items except first group first child. Am looking the result like below table. Thanks in Advance What I have tried: create table #table (id int, description...

SQL-Server-2016

Delete SQL table parent child detail

6 Mar 2021 by Maciej Los

You can use CTE[^]: create table atable (id int, description nvarchar(50), parentid int); insert into atable values (1, 'Group1', 0), (2, 'Group2', 0), (11, 'Group1Sub1', 1), (111, 'Group1Sub111', 11), (112, 'Group1Sub112', 11), (1111,...

SQL-Server-2016

DROP IF EXISTS in SQL Server 2016

1 Oct 2016 by Suvendu Shekhar Giri

Enhancement on dropping an object with check for existence in SQL Server 2016

SQL-Server-2016

DROP IF EXISTS in SQL Server 2016 (DIE)

24 Aug 2016 by Manjuke Fernando

DROP IF EXISTS in SQL Server 2016 (DIE)

SQL-Server-2016

Dynamic Data Masking in SQL Server 2016

11 Mar 2016 by Kashif-Sohail

What is Data masking, enabling it in an existing application and giving access to different users to the masked data

SQL-Server-2016

Error for service now source when run using open query

25 Oct 2018 by bunty swapnil

Hi, i had developed a package which integrate data from service now source to SQL database. Steps below which i used for integration. 1. Downloaded Service now ODBC driver 1.0.9 and installed. 2.Created DSN for Service now source and Used DatasourceName as Link_Server. 3.Created linked server...

SQL-Server-2016

Find all objects (like view, table, functions , parameters and variable , variable values ) in stored procedure

14 Jul 2020 by Member 11276381

Hi Team i am trying to get a query for below ask Find all objects (like View, table, functions ,parameters and variable ,variable values ) in Stored procedure but finding the query ,seems its not possible .anyone could you please help me ...

SQL-Server-2016

Find all objects (like view, table, functions , parameters and variable , variable values ) in stored procedure

14 Jul 2020 by MadMyche

Microsoft has documentation for a few different ways MS Docs: View the Dependencies of a Stored Procedure - SQL Server[^] MS Docs: SP_Depends - SQL Server[^]

SQL-Server-2016

Find different procedure amount on the same date

4 Feb 2021 by kasimmohamed

Hi, I have a table like below create table #mytable (vouno varchar(20), voudate date, procedure varchar(10), Amount decimal(18,3)) insert into #mytable values ('1001','01/01/2021','A', 100) insert into #mytable values ('1001','01/01/2021','B',...

SQL-Server-2016

Find different procedure amount on the same date

4 Feb 2021 by Richard Deeming

Seems simple enough: SELECT DISTINCT vouno, voudate FROM #mytable As T WHERE Exists(SELECT 1 FROM #mytable As A WHERE A.vouno = T.vouno And A.voudate = T.voudate And A.[procedure] = 'A') And Exists(SELECT 1 FROM #mytable As B...

SQL-Server-2016

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

SQL-Server-2008R2

SQL-Server-2012

SQL-Server-2014

SQL-Server-2016

SQL-Server-2008

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

SQL-Server-2008R2

SQL-Server-2012

SQL-Server-2014

SQL-Server-2016

SQL-Server-2008

FOR XML Basics (RAW Mode): 1 of 4

2 Jun 2017 by AhsanAhmed

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

SQL-Server-2008R2

SQL-Server-2012

SQL-Server-2014

SQL-Server-2016

Getting wrong result in sqlserver in substraction

1 Apr 2018 by Member 12245539

I have two tables first one Fee_Payable_to_Students and another one Fee_Assign_Waiver_to_Students it contains value as Fee_Payable_to_Students f_co |S_Adm_No | apr | may | june | jul | aug | sep | oct | nov | dec | jan | feb | mar 1 |s_1 | 5 | 5 | 5 | 5 | 5 | 5 | 5...

SQL-Server-2005

SQL-Server-2008R2

SQL-server-2000

Getting wrong result in sqlserver in substraction

1 Apr 2018 by Wendelius

What I would suggest is that instead of storing different months as columns you'd store them as rows in a new table. Each row would have a date column defining the month and another column defining the actual value plus a reference to the parent table. Consider following table example ...

SQL-Server-2005

SQL-Server-2008R2

SQL-server-2000

How can I build this SQL server update query ? ! ! ! please help me

10 Jul 2020 by ahmed yousif

Dear all i face difficulty in building one sql update query i have one table(stock_table) containing three columns as follow (item_id,box,loose) item_id, char(5) box, smallint ==> represent stock quantity for item_id (full box) loose, smallint...

SQL-Server-2016

How can I build this SQL server update query ? ! ! ! please help me

10 Jul 2020 by OriginalGriff

I can't help thinking you are going about this the wrong way: the schema you describe only works if all products come in boxes of three strips of 10 items - and in the real world that is generally not the case. Medication for example will often...

SQL-Server-2016

How can I compare values and to display particular string in listview

8 Aug 2018 by User 11900410

i want to compare a value on basis of that value show output into listview e.g. if id is 1 then 'A' this 'A' i have to display into listview. How can i? What I have tried: Public Sub load1() Dim SqlCommand As New SqlClient.SqlCommand Dim connection As String =...

SQL-Server-2016

How can I find the index that was out of range, my index are all equal

7 Jan 2020 by Erwin Alcantara

I already count the column and the index still got an error that my index was out of range, can someone help me please.. var query = "SELECT [tblemployee].[id] ,[tblemployee].[EMP ID], [tblemployee].[FIRST NAME], [tblemployee].[LAST NAME], [tblemployee].[MIDDLE NAME], [tblemployee].[EMAIL],...

SQL-Server-2016

How can I find the index that was out of range, my index are all equal

7 Jan 2020 by Kris Lantz

Have you put a breakpoint at var row = (DataRowView)dgEmployeeList.SelectedItems[0]; and verified that SelectedItems[0] exists? It's difficult to tell, because the error provided is vague, but I would step through the code, and pay particular attention to the above call.

SQL-Server-2016

How can I find the index that was out of range, my index are all equal

7 Jan 2020 by Erwin Alcantara

var query = "SELECT [tblemployee].[id] ,[tblemployee].[EMP ID], [tblemployee].[FIRST NAME], [tblemployee].[LAST NAME], [tblemployee].[MIDDLE NAME], [tblempworkinfo].[NAME], [tblemployee].[EMAIL], [tblemployee].[EMAIL PASSWORD], [tblemployee].[ADDRESS], [tblemployee].[CONTACT],...

SQL-Server-2016

How can I insert data into SQL server from a delimited text file having fixed positions

17 Dec 2019 by Hamza Hussain

I have 390 delimited text files collected from different sources. I have to load them in SQL Server. I don't want to use import and export wizard. I want to create a Bulk Copy Command Mechanishm through which data can be inserted into database. Every file has 32 columns but there are certain...

SQL-Server-2016

How can I insert data into SQL server from a delimited text file having fixed positions

17 Dec 2019 by #realJSOP

You can write a SSIS package that does it, or find a code snippet that parses it and transforms it into a SQL compatible entity that can be inserted into the database.

SQL-Server-2016

How can I insert data into SQL server from a delimited text file having fixed positions

17 Dec 2019 by AFell2

It sounds like you are dealing with fixed length files as opposed to value delimited files (i.e., comma or tab delimited). I assume this because you describe the columns as having a starting position and ending position. If this is the case, the Almighty Google has a solution for you! Welcome...

SQL-Server-2016

How can we pass varchar input to a SQL function?

5 Jan 2021 by Member 15036870

My table 'LoadData' is as below: Sequence_Code | ID | Current_Year | Record_Date| Status | :------------ | -----: | -----------: | :----------| --| 17AA | 310001 | 2017 | 2017-01-01 | S 18AB |...

SQL-Server-2016

How can we pass varchar input to a SQL function?

5 Jan 2021 by OriginalGriff

The problem isn't how you are passing the data, it's how you are processing the data. Basically put your SQL Function doesn't work correctly in all cases. And since we can't see your function code, we can't help at all. But you can help...

SQL-Server-2016

How can we pass varchar input to a SQL function?

5 Jan 2021 by Maciej Los

Your function is wrong. You should use sql code similar to that i posted in this threat: How can we write a SQL function to increment alphabetic 'sequence_code'[^] Take a look at below comments and change your function accordingly --...

SQL-Server-2016

How can we write a SQL function to increment alphabetic 'sequence_code'

4 Jan 2021 by Member 15036870

How can we Write a SQL function to Increment Alphabetic 'Sequence_Code' for only those ID whose Status is'A' Alphabetic 'Sequence_Code' is combination of Last Two Digit of 'Current_Year' and 2 character alphabet like AA,AB,AC....AZ,...

SQL-Server-2016

How can we write a SQL function to increment alphabetic 'sequence_code'

4 Jan 2021 by Maciej Los

Please, take a look here: Custom Auto-Generated Sequences with SQL Server - SQLTeam.com[^] There you'll find few very interesting functions with detailed information about algorithm. All what you need to do is to get one of existing functions...

SQL-Server-2016

How do I configure SQL server 2016 HA fail-over cluster

27 Feb 2020 by SupportICS

Please help in configuring the SQL Server 2016 fail-over cluster configuration for 99.99% availability of Database. What I have tried: Followed some of the steps available but couldn't succeed

SQL-Server-2016

How do I configure SQL server 2016 HA fail-over cluster

27 Feb 2020 by phil.o

sql server 2016 failover[^] SupportICS wrote: Followed some of the steps available ... Which steps exactly? What did you try? SupportICS wrote: ... but couldn't succeed How was it bad? Were there errors? Incorrect results? Please improve your...

SQL-Server-2016

How do I execute stored procedure automatically

18 Apr 2020 by ABAKh

I have Stored procedure to send email, and i want this Stored Procedure to be executed daily and automatically in specific time let's say 8:00 AM, i'm using SQL Server 2016, any solution is appreciated. Thank you,, What I have tried: I created...

SQL-Server-2016

How do I execute stored procedure automatically

18 Apr 2020 by RickZeeland

As you have SQL Server Agent (not available in Express) you can do it like shown in the examples here: Schedule a Job - SQL Server Agent | Microsoft Docs[^] Scheduling SQL Server Jobs with SQL Agent - SolarWinds[^]

SQL-Server-2016

How do I fix the issue where ASP.NET user log-in cannot access SQL server?

28 Aug 2017 by Member 12824529

I'm running VS2015 and SQL Server 2016 on Windows 10. I am trying to get Forms Authentication to work in MVC5; but even when I try a new MVC test application while following the walkthrough by Microsoft, I get the following error when I try to register a new user: ...

SQL-Server-2016

How do I fix the issue where ASP.NET user log-in cannot access SQL server?

26 Aug 2017 by Mehdi Gholam

Try this : c# - SQL Network Interfaces, error: 50 - Local Database Runtime error occurred. Cannot create an automatic instance - Stack Overflow[^]

SQL-Server-2016

How do I insert data in database in ASP.NET core without entity framework?

27 Mar 2017 by Member 11490433

I am Use Ado.net in Asp.net Core but it not working.What I have tried:How do i Insert data in database in Asp.net Core without Entity Framework?I am Use Ado.net but it not working. There is i am not add reference not system.data.configuration so how i do insert data in database.

SQL-Server-2016

How do I insert data in database in ASP.NET core without entity framework?

26 Mar 2017 by F-ES Sitecore

I googled "asp.net core ado.net" and found lots of results, here is the first one but do the search yourself for moreasp.net - Is ADO.NET in .NET Core possible? - Stack Overflow[^]Please do basic research like using google before asking a question.

SQL-Server-2016

How do I query 2 different databases in same domain but different servers using single SQL statement/stored procedure

1 Jan 2021 by onyangofred

I have identical mssql databases hosted in 2 different servers. 1st database replicates to update the 2nd database. Once I have connected to the 1st database, I need a query (stored procedure) to run and query data from the 2nd database. The...

SQL-Server-2016

stored-procedure

How do I query 2 different databases in same domain but different servers using single SQL statement/stored procedure

31 Dec 2020 by CHill60

If you are replicating from #1 to #2 then there is no need to query both databases, the data you are trying to access will be in both so just query one of them. If "NB: the 2 databases can contain different set of data for similar tables." is...

SQL-Server-2016

stored-procedure

How do I query 2 different databases in same domain but different servers using single SQL statement/stored procedure

1 Jan 2021 by PIEBALDconsult

(I'm assuming SQL Server) First off, you could look into linked servers, but I do not recommend that. Linking servers typically ends in woe. And, if you need this only periodically, it definitely is not worth the performance hit. I have never...

SQL-Server-2016

stored-procedure

How linq query fetch data from multiple tables without using join?

14 Jun 2019 by Umair Nafis

I have 3 tables in my database which have no relation with each other. what i want is to implement the search operation on my website. So that when a word is submit in search box the query go through all the tables and fetch the data wherever it find that word. I can fetch the data from single...

SQL-Server-2016

How linq query fetch data from multiple tables without using join?

13 Jun 2019 by F-ES Sitecore

Run the search three times, one for each table you want to search on. If you want to present a single result set then combine the three results into a single list and return that list.

SQL-Server-2016

How retrieve dynamic data from list to label

14 May 2020 by Fahim ullah

Hi Dear developers ! I am a student and beginner in C#. I want to add data from textbox into list and then retrieve all list data into single lable in list form example if user type 1 in textbox the vale store in list and user free to type...

SQL-Server-2016

How retrieve dynamic data from list to label

14 May 2020 by Richard MacCutchan

You need to learn how to use events to capture data as the user is typing, since you have no control over how fast or slow they will enter the data. See How to: Raise and Consume Events | Microsoft Docs[^].

SQL-Server-2016

How to add prefix N to the variable in stored procedure in SQL server 2016

23 Oct 2019 by abdul subhan mohammed

Dear All, I have a stored procedure where I have a variable that will be English or Arabic string, for Arabic string I need to add prefix N to the variable. declare @ModuleName nvarchar(max) set @ModuleName = 'أساسيات الكمبيوتر' SELECT * FROM Module WHERE NameEN LIKE N'%' + @ModuleName +...

SQL-Server-2016

How to add prefix N to the variable in stored procedure in SQL server 2016

23 Oct 2019 by RickZeeland

Try to move the % to where the parameter is added. See this question: How to search value from SQL data base using %[^] scCommand.Parameters.Add("@ModuleName", SqlDbType.NVarChar, 50).Value = "ساسيات الكمبيوتر";

SQL-Server-2016

How to calculate total time taken in SQL query

24 Oct 2019 by Member 11615785

I want to calculate total time taken in driving excluding time taken in Pause and Resume. Sample Values are as below mention: LocationTrackingId PatientRequestId NurseId LocationDateTime Status 1 22 4 17-06-2019 14:10 StopDriving 2 ...

SQL-Server-2016

How to calculate total time taken in SQL query

24 Oct 2019 by #realJSOP

Try this (may require tweaking)... ;WITH cte AS ( SELECT PatientRequestID, MIN(LocationDateTime) AS StartTime, MAX(LocationDateTime) AS EndTime, DateDiff(second, MIN(LocationDateTime), MAX(LocationDateTime)) AS TotalSeconds FROM dbo.MyTable ...

SQL-Server-2016

How to combine two rows in single rows SQL server

14 Nov 2019 by Member 12245539

I have a table A it contains 4 columns as mentioned below ID | SUBJECT | MARKS1 | MARKS2 | 1 | ENGLISH | 10 | 20 | 2 | HINDI | 20 | 30 | 3 | PHYSICS | 10 | 10 | 4 | CHEMISTRY| 20 | 20 | 6 | BIOLOGY | 10 | 10 | 7 | MATHS | 5 | 25 ...

SQL-Server-2008R2

SQL-Server-2012

SQL-Server-2014

SQL-Server-2016

How to combine two rows in single rows SQL server

14 Nov 2019 by Richard Deeming

Seems simple enough - you just need a CASE statement to map the subject name, and a GROUP BY to group the rows with the same subject. The only slightly tricky part is that you have to repeat the CASE statement in the GROUP BY clause. SELECT CASE WHEN Subject In ('PHYSICS',...

SQL-Server-2008R2

SQL-Server-2012

SQL-Server-2014

SQL-Server-2016

How to convert columns to rows in ms SQL server

9 Feb 2019 by Member 12245539

I have one table TIMETABLE it contains vales as id| Day | p1 | p2 | p3 | p4| Short_By_Name_by_Class 1 | mon | ES | GS | CP | GN| name1 2 | tue | AB | AC | AD | AE| name2 3 | Wed | BA | BB | BC | BD| name3 Now how to convert this like PD |mon| tue| wed P1 |ES | AB | BA | P2 |GS |...

SQL-Server-2008R2

SQL-Server-2012

SQL-Server-2014

SQL-Server-2016

How to convert columns to rows in ms SQL server

9 Feb 2019 by Maciej Los

First of all, you have to unpivot[^] data {p1, p2, p3, p4} to PD and PDValue fields then pivot them again - on Day field. SELECT PD, [mon], [tue], [wed], ... FROM ( SELECT id, Day, PD, PDValue FROM ( SELECT id, Day, p1, p2, p3, p4 FROM TIMETABLE )...

SQL-Server-2008R2

SQL-Server-2012

SQL-Server-2014

SQL-Server-2016

How to create a client server application using C#

14 Aug 2017 by Member 13361064

hi.. i'm a degree student an i'm working on my final year project. its an app that connects to a remote server in order to manage a certain microsoft sql database created on that server. So i have completed all the functionality for this app except for the part where it connects to the...

SQL-Server-2016

How to create a client server application using C#

14 Aug 2017 by Dave Kreskowiak

OK, so your connection string needs to be modified in code. That's simple string manipulation. The easiest way to do it is to just replace a tag in the string with the IP address or host name: Server={SERVER};Database=myDataBase;User Id=myUsername; Password=myPassword; string...

SQL-Server-2016

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-Server-2016

SQL-Server-2017

How to create pivot table in SQL sever

19 Mar 2018 by nityanand NCR

I have a table (Table_Marks) it contains value as described below: - CLASS | STD | NAME | SUBJECT | PT1 | PB1 | PT2 | PB2 | 1 | ST1 | NITYA | ENG | 12 | 15 | 30 | 9 | 1 | ST1 | NITYA | HIN | 2 | 22 | 25 | 6 | 1 | ST1 | NITYA |...

SQL-Server-2008R2

SQL-Server-2014

SQL-Server-2016

SQL-server-2000

How to create pivot table in SQL sever

19 Mar 2018 by OriginalGriff

Quote: Sir I am a beginner I am not getting any point, how to do this... Please help... We do not do your homework: it is set for a reason. It is there so that you think about what you have been told, and try to understand it. It is also there so that your tutor can identify areas where you...

SQL-Server-2008R2

SQL-Server-2014

SQL-Server-2016

SQL-server-2000

How to create pivot table in SQL sever

19 Mar 2018 by Member 13658881

i think it helpful to you.. select CLASS,STD ,NAME,SUBJECT,pt1,pt2,pb1,pb2,t1,t2,gtot from ( select class,std,name,subject,pt1,pt2,pb1,pb2,(pt1+pb1)T1,(pt2+pb2)T2,(T1+T2)Gtot )a pivot ( sum(Gtot) for subject in([Eng],[Hin],..) )pvt

SQL-Server-2008R2

SQL-Server-2014

SQL-Server-2016

SQL-server-2000

How to create pivot table in SQL sever

19 Mar 2018 by Maciej Los

Quote: Now I want to use this as PIVOT TABLE as described below: - CLASS|STD |NAME |ENG |HIN |MATH |T1 |ENG |HIN |MATH |T2 |T1+T2| |PT1|PB1 |PT1|PB1|PT1|PB1| |PT2|PB2|PT2|PB2|PT2|PB2| 1 |STD1|NITYA |12 |15 |2 |22 |3 |10 |64 |30 |9 |25 |6 |32 |8 ...

SQL-Server-2008R2

SQL-Server-2014

SQL-Server-2016

SQL-server-2000

How to delete multiple nodes from an XML in SQL server

12 Jul 2018 by TarunShrivastav

Hi, i have an xml as below declare @xml xml = '124 ' I want to delete multiple nodes in a single sql statement. i.e. I need a sql query to remove two nodes say Field_1 and Field_2 dynamically. XML will...

SQL-Server-2016

SQL-Server-2012

How to delete multiple nodes from an XML in SQL server

12 Jul 2018 by Kornfeld Eliyahu Peter

This is the 'delete' way: SET @XML.modify('delete /my/Fields/Fields_1') The reason is that modify can not get variables only literal... You can create of course dynamic SQL... sp_executesql (Transact-SQL) | Microsoft Docs[^]

SQL-Server-2016

SQL-Server-2012

How to fetch a persons team count based on manager_id relationship in SQL

11 Mar 2021 by Virendra S from Bangalore, Karnataka

SELECT COUNT(*) FROM [dbo].[tblEmployeeMaster] WHERE MGR_ID=@EMPID table tblEmployeeMaster is having key columns emp_no,emp_name,mgr_id. Here I want fetch count of a person's reportees, child reportees(nth level) that means his total team...

SQL-Server-2008R2

SQL-Server-2012

SQL-Server-2016

How to fetch a persons team count based on manager_id relationship in SQL

10 Mar 2021 by CHill60

Would give you a fully worked example but I'm just about to go into a meeting. I did something similar in my article about loops in SQL Server - see Processing Loops in SQL Server[^] the section about "Traversing a Hierarchy". You can then...

SQL-Server-2008R2

SQL-Server-2012

SQL-Server-2016

How to fetch a persons team count based on manager_id relationship in SQL

11 Mar 2021 by Virendra S from Bangalore, Karnataka

WITH CTEORG AS ( SELECT Emp_No, Emp_Name,MGR_ID,DOL,LEAVING_ACTION_DATE FROM [tblEmployeeMasterLive] WHERE MGR_ID =@EMP_NO AND CONVERT(DATETIME,DOJ,103) =...

SQL-Server-2008R2

SQL-Server-2012

SQL-Server-2016

How to fill out the second column accordint to the string in the first column using while

7 Jul 2020 by Member 10943256

Hello, I have only 2 columns in sql server. The first column (name) starts with the name "abc" in the first line and it ends in the 4th line (Endabc). I need to have second red color column: (while the first column starts with "abc" until the...

SQL-Server-2016

How to fill out the second column accordint to the string in the first column using while

7 Jul 2020 by Richard Deeming

Your data does not have a sequence defined. Without a column to order by, there is no defined order for your rows, so there is no way to know which rows should appear between "abc" and "endabc". Add a column to provide a sequence to your table: ...

SQL-Server-2016

How to find out my visula C# application connected that system SQL database or network

1 Jul 2019 by vijay_bale

In my application i am connecting SQL server data. I want to know that my application connected local data base or network database of SQL server. What I have tried: I searched for solution in net but came with some answers which are not suitable

SQL-Server-2016

How to find out my visula C# application connected that system SQL database or network

1 Jul 2019 by OriginalGriff

The easiest way to check - pretty much the only way - is to look at the connection string you are using in your code. If it's "LOCALHOST", or starts with your computers name, it's local. If it doesn't, it isn't. But ... I wouldn't rely on that for deciding if you are in dev or release: I'd use...

SQL-Server-2016

How to get all times greater than a particular time in SQL

3 Jul 2018 by Member 12926744

Hi all, I have a table with a field ie starttimes . What I need is that , I need to get all the starttimes that is greater than a particular time . I have stored the time in varchar type. What I have tried: For example , StartTimes 1:00 AM 2:00 AM 3:00 AM 4:00 AM 7:00 AM ...

SQL-Server-2016

How to get all times greater than a particular time in SQL

3 Jul 2018 by Krunal Rohit

Use SQL Server DATEPART() Function[^]. SELECT * FROM [your table] WHERE DATEPART(hh, [your date field]) = 7 --7 AM KR

SQL-Server-2016

How to get all times greater than a particular time in SQL

3 Jul 2018 by CHill60

Using this sample data create table #badway ( id integer identity(1,1), mytime varchar(8) ) insert into #badway (mytime) values ('1:00 AM'), ('2:00 AM'), ('3:00 AM'), ('4:00 AM'), ('7:00 AM'), ('1:00 PM'), ('2:00 PM'), ('3:00 PM') Then this query fits your criteriaselect id,...

SQL-Server-2016

How to get all times in a day with 30 mins interval in SQL

3 Jul 2018 by Member 12926744

Hi all, I need to get all times in a day in 12 hr format with 30 mins interval ie , I need to get the output as o/p 1:00AM 1:30AM 1:00PM 1:30PM 10:00AM 10:30Am 10:00PM 10:30PM 11:00AM 11:00PM 11:30AM 11:30PM 12:00PM 12:30PM 2:00AM 2:00PM 2:30AM 2:30PM and so on What I have tried: select...

SQL-Server-2016

How to get all times in a day with 30 mins interval in SQL

3 Jul 2018 by CHill60

Here is one way: ;WITH q AS ( SELECT 30 AS num UNION ALL SELECT num + 30 FROM q WHERE num

SQL-Server-2016

How to ignore varchar value in SQL table column

27 Sep 2017 by Member 12245539

I have a table i.e. create table tbl (id int, col1 varchar(50), col2 varchar(50),col3 varchar(50))insert into tbl values (1,'20','25','50') insert into tbl values (2,'30','35','60') insert into tbl values (3,'AB','AB','50') I want to sum of three columns, for this I am using select id,...

SQL-Server-2014

SQL-Server-2016

SQL-Server-2005

How to ignore varchar value in SQL table column

28 Sep 2017 by Karthik_Mahalingam

try select id, (col1+col2+col3) as Total from ( select id, case ISNUMERIC(col1) when 1 then CAST(col1 as decimal) else 0 end as col1 , case ISNUMERIC(col2) when 1 then CAST(col2 as decimal) else 0 end as col2 , case ISNUMERIC(col3) when 1 then CAST(col3 as decimal) else 0...

SQL-Server-2014

SQL-Server-2016

SQL-Server-2005

How to make the database go to SSMS instead of localdb?

29 Mar 2017 by Truecolors

I am able to log into SSMS under Windows Authentication and SQL Server Authentication. But I am not seeing the Database anywhere in the SQL Server Management Studio. In Visual Studio, it goes to the localDb even though I have changed the connection string to have the db created in my...

SQL-Server-2016

How to make the database go to SSMS instead of localdb?

29 Mar 2017 by Truecolors

After working my head off with this issue for the past 2 days, finally have it working. All I had to do was set the Project as a "StartUp Project" in Solution Explorer and it fixed the issue. The database was created in the right Server with all the data.

SQL-Server-2016

How to pass one by one column values to a SQL function?

6 Jan 2021 by Member 15036870

This is my table "LoadData" Sequence_Code | ID | Current_Year | Record_Date| Status | :------------ | -----: | -----------: | :----------| --| 17AA | 310001 | 2017 | 2017-01-01 | S 18AB | 310002 | ...

SQL-Server-2016

How to pass one by one column values to a SQL function?

6 Jan 2021 by Richard MacCutchan

Try some tutorials and you will get many clues, for example: SQL CASE Statement[^]

SQL-Server-2016

How to pass one by one column values to a SQL function?

6 Jan 2021 by CHill60

SQL Server is set-based, so you don't pass things one-by-one to a function! You probably need something like (untested)UPDATE LoadData SET Sequence_Code = dbo.GetNextSequenceCode_TEST([ID]) WHERE [Status] = 'A' AND Sequence_Code IS NULL; You...

SQL-Server-2016