ALTER    procedure spi_BatchLog_deneme_3
@pSQL  VARCHAR(8000),
@pJobID  INTEGER,
@pComment  VARCHAR(8000),
@pDonusAciklama  VARCHAR(1000) out

-------------------------------------------------------------------------------------
-- Run the basic script
-- This could be inside a stored procedure
-------------------------------------------------------------------------------------
as

declare @rows integer

DECLARE @pDonusDegeri INTEGER 
DECLARE @Status VARCHAR(255)

DECLARE @oSQLServer INTEGER
DECLARE @oDatabase INTEGER
DECLARE @oQueryResults INTEGER
DECLARE @CurrentDB VARCHAR(255)
DECLARE @Return  INTEGER
DECLARE @SQL  VARCHAR(8000)
DECLARE @Source  VARCHAR(255)
DECLARE @Length  INTEGER
 
DECLARE @JobID  INTEGER
DECLARE @Error  INTEGER
DECLARE @ErrorMsg VARCHAR(255)
 
SET @JobID = @pJobID
SET @pDonusDegeri = 0
SET @Status = 'BASLADI'

EXEC spi_BatchLog @JobID, Null, @pComment, @Status
BEGIN TRAN    
-- Create the SQLServer object
EXEC @Error = sp_OACreate 'SQLDMO.SQLServer2', @oSQLServer OUT
IF @Error <> 0
 GOTO OA_Error
-- Set the login process to use NT Authentication
EXEC @Error = sp_OASetProperty @oSQLServer, 'LoginSecure', -1
IF @Error <> 0
 GOTO OA_Error
-- Connect to server using NT Authentication
EXEC @Error = sp_OAMethod @oSQLServer, 'Connect', NULL,  @@SERVERNAME
IF @Error <> 0
 GOTO OA_Error
-- Verify the connection
EXEC @Error = sp_OAMethod @oSQLServer, 'VerifyConnection', @Return OUTPUT
IF @Error <> 0
 GOTO OA_Error
IF @Return = 0
 GOTO OA_Error
 
-- Set the database object to be the current database
-- (We do this so we don't need to execute the SQL against the
--  SQLServer object, which would require explicit DB prefixing)
SELECT @CurrentDB = DB_NAME()
SELECT @CurrentDB = 'Databases("' + RTRIM(@CurrentDB) + '")'
EXEC @Error = sp_OAGetProperty @oSQLServer, @CurrentDB, @oDatabase OUT
IF @Error <> 0
 GOTO OA_Error
 
-- Build the SQL string
-- SET @SQL = 'EXECUTE spi_BatchLog ' + CONVERT(CHAR(12),@JobID) + ', ' + '''Message from Inside Transaction'''+ ', ' + ''' '''
SET @SQL = @pSQL

-- Execute the SQL (this will NOT be rolled back)
-- We could use ExecuteImmediate here, but it doesn't return a result
--  and we found while debugging that you really NEED that
SET @Length = LEN(@SQL)
SET @ErrorMsg = '' -- Must initialize the @ErrorMsg string
EXEC @Error = sp_OAMethod @oDatabase,  'ExecuteWithResultsAndMessages2', @oQueryResults OUTPUT, @Command = @SQL ,  @Length = @Length, @Messages = @ErrorMsg OUTPUT
IF @Error <> 0 
 GOTO OA_Error

EXEC @Error = sp_OAGetProperty @oQueryResults, 'Rows', @Rows OUT
IF @Error <> 0
 GOTO OA_Error
set @pComment = '[DKS: ' + cast(@rows as char(9)) + ']'

-- Destroy the objects
EXEC @Error = sp_OADestroy @oSQLServer
EXEC @Error = sp_OADestroy @oDatabase
EXEC @Error = sp_OADestroy @oQueryResults
 
GOTO DONE
-----------------------------------------------
OA_Error:
ROLLBACK TRAN
-- Get the error text
EXEC sp_OAGetErrorInfo @oSQLServer, @Source OUT, @ErrorMsg OUT
SELECT
 @ErrorMsg = CONVERT(CHAR(16), @Error) + ': ' + @ErrorMsg + ' (Source: ' + @Source + ')'

SET @pDonusDegeri = @Error
SET @pDonusAciklama = @ErrorMsg
 
-- Write the error to the log
SET @Status = 'HATALI_BITTI'
EXEC spi_BatchLog @JobID, @ErrorMsg, @pComment, @Status

EXEC @Error = sp_OADestroy @oSQLServer
EXEC @Error = sp_OADestroy @oDatabase
EXEC @Error = sp_OADestroy @oQueryResults

return @pDonusDegeri
 
DONE:
SET @Status = 'BASARILI_BITTI'
SET @pComment = @pComment + replace(@ErrorMsg, '[Microsoft][ODBC SQL Server Driver][SQL Server]', '')
SET @pComment = substring(@pComment, 1, charindex('[IKS]',@pComment) - 1)
-- SET @pComment = @pComment + @ErrorMsg
EXEC spi_BatchLog @JobID, NULL, @pComment, @Status
COMMIT
SET @pDonusAciklama = @pComment
return @pDonusDegeri

GO

quote:

---

I used queryresults, but it seems returning only recordsets if any and corresponding rowcount. For example, an insert statement does not return any thing.

---

quote:

---

I think Ouery Analyzer also can be developed using SQL-DMO.

---