use dba
go
create table dmotest (
    col1 varchar(8000)
)
go

declare
    @sql varchar(8000),
    @method  varchar(8000),
    @replicator int,
    @HRESULT int,
    @oSQLServer int,
    @oQueryResults int,
    @Length int,
    @ErrorMsg varchar(255),
    @len int
/******************************************************************************
*    Create the SQLServer object
******************************************************************************/
exec @HRESULT = sp_OACreate 'SQLDMO.SQLServer', @oSQLServer out
exec @HRESULT = sp_OASetProperty @oSQLServer, 'LoginSecure', -1
exec @HRESULT = sp_OAMethod @oSQLServer, 'Connect', NULL,  @@SERVERNAME
/******************************************************************************
*    Write to the table
******************************************************************************/
select
    @replicator = 1

while @replicator < = 300
begin
    select
        @sql = 'insert dba.dbo.dmotest values(''' + replicate('X',@replicator) + ''')',
        @replicator = @replicator + 1
    select
        @method = 'ExecuteImmediate("' + @sql + '")',
        @Length = len(@sql)
    exec @HRESULT = sp_OAMethod @oSQLServer, @method
--    exec @HRESULT = sp_OAMethod @oSQLServer, 'ExecuteImmediate', @Command = @sql, @ExecutionType = 0, @Length = @Length
--    exec @HRESULT = sp_OAMethod @oSQLServer, 'ExecuteWithResults', @oQueryResults OUTPUT, @Length = @Length, @Command = @SQL

    if @HRESULT <> 0
    begin
        exec sp_oageterrorinfo @oSQLServer, @sql OUTPUT, @method output
--        print @sql
--        print @method
    end
end
exec @HRESULT = sp_OADestroy @oSQLServer

select
    max(len(col1)) + len('insert dba.dbo.dmotest values(XX)') + len('executeimmediate("")')
from
    dmotest
go
drop table dmotest
go

• if the transaction will implicitly persist through the ADO connection and thus rollback the logging of the error when the original connect rolls back?


• of any other way to get outside the scope of the transaction?


• if my original deduction about the 255 limit is incorrect?


• if the implementation of sp_OAMethod is different in SQL 2k and allows more than 255 character, in which case I will just left(error,255) for now and tell the client, “reason number 47 why you should (pay me to) upgrade to 2K”?