sp_helpdb 出現db owner錯誤

最近常常遇到這過情況,特此記錄一下.
用sp_helpdb 時出現了錯誤

Server: Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53
Cannot insert the value NULL into column 'owner',
table 'tempdb.dbo.#spdbdesc_..._000100443608';
column does not allow nulls. INSERT fails.
The statement has been terminated.

Excel:VBA 連接SQL SERVER拿取數據

以下將會介紹一下如何從SQL SERVER 的用SQL 或 Store procedure(有沒有#temp table) 拿出數據
I. 基本ADO連接
(關於User name/Password 或 Integrated 請看2.連接方法)
1. 到"工具"->"設定引用項目"
找出併打勾"Microsoft ActiveX Data Objects 2.8 Library" (2.7 也可)

Windows XP 已經預帶2.7版本(MDAC 2.7), 如果還是找不到, 可以到這個位置下載
2. 連接方法(connection)
引用好ADO後, 就可以在VBA使用ADO部件
以下是用User Name和password 連接數據庫的方法

Dim Conn As ADODB.Connection
Dim sConnect As String
Dim strSqlInstance As String
Dim strSqlDB As String
Dim strSqlUser As String
Dim strSqlPWD As String
strSqlInstance = "SERVER_NAME\INSTANCE" 'SQL SERVER實例
strSqlDB = "DATABASE NAME" '數據庫名稱
strSqlUser = "SA" 'SQL實例用戶名
strSqlPWD = "PASSWORD" 'SQL實例用戶密碼
sConnect = "PROVIDER=SQLOLEDB;"
sConnect = sConnect & "DATA SOURCE=" & strSqlInstance & ";INITIAL CATALOG=" & strSqlDB & ";"
sConnect = sConnect & " User ID=" & strSqlUser & ";Password=" & strSqlPWD & ";"
Set Conn = New ADODB.Connection
Conn.ConnectionString = sConnect
Conn.Open
…
Conn.Close

如果客戶機有設Active directory等, 可以用

sConnect = sConnect & " INTEGRATED SECURITY=sspi;"

來取代

sConnect = sConnect & " User ID=" & strSqlUser & ";Password=" & strSqlPWD & ";"

II. 拿出資料表(table)數據 (SQL SELECT)
把數據表 Information_Schema.tables 的數據
用sql “Select * From ..." 抽出 放入 工作頁 "DATA”

Sub subGetTableValues()
 
    Dim Conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim intColCounter As Integer
    Dim sConnect As String
 

    Dim strSqlInstance As String
    Dim strSqlDB As String
    Dim strSql As String
 

    strSqlInstance = "SERVER_INSTANCE"
    strSqlDB = "database"

    sConnect = "PROVIDER=SQLOLEDB;"
    sConnect = sConnect & "DATA SOURCE=" & strSqlInstance & ";INITIAL CATALOG=" & strSqlDB & ";"
    sConnect = sConnect & " INTEGRATED SECURITY=sspi;"
   
    'Establish connection
    Set Conn = New ADODB.Connection
    strSql = "SELECT * FROM Information_Schema.tables" 'SQL在這裡定義,可加上 where group by... 
    With Conn
        .ConnectionString = sConnect
        .CursorLocation = adUseClient
        .Open
        .CommandTimeout = 0
        Set rs = .Execute(strSql)
    End With

    Worksheets("Data").Cells.Clear
    If rs.RecordCount > 0 Then '如果有數據
        '寫出欄名
        For intColCounter = 0 To rs.Fields.Count - 1
           Worksheets("Data").Range("A1").Offset(0, intColCounter) = rs.Fields(intColCounter).Name
        Next
        '寫出內容 
       Worksheets("Data").Range("A2").CopyFromRecordset rs
    Else
        MsgBox ("找不到數據")
    End If
 

    rs.Close
    Conn.Close
    Set rs = Nothing
    Set Conn = Nothing
 
End Sub

另一個方法可代替CopyFromRecordset,一格一格寫上.

Application.ScreenUpdating = False '先關掉顯示,寫好再補上,可以省"點"時間
Dim intRow as long, intCol as long
intRow = 0
rs.MoveFirst
Do While rs.EOF = False
    For intCol = 0 To rs.Fields.Count - 1
        Range("A2").Offset(intRow, intCol).Value = rs.Fields(intCol).Value
    Next intCol
    rs.MoveNext
    intRow = intRow + 1
Loop
Application.ScreenUpdating = True

III. 拿出儲存程序(stored procedure, sp)結果集
假設要呼叫的SP. SP有3組參數:
1. 字串輸入: astring_input
2. 數字輸入: aint_input
3. 可變數字輸入: aint_output
最後SP會把 3組參數用1個結果集回傳出來, 和把aint_output(3)設定為999
例如, exec usp_test_vba 'abc',10,100 會回傳以下的表(第13-16行)

string input	integer input	integer output
abc	10	999

另一方面, SP的RETURN會回傳777的值 (第18行)

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO
CREATE PROCEDURE usp_test_vba
@astring_input varchar(50),
@aint_input integer,
@aint_output integer output
AS

set @aint_output = 999

select 
@astring_input as [string input], 
@aint_input as [integer input], 
@aint_output as [integer output]

RETURN 777
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

呼叫方法:

Dim Conn As ADODB.Connection
Dim ADODBCmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim sConnect As String
Dim intArgOutput As Integer
Dim intReturn As Integer
intArgOutput = 30
spName = "usp_test_vba"
Dim strSqlInstance As String
Dim strSqlDB As String
strSqlInstance = "SQL SERVER INSTANCE"
strSqlDB = "DATABASE NAME"

sConnect = "PROVIDER=SQLOLEDB;"
sConnect = sConnect & "DATA SOURCE=" & strSqlInstance & ";INITIAL CATALOG=" & strSqlDB & ";"
sConnect = sConnect & " INTEGRATED SECURITY=sspi;"
Set Conn = New ADODB.Connection
Conn.ConnectionString = sConnect
Conn.Open
 
Set ADODBCmd = New ADODB.Command
Conn.CommandTimeout = 0
With ADODBCmd
    .ActiveConnection = Conn
    .CommandText = spName
    .CommandType = adCmdStoredProc
    .Parameters.Refresh
    .Parameters(1).Value = "abc" '放入第1個參數
    .Parameters(2).Value = 5 '放入第2個參數
    .Parameters(3).Value = intArgOutput '放入第3個參數
    Set rs = .Execute()
    intArgOutput = .Parameters(3).Value '回傳第3個參數,經SP處理後數值, 即999
    intReturn = .Parameters(0).Value '回傳SP RETURN出來的數值 即777
End With
Worksheets("Data").Cells.Clear
If Not (rs.EOF) Then
    For intColCounter = 0 To rs.Fields.Count - 1
        Worksheets("Data").Range("A1").Offset(0, intColCounter) = rs.Fields(intColCounter).Name
    Next
 
   Worksheets("Data").Range("A2").CopyFromRecordset rs
Else
    MsgBox ("沒有回傳表")
End If

rs.Close
Set rs = Nothing
Conn.Close
Set Conn = Nothing

IV. 拿出儲存程序(SP)結果集, SP上有臨時表 (#TEMP TABLE)的問題
假設要呼叫的SP,中間有使用臨時表.
用上III方法,運行時可能會出現這樣的錯誤:
"當物件關閉時, 不允許操作"

但是直接在QA/SSMS運行是沒有問題的.
這個問題的解決方法 ,可以在SP上要加上這一行

SET NOCOUNT ON

詳情可以參考這裡. (實際上我也沒有看詳情, 為了找出這個BUG,已經花了半天來Google...)
下一篇會說一下UPDATE/DELETE/INSERT等的
本篇最新更新 2012-07-02

MSSQL >2000: 列出數據庫及日誌檔位置

頭6句是用來分辨2000還是較新版本.

DECLARE
 @li_sql_version int
 set @li_sql_version = convert(int,left(convert(varchar(100), SERVERPROPERTY('productversion')), 
  charindex('.', convert(varchar(100), SERVERPROPERTY('productversion')),1)-1))

if @li_sql_version >8
 --SQL >2005
 exec('
 SELECT db.name as db_name, mf.name as logical_name, mf.physical_name AS current_file_location 
 FROM  
 sys.master_files mf inner join sys.databases db on mf.database_id = db.database_id
 ')
else
 exec('
 --SQL 2000
 select db.name as db_name, mf.name as logical_name, mf.filename as current_file_location 
 from 
 master.dbo.sysaltfiles mf inner join master.dbo.sysdatabases db
 on mf.dbid = db.dbid
 ')

SQL Displaying data/log files location of all databases

SQL:數據庫管理員(DBA)的未來十年

由Andy Warren在 SQL Server central 發表的短編,內容提到DBA未來十年的路向,
相當有啟發性. 以下是要點中譯.

• 備份，索引，排程，監控 和 性能調整 仍會然是核心技能
• 根據業務需求而不是個別功能,去選擇內聯還是雲端存儲
• 虛擬化是默認配置考慮，即使有高性能服務器
• RAID和IOPS等作用只是普通基礎， 存儲空間 及 系統回應時間 等會更費設計上的功夫
• 數據加密會更為重要
• 商務智能(BI)將會成為應有基本技能，如SSIS，SSAS，ETL..等
• 一個數據庫管理員要管理數據,比起之前,會超出一倍
• 未來入門級數據庫管理員技能要求,會比現在的更深入

其實無論是做什麼行業,也要考慮一下,未來自己的工作要求,會有什麼的變化.從而做好準備
以DBA來看,現在報紙上也很少會出現DBA的一個職位招聘.
DBA以成了IT管理員或開發人員的基礎.

How to:Excel VBA 使用SQL方法快速拿取數據 (設定篇)

以下將會簡單介紹一下為何要用SQL來拿取EXCEL數據,和如何做

用SQL而不用EXCEL上方法的條件

那些時候需要用SQL (ODBC/ADO) 方法?

S1. 利用SQL方法,可以簡單地選擇數據, 如用WHERE,GROUP BY,ORDER BY...等SQL的DML方法.節省開發時間
S2. 如要通過多頁連接數據, 用SQL的JOIN比寫VBA更為直接

S3. 進行較大型工作,如插入多行數據,所需運行時間比用上VBA為少

(對SQL沒太大認識的朋友:簡單來說,SQL在這裡的用處,是可以在同一時間,
對某多個表來做統計,或用欄位作條件,抽出所需要的列.)

那些時候要用EXCEL VBA?

E1. 原數據不是完全的表(TABLE)型式
E2. 需要對數據表進行格式設定(如轉顏色或conditional formating等)
E3. 程序和數據表,在同一EXCEL檔下.

當然,兩個方法可以渾合使用,如E3,用SQL方法的話,可以先把數據分到另一個檔(如條件許可)

VBA SQL拿取數據範例
以下將會一步一步介紹如何抽取數據

下載範例檔案

下載範例數據檔案

範例一:把數據用表列些抽出

1.開啟新Excel活頁簿

2.啟動巨集/宏編輯 (參考如何編輯巨集)

3.Data Access Library

3A)加入ADO兩個library.
如果library不存在,可在這裡下載MDAC新版 (目前是2.8版本)

3B)OFFICE 2007 Data Connectivity Components

如果沒有安裝Office2007, 建議下載2007 Office System Driver: Data Connectivity Components
這個驅動可連接EXCEL2007,EXCEL2003或之前版本.

3C)在VBA編輯環境, 進入"工具"->"參考"
打勾ADO兩個LIBRARY

4.跟隨第二步的方法,加上以下代碼然後運行

sub subXlsSqlQuery()

'定義部份
Dim cn As ADODB.Connection 'Excel連接
Dim rs As ADODB.Recordset '得來的結果,會放到立這個rs的地方.
Dim strDataSrcXlsPath As String '數據庫EXCEL位置
Dim strQuery As String '用作拿數據的SQL
Dim strStartlocation as String '定義得來數據,放到那一個位置.


'I) 以下建立數據庫連接
strDataSrcXlsPath = "D:\Connection_data_many2.xlsm" '定立數據庫EXCEL位置

Set cn = New ADODB.Connection 
With cn
'確定連接所用的Library
.Provider = "Microsoft.ACE.OLEDB.12.0" 

'把連接指向指定Excel
'xtended properties為excel的版本, 通常為12 或 8, 
'12為2007Excel檔(如XLSX,XLSM). 8可相容1997-2003等excel(XLS)
.ConnectionString = "Data Source=" & strDataSrcXlsPath & _
";Extended Properties=Excel 12.0;"

'開始連接,這個時候ACE會打開excel
.Open

End With

'II) 以下運行SQL和回傳數據
'拿出一行資料,所用SQL架構大約為
'SELECT ... FROM ... WHERE
'
'SELECT 上的 * 用作指明拿出所有表上的欄位
'TOP 50 是指抽出頭50列數據
'
'FROM 從DATASHEET工作表抽出
strQuery = "SELECT top 50 * FROM [DataSheet$]" 
Set rs = cn.Execute(strQuery) '執行SQL

'III) 把數據抄至所需的位置
strStartlocation ="A1"

'III-1 把欄位名稱一個一地拿出
'rs.Fields.Count : 欄位數數
'rs.Fields(n).Name : 欄名
Dim lngColCounter as long
For lngColCounter = 0 To rs.Fields.Count - 1 
range(strStartlocation).offset(0,lngColCounter) = rs.Fields(lngColCounter).Name 
Next lngColCounter
'III-2 把數據庫資料列表拿出
ActiveSheet.Range(strStartlocation).offset(1,0).CopyFromRecordset rs

'IV) 關掉/清理連接
cn.Close
Set cn = Nothing
Set rs = Nothing

end sub

5.按執行(F5),效果將如下圖

範例二:抽出一行數據及自動填表

首先設定好範例一上1/2/3三個環境

4.在Excel上加入一新工作表,名為 Query
依如下格式和位置,配置工作表
D3為用戶輸入交易代號條件

5.加入以下新模組和代碼
由於大部份代碼跟範例的類似, 所以只介紹不同的部份

Sub subRetrieveByField()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
strDataSrcXlsPath = "D:\Connection_data_many2.xlsm"

Dim strDataSrcXlsPath As String

'I) 以下建立數據庫連接
Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source=" & strDataSrcXlsPath & _
";Extended Properties=Excel 12.0;"
.Open

cn.CursorLocation = adUseClient

End With

'II) 以下運行SQL

'II-1) 準備SQL
Dim strQuery As String
Dim strQueryWorksheet As String
strQueryWorksheet = "Query" '數據查詢頁面位置

'拿出一行資料,所用SQL架構大約為
'SELECT ... FROM ... WHERE
'
'SELECT 用作指明拿出的欄位
'留意 (Order_Qty * Product_Price) as TotalAmt 是直接用SQL算出的欄位.
'TOP 1 為指定只拿一行數據
'也留意一下欄位大小寫,要跟EXCEL數據庫上的相同.
'
'FROM 從DATASHEET工作表抽出
'
'WHERE 抽出的行數條件,這裡用了 交易代號 Transaction_ID
strQuery = "SELECT top 1 " & _
" Transaction_Date, " & _
" Order_Qty, " & _
" Product_Name, " & _
" Product_Number, " & _
" Product_Color, " & _
" Product_Size, " & _
" (Order_Qty * Product_Price) as TotalAmt, " & _
" Product_Price " & _
" FROM [DataSheet$] " & _
" WHERE Transaction_ID = " & _
ThisWorkbook.Worksheets(strQueryWorksheet).Range("D3")

'II-2)執行SQL
Set rs = cn.Execute(strQuery)

'III) 把數據抄至所需的位置
Dim strStartlocation As String
Dim lngRowCounter As Long

If rs.RecordCount <= 0 Then 'rs.RecordCount 為有多少列數據,沒的話就顯示MSGBOX提示
MsgBox ("Transaction not found")
Else
With ThisWorkbook.Worksheets(strQueryWorksheet)
'把資料依名放入左方指明的格上
.Range("D6") = rs.Fields("Transaction_Date").Value
.Range("D7") = rs.Fields("Order_Qty").Value
.Range("D8") = rs.Fields("TotalAmt").Value

.Range("D11") = rs.Fields("Product_Number").Value
.Range("D12") = rs.Fields("Product_Name").Value
.Range("D13") = rs.Fields("Product_Color").Value
.Range("D14") = rs.Fields("Product_Size").Value
.Range("D15") = rs.Fields("Product_Price").Value
End With
End If

'IV) 關掉/清理連接
cn.Close
'rs.Close
Set cn = Nothing
Set rs = Nothing

End Sub

6.按運行後效果