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

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

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

Streamline Log Shipping Failovers

29 Aug 2018 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.

SQL-Server-2008R2

SQL-Server-2012

SQL-Server-2014

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

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

Always Encrypted Feature in SQL Server 2016

19 Oct 2016 by Manjuke Fernando

Always encrypted feature in SQL Server 2016

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

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

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

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

Have you tried SELECT FORMAT (GETDATE(), 'MM/dd/yyyy ') AS Date You do realise that formatting only applies to dates in strings - dates which are prepared for presentation to the user in some form. No formatting you apply to a DATE, DATETIME or DATETIME2 column will be retained as dates are...

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

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

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

Want to seprate name from charindex

6 Sep 2017 by PeejayAdams

SQL 2016 introduced a STRING_SPLIT function which should do the job: STRING_SPLIT (Transact-SQL) | Microsoft Docs[^]

SQL-Server-2016

What order of installation I must follow and which versions for visual studio, SQL server and SSMS?

18 Sep 2017 by OriginalGriff

I always install SQL server first, then Visual studio - the reason is that VS uses SQL, so if it's already installed, it doesn't add it's own version. I've had problems with VS before after installing SQL "over the top" of it. SSMS installs as part of SQL Server, so that shouldn't be a problem.

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

22 Feb 2018 by Wendelius

Deadlock is a different concept. In a simple for deadlock occurs if - user A updates row 11 in table 1 - user B updates row 22 in table 2 - user B tries to update row 11 in table 1. Now it has to wait because the row is locked by user A - user A tries to update row 22 in table 2. Now it has to...

SQL-Server-2016

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

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

Storing PDF files as binary in sqlserver

12 Nov 2018 by OriginalGriff

Don't store them in SQL server - store them as files (I'd use a GUID as the file name) and store the location plus the original filename in SQL. That way, you aren't clogging SQL with unnecessary data, using up huge amounts of SQL server bandwidth, and you make it easier to back up the whole...

SQL-Server-2016

SQL-Server-2017

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

How to use union in my MS-SQL stored procedure

10 Feb 2019 by Richard MacCutchan

Google has lots of useful information: sql union - Google Search[^]

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

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

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

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

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

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

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

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

Need help on my datareader in visual studio 2015

19 Sep 2016 by Paolo Tansengco

My code was working fine until when I added parameters. Since then I get this error:An unhandled exception of type 'System.NullReferenceException' occurred in NewMonitoringSystem.exeAdditional information: Object reference not set to an instance of an object.I need help on how to...

SQL-Server-2016

Need help on my datareader in visual studio 2015

19 Sep 2016 by OriginalGriff

This is one of the most common problems we get asked, and it's also the one we are least equipped to answer, but you are most equipped to answer yourself.Let me just explain what the error means: You have tried to use a variable, property, or a method return value but it contains null -...

SQL-Server-2016

Need help on my datareader in visual studio 2015

19 Sep 2016 by Paolo Tansengco

dr = cmd.ExecuteReaderThis fixed the code.

SQL-Server-2016

How to publish VS 2015 project with sqlserver database

9 Oct 2016 by Paolo Tansengco

I was unable to google this.I need to publish my windows form application project that has a microsoft sql server database.I was able to publish it and install the project in a different computer but the database was not published with it. How do I publish the project with the database...

SQL-Server-2016

How to publish VS 2015 project with sqlserver database

9 Oct 2016 by Suvendu Shekhar Giri

Try adding it as prerequisite of your project. Have a look on the following documentationhttps://msdn.microsoft.com/en-us/library/7eh4aaa5(v=vs.100).aspx[^]In case you find difficulties while applying the steps as described in the documentation, please let me know.Hope, it helps :)

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

Table is empty not found anything

15 May 2017 by ZurdoDev

To rollback you'll need to use the Restore option on the database and use whatever backup you want. Here are the steps to restore from backup. Restoring a SQL Database Backup Using SQL Server Management Studio[^].

SQL-Server-2016

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

20 May 2017 by Member 13210334

Looking for stored procedure to calculate total work hours of the employees with multiple in&out Table structure Id - primary let EmpCode - employee code Status - IN/OUt ActionDatetime - DateTime Sample record1 emp100 IN 2017-05-18 10:00 2 emp100 OUT 2017-05-18 15:00 3 emp100 IN ...

SQL-Server-2012

SQL-Server-2014

SQL-Server-2016

stored-procedure

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

18 May 2017 by OriginalGriff

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 are weak, and focus more attention on remedial action. So give it a try, and see how far...

SQL-Server-2012

SQL-Server-2014

SQL-Server-2016

stored-procedure

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

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

Sql file permissions

19 Jun 2017 by RickZeeland

In the past I always found that copying SQL Server database files directly was looking for trouble, instead it's better to make a backup and restore that. This StackOverflow answer might be what you are looking for: Best way to copy a database (SQL Server 2008) - Stack Overflow[^]

SQL-Server-2016

SSRS 2016 cannot set parameters for web service query

11 Jul 2017 by Member 13305021

I have a query in SSRS 2016 which is working perfectly and returning the correct data from a webservice on my live webserver when I hard code the parameter value in, however, I need this to be dynamic, and to be a parameter passed into the report itself. I cannot work out what the syntax for...

SQL-Server-2016

reporting-services

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

17 Jul 2017 by SnvMohan

I want to select rows from table-A where Category-column values matched as any one of the Column name in table-B Table A UserName Category Raja ECE Manoj CAT Siva ENG Kumar MAT Viru WEB Table B Name ECE MAT ROB ENG Ching 10 0 7 9...

SQL-Server-2016

Why is the shared dataset no longer available in the 2016 version of SSRS?

