raterus
asked on
Cursor causing memory leaks?
Hello,
I have a co-worker that is anti-microsoft, and he's been sending out emails complaining that his stored procedures are causing memory leaks in SQL Server.
I looked at all the stored procedures, and they all have me a bit worried...
Look below, here is what is being done, notice the cursor is being declared/exec using an exec() statement, which worries me. Is this a bad practice? Is this bad generally. Can someone point me to something that says this is an awful way of doing things?
--cursor below, likely syntactically incorrect because I removed company related items
SET @execstr = '
declare rs cursor for
SELECT blah blah blah
FROM OPENROWSET(''MSDASQL'',''D SN='+ @DSN +''',''select * from some_other_db''')'
exec(@execstr)
open rs
fetch next from rs
into
@blah,
@blah2
--usual cursor iteration stuff
close rs
deallocate rs
I have a co-worker that is anti-microsoft, and he's been sending out emails complaining that his stored procedures are causing memory leaks in SQL Server.
I looked at all the stored procedures, and they all have me a bit worried...
Look below, here is what is being done, notice the cursor is being declared/exec using an exec() statement, which worries me. Is this a bad practice? Is this bad generally. Can someone point me to something that says this is an awful way of doing things?
--cursor below, likely syntactically incorrect because I removed company related items
SET @execstr = '
declare rs cursor for
SELECT blah blah blah
FROM OPENROWSET(''MSDASQL'',''D
exec(@execstr)
open rs
fetch next from rs
into
@blah,
@blah2
--usual cursor iteration stuff
close rs
deallocate rs
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Bad code reflects the knowledge of the developer.
There are plenty of jobs developing in Java if he hates Microsoft so much.
There are plenty of jobs developing in Java if he hates Microsoft so much.
ASKER
Thank you everyone for your input.
I've drafted a nice email explaining why not to use sql cursors and sent it out. We'll see how it goes. I definitely think what he's done can be rewritten to use joins, now whether he'll a) admit there is a problem and b) rewrite, is up to him.
I've drafted a nice email explaining why not to use sql cursors and sent it out. We'll see how it goes. I definitely think what he's done can be rewritten to use joins, now whether he'll a) admit there is a problem and b) rewrite, is up to him.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Well he never wrote back, I figure he's either busy fixing the problem, or ignored me...Either way, I'm done with this question and I thank you all for your time and insight!
ASKER
This is another co-worker's work, and he's anti-microsoft all the way, he's fully content to blame Microsoft & SQL Server for memory leaks he's been experiencing, not his code. I'm frankly getting annoyed as his constant Microsoft/SQL Server bashing, so I'd like to give him real proof he's the one at fault. I'd looking for some good arguments to use to convince him of his error with the example I gave in my question.