Click here to Skip to main content

Submit your article

T-SQL

T-SQL

Great Reads

SQLoogle - Part 1 of 2

by dale.newman

Google your SQL.

Reggie: A Non-Backtracking Streaming Regular Expression Code Generator

by honey the codewitch

Embed fast streaming C# code to match text based on inputted regular expressions

LINQ to Entities, Cross Apply, and Left Outer Join

by DaveDavidson

In this article, I show LINQ to Entities syntax that will produce queries with CROSS APPLY and LEFT OUTER JOIN clauses.

Are Using Bitwise Operators for Table Joins Really Taboo?

by Ed Wiebe

Can't we obtain the benefit of using bitwise operators for SQL many-to-many relationships AND maintain referential integrity?

Latest Articles

spSearchTables: Search and Find Tables or Columns by Name or Value

by Federico Di Marco

spSearchTables: a helper T-SQL stored procedure for digging into (large) databases

SQL Server Database Inventory using PowerShell

by Dathuraj Pasarge

Extract SQL Server DB inventory\baseline using TSQL and PowerShell scripts

SQL-File Technology for Transact-SQL

by Sergei Y.Kitáev

Introduction to statically parameterized SQL language

Reggie: A Non-Backtracking Streaming Regular Expression Code Generator

by honey the codewitch

Embed fast streaming C# code to match text based on inputted regular expressions

All Articles

T-SQL

Insert multiple rows to a DB using DAAB without multiple round trips

4 May 2011 by #realJSOP

Would it be possible to simply transfer the table as a binary file using FTP or TCP/IP, and then let the remote machine do all the database work itself?If you want to ensure some kind of recovery system (in the event the transfer is interrupted), you could send smaller files and alt least...

GMT using T-SQL 2005

23 May 2011 by #realJSOP

SELECT SYSDATETIMEOFFSET()Google reveals all...

SQL-Server-2005

Percentage calculated field

17 Aug 2011 by #realJSOP

Try this (it looks like you forgot to scope out part of the math):((Orders.Discount / 100) * (Orders.ProdQty * Products.Cost)) - (Orders.ProdQty * Products.Cost)

Select from multiple tables in sql

12 Sep 2011 by #realJSOP

Did you realize that you have no way to associate a student with a course? Sure, he has a grade, but what course is it for?

How to get SQL runtime errors ?

10 Dec 2016 by #realJSOP

Why don't you have the query that threw the error log it instead of making the app do it?

stored-procedure

Go to the next row depending on a value from a column in C#

6 Feb 2017 by #realJSOP

