Click here to Skip to main content

Submit your article

SQL Server 2012

SQL-Server-2012

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.

Reset SQL Server SA (System Admin) Password

by Sarathi Balakrishnan

Reset SQL server SA (System Admin) Password when you have lost all possible ways to connect to SQL server and your server has only Windows authentication disabled. You should have Windows administrator permission to do this.

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

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 2012

Error "object reference not set to an instance of an object." C# ASP.NET

18 Jul 2023 by Member 12369816

Hi..I have inserted update panel in my gridview but when i execute my program then this error Object reference not set to an instance of an object.Come.When i put form tag before gridview then it working well but page reload but i want page not reload when i search database in gridview and...

SQL-Server-2012

How to use the utf encoding in c# ?

19 Aug 2021 by Mohammed-cd7

I have application (winform) use a sql localdb, the probleme when I insert arabic word i have in the colomn of the name this character "??????" ,I use this code: var arabic = Encoding.GetEncoding(864); var bytes = arabic.GetBytes(libelléTextEdit.Text); ...

SQL-Server-2012

Find user having continuously three year of attendenance

12 Apr 2021 by Member 15145141

I have encountered this as challenge lately and I solved it using lead windows function just order the result by year and find the lead and the next lead then eliminate null values at last in the where conditions check for sequence rule. I think...

SQL-Server-2012

Find user having continuously three year of attendenance

10 Apr 2021 by Ragul M

I have a event table in which the list of user participated will be stored.Sample Data:╔════╦══════╦══════╗║ id ║ name ║ year ║╠════╬══════╬══════╣║ 1 ║ Arun ║ 2001 ║║ 2 ║ Arun ║ 2002 ║║ 3 ║ Arun ║ 2003 ║║ 4 ║ Arun ║ 2004 ║║ 5 ║ Arun ║ 2009 ║║ 6 ║ Arun ║ 2010...

SQL-Server-2012

How to avoid duplicates in XML while inserting into SQL table

24 Mar 2021 by Prathap Gangireddy

Note: this is a sample code and scenario. I will not be using distinct while using select statement. I'm trying to upload Itemcodes into an empty table for the first time using XML. below is the XML data {"ProductDetails":"\r\n ...

SQL-Server-2012

How to avoid duplicates in XML while inserting into SQL table

24 Mar 2021 by Richard Deeming

Try something like: WITH cte As ( SELECT N.value('(ItemCode/text())[1]', 'varchar(50)') As ItemCode FROM @ProductDetails.nodes('Root/ProductDetails') As T(N) ) INSERT INTO inv_productdetails (ItemCode) SELECT ItemCode...

SQL-Server-2012

How to delete from trades table when part id not exist on mapping table code type from and code value from?

14 Mar 2021 by ahmed_sa

How to delete from trades table when Part id not exist on mapping table code type from and code value from? How to delete from table trades where Part id not exist on mapping table on code type from and code value from I work on sql server...

SQL-Server-2012

How to delete from trades table when part id not exist on mapping table code type from and code value from?

14 Mar 2021 by Maciej Los

I don't know what exaclty you want to delete, because the description of your issue is no clear, but... seems you wnat to join mapping table to trades table. DELETE t FROM trades t INNER JOIN mapping m ON t.CodeTypeId = m.CodeTypeFrom OR...

SQL-Server-2012

How to fetch a persons team count based on manager_id relationship in SQL

11 Mar 2021 by Virendra S from Bangalore, Karnataka

SELECT COUNT(*) FROM [dbo].[tblEmployeeMaster] WHERE MGR_ID=@EMPID table tblEmployeeMaster is having key columns emp_no,emp_name,mgr_id. Here I want fetch count of a person's reportees, child reportees(nth level) that means his total team...

SQL-Server-2008R2

SQL-Server-2012

SQL-Server-2016

How to fetch a persons team count based on manager_id relationship in SQL

11 Mar 2021 by Virendra S from Bangalore, Karnataka

