「この ISAM では、リンク テーブル内のデータを削除することはできません。」回避方法

<SQL Server ストアドプロシージャ>

リンクサーバーを使い、EXCELファイルへ毎回最新データを上書きしたい時、前回のデータを削除する必要があります。
その際にDELETEコマンドを使用すると「この ISAM では、リンク テーブル内のデータを削除することはできません。」というエラーになります。
調べてみると、なんとDELETEコマンドはサポートされいませんでした。

なんとか回避方法を見つけたので記録しておきます。

■EXCELファイルへの書き出しポイント

・INSERT先は、EXCELの範囲名で指定した範囲の下側
・INSERT後、範囲は追加したレコードの数だけ拡張される
・2回目のINSERTを行うと、1回目のINSERTしたデータの下側に追加される(範囲が拡張されたため)
・指定した範囲の下側にデータがあるとINSERT出来ない
・UPDATEは指定した範囲が対象
・DELETEはサポートされていない(エラーとなる)

■DELETE不可の回避方法

・UPDATEで指定範囲にnull値を書き込む
・範囲を初期化する。(項目名の位置)

■サンプル

ALTER PROCEDURE [dbo].[q04業者番号EXCEL書き出し]
AS
BEGIN

 -- 書き出しファイル名の設定
DECLARE @filename varchar(255)
SET @filename = '\\Landisk\xxxxxxxx\業者台帳.xls'

 -- リンクサーバーの登録有無確認
IF EXISTS(SELECT * from MyExcel...業者番号)
BEGIN
 print 'EXIST'
 EXEC sp_dropserver MyExcel
END

 -- リンクサーバーの登録
EXEC sp_addlinkedserver
 @server = 'MyExcel',
 @srvproduct = 'OLE DB Provider for Jet',
 @provider = 'Microsoft.Jet.OLEDB.4.0',
 @datasrc = @filename,
 @provstr = 'Excel 8.0'

 -- 書き出し済のレコードをクリアする
UPDATE MyExcel...業者番号
 SET [業者番号] = null
 ,[業者名] = null
 ,[略称] = null
 ,[連想] = null
 ,[電話番号] = null
 ,[郵便番号1] = null
 ,[郵便番号2] = null
 ,[住所1] = null
 ,[住所2] = null

 -- EXCEL範囲指定をリセットする
DECLARE @hr int
DECLARE @objXls int
DECLARE @workBooks int
DECLARE @workSheet int
DECLARE @strExec varchar(255)
DECLARE @return_int int

EXEC @hr = sp_OACreate 'Excel.Application', @objXls OUT
SET @strExec = 'Workbooks.Open("' + @filename + '")'
EXEC @hr = sp_OAGetProperty @objXls ,@strExec, @WorkBooks OUT
EXEC @hr = sp_OAGetProperty @WorkBooks, 'Worksheets(1)', @workSheet OUT

EXEC @hr = sp_OAMethod @WorkBooks, 'Names.Add', @return_int OUT, @Name='業者番号', @RefersToR1C1='=業者台帳!R2C1:R2C9'

EXEC @hr = sp_OAMethod @WorkBooks, 'Save'
EXEC @hr = sp_OAMethod @objXls, 'Application.Quit'
EXEC @hr = sp_OADestroy @objXls

 -- データをEXCELファイルに書き出す
INSERT INTO MyExcel...業者番号
 ([業者番号]
 ,[業者名]
 ,[略称]
 ,[連想]
 ,[電話番号]
 ,[郵便番号1]
 ,[郵便番号2]
 ,[住所1]
 ,[住所2])
SELECT [業者番号]
 ,[業者名]
 ,[略称]
 ,[連想]
 ,[電話番号]
 ,[郵便番号1]
 ,[郵便番号2]
 ,[住所1]
 ,[住所2]
 FROM [S11_業者マスタ管理].[dbo].[t01業者台帳]

END

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です