#odbc....... Stored Procedures

[Brian Kipp]

What purpose do stored procedures beginning with #odbc(username)...........
serve?

Thanks

Brian Kipp
Federal Reserve Bank of Chicago

[yo...@ppg.com]

We've guessed that they are part of Crystal Reports getting kicked off
before it gets a chance to do cleanup.

I haven't been able to prove it.

___________________________________________________________
//)| |
/ / | "If I hear the phrase 'everything is an object' once more,|
_( (_ | I think I will scream." Ex-SmallTalk Programmer |
(((\ \>|_/()_______________________________________________________|
(\\\\ \_/ /
\ / Michael Yocca
\ _/ Data Analyst and President, PGH SQL Server Users Group
/ / PPG Industries, Inc., Pittsburgh, PA http://www.ppg.com
/___/ mailto:yo...@ppg.com (work) mailto:myo...@sgi.net (home)
http://users.sgi.net/~myocca/sql/pghssug (Pgh SQL Server User Group)
http://users.sgi.net/~myocca/sql/sp (stored procedures freeware)

[Karl Costenbader]

These tables are created by the SQL Server ODBC driver to handle stored
procedures. You should be able to turn this off by unchecking the
"Generate stored procedure for prepared statements" item in the Options
section of the ODBC setup dialog.

From the "Programming ODBC For SQL Server" manual:

"First, a temporary stored procedure is created from the statement,
because stored procedures are an efficient way to execute a statement
more than once. The procedure is named #odbc#useridentifier, where user
is up to 6 characters of the username and identifier is up to 8 digits
and identifies the statement."

--
Karl Costenbader, President
Competent Consulting
Sacramento, CA
EMail: ka...@competent.com
Home Page: http://www.competent.com

[yo...@ppg.com]

Thanks.

Any suggestions for what the procedure would look like?
Cursor with a where type = "P" and name like ("odbc%")
and then exec ("drop procedure "+@procname)

right ???

-my
----------
From: Karl Costenbader
To: yo...@ppg.com
Subject: Re: #odbc....... Stored Procedures
Date: Friday, August 30, 1996 8:03AM

I think the maintenance task is the way to go. I have found that
allowing ODBC to build the stored procedures results in enough of a
performance gain to justify leaving the box checked.

yo...@ppg.com wrote:
>
> Karl,
>
> Thank you for this information. Several postings have been made
> regarding this issue in the past couple of months and you are the
> first one to address the issue.
>
> This checkbox was easy to find. For the sake of "efficiency" as
> mentioned in the quote, are we better off having a maintenance
> task that removes these procedures when the SQL Server is recycled
> for orphaned procedures or is the performance difference small
> enough that we should uncheck this box as a standard procedure?
>
> -my

[yo...@ppg.com]

Karl,

Thank you for this information. Several postings have been made
regarding this issue in the past couple of months and you are the
first one to address the issue.

This checkbox was easy to find. For the sake of "efficiency" as
mentioned in the quote, are we better off having a maintenance
task that removes these procedures when the SQL Server is recycled
for orphaned procedures or is the performance difference small
enough that we should uncheck this box as a standard procedure?

-my

[yo...@ppg.com]

[yo...@ppg.com]

[Karl Costenbader]

I would suggest a cursor that uses the following select statement:

SELECT name FROM tempdb..sysobjects
WHERE name LIKE '#odbc#%'
AND type = "P"

yo...@ppg.com wrote:
>
> Thanks.
>
> Any suggestions for what the procedure would look like?
> Cursor with a where type = "P" and name like ("odbc%")
> and then exec ("drop procedure "+@procname)
>
> right ???
>
> -my
> ----------
> From: Karl Costenbader
> To: yo...@ppg.com
> Subject: Re: #odbc....... Stored Procedures
> Date: Friday, August 30, 1996 8:03AM
>
> I think the maintenance task is the way to go. I have found that
> allowing ODBC to build the stored procedures results in enough of a
> performance gain to justify leaving the box checked.
>
> yo...@ppg.com wrote:
> >

> > Karl,
> >
> > Thank you for this information. Several postings have been made
> > regarding this issue in the past couple of months and you are the
> > first one to address the issue.
> >
> > This checkbox was easy to find. For the sake of "efficiency" as
> > mentioned in the quote, are we better off having a maintenance
> > task that removes these procedures when the SQL Server is recycled
> > for orphaned procedures or is the performance difference small
> > enough that we should uncheck this box as a standard procedure?
> >
> > -my
>

[Neil Pike]

Michael,

They're created by ODBC connections generically, not just Crystal. But yes, if
the app doesn't shut down cleanly, then they won't get cleared up.

Check out Kb article Q151536

Neil Pike (neil...@natwestuk.co.uk)
Protech Computing Ltd (MS Solution Provider)
Using Virtual Access 3.50 build 147 (32-bit) on NT 4.0