Questions tagged [t-sql]

Transact-SQL (T-SQL) is a dialect of SQL used by Microsoft SQL Server and SAP's Sybase.

Filter by
Sorted by
Tagged with
4 votes
1 answer
82 views

SQL: finding unique multi-column value triplet matching multiple rows?

TL;DR: using Transact-SQL/SQL Server, how can I find the rows of a table which match a single "solution" triplet of column values (ColA, ColB, ColC) for some set of values in another column? ...
user avatar
0 votes
1 answer
34 views

SQL Server 2019 how can I create an alert for when service broker messages are aggregating in the queue and not being processed?

How do I create an alert in SQL Agent, to notify me once service broker message queue exceeds 300 messages? I am trying to get a notification to an operator in SQL Agent when this occurs.
user avatar
  • 25
0 votes
1 answer
50 views

How to map the newly created records in parent table with the child table?

have a table Employees Id Name EmployeeType_ID 1 John 1 2. Mathw 2 EmployeeTypes Id TypeName 1 Perm 2. Temp 3. Regular Now I am copying data from one server to ...
user avatar
0 votes
0 answers
21 views

How to convert correctly from datetime2 data-type to small date and to elliminate HH:mm:SS.000000 padding

I have created a table with a column with a date data type which correspond to a date data in yyy-MM-dd format. In the database it presents it as if the data type is datetime2(7). More generally all ...
user avatar
-3 votes
0 answers
32 views

Why case..when get a table scan ? how to workarround?

When I use CASE .. WHEN .. END I get an index scan less efficient than the index seek. I have complex business rules I need to use the CASE, is there any workaround ? Query A: select * from [dbo]....
user avatar
  • 1
-3 votes
1 answer
46 views

Retrieving range of values from a lookup table using SQL

I am using SQL Server. I have two tables Result and Grading. Using T-SQL select statement, from the lookup table (Grading),I want to fill Grade column in Result table with the knowledge of range of ...
user avatar
2 votes
3 answers
64 views

How do I restore multiple transaction log backups after restoring a full backup SQLSERVER 2019

I am creating my DR strategy, and my full back-up recovers fine. The full backups are taken with a modified version of the Ola Hallengren scripts. The file timestamp of the full backup shows 07/26/...
user avatar
  • 25
5 votes
2 answers
123 views

T-SQL Update with Max and Min value from another table

I don't have much experience with SQL queries and I'm trying to learn and understand from my mistakes. I have two tables: CREATE TABLE #TempTest (Id INT, Number INT) CREATE TABLE #TempTest2 (Id INT, ...
user avatar
  • 237
2 votes
2 answers
70 views

"NOT Column LIKE pattern" vs "Column NOT LIKE pattern"

Is there a logical or performance difference between these two seemingly equivalent expressions? WHERE NOT [Column] LIKE '%pattern%' WHERE [Column] NOT LIKE '%pattern%' ...or do both result in the ...
user avatar
  • 149
-2 votes
2 answers
41 views

How to give same number if it exist on table or new number if it not exist based on part value?

I work on sql server 2019 i have table #parts have group of numbers on Part Value Field . my issue how to give unique number to every group of numbers on Part Value Field if group numbers on part ...
user avatar
-3 votes
3 answers
49 views

INSERT INTO.. SELECT with default keyword [closed]

Could not find anything related to this issue I am having, google only seems to return how to use the default keyword with INSERT INTO.. VALUES. I have a table with the following columns: ...
user avatar
1 vote
1 answer
60 views

HASHBYTES() result of another HASHBYTES(), OR CHECKSUM_AGG() aggregate for HASHBYTES()?

I want to aggregate a single HASHBYTES() from the results of another HASHBYTES()... something like CHECKSUM_AGG() does for CHECKSUM()... I have been able find how to make a single HASH per row of ...
user avatar
0 votes
0 answers
38 views

Grant permissions only if an object exists, but without mentioning the object twice

We have a number of database build scripts that grant permissions to specific objects (sprocs, functions, etc) only to specific roles. We have recently moved to an automated deployment model that has ...
user avatar
  • 101
3 votes
1 answer
325 views

Calculate median of values in T-SQL

I've got a piece of code (stored procedure) written in T-SQL that inserts into a table several data from a temporal table as shown here: INSERT INTO [myschema].[CalculatedData]( Year, Month, ...
user avatar
2 votes
3 answers
93 views

SQL Server Select orders which have all of a set of items

I have the following tables, Order and OrderLine: Order: id | total ---------- 1 | 55.09 2 | 62.42 OrderLine: order_id | line_number | item | qty ---------------------------------------...
user avatar
3 votes
2 answers
240 views

Nest a SQL file into another in SQL Server

I have a simple SQL file I'm using to perform some test: DECLARE @VAR1 AS VARCHAR(100) = '12345' DECLARE @VAR2 AS INTEGER = 54321 WAITFOR DELAY '00:00:02' INSERT Orders SELECT @VAR1 AS COL1, @...
user avatar
  • 463
