SQLDMO Object Problem

[highlife]

I need to get the actual text of a SQL Server 2000 user defined function from a VB6 program, given the functions name.

I am using the SQL-DMO object elsewhere in my program but for some reason the "UserDefinedFunctions" collection is no longer used in SQL Server 7 and later.

I can get the names of the functions from the sysobjects table of the database, but how can I actually get the text?

There has to be some sort of system stored proc or some VB library to get access to this.

Can anyone help?

thank you!

[RobDog888]

Here is how to get the text of a user defined function.
Run in query analyzer for testing.

USE pubs
EXEC sp_helptext 'employee_insupd'

As long as you have permissions to execute the stored procedure.

Results...
Text
------------------------------------------------------------------------------------------
CREATE TRIGGER employee_insupd
ON employee
FOR insert, UPDATE
AS
--Get the range of level for this job type from the jobs table.
declare @min_lvl tinyint,
@max_lvl tinyint,
@emp_lvl tinyint,
@job_id smallint
select @min_lvl = min_lvl,
@max_lvl = max_lvl,
@emp_lvl = i.job_lvl,
@job_id = i.job_id
from employee e, jobs j, inserted i
where e.emp_id = i.emp_id AND i.job_id = j.job_id
IF (@job_id = 1) and (@emp_lvl <> 10)
begin
raiserror ('Job id 1 expects the default level of 10.',16,1)
ROLLBACK TRANSACTION
end
ELSE
IF NOT (@emp_lvl BETWEEN @min_lvl AND @max_lvl)
begin
raiserror ('The level for job_id:%d should be between %d and %d.',
16, 1, @job_id, @min_lvl, @max_lvl)
ROLLBACK TRANSACTION
end

[highlife]

perfect!

would you suggest I eliminate my SQLDMO useage and replace it with calls to stored procs via ADO? Mostly for speed but also for reliability.

it seems like I can accomplish all I need from this stored proc along with queries to the sysobjects table.

thoughts?

[techgnome]

Originally posted by highlife
I need to get the actual text of a SQL Server 2000 user defined function from a VB6 program, given the functions name.

I am using the SQL-DMO object elsewhere in my program but for some reason the "UserDefinedFunctions" collection is no longer used in SQL Server 7 and later.

I can get the names of the functions from the sysobjects table of the database, but how can I actually get the text?

There has to be some sort of system stored proc or some VB library to get access to this.

Can anyone help?

thank you!

Um... that's backwards... UDFs weren't implemented until SQL2000.... prior to that they aren't available.... (your statement seems to imply that in 6.5 they were there, but in 7 and 2K they aren't, which is incorrect).
What you need is the SQL DMO for SQL2000, and connect to a SQL2000 server.... if either of the components (DMO or the server itself), then you won't be able to get the UDFs.

[highlife]

if i have got SQL Server 2000 with SP3 installed, why would my SQLDMO object not include this collection?

[techgnome]

1) What ersion of SQLDMO?
2) What version is the SERVER you are querying?

It the answer to either of those is anything less than SQL2K, that's why you can't access it. The DMO that's on the CLIENT needs to have the 8.0 (SQL2K) objects in order to work. In addition, the SERVER itself needs to be SQL2K, otherwise, the DMO won't be able to retrieve the objects.
Also, some of the DMO objects were extended, so, to get to the newer SQL2K objects, you need to use the XYZ2 objects, ie SQLServer2, Database2, etc. in order to gain access to them. If you use the plain Database object, you won't see it (they did this so that the newer DMO can still access older SQL Servers....)

[highlife]

okay, I need to use the Database2 object instead of just Database, simple enough.

thanks for the answer, although I could have done without being talked down to.

[techgnome]

Originally posted by highlife
okay, I need to use the Database2 object instead of just Database, simple enough.

thanks for the answer, although I could have done without being talked down to.

Sorry, if it came across that way, I appologize, as that was never my intent.... it didn't ocurr to me to mention the DB2 thing until I was about to post that last message... Sorry about that....