SQL Server に関する情報

Service Pack 関連情報

SQL Server 2000 Service Pack 3a

Windows XP SP2

SQL Server 2000 Reporting Services Service Pack 1

リファレンス/基礎知識

  • http://msdn.microsoft.com/library/
    [Enterprise Development]
     +[Windows Server System]
      +[Microsoft SQL Server]
       +[Microsoft SQL Server 2000]
        +[SDK Documentation]
         [Transact-SQL Reference]

ホワイトペーパー

SQL Server 7.0 から 2000 への主な変更点

Oracleからの移行

SP / OLE オートメーション

CDO

BCPコマンド

TIPS

SQL Server 7.0 のデータベースを他の場所に移動する方法

sp_detach_db/sp_attach_db を用いることで、データベースの
物理ファイル(MDF/LDF)を移動することが可能。

<例>
/* データベースを切り離す */
sp_detach_db 'db_cx'
go
/* データベースをアタッチする */
sp_attach_db 'db_cx', 
@filename1 = 'H:\Data\MSSQL7\Data\db_cx.mdf', 
@filename2 = 'H:\Data\MSSQL7\Data\db_cx_log.LDF'
go

DTS 関連情報

Reporting Service 関連情報

SQL Server 2000 Reporting Services

MSDE 関連情報

MDAC 関連情報

ADO 関連情報

ADO サンプル

SQL 自動生成

  • レコードセットから INSERT SQL を自動生成するサンプル。
    Option Explicit
    
    Const g_strPROVIDER = "SQLOLEDB"
    Const g_strSERVER = "(local)"
    Const g_strDATABASE = "Northwind"
    Const g_strUID = "sa"
    Const g_strPWD = "********" ' Please input password
    Const g_strTable = "Employees"
    
    ' DataTypeEnum
    Const adEmpty = 0
    Const adSmallInt = 2
    Const adInteger = 3
    Const adSingle = 4
    Const adDouble = 5
    Const adCurrency = 6
    Const adDate = 7
    Const adBSTR = 8
    Const adIDispatch = 9
    Const adError = 10
    Const adBoolean = 11
    Const adVariant = 12
    Const adIUnknown = 13
    Const adDecimal = 14
    Const adTinyInt = 16
    Const adUnsignedTinyInt = 17
    Const adUnsignedSmallInt = 18
    Const adUnsignedInt = 19
    Const adBigInt = 20
    Const adUnsignedBigInt = 21
    Const adFileTime = 64
    Const adGUID = 72
    Const adBinary = 128
    Const adChar = 129
    Const adWChar = 130
    Const adNumeric = 131
    Const adUserDefined = 132
    Const adDBDate = 133
    Const adDBTime = 134
    Const adDBTimeStamp = 135
    Const adChapter = 136
    Const adPropVariant = 138
    Const adVarNumeric = 139
    Const adVarChar = 200
    Const adLongVarChar = 201
    Const adVarWChar = 202
    Const adLongVarWChar = 203
    Const adVarBinary = 204
    Const adLongVarBinary = 205
    
    ' FieldAttributeEnum
    Const adFldCacheDeferred = &h1000
    Const adFldFixed = &h10
    Const adFldIsChapter = &h2000
    Const adFldIsCollection = &h40000
    Const adFldIsDefaultStream = &h20000
    Const adFldIsNullable = &h20
    Const adFldIsRowURL = &h10000
    Const adFldLong = &h80
    Const adFldMayBeNull = &h40
    Const adFldMayDefer = &h2
    Const adFldNegativeScale = &h4000
    Const adFldRowID = &h100
    Const adFldRowVersion = &h200
    Const adFldUnknownUpdatable = &h8
    Const adFldUnspecified = &hFFFFFFFF
    Const adFldUpdatable = &h4
    
    Main
    
    Sub Main()
    
        Dim cn
        Dim rs
        Set cn = CreateObject("ADODB.Connection")
        Dim strConnection
        strConnection = "PROVIDER=" & g_strPROVIDER _
            & ";SERVER=" & g_strSERVER _
            & ";DATABASE=" & g_strDATABASE _
            & ";UID=" & g_strUID _
            & ";PWD=" & g_strPWD
        cn.Open strConnection
        Dim strTableName
        Dim strSQL
        strTableName = g_strTable
        strSQL = "select * from " & strTableName
        Set rs = cn.Execute( strSQL )
        Dim fld
        Dim i
        Dim nFields
        Dim strInsertSQL
        Dim strFields
        Dim strValues
        
        nFields = rs.Fields.Count
        For i = 0 To nFields - 1
           If i < nFields - 1 Then
              strFields = strFields & rs.Fields(i).Name & "," & vbCrLf
           Else
              strFields = strFields & rs.Fields(i).Name
           End If
        Next
        
        While Not rs.Bof And Not rs.Eof
           For i = 0 To nFields - 1
              If i < nFields - 1 Then
                  strValues = strValues & GetRecordItem(rs(i)) & "," & vbCrLf
              Else
                  strValues = strValues & GetRecordItem(rs(i))
              End If
           Next
           strInsertSQL = "INSERT INTO " & strTableName & vbCrLf _
           & "(" & vbCrLf _
           & strFields & vbCrLf _
           & ")" & vbCrLf _
           & "VALUES" & vbCrLf _
           & "( " & vbCrLf _
           & strValues & vbCrLf _
           & ")"
           WScript.Echo strInsertSQL
           strInsertSQL = ""
           strValues = ""
           rs.MoveNext
        Wend
    End Sub
    
    Function GetRecordItem( ByVal fld )
       Dim strQuotation
       Dim strItem
       strQuotation = ""
       Select Case fld.Type
         ' Numeric
         Case _
            adSmallInt, adInteger, adSingle, adDouble, adCurrency, _
            adBoolean, adTinyInt, adUnsignedTinyInt, adUnsignedSmallInt, _
            adUnsignedInt, adBigInt, adUnsignedBigInt, adNumeric
            strQuotation = ""
         ' String
         Case _
            adBSTR, adVariant, adChar, adWChar, adVarChar, _
            adLongVarChar, adVarWChar, adLongVarWChar
            strQuotation = "'"
         ' Empty
         Case _
            adEmpty
            strQuotation = ""
         ' Other
         Case Else
            strQuotation = "'"
       End Select
    
       If fld.Type = Empty Then
          strItem = fld.Value
       ElseIf fld.Type = adLongVarBinary Then
          strItem = "NULL"
       Else
          strItem = strQuotation & fld.Value & strQuotation
       End If
       
       GetRecordItem = strItem
    
    End Function

ADO.NET 関連情報

ADOからの移行

その他

クエリーツール(フリーソフト)

関連リンク

お薦めサイト

BLOG

ML

海外サイト

あまり役に立たないかも。


トップ   編集 凍結 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS
Last-modified: 2010-02-27 (土) 14:20:02 (2848d)