If I understand your question, you want to skip rows that have an "M" in a certain column.Change your if statement from this:if ((Myrow.Cells[8].Value.ToString()) == "M"){ //What should I do here?}else{...to this:if (Myrow.Cells[8].Value.ToString()) !=...

Pay Attention to What "Script...To" Generates

22 Sep 2017 by #realJSOP

It doesn't always happen the way you expect.

SQL-Server-2008

Is it possible have case statement inside a case statement

7 Jun 2018 by #realJSOP

Yes, you can use nested case statements, but it gets real ugly real fast. Try this: CASE WHEN @variable = 1 THEN (CASE WHEN CASE WHEN tbl_column_id IN(SELECT tbl_column_id FROM Table) THEN 1 ELSE 0 END ...

Sql server first business day of week case help

11 Mar 2019 by #realJSOP

Try this code: declare @firstBusinessDay int = 2; -- 2 = monday declare @isFirstBusDay varchar(10) = case when DATEPART(dw, GetDate()) = @firstBusinessDay then 'yes' else 'no' end; select @isFirstBusDay;

Sql: count bits as rolling sequentially

1 Apr 2019 by #realJSOP

Look at this article: Build a Calendar Without Pre-Existing Tables[^] If you don't feel like using that, simply do this: UPPER(DATENAME(WEEKDAY, yourDateField )) NOT IN ('SUNDAY','MONDAY') to evaluate if it's a weekend day (you could also use day number if that's your bent).

Create excel and export data from sql query in sql server without using wizard.

3 Dec 2013 by $*Developer - Vaibhav*$

Use below script DECLARE @PATH VARCHAR(500)DECLARE @Name Varchar(500)DECLARE @SQL VARCHAR(500)SET @Name = TableData.xls'SET @PATH = '\\C:\'SET @SQL = 'SELECT * FROM TableName'DECLARE @cmd VARCHAR(800)SET @Cmd = 'mkdir ' +@PATHEXEC master..xp_cmdshell @cmdDECLARE...

Check if a date range has a weekend

14 Dec 2014 by /\jmot

See..http://www.codersrevolution.com/blog/SQL-Server-How-Many-WorkWeek-Days-In-Date-Range[^]

Procedure not working ?

23 Jan 2023 by 0x01AA

Please see also all the comments to the question... ... and please take care I'm not very familar with mssql syntax, therefore +- some semicolons can be wrong. Anyway this should do what you need: CREATE PROCEDURE RAISE_SALARY(@P_DEPARTMENT_ID...

T-SQL get results for a particular order and also the sum of multiple orders, grouped by person

14 Sep 2023 by 5th LMNt

I need to return a single row for each customer that purchased a particular product, with the most recent order of that product and the amount of that order, and also the sum of the amounts of all orders with that product for that customer (in a...

Enter formated data in sql like 001,003,087,234

30 Aug 2012 by __TR__

Here is one approach--create table tblAssest (code nvarchar(4))DECLARE @InitialCount INTDECLARE @FinalCount INTSET @InitialCount = 1SET @FinalCount = 9999WHILE @InitialCount

how to achieve the below output in -TSQL 2008?

1 Jan 2013 by __TR__

TrySELECT T.Taskid, A1.Short_name, A2.Short_name AS [Short-Names]FROM TASKACTIVE TINNER JOIN ACTVCODE A1 ON T.Activity_code_id = A1.activitycode_Type_id INNER JOIN ACTVCODE A2 ON T.Activity_code_id = A2.activitycode_Type_id

can we use temproray tables in stored procedure which is having dynamic sql in it.

2 Jan 2013 by __TR__

The scope of the temp table is limited to the dynamic query when its defined within the dynamic query. Alternate options are - you can use Global temporary table[^] or create the temp table outside your dynamic query. Take a look at the below link for more...

How to insert the correct range in sql server?

25 Jun 2013 by _Amy

The overlap can be find using the following query:IF EXISTS (SELECT 1 FROM ODRange WHERE 31 = Minimum) print 'Overlaps'ELSE print 'Does not overlap'--Amit

How to get / return blank a result

4 Jan 2016 by _Asif_

Its pretty simple, all you have to do is eliminate COALESCE function which is converting null into "0"

When execute query it take 32 second to return 2 rows so how to solve slowly issue ?

20 Jan 2022 by _Asif_

You should have a secondary Index on (ZPLID, ZFeatureKey, ZValue,Code,CodeTypeID,RevisionID) which should solve your performance issue

What mean index seek noncluster where select based on mslid?

29 Jan 2022 by _Asif_

Well, By looking your execution plan the problem is not in index rather it is the insert that taking significant time. Inserts usually don't take that much time so you need to check with DBA why inserts are taking that much time. A quick...

stored-procedure

How to replace stuff with string agg on SQL server 2017 ?

29 Jan 2022 by _Asif_

This should work for you SELECT PartID,Code,Co.CodeTypeID,Co.RevisionID,Co.ZPLID,Count(1) as ConCount, ( SELECT STRING_AGG(CP.ColumnName, '$') FROM(SELECT distinct d.ColumnName,C.codeTypeId,C.Code,C.ZfeatureKey from gen C inner join...

When update table small number of rows it take too much time may be 11 minutes although I create indexes why this very slow?

20 Feb 2022 by _Asif_

Updates over large volume tables require a different approach. Usually, we don't understand the underlying complexity and treat both updates either over small or large volumes in a similar way. Let's discuss your design first. * You are...

When divide missing values from two tables it not display ?

20 Feb 2022 by _Asif_

Try this select t.companyid,t.[year],cast(cast(IsNULL(c.cnt,0) as decimal)/ cast(t.cnt as decimal) as decimal(18,2)) as totalpercentage from #tabletotal t left outer join #tableconfirment c on t.companyid=c.companyid and...

Get parts that have multi sourcetype ?

28 Feb 2022 by _Asif_

Try this select T2.* from ( select partid from #temp group by partid having count(distinct sourcetypeid)=2 ) T inner join #temp TM on T.PARTID = TM.partid AND TM.sourcetypeid = 8901 inner join #temp T2 on TM.PARTID = T2.partid

Long process when running stored procedure with suspend status ?

2 Mar 2022 by _Asif_

Well, its easy now, since you have got the query that is creating the problem. You try this query on SQL Command Window with Display Estimated Execution Plan and Include Actual Execution Plan turn on. Once you have the execution plan, you need to...

Help with t-SQL Select for .NET Multi select Dropdownlist

11 Feb 2013 by _Maxxx_

If your UDF gives you back a table..Select xxx from Properties AS P join theUDFTable AS U on P.Area_Id = U.AreaID

store procedure in asp.net

17 Feb 2013 by _Maxxx_

Don't quite understand your talk of loops etc. but an observation:Your code is summarised asif duration

stored-procedure

Find the first Day (01) and the Last Day (28) for a specified month in SQL Function (UDF), as an example (20091207)?

3 Mar 2013 by _Maxxx_

The first day is always 1 so there's 50% of your work done!As for the last day, you could tryCREATE FUNCTION dbo.LastDayInMonth (@when DATETIME)RETURNS IntASBEGIN Declare @lastDate int SELECT @lastdate =...

SQL-Server-2005

T-SQL Question. Trying to manipulate the dateTime column to only retrieve a desired date range

26 Jul 2011 by _Zorro_

Date and Time Functions (Transact-SQL)[^]Hope it helps

How to Avoid cursor...........................

17 Sep 2013 by a.pkumar

-- i hava one temp table (#tempTbl)DECLARE @Sno INTDECLARE @Id INTDECLARE @TotalMinutes AS INTEGER=0DECLARE @OTimeMinutes AS INTEGER=0DECLARE @RUMinutes INTEGER=0DECLARE sumcal CURSOR GLOBAL SCROLL STATIC OPTIMISTIC FOR SELECT Id,Sno FROM #tempTblOPEN sumcalFETCH FIRST FROM...

always rounding up in SQL Server

26 Sep 2012 by Aarti Meswania

see below exampleselect convert(numeric(18,2),2.237)you can use function convertconvert(numeric(18,2),Input value)orconvert(Decimal(18,2),Input value)Happy Coding!:)

IF/ELSE in WHERE statement in ORACLE

5 May 2013 by Aarti Meswania

Try This,SELECT P.*FROM PTABLE PLEFT JOIN MTABLE M ON P.MATERIALID = M.MATERIALIDLEFT JOIN NTABLE R ON P.MATERIALID = N.MATERIALIDWHERE M.MATERIAL = CASE WHEN P.MATERIAL IS NOT NULL THEN N.MATERIAL ELSE P.MATERIAL ENDHappy Coding!:)

Sql select query working abnormally

15 Apr 2014 by Aarti Meswania

this is second date '2014-04-09 15:39:20.000' if you write '2014-04-09 15:39' then it will be take seconds 00.000 by default so...'2014-04-09 15:39' = '2014-04-09 15:39:00.000'so, the records having RecordedDateTime greater than '2014-04-09 15:39:00.000' will not be displayedbecause...

SQL-Server-2012

Case statement Scenario

2 May 2011 by Abhinav S

The inner case will execute first and then the outer ones execute.The best way to write such complex expressions is to put brackets - that always makes code easier to read.When the case is asc then based on the expression @sortexpressions empno or ename is chosed.Some conditions are...

[SQL] DELETE Query with INNER JOIN?

30 Oct 2011 by Abhinav S

Try DELETE FROM table1WHERE NOT EXISTS ( select value from table2 where value = 1 )Warning: I have never run a similar query myself.

how Sql server choose which type of join perform

11 Nov 2012 by Abhinav S

This article will give you an idea that 'it depends' -http://www.sqlserverblogforum.com/2011/10/merge-join-vs-hash-join-vs-nested-loop-join/[^].

SQL-Server-2008

How do I insert 1000 rows of data from table 1 to table 2?

2 Apr 2014 by Abhinav S

Try insert into tableBselect col1,col2,col3,'' from tableAwhere '' represents value for the extra column.

How to alter the function or the table in this situation?

2 Jan 2015 by Abhinav S

Circulation is referring CalculateDelay field, thus you will not be able to alter this field.

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

How do I get two distinct column values ?

29 Jun 2017 by Abhipal Singh

SELECT DISTINCT C_ID, P_ID, P_Date FROM mytable or SELECT C_ID, P_ID, P_Date FROM mytable GROUP BY C_ID, P_ID, P_Date Any of these should work.

Employee Manager Hierarchy

27 Jun 2015 by abhishek6555

declare @empid int; set @empid = 7; with cte_report as ( select empcode,managercode from [Test].[dbo].[Emp_manager] where empcode = @empid union all select e.empcode,e.managercode from [Test].[dbo].[Emp_manager] as e inner join cte_report as m on e.empcode = m.managercode...

SQL-Server-2005

Remove Duplicate Entries

28 Aug 2012 by abhishekdhanotia

check logic of your query first.

How to remove white space in VB.NET

28 Jan 2013 by Adam R Harris

RTRIM[^] And/Or LTRIM[^] in your SQL statement.Alternatively you could use String.Trim[^]

How to insert the correct range in sql server?

26 Jun 2013 by Adarsh chauhan

Hi,Following will solve your purpose..declare @min intdeclare @max intset @min= 35set @max= 40if exists(select * from ODRange where (minimum=@min) or (minimum=@max))print 'Invalid Range'elseinsert into ODRange values(@min,@max)

How do I update a gridview value when a radiobutton in it is changed?

15 Dec 2022 by adriancs

This can be easily achieved by using pure HTML + javascript (AJAX). You may have a look at my demo project on this topic at: GitHub - adriancs2/GridView-Html-Table: A Comparison Doing a Table with GridView and Dynamic HTML Table in ASP.NET...

How a primary key value may be recalculated automatically?

21 Aug 2015 by Advay Pandya

Hi Dzianis,As per my understanding of your question, you want to recalculate/ reset identity value of the column, not the primary key itself.If I am correct then please use below DBCC command. That will help you to reset the identity value of the column.DBCC...

how use distinct in tsql

21 Nov 2014 by Afzaal Ahmad Zeeshan

The above query would return only one row, because the FScoreID is same throughout the table. You can create two queries. One would select the DISTINCT data, and the second one can be used inside a loop, to see for the data present for each of this DISTINCT data. Try this, SELECT...

Mission Impossible: Migrating .NET Core 1.x to 2.0

19 Aug 2017 by Afzaal Ahmad Zeeshan

.NET Core 2.0 brings a lot of improvements to the system, and it brings a lot of pain to the developers as well. I had a lot of problems upgrading .NET Core 1.x apps to .NET Core 2.0; I yet have to feel the promise it makes about performance and so, but let us see how to upgrade our existing applica

entity-framework

Need a specific SQL Select Stament!

16 Oct 2012 by Afzal Shaikh

1. I want to ouput ALL of the CustomerName with their SalesValue and the ProvinceSelect CustomerName,SalesValue,Province into #Tempfrom tbl_Name2. and then, the tricky part for me: view the total sales for each Province the customer has made in each Province.Select...

How to Get distinct record by concatenating rows in comma separated

1 Jul 2014 by Afzal Shaikh

FacultyName Mobile CourseName-Section IsAdjunctFaria 0333*23*13* CS3612 Software Engineering-II-A 0Faria 0333*2**13* CS3612 Software Engineering-II-B 0Faria 0333*2**13* CS3612 Software...

SQL-Server-2008R2

Combining MDX with T-SQL in One Result Set for SSRS (Hybrid Query)

27 Sep 2014 by Ahmad A.A. Ahmad

Combining MDX with T-SQL in One Result Set for SSRS (Hybrid Query)

How to Out Put Value in group by that is not in the result ?

15 May 2015 by Ahmad_kelany

Hello Every One , i have a query that extracts the sales per hour in a particular time period to know in which hour of the day does the business thrive and in which does it struggle the query goes like this : select top(24) Datepart(HOUR,inv.invoiceDate) as Hr ,...

How to use if exists in a UDF that returns a table?

3 Dec 2018 by Ahmad_kelany

Hello everyone I have a query that executes fine: if Exists (select top(1) invd.UnitCost from InvoiceDetails invd join Invoices inv on invd.InvoiceID = inv.InvoiceID where inv.InvoiceTypeID = 3 and invd.ItemID = 212 and invd.ExpireDate ='20190430' and upper(invd.batch) = upper('vatg') and...

Query not return any records although it give me result but in another database

21 Mar 2018 by ahmed_sa

Problem How to trace this query to know the reason of why it return null records . query below work in database and give me results but not give me any result in another database How to debug or trace it to know why it not give me any result . SELECT TrxInvH.Trxtype, TrxInvH.TrxYear,...

How to select rows from table shippolicyh that have related one row only one trxfootersafe ?

29 Jul 2019 by ahmed_sa

Problem How to select rows from table shippolicyh that have related one row only on table trxfootersafe ? meaning to modify query that get all policycode on shippolicyh that have one row only related on table trxfootersafe . the following query not give me result so that what I change . ...

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

I get error the multi-part identifier "rhst.name" could not be bound.?

18 Feb 2021 by ahmed_sa

How to use Exists on this sql statement ? update m set m.rohsstatus=RHst.Name from #ManuFacture m inner JOIN Parts.ROHS Rhh WITH(NOLOCK) ON Rhh.ZPartID=m.PartID inner JOIN Nop_AcceptedValuesOption RHst...

stored-procedure

When search on long text on table have 40 milion rows it is very slow so what I do ?

20 Feb 2021 by ahmed_sa

I work on SQL server 2012 I need to search on table partswithcompany that have 40 million rows . when make select SearchParts, CompanyId from partswithcompany where CompanyId=1234 and SearchParts='A5ghf7598fdmlcpghjk' it is very slow to...

stored-procedure

How to arrange featurename of comptitor first then npx feature name second based on displayorder?

21 Apr 2021 by ahmed_sa

I work on SQL SERVER 2012 I face issue I can't arrange feature on same display order to start by comptitor feature name then nxp no issue on display order 1 and 2 because it is correct issue exist on display order 3 so if i have more than one...

stored-procedure

How to using alternative solution of like ?

12 Sep 2022 by ahmed_sa

I work on SQL server 2012 I face issue I can't display Part Number with Mask related for family without using like because it is more slower running when data is big so can i do result below without using like CREATE TABLE...

SQL-Server-2008R2

Can make query below take less time this when run on SQL server 2012 ?

14 Jun 2021 by ahmed_sa

I run query below it take too much time it reach to 30 minue so I need enhance it to get 5 minute or 10 minute at maximum if less it is good this is my execution plan as below : https://www.brentozar.com/pastetheplan/?id=ryIvvs4od this is my...

SQL-Server-2008

How to make transfer quantity from inventory to another inventory ?

4 Jul 2021 by ahmed_sa

I work on sql server 2012 i face issue i can't handle invenotry tranfer order from inventory to another inventory so How to handle that on business as above when make sales then it store invoice no on inventory as negative on inventory...

SQL-Server-2008R2

How to get feature key and feature value separated $ based on part id ?

27 Oct 2021 by ahmed_sa

I work on sql server 2012 i need to get featurekey and feature value separated $ Based on partid but i don't know how o do that by select sql query ? expected result as below PartId Featurekey FeatureValue...

How to get feature name and feature value separated by $ for table all data?

5 Nov 2021 by ahmed_sa

I work on SQL server 2012 I face issue I can't get Feature Name and Feature Value for Table All Data From table Part Attributes Feature Name and Feature Value exist on table Part Attributes I attached Table structure with post Expected result...

When make drop to table already exist on begin of procedure it not working issue until I do by hand ?

9 Nov 2021 by ahmed_sa

I work on SQL server 2012 i face issue when make drop to table on begin of procedure it not working issue until I do by hand ? so if i alter table Extractreports.dbo.PartGeneration by adding new column as onlineid on other place then execute...

Dynamic procedure pivot not accept multi columns SQL server ?

22 Nov 2021 by ahmed_sa

I work on SQL server 2012 i face issue when using multi column ON COLUMN as companyid,Year exec [dbo].[USP_DYNAMIC_PIVOT] '[CompanyID],[Year]','MetarialID','Metarialperc','#KTempSemlterfinialRows','max' it give me error as Msg 173, Level...

How to add top 1 row as header on SQL server as extra row ?

17 Nov 2021 by ahmed_sa

I work on SQL server 2014 I face issue but I don't know how to solve it I need to add columns header as first row on table I Try as below but i get error Msg 213, Level 16, State 1, Line 1 Column name or number of supplied values does not...

What I do to enhance this statement select into ?

19 Nov 2021 by ahmed_sa

I Work on sql server i have slow transfer data when make select into small number of rows it take too much time my execution plan https://www.brentozar.com/pastetheplan/?id=r1o3p8NOt my query as below : SELECT d.PartID , d.Code , d.CodeTypeID...

How to use group by instead of distinct when make stuff feature name and feature value ?

19 Nov 2021 by ahmed_sa

I work on SQL server 2012 i need to use group by instead of distinct so how to do that please query working without any problem and give me result i need but I need to use group by instead of distinct on last statement executed in exec @SQL ...

Are there are any way to get partc without make join by verification hash?

25 Nov 2021 by ahmed_sa

I work on sql server 2014 I need to get partc as expected result Actualy i need partc from table #partsc that have Verification Hash on table #VervicationCode are there are any way to get part c without join with Verification Hash expected...

Error the index entry of length 1890 bytes for the index 'IDX_estrat' exceeds the maximum length of 1700 bytes for nonclustered indexes

25 Nov 2021 by ahmed_sa

I work on sql server 2014 I have table when create index on column Estrat but it give me error Warning! The maximum key length for a nonclustered index is 1700 bytes. The index 'IDX_EStrat' has maximum length of 5000 bytes. For some combination...

After add two statement stuff query executing take long time as 5 minute?

1 Dec 2021 by ahmed_sa

I work on SQL server 2014 after add two stuff statement to script below it become very slow before add two stuff statement it take 28 second for display 500 thousand now as below script and after add two statement stuff take 5 minutes so how...

How to solve error conversion failed when converting the nvarchar value '24VAC/D

1 Dec 2021 by ahmed_sa

I work on SQL server 2014 I get error when run statement below error say Conversion failed when converting the nvarchar value '24VAC/DC' to data type int. I got error when execut dynamic sql EXEC (@SQL) so how to solve this error please data...

Case when char index is bigger applied for char index > and <> ?

3 Dec 2021 by ahmed_sa

I work on sql server 2014 i face issue when i need to add double quotes for operator bigger than as > only but my issue it applied also on not equal so how to solve issue please as sample >10 then it will be >'10' 9 will be 9 with...

How to use inner join instead of left join on query below in case of data not exist on chemical master ?

29 Dec 2021 by ahmed_sa

I work on sql server 2014 I need to use inner join instead of left join and update Haschemical i case of exist by haschemical or No in case of not exist on statement below i need to use inner join instead of left join because data is very big on...

Execution plan not show missing index so are there are remainig point to enhance ?

3 Jan 2022 by ahmed_sa

I work on sql server 2012 my execution plan as below my query is very slow it not show missing index so please how to enhance query please ? What I have tried: my execution plan Paste The Plan - Brent Ozar Unlimited®[^]

How to write update with best practice ?

4 Jan 2022 by ahmed_sa

I work on SQL server 2014 I need to rewrite update statement with best practice CAN I Write it without subquery ? What I have tried: what i try UPDATE FFFFF SET FamilyGroup = STUFF( ( SELECT DISTINCT '|' + CAST(...

How to reduce high cost index seek and hash match inner join when analysis execution plan issue?

5 Jan 2022 by ahmed_sa

I work on sql server 2014 I have issue on my execution plan below https://www.brentozar.com/pastetheplan/?id=SJCzRrmht index seek is high 57 what this mean and how to solve it and how mean hash match inner join 40 AND HOW TO REDUCE also...

How to write query get long process job or query or stored procedure running on server ?

6 Jan 2022 by ahmed_sa

I work on sql server 2014 How to write query get long process job or query or stored procedure running on server ? as example suppose i run exec sp_joblong how to know this procedure running now and which place it stop and take long time and how...

How to allow multi user work on same table without any issue of interact data ?

8 Jan 2022 by ahmed_sa

I work on sql server 2017 I have issue How to allow mutli user working on same table without using temp table and without interact two user with same data as example user a : exec sp_workingmultiuser 5 user b : exec sp_workingmultiuser 10 ...

How to get first row group by partid and compliance typeid and document type ?

10 Jan 2022 by ahmed_sa

I work on sql server 2014 I face issue I can't get only one row based on partid and compliance type id and document type so if have partid as 3581935 and compliance type id 1 and document type Web Page OR Coc OR Contact then first priority...

When execute query it take 32 second to return 2 rows so how to solve slowly issue ?

23 Jan 2022 by ahmed_sa

I work on sql server 2017 i face issue when run simple query it take 32 second to run two rows so it is very slow according to number of rows returned and size of row not big my execution plan is ...

What mean index seek noncluster where select based on mslid?

29 Jan 2022 by ahmed_sa

I work on sql server i face issue index seek noncluster index so what this message mean execution plan as below https://www.brentozar.com/pastetheplan/?id=Bk3HHR6aY so how to enhance performance from exection plan table name as below What I...

stored-procedure

How to display name for C and X from a table category instead of using self join?

1 Feb 2022 by ahmed_sa

I work on SQL Server 2014 and I need to get categories c and x without using self join, but I don't know how to do that. My data sample: create table #category ( categoryc int, categoryx int ) insert into #category(categoryc,categoryx) ...

When using data compression and file factore and sort on temp ?

8 Feb 2022 by ahmed_sa

i work on sql server 2017 I need to create cluster index on table but after search i found som features i don't know what it mean like DATA_COMPRESSION,FILLFACTOR, ONLINE, SORT_IN_TEMPDB so what thses features mean please and what case we...

When update table small number of rows it take too much time may be 11 minutes although I create indexes why this very slow?

20 Feb 2022 by ahmed_sa

I work on sql server 2019 when update 20 rows or no rows it take 11 minute I mean 20 rows or no rows by different on m.MaximumReflowTemperatureID r.z2valueid between two tables why update is very slow although I update small number of rows or...

When divide missing values from two tables it not display ?

21 Feb 2022 by ahmed_sa

i work on sql server 2014 i face issue year 2020 not display when divide two values from two tables so i divide all data for same company and year so year 2020 not have row on table #tableconfirment so if missing year i will suppose it will be...

How to get rows that have specific sourcetype only when group by givenpartnumber_non and vcompanyid ?

24 Feb 2022 by ahmed_sa

I work on SQL Server 2014. I need to get rows that have source type 484456. When group by two columns, group by GivenPartNumber_Non and vcompanyid. So, I need to make select query display every group of rows by GivenPartNumber_Non and...

How to get rows with specific stockid=1 and website source type ?

27 Feb 2022 by ahmed_sa

I work on sql server 2017 i face issue i can't get partnumber that have at least source type website and at least have stockid equal 1 per partnumber so with another meaning i need to get part numers that have stockid 1 and source type website ...

Get parts that have multi sourcetype ?

28 Feb 2022 by ahmed_sa

I work on sql server 2017 i need to get different part id that have two different source type per same part but it must part have two different source type and it must one source type from two have source type equal 8901 . sample data create...

Error display when dividing arithmetic overflow error converting int to data type numeric ?

1 Mar 2022 by ahmed_sa

i work on sql server 2012 i face error Msg 8115, Level 16, State 8, Line 1 Arithmetic overflow error converting int to data type numeric. The statement has been terminated. so how to solve this issue ? statment generate error What I have...

Error the datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large.

2 Mar 2022 by ahmed_sa

i work on sql server 2019 i face issue when run query to get queries run on server i get error Msg 535, Level 16, State 0, Line 1 The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too...

Long process when running stored procedure with suspend status ?

2 Mar 2022 by ahmed_sa

I work on sql server 2019 i run my stored procedure on sql server . it take may be 5 hours so i try to trace why it take too much time or too long time . so I make this query to trace issue on my procedure SET TRANSACTION ISOLATION LEVEL READ...

Wait type as expackt and status suspend when update data what this mean ?

5 Mar 2022 by ahmed_sa

i work on sql server 2019 when update data on table it take too much time so i try to see that and analysis that SELECT [Spid] = session_Id , ecid , [Database] = DB_NAME(sp.dbid) , [User] = nt_username , [Status] = er.status...

How to wite select query get data from last date to current dates with gap based on partid ?

15 Mar 2022 by ahmed_sa

I work on sql server 2012 i face issue i need to make select statment get Partid from last month until current month based on last date exist per partid and on same time if there are any gaps between dates then file it based on last date so ...

When import and export file using Python with SQL server 2019 I get error ?

11 Apr 2022 by ahmed_sa

I work on sql server 2019 when import and export excel file using python with sql server 2019 i get error as below Msg 39004, Level 16, State 20, Line 0 A 'Python' script error occurred during execution of 'sp_execute_external_script' with...

Which command will using to delete all rows from table truncate or delete ?

27 Apr 2022 by ahmed_sa

I work on sql server 2017 i need to delete all rows from table student_course but i don't know use delete from student_course or truncate table student_course table student_course studentid pk courseid pk table student table ...

I get error syntaxerror: invalid syntax when copying file to another path ?

29 Apr 2022 by ahmed_sa

I work on sql server 2017 i run python script from sql server this script export data from sql server table to excel file but i get error when add shutil.copy(@FixedPath,@ExportPath) (1 row affected) Msg 39004, Level 16, State 20, Line 4...

Header on excel file changed when export data to from SQL server 2017 to excel ?

1 May 2022 by ahmed_sa

I work on sql server 2017 I run script depend on python language . I run script run query on sql server 2017 to export data to Excel file. header of excel file before export data as below StudentId,StudentName after run query export data...