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

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

Ms sqlserver store procedure problem in C#

5 Oct 2018 by #realJSOP

0) Allowing an exception to happen to implement some arbitrary form of error handling is just plain bad practice. Exceptions should generally be reserved for unexpected problems. 1) There's no point in repeatedly performing a process if it doesn't need to be done. 2) If there's already data...

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

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

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 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 restore database from network workgroup path?

16 Aug 2018 by abhimestry

I am using MS SQL Server 2016. Already restored the database using domain name. Now I want to restore database from network workgroup. What I have tried: USE MASTER GO ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE RECONFIGURE GO -- To enable xp_cmdshell EXEC sp_configure...

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

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

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

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

Pulling from variable into temp table

21 Aug 2018 by Alek Massey

As much as I dislike just throwing code out there. Insert into #While_Loop (@courseCodeList, @courseCodeList2) You execute the varchar @begin as a sql script, so the variables you create inside it exist in the environment you ran it from. They are availible on their own after it is run, not...

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

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 - will there be a problem if I try to insert data into a table via concurrent processes?

22 Feb 2018 by Boy Balantoy

Hi. Just wondering if any of you have encountered issues when multiple users are trying to insert their data into a single table concurrently. Table has unique identity column named ID and it auto-increments its own value whenever a record is inserted. if users will try to insert 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

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

Stored procedure to calculate total work hours with multiple in/out.

20 May 2017 by CHill60

I had a go at this. As I said in my comment I hope this isn't homework as I found it quite difficult to get all of the information you wanted. The first problem to deal with is the missing information. The best way to handle that is to generate a sequence of expected entries e.g. SELECT...

SQL-Server-2012

SQL-Server-2014

SQL-Server-2016

stored-procedure

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 CHill60

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

SQL-Server-2016

MS-SQL giving wrong results

10 Feb 2019 by CHill60

There is no mistake - your query is showing everything from Sales AND Purchases. These articles might help you understand Visual Representation of SQL Joins[^] Joining Tables in SQL[^] What you probably want is a UNION or something like this select salesDB.id, 'SALE' as tranType, sales from...

SQL-Server-2016

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

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

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

Sql server - how to make my app work on other computer

28 Dec 2017 by Dave Kreskowiak

One word: DEBUGGER. Learn to use it and it'll become obvious what you did wrong. You're doing some goofy things and not account for spaces in the path you generated. For example, why are you calling .ToString() on a string returned by BaseDirectory? That's just .... The connection string...

SQL-Server-2016

Sql server - will there be a problem if I try to insert data into a table via concurrent processes?

22 Feb 2018 by Dave Kreskowiak

All server-based SQL Servers handle concurrent connections and operations themselves. You don't have to do anything in your code to ensure this works. In fact, you actually have to go out of your way to screw things up, like assigning your own primary key values in code instead of letting the...

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

Sql file permissions

19 Jun 2017 by dnibbo

Hope someone on here can help... Our C: drive on one of our servers is getting a bit full so we decided to try to relocate the SQL databases to another drive on the same machine. SQL 2016 - Windows Server 2016. Note that this is a virtual server running under Hyper-V. We followed the...

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

Sql server 2016 to oracle 18c database migration

9 Oct 2018 by Gerry Schmitz

Since you're stuck, I would: 1) In SQL Server, right-click db and "Tasks | Generate Scripts..." 2) "script entire database and all database objects' 3) "Save to file" ... "Advanced" ... "Types of data to script" ... "schema and data". 4) Hack the resultant sql and data "text file" until...

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

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

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

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

The specified localdb instance does not exist

2 May 2018 by Jamie888

Hi, I have just published a MVC application into IIS. My app is using localDB created using SQL Server 2016(shall be version 13.0) and have executed commands SQLLOCALDB CREATE "Projects13" 13.0 -s using cmd. Everything run fine and i have validated using SQLLOCALDB info. Using SSMS to create...

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

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

5 Nov 2018 by Jovan Popovic(MSFT)

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

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

Sql - select rows from table where column values matched as column name in another table

17 Jul 2017 by Karthik_Mahalingam

select * from tablea where category in ( select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'tableb')

SQL-Server-2016

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

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

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

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

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

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

