1アプリでMDBの作成→SQLServerから更新できない。

解決


ひぃ  2008-07-14 19:13:00  No: 144933

開発環境は、VB2008+SQLServer2005+Access2005です。

2〜3日自分なりにやったり、検索等でも調べてみたのですが、
どうしても出来ないので質問させて下さい。

ユーザ要望でSQLServerのデータをMDBに定期的に落とす処理を作成しています。
MDBは処理月毎に新規作成をするようにしています。

テーブル作成まで完了したMDBに対し、SQLServerのデータをINSERTするのですが、
レコード取得→ループでINSERTでは時間が掛かり過ぎます(10万件で5分程ですが「待たせ過ぎ」だそうです)
そこで、一括でINSERTするようにOPENROWSETを利用する事にしました。

ですが、テーブル作成完了後、
System.Runtime.InteropServices.Marshal.FinalReleaseComObject
や、オブジェクト=Nothingをしてもうまく開放されていないようで、
INSERTしようとしても、「現在、他のユーザーが〜」のメッセージが表示され追加出来ません。
原因は開放してもldbファイルがまだ残っているからだとは思うのですが、
全てのオブジェクトを開放してもldbファイルが残っています。
(アプリが終了するとldbは無くなります)

もう+1アプリ作成すればやりたい事は実現出来そうですが、
出来れば1アプリで完結させたいです。
何か良い方法はないものでしょうか。
何でもかまいませんのでヒントや解決案を頂ければと思います。

以下のようなコードです。(長いですごめんなさい)

