Linux 透過FreeTDS連接MSSQL (一) 安裝與設定

當Linux工作者遭遇到了MSSQL, 真是一件很麻煩的事情, 想不到我真的遇到了這件事情
再來會紀錄一下使用方法

安裝

•  FreeTDS
  
  官網 : http://www.freetds.org/
  github : https://github.com/FreeTDS/freetds
  
  預設會安裝於 /usr/local/bin ,  /usr/local/etc
•  unixODBC
  
  官網 : http://www.unixodbc.org/
  採用 yum 安裝
  yum install unixodbc

設定

• FreeTDS
  
  設定檔 : /usr/local/etc/freetds.conf 或 $(HOME)/.freetds.conf
  http://www.freetds.org/userguide/freetdsconf.htm
  
  假設我要連到某一台SQL Server, 就得加上以下設定
  
  [TDSNAME]
  host = [server host]
  port = 1433
  tds version = 7.0
  client charset = UTF-8
  
• unixODBC
  
  設定擋 : /etc/odbcinst.conf
  [FreeTDS]
  Description = FreeTDS Driver
  Driver = /usr/local/lib/libtdsodbc.so
  
  設定擋 : /etc/odbc.ini 或 $(HOME)/.odbc.ini
  [SERVERNAME]
  Driver=FreeTDS
  Servername=TDSNAME
  Port = 1433
  Database=DBInstance
  TDS_Version = 7.0
  
  

設定好後可以用以下語法測試

• FreeTDS
  tsql -S TDSNAME -U ID -P PASSWORD
• unixODBC
  isql -v SERVERNAME ID PASSWORD

©Yichen

[MS SQL2005] How to query table and stored procedure last modify date

In the MS SQL Management Studio. Although I can find the table create date in summary page, but sometimes I need know last modify date ,but i only find create date .

But,we can find so many information in sys.all_object .

You can use this sql statement query table information



select [name],create_date,modify_date FROM sys.all_objects where type_desc = 'USER_TABLE'



You can use this sql statement query stored procedure information



select [name],create_date,modify_date FROM sys.all_objects where type_desc = 'SQL_STORED_PROCEDURE'
and substring([name],1,3) not in ('sp_','dt_','xp_')



Why we need to filter 'sp_,dt_,xp_' ,because they are at beginning of sql server system stored procedure name.

©Yichen