ASP ストアドプロシージャを実行し、結果をcsvでダウンロード

何とも欲張りなタイトルになってしまった。
ASP から SQL Server のストアドプロシージャを実行して、その結果表示されるデータをcsvでダウンロードしよう

というのである。

まず、ストアドプロシージャの実行と、csvファイルのダウンロードは分けて説明する。

【ストアドプロシージャの実行】

＊ストアドプロシージャについて
まず、実行するストアドプロシージャがどういうものかというと、
顧客データベースから指定した都道府県の住所を持つレコードだけを抽出し、最後に、その抽出されたレコードを表示するというもの。
１レコードには、顧客ID、氏名、住所、TEL、Emailなどがあるり、複数レコードが最後に表示されるということであ

る。

＊ASP
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "PROVIDER=SQLOLEDB;DATA SOURCE=＜SQL Serverの名前＞;USER ID=＜DBユーザー名＞;PASSWORD=＜パスワー

ド＞;DATABASE=＜最初に接続するDB＞"

param1 = "20"
strSQL = "getting_user " & param1 & ""
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSQL, cn, 3

Do Until rs.EOF
strcsv = strcsv """"& rs(0) &""","""& rs(1) &""","""& rs(3) & """" &vbCrLf
rs.MoveNext
Loop
rs.Close

＊解説

・ストアドプロシージャが返す値を取得するには、rs(0)、rs(1)、rs(3)．．．とする。

・カンマ区切りだが、各列はダブルクォーテーション区切り(列中にカンマが含まれる場合を想定して)。

・レコードの最後に vbCrLf (改行) を入れる。

【csvファイルのダウンロード】

＊ASP
csv_file_name = type & date & num
Response.ContentType = "application/octet-stream"
Response.AddHeader "Content-Disposition", "attachment;filename=" &csv_file_name& ".csv"
Response.Write strcsv
Response.End

この２つを合体させるとこうなる。

【ストアドプロシージャを実行してcsvファイルをダウンロード】

csv_file_name = type & date & num
Response.ContentType = "application/octet-stream"
Response.AddHeader "Content-Disposition", "attachment;filename=" &csv_file_name& ".csv"
strcsv = """顧客ID"",""氏名"",""住所"""

Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "PROVIDER=SQLOLEDB;DATA SOURCE=＜SQL Serverの名前＞;USER ID=＜DBユーザー名＞;PASSWORD=＜パスワー

ド＞;DATABASE=＜最初に接続するDB＞"

param1 = "20"
strSQL = "getting_user " & param1 & ""
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSQL, cn, 3

Do Until rs.EOF
strcsv = strcsv """"& rs(0) &""","""& rs(1) &""","""& rs(3) & """" &vbCrLf
rs.MoveNext
Loop
rs.Close

Response.Write strcsv
Response.End

＊注意点
・ストアドプロシージャの先頭に「SET NOCOUNT ON」を配置すること。
Transact-SQL ステートメントで処理された行数を示すメッセージが結果の一部として返されないようにする。
じゃないと、ASPにてエラーになり、「オブジェクトが閉じている場合は、操作は許可されません。」と表示される。

・このaspファイルに、HTMLタグは不要。

・ストアドプロシージャがキャッシュされ、毎回同じデータが表示されてしまう。
IEの一時ファイル削除、ファイル名変更、WWWサービスの再起動、サーバー自体の再起動、IISサーバーのレジストリ HKEY_LOCAL_MAHCINE\\SYSTEM\CurrentControlSet\Services\Inetinfo\Parameters にDisableMemoryCache「1」を追加、などしたが解決されなかった。

結局、ストアドプロシージャの中で使う作業テーブルを「#」で始まる一時テーブルにしたら解決した。どちらにしても、複数の人が同時に使うことを想定したら、一時テーブルにすべきなので、これで良しとする。

と思ったが、どうやら原因は、パーマネントの作業テーブルの所有者を指定していなかったことが原因のようだ。WEBアプリが使うログインの名前でテーブルが作成されていたが、SELECTするときには、dbo所有のテーブルが使われていたのだろう。

ということで、ストアドプロシージャ内でテーブルを作成する場合は、所有者を指定すべきだということを改めて実感（久しぶりで忘れてた。）。