SQL Server 2016 Helps Us to Protect the Sensitive Data

13 Aug 2016 by Krishna KV

Dynamic masking using SQL Server 2016

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

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

Sql server 2016 to oracle 18c database migration

9 Oct 2018 by kyrsoronas

I'm trying to migrate a database created in SQL Server 2016 to Oracle 18c. Both database instances are installed in VMs. However the migration fails providing a message which I could not understand as I'm not that deep into Oracle RDBMS. Now, both VMs have 4GB of RAM and each one is used to...

SQL-Server-2016

Sql server 2016 to oracle 18c database migration

16 Oct 2018 by kyrsoronas

Apparently, I've misunderstood how the migration procedure works in Oracle. I've noticed, after some time, that you need to create a migration user which will be used as a middleman to migrate the schema and data from SQL server to Oracle. After the procedure is complete, you need to run the...

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

Select Statement to HTML Table

2 Feb 2018 by Luca Astolfi

User defined procedure for make an HTML table from T-SQL Select statment

SQL-Server-2008

SQL-Server-2008R2

SQL-Server-2005

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

Sql pivot table query issue

22 Jun 2018 by Maciej Los

You can't just pivot data, you need to unpivot it first. Check this: DECLARE @tmp TABLE (ProductType NVARCHAR(30), YTD_SPD DECIMAL(20,8), Var_SPD DECIMAL(20,8), Per_SPD DECIMAL(20,8)) INSERT INTO @tmp (ProductType, YTD_SPD, Var_SPD, Per_SPD) VALUES('OutboundNON DOC', 2448.029903, 244.0843848,...

SQL-Server-2016

Need your suggestions to automate the process

29 Jun 2018 by Maciej Los

Without knowing the details, an answer may be opinion-based only and the common answer would be: "It depends on many factors.." ;) In my opinion, the best way to automate "comparison process" is to distribute a service. I can't tell you exactly how to construct the service (what should it take...

SQL-Server-2016

stored-procedure

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

Sql date format for specific convert strings

25 Feb 2019 by Maciej Los

Please, read my comment first, then refer this article: Dealing with custom date formats in T-SQL - SQLServerCentral[^] For further details, please see: CAST and CONVERT (Transact-SQL) - SQL Server | Microsoft Docs[^]

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

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

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

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

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 date dimension non leap year giving 53rd week

11 Mar 2019 by MadMyche

Just as an added bonus.... Week #1 begins on January 1st of the year, and subsequent weeks begin on Sundays; so 53 week years are going to be the norm and not the exception. For instance this year (2019) we start off with week-1 starting on a Tuesday, and week-2 beginning on Sunday January 6th....

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

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

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

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

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

Always Encrypted Feature in SQL Server 2016

19 Oct 2016 by Manjuke Fernando

Always encrypted feature in SQL Server 2016

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

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

The Content Database has a Schema Version which is Not Supported by this Version

6 Mar 2019 by Melick

How to copy your production SharePoint database to development environment

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

Problem fetching pending quantity and pending amount

23 Aug 2018 by Member 10989312

hi all... i am having data in transaction table. the table structure is trans_Id, Order_Id, Item_No, Order_Qty,Supply_Qty,Trans_Type,amnt_Climed_Type,amnt_Climed_Prcntage Generally we are getting orders from client and based on the orders we are supplying material and at the time of supply...

SQL-Server-2008R2

SQL-Server-2012

SQL-Server-2014

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

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

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

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

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

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

Migrating from SQL2000 to SQL2017 - syntax changes

26 Jul 2018 by Member 12561559

Hi all, I've done some searching but not found anything in particular so thought one of you clever people might have done this yourselves and made some notes possibly.. We have some ancient VB legacy apps running on SQL2000 and we intend to go up to SQL2017. Now I know the compatibility level...

SQL-Server-2016

How to replace the row and column in SQL server

25 Aug 2018 by Member 12632819

with the command bulk insert I now want to place this line and column of this information. Thank you.... Before : title2 title3 Name Default Web Site State Stopped PSComputerName s07 RunspaceId c62cf173-af1f-46bd-a57f-3b29e74998ed After : Default Web Site ...

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