access2010 SQL Azureいってみようか -12-

前回のテーブル同期に使ったパススルークエリにストアドプロシージャを使ってみる。

ローカルのtimestamp最大値を引数として渡し、比較することで更新追加されたレコードを取得する目論見。

名前の付け方にいつも悩む。そしていつも後悔。

CREATE PROCEDURE [dbo].[proc_getrs_table01sync]
	@Param1 timestamp = 0x0000000000000000 
AS
BEGIN
	SET NOCOUNT ON;
	SELECT ID, F01, TS FROM dbo.table01 WHERE TS > @Param1
END

CREATE PROCEDURE (Transact-SQL)

Option Compare Database
Option Explicit

Sub SQLAzureToLocal()
    Dim dbs As DAO.Database, qdf As DAO.QueryDef
    Dim strTS As String, rsTo As DAO.Recordset, rsFrom As DAO.Recordset
On Error Resume Next
    strTS = TimestampToString(DMax("TS", "Local_table01"))
On Error GoTo ErrHnd
    Set dbs = CurrentDb
    Set qdf = dbs.CreateQueryDef("")
    qdf.Connect = strCn
    qdf.SQL = "exec [dbo].[proc_getrs_table01sync] " & strTS
    qdf.ReturnsRecords = True
    Set rsFrom = qdf.OpenRecordset
    Set rsTo = dbs.OpenRecordset("Local_table01", dbOpenTable)
    rsTo.Index = "PrimaryKey"
    Do Until rsFrom.EOF
        rsTo.Seek "=", rsFrom("ID")
        If rsTo.NoMatch Then
            rsTo.AddNew
                rsTo("ID") = rsFrom("ID")
                rsTo("F01") = rsFrom("F01")
                rsTo("TS") = rsFrom("TS")
            rsTo.Update
        Else
            rsTo.Edit
                rsTo("F01") = rsFrom("F01")
                rsTo("TS") = rsFrom("TS")
            rsTo.Update
        End If
        rsFrom.MoveNext
    Loop
Done:
    Exit Sub
ErrHnd:
    If Err.Number = 3146 Then
        Dim oErr As Error, msg As String
        For Each oErr In DBEngine.Errors
            msg = msg & oErr.Number & vbCrLf & _
                        oErr.Source & vbCrLf & _
                        oErr.Description & vbCrLf
        Next
        MsgBox msg
    Else
        MsgBox Err.Number & "/" & Error
    End If
    Resume Done
End Sub

Function TimestampToString(ts() As Byte) As String
    Dim Buffer As String, i As Integer
    Buffer = "0x"
    For i = 1 To 8
        Buffer = Buffer & Right("00" & Hex(AscB(MidB(ts, i, 1))), 2)
    Next i
    TimestampToString = Buffer
End Function

実行してみる。

ストアドプロシージャ実行権限がないということ。なのでユーザにEXEC権限を与えてやらねば。

--ユーザ：testdb01user01の実行を許可
GRANT EXECUTE ON dbo.proc_getrs_table01sync TO testdb01user01

接続に使用しているユーザは、ロールdb_datareaderとdb_datawriterのメンバーだから権限がなかった。動くことが確認できればよいのでユーザに権限を与えてしまっている。データベースロールを作成し、それに権限を設定してユーザをメンバーにするってことをしていない。

--ユーザ：testdb01user01の実行を拒否
DENY EXECUTE ON dbo.proc_getrs_table01sync TO testdb01user01

--ユーザ：testdb01user01の実行権限設定取消
REVOKE EXECUTE ON dbo.proc_getrs_table01sync TO testdb01user01

GRANT (Transact-SQL)
DENY (Transact-SQL)
REVOKE (Transact-SQL)