27 Jul 2017 by Mike Ahrens

I have over 200 SSRS reports that use shared datasets. We are trying to upgrade from 2008 R2 to 2016, but this functionality is not available in the Standard distribution. We have a Web based application, and this will cause significant issues going forward. What I have tried: Visual Studio...

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

In SQL server 2014 emoji searching gives wrong result

22 Aug 2017 by RebelStar

I have one temp table having single column as 'Test' NVARCHAR(100). I have inserted into it two emojis as below 1. 😍🤣😂💻💡 2. 💡 When I execute search query=> Select * from TempTable WHERE Test=N'😍🤣😂💻💡'; gives => 😍🤣😂💻💡 BUT When I execute search query=> Select * from...

SQL-Server-2012

SQL-Server-2014

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

Want to seprate name from charindex

6 Sep 2017 by Member 13138564

Could you help records in name are like ANTOWYN PEARSON XUAN THI HA DARRIN HAALA BENJAMIN HAAS KALIM HABET What I have tried: ------------------------------------------------------ select left(name,CHARINDEX(' ',name)-1) as first_name from Business...

SQL-Server-2016

Want to seprate name from charindex

6 Sep 2017 by OriginalGriff

If you need to treat a name as separate parts, then get them entered as separate parts and store them separately - you can't assume that a user will have just two names( or even more than one!), or that the one to the left of a space is the "first name" - it can be the family name. In this...

SQL-Server-2016

Want to seprate name from charindex

6 Sep 2017 by RedDk

You get the specific message you're getting when you don't specify the size of the variable NVARCHAR so THAT defaults to 1. And the LENGTH is, after all your math, therefore zero. Here's what shows my drift. DECLARE @exampleY as nvarchar(20) SET @exampleY = 'Nater Termi' SELECT...

SQL-Server-2016

What order of installation I must follow and which versions for visual studio, SQL server and SSMS?

18 Sep 2017 by Member 13415725

Error while installing SQL Server 2016 Developer Edition on a laptop that has Visual Studio 2017 Community version What I have tried: I installed Visual Studio 2017 Community version and then SQL Server 2017 successfully. While installing SSMS - I got some compatibility error. After that I...

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

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

28 Dec 2017 by Member 13116441

static string domain = (AppDomain.CurrentDomain.BaseDirectory).ToString(); internal static string connect = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename= "+domain+"BMS_PRO_DB.mdf;" + "Integrated Security=True;Connect Timeout=30;"; the database is...

SQL-Server-2016

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

28 Dec 2017 by ZurdoDev

Yes, your connection is wrong. When the code runs on the other computer it will try to access a sql server at (LocalDB) so if the local computer does not have sql, it will not work. Change it to the name of the sqlserver machine or ip address so it can connect.

SQL-Server-2016

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

28 Dec 2017 by Member 13116441

HappyFestivus, give me an example , also tell me that , do i need to get the code of machine on which app is running, i dont get that.

SQL-Server-2016

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

28 Dec 2017 by Patrice T

Quote: do i need to get the code of machine on which app is running, i dont get that. Looks like you need to learn the basics of SQL. You need to install an SQL engine and your database on a server in your network. Then change your app to refer to the database. Internet is loaded with examples...

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

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

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

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

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

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

The specified localdb instance does not exist

2 May 2018 by Wendelius

In order to use localdb with IIS you need to make few adjustments to the IIS configuration. Have a look at Deployment of SQL Express LocalDB on IIS – Gaurav's Blog[^]

SQL-Server-2016

Index was out of range. Must be non-negative and less than the size of the collection

8 May 2018 by Member 13817169

Hey guys, I got an error at this called says" Index was out of range. Must be non-negative and less than the size of the collection, Parameter name : Index" Please help ASAP Private Sub dgw_MouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles...

SQL-Server-2016

Index was out of range. Must be non-negative and less than the size of the collection

8 May 2018 by OriginalGriff

The error message is pretty explicit: Index was out of range. Must be non-negative and less than the size of the collection, Parameter name : Index What it is saying is that one of the values you are using to access a collection is either negative, or larger than the number of items in the...

SQL-Server-2016

Sql pivot table query issue

22 Jun 2018 by sunil mali

Hello, I am always stuck with Pivoting tables, I feel it is most difficult topic in sql, Below is the expected result which i want, can somebody help? Main Table ProductType | YTD_SPD | Var_SPD | Per_SPD OutboundNON DOC | 2448.029903 | 244.0843848 | 11.07488288 InboundNON DOC |...

SQL-Server-2016

Need your suggestions to automate the process

29 Jun 2018 by samisyed80

Hi, I am working in a healthcare project where we have developed lots of stored procedures, web application, and even the ETL involved in it. Very often, we receive a separate data from client in excel file to compare with the data in our system and we need to generate the report for the same...

SQL-Server-2016

stored-procedure

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

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

Pulling from variable into temp table

10 Aug 2018 by Member 13945339

This is a snipbit of the entire project. What I'm trying to do is select @Begin execute @Begin and then put those results of the While loop into the #While_Loop temptable but I keep coming up with nothing being put into the Temp or just the string that @begin getting put into the Temp....

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

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

Problem fetching pending quantity and pending amount

23 Aug 2018 by Santosh kumar Pithani

CREATE TABLE #orders(trans_Id int identity(1,1),Order_Id varchar(50), Item_No int,Order_Qty int,Supply_Qty int,Trans_Type char(2), Climed_Type char(2),Climed_Prcntage int ); INSERT INTO #orders VALUES ...

SQL-Server-2008R2

SQL-Server-2012

SQL-Server-2014

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

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

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