Cursor causing memory leaks?


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'',''DSN='+ @DSN +''',''select * from some_other_db''')'


open rs

fetch next from rs

--usual cursor iteration stuff

close rs
deallocate rs
LVL 33
Who is Participating?
Scott PletcherConnect With a Mentor Senior DBACommented:
While you may not want to hear this, there do appear to be known issues with memory leaks and cursors on remote/linked servers.  So, unless you are on SP4, these problems would continue.  Odd, it seems almost as if he were trying to create memory leaks, so that he could send out nasty e-mails ... .

Reply -- with very careful wording -- essentially that he needs to speak with the leadership of your company if he wants to quit using MS products.  Otherwise, he needs to use what is there to maximum *benefit*, not just search for detriments.

knightEknightConnect With a Mentor Commented:
Try eliminating the cursor altogether by using a table variable instead:
raterusAuthor Commented:
ahem..let me clarify

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.

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

ExpertAdminConnect With a Mentor Commented:
Just for the record, that is a weird way to code. Cursors are bad enough (and almost always a sign of bad database design), but to add that layer on top of it is really poor design.

I would code the query without using the exec and run them both with SQL Profiler to determine which one is hogging more resources. Ideally (as suggested), you will eliminate it althogether and use JOIN statements to replace it.

I suppose he thinks Oracle is the realm of the gods.

Bad code reflects the knowledge of the developer.

There are plenty of jobs developing in Java if he hates Microsoft so much.
raterusAuthor Commented:
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.
Atlanta_MikeConnect With a Mentor Commented:
Good Luck!! It's always hard to get someone to change their ways.
raterusAuthor Commented:
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!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.