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

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

24 Jun 2021 by Oleksandr Viktor (UkrGuru)

Minimally simple UkrGuru.SqlJson package for modern data manipulation

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

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

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

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

Sql server concurrency issue while transaction amount limit validation

4 Feb 2021 by vishal_h

I have transaction table like below where transaction entry will insert and after that validation is happening for transaction such as no account number perform transaction more than 5000 ( 5K) in a Month. Now in below scenario both the...

SQL-Server-2016

Sql server concurrency issue while transaction amount limit validation

4 Feb 2021 by Richard Deeming

Using NOLOCK is not a good solution. It will cause lots of problems. Bad habits : Putting NOLOCK everywhere[^] It would be better to fix the indexes and/or partitioning on your table so that you can run the query without using this dangerous hint.

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

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

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

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

Sql server agent job failure

21 Dec 2020 by Member 15010041

We have a log backup job running every hour. The log backups are being taken for all the databases except one of them. For security purposes lets call this database FAILED_DB.I looked at the history of the job and it has been failing since...

SQL-Server-2014

SQL-Server-2016

Sql server agent job failure

21 Dec 2020 by Gerry Schmitz

I think it's saying you need a full backup (first) in order to run the type of backup you are doing now. "Something" happened previously (or did not happen) that precipitated this. SQL SERVER - Fix - Error: 4214: BACKUP LOG cannot be performed...

SQL-Server-2014

SQL-Server-2016

Sql server table questions

8 Dec 2020 by Member 15010041

How/Where can I figure out the answers to these set of questions below in SQL Server?: -Is the data in this table modified in any way? -What is the data refresh frequency of this table? -Do they keep historical data for each month this gets...

SQL-Server-2016

Sql server table questions

8 Dec 2020 by CHill60

In response to your comments.. Quote: -Is the data in this table modified in any way? You would need to know if there is any audit of data changes on the table. It might be a "temporal[^]" table for example or there may be asssociated Triggers...

SQL-Server-2016

Sql server table questions

8 Dec 2020 by OriginalGriff

The questions depend on the table design they are discussing - and we have no access to that - and the code that is running against them - and we have no access to that either. Even if we did, while we are more than willing to help those that...

SQL-Server-2016

Sql server table questions

8 Dec 2020 by Richard MacCutchan

As a new DBA you probably need to find some good training materials: sql server learning - Google Search[^].

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

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

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

Sql server high availability maintenance plane

26 Jul 2020 by Al Mosaaed

Hi All, I just finished create 3 node SQL server high availability setup now I need to know the best practices for the maintenance planes 1- Backups 2- DBCC check 3- Managing log files 4- Adding alerts 5- Rebuild Index All the tasks about...

SQL-Server-2016

Sql server high availability maintenance plane

26 Jul 2020 by Richard MacCutchan

This is the Quick Answers forum, see Code Project Quick Answers FAQ[^]. Your question is one of research and MSDN and Google are the places to find information on these subjects. See SQL Server technical documentation - SQL Server | Microsoft...

SQL-Server-2016

How to store multiples values to a column using SET?

16 Jul 2020 by SravanM

Using SET how can i store multiple Values? When I'm trying to assign multiple values (which are extracted from a database)like this DECLARE @Level4 varchar(255) SET @Level4 = '[A,B]' i'm not getting a proper result. Can any one let me...

SQL-Server-2005

SQL-Server-2014

SQL-Server-2016

SQL-server-2000

How to store multiples values to a column using SET?

16 Jul 2020 by Maciej Los

If i understand you well... You can declare variable type of table: DECLARE @tmp TABLE(Column1 As VARCHAR(255)) Then: INSERT INTO @tmp (Column1) SELECT OtherColumn FROM YourTable WHERE YetAnotherColumn = 'SomeValue' To get values from table...

SQL-Server-2005

SQL-Server-2014

SQL-Server-2016

SQL-server-2000

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

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

Sql server deadlock issue with table bulk insert and update on same table

29 Jun 2020 by Richard MacCutchan

You already posted this question in the Database forum. Please do not crosspost.

SQL-Server-2008R2

SQL-Server-2012

SQL-Server-2014

SQL-Server-2016

How to write data or push it between multiple databases in azure SQL. As external tables doesn't support DML operation is there any other way to do it?

1 Jun 2020 by Ashirwad Satapathi

While performing cross database query is common in SQL server, But the same is not supported in Azuer SQL. Though i can query and join table across multiple databases using External Table but it doesn't support DML operations. So Can you Suggest me any way to do DML operation across databases. ...

SQL-Server-2016

SQL-Server-2014

Incorrect syntax near the keyword 'TRIGGER.'

29 May 2020 by CHill60

I think it's this segment here'... SELECT @UpdatedBy = @@SERVERNAME; SELECT @RecordPk = @PrimaryKeyId FROM inserted; SELECT @RecordPkDelete = @PrimaryKeyId FROM deleted; ...' Shouldn't that be '... SELECT @UpdatedBy = @@SERVERNAME; ...

SQL-Server-2016

Incorrect syntax near the keyword 'TRIGGER.'

29 May 2020 by Member 14565355

I am creating a procedure for creating triggers. I set the codes for creating triggers in a variable. I've used Double Single Quotes for some codes that has single quotes. These are the errors: Msg 156, Level 15, State 1, Line 1 Incorrect...

SQL-Server-2016

Running balance in MS access

15 May 2020 by Member 14833529

Dear MadMyche, A copy of my query follows: ID Date_Transaction Credit_Details Company Debit_Details Credit_Amount Debit_Amount Balance 1 13-May-2020 Sales of Heifers; Agra Auction A32055057 Agra None $21,326.00 0 $21,326.00 2 14-May-2020 Spar...

SQL-Server-2016

Running balance in MS access

15 May 2020 by Member 14833529

Hi, I have a database that I created with my basic knowledge. I want to ask whether somebody could assist with a SQL statement for a running balance. I tried several proposed SQL statements, but it seems to not work. Pardon me if I cross the...

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

Spit column that has underscore to multiple colums

10 Apr 2020 by learning_new

I have a table called clients and I'm trying to split the value which contains underscore that is one column into multiple columns and I'm also trying to create a column that calculate the age of the person. Here is how the table looks like ...

SQL-Server-2016

Spit column that has underscore to multiple colums

10 Apr 2020 by NEGIN_K

