Detecting Custom Objects Used by SQL Server 2000 objects

Hello,
I work as a software engineer and we have installed third party and custom
in-house dlls and ocx on our SQL Server 2000 server to support our business
applications. These dlls and ocx are referenced by the database
objects(jobs, storedprocedures). What I need to do is to generate a list of
all the dlls and ocx that our SQL Server reference, so that I can copy these
files out to our new future SQL Server box.
Does anyone here know how to do this? Is there a system table in one of the
system databases that keep track of the information I'm seeking?
Thanks you.
Scott Yu
"Scott Yu" <ScottYu@.discussions.microsoft.com> wrote in message
news:C95F6558-D80C-44F7-8E2D-65ED12C13721@.microsoft.com...
> Hello,
> I work as a software engineer and we have installed third party and custom
> in-house dlls and ocx on our SQL Server 2000 server to support our
> business
> applications. These dlls and ocx are referenced by the database
> objects(jobs, storedprocedures). What I need to do is to generate a list
> of
> all the dlls and ocx that our SQL Server reference, so that I can copy
> these
> files out to our new future SQL Server box.
> Does anyone here know how to do this? Is there a system table in one of
> the
> system databases that keep track of the information I'm seeking?
>
Unfortunately, you will have to dig through the text of the stored
procedures for calls to sp_oacreate.
set textsize 64000
exec sp_msforeachdb '
select text from syscomments
where text like ''%sp_oacreate%'''
This will give you the progid's of the com components used by sql server.
They must be copied to the new server and registered there, along with all
of their dependencies.
This is an exceedingly difficult thing for a DBA to get right. You may need
to track down the owners of each component to sucessfully move it to the new
server.
David
|||Hey David,
Thanks for the script. I pretty much did the same thing by scripting out
the storedprocedures and jobs, but that was before I learned of
sp_msforeachdb, thanks to you. The hardest part is trying to locate all the
dependent dlls. Another dll hell.
Thank you David.
Scott Yu
"David Browne" wrote:

> "Scott Yu" <ScottYu@.discussions.microsoft.com> wrote in message
> news:C95F6558-D80C-44F7-8E2D-65ED12C13721@.microsoft.com...
> Unfortunately, you will have to dig through the text of the stored
> procedures for calls to sp_oacreate.
> set textsize 64000
> exec sp_msforeachdb '
> select text from syscomments
> where text like ''%sp_oacreate%'''
> This will give you the progid's of the com components used by sql server.
> They must be copied to the new server and registered there, along with all
> of their dependencies.
> This is an exceedingly difficult thing for a DBA to get right. You may need
> to track down the owners of each component to sucessfully move it to the new
> server.
> David
>
>
|||One more thing to remember when you are searching using the LIKE operation:
"_" is a wildcard character meaning "any single character." When you want
the literal "_", you'll want to use brackets like so:
SELECT [name]
FROM syscomments
WHERE [text] LIKE '%sp[_]oacreate%"
Sincerely,
Anthony Thomas
"Scott Yu" wrote:
[vbcol=seagreen]
> Hey David,
> Thanks for the script. I pretty much did the same thing by scripting out
> the storedprocedures and jobs, but that was before I learned of
> sp_msforeachdb, thanks to you. The hardest part is trying to locate all the
> dependent dlls. Another dll hell.
> Thank you David.
> Scott Yu
> "David Browne" wrote:
|||Thank you very much Anthony. What is the difference between "%" and "_" in
TL SQL?
Scott
"AnthonyThomas" wrote:
[vbcol=seagreen]
> One more thing to remember when you are searching using the LIKE operation:
> "_" is a wildcard character meaning "any single character." When you want
> the literal "_", you'll want to use brackets like so:
> SELECT [name]
> FROM syscomments
> WHERE [text] LIKE '%sp[_]oacreate%"
> Sincerely,
>
> Anthony Thomas
>
> "Scott Yu" wrote:
|||Never mind. After re-reading your reply, I understoodd the difference
between "%" and "_".
Thank you.
"AnthonyThomas" wrote:
[vbcol=seagreen]
> One more thing to remember when you are searching using the LIKE operation:
> "_" is a wildcard character meaning "any single character." When you want
> the literal "_", you'll want to use brackets like so:
> SELECT [name]
> FROM syscomments
> WHERE [text] LIKE '%sp[_]oacreate%"
> Sincerely,
>
> Anthony Thomas
>
> "Scott Yu" wrote:
sql