Click here to Skip to main content

Submit your article

SQL Server 2014

SQL-Server-2014

Great Reads

Executing Stored Procedure with User Defined Table Type Parameter in Entity Framework Using EntityFrameworkExtras

by AshishShukla6

This tip describes step by step implementation of executing stored procedure having user-defined table type as a parameter in entity framework using EntityFrameworkExtras 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

SQL Server: Doing String Split And Join

by DiponRoy

How to do string split and join in SQL Server

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.

Latest Articles

SQL Server: Find Table Order/Generation And Relation Details

by DiponRoy

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

SQL Server: Doing String Split And Join

by DiponRoy

How to do string split and join in SQL Server

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.

Select Statement to HTML Table

by Luca Astolfi

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

All Articles

SQL Server 2014

Executing Stored Procedure with User Defined Table Type Parameter in Entity Framework Using EntityFrameworkExtras

26 Sep 2015 by AshishShukla6

This tip describes step by step implementation of executing stored procedure having user-defined table type as a parameter in entity framework using EntityFrameworkExtras package.

SQL-Server-2012

SQL-Server-2014

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

SQL Server: Doing String Split And Join

19 Feb 2019 by DiponRoy

How to do string split and join in SQL Server

SQL-Server-2008R2

SQL-Server-2012

SQL-Server-2014

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

SQL Server Disk Usage Statistics

24 Sep 2015 by Dr. Song Li

This is an example to check the SQL server disk and data file usage statistics.

SQL-Server-2014

How to Split Microsoft SQL Server Table Row Data

18 Dec 2015 by Md. Marufuzzaman

This tip shows you an easy way to split Microsoft SQL Server table row data.

SQL-Server-2005

SQL-Server-2008

SQL-server-2000

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

Creating a SqlDbConnection class in C#

10 Jun 2014 by OriginalGriff

