I am getting stuck with a query getting the sum of counts. I have a situation where I have to get every department and sum up all employee tasks: Currently, my output is as follows: Department Total Tasks Num Completed Num OVERDUE Asset...

T-SQL

SQL

SQL-Server

How do I get the sum of a count

27 Sep 2023 by SiphoB

WITH intermediate(department, total, completed, overdue) AS (SELECT ... ) -- query SELECT department, SUM(total), SUM(completed), SUM(overdue) FROM intermediate GROUP BY department

T-SQL

SQL

SQL-Server

How do I get the sum of a count

27 Sep 2023 by Richard Deeming

Start by removing those with (nolock) hints: Bad habits : Putting NOLOCK everywhere[^] You've got some redundant comparisons in your query - for example, "completed" requests check that: date_closed

T-SQL

SQL

SQL-Server

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

T-SQL

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

16 Aug 2023 by OriginalGriff

Start here: SQL GROUP BY Statement[^] and the Aggregate Functions[^] that accompany them.

T-SQL

How do I return values on the required column in the final table as below

2 Aug 2023 by yande mwale

Current data: | Old_code | New_code | | -------- | -------- | | HBP112 | HBP0501 | | HBP112 | NULL | | HBP132 | HBP0501 | | HBP132 | NULL | | HCIH112 | NULL | | HCIH112 |HCIH0501 | Required output: ...

T-SQL

SQL

SSMS

How do I return values on the required column in the final table as below

2 Aug 2023 by Richard Deeming

You're nearly there: SELECT Old_Code, New_Code, IsNull(MAX(New_Code) OVER (PARTITION BY Old_Code), Old_Code) As Required_Code FROM t ;

T-SQL

SQL

SSMS

Why return null responsetext by api in t-SQL?

24 Jul 2023 by Servet çayır 2022

I created a stored procedure in sql server to get json data with API. This sp runs successfully manually at once. But I created sql agent job which constantly calls this sp. This job always works, it works successfully for about 3 hours, but then...

T-SQL

SQL

Why return null responsetext by api in t-SQL?

24 Jul 2023 by Member 16056812

Declare @Object as Int; Declare @ResponseText as Varchar(800); Exec sp_OACreate 'MSXML2.ServerXMLHTTP', @Object OUT; Exec sp_OAMethod @Object, 'open', NULL, 'get', '@url', 'false' Exec sp_OAMethod @Object,...

T-SQL

SQL

How to call stored procedure on other server

20 Jun 2023 by BoySetsFire

T-SQL

SQL

SQL Server Database Inventory using PowerShell

8 May 2023 by Dathuraj Pasarge

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

T-SQL

SQL

PowerShell

TSQL query : passing parameter to sp for in clause

28 Apr 2023 by Ravi Sargam

I have a sp where i need to pass a parameter which is using IN Clause below query is working fine select name from tbl where Id='001' and locationId IN (1,2,3)) but this query is giving error Declare @var NVARCHAR(100) = '1,2,3'; Declare @whereClause NVARCHAR(100); SET @whereClause =...

T-SQL

SQL-Server

stored-procedure

TSQL query : passing parameter to sp for in clause

28 Apr 2023 by Pavan Muppalla

This is awesome answer. SELECT value FROM STRING_SPLIT(@LocationIDs, ',') I have been searching a lot and this was the best solution I got. Thanks a lot

T-SQL

SQL-Server

stored-procedure

Get youngest child of each parent

22 Feb 2023 by Member 13442306

select * from ( select a.Name, min(b.Age) age from People a INNER JOIN People b ON a.PersonID = b.Father group by a.Name union select a.Name, min(b.Age) from People a INNER JOIN People b ON a.PersonID = b.Mother group by a.Name; )t...

T-SQL

SQL

How to use only one select statement to get printserver with vbranchdesc instead of using two select statement ?

18 Feb 2023 by ahmed_sa

I working on SQL server 2012 I face issue i can't display Print Server with vBranchDesc by using only one select statement instead of two select statement . i convert values separated comma to rows Id on first select statement . then on second...

T-SQL

SQL

stored-procedure

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

SQL-Server

Procedure not working ?

23 Jan 2023 by jenkins123

I have an SQL database and I need to make a procedure that takes the department id from the user and updates the salary of the employees where the salary is less then average . ERROR: An aggregate may not appear in the WHERE clause unless it is...

T-SQL

SQL-Server

SQL-File Technology for Transact-SQL

19 Dec 2022 by Sergei Y.Kitáev

Introduction to statically parameterized SQL language

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

T-SQL

ASP.NET

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

15 Dec 2022 by David Megnin 2022

Thank you for your help with this. I have a GridView displaying data from the "Attendees" SQL table. Column 5 is a TemplateField with a RadioButtonList, RbCompleted, (Yes/No/Pending). Column 9 is a TemplateField with a TextBox, TbHoursCompleted. ...

T-SQL

ASP.NET

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

13 Dec 2022 by tardezyx

I do not know ASP.NET but C# and your code looks really messy. Therefore, I give you some hints: 1) You should use an own (static or singleton) class to generally communicate with your database wherein you only need 3-4 general methods: Read,...

T-SQL

ASP.NET

Websecurity login not logged success althogth I have same user name and password on membership table ?

6 Nov 2022 by ahmed_sa

I working on c# web application i use the following code to login but it not logged success it show model state error although i have same user name and password on database so how to solve this issue please so when make break point to...

SQL

T-SQL

Websecurity login not logged success althogth I have same user name and password on membership table ?

6 Nov 2022 by Richard Deeming

Look at your code again - you have nothing inside the if block. Regardless of whether the Login method succeeds or fails, you always unconditionally return a "login error" response. Change your code to return the expected response if the login...

SQL

T-SQL

How to add percentage of similar compare two string ?

1 Nov 2022 by ahmed_sa

I work on sql server 2019 i have issue i can't get percentage of two string from table so i will compare between two string columns (PartText,MaskText) and i will display similar percentage of two column so as sample...

T-SQL

SQL-Server

How to add percentage of similar compare two string ?

1 Nov 2022 by OriginalGriff

You will have to write your own function: DIFFERENCE returns an integer between 0 and 4 inclusive, which means the only values you can get from your code are 0%, 25%, 50%, 75%, and 100%. SQL Server DIFFERENCE() Function[^]

T-SQL

SQL-Server

What I modify on code to make two value temperature equal

24 Oct 2022 by ahmed_sa

I work on sql server 2019 i compare value between two table it display as 125℃ 125°C it display two values different although it seem it is equal what query can i write it to make both values equal select...

T-SQL

SQL

What I modify on code to make two value temperature equal

24 Oct 2022 by OriginalGriff

Basically, if you want to compare numbers, store them as numbers in a numeric field: have a INT column which holds a Centigrade temperature and convert the input from whatever the user (or other source) provides to a simple integer. It's more...

T-SQL

SQL

Error access is denied when try read excel from shared path ?

12 Oct 2022 by ahmed_sa

I work with SQL Server 2019 on server I face issue when I try to read an Excel file from shared path using python 3.10. SQL Server exists on server 7.7 and files exist on another server on Active Directory domain 7.9. When I execute reading to...

T-SQL

Python3.6

SQL

Error access is denied when try read excel from shared path ?

12 Oct 2022 by Dave Kreskowiak

What account is running the Python scripts? Are these Jobs running on the SQL Server? On the 192.168.7.9 machine, is there a network share setup holding the files you're trying to read? What's the name of that share? In your code, it seems to be...

T-SQL

Python3.6

SQL

How to display original part and recomended part with each other but must recommended part have bigger display order?

7 Oct 2022 by ahmed_sa

I work on sql server 2019 i face issue on slow running when I have 100000 part on table PartsHaveBestDisplayOrder and all these parts have same package and same coderulesid and Different display order so it take too much time so are there are...

T-SQL

SQL

stored-procedure

When cases I can use SQL graph on it by SQL server 2019 ?

25 Sep 2022 by ahmed_sa

