Questions tagged [t-sql]

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

209 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
8
votes
4answers
2k views

SELECT query slow even with nonclustered Index and a full-text index

I am attempting to query between 0 and 65,000 rows from a table. The server is using Microsoft SQL Server 2014, and I have no way to change the hardware on the server. Schema [Id] (PK) INT ...
3
votes
2answers
981 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 ...
3
votes
2answers
4k views

Full text search using words stored in another table

Is there a way to do a full-text search using words stored in another table? This is my scenario: Table_A contains phrases entered by users in field "UserInput" (and other stuff). I have a full-...
3
votes
0answers
125 views

Data Synchronization from Remote MS SQL Server

I have a client server on which my user has limited permissions (i.e. SELECT only for example). I need to select some data from that server and insert into my local database. This has to be a ...
3
votes
0answers
79 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 (...
3
votes
0answers
4k views

SQL DatabaseMail smtp error 5.7.57 at one site, email works at another with the same details

I have several SQL databases on different servers. I have used a script to set up Database mail. EXECUTE msdb.dbo.sysmail_add_account_sp @account_name = 'Emailer' ,@description = 'Email out ...
2
votes
1answer
112 views

Execute SQL conditionally based on database Compatibility Level

The Problem I have a script that I need to execute on both SQL Server 2016 and SQL Server 2019. If executing on Server 2019+, I want to use syntax that is only supported on that version (specifically, ...
2
votes
1answer
139 views

What is the difference between `ALTER TABLE DISABLE TRIGGER` and `DISABLE TRIGGER`?

My company logs DDL on one database using a Database Trigger, looking for DDL including: ALTER_TABLE CREATE_TRIGGER ALTER_TRIGGER DROP_TRIGGER When I disable or enable triggers using ALTER TABLE x ...
2
votes
0answers
43 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 ...
2
votes
1answer
152 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 ...
2
votes
0answers
57 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
0answers
336 views

Reading decimal mark comma with an external table

SQL-server 2016, SQL server management studio (SSMS). Creating external tables with transact-SQL and Polybase. How can we read decimals with a decimal mark, comma instead of dot? When using float and ...
2
votes
0answers
430 views

Storing encrypted and not encrypted data in the same column value?

I have several concept questions about encrypting data. The task My goal is to encrypt NVARCHAR(3800) column but I want to encrypt only small percent of the data (less than 1% of it). As the table ...
2
votes
0answers
73 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 ...
2
votes
1answer
132 views

TSQL Generic Row number theoretical question

To start, We have a pretty complicated system that's been a compilation of over 20 years of code... And the code needs to work with SQL2005 all the way to SQL2014 (can't use the offset Fetch Next) ...
2
votes
0answers
2k views

What is the correct way to rename/swap a table in production

I have two tables, tblCode and tblCodeAlternative tblCode has about 4100 rows and is used in production. tblCodeAlternative has about 4200 rows and is in the production database but not used ...
2
votes
0answers
417 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
0answers
224 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 ...
2
votes
0answers
70 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 ...
2
votes
0answers
689 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
0answers
332 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....
2
votes
0answers
539 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] ...
2
votes
0answers
7k 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 ...
2
votes
0answers
547 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 ...
1
vote
2answers
48 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 ...
1
vote
2answers
23 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. ...
1
vote
1answer
59 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
1answer
61 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 ...
1
vote
1answer
34 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 ...
1
vote
3answers
178 views

How to calculate end_date from start_date and duration in SQL Server Query

I have a table with 4 fields .Start-date, Duration, End_date, Actual_date. I am providing the start_date and duration as the input to calculate end-date. While calculating the end_date the ...
1
vote
0answers
32 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
1answer
42 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 ...
1
vote
0answers
136 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\...
1
vote
0answers
51 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
vote
0answers
23 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
vote
1answer
5k views

How to replace multiple unicode characters

I have identified a couple of unicode characters in my database by means of the following query: SELECT name FROM table WHERE name NOT LIKE '%'+CAST(name AS VARCHAR(1000))+'%' ...with which I get ...
1
vote
1answer
107 views

T SQL Cte Previous record calcuation

I'm using SQL Server 2016. I have the below table: SKU Shop Week ShopPrioirty Replen Open_Stk Open_Stk Calc 111 100 1 1 0 17 ...
1
vote
0answers
44 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 (...
1
vote
0answers
229 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. ...
1
vote
0answers
40 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 ...
1
vote
0answers
157 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....
1
vote
0answers
1k 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 ...
1
vote
1answer
69 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 ...
1
vote
1answer
870 views

INSERT/SELECT xml column from one table to another

First off I apologize for asking a question I'm sure is well answered, however I cannot quite understand what I'm reading. I have an existing table with an xml column. I have a second table with the ...
1
vote
0answers
33 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 ...
1
vote
0answers
73 views

Creating 'temporary' memory optimised tables with only read/write roles

I'm currently trying to get an application to use memory optimised tables instead of temporary tables, but have hit an issue. The issue is related to permissions, as users of the application are only ...
1
vote
0answers
552 views

Viewing directory of a mapped drive

I'm using the following query to view files/folders in a given directory. It works locally, but when trying to view a mapped drive I get... "The system cannot find the path specified." I have the ...
1
vote
0answers
107 views

How to list PDF Files names and path in a SQL Server Database

I need help on script to list PDF Files names and path in a SQL Server Database. DECLARE @full_path VARCHAR(500) SET @full_path = '\some\path\here' DECLARE @FindFile TABLE (FileNames nvarchar(...
1
vote
0answers
323 views

Encrypt through Java and Decrypt through SQL Server AES 256

Am trying to insert some value through JAVA Application with AES Algorithm and need to decrypt the data from the SQL Procedure and need to process a data. Which is the best practice to do so?
1
vote
1answer
68 views

Averaging 2 values based on an ID in SSRS

I am hoping I might be able to get some help with a problem I am having. I am currently trying to build a report in SSRS to replace an excel spreadsheet we use for data analysis so the report can be ...

1
2 3 4 5