WITH CTEORG AS ( SELECT Emp_No, Emp_Name,MGR_ID,DOL,LEAVING_ACTION_DATE FROM [tblEmployeeMasterLive] WHERE MGR_ID =@EMP_NO AND CONVERT(DATETIME,DOJ,103) =...

SQL-Server-2008R2

SQL-Server-2012

SQL-Server-2016

How to fetch a persons team count based on manager_id relationship in SQL

10 Mar 2021 by CHill60

Would give you a fully worked example but I'm just about to go into a meeting. I did something similar in my article about loops in SQL Server - see Processing Loops in SQL Server[^] the section about "Traversing a Hierarchy". You can then...

SQL-Server-2008R2

SQL-Server-2012

SQL-Server-2016

How to insert a file as a XML in sqldatabase table

27 Jan 2021 by saagarchary

Hi i Have a File i want to store the file in the format of xml , which can access or load from Database when it is required , any One help me with sql server code , it saves me lot of time and codeing for my developemnt project should be like ...

SQL-Server-2012

How to insert a file as a XML in sqldatabase table

27 Jan 2021 by Wendelius

If the content of the file is XML, why not store it in an XML field inside the SQL database. Have a look at XML Data Type and Columns (SQL Server) - SQL Server | Microsoft Docs[^] and Create XML Data Type Variables and Columns - SQL Server |...

SQL-Server-2012

How to insert a file as a XML in sqldatabase table

27 Jan 2021 by OriginalGriff

If you mean you want to store an XML file in a single column of a row in an SQL table, then just pass it via a parameterized query to a NVARCHAR(MAX) column. Retrieve it by SELECTing it as you would any other text. We can't give you any idea of...

SQL-Server-2012

How to insert a file as a XML in sqldatabase table

27 Jan 2021 by Richard MacCutchan

You can save it to disk and store its address in the database. Or, if it is not too large just store it in a varchar field.

SQL-Server-2012

How to to split the comma separated value from a column

7 Dec 2020 by Virendra S from Bangalore, Karnataka

select EmpName,Geo from tblemployees ///result: JOHN 1,2,4,7 select * from tblGeoMaster ///result coulmns-->Geo_id, Geo_Name how to fetch mapped mapped Geo_Names for JOHN. What I have tried: I need the SQL logic to fetch the split the...

SQL-Server-2012

How to to split the comma separated value from a column

7 Dec 2020 by OriginalGriff

Basically don't. You can do it, but it's clumsy: Converting comma separated data in a column to rows for selection[^] A much, much better idea is to use use a separate table to store each value in your comma delimited data in it's own row, with...

SQL-Server-2012

Solvedwhat am I missing …the current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction

23 Nov 2020 by Prathap Gangireddy

**********Tried the syntax from the below link and its working like charm *********** sql server - SQL Transaction Error: The current transaction cannot be committed and cannot support operations that write to the log file - Stack Overflow[^] ...

SQL-Server-2012

Solvedwhat am I missing …the current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction

23 Nov 2020 by CHill60

You should put the Rollback of the transaction before attempting to insert anything into the log table - if you think about it, that error table update will be rolled back as well as the original insert. Try this instead (untested)Begin Catch ...

SQL-Server-2012

Msg 102, level 15, state 1, procedure auditview, line 1 incorrect syntax near 'as'

17 Nov 2020 by suniti dinesh

When i try to run the below procedure from my application i am getting stored procedure error asMsg 102, Level 15, State 1, Procedure AuditView, Line 1 Incorrect syntax near 'AS' Could you please help me. USE [AdminDB] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE...

SQL-Server-2012

Why it didnt insert any value in the database?

17 Nov 2020 by syuhaiba hamizi

i add a new column(user_securitycode) in the table(userlogistic) and it shows NULL. i wanna replace the value by using the "UPDATE" command. but, it just replace NULL value and leave empty value in it. so, what is the exact codes or commands i...

SQL-Server-2012

Why it didnt insert any value in the database?

17 Nov 2020 by Henry Z.

As OriginalGriff has said it is bad practice to concatenate strings to build sql statements, however his solution does not offer a solution at all but rather a recomendation of best practices at best and in probably should have been better...

SQL-Server-2012

When make dynamic query converting name field to numbers give error ?

16 Nov 2020 by ahmed_sa

I work on SQL server 2012 I Face issue as below : Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the nvarchar value '1.2kV' to data type int. this error done where converting Name to number but it is failed . as Example...

SQL-Server-2012

stored-procedure

When make dynamic query converting name field to numbers give error ?

16 Nov 2020 by Richard MacCutchan

The string "1.2kV" is not a valid number. You need to parse the "kV" out and just paas the "1.2" to the convert call.

SQL-Server-2012

stored-procedure

Why it didnt insert any value in the database?

16 Nov 2020 by OriginalGriff

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

SQL-Server-2012

When update data on temparts table mass value changed after float point ?

8 Nov 2020 by ahmed_sa

I work on SQL Server 2012. When using Stuff to collect data separated by comma, I get strange results. Mass number separated by comma in table #tmpParts Not exactly what exist in the original table #TempPC. To summarize my issue mass for part...

SQL-Server-2012

stored-procedure

When update data on temparts table mass value changed after float point ?

7 Nov 2020 by Daniel Pfeffer

In table #TempPc, you define Mass as a float. A float typically has a range of about 10-38 to 1038, and a precision of about 6 digits. This means that a value such as 580.28613 will be rounded to 508.286. If you use the double type, your value...

SQL-Server-2012

stored-procedure

HOW to GET a RETURN from a STORED PROCEDURE, using C++, THAT HAS NO PARAMETERS, ONLY an OUTPUT

28 Oct 2020 by inlandchris1

I have finally found it reading the docs: After you do a fetch, now, get the data from SQLGetData. Works with the output param or input_output param but SP has only an output. AlarmCount has the data (long). retcode =...

SQL-Server-2012

stored-procedure

HOW to GET a RETURN from a STORED PROCEDURE, using C++, THAT HAS NO PARAMETERS, ONLY an OUTPUT

28 Oct 2020 by inlandchris1

Here is the basic code taken from Microsoft docs. I had the biggest trouble connecting but found out, as you can see, the length of the DSN, username, and password has to be spot on. I will later put the size in a variable to clean it up. I...

SQL-Server-2012

stored-procedure

'Cdc control task' cannot run on the installed version of the integration services.

29 Sep 2020 by Richard Deeming

This is a problem with the Microsoft documentation - the feature requires Enterprise edition, but this is not mentioned in the documentation. Even if you don't believe the error message for some reason, other sources will confirm that CDC only...

SQL-Server-2012

'Cdc control task' cannot run on the installed version of the integration services.

29 Sep 2020 by SandeepKumar.K

Hi, I have deployed SSIS package on SQL SERVER 2019 Standard Edition(Version : 15.0.200) and package is using CDC control Flow task. Now, when I execute the package it's getting below error. Quote: 'cdc control task' cannot run on the installed...

SQL-Server-2012

Sql comma separated values

9 Sep 2020 by Vishal0903

declare @temp Varchar(100) = '122,122,456,1333,55555,XYZ,ABC,XY1' How do I sort the Values in SQL? (Values in ascending order, and NOT order in split is done) How do I remove the duplicate values in SQL? need 2 separate queries for each use...

SQL-Server-2012

Sql comma separated values

8 Sep 2020 by CHill60

In response to OP comment here's a link from a question I answered yesterday Best split function - SQL Server Forums[^]

SQL-Server-2012

Sql comma separated values

8 Sep 2020 by Richard Deeming

STRING_SPLIT (Transact-SQL) - SQL Server | Microsoft Docs[^] Available in SQL Server 2016 and later. -- Sort the values: SELECT value FROM STRING_SPLIT(@temp, ',') ORDER BY value; -- Remove duplicates: SELECT DISTINCT value FROM...

SQL-Server-2012

How can multiple users query a SQL table using a parameter passed into a textbox on a VB.NET form without overwritting each others results

24 Aug 2020 by sazmuelz

I have a SQL table, I want several users to be able to query the table based on a parameter passed via a textbox on a form in visual basic and the results displayed in a Crystal Report. The way I have been doing it is to create another table...

SQL-Server-2012

How can multiple users query a SQL table using a parameter passed into a textbox on a VB.NET form without overwritting each others results

24 Aug 2020 by OriginalGriff

First off, 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...

SQL-Server-2012

Protocol error in TDS stream in SQL

17 Aug 2020 by Dave Kreskowiak

There's about a couple of hundred things that can be wrong. You're not going to get "the fix" for this without you doing a bunch of troubleshooting of everything between your machine and the SQL Server, including the stuff on your machine and the...

SQL-Server-2012

Sometime identity column big jump in SQL server 2012

17 Jul 2020 by hareshdgr8

Sometimes my identity columns big jumps my sql server table is create table Invoice_Details ( Invoice_No int not null identity(1,1), Inv_date datetime, Customer_Name varchar(100), Primary Key(Invoice_No) ); In My Table my Data is ...

SQL-Server-2012

Sometime identity column big jump in SQL server 2012

17 Jul 2020 by OriginalGriff

I'd check your code - all of it that does INSERT or UPDATE operations, and that includes SP's as well as "external" software. It is possible to change the "current index" for IDENTITY values, and it's most likely that it's a fault in external...

SQL-Server-2012

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

29 Jun 2020 by Richard MacCutchan

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

SQL-Server-2008R2

SQL-Server-2012

SQL-Server-2014

SQL-Server-2016

How to add column column unit dynamically when flag allow is 1?

19 Jun 2020 by CHill60

Your question is not at all clear but there are a couple of things I can point out that might help you. If you are going to use temporary tables in dynamic SQL then you need to create them first. The table created by SELECT * Into #NewTable2...

SQL-Server-2012

stored-procedure

How to add column column unit dynamically when flag allow is 1?

19 Jun 2020 by ahmed_sa

How to add column column unit dynamically when Flag Allow is 1 on table #nonparametric table ? I work on SQL server 2012 I can't add column unit dynamically when Flag Allow=1 as example below I have two rows have Flag Allow=1 family 90AM will...

SQL-Server-2012

stored-procedure

The client has asked us to create a testing SQL view from the CUSTOMER and PRODUCT tables that will capture 2 records from each possibility.

11 Jun 2020 by Anuj Kumar (kanhaiya dhakarey)

The client has a promotion that is highly customized to the customer group and state fields in the customer table. The client has asked us to create a testing SQL view from the CUSTOMER and PRODUCT tables that will capture 2 records from each...

SQL-Server-2012

The client has asked us to create a testing SQL view from the CUSTOMER and PRODUCT tables that will capture 2 records from each possibility.

11 Jun 2020 by OriginalGriff

We are more than willing to help those that are stuck: but that doesn't mean that we are here to do it all for you! We can't do all the work, you are either getting paid for this, or it's part of your grades and it wouldn't be at all fair for us...

SQL-Server-2012

How to split single SQL row into multiple rows on the basis of begdate and enddate?

7 Jun 2020 by Maciej Los

You can use CTE: SET DATEFORMAT dmy; DECLARE @dateranges TABLE(ID INT, BegDate DATE, EndDate DATE) INSERT INTO @dateranges(ID, BegDate, EndDate) VALUES(1, '01-01-2020', '01-07-2021'), (2, '01-01-2020', '01-02-2021'), (3,...

SQL-Server-2012

How to split single SQL row into multiple rows on the basis of begdate and enddate?

7 Jun 2020 by Member 14112539

Table t1 ID BegDate EndDate 1 01-01-2020 01-07-2021 2 01-01-2020 01-02-2021 3 01-01-2020 01-12-2021 I need to split table t1 records into month wise in SQL Server 12 Below is the example for ID=1 ID OLD_ID BegDate EndDate...

SQL-Server-2012

How to calculate the duration of an OverTime according to the InOutMode using sql ?

1 Jun 2020 by Leila Toumi

I'm trying to calculate the duration of the overtime for each employee. I'm using the Log Table: Log_Attendance:EnrollNumber DateAttendance TimesInOut ModeEvent 1 12-07-2015 12-07-2015 14:00:00 41 ...

SQL-Server-2008R2

SQL-Server-2012

Sub-query which returns several lines

29 May 2020 by #realJSOP

Dude, learn how to format your code so we can read it. Beyond that, here is your code formatted. Note the comments: SELECT Q.DTNUMEXE ,Q.DTMOIEXE ,Q.DTANNQIT ,Q.DTMOIQIT ,Q.DTCODBRC ,Q.DTSERQIT ...

SQL-Server-2012

Sub-query which returns several lines

29 May 2020 by houssem eddine ayari

SQL0811 : Résultat de SELECT dépasse une ligne. Cause . . . . . : La table résultante d'une instruction SELECT INTO, d'une sous-requête ou d'une sous-requête d'une instruction SET comporte plusieurs lignes. Il s'agit d'une erreur de type 2. Une...

SQL-Server-2012

Highest rank fro same customer

19 May 2020 by Maciej Los

Try this: DECLARE @customer TABLE(Customer VARCHAR(50), account int, [rank] int) INSERT INTO @customer (Customer, account, [rank]) VALUES('a',1,0), ('b',2,1), ('a',3,3), ('a',4,2), ('c',5,0) UPDATE t1 SET [rank] = t2.MaxRank FROM @Customer t1...

SQL-Server-2008R2

SQL-Server-2012

Highest rank fro same customer

19 May 2020 by Hemil Gandhi

I have around 10,000 customer data containing customer number, account number & account rank (1,2,3,4). several customers have multiple account numbers. i want to update highest rank for unique customer number. i.e account rank must be same for...

SQL-Server-2008R2

SQL-Server-2012

Sql select distinct based on datetime range

18 May 2020 by Maciej Los

Take a look at: LEAD (Transact-SQL) - SQL Server | Microsoft Docs[^] LAG (Transact-SQL) - SQL Server | Microsoft Docs[^] Usage: DECLARE @T_Finger_Log TABLE(Timer_finger VARCHAR(30), Insert_Date DATETIME) INSERT INTO @T_Finger_Log...

SQL-Server-2012

Sql select distinct based on datetime range

18 May 2020 by Member 14760154

I have a data in my table like this 001800081 2020-05-19 11:59:04.720 001800081 2020-05-19 12:01:51.997 001800081 2020-05-19 22:03:58.627 001800081 2020-05-19 22:04:03.190 I want to sql query result like this 001800081 2020-05-19 12:01:51.997...

SQL-Server-2012

How to retrieve DLL file version of remote system using C# and/or SQL server

16 May 2020 by Garth J Lancaster

Well, you haven't provided any code for the C#/WMI attempt, so it's hard to help you - there are two links that said WMI/Powershell should be possible, these might give you ideas, but without your code/actual errors rather than the vague "Quote:...

SQL-Server-2012

How to retrieve DLL file version of remote system using C# and/or SQL server

16 May 2020 by santanuchat

Hi I need to retrieve version of DLL files under a specific folder of different remote system connected in vpn using C# and/or SQL Server. Please help/guide me how it can be done? What I have tried: I have tried using WMI in C# & sp_OACreate in...

SQL-Server-2012

C# service to gather SQL data

4 May 2020 by Member 11955245

Need some guidance here - Requirement- I have 1000 SQL servers and I have a scheduled task running on all of those which updates a table with just an integer, let's call it X (its a sum of some numbers). Now the ask is to create some sort of...

SQL-Server-2012

C# service to gather SQL data

4 May 2020 by RickZeeland

Maybe you can use ApexSQL Propagate instead of writing a C# application: Multi-database SQL script execution | ApexSQL[^] Or take a look at this CodeProject article which uses SignalR: SQL Server Notifications on Record Change with SignalR and...

SQL-Server-2012

C# service to gather SQL data

4 May 2020 by Dave Kreskowiak

Well, if you can't use 3rd party solutions, you're going to have to write a service that runs on all of the SQL Servers. This service would get the data it needs from the server it's running on and send it to a central SQL database. Your website,...

SQL-Server-2012

Adding a condition using EXISTS with a second INNER JOIN

30 Apr 2020 by Eek Ten Bears

ah ok I understand what you are after now. Wood for the trees problem. Don't need to do much to make your code work at all. I still don't much like using EXISTS but it might be appropriate here and will work if you remove this: INNER JOIN...

SQL-Server-2012

Adding a condition using EXISTS with a second INNER JOIN

30 Apr 2020 by Member 13779417

The following stored procedure works fine but I need to add another condition in relation to another table called tblItems. I require tblAccounts.accountID to join with tblItems.AccountID so I can check if tblItems.fileID > 0 for every accountID...

SQL-Server-2012

Adding a condition using EXISTS with a second INNER JOIN

29 Apr 2020 by Eek Ten Bears

This is a comment and suggestion rather than a solution A WHERE EXISTS is not the best way for you here, it is overly complex for what you need. As a result it will be detrimental to the performance of the query. Also I note you include the...

SQL-Server-2012

Split a single row into multiple row based on column value

24 Apr 2020 by Richard Deeming

Here's a solution with a single recursive CTE[^]: WITH cte As ( SELECT Item, CASE WHEN QuantityRequired > MaxQuantity THEN MaxQuantity ELSE QuantityRequired END As QuantityRequired, ...

SQL-Server-2012

Split a single row into multiple row based on column value

24 Apr 2020 by Member 14636607

I have following quantity table Item QuantityRequired MaxQuantity Item1 200 50 Item2 100 30 Based on MaxQuantity column value, table rows has to get split like below, Item QuantityRequired...

SQL-Server-2012

Split a single row into multiple row based on column value

24 Apr 2020 by CHill60

I chose to use a recursive CTE (Common Table Expression) to do this, combined with another CTE ;with cte1 as ( select item, quantityrequired, maxquantity, cast(QuantityRequired / maxquantity as integer) as wholes, QuantityRequired %...

SQL-Server-2012

Execute SQL script from SQL 2019 to SQL 2012

22 Apr 2020 by ZurdoDev

That option was added in SQL 2019 so if you want to run that script against Sql 2012 you will need to remove the option.

SQL-Server-2012

Execute SQL script from SQL 2019 to SQL 2012

22 Apr 2020 by Member 13058758

when I execute SQL script from SQL Server 2019 version in SQL Server 2012 it make this error OPTIMIZE_FOR_SEQUENTIAL_KEY is not a recognized CREATE TABLE option and Cannot find the object "dbo.AspNetUserRoles" because it does not exist...

SQL-Server-2012

Get all tags for each post

17 Apr 2020 by MadMyche

To add in the question; you will need to add in your Question table to the FROM / INNER JOIN list "source" list. To get All of the questions and tags, you will need to remove the WHERE clause from the query. Without knowing the table...

SQL-Server-2012

Get all tags for each post

17 Apr 2020 by manosabari

I have the following query to get the all questions from the database and the separate query to get tags for a specific question. FYI, I have 3 tables. Question Tag QuestionTagMap Select QuestionId, Title, UrlSlug From dbo.Question ...

SQL-Server-2012

Cross apply query on the same table SQL

9 Apr 2020 by NEGIN_K

SELECT M.* FROM ( SELECT AutheriseID, UID, Name, AccountNumber, AccountName, Match, Frequency FROM authorise WHERE Frequency=1 ) M INNER JOIN authorise A ON M.UID=A.UID AND M.NAME=A.NAME AND A.AutheriseIDM.AutheriseID AND A.UID...

SQL-Server-2012

Cross apply query on the same table SQL

9 Apr 2020 by CPuser2020

Hi, I have a table that I need to self query on. Not too sure if it need to be done with a join or cross aply etc. The table is : Authorise(AutheriseID(PK), UID, Name, AccountNumber, AccountName, Match, Frequency) I need to search the table...

SQL-Server-2012

Cross apply query on the same table SQL

8 Apr 2020 by CPuser2020

select * from authorise where UID IN ( select top 1 UID from authorise where NOT EXISTS (SELECT * FROM trained WHERE authorise.name = trained.name AND authorise.accountnumber = trained.accountnumber) and frequency = 1 order by UID) AND...

SQL-Server-2012

Side by side output of SQL query (join three outputs)

6 Apr 2020 by Hemil Gandhi

I have below mentioned three queries, I want to combine their ouputs side by side. I have no idea how to do that please help...!! SELECT BrCode,Code,sum((ClearBal+AccumuInt+clgamt)) AS BALANCE,sum(OverdueAmt) FROM LON20200331 where AcSts9 AND...

SQL-Server-2012

SQL-Server-2008R2

Side by side output of SQL query (join three outputs)

6 Apr 2020 by Maciej Los

Try this: SELECT BrCode, Code, Balance1 = SUM(CASE WHEN [limit]=1 THEN BALANCE ELSE 0 END), Overdue1 = SUM(CASE WHEN [limit]=1 THEN Overdue ELSE 0 END), Balance2 = SUM(CASE WHEN [limit]=2 THEN BALANCE ELSE 0 END), Overdue2 = SUM(CASE...

SQL-Server-2012

SQL-Server-2008R2

Side by side output of SQL query (join three outputs)

6 Apr 2020 by MadMyche

You don't need to PIVOT, you need to JOIN[^] the tables; and I would also recommend that you use CTEs[^] to make it a little cleaner. The CTE will basically encapsulate one of your queries into a one time use alias. I would do this for each of...

SQL-Server-2012

SQL-Server-2008R2

Side by side output of SQL query (join three outputs)

6 Apr 2020 by OriginalGriff

Just use a JOIN to select rows with matching Brcode and Code values from the three SELECT operations: SQL Joins[^] Something like: SELECT a.Brcode, a.Code, a.Balance, a.Overdue, b.Balace, b.Overdue, c.Balace, c.Overdue FROM (SELECT ...) a JOIN...

SQL-Server-2012

SQL-Server-2008R2

How to compare one table column names with other table rows value.

25 Mar 2020 by CHill60

The technique you need is UNPIVOT[^] If you first cross join your tables select * from table2 cross join table1 You will get col col-1 col-2 col-3 col-1 1 2 3 col-2 1 2 3 col-3 1 2 3 As you can see - the values you want are across the top, but...

SQL-Server-2012

How to compare one table column names with other table rows value.

25 Mar 2020 by TCS54321

i need to compare table-1 columns name with table-2 row values. for example:- Table-1 have 3 columns like :- col-1 | col-2 | col-3 and Table-2 have one column with 3 row:- col ---- col-1 col-2 col-3 now i need to compare table-1 column...

SQL-Server-2012

How to install SQL server profiler in SQL server 2012

15 Mar 2020 by OriginalGriff

Google is your friend: Be nice and visit him often. He can answer questions a lot more quickly than posting them here... A very quick search using your subject as the search term gave over 800,000 hits: install SQL server profiler in SQL server...

SQL-Server-2012

How to install SQL server profiler in SQL server 2012

15 Mar 2020 by Gauravg9598

In my Sql server 2012, Sql Server Profiler is not installed. How i will install in my sql server 2012 What I have tried: I have search but not got any answer about that

SQL-Server-2012

There are fewer columns in the insert statement?

9 Mar 2020 by gcogco10

I want to insert data to my table, but i am getting this error below " There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns...

SQL-Server-2012

There are fewer columns in the insert statement?

9 Mar 2020 by OriginalGriff

The error means exactly what is says: when you write an INSERT statement, the number of columns specified (4 in your case) must exactly match the number of values supplied (12 in your case). If they don't, then the system doesn't know what to do...

SQL-Server-2012

There are fewer columns in the insert statement?

9 Mar 2020 by phil.o

Please follow the syntax for inserting multiple rows: INSERT (Transact-SQL) - Inserting multiple rows of data - SQL Server | Microsoft Docs[^]

SQL-Server-2012

Enhance performance of select query

8 Mar 2020 by RickZeeland

You can use the "Execution plan" feature to test if different options make a difference: SQL Server Query Execution Plans in SQL Server Management Studio[^] Also see: free-tools-for-sql-server[^] Primary keys are really a must, if there is no...

SQL-Server-2012

Enhance performance of select query

8 Mar 2020 by tirumalabalaji

I have a SQL database table in which records keep growing every sec when I perform a select statement on the table it takes ~1.5 minutes to retrieve the data with around 380000 records in the table. There are no indexes on the table, I know this...

SQL-Server-2012

Compare date with by month & year in SQL server

4 Mar 2020 by hareshdgr8

select * from Performance_tb where format(Perf_Date,'MM/yyyy')='01/2020' giving Perfect result but when I used this select * from Performance_tb where format(Perf_Date,'MM/yyyy')

SQL-Server-2012

Compare date with by month & year in SQL server

4 Mar 2020 by CHill60

In response to OP posting some actual data... If that is your data then solution 3 is correct. There are only 20 rows in that data that are before January 2020. If you want to include January then use

SQL-Server-2012

Compare date with by month & year in SQL server

4 Mar 2020 by Maciej Los

If you want to reject the time part, you can use: SELECT CAST(Perf_Date AS DATE) JustDateWithoutTimePart If you want to reject the day part, i would suggest to replace it with predefined value (for eaxmple first day of month) to be able to...

SQL-Server-2012

Compare date with by month & year in SQL server

4 Mar 2020 by hareshdgr8

Using this get the perfect result select * from Performance_tb where convert(VARCHAR(7),Perf_Date,111)

SQL-Server-2012

Compare date with by month & year in SQL server

3 Mar 2020 by Patrice T

Quote: SHowing wrong data. You only forgot to tell what is wrong in result and what is expected. Generally speaking, when you want to compare dates as strings, the format to use is "yyyymmdd" or "yyyy/mm/dd". If I understand what you want, you...

SQL-Server-2012

Compare date with by month & year in SQL server

3 Mar 2020 by Richard Deeming

Don't format the date column; don't convert the date column; and don't try to call a function on the date column. None of these will be SARGable[^]. Instead, test for a range of dates. DECLARE @Month date = '20200101'; -- Make sure we've got...

SQL-Server-2012

Compare date with by month & year in SQL server

3 Mar 2020 by phil.o

You could trySELECT * FROM Performance_tb WHERE YEAR(Perf_Date)

SQL-Server-2012

How to write expression in ssis to move dynamic file from main folder to archive folder on FTP server..any help would be really appreciate. ?

28 Feb 2020 by Member 14751855

I have a package that run's on weekly basis.. I need to move file from main folder to archive folder on FTP server. so how to create expression for below files name:- ABC.FSR_Satrtdate20200131_date20200221 ABC.FSR_Satrtdate20200124_date20200214...

SQL-Server-2012

Sql join return only one row from left table

27 Feb 2020 by Stefan_Lang

You've got what you asked for: the Select fields contain fields from the EmployeeManager and from the Employee tables. Of course you will have multiple entries per Employee! If you don't actually need the EMG fields, drop that join and the...

SQL-Server-2012

Sql join return only one row from left table

27 Feb 2020 by Christopher Fernandes

I have the following tables Employee, EmployeeManager, EmployeeLeavesAssigned, EmployeeLeavesTaken, EmployeeLeaveCashed Employee 1 to Many EmployeeManager (One employee can have multiple managers) Employee 1 to Many EmployeeLeavesAssigned (one...

SQL-Server-2012

Sql join return only one row from left table

27 Feb 2020 by OriginalGriff

I'm pretty sure it's your DB design that tripping you up - it doesn't sound right at all. In particular: Employee 1 to Many EmployeeManager (One employee can have multiple managers) Seems wrong: an employee will normally have one manager, a...

SQL-Server-2012

Sql join return only one row from left table

27 Feb 2020 by Jörgen Andersson

Well the problem is obviously that there might be more than one manager per employee. So either you have to decide for a main manager for every employee which will need some changes to the database. Or you have denormalize the query and show all...

SQL-Server-2012

Why we are using entity framework even though native SQL sever is more faster than ORM why we are using this?

23 Feb 2020 by grghimal

On building any any solutions, not everyone have same priority as their product to run faster, some might want to finish the product faster so if they want to do it in faster way by spending less time and resources ORM provides the automated...

entity-framework

SQL-Server-2012

Why we are using entity framework even though native SQL sever is more faster than ORM why we are using this?

23 Feb 2020 by KGBRS

why we are using Entity Framework even though native SQL Sever is more faster than ORM why we are using this? What I have tried: SQL Storied procedures or compiled queries are more faster the our server side queries (ORM) but why we are...

entity-framework

SQL-Server-2012

Problem for some time insert data two time with some mini sec. Difference in VB.NET

19 Feb 2020 by OriginalGriff

Before you try to fix that, you need to change your whole app: 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...

SQL-Server-2012

Problem for some time insert data two time with some mini sec. Difference in VB.NET

19 Feb 2020 by hareshdgr8

Hi, I have an issue that sometimes my data inserting two times in database with difference just mini second like 2020-02-18 17:05:15.533 & 2020-02-18 17:05:40.440 with same client data but my auto-generated no. is different can you please...

SQL-Server-2012