SQL
39 TopicsSQL Cluster Connecting to Linked Server over a firewall.
Hi, Scenario: SQL FCI Cluster --> firewall (stateful) --> SQL Linked Server SQL cluster needs to connect to linked server at the other side of a firewall. Question: What needs to be opened on the firewall? SQL VIP --> SQL VIP or are the cluster node IP's required too? SQL VIP --> SQL VIP Node1 --> SQL VIP Node2 --> SQL VIP Many thanksSolved35Views0likes2CommentsDeadlocks on High Frequency Updates
Using SQL Server 2022, I'm stress testing an UPDATE statement. I'm using a python script to send parallel requests to the database. The problem is that, as soon as the number of parallel requests exceed max_workers_count, 576 in my case, I get multiple errors of the form: ('40001', '[40001] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Transaction (Process ID 448) was deadlocked on lock | thread resources with another process and has been chosen as the deadlock victim. Rerun the transaction. (1205) (SQLExecDirectW)') I wasn't able to reproduce the error with less requests than max_workers_count. The UPDATE request is the following: UPDATE dbo.UsersAnswer SET UsersSelectionType = ? WHERE For_Question = ? AND For_Quiz = ? AND FK_Answer = ?; Note that, I've tried with and without (UPDLOCK, ROWLOCK) and (UPDLOCK), but it doesn't change the outcome. Also, the updates are done for the same primary key. Finally, the UsersAnswer table is created as follows: CREATE TABLE [dbo].[UsersAnswer]( [For_Question] [smallint] NOT NULL, [For_Quiz] [uniqueidentifier] NOT NULL, [FK_Answer] [int] NOT NULL, [UsersSelectionType] [tinyint] NOT NULL, CONSTRAINT [PK_UsersAnswer] PRIMARY KEY CLUSTERED ( [For_Question] ASC, [For_Quiz] ASC, [FK_Answer] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[UsersAnswer] WITH CHECK ADD CONSTRAINT [FK_UsersAnswer_Answer_FK_Answer] FOREIGN KEY([FK_Answer]) REFERENCES [dbo].[Answer] ([PK_Answer]) GO ALTER TABLE [dbo].[UsersAnswer] CHECK CONSTRAINT [FK_UsersAnswer_Answer_FK_Answer] GO ALTER TABLE [dbo].[UsersAnswer] WITH CHECK ADD CONSTRAINT [FK_UsersAnswer_QQ_For_Question_For_Quiz] FOREIGN KEY([For_Question], [For_Quiz]) REFERENCES [dbo].[QQ] ([FK_Question], [FK_Quiz]) ON DELETE CASCADE GO ALTER TABLE [dbo].[UsersAnswer] CHECK CONSTRAINT [FK_UsersAnswer_QQ_For_Question_For_Quiz] GO Do you have any idea on what could cause the deadlock? The deadlock graph is huge, you can find it here. Thanks for your insights on this.Solved167Views0likes8CommentsCapturing Latest Effective Date of Current Instance of Position
I am having issues trying to formulate a JOIN statement within my query that will provide the values that I am needing for all employees. Specifically, situations where an employee held the position multiple times. Here my current statement segment: JOIN (SELECT R_POSITION, WORK_ASSIGNMNT, EMPLOYEE, MIN(EFFECT_DATE) AS EFFECT_DATE FROM HR_EMP_POSITIONS GROUP BY R_POSITION, WORK_ASSIGNMNT, EMPLOYEE) AS EP ON EP.R_POSITION = W.POSITION AND EP.EMPLOYEE = W.EMPLOYEE AND EP.WORK_ASSIGNMNT = W.WORK_ASSIGNMNT For my statement, I need to retrieve the latest EFFECT_DATE of the last time the employee held a specific position that matches their current position. Please find my sample document with the EP and W tables. My current statement works for employee A; however, for employee B it is providing the 10/16/2023 date when I need it to populate the 8/26/2024 date. Any ideas on what I can do to get the data that I need? I don't have any other fields that I can use to help refine the criteria.61Views0likes3CommentsSQL Help : Time difference for events within a specific group that resets based on an anchor event
Hi All, Need help with an SQL query to find the time difference between events that happen in sequence, where an anchor event resets the calculation. Below is the sample data and the expected output. thanksSolved94Views0likes5CommentsDoes the Maintenance Cleanup Task delete all backup files or only the ones backed up in the Back Up
I have one very large database that I only want to backup once a week so I'm wondering if the Cleanup Task is going to remove because the current maintenance plan is set to delete after 2 days.125Views0likes1CommentNeed help in restoring table using large .sql data file(4GB)
We have run into a situation where we need to restore the data into a SQL table. We have taken backup of data only using MS SQL Server and the .sql file size is 4 GB. Since we are unable to open this file in SQL server, we are using sqlcmd to execute this file but after restoring 140K records it is throwing some syntax error (Unclosed quotation mark after the character strig 'abc_121223354565 and incorrect syntax near 'abc_121223354565 ). The .sql file has total 240K records in it. Questions: 1. When the backup was provided by SQL server only, why it is throwing syntax error? 2. How to open this large file to fix the syntax error? if option 2 is not possible, how do we split the large file into smaller chunks so we can identify the exact place where the issue is? Thanks, Srikanth371Views0likes5CommentsSub query
I want to use a query to pick out the latest record for a user. A user may have 5 records in the database and I want to pick the latest one. The latest one can be determined by the user Id and max mod id as there is a new id created every time a user registers for one. Is this the most efficient query to pick the latest record SELECT MC.USERID ,OG.OrganisationID , MC.Id AS ModID , MC.TYPE AS ResID ,CAST(MC.EndDateTime AS DATETIME2) AS EndDate ,CAST(MC.ExpiryDate AS DATETIME2) ExpiryDate ,CASE WHEN ExpiryDate >= GETDATE() AND IsDeleted = 0 THEN 1 ELSE 0 END AS Compliance FROM MC LEFT JOIN OG ON MC.USERID = OG. USERID INNER JOIN ( SELECT MC.USERID ,MAX(MC.Id) AS Max_Record FROM MC GROUP BY MC.USERID) Latest ON MC.USERID = Latest.USERID AND MC.Id = Latest.Max_Record767Views0likes1CommentSQL Server Config Manager Error "MMC could not create the snap-in"
Hi, I have seen this error elsewhere online. I have gone to mmc to enable the snap in and I still have had no fix. My computer is running Windows Server 2022, SQL Server Express 2022, and SSMS. I have reinstalled, repaired, and all of the other tricks. Help!Solved2.3KViews0likes3Comments