＜SQL Server ストアドプロシージャ＞

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

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

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

・INSERT先は、EXCELの範囲名で指定した範囲の下側
・INSERT後、範囲は追加したレコードの数だけ拡張される
・2回目のINSERTを行うと、１回目の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
 ,[郵便番号１] = null
 ,[郵便番号２] = null
 ,[住所１] = null
 ,[住所２] = 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...業者番号
 ([業者番号]
 ,[業者名]
 ,[略称]
 ,[連想]
 ,[電話番号]
 ,[郵便番号１]
 ,[郵便番号２]
 ,[住所１]
 ,[住所２])
SELECT [業者番号]
 ,[業者名]
 ,[略称]
 ,[連想]
 ,[電話番号]
 ,[郵便番号１]
 ,[郵便番号２]
 ,[住所１]
 ,[住所２]
 FROM [S11_業者マスタ管理].[dbo].[t01業者台帳]

END