Click here to Skip to main content

Submit your article

SQL Server 2008R2

SQL-Server-2008R2

Great Reads

Update Multiple Rows of GridView using JSON in ASP.NET

by Debabrata_Das

This article will talk about how to update multiple records in a GridView on a single button click using JSON in ASP.NET

Debug ASP.NET MVC Application with Glimpse

by Muhammad Hassan Tariq

Glimpse Extension for server side debugging and diagnostic information of ASP.NET applications

SQL Server Procedure to Report Duplicates

by PIEBALDconsult

A stored procedure I use to find duplicate records in tables and views.

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

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

AngularJS MVC Repository Dispose

by Mahsa Hassankashi

This is the simplest tutorial on AngularJS and Repository Pattern for developing professional MVC applications. It includes a source code about practical permission management project.

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.

All Articles

SQL Server 2008R2

Update Multiple Rows of GridView using JSON in ASP.NET

20 Jun 2014 by Debabrata_Das

This article will talk about how to update multiple records in a GridView on a single button click using JSON in ASP.NET

SQL-Server-2008R2

Debug ASP.NET MVC Application with Glimpse

11 Jan 2015 by Muhammad Hassan Tariq

Glimpse Extension for server side debugging and diagnostic information of ASP.NET applications

SQL-Server-2008R2

SQL Server Procedure to Report Duplicates

13 Dec 2014 by PIEBALDconsult

A stored procedure I use to find duplicate records in tables and views.

SQL-Server-2008R2

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

Creating Report using Report Builder and Oracle DB - Part 2

28 Dec 2014 by Snesh Prajapati

In this article series, I am sharing how to create report using Report Builder by fetching data from Oracle DB.

SQL-Server-2008R2

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

How do I use "between" in SQL Case ?

29 Sep 2015 by Wendelius

You can't use case just like that but there are several ways to do the comparison. For example:SELECT COUNT (DISTINCT Table.users) FROM Table WHERE Table.Age BETWEEN CASE @cat WHEN 1 THEN 18 ELSE 21 END AND CASE @cat WHEN 1 THEN 20 ELSE 42 END;ADDITION:If...

SQL-Server-2008R2

How to insert 5 lac record into sql server

22 Jul 2014 by OriginalGriff

Use the SqlBulkCopy[^] class.

SQL-Server-2008R2

What is a self referencing table

15 Oct 2014 by Sergey Alexandrovich Kryukov

First of all, you can never have two primary keys: http://en.wikipedia.org/wiki/Unique_key[^].Don't confuse this statement with the situation when you can have a compound key, which can be uses as a primary key, but it still has to be unique:...

SQL-Server-2008R2

Uploading the Files – HTML5 and jQuery Way!

28 Nov 2014 by Afzaal Ahmad Zeeshan

Uploading the files - HTML5 and jQuery way

web_development

Money Precision Issues

15 Feb 2015 by Bohdan Stupak

This tip shows some primary issue which puts a serious limitation on SQL Server MONEY data type.

SQL-Server-2012

SQL-Server-2008R2

SELECT DISTINCT on one column, with multiple columns returned, ms access query

26 Mar 2015 by OriginalGriff

Try:WITH cte AS( SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY val1 DESC) AS rn FROM MyTable)SELECT *FROM cteWHERE rn = 1

SQL-Server-2008R2

How Can I Hide The Password In Sql Server

12 Apr 2015 by Kornfeld Eliyahu Peter

Do not store passwords in the first place, but a kind of a one-way hash, better with some salt (and pepper) added...http://en.wikipedia.org/wiki/Cryptographic_hash_function[^]http://en.wikipedia.org/wiki/Salt_%28cryptography%29[^]

SQL-Server-2008R2

convert decimal to string

29 Apr 2015 by CPallini

You don't need anything: text box content (that is the Text property) is already a string.

SQL-Server-2008R2

How do generate list from value row in sql that is seperated by comma

13 Nov 2015 by OriginalGriff

The best solution is: don't. Store the values separately in a separate table that uses the student table roll no and Citylist table ID as foreign keys, and each row references a pair of keys - you can then use JOIN to populate your result.You can do what you want using your method, but it's...

SQL-Server-2008R2

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

Long running join in sql server is taking a lot of time

6 Feb 2016 by CHill60

As suggested by PIEBALDconsult replace the WHERE with a joine.g. LEFT join Last_Authorised_Range lar on lar.Range_Event_Id = srg.Range_Event_Id WHERE lar.Range_Event_Id is nullIndexes on the ID fields in the tables should also help.As Member 10454138 points...

SQL-Server-2008R2

How do Hide the column of gridview on the basis of the status of any db value ?

4 Apr 2014 by Schatak

you can Usegridview.Columns[Index].visible=false;note: index of columns start from 0

SQL-Server-2008R2

Using Stored Procedures in Entity Framework Model First Approach (Part I - Adding New Stored Procedure)

15 Apr 2014 by SRS(The Coder)

How to use Stored Procedures in Entity Framework Model First Approach

SQL-Server-2008R2

SQL-Server-2012

I wanna Skip 6 lines from Excel Sheet and Bind the Remaining Values to Database

22 May 2014 by DamithSL

you can do it with Linq GridView1.DataSource = dtExcelRecords.AsEnumerable().Skip(6).CopyToDataTable();Ref:http://msdn.microsoft.com/en-us/library/bb396189(v=vs.90).aspx[^]

SQL-Server-2008R2

retrieve millions record from SQL server in seconds

15 Jul 2014 by Maciej Los

It's impossible!Please, have a look at past discussion[^]

SQL-Server-2008R2

No More Stray User Tables in Master

19 Jul 2014 by Tim Golisch

The Master database often seems to be the "Lost & Found" bin of the SQL Server world. There are two popular ways for preventing this kind of problem. Read on...

SQL-Server-2012

SQL-Server-2008R2

Entity Framework Code First Migrations Alternative

30 Jul 2014 by DiponRoy

How to handle database scheme changes with normal SQL scripts, without migration in Entity Framework code first

SQL-Server-2008R2

entity-framework

how does work Execution plan

9 Sep 2014 by Gihan Liyanage

Try Google first.You Answer is here. Execution plan[^]

SQL-Server-2008R2

What is the format of this 0x000000001D4AE901

31 Jan 2015 by CPallini

Is is the hexadecimal representation of a 64 bit number.

SQL-Server-2008R2

how to get sum of result from sql query

18 May 2015 by Maciej Los

Replace this:select distinct(SingleUser),(sum(Quality)/COUNT(SingleUser)) as QualityPercentage, 100-sum(Quality)/COUNT(SingleUser ) as ErrorPercentagefrom UserTotalswith:SELECT SingleUser, AVG(Quality) as QualityPercentage, 100-AVG(Quality) as ErrorPercentageFROM UserTotalsGROUP BY...

SQL-Server-2008R2

Creating a SSRS Report With Microsoft Dynamics CRM On-line

12 Jun 2015 by coded007

Designing reports with Dynamics CRM

SQL-Server-2012

SQL-Server-2008R2

Unexpected rounding-off from DATETIME2 to DATETIME

23 Jul 2015 by Wendelius

Datetime2 is more accurate than datetime which has only accuracy for milliseconds.From datetime2 (Transact-SQL)[^]: When the fractional precision of the datetime2(n) value is greater than three digits, the value will be truncated.The following example shows the results of converting a...

SQL-Server-2012

SQL-Server-2008R2

In sql query as follows

11 Aug 2015 by Maciej Los

Try this:SELECT [Name], CASE WHEN [Designation]='Director' THEN 'Authorisedsignatory' ELSE [Designation] END AS NewDesignationFROM Designation orSELECT [Name], CASE WHEN [Headtype]=1 THEN 'Authorisedsignatory' ELSE [Designation] END AS NewDesignationFROM Designation

SQL-Server-2008R2

SQL Query Containing IN Clause and GROUP BY CLAUSE

18 Aug 2015 by OriginalGriff

