topic Re: Run a stored procedure that contains a cursor in Talend Studio

Run a stored procedure that contains a cursor

Rotha_HUOCH — Sat, 16 Nov 2024 01:17:55 GMT

Hello,

I am calling the community because I need your help to execute a stored procedure.

I am able to run a simple stored procedure that contains an insert, using the following components:

It works very well.

Now I want to run the stored procedure below:

DECLARE @v_spid INT

DECLARE c_Users CURSOR

FAST_FORWARD FOR

SELECT SPID

FROM master..sysprocesses (NOLOCK)

WHERE spid>50

AND loginame <> 'DIR%'

AND program_name LIKE N'%Microsoft Dynamics NAV Classic client %'

AND status='sleeping'

AND DATEDIFF(mi,last_batch,GETDATE())>=25

AND spid <> @@spid

OPEN c_Users

FETCH NEXT FROM c_Users INTO @v_spid

WHILE (@@FETCH_STATUS=0)

BEGIN

PRINT 'Killing '+ CONVERT(VARCHAR,@v_spid)+'...'

EXEC('KILL '+ @v_spid)

FETCH NEXT FROM c_Users INTO @v_spid

END

CLOSE c_Users

DEALLOCATE c_Users

The purpose of this procedure is to disconnect users who are inactive for more than 30 minutes (in the erp).

It works great when I run it manually via ssms.

On the other hand, when I run it via Talend, I have no error message but it is obviously not executed since inactive users remain connected

Re: Run a stored procedure that contains a cursor

Rotha_HUOCH — Thu, 03 Jun 2021 05:58:31 GMT

wow, still no answer after months of waiting 😞

Could a nice super user help me or give me a link to a solution?

Re: Run a stored procedure that contains a cursor

gjeremy1617088143 — Thu, 03 Jun 2021 06:37:57 GMT

Hi, have you try a tDBRow with :

"EXEC kill_sessions;" ?

Send me Love and kudos

Re: Run a stored procedure that contains a cursor

Rotha_HUOCH — Thu, 03 Jun 2021 06:50:17 GMT

Let me check

Re: Run a stored procedure that contains a cursor

Rotha_HUOCH — Thu, 03 Jun 2021 08:09:24 GMT

I don't think it's working because right after I execute le talend job, the users are still connected :

Re: Run a stored procedure that contains a cursor

pawe84 — Fri, 29 Jul 2022 14:29:22 GMT

Have the same issue. No one has a solution for it?