SQLServer レコードの取得

ＳＱＬの結果を ＣＳＶに出力する

読み込み用 SQL

lesson005.sql

select * from DimAccount

接続文字列

lesson004.con

DRIVER={SQL Server};SERVER=xxxxxx;DataBase=adventureworksdw;UID=yyyyyy;PWD=zzzzzz;

ＣＳＶ出力用スクリプト

lesson002.vbs

Option Explicit

Private fs
Set fs = CreateObject("Scripting.FileSystemObject")

'接続文字列 読み込み
Private tsCon
Set tsCon = fs.OpenTextFile(WScript.Arguments(0))

Private sCon
sCon = ""
If Not tsCon.AtEndOfStream Then
    sCon = tsCon.ReadLine
End If
tsCon.Close
Set tsCon = Nothing

'接続
Private con
Set con = CreateObject("ADODB.Connection")
With con
    .Provider         = "SQLOLEDB"
    .ConnectionString = sCon
    .Open
End With

'SQL 読み込み
Private tsSql
Set tsSql = fs.OpenTextFile(WScript.Arguments(1))

Private sSql
sSql = ""
Do Until tsSql.AtEndOfStream
    sSql = sSql & tsSql.ReadLine & vbNewLine
Loop
tsSql.Close
Set tsSql = Nothing

'結果を CSV に保存
Private tsCsv
Set tsCsv = fs.OpenTextFile(WScript.Arguments(2), 2, True)  '2 = ForWriting

Dim rs
Set rs = CreateObject("ADODB.Recordset")
With rs
    .ActiveConnection = con
    .CursorType       = 0 'adOpenForwardOnly
    .LockType         = 1 'adLockReadOnly
    .Source           = sSql
    .Open
End With

If Not rs.EOF Then
    Dim i
    For i = 0 To rs.Fields.Count - 1
        tsCsv.Write rs.Fields(i).Name & ","
    Next
    tsCsv.WriteLine ""

    Do Until rs.EOF
        For i = 0 To rs.Fields.Count - 1
            tsCsv.Write """" & rs.Fields(i).Value & ""","
        Next
        tsCsv.WriteLine ""

        rs.MoveNext
    Loop
End If
rs.Close

tsCsv.Close
Set tsCsv = Nothing
Set fs = Nothing

'切断
con.Close
Set con = Nothing

実行形式

C:\>cscript c:\study\vbscript\chapter006\lesson002.vbs  c:\study\vbscript\chapte

r006\lesson004.con c:\study\vbscript\chapter006\lesson005.sql c:\study\vbscript\

chapter006\lesson005.csv //nologo

出力結果

lesson005.csv

AccountKey,ParentAccountKey,AccountCodeAlternateKey,ParentAccountCodeAlternateKey,AccountDescription,AccountType,Operator,CustomMembers,ValueType,CustomMemberOptions,

"1","","1","","Balance Sheet","","~","","Currency","",

"2","1","10","1","Assets","Assets","+","","Currency","",

"3","2","110","10","Current Assets","Assets","+","","Currency","",

"4","3","1110","110","Cash","Assets","+","","Currency","",

"5","3","1120","110","Receivables","Assets","+","","Currency","",

"6","5","1130","1120","Trade Receivables","Assets","+","","Currency","",

"7","5","1140","1120","Other Receivables","Assets","+","","Currency","",

"8","3","1150","110","Allowance for Bad Debt","Assets","+","","Currency","",

"9","3","1160","110","Inventory","Assets","+","","Currency","",

"10","9","1162","1160","Raw Materials","Assets","+","","Currency","",