40 questions with SQL Server Transact-SQL tags

Sort by: Updated
Updated Created Answers
0 answers

cleanup old transactional replication snapshot files

I have a transactional replication between a publisher and a subscriber where the subscriber also acts as the distributor. I am facing storage issues as old snapshot files are not being auto cleaned up after successful application via distributor. I…

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,638 questions
Windows Server
Windows Server
A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.
12,077 questions
SQL Server Transact-SQL
SQL Server Transact-SQL
SQL Server: A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.Transact-SQL: A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
40 questions
asked 2024-04-18T17:46:01.6433333+00:00
Abimbola Adeniran 21 Reputation points
edited the question 2024-04-18T17:46:32.64+00:00
Abimbola Adeniran 21 Reputation points
1 answer

should i prefer join over exists or in

hi, I have seen people use exists when exists is required and no cols are required in select. But I notice sometimes exists slows down and join performs better q1) is there any news i should know ,  that one should not use exists or in , and should go…

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,638 questions
SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,790 questions
SQL Server Transact-SQL
SQL Server Transact-SQL
SQL Server: A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.Transact-SQL: A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
40 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,547 questions
asked 2024-04-16T07:20:37.7866667+00:00
Rajesh Kumar Yadav 0 Reputation points
answered 2024-04-18T00:59:52.34+00:00
Greg Low 1,155 Reputation points Microsoft Regional Director
1 answer

What Roles give SHOWPLAN permission?

With certain server level or database level roles, you have SHOWPLAN permission. Is there a hierarchy list that would indicate all the permissions inherited by various roles? I'm thinking of assigning ##MS_ServerStateReader## to our developer Login on…

Azure SQL Database
SQL Server Transact-SQL
SQL Server Transact-SQL
SQL Server: A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.Transact-SQL: A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
40 questions
asked 2024-04-16T23:09:20.67+00:00
Nick Ryan 216 Reputation points
answered 2024-04-17T04:51:36.88+00:00
Olaf Helper 40,656 Reputation points
1 answer

VB.NET SQL Connection and SQL Command Error BC3002

Hello, I'm in the process of converting VBA to VB.NET with SQL statements. One of the changes is utilizing the "ExecuteNonQuery". Since I'm still learning the syntax, I simply copied the VB.NET statements from a web-site. But I'm getting 2…

VB
VB
An object-oriented programming language developed by Microsoft that is implemented on the .NET Framework. Previously known as Visual Basic .NET.
2,563 questions
SQL Server Transact-SQL
SQL Server Transact-SQL
SQL Server: A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.Transact-SQL: A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
40 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,547 questions
asked 2024-04-16T16:02:09.4533333+00:00
Giacomo Raucci 296 Reputation points
commented 2024-04-16T19:46:11.4966667+00:00
Viorel 111.7K Reputation points
1 answer

Retrieve value from the row above

How can I retrieve the FinalBalance value from the previous row and add it to the Total value in the current row, to get the new FinalBalance value for each row? The table structure and desired outcome are shown in the image below. Each row's TransDate…

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,638 questions
SQL Server Transact-SQL
SQL Server Transact-SQL
SQL Server: A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.Transact-SQL: A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
40 questions
asked 2024-04-15T21:16:50.3333333+00:00
Michael M.M. D'Angelo 176 Reputation points
edited an answer 2024-04-16T01:53:38.6666667+00:00
CosmogHong-MSFT 22,616 Reputation points Microsoft Vendor
2 answers

Connect to on-prem sql server using Azure Relay

Hi, I would like to connect to a on-prem sql server and execute T-SQL commands to listen and receive SQL broker messages from Azure cloud hosted .net application. How can i connect to on-prem sql server and execute these sql commands and receive the…

.NET
.NET
Microsoft Technologies based on the .NET software framework.
3,344 questions
Azure Fluid Relay
Azure Fluid Relay
An Azure service used to add real-time collaborative experiences to apps with Fluid Framework.
2 questions
SQL Server Transact-SQL
SQL Server Transact-SQL
SQL Server: A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.Transact-SQL: A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
40 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,547 questions
asked 2024-04-01T07:44:00.09+00:00
Santhi Dhanuskodi 125 Reputation points
edited the question 2024-04-15T16:10:13.3+00:00
MayankBargali-MSFT 68,066 Reputation points
3 answers

sys.dm_exec_cached_plans not showing any data after executing stored proc multiple times.