Try:SELECT * FROM MyTable mJOIN (SELECT MAX(Id) AS MaxId FROM MyTable GROUP BY ForeignId) jON m.Id = j.MaxId

SQL-Server-2008R2

Reading Acrofields from PDF Files

29 Sep 2015 by DotNetSteve

Variety is the spice of life - rewarding PDF AcroForm reader

SQL-Server-2008R2

How to split int and other characters from a string

28 Oct 2015 by phil.o

A regular expression seems to be what you need here:using System.Text.RegularExpressions;Regex regex = new Regex(@"^(?[0-9]+)(?[^0-9]+)$", RegexOptions.Compiled | RegexOptions.CultureInvariant);string input = "12pcs";Match m = regex.Match(input);int quantity =...

SQL-Server-2008R2

Please help me to solve this code I'm getting error

21 Apr 2016 by Matt T Heffron

Since your comments don't match the names of the variables to which you are assigning, I'd guess that you're putting the wrong values into your update statement. (See **MISMATCH** comments below!)Also, your where clause is wrong. You aren't using the ID value from the row of the GridView.I...

SQL-Server-2008R2

How to update data in table?

25 Apr 2016 by Suvendu Shekhar Giri

You can use UPDATE with JOIN to do this.Check something like following-UPDATE S SET S.salary=S.salary+(S.salary*(CASE WHEN R.[role]='a' THEN 0.2 WHEN R.[role]='c' THEN 0.3 ELSE 0 END))FROM sal SINNER JOIN map M ON S.empid=M.empidINNER JOIN rolemaster R ON R.roleid=M.roleidI...

SQL-Server-2008R2

SQL-Server-2012

Sql select field value if not null

23 Jun 2016 by Maciej Los

The simplest way is to use COALESCE function[^].SELECT COALESCE(tel, mob) as Saleable FROM ProductUsing CASE ... WHEN ... END statement:SELECT CASE WHEN tel IS NULL THEN mob ELSE tel END as Saleable FROM Product

SQL-Server-2008R2

Sql server replication problem

16 Apr 2017 by Member 10914736

Hi There,I have one stage server database in which consists of 20 tables. These to be transferred from this server to another server, say to production server. Previously, our dba’s are replicated those and they have scheduled hourly and daily snapshots replication, so every hour and daily a...

SQL-Server-2008R2

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

Need help in SQL query

27 May 2013 by Maciej Los

It should works:SELECT t1.ID, t1.SKU, t1.PriceFROM YourTable AS t1 RIGHT JOIN ( SELECT SKU, MAX(ID) AS ID FROM YourTable GROUP BY SKU ) AS t2 ON t1.ID = t2.IDORDER BY ID

SQL-Server-2008R2

SQL-Server-2005

i don't want to pass worked_dates dynamically ,i want to pass worked_dates in between range in pivot

4 Apr 2014 by Schatak

TRY THIS:DECLARE @cols AS NVARCHAR(MAX);DECLARE @query AS NVARCHAR(MAX);select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Worked_dte) FROM User_Timesheet FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') , 1, 1, '');SELECT @query = 'SELECT *...

SQL-Server-2008R2

How to Get Words Separated in a String Concatenated by Delimiter

14 Apr 2014 by Ankur .K. Vishwakarma

This tip shows you a T/SQL function to split a string concatenated by a delimiter.

SQL-Server-2008R2

Regarding Combine 2 tables , 2nd table rows into columns

22 May 2014 by Maciej Los

Alternatively to solution1 by Sergey Vaselenko, you can join two pivots:DECLARE @Questions TABLE (QID INT IDENTITY(1,1), Question VARCHAR(300))INSERT INTO @Questions (Question)VALUES('select fruits:'),('select animals:'),('Select flowers:')DECLARE @QuestionOptions TABLE (QOID...

SQL-Server-2008R2

how to get last date of the month from the current date in sql server

27 May 2014 by Awadhendra Tripathi

CREATE FUNCTION [dbo].[udf_GetLastDayOfMonth]( @Date DATETIME)RETURNS DATETIMEASBEGIN RETURN DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, @Date) + 1, 0))ENDQuery:DECLARE @date DATETIMESELECT @date = '2013-05-31 15:04:10.027'SELECT DATEADD(d, -1, DATEADD(m,...

SQL-Server-2008R2

Save your Data from LDAP Injection Attack

9 Jul 2014 by Jason Parms

Lightweight Directory Access Protocol (LDAP) Injection

SQL-Server-2008R2

Get rid of white spaces at end of field from database?

12 Aug 2014 by PIEBALDconsult

Try using VARCHAR rather than CHAR.char and varchar (Transact-SQL)[^]•Use char when the sizes of the column data entries are consistent.•Use varchar when the sizes of the column data entries vary considerably.

SQL-Server-2008R2

How to work withLong Running Processes using ASP.NET with C#

13 Aug 2014 by Manfred Rudolf Bihy

Don't do it directly from the webpage is my advice. Why is that so, you might ask. Simples:Worker processes are recycled regularily and thus your threads will also stop running and you won't even know how much of the work has been done and where to resume (if this has been coded into the...

SQL-Server-2008R2

[Noob alert] How to include a db in to windows form app so that we don't need any other installation at client side

13 Aug 2014 by Sergey Alexandrovich Kryukov

Your dad needs to install the SQL server, period. Moreover, even though deployment of database service with the application is the acceptable practice, it all heavily depends on licensing models, actually available licenses and maybe business setting. Therefore, the choice #1 is still just...

SQL-Server-2008R2

SQL Server Add extra columns in Select Query

25 Aug 2014 by CHill60

Well I found a way to get most of the way there using PIVOT and PARTITION and a temporary table but it's not elegant and it doesn't exactly match your expected outputs ...Firstly, the id in your expected results seems meaningless as you want to combine multiple ids into a single set of...

SQL-Server-2008R2

problem with left join in sql query

24 Sep 2014 by Gihan Liyanage

Use This Query ,SELECT C.student_id, C.studentname, M.Guardian_NameFROM TableStudent C OUTER APPLY ( SELECT TOP 1 * FROM TableGuardian M WHERE M.student_id = C.student_id ) mSince you want to get any guardian I use Top...

SQL-Server-2008R2

How to validate TextBox inside Gridview using JavaScript

12 Oct 2014 by Rajesh waran

Refer this,http://www.xpode.com/ShowArticle.aspx?ArticleId=278[^]http://weblogs.asp.net/varadam/how-to-validate-textbox-inside-gridview-using-javascript[^]http://www.c-sharpcorner.com/UploadFile/abhijmk/how-to-validate-gridview-fields-in-Asp-Net/[^]

SQL-Server-2008R2

What is a self referencing table

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

Quote:for example a drop down for city and a drop down for province ?This is very simple. Two tables that's it.1. City - CityId, CityName2. Province - ProvinceId, FK_CityId, ProvinceNameWhile binding DropDownList for City, directly bind from City Table. The DropDownList for Province...

SQL-Server-2008R2

What is a self referencing table

15 Oct 2014 by User 11060979

A self referencing table is a table which has a field which is defined as foreign key to the same table's primary key.CREATE TABLE DBREGISTRY( ID INTEGER, PARENT INTEGER, // PARENT references ID of table DBREGISTRY DESCRIPTION CHAR(30), INTVALUE INTEGER, ...

SQL-Server-2008R2

multiple value insert ,delete and update in store procedure & trigger

28 Oct 2014 by PhilLenoir

It is all there in the T-SQL documentation:Update (http://msdn.microsoft.com/en-us/library/ms177523.aspx[^]):An update query is always effectively for multiple rows for a set of rows defined by the WHERE clause (or FROM clause join). UPDATE table set field = value will update every row in...

SQL-Server-2008R2

stored-procedure

How do I CTE for this

30 Oct 2014 by Maddy selva

DECLARE @ID intDECLARE @cnt intDECLARE IDs CURSOR LOCAL FOR select advid from #tableCreate table #table1(advId int ,name nvarchar(100),cont bigint)OPEN IDsFETCH NEXT FROM IDs into @IDWHILE @@FETCH_STATUS = 0BEGIN with cte (AdvId,ReferId,Level)as( select AdvId,ReferId,1...

SQL-Server-2008R2

How do I CTE for this

30 Oct 2014 by Maciej Los

The simplest way:SELECT advId, name, (SELECT COUNT(*) FROM #table WHERE ReferId = t1.advId AND ReferalRank = 'MGR') AS CountOfMgrFROM #table AS t1[EDIT]Sorry, King_Fisher, because above query does not meets your needs. It counts the employees in direct relationship to its chief ;)...

SQL-Server-2008R2

the name 'Rollnumber' does't exist in the current context? why?

1 Nov 2014 by DamithSL

it seems you need to search based on input str, but your method expect integer value and that RollNumber is not defined. try below ( assume you have numbers as input str)int RollNumber = Convert.ToInt32(str);ds = objdal.FindRecord(RollNumber);

SQL-Server-2008R2

how to insert kruti font plus English font(times new roman) into database

9 Nov 2014 by Manas Bhardwaj

SQL Server has no knowledge about the fonts. What it knows is only the data type and the encoding of the text.What you can do is define a columns of NVARCHAR [^]type.What it means is that NVARCHAR can store Unicode [^]data. Thus, it's upto you if you want to store the text in Hindi,...

SQL-Server-2008R2

How to compare 2 records of same table?

22 Nov 2014 by Tomas Takac

As Andre pointed out your input data seems to be incorrect. I took the liberty of changing the modified time stamps for the last two records. Now to get the results you need, first assign a sequence number based on the time stamp. Descending order will help you identify the current schedule...

SQL-Server-2008R2

SQL Server Query question

25 Nov 2014 by PIEBALDconsult

Maybe this will help. LEFT JOIN the list of DISTINCT names to the data and use ISNULL:(take two)DECLARE @yr INTEGER = 2014DECLARE @mn INTEGER = 10DECLARE @ym INTEGER = @yr*100+@mn;WITH src AS( SELECT 'John' [name] , 0.50 [value] , 11 [month] , 2014 [year]UNION ALL ...

SQL-Server-2008R2

Convert rows into Column in SQL Server

25 Nov 2014 by Maciej Los

Here is a basic idea:Dynamic PIVOT in Sql Server[^]T-SQL: Dynamic Pivot on Multiple Columns[^]Script to create dynamic PIVOT queries in SQL Server[^]Pivots with Dynamic Columns in SQL Server 2005[^]

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

selecting from different condition

4 Jan 2015 by Suvendu Shekhar Giri

Oh dear ! No need to write 720 if else conditions. Only a single line of code can do the same job. A small trick to be implemented using logical OR operator.query="select * from table1 where ('"+txt1.Text+"'='' OR col1='"+txt1.Text+"') and ('"+txt2.Text+"'='' OR col2='"+txt2.Text+"') and...

SQL-Server-2008R2

Pass Parameters from Web Page to Telerik Report

19 Jan 2015 by Thinira

In this tip, we discuss how to pass report parameters from web page to Telerik report programmatically.

SQL-Server-2008R2

I need of Query in sqlserver2008 to get data avoiding null values fileds

26 Jan 2015 by Peter Leow

Try this:select isnull(firstname, '') + isnull(middlename, '') + isnull(lastname, '') as result from table1Read: ISNULL (Transact-SQL)[^]

SQL-Server-2008R2

What is the maximum number of databases that an sql server 2008r2 can hold.

28 Jan 2015 by jaket-cp

Doing a Google search with "What is the maximum number of databases that an sql server 2008r2 can hold", the results of the search should direct you to:Maximum Capacity Specifications for SQL Server - MSDN[^]From the page:Databases per instance of SQL Server 32/64...

SQL-Server-2008R2

How Do I Loop Over Every Row Of A Sql Query Result Set In Sql Server?

11 Feb 2015 by Abhinav S

You can use a cursor - https://msdn.microsoft.com/en-IN/library/ms180169.aspx[^].

SQL-Server-2008R2

Seeking career advice .

14 Feb 2015 by Sergey Alexandrovich Kryukov

1) Here is my advice: don't learn any technological or scientific subjects for the interview, don't wast time on it. Show what you really know and can do; this is not what you can learn just before the interview.2) You need to think the way you potential employer think: why do they want to...

SQL-Server-2008R2

SQL Full Text returns wrong rows ?

4 Mar 2015 by Peter Leow

Among other things, "CONTAINS" returns synonym, note the last item:Quote:CONTAINS can search for:A word or phrase.* The prefix of a word or phrase.* A word near another word.* A word inflectionally generated from another (for example, the word drive is the inflectional stem of drives,...

SQL-Server-2008R2

How to find the maximum number in the table

14 Mar 2015 by OriginalGriff

Use UNPIVOT:SELECT MAX(vx) FROM MyTable AS aUNPIVOT (vx FOR Value IN (Col1, Col2, Col3, Col4)) AS up[edit]Typo: missed "AS a"[/edit]

SQL-Server-2008R2

How to Replace null values with specific value and how to increment the numbers

14 Mar 2015 by Suvendu Shekhar Giri

ISNULL() can help you in this regard.Try thisSELECT A,ISNULL(A,0)+10 AS BFROM YourTableNameExample:SELECT A,ISNULL(A,0)+10 AS BFROM ( SELECT 10 AS A UNION ALL SELECT NULL UNION ALL SELECT 20 UNION ALL SELECT NULL UNION ALL SELECT 40 UNION ALL SELECT 20 UNION...

SQL-Server-2008R2

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

How to merge two queries in sql queries

30 Mar 2015 by Suvendu Shekhar Giri

A simple left/inner join can helpTry this-select t1.USER_NAME as UserName, test1, test2from( select b.UserName as USER_NAME, sum(a.TotalCount)*2 as test1 from [database].[dbo].table1 a ,[database].[dbo].table2 b where a.UserID = b.UserID and a.WorkAreaID = '2' group by b.UserName)...

SQL-Server-2008R2

Move the whole project to another pc

11 Apr 2015 by Sergey Alexandrovich Kryukov

There are no concerns at all, except just one: your code itself. Simply copy all source code from one computer to any directory of another computer. You need to have all the build tools and all dependencies to be installed on that computer, including SQL server and all libraries you use. Now,...

SQL-Server-2008R2

separate multiple values for the same column

21 Apr 2015 by CHill60

First you need a function to split that column - there are loads out there but I like this one from sqlservercentral.com[^]CREATE FUNCTION [dbo].[fnSplitString] ( @string NVARCHAR(MAX), @delimiter CHAR(1) ) RETURNS @output TABLE(splitdata NVARCHAR(MAX) ) BEGIN ...

SQL-Server-2008R2

Can you please give me the links of sample asp.net websites

9 May 2015 by Mehdi Gholam

Google is your friend, search for "asp.net tutorials" and you will find a lot of information.

SQL-Server-2008R2

Problem with Comma Seprated Values in sql

13 May 2015 by Maciej Los

Check this:DECLARE @tmp TABLE (Username VARCHAR(250), WorkArea VARCHAR(50), Scope VARCHAR(50), Quality INT)INSERT INTO @tmp (Username, WorkArea, Scope, Quality)VALUES('aa,bb,cc,ee,ff,gg,hh,ii', 'xyz', 'zyx', '100'),('aa,bb,dd,ii,kk', 'xyz', 'zyx', '99'),('cc,gg,ee,hh,kk', 'xyz',...

SQL-Server-2008R2

how to insert 1-9999 values table in stored procedure

22 Jul 2015 by _Asif_

You don't need to have a loop for getting a serial number 1..9999. This can be done without any loop/iteration at all. See below for a sampel code. CREATE PROCEDURE writeNoASBEGIN SET NOCOUNT ON INSERT INTO [dbslno].[dbo].tblslno(slno) SELECT n FROM ( SELECT TOP (9999) n =...

SQL-Server-2008R2

how to insert 1-9999 values table in stored procedure

22 Jul 2015 by Andy Lanng

I'd prefer something a little more self reliant:CREATE PROCEDURE writeNoASBEGIN WITH mycte AS ( SELECT 1 AS number UNION ALL SELECT number + 1 FROM mycte WHERE number

SQL-Server-2008R2

how to stop reinserting the values in asp.net

20 Aug 2015 by Suvendu Shekhar Giri

This happens as on page refresh, it fires the same button click event and thus causing duplicate inserts. There are many ways to do this.1. Redirect to the same page after insert is done.Response.Redirect(Request.Url.AbsoluteUri);2. Put RequiredFieldValidator and clear all the form fields...

SQL-Server-2008R2

check string in two tables

26 Aug 2015 by CHill60

An alternative would be to union the email addresses e.g. DECLARE @Result int--Dummy datadeclare @tbl1 table (id1 int identity(1,1), email1 varchar(30))declare @tbl2 table (id2 int identity(1,1), email2 varchar(30))insert into @tbl1 values('email1'),('email2'),('email3')insert into...

SQL-Server-2008R2

retrieve values from SQL Server DB in C#

28 Sep 2015 by Wendelius

SQL Server doesn't have a split function out-of-the-box so one easy way is to build your own and use it inside SQL statements. For example have a look at An Easy But Effective Way to Split a String using Transact-SQL[^]If you don't want to use a function there are alternative approaches such...

SQL-Server-2008R2

How do I use "between" in SQL Case ?

29 Sep 2015 by Suvendu Shekhar Giri

You can use a tricky solution as follows-SELECT COUNT (DISTINCT Table.users) FROM Table WHERE (@cat1 OR (Tabl.age between 18 and 20))AND (@cat=1 OR (Tabl.age between 21 and 42))For the first condition,It will check if @cat not equals to 1 then skip the 2nd part of the expression...

SQL-Server-2008R2

How do I use "between" in SQL Case ?

29 Sep 2015 by Maciej Los

If you want to count users based on age category, you have to create helper-table to define age categories. Then you'll be able to join data from users table. Have a look at example:DECLARE @users TABLE(UserId INT IDENTITY(1,1), UName VARCHAR(30), BirthDate DATE)INSERT INTO @users(UName,...

SQL-Server-2008R2

delete some records in a table without using top and where clause.

8 Oct 2015 by OriginalGriff

The problem is that unless you have a WHERE clause or similar SQL will delete all records - so to restrict your input to just "any 1000 records" you would need some kind of filtering.I'm pretty sure that you are trying to do something that would be better handled elsewhere, because deleting...

SQL-Server-2008R2

I need max + 1 value from SQL server

29 Mar 2016 by CHill60

select MAX(reg_num) + 1 from Student_DetailsIf you are using this method to assign id's to the students then don't do it this way. Use an IDENTITY column on your database table

SQL-Server-2008R2

Is it possible to trace the system, who has modified the script(stored procedure) recently in SQL server?

21 Apr 2016 by ZurdoDev

There is a built-in report that shows this. From Sql Management Studio right-click your db, choose Reports, Standard Reports, Schema Changes History. That will show who changed it last.

SQL-Server-2008R2

How to declare cursor after with keyword in SQL

5 May 2016 by CHill60

Declare the cursor before the CTE i.e.DECLARE A CURSOR FORWITH C AS( SELECT MC_id FROM CM_MaterialCategory WHERE MC_id IN (SELECT id FROM @id) UNION ALL SELECT T.MC_id FROM CM_MaterialCategory AS T INNER JOIN C ON T.MC_parent = C.MC_id)SELECT DISTINCT MC_id FROM...

SQL-Server-2008R2

Problem while using UNION in SQL

22 Jun 2016 by Maciej Los

You don't need UNION's! You have to use CASE WHEN ... END.So, the simplest way is:SELECT CONVERT(CHAR(4), dateofentry, 100) + CONVERT(CHAR(4), dateofentry, 120) as 'monthyear', SUM(CASE WHEN accountid=4 THEN amount ELSE 0 END) as 'Amount1', SUM(CASE WHEN accountid=3 THEN amount...

SQL-Server-2008R2

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

How to add daily date for each employee in attendance table ?

6 Jan 2019 by Wendelius

You can use for example recursive CTE to generate desired dates. Consider the following example CREATE TABLE Person ( PersonId int ); CREATE TABLE Attendance ( PersonId int, InTime datetime, OutTime datetime ); INSERT INTO Person (PersonId) VALUES (1), (2), (3); INSERT...

SQL-Server-2008R2

Which is the best way to connect to db(SQL server 2008 r2) from a VB.NET web application?

3 Feb 2019 by Maciej Los

Quote: currently am using SqlConnection. is that fine? Yes, yes, yes! Because SqlConnection Class (System.Data.SqlClient)[^] is designed for SQL server database.

SQL-Server-2008R2

Keep a track of Changes on Tables

19 Jul 2012 by Espen Harlinn

Have a look at SQL Server Audit Action Groups and Actions[^] - particularly DATABASE_OBJECT_CHANGE_GROUP.This will allow you to monitor who is dropping the column.Best regardsEspen Harlinn

SQL-Server-2008R2

Alpha Numeric sort in sql

27 Jul 2012 by Prasad_Kulkarni

How to Sort Alphanumeric Data in SQL[^]Sort AlphaNumeric Data in Sql[^]Alphanumeric sorting in sql[^]

SQL-Server-2008R2

how to copy and rename a .bak file

17 May 2013 by Richard MacCutchan

I already posted you a link to the File.Copy page in your other question. Please don't repost.

SQL-Server-2008R2

Need First 3 letters of the month

2 Dec 2013 by Animesh Datta

Hello ,you have to use SUBSTRING in your query to get first 3 letters of the month.modify the query SELECT SUBSTRING(DATENAME(MONTH, CAST(CAST(YEAR(GETDATE()) AS VARCHAR)+ '-' + CAST('2' AS VARCHAR) + '-01' AS DATETIME)),1,3) AS MonthNameFor more details on substring go through...

SQL-Server-2008R2

Bind Crystal Reports with Dataset or Datatable

5 Aug 2014 by saurav ray

Article determines how to bind a crystal report with a virtual database

SQL-Server-2008R2

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, , >= or when the subquery is used as an expression

3 Apr 2014 by Jörgen Andersson

Andrius Leonavicius writes that you have to check the check the subselect starting with:ISNULL ((SELECT xl.InvoiceForeignUnitPriceWell, it's actually that specific line that is the problem. You need to aggregate it using Min(), Max(), Avg() or whatever is making best sense for your business...

SQL-Server-2008R2

How to get data from two diffenerent tables ?

5 Apr 2014 by Maciej Los

Try this:DECLARE @src TABLE(col1 VARCHAR(30), col2 VARCHAR(30), col3 VARCHAR(30))INSERT INTO @src (col1, col2, col3)VALUES('a', 'b', 'c')DECLARE @dst TABLE(id INT IDENTITY(1,1), col VARCHAR(30))INSERT INTO @dst (col)SELECT colValue AS colFROM ( SELECT colValue,...

SQL-Server-2008R2

How To Get Last Date Of What Ever I Am Passing Any Month And Year In (Yyyy/Mm/Dd ) Format

3 Apr 2014 by King Fisher

try this:DECLARE @Year INT = 2011, @Month INT = 2DECLARE @dtDate DATETIMEset @dtDate= CONVERT(DATE, CAST((@Year*100+@Month) AS VARCHAR(6))+'01') SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))LastDay_AnyMonth

SQL-Server-2008R2

How to get rid of this SQL errot?

4 Apr 2014 by Guruprasad.K.Basavaraju

Please change the line that is underlined below.GOCREATE PROCEDURE SP_ADD_ENQUIRIES(@POL VARCHAR(50),@POD VARCHAR(50),@FPOD VARCHAR(50),@WEIGHT REAL,@SHIPPER VARCHAR(150),@CONSIGNEE VARCHAR(150))ASDECLARE @SUFFIX INT;DECLARE @PREFIX VARCHAR(3);DECLARE @RESULT...

SQL-Server-2008R2

stored-procedure