I working on sql server 2019 . i read more about sql graph but really i don't know what befit from using sql graph what cases that can i use sql graph on it and what issues that sql graph solve it so can you help me please What I have tried:...

T-SQL

SQL

When cases I can use SQL graph on it by SQL server 2019 ?

25 Sep 2022 by OriginalGriff

See here: An introduction to a SQL Server 2017 graph database[^] and then here: SQL Graph Architecture - SQL Server | Microsoft Learn[^]

T-SQL

SQL

How to export data more than 20 milions to only one file ?

21 Sep 2022 by ahmed_sa

I work on sql server 2019 i have table have 20 milion rows . i need to export data Exist on table to only one file by using sql server 2019 . so what best tool and faster tool can help me to extract 20 milions rows from sql server to csv file...

T-SQL

SQL

SQL-Server

How to export data more than 20 milions to only one file ?

21 Sep 2022 by Richard MacCutchan

All your "How to" questions can be answered by sql export - Google Search[^].

T-SQL

SQL

SQL-Server

Why return null responsetext by api in t-SQL?

16 Sep 2022 by Servet çayır 2022

I hope I found the reason. I added "exec sp_OADestroy @Object" after the last step. It's been running for 20 hours, no problem.

T-SQL

SQL

Why return null responsetext by api in t-SQL?

15 Sep 2022 by Servet çayır 2022

I followed the running of the Windows service for 2 days. I see it's okay, I'm constantly getting the data.

T-SQL

SQL

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

T-SQL

SQL-Server-2008R2

SQL-Server

How to using alternative solution of like ?

12 Sep 2022 by Member 7870345

Instead of using: SELECT * FROM getfinaldatapc g JOIN getfinaldatapcmask gm ON g.familyid=gm.familyid AND (g.PartNumber=gm.MaskNumber OR (g.PartNumber LIKE gm.MaskNumber+'%' AND LEN(g.PartNumber)=LEN(gm.MaskNumber))) try with SELECT...

T-SQL

SQL-Server-2008R2

SQL-Server

How to reduce high cost of sort on execution plan ?

10 Sep 2022 by ahmed_sa

I work on sql server 2019 i face issue i can't reduce high cost of sort it reach to 86 percent so How to reduce it please additionally it take too much time to execute it . it take 6:06 minutes execution plan as below : Paste The Plan -...

T-SQL

SQL

stored-procedure

How to apply pivot based on feature name and value ?

25 Aug 2022 by CHill60

Please do your own research. https://docs.microsoft.com/en-us/answers/questions/105151/sql-pivot-table-multiple-columns.html[^]

T-SQL

SQL

How to apply pivot based on feature name and value ?

23 Aug 2022 by yosiasz

this OP has been doing this for years!!!

T-SQL

SQL

How to apply pivot based on feature name and value ?

23 Aug 2022 by yosiasz

OP has not tried anything at all other than provide DDL and DML. Seems to want others to do their work by posting same question across multiple forums for over 2 years. Pivot data horizontally based on feature name and feature value -...

T-SQL

SQL

How get number of occurrences in varbinary datatype?

8 Aug 2022 by Aravindba

I have a table with varbinary datatype (file stream enabled) column which store pdf files as binary , form that i can get result as which of file have 'invoice' word using following query. SELECT * FROM FileStore WHERE...

T-SQL

SQL

How get number of occurrences in varbinary datatype?

8 Aug 2022 by OriginalGriff

That's not easy - there are ways which work to find if a string exists in a VARBINARY field: https://stackoverflow.com/questions/19709357/how-can-i-search-for-a-sequence-of-bytes-in-sql-server-varbinarymax-field[^] but they are subject to...

T-SQL

SQL

Issue checking directory path by Python return false although path directory exist?

5 Aug 2022 by ahmed_sa

I working on script running on sql server 2019 using python 3.10 . I have directory path \\192.168.7.9\Import\8 and can write and read to files and delete and create files on this directory path \\192.168.7.9\Import\8. my issue when run...

T-SQL

Python3.6

SQL

How to modify Python script to append data on file using SQL server 2019?

1 Aug 2022 by ahmed_sa

I need to append data to excel file from table students on SQL Server if Excel file already exist. So I need to append data when dbo.fn_FileExists(@FullFilePath)=1 using an Else statement to append new data to the Excel file from Table students....

T-SQL

Python3.6

SQL

How to give unque number to every different group of numbers ?

24 Jul 2022 by ahmed_sa

I work on sql server 2019 i face issue i need to give unique number to every group of numbers without using string aggreagte or stuff original table as below : create table #parts ( PartNumber varchar(50), PartValue int, ...

T-SQL

SQL

stored-procedure

How to give unque number to every different group of numbers ?

24 Jul 2022 by Maciej Los

Try this: SELECT T.PartNumber, T.PartValue,T.CNT, DENSE_RANK() OVER(ORDER BY T.CNT) AS UniqgNo FROM ( SELECT *, COUNT(*) OVER(PARTITION BY PartNumber) AS CNT FROM PP ) AS T SQL Server 2019 | dbfiddle[^] Note: Replace [*] with column...

T-SQL

SQL

stored-procedure

How to merge count item with value item separated stick SQL server ?

21 Jul 2022 by ahmed_sa

I work on sql server 2019 i can't get count and values separated stick by using string aggregate function order not important when arrange count and values sticks separated . my issue is can't merge count per value with msl value it formate as...

T-SQL

SQL

stored-procedure

How to merge count item with value item separated stick SQL server ?

21 Jul 2022 by Maciej Los

You need to concat count of parts with description. See: CREATE TABLE DUMMY ( CID INT, SID VARCHAR(1), CNT INT ); INSERT INTO DUMMY(CID, SID, CNT) VALUES(1, 'A', 2), (1, 'B', 1), (1, 'C', 3); SELECT CID, STRING_AGG(CONCAT(SID, '(',...

T-SQL

SQL

stored-procedure

I get wrong technology id if data exist on table #partsfeature?

20 Jul 2022 by ahmed_sa

I work on sql server 2017 I have table #partsfeature already exist as below create table #partsfeature ( PartId int, FeatureName varchar(300), FeatureValue varchar(300), TechnologyId int ) insert into...

T-SQL

SQL

I get wrong technology id if data exist on table #partsfeature?

20 Jul 2022 by Maciej Los

All what you need to do is to join data on FeatureName column. See: WITH CTE AS ( SELECT 3900 AS PartId, 'grail' AS FeatureName, '51V' AS FeatureValue UNION ALL SELECT 5442,'compress','30v' UNION ALL SELECT 7791,'AC','59V' UNION ALL ...

T-SQL

SQL

How to solve stuff error executed SQL server 2019 ?

14 Jul 2022 by ahmed_sa

I work on sql server 2019 i have issue errors when execute statment below stuff but i don't know how to solve issue error i get and i need to solve it Msg 1033, Level 15, State 1, Line 6 The ORDER BY clause is invalid in views, inline...

T-SQL

SQL

stored-procedure

How to solve stuff error executed SQL server 2019 ?

14 Jul 2022 by CHill60

Use the Reply link when responding to comments so that the poster is notified. Error 1: You cannot have ORDER BY in your sub-queries - the language does not allow it except in the specific circumstances listed in the error message. Options a)...

T-SQL

SQL

stored-procedure

How to display chemical id that have difference between chemical mass and chemical mass modified is more than 0.01 ?

28 Jun 2022 by ahmed_sa

I work on sql server 2019 i face issue can't solve it i need to display chemicalid that have different on chemicalmass and chemicalmassmodified but if there are difference 0.01 then not display it as different i will consider it equal so if...

T-SQL

SQL

stored-procedure

How to display chemical id that have difference between chemical mass and chemical mass modified is more than 0.01 ?

28 Jun 2022 by CHill60

Substract one from the other and compare the absolute difference to 0.01 See SQL Server ABS() Function[^] You may get anomalies from using float - see SQL: Newbie Mistake #1: Using float instead of decimal - The Bit Bucket[^]

T-SQL

SQL

stored-procedure

How to read data from excel file by Python on SQL server 2019 ?

26 Jun 2022 by Richard MacCutchan

Look at the error message: File "D:\ProgramData\MSSQLSERVER\Temp-PY\Appcontainer1\DE2D6F1E-639B-40F3-BCFE-4CD46AACA4AD\sqlindb_0.py", line 31 f = open(\\192.168.7.9\\Import\6\strtinsertinput (4)-953aee07-ca14-4213-a91e-ab0b0f7f3db2.xlsx)...

T-SQL

Python3.6

SQL

How to read data from excel file by Python on SQL server 2019 ?

26 Jun 2022 by ahmed_sa

I work on sql server 2019 I can't read excel file by python 3.10 but i can read excel file by using open rowset to read excel file by using open rowset i do as below : select * from OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0...

T-SQL

Python3.6

SQL

Select top 1 take too much time reach to 3 minutes ?

20 Jun 2022 by CHill60

You are defining the relationship between the tables with [MessageRule] [varchar](800) NULL and [GeneratedGroupRule] [nvarchar](500) NULL. What do you think will happen if there are any rows in the tables where those values are NULL? You will...

T-SQL

SQL

Select top 1 take too much time reach to 3 minutes ?

19 Jun 2022 by ahmed_sa

I work on sql server 2019 i face issue when select top 1 it take too much time although it one record but it take too much time may be 3 minutes this table extractreports.[dbo].[FinalTechnologyGeneration] have 32000 rows 32 thousand rows and...

T-SQL

SQL

How to make select dense rank over max of technologyid based on featurestring for new inserted data?

13 Jun 2022 by ahmed_sa

I work on sql server 2017 i have table have dense rank over FeatureString and column store rank increment is technology id as below create table #partsfeature ( PartId int, FeatureName varchar(300), FeatureValue varchar(300), ...

T-SQL

SQL

SQL-Server

How to make select dense rank over max of technologyid based on featurestring for new inserted data?

13 Jun 2022 by CHill60

Your Window clause is using the wrong column to get your desired results - you should be using PartID not FeatureString. You should also use a WHERE clause to ignore the TechnologyIds that are already set. Try something like thisUPDATE a set...

T-SQL

SQL

SQL-Server

Why ie not converted to decimal ?

5 Jun 2022 by OriginalGriff

This is the same question you asked earlier: Issue of IE data on float numbers data type what save data have fractions ?[^] and the reply has not changed, so let me try to spell it out for you. 1) Numbers are not strings. 2) You can't compare a...