Executed different stored proc multiple times and ran below query to check stats. its not showing any data. SQL Version : Microsoft SQL Server 2012 (SP4) (KB4018073) - 11.0.7001.0 (X64) Stored Proc : exec [dbo].[uspGetManagerEmployees]…

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,638 questions
SQL Server Transact-SQL
SQL Server Transact-SQL
SQL Server: A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.Transact-SQL: A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
40 questions
asked 2024-02-16T16:39:46.52+00:00
SSR 20 Reputation points
edited an answer 2024-04-15T06:24:06.9433333+00:00
SSR 20 Reputation points
1 answer

Complex Pivot using the existing data

--ALTER PROCEDURE Energy.GetGTSTDataForManualEntry --( -- @Date DateTime -- ) --AS BEGIN DECLARE @Date DateTime = '2024-03-29 00:00:00.000'; DECLARE @CalendarID INT = Time.GetCalendarIDByLocationCode('Energy'); DECLARE @DayPeriod…

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,638 questions
SQL Server Transact-SQL
SQL Server Transact-SQL
SQL Server: A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.Transact-SQL: A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
40 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,547 questions
asked 2024-04-07T09:23:56.2033333+00:00
pepcoder 1 Reputation point
edited a comment 2024-04-11T05:13:40.8+00:00
pepcoder 1 Reputation point
4 answers

SQL Server optional parameter performance issue

Hello guys, I have an optional parameter in a SP e.g. @MyParam int = NULL When I check the param against NULL upfront I end up with TWO queries e.g. IF (@MyParam IS NULL) BEGIN SELECT ... END ELSE BEGIN SELECT ... END Otherwise if I check…

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,638 questions
SQL Server Transact-SQL
SQL Server Transact-SQL
SQL Server: A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.Transact-SQL: A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
40 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,547 questions
asked 2024-04-05T12:56:45.11+00:00
Kuler Master 246 Reputation points
answered 2024-04-08T02:20:03.29+00:00
CosmogHong-MSFT 22,616 Reputation points Microsoft Vendor
1 answer

how to fix bulk insert issue

cannot bulk load because file "\mynetwork\fiel\myfile" could not be opened. Operating system error code 5 (access is denied) my job which bulk inserts from the above mentioned source used to work fine. But lately, I see this error. Nothing…

SQL Server Transact-SQL
SQL Server Transact-SQL
SQL Server: A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.Transact-SQL: A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
40 questions
asked 2024-03-19T16:38:35.3433333+00:00
Biniam Ashami 0 Reputation points
answered 2024-04-07T07:29:53.7066667+00:00
Abubakar Riaz 1 Reputation point
0 answers

Parsing First, Last, Middle Initial and suffix from full name

I am trying to extract first last middle initial and suffix from a name field and there's no set pattern please see the pattern of the in my table LOPEZ ROQUE, CARMELINA ORTEGA, GISELLE A A RUSSELL JR, WILLIE C NUNEZ, LILIANA I have looked online and…

SQL Server Transact-SQL
SQL Server Transact-SQL
SQL Server: A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.Transact-SQL: A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
40 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,547 questions
asked 2024-04-01T01:59:34.55+00:00
Ali Ahad 91 Reputation points
commented 2024-04-02T12:20:26.9066667+00:00
Yitzhak Khabinsky 24,936 Reputation points
2 answers One of the answers was accepted by the question author.

Could drop user sa?

Dear Sir/Madam As we know user sa is default user that have sid=0x01 is_disabled=0 principal_id=1 on sys.server_principals table. Could we drop it to protect from attacker? Srean Regard Thank

SQL Server Transact-SQL
SQL Server Transact-SQL
SQL Server: A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.Transact-SQL: A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
40 questions
asked 2024-03-07T04:53:58.0233333+00:00
Srean Noem 40 Reputation points
accepted 2024-03-29T03:30:29.4466667+00:00
Srean Noem 40 Reputation points
3 answers

DATEDIFF (YEAR, StartDate, EndDate) returns incorrectly

