kekejann

2016年11月21日星期一

[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

2016年10月11日星期二

[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是宣告在外面！

2016年7月20日星期三

Gmail的附件檔案被封鎖

作者nodavirius (.....)

看板Google

標題[詢問] gmail的附件檔案被封鎖

時間Mon Dec 15 10:01:36 2014

最近在gmail信箱，在附件下載一個以前備份的驅動程式，

結果它顯示"封鎖的檔案"......

這個檔案我特地用rar加密上傳上去的，一開始也都沒問題，

一直到現在要下載才這樣，這還有救嗎?

因為已經是很舊的產品了，網路都找不到下載點，

寫信給gmail申訴有用嗎?

※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 49.156.214.175

※ 文章網址: http://www.ptt.cc/bbs/Google/M.1418608898.A.CFA.html

推 logs: try: 顯示原始郵件內容，另存成 eml 檔，用郵件軟體開 12/15 10:19

→ planetoid2: 點選轉寄，在草稿狀態，看能否下載附檔？ 12/16 00:03

2016年7月6日星期三

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、總筆數

2016年6月26日星期日

SQL document看法

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

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

*建議使用IE開啟

簡易說明如下圖：

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

因此，上述內容就變成

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

則變成

先把要用的貼一貼，再刪

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'

)

2016年4月26日星期二

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

2016年3月23日星期三

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

匯入資料時發現這種錯誤

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

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

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

kekejann

2016年11月21日星期一

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

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

2016年10月11日星期二

[SQL] 取T-SQL結果

2016年7月20日星期三

Gmail的附件檔案被封鎖

2016年7月6日星期三

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

2016年6月26日星期日

SQL document看法

2016年4月26日星期二

SQL Server 維護計畫錯誤

2016年3月23日星期三

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

總網頁瀏覽量

關於我自己

2016年11月21日 星期一

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

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

2016年10月11日 星期二

[SQL] 取T-SQL結果

2016年7月20日 星期三

Gmail的附件檔案被封鎖

2016年7月6日 星期三

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

2016年6月26日 星期日

SQL document看法

2016年4月26日 星期二

SQL Server 維護計畫錯誤

2016年3月23日 星期三

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

2016年11月21日星期一

2016年10月11日星期二

2016年7月20日星期三

2016年7月6日星期三

2016年6月26日星期日

2016年4月26日星期二

2016年3月23日星期三

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