SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Calling COM Objects From T-SQL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 08/08/2000 :  23:44:56  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Dan (and Nathan, and a few others) writes: "I've heard/read that you can call a COM Object from within a stored proc. Could you explain how this is done?"

Yup. This is fairly straightforward; check it out.

Article Link.

bubber
Starting Member

1 Posts

Posted - 09/13/2000 :  13:25:44  Show Profile  Visit bubber's Homepage  Reply with Quote
Calling COM Objects From T-SQL

Yea, it's pretty easy.. The hardest part is finding it in the Books online if you don't know where to look....

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 11/14/2000 :  21:03:46  Show Profile  Reply with Quote
255 char limit to method calls

One limit to this is length of text you can send to an object. You have a limit of 255 characters. I found this out while trying to use the CDO Mail object from SQL Server.


When I did a
objmail.body="Blah, blah, blah, so on for greater than 255 characters.....", it truncate the message at 255 characters.


I haven't tried this with SQL 2000.


Go to Top of Page

blake
Starting Member

Canada
29 Posts

Posted - 06/05/2001 :  14:24:23  Show Profile  Visit blake's Homepage  Reply with Quote
Great article! It's helped me quite a bit. However, I've been racking my brain over how to call an 'Add' method for a collection.

eg: in ASP, you would write:

Set obj = Server.CreateObject("Company.AddressBook")
obj.Address.Add "1234 Main Street", "Description"
etc...

In a stored procedure, this has eluded me. I've managed to create the object, set/get properties, and call methods but when it comes to calling a method within a collection, I'm stumped. I've tried using sp_OASetProperty with the collection name, the value, and an index but this doesn't work as the collection holds objects rather than simple variants.

Any tips?
Go to Top of Page

blake
Starting Member

Canada
29 Posts

Posted - 06/05/2001 :  22:31:18  Show Profile  Visit blake's Homepage  Reply with Quote
I have solved the collection problem I was having. The relevant code for this can be found at:

http://www.pstruh.cz/help/usrmgr/sa89.htm

Look at the "Get Users Collection" part.

Aside from that, I've also run into the 255 character limit for method parameters and, so far, haven't found a way around that.
Go to Top of Page

blake
Starting Member

Canada
29 Posts

Posted - 06/06/2001 :  16:19:24  Show Profile  Visit blake's Homepage  Reply with Quote
About the 255 character limit... there are some excellent articles out there on workarounds to this... however, they are just workarounds:

http://www.google.com/search?q=255+character+limitation+SQL
Go to Top of Page

bishal
Starting Member

Nepal
1 Posts

Posted - 09/25/2001 :  07:20:44  Show Profile  Send bishal a Yahoo! Message  Reply with Quote
Check it Out .This is used For Sending Mail using CDONT Object

declare @OLEResult int,
@ServerID INT,
@UserName varchar(20),
@ToM varchar(20),
@Bod varchar(20)

select @UserName="Your Email"
select @ToM="Destination Email
select @Bod="Body Of Ur Mail

EXEC @OLEResult = sp_OACreate 'CDONTS.newmail', @ServerID OUT
if @OLEResult<>0
BEGIN
PRINT'Error Occured on Creating Object'
RETURN
END

EXEC @OLEResult = sp_OASetProperty @ServerID,'From', @UserName
IF @OLEResult<>0
BEGIN
PRINT 'Error Occured On Setting From Property'
RETURN
END

EXEC @OLEResult = sp_OASetProperty @ServerID,'To', @ToM
if @OLEResult<>0
begin
print 'Error Occured on Setting To Property'
return
end


EXEC @OLEResult = sp_OASetProperty @ServerID,'Body', @Bod
if @OLEResult<>0
begin
print 'Error Occured on Setting the Body'
return
end

EXEC @OLEResult = sp_OAMethod @ServerID,'Send'

if @OLEResult<>0
begin
print 'Error Occured On Calling Send Methods'
return
end

EXEC @OLEResult = sp_OADestroy @ServerID


Go to Top of Page

qchau_98
Starting Member

6 Posts

Posted - 07/25/2002 :  11:50:32  Show Profile  Reply with Quote
SQL Sever 2000 over write the limition of 255 charater in the COM object.
Go to Top of Page

mtrein
Starting Member

Brazil
4 Posts

Posted - 02/03/2005 :  13:01:49  Show Profile  Reply with Quote
Hi, I have a dll written in C. Can I call its methods and fiddle with its attributes from the sql code? How do I do that, can anyone explain? I am totally newb to this, so I need as much detail as possible.

Thanks in advance,

Marcelo
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000