September 17, 2020 at 5:11 pm
I faced a weird problem this morning, when I want to see the estimated plan for below sp, it hangs the SSMS(V17.1).
EXEC TEST.dbo.spbalance_SchToPending
SP code below;
USE [TEST]
GO
CREATE PROCEDURE [dbo].[spbalance_SchToPending] AS
BEGIN
SET XACT_ABORT ON
declare @thisSTransactions_ID int
CREATE TABLE #SPendingTransactions
(
STransactions_ID int
)
insert into #SPendingTransactions ( STransactions_ID )
select STransactions_ID
from testtable ***this table has 258981534 rows. So big table.
where Trans_DateTime <= GetDate()
and Trans_TransState_ID = 8 --Scheduled
ORDER BY RecordCreated asc -- Oldest first
select @thisSTransactions_ID = min(STransactions_ID)
from #SPendingTransactions
while @thisSTransactions_ID is not null
begin
begin try
exec spTrans_PrepareTxnForNacha @STransactions_ID = @thisSTransactions_ID
end try
begin catch
ROLLBACK TRANSACTION
end catch
select @thisSTransactions_ID = min(STransactions_ID)
from #SPendingTransactions
where STransactions_ID > @thisSTransactions_ID
end
END
GO
September 17, 2020 at 6:00 pm
I would try with another higher version of SSMS and see if you still have the same issue. Just seems there are many weird bugs with different versions of SSMS so I always try with another version when I hit something that could be an SSMS issue.
Sue
September 17, 2020 at 6:04 pm
Alternately to Sue's approach, try matching your SSMS version to your SQL Server engine version. I know some things behave strangely when you have mismatched versions.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
September 18, 2020 at 6:54 pm
Tried a different version and still does not work.
September 18, 2020 at 6:56 pm
Do you have any 3rd party plugins installed (ApexSQL, RedGate, etc)?
I had a weird conflict with ApexSQL Model + ApexSQL Refactor + RedGate SQL Prompt. Had to remove the ApexSQL stuff to get SSMS to behave properly.
On top of that, is SSMS hanging or is it working? You may just need to be patient.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
September 23, 2020 at 8:54 pm
no third party softwares, the solution was I had to be patient. The screen froze however after 2 mins it back with the results.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply