[SQL] 透過指令將xlsx匯入MSSQL

• 需安裝Microsoft.ACE.OLEDB.12.0：https://www.microsoft.com/zh-tw/download/details.aspx?id=13255#filelist
• 確認是否有安裝成功

• 需先設定安全配置，使用指令

 
sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
  
sp_configure 'Ad Hoc Distributed Queries', 1;  
GO  
RECONFIGURE;  
GO  

• 下指令匯入資料庫，『D:\Text.xlsx』為檔案位置，『[sheet1$]』為檔案中要匯入的sheet名稱

 
SELECT  *  
FROM    OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0',  
                       'Excel 12.0;HDR=Yes;IMEX=1;Database=D:\Test.xlsx')... [sheet1$] 
 

• 如果到這邊執行還有問題

消息 7399……鏈接伺服器 "(null)" 的 OLE DB 訪問介面 "Microsoft.ACE.OLEDB.12.0" 報錯。訪問被拒絕。消息 7301……無法從鏈接伺服器 "(null)" 的 OLE DB 訪問介面 "Microsoft.ACE.OLEDB.12.0" 獲取所需的介面("IID_IDBCreateCommand")。消息 7302……無法創建鏈接服務器 "(null)" 的 OLE DB 訪問接口 "MSDASC" 的實例

可能原因：SQL Server(MSSQLSERVER) 的登陸身份不合適

參考解決方案：將相關服務的登陸身份改為 local system

操作方法：
運行 services.msc，找到SQL Server(MSSQLSERVER)，右鍵屬性，登陸相關選項卡，本地系統帳戶。
或
開始---所有程序---MicrosoftSQL Server 2008 R2(或其他版本)---配置工具---SQLServer 配置管理器，找到SQL Server(MSSQLSERVER) ，右鍵屬性，登陸選項卡，內置帳戶改為Local system。
最後，若在MSSQL JOB中也要用到此接口，則SQL Server代理（MSSQLSERVER）這個服務也要做相同修改。

Ref.
http://blog.csdn.net/helloword4217/article/details/19000277

[SQL] 取T-SQL結果

DECLARE @sql nvarchar(1000),@i int,@max int,@getname nvarchar(1000) 
SELECT TOP 1 @max=id_num FROM [general].[dbo].[matchCrop] ORDER BY id_num desc;
SET @i=1;

SET @sql=N'SELECT @name=CROP_NAME FROM [general].[dbo].[matchCrop] WHERE id_num=@num'
EXEC sp_executesql @sql,N'@name nvarchar(100) OUTPUT,@num nvarchar(100)',@getname OUTPUT,@num=@i
SELECT @getname;--會是'SELECT @name=CROP_NAME FROM [general].[dbo].[matchCrop] WHERE id_num=@num'的結果

說明： 對應關係為，下面顏色部分，紅色對應紅色，綠色對應綠色，所以可以透過OUTPUT將＠sql的結果寫入getname中

注意1：＠getname是宣告在外面！

SQL Server 查資料庫中所有表的筆數

查資料庫中所有表的筆數 所以USE

  USE [EOICARD_old]                       --change DB
  SELECT TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,
  ROW_NUMBER() OVER (ORDER BY TABLE_SCHEMA,TABLE_NAME) [no]  
  into #tmp 
  FROM INFORMATION_SCHEMA.TABLES;                   --create temp table

  DECLARE @tb_name nvarchar(100), @num int, @total int, @i int,            --declare variable
    @tb_schema nvarchar(100), @sql nvarchar(100); 
  DECLARE @tble_total table (                    --declare output table
 schema_ nvarchar(100),
 name nvarchar(100),
 rowtotal int
  );  
                       
  SELECT @num=count(*) FROM #tmp;                   --set total table count
  SET @i=1;                         --set loop count

  while @i<=@num                       --loop start
  BEGIN
 SELECT @tb_schema=TABLE_SCHEMA FROM #tmp;                --get schema name
 SET @tb_name=(SELECT TABLE_NAME FROM #tmp WHERE [no]=@i);            --get table name
 SET @sql=N'SELECT '''+@tb_schema+''','''+@tb_name+''',count(*) FROM '+ @tb_schema+'.'+@tb_name;   --set sql statement
 INSERT INTO @tble_total(schema_,name,rowtotal) EXEC(@sql)            --temp table row count
 SET @total = (select top (1) rowtotal from @tble_total WHERE name=@tb_name);       --set row count
 SET @i=@i+1;                       --set next loop
  END                          --loop end

SELECT * FROM @tble_total;                     --output
DROP TABLE #tmp;                       --drop tmp table
 GO

結果會大致長這樣，有schema、table name、總筆數

SQL document看法

例如我們查『alter database file and filegroup options』

https://msdn.microsoft.com/en-us/library/bb522469.aspx

*建議使用IE開啟

簡易說明如下圖：

1. {  }：大刮號裡面的內容，是必寫，但大刮號本身不用寫
2. [  ]：中刮號裡面的內容，是可寫可不寫，但中刮號本身不用寫
3. <  >：大於、小於裡面的內容，是下面還會有其他補充資料，所以要繼續複製要下面的內容上來，但大於、小於本身不用寫
4. ｜：直線表示多選一，不能都選，但直線本身不用寫
5. (  )：小刮號表示可多選多，小刮號本身用寫
6. 小寫字：是我們要改的內容
7. 大寫字：SQL SERVER本身的保留字

因此，上述內容就變成

如果是要修改Sales database中的Sales_sec.ndf變更路徑到 M:\data\  下面

則變成

1. 先把要用的貼一貼，再刪

ALTER DATABASE database_name Sales 

{    

<add_or_modify_files>::=  --這邊是從下面複製上來的  

{  

ADD FILE <filespec> [ ,...n ]   

[ TO FILEGROUP { filegroup_name } ]  

 | ADD LOG FILE <filespec> [ ,...n ]   

 | REMOVE FILE logical_file_name  

 | MODIFY FILE <filespec>::=   --這邊也是從下面複製上來的 

(  

NAME = logical_file_name  

[ , NEWNAME = new_logical_name ]   

[ , FILENAME = {'os_file_name' | 'filestream_path' | 'memory_optimized_data_path' } ]  

[ , SIZE = size [ KB | MB | GB | TB ] ] 

[ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED }  

[ , FILEGROWTH = growth_increment [ KB | MB | GB | TB| % ] ] 

[ , OFFLINE ]   

)  

}
  | <add_or_modify_filegroups>  }  [;]    

• database_name=Sales

    2.慢慢修正內容，先改 add_or_modify_files

ALTER DATABASE Sales  

{    

ADD FILE <filespec> [ ,...n ]     

[ TO FILEGROUP { filegroup_name } ]    

  | ADD LOG FILE <filespec> [ ,...n ]       

| REMOVE FILE logical_file_name      

 | MODIFY FILE 

<filespec>::= (  NAME = logical_file_name 

[ , NEWNAME = new_logical_name ]  

[ , FILENAME = {'os_file_name' | 'filestream_path' | 'memory_optimized_data_path' } ]  

[ , SIZE = size [ KB | MB | GB | TB ] ] 

[ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ]    

[ , FILEGROWTH = growth_increment [ KB | MB | GB | TB| % ] ] 

[ , OFFLINE ]  

) 

}  

就變成
ALTER DATABASE SalesMODIFY FILE 

<filespec>::= (  NAME = logical_file_name 

[ , NEWNAME = new_logical_name ]  

[ , FILENAME = {'os_file_name' | 'filestream_path' | 'memory_optimized_data_path' } ]  

[ , SIZE = size [ KB | MB | GB | TB ] ] 

[ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ]    

[ , FILEGROWTH = growth_increment [ KB | MB | GB | TB| % ] ] 

[ , OFFLINE ]  

) 

   3.再來改filespec

ALTER DATABASE Sales 

MODIFY FILE 

<filespec>::=    

(    

NAME = logical_file_name Sales_sec    

[ , NEWNAME = new_logical_name ]     

[ , FILENAME = {'os_file_name' | 'filestream_path' | 'memory_optimized_data_path' } ]     

[ , SIZE = size [ KB | MB | GB | TB ] ]     

[ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ]     

[ , FILEGROWTH = growth_increment [ KB | MB | GB | TB| % ] ]     

[ , OFFLINE ]    

)   

變成 

ALTER DATABASE Sales 

MODIFY FILE (  

NAME =Sales_sec   

[ , FILENAME = {'os_file_name' | 'filestream_path' | 'memory_optimized_data_path' } ] 

                            )

   4.因為我們是要變更位置，所以只用到'os_file_name' 

ALTER DATABASE Sales 

MODIFY FILE (  

NAME =Sales_sec   

[ , FILENAME = {'os_file_name' | 'filestream_path' | 'memory_optimized_data_path' } ]   

                            )

• os_file_name＝M:\data\ Sales_sec.ndf

所以變成 

ALTER DATABASE Sales 

MODIFY FILE (  

NAME =Sales_sec            

 , FILENAME = 'M:\data\ Sales_sec.ndf' 

                           )

SQL Server 維護計畫錯誤

SQL server 維護計畫 啟用
需先下以下指令，以免會有『Agent XPs 元件已經由此伺服器的安全性組態關閉』等錯誤訊息

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'Agent XPs', 1;

GO

RECONFIGURE

GO

參考網站
https://msdn.microsoft.com/zh-tw/library/ms178127(v=sql.120).aspx

SQL Server匯入錯誤 連結伺服器 '(null)'

匯入資料時發現這種錯誤

連結伺服器 '(null)' 的 OLE DB ....

之後發現只要將資料屬性改成DT_WSTR就可以匯入成功了

原因不知道是不是因為我是用UTF8匯入的

SQL SERVER匯入excel 2013檔案

如果遇到  ‘Microsoft.ACE.OLEDB.12.0′ 提供者並未登錄於本機電腦上 ,

網路上大部分都說：如需要讀寫2007以上的格式則需於Server上安裝 Microsoft Access Database Engine 2010 可轉散發套件,

但有人安裝後還是一樣無法讀取, 建議先在server安裝2007 Office system 驅動程式：資料連線元件 即可：2007 Office system 驅動程式：資料連線元件

連結

server安裝 2007 Office system 驅動程式：資料連線元件 後, 程式就可以正常運作.

---

以上是參考網址1,因為查到不少網址2的相關內容，但是還是無法直接在MSSQL上面匯入excel2013的檔案,直到安裝了「2007 Office system 驅動程式：資料連線元件」才終於可以使用

---


參考網址
1.
網址1

2.
網址2

SQL Server 匯入文字 截斷

一直匯入失敗,明明看文字沒什麼問題
最後參考了這方法才成功

http://lanfar.pixnet.net/blog/post/40707319-sql-server%EF%BC%9A%E5%8C%AF%E5%85%A5%E8%B3%87%E6%96%99%E9%81%AD%E9%81%87%E2%80%9C%E6%96%87%E5%AD%97%E5%B7%B2%E6%88%AA%E6%96%B7%E2%80%9D%E9%8C%AF%E8%AA%A4


文字改成DT_NTEXT (如果有Unicode) ,DT_TEXT (沒Unidcode) 
數字改DT_UI2 

SQL 無法連接到(local)

公司的sql server是用windows認證

結果昨天我登入一直出現這樣的錯誤

我一直以為是公司資訊部門更改設定的關係

導致我沒辦法登入sql

還勞煩他們幫我看一下錯誤

結果來了一位高手三兩下就幫我設定好了

還說其實只是密碼錯誤

也說要是我開機登入的密碼有改的話,sql這邊認證也要重設密碼

(殊不知其實我根本沒改密碼阿...,

  唯一覺得有疑問的

  就是發生這問題前一天離開公司時

  電腦有自己更新)

原以為事情就這樣告一段落

結果今天我開機又遇到同樣問題

所以我就試著自己解看看

1. 我的電腦>右鍵 管理





2. 服務與應用程式>服務> 找到 SQL Server(MSSQLSERVER) 點兩下





3. 登入>帳號、確認密碼 重設，設完後按確定





4. 最重要步驟，就是再回到  SQL Server(MSSQLSERVER) 按右鍵 啟動



應該就可以順利登入SQL了

SQL 新增欄位 自動編碼

alter table [master].[dbo].[101acdList] add id_num int IDENTITY(1,1)

SQL找出「'」

  SELECT 
      [id_num]
      ,[LNDID]
      ,[LNDNO123]
      ,[LNDNO4]
      ,[LNDNO5]
      ,[LNDNO7]
      ,[LNDARA]
      ,[LNDUSEA]
      ,[LNDCHRA]
      ,[LNDAREA]
      ,[LNDOID]
      ,[LND721]
      ,[LND722]
      ,[LNDPAR1]
      ,[LNDPAR2]
      ,[LNDMEM]
      ,[LNDONM]
      ,[LNDHOW]
      ,[LNDTYPE]
      ,[EID]
      ,[RENT]
      ,[REST]
      ,[RENT_AREA]
      ,[RENT_WAREA]
      ,[CDEXP]
      ,[PSTNAM]
      ,[PSTADR]
      ,[PSTTEL]
      ,[PRMADR]
      ,[PSTTELM]
      
  FROM [master].[dbo].[101LAND]
  WHERE (PSTTEL LIKE '%''%')or
  (PSTTELM LIKE '%''%') or 
  (PSTADR LIKE '%''%') or 
  (PRMADR LIKE '%''%') 
  order by id_num asc

[sql server] 建立資料夾

1. 對著資料庫按右鍵





2. 點選新增資料庫





3. 資料庫名稱打上要命名的資料夾名字，點選確定





4. 就完成了

[sql server] 連sql server (非本機端架設)

●連線code

在 sql server 2000 中的語法是
String driverName = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
String dbURL = "jdbc:microsoft:sqlserver://ip:1433;DatabaseName=sample","user","pawd";

而 sql server 2005 則為
String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
String dbURL = "jdbc:sqlserver://ip:1433;DatabaseName=sample","user","pawd";

這樣子就大功告成，如果是要方便往後的系統沿用，可以包成公用物件.jar，程式需要連到sql資料庫時下 connection會比較簡便。

package test;

import java.sql.*;

public class connDB {
  private static String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
  private static String sourceURL = "jdbc:sqlserver://ip:1433;DatabaseName=name";
  private static String user = "test";
  private static String password = "(密碼)";
  private static Connection conn = null;


  public connDB() {
    try{
      Class.forName(driver);
      conn = DriverManager.getConnection(sourceURL,user,password);
    }
    catch(Exception ex)
    {
      System.out.println(ex.getMessage());
    }
  }
  public static Connection getConn()
  {
    return conn;
  }
}