Public Function Set_MDBCreate(ByVal iCnt As Integer) As Boolean

    '' プロシージャ返り値セット(False)
    Set_MDBCreate = False

    '' 変数宣言部
    Dim objCat As ADOX.Catalog              ' カタログ
    Dim objTable As ADOX.Table              ' テーブル
    Dim strParam As String                  ' データベースパラメータ
    Dim strShoriYM As String
    Dim strParamSV As String
    Dim strWkMDB As String

    ' SQL Server のテーブルを MDBファイル内にエクスポートする
    strShoriYM = dtmStart.AddMonths(-1).ToString("yyyyMM")

    strWkMDB = gstrDirName + "\" + gstrMDBName + strDBNM + "_" + strShoriYM + ".mdb"

    strParam = "Provider=Microsoft.Jet.OLEDB.4.0;" + _
               "Data Source=" + strWkMDB & ";" + _
               "Jet OLEDB:Engine Type=5;"

    strParamSV = "Server              = mtintradev;" + _
                 "Initial Catalog     = MEISAI_CHECK_" + strDBNM + ";" + _
                 "User Id             = sa;" + _
                 "Password            = 2939"

    Dim conSV As New SqlConnection
    Dim sqlCom As New SqlCommand
    Dim sqlRec As SqlDataReader

    Dim acApp As Access.Application = New Access.Application
    acApp.Visible = False
    Dim tdfNew As dao.TableDef

    Try

        'ADOXオブジェクトを作成
        objCat = New ADOX.Catalog

        '' ローカル接続
        '' フォルダ存在確認
        If Not (Directory.Exists(gstrDirName)) Then
            ''' フォルダが存在しない場合はフォルダを作成する
            Directory.CreateDirectory(gstrDirName)
        End If

        ''' MDBファイル存在確認
        If Not (File.Exists(strWkMDB)) Then
            '''' 存在しない場合MDBファイル作成
            objCat.Create(strParam)
        Else
            '''' 存在する場合は削除してから作成する
            File.Delete(strWkMDB)
            objCat.Create(strParam)
        End If

        ''' サーバ接続開始
        conSV = New SqlConnection(strParamSV)
        conSV.Open()
        sqlCom.Connection = conSV

        ''' テーブル定数分繰り返し処理
        For tblCnt = 0 To (gstrTableNM.Count - 1)

            ''' 繰り返し処理で開放されている可能性があるので再接続する
            If objCat Is Nothing Then
                objCat = New ADOX.Catalog
                objCat.let_ActiveConnection(strParam)
            End If

            ''' フィールドを取得し、テーブルを作成する
            strSQL = " SELECT "
            strSQL += "   CAST(A.NAME AS VARCHAR(40)) AS FIELD_NAME, "
            strSQL += "   CAST(C.NAME AS VARCHAR(20)) AS FIELD_TYPE, "
            strSQL += "   A.LENGTH AS FIELD_SIZE, "
            strSQL += "   CASE WHEN E.COLID IS NULL THEN '0' ELSE '1' END AS PK_FLAG"
            strSQL += " FROM "
            strSQL += "   SYSCOLUMNS AS A "
            strSQL += "   INNER JOIN SYSOBJECTS AS B ON B.ID = A.ID "
            strSQL += "   INNER JOIN SYSTYPES AS C ON C.XUSERTYPE = A.XTYPE "
            strSQL += "   LEFT JOIN SYSINDEXES AS D ON D.ID = A.ID "
            strSQL += "       AND D.STATUS & 2048 <> 0 "
            strSQL += "   LEFT JOIN SYSINDEXKEYS AS E ON E.ID = D.ID "
            strSQL += "       AND E.INDID = D.INDID "
            strSQL += "       AND E.COLID = A.COLID "
            strSQL += " WHERE "
            strSQL += "   B.NAME = '" & gstrTableNM(tblCnt) & "' "
            strSQL += " ORDER BY "
            strSQL += "   A.NUMBER, "
            strSQL += "   A.COLID"

            sqlCom.CommandText = strSQL
            sqlRec = sqlCom.ExecuteReader()

            '''' テーブルを作り直す
            Dim PrimaryKeys() As String = {""}
            Dim KeyCnt As Integer = 0
            objTable = New ADOX.Table
            With objTable
                .Name = gstrTableNM(tblCnt)
                For Each rec In sqlRec
                    Select Case rec("FIELD_TYPE")
                        Case "nvarchar"
                            .Columns.Append(rec("FIELD_NAME"), ADOX.DataTypeEnum.adWChar, rec("FIELD_SIZE"))
                        Case "float"
                            .Columns.Append(rec("FIELD_NAME"), ADOX.DataTypeEnum.adDouble)
                        Case "int"
                            .Columns.Append(rec("FIELD_NAME"), ADOX.DataTypeEnum.adInteger)
                        Case "smallint"
                            .Columns.Append(rec("FIELD_NAME"), ADOX.DataTypeEnum.adInteger)
                        Case "datetime"
                            .Columns.Append(rec("FIELD_NAME"), ADOX.DataTypeEnum.adDate)
                        Case Else
                            .Columns.Append(rec("FIELD_NAME"), ADOX.DataTypeEnum.adWChar, rec("FIELD_SIZE"))
                    End Select
                    If rec("PK_FLAG") = "1" Then
                        ReDim Preserve PrimaryKeys(KeyCnt)
                        PrimaryKeys(KeyCnt) = rec("FIELD_NAME")
                        KeyCnt += 1
                    End If
                Next
                sqlRec.Close()
            End With
            objCat.Tables.Append(objTable)

            '''' 主キーの設定(設定されている場合のみ)
            If PrimaryKeys.Count > 0 Then
                Dim MyKey As New ADOX.Key
                With MyKey
                    .Name = gstrTableNM(tblCnt) & "_Key"
                    .Type = ADOX.KeyTypeEnum.adKeyPrimary
                    For i = 0 To (PrimaryKeys.Count - 1)
                        .Columns.Append(PrimaryKeys(i))
                    Next
                End With
                objCat.Tables(gstrTableNM(tblCnt)).Keys.Append(MyKey)
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(MyKey)
            End If

            '''' オブジェクトを開放
            System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objTable)
            System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objCat)

            '''' データのエクスポート
            strSQL = "INSERT INTO "
            strSQL += "OPENROWSET('Microsoft.Jet.OLEDB.4.0', "
            strSQL += "   '" & strWkMDB & "';'admin';'', " & gstrTableNM(tblCnt) & ")    "
            strSQL += "SELECT *  FROM " & gstrTableNM(tblCnt)

            sqlCom.CommandText = strSQL

            sqlCom.ExecuteNonQuery()
        Next
        conSV.Close()

        '''' オブジェクトを開放
        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objCat)
        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objTable)
        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(sqlCom)
        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(sqlRec)
        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(conSV)

        '' プロシージャ返り値セット(True)
        Set_MDBCreate = True
        Exit Function

    Catch ex As Exception
        Input = "*バッチ処理情報 Err   更新 処理番号:" & intIDENTITY.ToString & " Err:" & ex.Message
        Console.WriteLine(Input)
        Exit Function

    End Try

End Function


魔界の仮面弁士  2008-07-14 19:28:09  No: 144934

そのような用途には、SSIS (SQL Server 2005 Integration Services) を
使った方が、高速かつ簡単に取得できるのでは無いでしょうか。

あるいは、昔ながらの DTS (SQL Server データ変換サービス) を使うとか。

http://msdn.microsoft.com/ja-jp/library/ms141026.aspx
http://www.atmarkit.co.jp/fdb/rensai/sqls05try01/sqls05try01_1.html


ひぃ  2008-07-14 19:46:05  No: 144935

魔界の仮面弁士様、ご返答ありがとうございます。

お教え頂いたページを少し拝見致しました。
このような方法もあるとは全く知りませんでした;

このページによると、新しくSSISのルールをVB上で作成して、
それを実行する。という事も出来そうですね。

DTSサービスもVSから作成出来るようになっているんですね、、、
.NETは今まで殆どと言っていいほどやっていなかったので、
そういう事が出来る?という疑問も持ちませんでした。
(DTSはサーバーで作成するものだ。という頭しかなかったので;)
とても勉強になりました。

私がやろうとしていた方法ももう少しやってみたいとは思いますが、
魔界の仮面弁士様が提案して頂いた方法も試したいと思います。
ありがとうございました。


※返信する前に利用規約をご確認ください。




  


  このエントリーをはてなブックマークに追加