T-SQL

SQL

Issue of IE data on float numbers data type what save data have fractions ?

4 Jun 2022 by ahmed_sa

I work on sql server 2017 i face issue when save data on float column data type it saved as IE-07 AND ie-05 so what this and how to handle it please how to save data on float numbers as it is if there are any way to save data on correct way...

T-SQL

SQL

Issue of IE data on float numbers data type what save data have fractions ?

4 Jun 2022 by OriginalGriff

Basically, don't rely on SQL to format your data: if you look at how floating point numbers are stored: Floating-point arithmetic - Wikipedia[^] you will see why you get the results you do. The SELECT operations return a floating point value not...

T-SQL

SQL

How to get partvalue of #temp where same chemicalid and same partid exist on two tables ?

28 May 2022 by ahmed_sa

I work on sql server 2017 i face issue i need to get all parts related to chemicalid exist on two table #temp and #chemical on case of part id related to same chemical on two table and same part exist on two table then i will get partvalue of...

T-SQL

SQL

How to get partvalue of #temp where same chemicalid and same partid exist on two tables ?

28 May 2022 by ahmed_sa

this solve my issue SELECT ch.partid,ch.chemicalid,Coalesce(t2.partvalue,ch.partvalue) partvalue FROM #chemicals ch inner join #temp t on t.chemicalid=ch.chemicalid left join #temp t2 on t2.partid=ch.partid thank you for help and support

T-SQL

SQL

Are using checksum for hashing numbers with sticks will make issue ?

22 May 2022 by ahmed_sa

i work on sql server 2017 i need to use check sum for hashing but im afraid if i got stuck or issue when compare my data all will be numbers only or numbers with stick example of my data 121 121|145 155|233|2231| so are check sum hashing with...

