and on this case I would nearly bet that the 2 procs involved on the deadlock are called in a loop with an outside transaction

process 1
exec dbo.sproc_ValidateUser

...

• This reply was modified 2 years, 6 months ago by frederico_fonseca.

on my case the issues we had was not on the data retrieval but on rendering and processing - which is done after all data has been loaded.

the OP didn't...

had lots of those as well - even after increasing the timeout significantly they were getting similar errors after 90 min - as we were only using SSRS to generate...

have a look at this example - based on something I do for dynamic reports

drop table if exists #data

 CREATE TABLE #data 
( RecordID int identity(1, 1) --...

yes you will need to either change the code and have multiple conditions or use dynamic sql to build the output if your final output should have each "string" placed...

PARSENAME won't work for more than 4 "parts".

and instead of trying to invent the wheel again look at already made functions

https://www.sqlservercentral.com/articles/reaping-the-benefits-of-the-window-functions-in-t-sql-2 -- use this one as it is faster than...

unless changed the deploy process and/or schema compare will disable all triggers (both DML and database) - make sure you change that setting.

I would say that your left outer join to PersuKetsi results in you having more rows being processed than on the query you get the count(*) from.

change your first query...

are you sure its the designer? I've never seen it to make that.

but the script generation is another story - the defaults remove lots of things, and this is a...

Thanks for that.   We use synonyms to refer to other databases so hopefully that shouldn't cause too many issues.

I'll give it a go with a test database...

Thanks for that.   We use synonyms to refer to other databases so hopefully that shouldn't cause too many issues.

I'll give it a go with a test database and see...

create an extended event session and log all the code being issued by the pc where you execute that app - that will give you full list of sql statements...

search for the content instead of the object id

SELECT * 
FROM sys.sql_modules
WHERE definition like '%usp_drop_temp_ingenix_codes%' 
and see what you get.

I would also recreate the SP just in...

most of the code looks like someone used to Oracle that was moved to work with SQL Server - in Oracle world majority of things are case sensitive - and...

check if someone had DENY'ed create tables in TEMPDB- or even deny'ed other permissions that could cause the issue.