Professional Web Applications Themes

Creating Com Objects in Stored Procedure - Microsoft SQL / MS SQL Server

Hi, How do I create a com components instances in stored procedure. I am having one stored procedure which create a word doent, but it is giving me error. error create Word.Application Stored Proc: IF OBJECT_ID('spk_ExportToWord') IS NOT NULL DROP PROC spk_ExportToWord GO CREATE PROCEDURE spk_ExportToWord ( Text varchar(200) = null, filename varchar(200) = 'c:\ImportToWord.doc' ) AS DECLARE object int, Range int, Doents int, Doent int, hr int, result_str varchar(255) IF Text IS NULL BEGIN PRINT 'You should write text' RETURN END SET NOCOUNT ON EXEC hr = sp_OACreate 'Word.Application', object OUT IF hr <> 0 BEGIN PRINT 'error create ...

  1. #1

    Default Creating Com Objects in Stored Procedure

    Hi,
    How do I create a com components instances in stored procedure.
    I am having one stored procedure which create a word doent, but it is
    giving me error.

    error create Word.Application

    Stored Proc:


    IF OBJECT_ID('spk_ExportToWord') IS NOT NULL DROP PROC spk_ExportToWord
    GO

    CREATE PROCEDURE spk_ExportToWord (
    Text varchar(200) = null,
    filename varchar(200) = 'c:\ImportToWord.doc'
    )
    AS
    DECLARE object int,
    Range int,
    Doents int,
    Doent int,
    hr int,
    result_str varchar(255)

    IF Text IS NULL
    BEGIN
    PRINT 'You should write text'
    RETURN
    END

    SET NOCOUNT ON

    EXEC hr = sp_OACreate 'Word.Application', object OUT
    IF hr <> 0
    BEGIN
    PRINT 'error create Word.Application'
    RETURN
    END

    EXEC hr = sp_OAGetProperty object, 'Doents', Doents OUT
    IF hr <> 0
    BEGIN
    PRINT 'error create Doents'
    RETURN
    END

    EXEC hr = sp_OAMethod Doents, 'Add', Doent OUT
    IF hr <> 0
    BEGIN
    PRINT 'error with method Add'
    RETURN
    END

    EXEC hr = sp_OAGetProperty Doent, 'Range', Range OUT
    IF hr <> 0
    BEGIN
    PRINT 'error create Range'
    RETURN
    END

    EXEC hr = sp_OASetProperty Range, 'Text', Text
    IF hr <> 0
    BEGIN
    PRINT 'error set Text'
    RETURN
    END

    SELECT result_str = 'SaveAs("' + filename + '")'
    EXEC hr = sp_OAMethod Doent, result_str
    IF hr <> 0
    BEGIN
    PRINT 'error with method SaveAs'
    RETURN
    END

    EXEC hr = sp_OAMethod Doents, 'Close'
    IF hr <> 0
    BEGIN
    PRINT 'error with method Close'
    RETURN
    END

    EXEC hr = sp_OADestroy object
    IF hr <> 0
    BEGIN
    PRINT 'error destroy Word.Application'
    RETURN
    END
    GO


    EXEC spk_ExportToWord Text = 'Test example',
    filename = 'c:\ImportToWord.doc'







    TIA,
    Kishor Pise.
    Andheri(E), Mumbai 400 093
    Maharashtra, INDIA.



    Kishor Guest

  2. #2

    Default Re: Creating Com Objects in Stored Procedure

    Hi Kishor.

    There's not really enough information here to work with. Instead of simply
    printing out 'error create Word.Application', you should add extra error
    handling code to your stored procedure so that you get the full error
    message.

    To do this, you'd change:

    EXEC hr = sp_OACreate 'Word.Application', object OUT
    IF hr <> 0
    BEGIN
    PRINT 'error create Word.Application'
    RETURN
    END

    to include sp_OAGetErrorInfo :

    EXEC hr = sp_OACreate 'Word.Application', object OUT
    IF hr <> 0
    BEGIN
    PRINT 'error create Word.Application'
    EXEC sp_OAGetErrorInfo object, src OUT, desc OUT
    SELECT hr=convert(varbinary(4),hr), Source=src, Description=desc
    RETURN
    END

    There is more information on how to use COM / T-SQL error trapping in the
    SQL Server Books Online at:
    http://msdn.microsoft.com/library/en-us/acdata/ac_8_qd_14_2ktw.asp
    and
    http://msdn.microsoft.com/library/en-us/acdata/ac_8_qd_14_8k6m.asp

    HTH

    Regards,
    Greg Linwood
    SQL Server MVP

    "Kishor" <com> wrote in message
    news:phx.gbl... 


    Greg Guest

  3. #3

    Default Re: Creating Com Objects in Stored Procedure

    Hi,
    Still there is same error.
    Kishor
    "Greg Linwood" <com> wrote in message
    news:O1RgQZ$phx.gbl... 
    >
    >[/ref]


    Kishor Guest

  4. #4

    Default Re: Creating Com Objects in Stored Procedure

    Kishor - if you've implemented the error checking code properly you should
    get more information. If you can post your code with the call to
    sp_OAGetErrorInfo we could look over it again.

    Regards,
    Greg Linwood
    SQL Server MVP

    "Kishor" <com> wrote in message
    news:u6$phx.gbl... [/ref]
    simply [/ref]
    the [/ref][/ref]
    is [/ref][/ref]
    spk_ExportToWord 
    > >
    > >[/ref]
    >
    >[/ref]


    Greg Guest

  5. #5

    Default Re: Creating Com Objects in Stored Procedure

    Hi Dave.

    You are certainly on the right track with this, but even though most people
    only use SQL Server's OLE Automation services for simple COM activation,
    it's actually possible to configure COM to launch under an interactive
    identity. The simplest way to do this is to configure DCOM settings, but
    there are more complex design patterns that can be implemented as well, even
    from VB but also via more complex API coding.

    The biggest problem with using Word's OLE Automation / COM interface is that
    it is just so risky that Word will throw a dialog of some sort which will
    have no-where to go under SQL Server & hang the connection. This is to say
    nothing of Word multi-threading issues (think print services) either.

    When designing SQL Server / Word interop into applications in the past, I've
    used out of process wrappers from SQL Server, configured with DCOM settings
    to launch into the interactive session. This at least allows dialogs to be
    clicked on if need be on the server if necessary.

    Regards,
    Greg Linwood
    SQL Server MVP

    "Dave" <co.uk> wrote in message
    news:phx.gbl... 
    needs [/ref]
    > simply [/ref][/ref]
    error [/ref][/ref]
    Description=desc [/ref]
    > the [/ref][/ref]
    it [/ref]
    > spk_ExportToWord 
    > >
    > >[/ref]
    >
    >[/ref]


    Greg Guest

  6. #6

    Default Re: Creating Com Objects in Stored Procedure

    This may seem like a stupid question, but why would you EVER want to do
    something like this? This seems like one of Microsoft's added features that
    leads to bad design and brittle systems. My understanding is that a robust
    system maintains strong separation between the data layer and the client,
    often using some middleware. Allowing the invocation of an OLE object from
    directly within SQL Server just seems wrong headed, even if you can do it.
    The doentation says you can even invoke the object so that it is in
    process, sharing the SQL Server memory resources etc. OK. Let's do something
    that has the potential for totally ing up the server's memory.

    I must be niave, but to me a database server should be just that. Allowing a
    developer to punch holes in the wall to allow tighter integration with
    his/her application seems wacked.

    Just my opinion.

    Bob Castleman
    SuccessWare Software


    "Greg Linwood" <com> wrote in message
    news:phx.gbl... 
    people 
    even 
    that 
    I've 
    settings 
    > needs 
    > > simply [/ref]
    > error [/ref][/ref]
    error [/ref]
    > Description=desc [/ref][/ref]
    in [/ref]
    > it 
    > > spk_ExportToWord 
    > >
    > >[/ref]
    >
    >[/ref]


    Bob Guest

  7. #7

    Default Re: Creating Com Objects in Stored Procedure

    Hi Bob.

    You've made a few points & I'd have to say that I can see your point of
    view, but I'd also say that having the feature (sp_OACreate & it's cousins)
    and abusing it are very different things. I get the same sick feeling in my
    stomach when I see people using sp_OACreate in triggers underneath databases
    that are otherwise designed for high OLTP performance for example. But
    overall, I'm glad the feature's available because at the very least, if
    gives you architectural choices. If you choose to mis-use the feature, then
    you pay the price.

    A few more comments inline..

    "Bob Castleman" <nomailhere> wrote in message
    news:phx.gbl... 
    that 
    perhaps - but what about the case where you need to customise an existing
    app - ie you aren't in a position to re-design the whole app, but your
    business needs to get something done & the database is the easiest place to
    perform the task? Sometimes there are practical reasons to design system
    components into otherwise less than ideal places (like OLE in the DB)
     
    Don't forget that SQL Server is built largely upon OLEDB (a form of OLE).
    OLE is not necessarily inefficient.
     
    something 
    This issue is often overplayed. Writing components in VB that AV & crash the
    SQL Server process for example is not actually that easy to do. You've got
    to have virtually malicious intent to actually succeed in doing this.. &
    there are so many other (simpler) ways you can bring SQL Server to it's
    knees - eg poorly designing a layered architecture that requires too much
    communication accross the layers / depends too much on DB level IO etc..
     

    imho, your opinion's on the right track. Although having attempted to
    provide argumentative opinions to yours, I'd say I generally agree with you.
    OLE in the DB is usually a last choice for me. I'll design logic into the
    "best" layer of an architecture whenever I can, but sometimes I am relieved
    that it's there to use..

    Regards,
    Greg Linwood
    SQL Server MVP
     
    > people 
    > even 
    > that [/ref]
    will [/ref]
    say 
    > I've 
    > settings [/ref]
    be 
    > > needs [/ref][/ref]
    of 
    > > error [/ref]
    > error 
    > > Description=desc [/ref]
    > in [/ref][/ref]
    but 
    > >
    > >[/ref]
    >
    >[/ref]


    Greg Guest

  8. #8

    Default Re: Creating Com Objects in Stored Procedure

    I have worked at a couple of places where the policy is, "not only will we
    let you shoot yourself in the foot, we will sell you devices to improve
    your aim".

    As far as isolation one employer had a policy of "one application or
    service per machine". We installed a SQL server machine, a web server
    machine, an application server machine, etc. It's extreme but it works.


    --
    ATB

    Charles Kincaid
    Charles Guest

  9. #9

    Default Re: Creating Com Objects in Stored Procedure

    Mmmmm Lasagna! (c:

    Regards,
    Greg Linwood
    SQL Server MVP

    "Bob Castleman" <nomailhere> wrote in message
    news:phx.gbl... 
    are 
    > cousins) 
    > my 
    > databases 
    > then [/ref][/ref]
    do 
    > > that [/ref]
    > robust [/ref]
    > client, [/ref]
    existing 
    > to [/ref]
    > from [/ref]
    > it. [/ref]
    OLE). 
    > > something 
    > > This issue is often overplayed. Writing components in VB that AV & crash[/ref]
    > the [/ref]
    got [/ref]
    much [/ref]
    > Allowing 
    > > imho, your opinion's on the right track. Although having attempted to
    > > provide argumentative opinions to yours, I'd say I generally agree with[/ref]
    > you. [/ref]
    the 
    > relieved [/ref]
    > activation, [/ref][/ref]
    interactive [/ref]
    > but [/ref]
    > well, [/ref]
    > is 
    > > will [/ref][/ref]
    to [/ref][/ref]
    either. [/ref]
    > past, [/ref]
    > to [/ref]
    > it [/ref][/ref]
    Instead [/ref]
    > extra [/ref][/ref]
    full [/ref]
    > trapping [/ref]
    > http://msdn.microsoft.com/library/en-us/acdata/ac_8_qd_14_2ktw.asp [/ref]
    > http://msdn.microsoft.com/library/en-us/acdata/ac_8_qd_14_8k6m.asp [/ref]
    > procedure. [/ref][/ref]
    doent, [/ref]
    > OUT 
    > >
    > >[/ref]
    >
    >[/ref]


    Greg Guest

Similar Threads

  1. Stored Procedure Help
    By drudiggy in forum Coldfusion Database Access
    Replies: 11
    Last Post: November 29th, 08:44 PM
  2. Storing Objects/Arrays in Stored Objects
    By teflon000 webforumsuser@macromedia.com in forum Macromedia Flash Actionscript
    Replies: 0
    Last Post: January 24th, 07:11 PM
  3. SQL1034C error when creating stored procedure
    By Gianfranco in forum IBM DB2
    Replies: 2
    Last Post: October 8th, 07:17 PM
  4. Creating a trigger that calls a stored procedure
    By Elie in forum Oracle Server
    Replies: 2
    Last Post: December 29th, 06:13 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139