T-SQL

SQL

How to handle sticks on column country ?

22 May 2022 by ahmed_sa

I work on sql server 2017 i have table data as below i need to make design for data to be best practise and prevent repeating data my issue here on column countries have data separated sticks so how to handle that my key is company and year...

T-SQL

SQL

SQL-Server

How to handle sticks on column country ?

22 May 2022 by CHill60

Create one table to hold the countries, each country should have a unique id Create one table to old what ever this data is meant to be - without the countries column Create a third table that links them together containing just the ids - each...

T-SQL

SQL

SQL-Server

When run SQL query get long queries running I get error ?

18 May 2022 by ahmed_sa

I work on sql server 2019 i run queries to get current long queries run but i get error so how to solve error below Msg 535, Level 16, State 0, Line 52 The datediff function resulted in an overflow. The number of dateparts separating two...

T-SQL

SQL

stored-procedure

When run SQL query get long queries running I get error ?

18 May 2022 by OriginalGriff

Read the error message: it's pretty explicit. You are using DATEDIFF with "ms" - or millisecond - difference. So for each second that two timestamps are different, the value will add 1000. Since there are 60 seconds in a minute, 60 minutes in an...

T-SQL

SQL

stored-procedure

What length of hash byte column that can allowed to hash multi columns ?

17 May 2022 by Richard Deeming

The length of a hash does not depend on the length of the input to that hash. For SHA256, the output will always be 32 bytes. For SHA512, the output will always be 64 bytes. You should declare your column as varbinary, not nvarchar. Storing the...

T-SQL

SQL

stored-procedure

What length of hash byte column that can allowed to hash multi columns ?

17 May 2022 by ahmed_sa

I work on sql server 2019 i have table have multi column string concatenation as StrSubstance,strmass ,strcasnumber i need to create filed hashchemical with nvarchar(700) and this will store hash for 3 columns concatenation with each other are...

T-SQL

SQL

stored-procedure

Slow execution WHEN update chemical id on table chemicalhash so how to enhance it ?

16 May 2022 by ahmed_sa

I work on sql server 2017 i need update chemical id on table chemicalhash based on 3 columns compare to table fmd chemical compare 3 columns strSubstances,strMass,strCASNumber from table chemicalhash with 3 columns...

T-SQL

SQL

stored-procedure

Slow execution WHEN update chemical id on table chemicalhash so how to enhance it ?

16 May 2022 by CHill60

This is incredibly similar to your later question at Why execution plan high cost on clustered index scan and how to mimize cost of clustered index scan ?[^] The solution I have posted there should apply to this as well. Instead of posting...

T-SQL

SQL

stored-procedure

Why execution plan high cost on clustered index scan and how to mimize cost of clustered index scan ?

16 May 2022 by ahmed_sa

I work on sql server 2019 i have clustered index scan 98 percent how to minimize it please i have execution plean have high cost on clustered index scan as https://www.brentozar.com/pastetheplan/?id=HkpoQtlwq table i have issue on it What I...

T-SQL

SQL

stored-procedure

Why execution plan high cost on clustered index scan and how to mimize cost of clustered index scan ?

16 May 2022 by CHill60

This is your querymerge #tmpParts h using Parts.FMDMaster f on h.strsubstances=f.StrSubstance and h.strmass=f.strmass and h.strcasnumber=f.strcasnumber AND h.strHomogeneousMaterialName=f.strHomogeneousMaterialName AND...

T-SQL

SQL

stored-procedure

How to validate user input date formate as yyyy-MM-dd for created date ?

10 May 2022 by ahmed_sa

I work on sql server 2017 i need to validate user input of temp table if formate of created date not YYYY-MM-DD then reject by update status field of temp table to be Notvalid and if formate of created date is YYYY-MM-DD then update status by...

T-SQL

SQL

stored-procedure

How to validate user input date formate as yyyy-MM-dd for created date ?

10 May 2022 by OriginalGriff

Dave is right: Always store data in appropriate datatypes - and that means that only string based data gets stored in VARCHAR or NVARCHAR columns. If you don't do that the you get two problems: 1) Sort order doesn't work, which means that...

