sp_OAMethod methods

10 Jun 2005 11:06 AM

NeilDJones

Hi.

I am looking for a list of all methods available to sp_OAMethod. Is there
such a thing?

Cheers,

Neil

10 Jun 2005 12:14 PM

Mike Epprecht (SQL MVP)

Hi

It does not have any itself. It depends on what the COM object that you are
calling exposes.

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

Show quote

"NeilDJones" wrote:

> Hi.
>
> I am looking for a list of all methods available to sp_OAMethod. Is there
> such a thing?
>
> Cheers,
>
> Neil

10 Jun 2005 12:40 PM

NeilDJones

Which brings me to...

Where would I find a list of the methods available to any given COM object?

In particular: the code below writes a line to a file. I would like to write
out a whole table, and diong it a line at a time doesn't seem like such a hot
plan. Where could I find out whether there is a better method than WriteLine?
--------
DECLARE @i INT, @File VARCHAR(1000), @FS INT, @RC INT, @FileID INT, @Date
DATETIME

SET @File = 'c:\test6.csv'
SET @i = 1
EXEC @RC = sp_OACreate 'Scripting.FileSystemObject', @FS OUT

IF @RC <> 0
BEGIN
    PRINT 'Error: Creating the file system object'
END

-- Opens the file specified by the @File input parameter
EXEC @RC = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @File, 8, 1

-- Prints error if non 0 return code during sp_OAMethod OpenTextFile
execution
IF @RC <> 0
BEGIN
    PRINT 'Error: Opening the specified text file'
END

-- Printing start time in file
SET @Date = GETDATE()
EXEC @RC = sp_OAMethod @FileID, 'WriteLine', Null, @Date

-- Prints error if non 0 return code during sp_OAMethod WriteLine execution
IF @RC <> 0
    BEGIN
        PRINT 'Error: Writing string data to file'
    END

WHILE @i <= 100000
BEGIN
   -- Appends the string value line to the file specified by the @File input
parameter
    EXEC @RC = sp_OAMethod @FileID, 'WriteLine', Null , @i

   -- Prints error if non 0 return code during sp_OAMethod WriteLine execution
    IF @RC <> 0
        BEGIN
            PRINT 'Error: Writing string data to file'
        END

    SET @i = @i + 1
END

-- Printing completion time in file
SET @Date = GETDATE()
EXEC @RC = sp_OAMethod @FileID, 'WriteLine', Null, @Date
IF @RC <> 0
BEGIN
    PRINT 'Error: Writing string data to file'
END

EXECUTE @RC = sp_OADestroy @FileID
EXECUTE @RC = sp_OADestroy @FS

Show quote

"Mike Epprecht (SQL MVP)" wrote:

> Hi
>
> It does not have any itself. It depends on what the COM object that you are
> calling exposes.
>
> Regards
> --------------------------------
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
>
> MVP Program: http://www.microsoft.com/mvp
>
> Blog: http://www.msmvps.com/epprecht/
>
>
>
> "NeilDJones" wrote:
>
> > Hi.
> >
> > I am looking for a list of all methods available to sp_OAMethod. Is there
> > such a thing?
> >
> > Cheers,
> >
> > Neil

10 Jun 2005 12:55 PM

David Portas

> Where would I find a list of the methods available to any given COM object?

Either in the docs for that API or in a dev environment (such as
VisualStudio) that returns this info for you. SQL Server doesn't
provide a method to do it.

If you just want to export a table programmatically I would either use
DTS, BCP or OSQL (with the -o command line switch). That's a whole lot
simpler than coding it with the FileSystemObject.

--
David Portas
SQL Server MVP
--

10 Jun 2005 1:11 PM

NeilDJones

David,

The problem being that for various reasons we cannot use any commandline
commands, including bcp and osql.

Dammit.

Thanks anyway,

Neil

Show quote

"David Portas" wrote:

> > Where would I find a list of the methods available to any given COM object?
>
> Either in the docs for that API or in a dev environment (such as
> VisualStudio) that returns this info for you. SQL Server doesn't
> provide a method to do it.
>
> If you just want to export a table programmatically I would either use
> DTS, BCP or OSQL (with the -o command line switch). That's a whole lot
> simpler than coding it with the FileSystemObject.
>
> --
> David Portas
> SQL Server MVP
> --
>
>

10 Jun 2005 4:43 PM

Mike Epprecht (SQL MVP)

Look on MSDN for WSH (Windows Scripting Host) help.

"Scripting." forms part of VBS and WSH.

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: m***@epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

Show quote

"NeilDJones" <NeilDJo***@discussions.microsoft.com> wrote in message
news:294D4E12-034B-41B4-8C35-B3843246E9C3@microsoft.com...
> Which brings me to...
>
> Where would I find a list of the methods available to any given COM
> object?
>
> In particular: the code below writes a line to a file. I would like to
> write
> out a whole table, and diong it a line at a time doesn't seem like such a
> hot
> plan. Where could I find out whether there is a better method than
> WriteLine?
> --------
> DECLARE @i INT, @File VARCHAR(1000), @FS INT, @RC INT, @FileID INT, @Date
> DATETIME
>
> SET @File = 'c:\test6.csv'
> SET @i = 1
> EXEC @RC = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
>
> IF @RC <> 0
> BEGIN
> PRINT 'Error: Creating the file system object'
> END
>
> -- Opens the file specified by the @File input parameter
> EXEC @RC = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @File, 8, 1
>
> -- Prints error if non 0 return code during sp_OAMethod OpenTextFile
> execution
> IF @RC <> 0
> BEGIN
> PRINT 'Error: Opening the specified text file'
> END
>
> -- Printing start time in file
> SET @Date = GETDATE()
> EXEC @RC = sp_OAMethod @FileID, 'WriteLine', Null, @Date
>
> -- Prints error if non 0 return code during sp_OAMethod WriteLine
> execution
> IF @RC <> 0
> BEGIN
> PRINT 'Error: Writing string data to file'
> END
>
> WHILE @i <= 100000
> BEGIN
> -- Appends the string value line to the file specified by the @File
> input
> parameter
> EXEC @RC = sp_OAMethod @FileID, 'WriteLine', Null , @i
>
> -- Prints error if non 0 return code during sp_OAMethod WriteLine
> execution
> IF @RC <> 0
> BEGIN
> PRINT 'Error: Writing string data to file'
> END
>
> SET @i = @i + 1
> END
>
> -- Printing completion time in file
> SET @Date = GETDATE()
> EXEC @RC = sp_OAMethod @FileID, 'WriteLine', Null, @Date
> IF @RC <> 0
> BEGIN
> PRINT 'Error: Writing string data to file'
> END
>
> EXECUTE @RC = sp_OADestroy @FileID
> EXECUTE @RC = sp_OADestroy @FS
>
>
>
> "Mike Epprecht (SQL MVP)" wrote:
>
>> Hi
>>
>> It does not have any itself. It depends on what the COM object that you
>> are
>> calling exposes.
>>
>> Regards
>> --------------------------------
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>>
>> MVP Program: http://www.microsoft.com/mvp
>>
>> Blog: http://www.msmvps.com/epprecht/
>>
>>
>>
>> "NeilDJones" wrote:
>>
>> > Hi.
>> >
>> > I am looking for a list of all methods available to sp_OAMethod. Is
>> > there
>> > such a thing?
>> >
>> > Cheers,
>> >
>> > Neil