Add the Interface IDisposable to your class definition:public class SqlDbConnection : IDisposableThen add the Dispose method:public void Dispose() { if (cmd != null) { cmd.Dispose(); cmd = null; } if (sda != null) { ...

SQL-Server-2014

Save your Data from LDAP Injection Attack

9 Jul 2014 by Jason Parms

Lightweight Directory Access Protocol (LDAP) Injection

SQL-Server-2008R2

Concatenation of two char variable in sql

1 Dec 2014 by Maciej Los

There is a bit difference between char and varchar[^] data types:char [ ( n ) ] Fixed-length, non-Unicode character data with a length of n bytes. n must be a value from 1 through 8,000. The storage size is n bytes. The SQL-2003 synonym for char is character.varchar [ ( n | max ) ] ...

SQL-Server-2008R2

SQL-Server-2012

SQL-server-2000

Synchronization between local and Azure SQL DB using SLQ Management Studio "Export Data..."

17 Mar 2015 by Sergey Kizyan

This article explains how to merge your local SQL DB content to Azure DB from scratch

SQL-Server-2012

SQL-Server-2014

SQL-Server-2008R2

I AM USING BELOW CURSOR BUT GIVING ME ERROR AS STATED. ANY HELP WILL BE GREATLY APPRECIATED. THANKS IN ADVANCE

10 Apr 2015 by ZurdoDev

1. Please do not shout. Do not use all CAPS.2. See, https://msdn.microsoft.com/en-us/library/ms180169.aspx[^]. You need to use a FOR SELECT statement when you declare the cursor. You are missing select_statement.3. I'm not sure if it makes a difference; however, when I have done cursors I...

SQL-Server-2014

How to get sum and hold multiple rows value in one row SQL server

1 Mar 2017 by Bryian Tan

I think, for now, this query should work. SELECT bank, branch, chqno, SUM(Amount) Amount ,STUFF((SELECT ', ' + CAST(ReceiptNo AS VARCHAR(10)) [text()] FROM dbo.code1 WHERE bank = t.bank FOR XML PATH(''), TYPE) .value('.','NVARCHAR(MAX)'),1,2,'...

SQL-Server-2005

SQL-Server-2008R2

SQL-Server-2014

SQL-server-2000

Update all the rows with first row value

22 Jan 2019 by OriginalGriff

That sounds like an odd thing to do, but... UPDATE MyTable SET empname = (SELECT TOP 1 empname FROM MyTable WHERE empname IS NOT NULL ORDER BY eid ASC)

SQL-Server-2014

Iam trying to insert in the table which identity is true and getting the error

6 Mar 2019 by OriginalGriff

Simple: always name your columns. At the moment you are going:INSERT INTO MyTable VALUES (1, 2) which works only if you have two columns in your table and you know the order, because SQL assumes that the values are to be inserted starting with the "left most column" and movign right. But you...

SQL-Server-2014

what the means of select 123&789

29 Mar 2014 by OriginalGriff

"&" is a binary AND operator: it combines bits in the operand, so if the bit is one in both operands, than the result is the same bit one - other wise it is zero.So, for each bit:Op1 & Op2 Result 0 0 0 0 1 0 1 0 0 1 1 1It's...

SQL-Server-2014

The remote server returned an error: (404) Not Found in asp.net?

14 May 2014 by Bernhard Hiller

First you need to understand the most important part of web applications: what happens on the server, what happens on the client?You show code regarding a WebResponse. A WebResponse is a response to a WebRequest. A WebRequest requests a URL (perhaps with some parameters) from the server....

SQL-Server-2014

how to find recursion data in single query.

18 May 2014 by King Fisher

Refer thishttp://technet.microsoft.com/en-us/library/ms186243%28v=sql.105%29.aspx[^]Result :create table ##table1 (id bigint identity(1,1),case_id bigint,Related_case_id bigint)insert into ##table1(case_id ,Related_case_id) values(11,22)insert into ##table1(case_id...

SQL-Server-2014

How to Import data from "xml type column"

20 May 2014 by Ravi Shankar Dokka

--Creating a temporary table variabledeclare @Temp table( XMLCol xml)--insert your xml datainsert into @Temp values(' 1 Bookpass ')insert into @Temp values(' ...

SQL-Server-2014

What are sources to understand SSIS from basics to advanced?

22 May 2014 by Arjun Holla

http://www.bimonkey.com/about-the-bi-monkey/support/ssis-component-samples-list/[^]

SQL-Server-2008R2

SQL-Server-2005

How to lock particular record in sqlserver using C#

15 Jun 2014 by Debabrata_Das

Hello friend, the following link might help you:6 ways of doing locking in .NET (Pessimistic and optimistic)[^]- DD

SQL-Server-2014

How to lock particular record in sqlserver using C#

15 Jun 2014 by Kornfeld Eliyahu Peter

There is no way to lock a record (or table or index or page or any other SQL 'object') using programming techniques. SQL locks are created by SQL while executing different queries (transactions). You can learn a lot about locking here:...

SQL-Server-2014

Export Primary, Unique, Foreign Key and Default Constraints in SQL Server

26 Sep 2014 by Anil kumar Bhardwaj

Export all the primary, Unique, Foreign key and default constraints from existing database

SQL-Server-2014

SQL-Server-2012

Comparing two tables

29 Oct 2014 by Garth J Lancaster

I think you could use something like select key from t2 where key not in (select key from t1)now, you'll note Ive used 'key' .. iirc, when I first started sql and had to do something like this, I think I basically concatenated the column values together to form a single string... so,...

SQL-Server-2014

When to use Sql Datatype Geometry and Geography ?

21 Nov 2014 by /\jmot

Links..http://techbrij.com/html5-geo-location-sql-server-asp-net[^]http://msdn.microsoft.com/en-us/library/bb895266(v=sql.105).aspx[^]

SQL-Server-2012

SQL-Server-2014

SQL-Server-2008R2

How can I pivot this query?

23 Feb 2015 by CHill60

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

SQL-Server-2014

Amazon Web Services part 2 - Adding a database to your web site

24 Mar 2015 by Matt Perdeck

Add a database to your web site on AWS with automatic fail over using RDS

SQL-Server-2014

How to get the sum of Quarters Amounts

23 Apr 2015 by Maciej Los

Try this:DECLARE @tmp TABLE(MonthCol DATE, [NoProjects] INT, [Amount] INT, [Ab_Zugang] VARCHAR(30))INSERT INTO @tmpVALUES('2014-05-01' , 8 , 3004 , 'Abgang'),('2014-02-01' , 5 , 2314 , 'Abgang'),('2014-11-01' , 10 , 1366 , 'Zugang'),('2014-01-01' , 1 , 37443 ,...

SQL-Server-2014

How to pivot or unpivot

29 Apr 2015 by Maciej Los

Pivoting data is easy as... flail construction ;)Have a look at my "translation" of your sql queries:SELECT [DataDescription], [Luft], [TGL], [BHW], [WM]FROM ( SELECT 'Sales Old' AS DataDescription, [Source], [SalesAmount1] As MySales FROM [dbo].[tblSales] WHERE [Planung]...

SQL-Server-2014

what is the meaning of " if exists(select top 1 null from table t)"

30 Apr 2015 by Karthik_Mahalingam

If row(s) are present in the table it returns true else falseAFAIK no other logic behind, tested.

SQL-Server-2014

Date between in sql command

28 Jun 2015 by Suvendu Shekhar Giri

Try this-SELECT [Date],Price FROM Dataupload WHERE [Date] BETWEEN DATEADD(DAY,-8,GETDATE()) AND DATEADD(DAY,-1,GETDATE())Hope, it helps :)

SQL-Server-2008R2

SQL-Server-2012

SQL-Server-2014

Joining 2 queries output vertically in SQL Server 2014

21 Sep 2015 by Mohammed_Faisal_Majeed

How do i join 2 select queries SELECT DATENAME(DW,Dated) as Days, FinalTime FROM [Attendance].[dbo].[AttnDaily] where EmpID=111 and Dated>='2015-09-14' and Dated

SQL-Server-2014

How to change row to column in SQL

30 Mar 2017 by _Asif_

First your in clause of query is wrong. Correct way of doing it isDECLARE @TBL TABLE(MONTH VARCHAR(MAX),ORDERS VARCHAR(MAX))INSERT INTO @TBL VALUES('Jan','1');INSERT INTO @TBL VALUES('Feb','2');INSERT INTO @TBL VALUES('Mar','3');SELECT 'Assign' Assign ,*,'Total' [Total]...

SQL-Server-2014

Split a comma sperated string in SQL server with []

25 Apr 2017 by CHill60

Split on ] instead of comma, replace the [ and handle the comma at the start of an item if it exists...DECLARE @x nvarchar(max) = '[abc],[cde,cdf]' ;with q as ( select REPLACE(val, '[','') as val2 from dbo.Split(@x, ']') where isnull(val,'') '' ) select case when SUBSTRING(val2,1,1)=','...

SQL-Server-2014

Order of query execution on where and view in SQL server

19 Jun 2017 by AnvilRanger

You say after rebuild the indexes and stats it takes "some time" to return to the 2 hour execution for your query. How long is sometime? Hours, days, weeks? It sounds like what you need is to create a maintenance plan for you DB. This includes index rebuilding, etc. With a table of 100+ million...

SQL-Server-2014

What is windows workflow foundation ?

5 Jul 2017 by OriginalGriff

1) what is windows workflow foundation? - Google Search[^] 2) what is code activity and native activity? - Google Search[^] 3) how to interact SQL server using workflow? - Google Search[^] Quote: can u give real time sample project No - we do not do your homework: it is set for a reason. It...

SQL-Server-2014

I want to upload file in ASP.NET-MVC and angularjs. How can I upload the file using HTML input file control?

19 Jul 2017 by Satya Prakash Swain

@using(Html.BeginForm("UploadFile","Upload", FormMethod.Post, new { enctype="multipart/form-data"})) { @Html.TextBox("file", "", new { type= "file"}) }

SQL-Server-2014

How to dynamically add rows to a gridview through a datatable without affecting any table?

21 Jul 2017 by omerkamran

Answers: 1. To display only the footer template when grid is empty you have to enter an empty row in Gridview and hide that row. You should follow these steps: Dim dt as DataTable dt.Cols.add("column1") dt.Cols.add("column2") dt.Cols.add("column3") dt.rows.Add("","","") ViewState("table")...

SQL-Server-2014

How to pivot/unpivot this table?

14 Sep 2017 by babaiZhere

select lsl = l.SL , lid = l.R_ID , lname = l.SL_HEAD , lamt1 = l.AMT , lamt2 = l.CURR , lamt3 = l.PRE , lpos = l.L_SLIDE , rsl = r.SL , rid = r.R_ID , rname = r.SL_HEAD , ramt1 = r.AMT , ramt2 = r.CURR , ramt3 = r.PRE , rpos = r.L_SLIDE from...

SQL-Server-2014

How to run SQL query with a update statement, where clause and order by/

14 Sep 2017 by OriginalGriff

The error message is pretty clear: Quote: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions unless TOP, OFFSET or FOR XML is also specified. Try removing it, or add TOP or OFFSET to make it worthwhile - without them, it serves no...

SQL-Server-2014

C# 2 different mssql connection

27 Sep 2017 by OriginalGriff

Yes, all you need is two SqlConnection objects. You can't "combine" two servers in the the same query: for example you can't use a JOIN to SELECT related records from two tables on different servers. But you can issue SQL commands to one or the other. What exactly are you trying to do?

SQL-Server-2014

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

Keyword:"provider" not supported?

23 Oct 2017 by OriginalGriff

SQL does not support the "Provider" keyword - remove it up to and including the first semicolon and it should work. But do yourself a favour and never hard-code connection strings. The number of place you have to change it when you release to production is ridiculous and generally means your...

SQL-Server-2014

Sql round function not working as expected

8 Feb 2018 by Maciej Los

Take a look at MS Access documentation: Quote: The Microsoft Access Round function returns a number rounded to a specified number of decimal places. However, the Round function behaves a little peculiar and uses something commonly referred to as bankers rounding. So before using this function,...

SQL-Server-2014

Credit debit balance calculation on SQL

19 Feb 2018 by Bryian Tan

You can use the same concept from here: How do I account for null values in a running total?[^] Only different is, first the query need to find the different between the debit and credit, then run the running total calculation. See below as an example. DECLARE @RunTotalTestData TABLE ( Id ...

SQL-Server-2014

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

I want to get rotational shift in-out result

30 May 2018 by CHill60

You had the right idea using ROW_NUMBER and modulus (%) you were just slightly over-complicating things. This query gets the data in a tidy formatselect E.PunchID, iDateTime, CASE WHEN ((ROW_NUMBER() OVER(partition BY [sEnrollNumber] ORDER BY [iDateTime] ASC))%2) = 0 THEN 'IN' ELSE 'OUT' END as...

SQL-Server-2014

I like to insert total doctors in a department by getting a value from doctor table

26 Jul 2018 by OriginalGriff

Your inner query: Select Count(*) from department Dep, Doctor do Where Dep.De_ID = Do.De_ID Group by Dep.Name Returns more than one row, because it is a GROUP BY query and that is what it is supposed to do! But the outer query expects only one value, so you get an error. Look at using INSERT...

SQL-Server-2014

Image not display in datalist using network folder path stored in SQL server using ASP.NET

14 Aug 2018 by ZurdoDev

Images are loaded from the client. When you put in the url to a page the web server processes the code and then returns html only. In that html you have images so the client then tries to request the images. So, the client has to have access to that same path. What you need to do is put...

SQL-Server-2014

System.argumentexception: 'format of the initialization string does not conform to specification starting at index 35.'

31 Jan 2019 by Richard MacCutchan

connectionString="Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename='|DataDirectory|\db\RetilShop_POS;Integrated Security=True" providerName="System.Data.SqlClient"/> You have mismatched single and double quotes in your text.

SQL-Server-2014

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

Oracle query to SQL query conversion issues

24 Feb 2019 by CHill60

First understand what VC_Days, VC_Hrs, VC_Min, VC_Secons are and get rid of those ridiculous calculations. It appears that you are trying to express some dates as days, hours, minutes and seconds by truncating a value (note that 86400 = 60 * 60 * 24 - i.e. the number of seconds in a day) ...

SQL-Server-2014

Sql 2014 registry entry

26 Feb 2019 by Maciej Los

[EDIT#3] Check this: File Locations for Default and Named Instances of SQL Server - SQL Server | Microsoft Docs[^] Sql Server 2014 code is 120, so registry key is: HKLM\Software\Microsoft\MicrosoftSQL Server\120 Quote: Common files used by all instances on a single computer are installed in...

SQL-Server-2014

How to subtract two values in SQL server which are in different columns in the same table

10 May 2019 by Maciej Los

Try this: SELECT A, B, A-B AS C, B-A AS D FROM YourTable

SQL-Server-2014

How to update data in texbox control by dropdownlist selected value using stored procedure ?

8 Aug 2019 by Vincent Maverick Durano

First off, you should never hard code your database connection string. Instead define your connection string in the web.config and reference the value from there. See: Creating a Connection String and Working with SQL Server LocalDB | Microsoft Docs[^] For example:

SQL-Server-2014

I have a problem getting results from SQL database for two dates in datepicker

22 Sep 2019 by Dave Kreskowiak

You're using an Access query with SQL Server. Obviously, that doesn't work. Also, NEVER use string concatenation to build an SQL query. ALWAYS use parameters. In Access, parameters are denoted with a ? and no name. In SQL Server, they are named parameters denoted with a @: sqlQry = "SELECT...

SQL-Server-2014

I have an error while running an SQL query using parameters method (child list cannot be created

30 Sep 2019 by OriginalGriff

We can't help you based on that info: we have no idea what the base query you are appending to is, and that's probably relevant. I'd start by using the debugger: find out what exactly is in sqlQry when you create the SqlDataAdapter, and what exactly is in each parameter you pass over. At a...

SQL-Server-2014

What is the best database solution to be used with network shared drive using VB.NET application

4 Oct 2019 by OriginalGriff

Don't use Access as a multiuser DB - while you can do it, it's not a good idea and will always give you problems. For a 40 user network, I'd allocate a machine to SQL Server, and use that instead*. SQL Server is designed from multiuser working, and is much, much better at it - especially if your...

SQL-Server-2014

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

Sql string doesnt accept combobox selected text

20 Nov 2019 by OriginalGriff

Don't do it like that! Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead. When you concatenate strings, you cause problems because SQL...

SQL-Server-2014

how to get the character from string in SQL ?

28 Mar 2014 by OriginalGriff

Yes...but it's not at all simple, and it would be a much, much better idea to separate those into separate rows in a different table, with a foreign key relationship back to this one. The problem is that SQL string handling is...basic...and it doesn't know about comma separated data, or arrays,...

SQL-Server-2008R2

SQL-Server-2012

SQL-Server-2014

what the means of select 123&789

29 Mar 2014 by Mehdi Gholam

& is the bit wise AND so 1&2 (decimal) == 01 and 10 (binary) = 0 etc.

SQL-Server-2014

Need to map and import into relational access database from a flat file having the many relationships as columns

13 May 2014 by Jörgen Andersson

Your tags claim that this is about SQL-Server-2014 while your text claims that you're using MS Access.If I assume that your tags are correct I'd tell you use Unpivot[^].But if you indeed are using Access there is no Unpivot available. So then you have (atleast) four choices. And I don't...

SQL-Server-2014

How to remove folder name from URL in asp .net

15 May 2014 by Keith Barrow

Hi, The phrase you are looking for is "URL Re-writing". This MSDN article has info about it[^] - it focuses on getting rid of the "foo.aspx" but of ugliness, though you can specify practically anything (including the directory) using the same principle. You might also find ScottGu's blog...

SQL-Server-2014

how to find recursion data in single query.

18 May 2014 by DamithSL

WITH q AS ( SELECT * FROM Tbl1 WHERE Case_ID=11 UNION ALL SELECT m.* FROM Tbl1 m JOIN q ON m.Case_ID = q.Related_Case_ID )SELECT *FROM qDEMO[^]

SQL-Server-2014

how to find previous & forward relation of data in single query.

20 May 2014 by Maciej Los

Have a look here: SQL Wizardry Part Three - Common Table Expressions (CTEs)[^]. There you'll find a sample query to get hierarchical data.

SQL-Server-2014

how to find previous & forward relation of data in single query.

20 May 2014 by soumyajayaraj

Try thisDECLARE @PersonId INT =10;WITH ForwardRelationsCTE AS( SELECT Person_ID, Person_Relative_ID FROM dbo.Relations_Table WHERE Person_ID = @PersonId UNION ALL SELECT t1.Person_ID , t1.Person_Relative_ID FROM dbo.Relations_Table AS t1 JOIN ForwardRelationsCTE AS...

SQL-Server-2014

I want to get one row of record from the 1 lack of rows

30 May 2014 by Raul Iloc

The faster data fetching is storing and accessing data from the database, in your case using SQL database will be faster then using any files (XML, JSON, etc), if your data amount is not only few records.

SQL-Server-2014

Error while Populating a Worksheet from a DataSet

3 Jun 2014 by Bhushan Patki

Once you deploy your code in any server. Due to security concern Office is not installed there. My suggestion is to use these library EpplusRead these ArticleSupport for Office has ended

SQL-Server-2014

How to Create a facebook App?

6 Jun 2014 by Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)

You should start something yourself. Before that read the documentations and make yourself equipped.Start from Getting Started with the Facebook SDK for .NET and ASP.NET[^].

SQL-Server-2014

Sqlclient exception error - sql server 2014

9 Jun 2014 by ZurdoDev

The problem is this line of code:SqlCommand cmd = new SqlCommand("select * FROM UsersTbl WHERE UserName =" + userName, connToDB);If you read the lines after it you add a parameter named @UserName but you never use it. Your code should beSqlCommand cmd = new SqlCommand("select *...

SQL-Server-2014

Sqlclient exception error - sql server 2014

9 Jun 2014 by Richard MacCutchan

You have (correctly) used a parameterized query, but you added the value rather than the parameter name to your select statement. It should be: SqlCommand cmd = new SqlCommand("select * FROM UsersTbl WHERE UserName = @UserName", connToDB); SqlParameter param = new...

SQL-Server-2014

How to lock particular record in sqlserver using C#

18 Jun 2014 by Sprint89

You are talking about security rather than locking. You should limit access to the database then set up role-based security in your code. When a user tries to view/change data, check their credentials and limit their access in your code rather than by locking the database

SQL-Server-2014

Sql Server 2014 installation error

19 Jun 2014 by Sumit Bhargav

hi,I have been trying to install sql server 2014 for many days now but have not succeeded yet.I have sql server 2008 R2 Express edition installed on my windows 8.1 and after removing it i am trying to install original sql server 2014 but receiving the...

SQL-Server-2014

A potentially dangerous Request.Path value was detected from the client (&)?

3 Jul 2014 by Peter Leow

Refer: getting-a-potentially-dangerous-request-path-value-was-detected-from-the-client[^]

SQL-Server-2014

Dynamic Category and Sub category Database Design

14 Jul 2014 by RAHUL(10217975)

Hello All,I want to design database for my application. In my application users are allowed to create category, subcategory and their attributes. One SubCategory can have more than one attribute of different datatypes. User will select CategoryID,SubCategoryID and add value to its...

SQL-Server-2014

SQL-Server-2008R2

Dynamic Category and Sub category Database Design

14 Jul 2014 by Sergey Alexandrovich Kryukov

You are trying to badly deviate from relational model. At the same time, all your problems are simply resolved in the relational model. First, let's start from the notion of "sub-category". You can have just one table "Category" with the key "Id". If you add the columns "ParentCategory" which...

SQL-Server-2014

SQL-Server-2008R2

Dynamic Category and Sub category Database Design

14 Jul 2014 by Maciej Los

I'd suggest you to read about recursive hierarchies[^]. You should have only 2 tables:Categories:CatID INT IDENTITY(1,1)Parent INT (related to CatID)CatName NVARCHAR(50)... (other fields) andCategory_Attributes:AttID INT IDENTITY(1,1)CatID INT (related to CatId in...

SQL-Server-2014

SQL-Server-2008R2

What are the steps to tune the SQL Queries?

29 Jul 2014 by Nirav Prabtani

refer this.. :)How To: Optimize SQL Queries[^]SqlServer Query Optimization Tips[^]Query Optimization Tips[^]SQL Tuning/SQL Optimization Techniques:[^]SQL SERVER – Tips for SQL Query Optimization by Analyzing Query Plan[^]Top 10 steps to optimize data access in SQL Server:...

SQL-Server-2008R2

SQL-Server-2014

SQL Query for fetching Hierarchical value from Parent Child Relationship

8 Sep 2014 by _Asif_

Tough i am not happy with below solution but this could get you going.DECLARE @TBL TABLE( PARENTID INT, CHILDID INT)DECLARE @TAGERTTBL TABLE( PARENTID INT, CHILDID INT, Level int IDENTITY(0, 1))INSERT INTO @TBL(PARENTID, CHILDID)SELECT 1, 11 UNION...

SQL-Server-2012

SQL-Server-2014

SQL-Server-2008R2

How Do I Read A Specific Users Info From Sql C#

8 Sep 2014 by Richard Deeming

Some suggestions:Always use parameterized queries to pass parameters to a query, never string concatenation.(Not a problem in the code you've posted, but writing code that's susceptible to SQL Injection[^] seems to be very popular at the moment!)Don't call reader.NextResult() within the...

SQL-Server-2014

how to read from multiple sql tables (knowing only the column names and not table names) c#

15 Sep 2014 by Wendelius

Now, in order to make a query that makes sense, you need to know the database structure. That's a must.When you know the table names as you have listed in your query you typically need a join to correctly join rows in both tables. Have a look at join definitions from...

SQL-Server-2014

How Do I Correctly Use Scope_Identity C#

1 Oct 2014 by SteveyJDay

You don't create the identity before the insert. SQL Server will handle that for your. Also don't insert the identity column.Original Solution:using (SqlConnection sqlcon = new SqlConnection("Server")) { using (SqlCommand myCommand = new SqlCommand("INSERT INTO cas_user_ex(fullname,...

SQL-Server-2014

Inserting the records

13 Oct 2014 by Garth J Lancaster

plenty of sql references on google for the sql insert statement, like this (http://www.w3schools.com/sql/sql_insert.asp[^]So you need insert into table (field1, field2, field3, field4, field5) values(value1, value2, value3, value4, value5);orinsert into table (field1, field2,...

SQL-Server-2014

How do I generate a report from SQL to MS Excel?

29 Oct 2014 by Peter Leow

Check this out: export data from SQL Server to Excel or any other Format[^]

SQL-Server-2014

How do I generate a report from SQL to MS Excel?

29 Oct 2014 by Maciej Los

Alternativelly...You can connect to SQL database using MS Excel functionality. Please, have a look here: Excel: Connect to (import) SQL Server data[^]

SQL-Server-2014

When to use Sql Datatype Geometry and Geography ?

19 Nov 2014 by Er. Puneet Goel

I an new to this geospatial functionality of SQL server. Although the MS BLogs helps us most to learn this but i am still not sure of the differences of when we use geometry and when to use geography ?

SQL-Server-2012

SQL-Server-2014

SQL-Server-2008R2

how to upload more than 2GB video in asp.net application

18 Dec 2014 by christhuxavier

Hello,I am trying to upload large video into sql server through our asp.net application.( Using File uploader and Bytes concept)..i created column type in sql server as varbinary(MAX); for video uploading. from database side we can fix using filestream concept for larger...

SQL-Server-2014

Could not find the provider 'dbDacFx'.

2 Jan 2015 by Suvendu Shekhar Giri

You can try reinstalling webdeploy to make sure you have successfully installed dbDacFx Provider.Check this link for similar error resolveddbDacFx Error Importing IIS Deploy Package[^]Hopefully, it helps :)

SQL-Server-2014

I want to transfer rows to Column SQL Server

22 Jan 2015 by Rajesh waran

Check this: create table #table1(Item_ID nvarchar(max),Item nvarchar(max),category_name nvarchar(max),balance float,branch_name nvarchar(max))insert into #table1(Item_ID,Item,category_name,balance,branch_name) values('PD-0000002DR','ABC','Drink',30000,'Sensok')insert into...

SQL-Server-2008R2

SQL-Server-2012

SQL-Server-2014

sql query with if condition?

23 Jan 2015 by Wendelius

You can use the CASE as you have tried. Sommthing like:select O.OrderID,D.OrderDetailID,ProductName,case when D.OrderDetailID != R.OrderDetailID then '1' else '0'end as ColNameFrom dbo.vOrderDetails D Join tblOrders O On O.OrderID = D.OrderID Join...

SQL-Server-2014

how to use html tags in ms access and sql server?

25 Jan 2015 by Suvendu Shekhar Giri

If you want to store html tags in database, there should not be any problem. Then when you need to show them in front end/application, you can just assign them to a label or literal.But, if you are talking about using font styling in the query window you can always go to In SQL...

SQL-Server-2014

Dynamic SQL Server stored procedure

28 Jan 2015 by Liju Sankar

Hi mikybrain,I would suggest you to run your dynamic query and use SQL Profiler to grab the parsed T-SQL script from SQL Profiler and convert that SQL to stored procedures.If you dont have rights to execute SQL profiler, you can also try Express Profiler available in the link to grab...

SQL-Server-2014

How to share a session details to multiple domain?

31 Jan 2015 by Sinisa Hajnal

Session What you CAN do is post the data to your second site and put it in session variable in the second application.Or you can use domain cookie if you're asking only and specifically about subdomain sites. Something like thisResponse.Cookies("CookieName").Domain =...

SQL-Server-2014

How to share a session details to multiple domain?

31 Jan 2015 by Zoltán Zörgő

If you have IIS infrastructure, you can share session state between servers. Look here: http://dotnetcodr.com/2013/07/01/web-farms-in-net-and-iis-part-5-session-state-management/[^].But I am not sure, this is what you need. It looks like you need SSO (single sign-on). There are several...

SQL-Server-2014

Get Ready to Learn SQL Server 19: Introduction to Sub Queries

4 Feb 2015 by essentialSQL

The purpose of this article is to introduce you to subqueries and some of their high-level concepts. There are a lot of details to cover in order to learn sub queries, but you’ll see we cover those in depth in later articles.

SQL-Server-2014

SQL-Server-2012

Search Employee by JoinYear and LeaveYear

15 Feb 2015 by OriginalGriff

First thing to look at is the actual values you are comparing: if they are dates (and it's normal to store Join dates and leave dates as DateTime rather than just a year value as almost nobody starts of finishes on Jan 1st) then equality tests don't often work as they are accurate to...

SQL-Server-2008R2

SQL-Server-2012

SQL-Server-2014

Search Employee by JoinYear and LeaveYear

15 Feb 2015 by Wendelius

Not sure if I understood your requirement correctly, but if the data is in date format you need to extract the year portion. So perhaps somethig likeSELECT ...FROM TableNameWHERE DATEPART(year, JoinDate)

SQL-Server-2008R2

SQL-Server-2012

SQL-Server-2014

How to change MDF file default properties

19 Feb 2015 by Wendelius

Normally a file with MDF extension is used by SQL server for the first (primary) database file.This file contains data from the database in a binary format and it is not intended to be opened by anything else than the SQL Server engine.Never store any SQL queries or anything else in MDF...

SQL-Server-2014