T-SQL

SQL

stored-procedure

How to validate user input date formate as yyyy-MM-dd for created date ?

10 May 2022 by Dave Kreskowiak

Your problem isn't necessarily the validation. It's the fact that you're storing dates as a string in the database. That's NEVER a good idea.

T-SQL

SQL

stored-procedure

Which will best for performance separate update or do all update on one step ?

9 May 2022 by ahmed_sa

I work on sql server 2017 i need to ask are separate update will be best or do all update on one step ? so using only one update to update all columns or using only one update for every column update ? What I have tried: UPDATE TT ...

T-SQL

SQL

SQL-Server

Which will best for performance separate update or do all update on one step ?

9 May 2022 by Graeme_Grant

Here is an article on checking how long quieries take: Usage details of the SET STATISTICS TIME ON statement in SQL Server[^] Run both sets of queries and compare the results.

T-SQL

SQL

SQL-Server

How to extract data from hashbyte text after hash or encrypted on SQL server 2017 ?

9 May 2022 by ahmed_sa

how to extract data from hashbyte text after hash or encrypted on sql server 2017 ? I work on sql server 2017 i have field nvarchar(max) store values hashbytes suppose i have text as username:sayed password:321 and i hash it by using hashbyte ...

T-SQL

SQL

How to extract data from hashbyte text after hash or encrypted on SQL server 2017 ?

9 May 2022 by CHill60

I suspect what you are trying to do is compare a password entered by a user with a password stored on the database. That is the wrong way around. Hash the entered value and compare the hashed values See Password Storage: How to do it.[^]

T-SQL

SQL

How to extract data from hashbyte text after hash or encrypted on SQL server 2017 ?

9 May 2022 by OriginalGriff

You can't - that's the whole idea. A Hash code (like MD5 or SHA) is not encryption: it can't be reversed: Decrypting MD5 and SHA: Why You Can't Do It[^] - this applies to all hashing algorithms.

T-SQL

SQL

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

T-SQL

Python3.6

SQL

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

1 May 2022 by Richard MacCutchan

According to your code, the second column of your database is Name, not StudentName. Maybe you should read your own code more closely. INSERT [dbo].[students] ([StudentId], [Name]) VALUES (1, N'ahmed')

T-SQL

Python3.6

SQL

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

T-SQL

Python3.6

SQL

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

29 Apr 2022 by OriginalGriff

If you reduce your query to this: DECLARE @ExportPath NVARCHAR(MAX)='DEST' DECLARE @FixedPath NVARCHAR(MAX)='SOURCE' DECLARE @PythonScript NVARCHAR(MAX) = N'' SET @PythonScript = N'shutil.copy(@FixedPath,@ExportPath)' print @PythonScript You will...

T-SQL

Python3.6

SQL

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

T-SQL

SQL

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

27 Apr 2022 by M Imran Ansari

You can use either DELETE or TRUNCATE command to server the purpose. DELETE FROM student_course OR TRUNCATE TABLE student_course DELETE is use when we want to apply WHERE clause. TRUNCATE is faster and we cannot undu deleted records but in delete...

T-SQL

SQL

Auto Increment ID / Incorrect syntax near the word [table name]

25 Apr 2022 by Lotus90

Hi I have database table where the id should increment automatically.I set the "identity specification to yes and increment it by 1 "also I put the "SET IDENTITY_INSERT MyTable ON " but I still have the error."Incorrect syntax near the word [table name]"How I Can insert to the...

T-SQL

SQL-Server

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

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

11 Apr 2022 by Richard MacCutchan

You need the pip-api · PyPI[^] to use function calls.

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

T-SQL

SQL

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

15 Mar 2022 by OriginalGriff

I would create a a Calendar table with a single Primary key column, containing all the date between the earliest date you expect (2000-01-01 perhaps) and some point in the future (2099-12-31 maybe) - that's less than 40,000 rows, and simple to...

T-SQL

SQL

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

T-SQL

SQL

transaction

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

T-SQL

SQL

performance