Questions tagged [t-sql]
Transact-SQL (T-SQL) is a dialect of SQL used by Microsoft SQL Server and SAP's Sybase.
208
questions with no upvoted or accepted answers
4
votes
0
answers
130
views
T-SQL: Iteration vs. Set-Based Operations When Independence of Ancillary Data is Needed
BACKGROUND
I am using Microsoft SQL Server Management Studio v18.9.1 on Windows 10.
I've created a database to store test results of our company's widget (not yet being produced). I am working on a ...
- 197
4
votes
2
answers
3k
views
Way to obtain full list of column dependencies for stored procedure?
I've built a stored procedure (which we can call sproc_deps) that uses sys.sql_expression_dependencies and sys.dm_sql_referenced_entities. I want it to list out all of the tables and columns used by a ...
- 51
3
votes
0
answers
480
views
What is the cause of persistent SQL Server deadlocks with a chosen victim, where the victim is never stopped, and no transactions are rolled back?
We are experiencing an issue with Microsoft SQL Server where every few days of running our application against the database, it goes into a state where it keeps deadlocking. It logs a deadlock about ...
- 31
3
votes
1
answer
387
views
Assign a student to a Classroom based on course and scheduled hour
Each year we have to manual assign students to classes that were not included in original assignment either they are new or the class was cancelled etc,
we are talking 100s of students where we have ...
- 33
3
votes
0
answers
90
views
Workout change operation with temporal table records
I have just started using using temporal tables/system versioning, its very easy to setup and get going in terms of querying the data. The one thing missing for me is storing the change operation (...
- 131
2
votes
2
answers
217
views
Change Tracking SYS_CHANGE_COLUMNS on Update operation
I would like to get an understanding of why on an update operation in the change tracking table, the SYS_CHANGE_COLUMNS can be NULL. I would expect the SYS_CHANGE_COLUMNS will be populated.
In the ...
- 31
2
votes
1
answer
77
views
variable grouping? or filling available slot from pool
i am creating a execution list for some kind of engine, which executes 10 tasks at a time.
the tasks are two types: standalone and dependent.
dependent ones should be executed in ExecutionOrder, ...
- 53
2
votes
1
answer
61
views
Advantage of querying data outside a filtered index
Is there any way to take an advantage of having a filtered index to speed DML queries targeting data sets outside that index?
My playground:
Table with billion rows and 100 columns
Unique clustered ...
- 503
2
votes
0
answers
287
views
sys.login_token doesn't include particular windows groups while they can be listed by sys.database_principals
When I query
SELECT principal_id, name, [type]
FROM sys.login_token
I found some local windows groups were not there.
However, when I check by
SELECT principal_id, name, [type]
FROM sys....
- 435
2
votes
0
answers
85
views
Is there a setting in SQL Server to keep triggers from firing SELECT @@IDENTITY
This is a SQL Server 2014 Server and we have application code the does a Select @@Scope_Identity after an insert into a table.
Recently other developers added triggers to this table and ever since, ...
2
votes
0
answers
747
views
Inserting into an table with identity column from stored procedure works even though it shouldn't
We have this piece of (anonymyzed code)
create table #SomeTmpTable
(
[RowNumber] integer identity,
[Value] integer,
primary key (RowNumber)
)
insert into #SomeTmpTable (...
- 1,523
2
votes
0
answers
80
views
TVF Poor Performance with Linked Query
I have an inline TVF that take much longer to run than should be necessary. The issue looks to be the Linked Server reference.
The Linked Server is SQL Server 2012. The server with the TVF is SQL ...
- 1,394
2
votes
0
answers
482
views
Cross database - Table valued Stored Procedure
I have the same table type in two databases (DB1 and DB2),
DB1 has an stored proc (sp1) which has the above type as an input parameter.
Now, I want invoke this SP from DB2.
But, I am getting this ...
2
votes
0
answers
233
views
How do I provide a failsafe for retrieval of data from excel to SQL
So say there is a process in place for importing data from excel spreadsheet to SQL using SSIS. And there are two tables, one with new data and one with data from its previous run.
I sometimes have ...
- 667
2
votes
0
answers
79
views
restore script variable date and time
Goodafternoon,
i am looking for a variable for the date and time. I have several backups in one directory and want to restore the latest to a another server. Is there a variable i can use in my ...
- 21
2
votes
0
answers
862
views
Using :Connect I keep getting msg 911 - database [dbname] does not exist
I have a two part T-SQL script, the first part runs against a database on a different server, the second part is supposed to run on the local PC. I'm using SQLCMD mode and the :Connect keyword but ...
2
votes
0
answers
521
views
What is the bare minimum access needed in the public role?
I need to lock down access to a specific database for a user - they should only have access to execute a couple of stored procedures.
However, by default the user gets everything in the "public" role....
- 405
2
votes
0
answers
601
views
Avoiding Functions() in the Where clause
Okay, while analyzing queries against a SQL Server 2008 R2 database, I happened upon the following query:
(@p__linq__0 nvarchar(4000))
SELECT TOP (1) [Extent1].[OrderSOId] AS [OrderSOId], [Extent1]
...
- 187
2
votes
0
answers
8k
views
DELETE failed because the following SET options have incorrect settings: 'ANSI_NULLS'
I have inherited a few scripts that are pumped through a c#.NET custom app to update a SQL2005 SP3 database. One of these scripts does a deletion on a user table called msw_timer_task, based on a ...
- 1,614
2
votes
0
answers
815
views
SQL Server : where with cast(value as varbinary) vs cast(value as varbinary(300))
Are there any theoretical performance differences between these queries in the scenarios below.
Scenarios are:
MyTable has Value nvarchar(300)
MyTable has Value nvarchar(max)
Q1:
declare @p ...
- 297
2
votes
1
answer
64
views
Insert rows into a table from another table row?
I've got a table Schedule
Id
unserId
Sunday
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
0
10
0
1
1
1
1
0
0
1
20
1
0
0
0
0
0
0
I need to insert lines from one row in table Schedule into seven ...
2
votes
1
answer
336
views
How to reverse only English words?
I've an nvarchar column with English and Hebrew letters in some fields.
In the fields where the English and Hebrew are together, the English letters are in reverse order (this is how the ERP program ...
- 71
1
vote
0
answers
83
views
Same update on different rows results in deadlocking
We are testing our SQL Server database for concurrency issues and it appears that a single, simple update statement being run concurrently in transactions is resulting in deadlocks.
Here is the ...
- 11
1
vote
0
answers
152
views
How to write query to link complex Parent-Child relationships
We have a table that has a DefectID field, and a PreviousDefectID field, in order to link defect records to each other as they change through time.
I need to link these fields together by creating a ...
- 11
1
vote
1
answer
281
views
Is there a best practice for resolving dependencies of SQL objects for build during development?
I have a greenfield project involving a SQL database that is under development. There is no production data involved yet. There is a rather long list of specifications which I expect can be ...
- 421
1
vote
2
answers
341
views
Update one table's row from the results of multiple rows of another table
I've seen posts on how to update multiple rows (specifically using case?), but my question is the inverse.
I have a table of phone_numbers
Person_ID
Phone_number
1
102-345-6789
1
102-445-6789
2
...
- 111
1
vote
0
answers
57
views
TSQL - retrieve all IDs in a tree for a given subnode
I've tried to translate and implement the PostGres solution to the problem found here: PostgreSQL - retrieve all IDs in a tree for a given subnode, but I'm either programming it wrong, or ...
- 111
1
vote
0
answers
179
views
How do I change the database owner in Synapse Analytics Serverless SQL Pool?
I am working with Azure Synapse Serverless SQL Pool. A former employee created the databases in the pool, and is listed as their owner. Consequently, I cannot drop the login.
I would like to change ...
- 111
1
vote
0
answers
273
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 / ...
- 532
1
vote
0
answers
75
views
Questions about execution plan
I'm reading "SQL Server Execution Plans, Third Edition, by Grant Fritchey".
It includes the following query and the execution plan (queried against the Adventure Works database).
SELECT soh....
1
vote
1
answer
226
views
Is there any way to monitor if all columns in an article is published in replication
In replication, if we add a column in Publisher then it doesnt replicate to Subscriber unless it is done explicitly. I am looking for a way to track or monitor this.
I am not able to find any system ...
- 11
1
vote
1
answer
2k
views
Comma separated value string and compare against other values
Hey all I have been trying to wrap my head around how to go about doing this for a while now and I'm not really coming up with anything.
Here is my query:
DECLARE @ATID AS VARCHAR(3) = 12
DECLARE @...
- 115
1
vote
2
answers
126
views
SQL SERVER Detailed Backup Report
I need to create a script that gives the report for the following information:
Backup Job Name
Databases that have been backed up in the last 24 hours
Size of each DB backup
Start time of backup
End ...
- 83
1
vote
2
answers
808
views
BULK INSERT does not fail when file contains commas instead of semicolons when FIRSTROW > 1
I encountered an issue where I received a CSV file that supposed to be delimited by semicolons (;) but was delimited by commas (,).
Bulk insert called by sqlcmd did not fail but did not insert either. ...
- 304
1
vote
0
answers
610
views
Debugging T-Sql code using Visual Studio 2019. Error message: frame not in module
DBAs,
Has anyone had success using Visual Studio 2019 for debugging T-SQL code, the way SSMS allows Step-into & Step-over?
When I hit debug (Alt-F5), gives me this error, after making some ...
- 565
1
vote
1
answer
310
views
Validating SQL Server Change Tracking Synchronization
I have some tables that I need to synchronize from one SQL Server database to another. I'm using SQL Server's "Change Tracking" feature. My program uses Change Tracking to synchronize the ...
1
vote
1
answer
954
views
Execute SSIS (package) in SSMS using T-SQL and return a value
Using VS (2019) I created an Integration Services Project to code an SSIS package. The goal was to call a Web API from an SSIS package using input variables like username/password/url and so on. I ...
- 11
1
vote
1
answer
119
views
Combine Rows with indirect relation
I am trying to create a report from a cloud based EHR so I cannot share real data and some of these tables are fairly massive. I will try to minimize and share the bare minimum and expand if someone ...
- 11
1
vote
0
answers
73
views
Removing files from a filegroup
I am writing some T-SQL code to retrieve the file names that make up a file group so that I can remove the files and the related filegroup.
The way that I coded it is below:
set @cnt = (select ...
1
vote
1
answer
162
views
Do I always need boilerplate code to prevent nested transactions?
Because I never know if my procedure might be called from other procedures that have an opened transaction, should I always check for existing transactions before opening a new one?
It seems like a ...
- 3,931
1
vote
0
answers
403
views
How to select all files of a SQL Server database from blob folder?
I have a SQL Server database.
You can use the following script to create similar database:
CREATE DATABASE [test]
CONTAINMENT = NONE
ON PRIMARY
(NAME = N'test', FILENAME = N'C:\Program Files\...
- 67
1
vote
0
answers
281
views
Capture trigger update() with inserted & Deleted changes only
I have price table that has over 100 columns.
I'm in the process of fine tunning triggers and reducing amounts of records inserted, to audit changes when updates are applied.
The audit table ...
- 1,071
1
vote
0
answers
53
views
Read all Captured Extended Events for a Given Extended Event Session Name
I want to be able to read all XE files for a dynamic XE session name. My XE session stores files to disk.
I have the query below
DECLARE @XeName SYSNAME = 'MyXE'
DECLARE @Path NVARCHAR(255)
SELECT @...
- 1,928
1
vote
2
answers
418
views
How to avoid conditional join in order to write a tsql view
I have a scenario where I have Roles, Set, DefaultSet, and CompanySet tables
Roles table
RoleName
---------
Admin
Viewer
User
Set table
Id Name
--- ----
1 Employee Data
2 Help ...
- 321
1
vote
0
answers
108
views
Determine if query comes after cascade update
We have a situation that in a trigger we are calling one procedure to update/insert values in another table. Now, the new requirement asked that we have to cascade update foreign key on that table (...
- 135
1
vote
0
answers
367
views
Worked example of closure tables with from and thru dates
I work for a law enforcement agency, and have been asked to create a way of storing the chain of command in such a way that its structure can be viewed at any point in time, not only as it is now.
...
- 11
1
vote
0
answers
44
views
Repivoting rows of pivoted data with an dynamic amount of rows
I am trying to create an overall sequential pivot of various rows of data, repeating each row as a new set of columns and using the sequence number as the trailing identifier in each column name. Here ...
- 11
1
vote
0
answers
3k
views
Openrowset is throwing error for file access as Access Denied
I am getting Access Denied error ..
Openrowset is throwing error for file access as Access Denied
I tried running these 2 queries as well..
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
...
- 653
1
vote
1
answer
153
views
How to index of filter extended event files in Sql Server
I am collecting errors data using the error_reported event and saving the data on XEL files. The problem is that there are hundreds of thousands of errors over there and querying them can take a lot ...
- 111
1
vote
0
answers
46
views
File Extensions are missing from new SSMS 2012
One of my colleagues SSMS recently got updated to 2012 version from SSMS 2008 R2. After that they are view only a few editors in SSMS for adding different File Extensions
Tools ==> Options ==>Text ...
