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 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…
best approach to migrating a table from system to another
I have a test system where we update entries on a table for cross refence purpose and was wondering the best approach for this.
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…
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…
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…
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…
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…
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]…
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…
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…
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…
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
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',…
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…
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…
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…
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?
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
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…