Excel (VBA)

Excel VBAに関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(Windows 7 Professional : Excel 2010)
SQL Server上のストアドの処理件数を取得したい
投稿日時: 20/02/09 15:27:48
投稿者: sakamoto_

はじめまして。よろしくお願い致します。
 
==============================================
【背景】
==============================================
現在、SQL Server(職場:2008)上のデータをSSMS(SQL Server Management Studio)上から
ストアド(約200個)をトランザクションなしの手動処理(実行)している作業があります。
 
実行の処理順序を前後間違えるミスがしばしば発生し、
これを最終的には、VBA(ADO)を使って(トランザクション付きの)自動処理にしたいと考えています。
 
==============================================
【問題点(教えてほしいこと)】
==============================================
VBA上のストアドの実行方法は、ネットなどで実装できるようになりましたが、
一点だけ取得できないデータがあります。
それはSSMS(SQL Server Management Studio)上で発行される「メッセージ」です。
 
ストアドの手動作業では、処理件数を確認するために、
ここに表示される「〇〇件処理されました」をログとして(コピペ後、シート)管理しています。
 
==============================================
【その他】
==============================================
「メッセージ」を調べると、これはT-SQLの内容ではなく、SSMS上で発行される内容とのことでした。
 
対策として、既存のストアドの処理に「件数の戻り値」を入れることでも取得できると思いますので
200個のストアドにそれぞれにPRINTを入れることも考えましたが、
各ストアド内には、1〜10個のSQL(Insert、Update)があり、規則性の無い書き方になっています。
(一括の置換などが利用できず、それぞれ手動で修正する必要があり、ミスを懸念しています)
 
質問
-----------------------
教えていただきたいのは、既存のストアドを変更することなく、
ADOからのSSMS上でのメッセージ取得方法、あるいはストアドで実行した処理件数の取得方法です。
 
上記でも書いていますが、1つのストアドにはInsert、Updateが複数あるケースがほとんどで
それぞれの処理件数を取得したいと思っています。
ご存じの方がいらっしゃいましたら教えていただけないでしょうか?
 
以下のコードは、ストアドに「test」を作って試した結果です。
 

Sub ストアド実行()

    Dim Cn As ADODB.Connection
    Dim com As ADODB.Command
    
    Set Cn = New ADODB.Connection
    Cn.ConnectionString = "DSN=SQLS"
    Cn.Open
    
    Set com = New ADODB.Command
    Set com.ActiveConnection = Cn
    
    com.CommandType = adCmdStoredProc
    com.CommandText = "test"
    
    Dim objParameter As ADODB.Parameter
    
    Set objParameter = com.CreateParameter()
    objParameter.Name = "ReturnValue" 
    objParameter.Type = adInteger
    objParameter.Direction = adParamReturnValue
    objParameter.Value = Null
    
    com.Parameters.Append objParameter
    
    Dim cnt
    
    'ストアド実行
    com.Execute , cnt
        
    'ストアドの戻り値を取得
    Dim returnValue As Integer
    returnValue = com.Parameters("ReturnValue").Value
    
    Debug.Print "戻り値:" & returnValue, "件数" & CStr(cnt)

End Sub

 
実際のストアドではデータの戻り値はありませんが、色々やってもできないため「test」ではデータを戻す形で試行中です。※自宅内の検証でSQL Serverのバージョンが異なっています。
 
USE [AdventureWorksLT2017]
GO
/****** Object:  StoredProcedure [dbo].[test]    Script Date: 2020/02/09 14:03:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[test] AS
  SELECT TOP 10 * FROM [SalesLT].[Customer]

 
↓手動実行後の「メッセージ」欄に表示(これをVBA側で取得したい)
 
(10 行処理されました)

(1 行処理されました)

完了時刻: 2020-02-09T14:38:43.8972355+09:00

 
 
よろしくお願い致します。

回答
投稿日時: 20/02/09 22:39:24
投稿者: MMYS

VBAのやり方は知りませんが、
.Netの世界ならExecuteNonQueryメソッド で影響を受けた行数を取得出来ます。
https://docs.microsoft.com/ja-jp/dotnet/api/system.data.sqlclient.sqlcommand.executenonquery
 
私ならVBAにこだわらす、C#/VB.Net で処理します。
 

回答
投稿日時: 20/02/09 22:47:11
投稿者: takesi

https://www.projectgroup.info/tips/SQLServer/MSSQL_00000008.html
SQL Server に発行されたクエリをログに出力する方法
 
的外れでしたらすみません。

トピックに返信