2 votes
1 answer
108 views

SQL Server PATINDEX issue/bug when using different case-sensitive collations

I have a function (which I found here years ago) that uses STUFF / PATINDEX to strip out non-alphanumeric characters from a string. When running on a Case Insensitive collation, it works fine. ...
user avatar
2 votes
1 answer
106 views

tSQLt test problem for some DDL queries

I am a SQL Server DBA. To automate some of my daily tasks, I need to write stored procedures. Creating functionality without testing does not make sense to me. For creating and running my tests, I ...
user avatar
0 votes
1 answer
75 views

JSON - How do I get JSON Child Array into one column - OPENJSON in Synapse

I've broken this down with executable code. I need to get the lang and value columns into a single record. Any ideas on how to go about this: DECLARE @JSONMain NVARCHAR(1000) SET @JSONMain = N'{ &...
user avatar
  • 173
0 votes
1 answer
61 views

When Insert Press Name Tech Id Take Wrong Number Where Press Name Exist?

I work on SQL server 2017 I have table #Pressfeature as below create table #Pressfeature ( PartId int, PressName varchar(300), TechId int ) insert into #Pressfeature(PartId,PressName,...
user avatar
0 votes
0 answers
74 views

Linked Servers - MS SQL Server Migration from 2012 to 2017

I'm preparing migration for whole server SQL Server instance 2012 to 2017 (new project, don't know the old environment in details), but have question here only about the Linked Server. This is the ...
user avatar
-3 votes
2 answers
44 views

How to get numbers that have IE in case of data type float?

I work on sql server 2017 i face issue when save data on float data type it display as IE so how to get data only that display as IE on float data type CREATE TABLE #TestTable ( ...
user avatar
-1 votes
3 answers
121 views

How to select Max date that is valid?

I have a date as varchar (dont ask why). I need to select Max(date). However, date can be faulty, like 2034-34-34. I would like to select max date that is valid and not greater than today. It's t-sql. ...
user avatar
  • 133
3 votes
1 answer
77 views

How to get call stack info in SQL Server's catch block?

When I have multiple errors in my query, and I use try catch and error_message() function in my catch block, I only get the last error message. For example, if my errors are: Msg 2714, Level 16, State ...
user avatar
  • 179
-3 votes
1 answer
95 views

How to convert data separated pipe to rows?

I work on sql server 2017 I have table name #countriesData data as below my issue here How to convert countries column data separated pipe to rows . meaning every rev and company and year will have ...
user avatar
1 vote
1 answer
72 views

Resource consuming query

I have found below CPU consuming query with a query cost of 1540. I have tried creating appropriate indexes which reduced the query cost to 1234 but still, the query is expensive. Index Created : ...
user avatar
  • 11
0 votes
3 answers
84 views

Using Boolean Fields in a View to Identify Linked Tables

I am using SQL Server 2019. I am using SQL Server Management Studio on the client side. Suppose I have the following data model: -- A record of the overall test of a widget CREATE TABLE [TestRecords] ...
user avatar
  • 157
-2 votes
1 answer
227 views

Row_number to not increment if values on three columns are the same from the row before

Suppose I have a dataset that looks like the screenshot below and I need to add a count column for the rows based off the orderid, DocumentNo_, and CurrPackageTrackingNo with the following logic: ...
user avatar
2 votes
3 answers
188 views

Generate JSON with property names that include dot using SELECT FOR JSON

I need to generate the folowing JSON payload (shortened) from a table in SQL Server. Please note the dot in the property name. This is a special syntax called OData. { "Id" : "A1"...
user avatar
  • 23
0 votes
0 answers
36 views

Exporting multiple TSQL result sets into one spreadsheet using Powershell

I have to load a lot of CSV files into a DB (3 at a time, resulting in one table per CSV, named as the file name and headers are). I then run about 12 different stored procedures using those table ...
user avatar
0 votes
1 answer
32 views

Setting up reporting and alerting in SQL Server through TSQL and Powershell

I am trying to automate monitoring on SQL Server through PowerShell and TSQL, to get alerts through mail whenever some issue is there. I have gone through many articles over the internet and tried the ...
user avatar
0 votes
1 answer
70 views

SQL query to group results on matching record ID

I am having trouble designing a query. I have a list of PersonIds for records in a Persons table. I would like to have a single query that takes this list of PersonIds and returns other records in ...
user avatar
1 vote
0 answers
101 views

Any benefit creating temp tables with random prefixes in SQL Server to control latch waits on tempdb database

Recently I came across reading in one of the blogs that if a temp table name is prefixed with different/random names (in different procedures) that could benefit in reducing the concurrency issues / ...
user avatar
-1 votes
2 answers
248 views

Must declare the scalar variable "@Name"

I need your help please since I am stuck here and not sure how to fix this. Trying to run this code below and getting this message (Must declare the scalar variable "@Name"). How can I pass ...
user avatar
3 votes
1 answer
564 views

Is there a way to Delete inserted data, or vice versa?

I'm trying to get Data inserted and then Deleted in the same query. I'm trying to delete it in chunks to not cause LOG issues: DECLARE @BatchSize INT SET @BatchSize = 100000 WHILE @BatchSize <> ...
user avatar
  • 7,156
2 votes
1 answer
57 views

ownership chaining not working with sp_cursorfetch

ownership chaining is not working for me in some cases. I use Microsoft SQL Server 14.0.3370.1. This is the setup for my tests: create function fnTest() returns int begin return 3 end go create ...
user avatar
  • 121
0 votes
0 answers
23 views

Move two or more columns as starting columns in TSQL depending on values

AnimalId Date Remark Number PA PCountA PB PCountB PC PCountC PD PCountD AN001 2022-01-25 LR 22025002 coli 100 Strep ag 100 Strep s 100 Staph a 100 AN001 2022-01-25 12:10am 22025002 coli 300 Staph a ...
user avatar
0 votes
1 answer
47 views

How can I set-up SQL Server alert for when table got corrupted?

Recently one of the database tables got corrupted in production's SQL server. I am trying to create an alert for this kind of issue. I don't see exact Error number or severity to choose from while ...
user avatar
0 votes
0 answers
33 views

The configuration Option for a column is not unique error - sql server

I have a table with 5 columns. regionId is the primary key. One of the columns, regionKey, is a sql_variant type. Create Table #Region ( regionId int identity(1,1), regionName varchar(100), regionKey ...
user avatar
0 votes
2 answers
119 views

How to manipulate the output of a GROUP BY CUBE query changing the NULL values generated for the groups?

I wrote a query to sum salaries grouped by Country and Gender. I don't want the rows to show NULL or empty values for the totals, instead I want it to show Total and Grand Total to better describe the ...
user avatar
0 votes
1 answer
29 views

Inhibiting results to pane in SSMS (TSQL language) via script

I found that one could use a menu to "discard" results in SSMS (TSQL language). Please see: Original Question about inhibiting results and its answer. However, I was wondering if there is a ...
user avatar
1 vote
1 answer
62 views

Log Replay on top of SQL database files captured during VSS snapshot failed

I am having a SQL Server and want to perform hourly VSS based backups (will store the VHD separately in a disk) and perform log backups in every 10 min interval. And with the help of backed-up VHD and ...
user avatar
2 votes
0 answers
131 views

Adding a new column to a table that has more than 1 billion rows

I have a table that has more than 1 billion rows. I have a requirement to add a new column to this table. Alter Table mylargetable Add newColumn varchar(50) NULL I'm concerned that this might take a ...
user avatar
-3 votes
1 answer
41 views

How to write select statment that get data based on partid from last date to current date with filling gapes?

I work on sql server 2012 i face issue i can't make select statment get Partid from last month until current month march (3) . based on last date exist per partid and on same time if there are any ...
user avatar
0 votes
1 answer
29 views

Convert 7 columns in two rows to 14 columns in one row

I need a T-SQL script to change two rows to one row. The rows are grouped by Student ID and Unique Course Identifier. The second row columns in the groups for the Days, Start Time, End Time, Start ...
user avatar
  • 5
3 votes
3 answers
170 views

How to rename logical file names in recovery pending database that cannot be recovered?

Here's the situation: Someone renamed database A to A_old in SSMS They did not rename the physical nor logical file; physical file was renamed somehow to A_old.mdf They then created a new database ...
user avatar
2 votes
2 answers
157 views

Create unique index based on two columns (bi-directional)

I want to make a check constraint or a unique index to validate that I'm not creating duplicates within my table based on 2 columns, I do not want the same two IDs in these columns. Thinking about ...
user avatar
  • 123
2 votes
2 answers
137 views

Temporarily disable SELECT for one table (during an update)

We have a dedicated data mart SQL Server with several really big tables that are updated one partition at a time via parametrized SSIS loop. Quite often when clients try to read such a table during an ...
user avatar
  • 31
0 votes
1 answer
82 views

Return SQL Agent Job status in SSRS when executing job from SSRS

I'm calling a SQL Agent job from SSRS and using a parameter to determine which job to run. I can run the job(s) just fine. What I'm looking to do is return a message back to the user based on the job ...
user avatar
  • 309
3 votes
1 answer
893 views

Very slow update statement taking up to 11min to update 20 or 0 rows even with indexes

When I update 20 rows or no rows, it takes 11 minutes. I mean 20 rows or no rows by different on m.MaximumReflowTemperatureID <> r.z2valueid between two tables. Why is my update so slow, even ...
user avatar

1
2 3 4 5
59