I am trying to figure out why the DATEDIFF(YEAR) Function is returning incorrectly and am wondering if I have to get the StartDate and EndDate in a certain format before attempting to use the function. SELECT DATEDIFF(YEAR, '2023-12-31 08:31:42.373',…

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,638 questions
SQL Server Transact-SQL
SQL Server Transact-SQL
SQL Server: A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.Transact-SQL: A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
40 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,547 questions
asked 2024-03-28T13:03:43.4066667+00:00
Bobby P 221 Reputation points
answered 2024-03-29T02:13:05.4266667+00:00
CosmogHong-MSFT 22,616 Reputation points Microsoft Vendor
1 answer

The MSSQLSERVER service terminated unexpectedly

Dear All SQL expert, Do you know what is the case that make The MSSQLSERVER service terminated unexpectedly? Recently, The SQL server (MSSQLSERVER) and SQL Server Agent are stopped automatically during running import data to sql server. We are using…

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,638 questions
SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,790 questions
SQL Server Transact-SQL
SQL Server Transact-SQL
SQL Server: A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.Transact-SQL: A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
40 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,547 questions
asked 2024-03-27T05:07:01.8566667+00:00
Suy Peang 91 Reputation points
commented 2024-03-28T06:18:59.3333333+00:00
Suy Peang 91 Reputation points
3 answers

select query left outer join not returning expected result

declare @pTargetFund varchar(8) = '500', @tolAmt money = 1 declare @test1 table ( amount money, tradeid int, cusip varchar(12), principal decimal(14,0), principalx decimal(2,0), accruint decimal(14,0), accruintx decimal(2,0), transnum…

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,638 questions
SQL Server Transact-SQL
SQL Server Transact-SQL
SQL Server: A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.Transact-SQL: A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
40 questions
asked 2024-03-21T13:45:39.6833333+00:00
Spunny 326 Reputation points
answered 2024-03-22T01:55:00.4366667+00:00
CosmogHong-MSFT 22,616 Reputation points Microsoft Vendor
1 answer One of the answers was accepted by the question author.

Easiest way to verify that SQL has access to a folder that would contain mdf and ldf files

Re: Easiest way to verify that SQL has access to a folder that would contain mdf and ldf files I am writing a script to move ldf and/or mdf files. Doing the basic ALTER DATABASE commands below, but writing them in dynamic SQL so that multiple database…

SQL Server Transact-SQL
SQL Server Transact-SQL
SQL Server: A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.Transact-SQL: A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
40 questions
asked 2024-03-07T13:56:37.7066667+00:00
Grossnickle, Brenda 40 Reputation points
accepted 2024-03-14T16:51:28.9766667+00:00
Grossnickle, Brenda 40 Reputation points
1 answer

How to determine which row in a table a LOB Page is connected to?

I have a table with Lob Data Pages, using DBCC IND and DBCC PAGE to look at a page, I am curious if there is a way to determine which record in the table the data on that page is tied too?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,638 questions
SQL Server Transact-SQL
SQL Server Transact-SQL
SQL Server: A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.Transact-SQL: A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
40 questions
asked 2024-03-12T21:15:51.3066667+00:00
John Couch 181 Reputation points
commented 2024-03-13T04:18:12.9233333+00:00
Greg Low 1,155 Reputation points Microsoft Regional Director
2 answers One of the answers was accepted by the question author.

The conversion of a nvarchar data type to a datetime

When I get a report via view, I get an error like this. how can i fix this

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,638 questions
SQL Server Transact-SQL
SQL Server Transact-SQL
SQL Server: A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.Transact-SQL: A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
40 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,547 questions
asked 2024-02-20T09:21:41.4+00:00
Rabia Kurnaz 386 Reputation points
accepted 2024-03-08T07:54:56.8866667+00:00
Rabia Kurnaz 386 Reputation points
1 answer

Database consistency check for 2 TB database taking 14 hours 20 mins

1)Database size is 2 TB 2)Largest Tables (only one large table) Total pages it has - 274190987 3)Execution for this single DB consistency check is 13 hours 38 minutes 23 seconds. ---most of the time we had to cancel the execution just to avoid conflicts…

Windows Server 2019
Windows Server 2019
A Microsoft server operating system that supports enterprise-level management updated to data storage.
3,443 questions
Azure SQL Database
Azure VMware Solution
Azure VMware Solution
An Azure service that runs native VMware workloads on Azure.
314 questions
SQL Server Transact-SQL
SQL Server Transact-SQL
SQL Server: A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.Transact-SQL: A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
40 questions
asked 2024-02-28T16:42:06.3033333+00:00
Dhaval Ambawade 0 Reputation points
commented 2024-02-29T22:18:01.6466667+00:00
Erland Sommarskog 100.8K Reputation points MVP
2 answers

Why Dynamic Sql written in procedure working and returning data on development server but not returning on production server

Why Dynamic Sql written in procedure working and returning data on development server but not returning on production server

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,638 questions
SQL Server Transact-SQL
SQL Server Transact-SQL
SQL Server: A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.Transact-SQL: A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
40 questions
asked 2024-02-08T16:39:24.89+00:00
BIDYUT KARMAKAR 0 Reputation points
commented 2024-02-29T06:08:29.9433333+00:00
CosmogHong-MSFT 22,616 Reputation points Microsoft Vendor