ODBC Stored Procedures

  • David Benoit

    SSC-Dedicated

    Points: 34562

    We have an internal application which when viewing the events through Profiler, I see a bunch of ODBC Stored Procedures. Basic sequence is create proc as.... and then the execute then it deletes the same procedure. The server is showing a bunch of compiles and my guess is these ODBC stored procedures are causing that. The naming convention on these procedures is #odbc#userid. The developer that I have been working with on this found a MS article Q115907 in reference to this but, that article states that the user would have to have create proc permissions in order for this to occur. The user definitely does not have that permission.

    Has anyone seen this and if so, how do you fix this?

    I am working with the development staff to actually create stored procedures for the updates, inserts and deletes that are needed but, that will take some time for them to go through all their code so, I was looking for a quick fix. I do know that this will be the true fix.

    Ultimately, this is not overly problematic, just seems like bad practice to me.

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • sxnandwa

    Right there with Babe

    Points: 716

    I am not sure whether this has anything to do with your problem.In ODBC settings at the client side there is a provison to create stored procedures on Prepared statements.If that is checked then all prepared statements being fired from these clients would be creating a stored procedure as described in your query.

  • Andy Warren

    SSC Guru

    Points: 119675

    Does sound like prepared procs. Mostly went away with SQL 7. Been awhile, but I think the box in the dsn dialog is grayed out. Might take a look at the odbccmpt util that will put your connection in 6.5 compatibility mode.

    Andy

  • David Benoit

    SSC-Dedicated

    Points: 34562

    quote:


    Does sound like prepared procs. Mostly went away with SQL 7. Been awhile, but I think the box in the dsn dialog is grayed out. Might take a look at the odbccmpt util that will put your connection in 6.5 compatibility mode.

    Andy


    Andy, excuse my ignorance on this one (and apologize for not getting back to this sooner...other more pressing issues) but, what is the odbccmpt util? Thanks in advance.

    David

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • Andy Warren

    SSC Guru

    Points: 119675

    It's included with SQL. You can find the info in BOL or at this link on MSDN:

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/coprompt/cp_odbccmpt_6cv7.asp

    Andy

  • David Benoit

    SSC-Dedicated

    Points: 34562

    Just thought I would finally close this post out! We found that the issue is with the MDAC version that was used in the ODBC connection to the database. We were using 2.1 and once upgraded to a reasonable 2.6, all ODBC stored procs disappeared and processing improved. Thanks to all for the help.

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply