Questions tagged [t-sql]

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

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

SQL Server Select Query Is Slow Even With NC-Index and Full-Text Index

I am attempting to query a maximum of 65,000 records in a database (minimum of 0). The server is using MS SQL Server 2014, and I have no way to change the hardware on the server. Schema [Id] (PK) ...
5
votes
1answer
176 views

Code required to make column not nullable

I have a table where I mistakenly allowed NULL values in a column: CREATE TABLE EXAMPLE ( EXAMPLE_ID INT IDENTITY(1, 1) NOT NULL, FOO_ID INT, CREATED_ON DATETIME DEFAULT CURRENT_TIMESTAMP,...
3
votes
2answers
477 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
0answers
228 views

Best Way to Index Multiple Effective Date Columns

I've got a table that is primarily to represent relationships between entities (i.e. mostly composed of foreign keys). These relationships change over time, and so the table has a StartDate and an ...
3
votes
2answers
3k 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
121 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
77 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
1answer
552 views

TSQL LDAP query via Linked Server

I have strange problem, I have 3 SQL Server 2012 with Sp1 instances, and on all of them, the versions are identical. I am trying to query Active Directory services, on 2 of them this is working ...
3
votes
0answers
3k 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
0answers
16 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 ...
2
votes
0answers
41 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
129 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
49 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
296 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
417 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
1answer
229 views

Why is our select statement using a predicate clause with string concatenation running faster than our select statement using a single field?

We have been working on an application that utilizes a view with several tables in our database. This view is utilized throughout the application, and although significantly more complicated than the ...
2
votes
0answers
72 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
123 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
1k 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
408 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
68 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
632 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
525 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
533 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
1answer
28 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
30 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
70 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
16 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
0answers
30 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
55 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
1answer
86 views

Error during sp_refreshsqlmodule after altering table type

I am trying to add a column to a user-defined table type. I have done some research and here is what I have now: -- First, rename existing table type to something else EXEC sp_rename '...
1
vote
0answers
34 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
2answers
380 views

Getting Msg 15151, Cannot alter the user 'xxxx', because it does not exist or you do not have permission

I have the following script and I keep getting errors when it is executed. USE [master] GO If Not EXISTS (Select loginname from [master].[dbo].[syslogins] Where name = 'xxxx' and dbname = 'xxxx-...
1
vote
0answers
20 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
2k 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
65 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
35 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
196 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
108 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
66 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
732 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
2answers
5k views

The EXECUTE permission was denied on the object 'SPROC', database 'DATABASE', schema 'dbo'

I have a few users that are unexpectedly receiving this error message today when they attempt to refresh a stored procedure via Excel: The EXECUTE permission was denied on the object 'SPROC', ...
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
72 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
329 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
79 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(...