DECLARE @tmp TABLE(USERID VARCHAR(30), Vendors VARCHAR(10), dobyr INT, [login] VARCHAR(10), [source] VARCHAR(50)) INSERT INTO @tmp (USERID, Vendors, dobyr, [login], [source]) VALUES('10bta', 'yes', 1976, 'yes', 'google_hope'), ('25cwd', 'yes',...

SQL-Server-2016

Spit column that has underscore to multiple colums

10 Apr 2020 by NEGIN_K

DECLARE @tmp TABLE(USERID VARCHAR(30), Vendors VARCHAR(10), dobyr INT, [login] VARCHAR(10), [source] VARCHAR(50)) INSERT INTO @tmp (USERID, Vendors, dobyr, [login], [source]) VALUES('10bta', 'yes', 1976, 'yes', 'google_hope'), ('25cwd', 'yes',...

SQL-Server-2016

Spit column that has underscore to multiple colums

4 Apr 2020 by Maciej Los

One of the way to achieve that is to use CTE[^]. See: DECLARE @tmp TABLE(USERID VARCHAR(30), Vendors VARCHAR(10), dobyr INT, [login] VARCHAR(10), [source] VARCHAR(50)) INSERT INTO @tmp (USERID, Vendors, dobyr, [login], [source])...

SQL-Server-2016

Spit column that has underscore to multiple colums

3 Apr 2020 by MadMyche

SQL Server has a function which can split delineated text: Split_String(). I would recommend you read up on how to use it and go through the samples that MS has, not going to match exactly what you want but you will be able to work with it as a...

SQL-Server-2016

Incorrect column for pivot table

27 Mar 2020 by Richard Deeming

As the error says, the column names in your outer SELECT are incorrect. Try: SELECT sr, [Basic Salary], [Food Allowance], [Tuition Allowance], [Transportation Allowance] FROM ( SELECT sr = CASE when...

SQL-Server-2016

Incorrect column for pivot table

27 Mar 2020 by $ultaNn

I was trying to create pivot table from my query when i choose select * from it works fine but when i tries to select columns as shown in below code it gives me error Incorrect column name select sr,PayDescr,PayMonAmt from (select sr= case...

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

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

Server principal LOGIN has granted one or more permission(s). Revoke the permission(s) before dropping the server principal.

31 Jan 2020 by phil.o

Revoke the permission(s) before dropping the server principal – SQLServerCentral[^] SQL SERVER - FIX – Server principal ‘Login Name’ has granted one or more permission(s). Revoke the permission(s) before dropping the server principal - SQL Authority with Pinal Dave[^] Drop failed for Login...

SQL-Server-2014

SQL-Server-2016

Server principal LOGIN has granted one or more permission(s). Revoke the permission(s) before dropping the server principal.

31 Jan 2020 by bjay tiamsic

I am trying to delete a login in SQL Server using the command DROP LOGIN [user] BUt I get the following error message: Server principal LOGIN has granted one or more permission(s). Revoke the permission(s) before dropping the server principal. I have been reading...

SQL-Server-2014

SQL-Server-2016

Sql server - avoid cursor while serial update

24 Jan 2020 by MadMyche

You could use a WHILE loop to redo this via a UPDATE on JOIN query. This is a functional version of this; you may be able tweak a little more performance out of it, but it does give the same results as your cursor based varietyDECLARE @DateWork DATE = (SELECT Min(Eff_Date) FROM #transfers)...

SQL-Server-2016

Sql server - avoid cursor while serial update

24 Jan 2020 by Member 12127164

How can I avoid use cursor to implement the following? I read that it can be done with CTE but I didn't get it working with the same result. In the example I am using two tables, the first one is the holders table containing a list of people and the transfers table where each transfer indicates...

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

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

How to write data or push it between multiple databases in azure SQL. As external tables doesn't support DML operation is there any other way to do it?

20 Jan 2020 by Wendelius

Have you already tried using a linked server? See: Create Linked Servers - SQL Server | Microsoft Docs[^]

SQL-Server-2016

SQL-Server-2014

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

SQL Server Performance Issue After An Upgrade

9 Dec 2019 by Kanishka Basak

Analysis and troubleshooting a database performance bottleneck

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

Microsoft SQL server management studio 17: the application cannot start

3 Nov 2019 by OriginalGriff

Contact Microsoft tech support: support.microsoft.com/[^] and explain to them. It's a known problem and they have solutions (or it was with SQL 2016): Visual Studio 2017 install breaks SQL Server Managment Studio 2016 - Developer Community[^]

SQL-Server-2016

Microsoft SQL server management studio 17: the application cannot start

3 Nov 2019 by Nishanth J

After reinstalling the Visual Studio Professional 2017, since then I used to get the error message as The application cannot start, while opening the Microsoft SQL Server Management Studio 17 I had repaired and reinstalled but still the problem exist. The application cannot start[^] What...

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

Vb.net how do I execute a looping query and display its result in 1 datagridview

22 Oct 2019 by RickZeeland

Don't put these lines in your For loop: Dim query As String = "Select * from customer where meter_num = @meter_num" conn.Open() Dim command As New SqlCommand(query, conn) command.Parameters.AddWithValue("@meter_num", meter_number) Dim da As New SqlDataAdapter(command) Dim ds As New DataSet...

SQL-Server-2016

Vb.net how do I execute a looping query and display its result in 1 datagridview

22 Oct 2019 by Richard Deeming

Try something like this: Using command As New SqlCommand() command.Connection = conn Dim parameterNames As New List(Of String)(dt_data.RowCount - 2) For i As Integer = 0 To dt_data.RowCount - 3 Dim parameterName As String = "@meter_num_" & i Dim meter_number As...

SQL-Server-2016

Vb.net how do I execute a looping query and display its result in 1 datagridview

22 Oct 2019 by Member 14153541

I have an Excel sheet loaded in dt_data,and i want to compare the values of dt_data to my database and display it on another datagridview which is dt_sample , inside my database there is past 3 months record and i want to get them all to be displayed here is my code below What I have tried: ...

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

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

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

Is it possible to create more than one SQL failover in one windows cluster

30 Jun 2019 by Mohammad Al Hoss

I need to know if it is supported to create two or more SQL Server failover cluster installation on the same Windows Cluster? What I have tried: I am checking online forums but most of the answers are about creating a failover cluster.

SQL-Server-2016

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

SQL Server: Find Table Order/Generation And Relation Details

29 May 2019 by DiponRoy

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

SQL-Server-2008R2

SQL-Server-2012

SQL-Server-2014

SQL-Server-2016

Strange Behavior on JSON_VALUE When Table Contains Blank and Non-blank Values (JSON Text is Not Properly Formatted. Unexpected Character '.' is Found at Position 0.)

12 May 2019 by Manjuke Fernando

Observations made for a strange behavior on JSON_VALUE when table contains blank and non-blank values

SQL-Server-2016