Microsoft SQL Server 2019 bug

ROHIT KUMAR 0

Hi,

I want to raise a bug for the MSSQL Server 2019 bug in which I have different schemas and suppose that a same-named table exists in both the schemas and if I update any one schema's table then it's updating the table in both schemas.

For ex.

Schemas:- Dbo, DeletedObjects

TableName:- Sysinfo (component, Brand)

Updating the DeletedObjects Sysinfo automatically updates the Dbo table even if I mention the schema name in the update clause.

As, Update DeletedObjects.Sysinfo Set Brand ='HP' where Componentname='Monitor'

It is updating the same value in the Dbo schema table.

Erland Sommarskog 111.6K MVP

Well, this works as expected for me:

CREATE DATABASE SchemaTest
go
USE SchemaTest
go
CREATE SCHEMA OtherSchema
go
CREATE TABLE dbo.tbl (a int NOT NULL PRIMARY KEY,
                  data varchar(80) NOT NULL)
CREATE TABLE OtherSchema.tbl (a int NOT NULL PRIMARY KEY,
                              data varchar(80) NOT NULL)
go
INSERT dbo.tbl (a, data)
   VALUES (1, 'Firenze')
INSERT OtherSchema.tbl (a, data)
   VALUES (1, 'Firenze')
go
UPDATE OtherSchema.tbl
SET    data = 'Venezia'
WHERE  a = 1
go
SELECT a, data FROM dbo.tbl
SELECT a, data FROM OtherSchema.tbl
go
USE tempdb
go
DROP DATABASE SchemaTest

Can you produce a script like the above the produces the result you are talking about?

Also, include the output of "SELECT @@version".

My guess, though, is that you have a trigger that cascades the update.

LiHongMSFT-4306 27,026 Reputation points

2024-09-23T02:28:41.2366667+00:00

Hi @ROHIT KUMAR

Does this issue only occur in specific tables and schemas, or does it exist in all schemas or tables?

Can you reproduce this issue in other schema with different users?
ROHIT KUMAR 0 Reputation points

2024-09-24T16:22:04.2033333+00:00

I'm updating the specific table while giving access to the specific user and its associated schema and getting the result as I'm working in a restricted offline environment so not able to check it with the latest SQL server as I observed the bug in SQL server 2019.
Erland Sommarskog 111.6K Reputation points MVP

2024-09-24T20:49:00.7666667+00:00
If you run

SELECT * FROM sys.triggers WHERE parent_id = object_id('yourschema.yourtable')

Do you see any rows? If so, check the code of these triggers.

I'm quite confident that this is not a bug in SQL Server, but something in your environment.

Share via

Microsoft SQL Server 2019 bug

Your answer