Category Archives: SQL Server

Codeigniter V.S. MS SQL Server (Unicode)

Codeigniter won’t prepend “N” to strings and that’s critical for SQL server to retrieve string values. The solution is to revise DB driver of CI:

File: sys\database\DB_Driver.php

 

Using Like to Join Tables (MS Access)

Key:

  1. Use Regular Express
  2. Use Instr function

Example:

Reference:

Backup / Restore a MS SQL Server Database (Using SQL)

PDO to Connect to MS SQL Server

  1. Install PHP: 5.3 is recommended. (All .dll files related to 5.4 won’t work!)
    http://www.php.net/downloads.php
  2. Download Microsoft Drivers: Do not use 3.0, only 2.0 dll files will work!
    http://www.microsoft.com/en-us/download/details.aspx?id=20098
  3. Copy drivers to “ext” folder (default in php 5.3, and can be changed in php.ini)
  4. Update php.ini (Use text editor or PHP Manager)
    e.g. extension=php_pdo_sqlsrv_53_nts_vc9.dll (vc6 won’t work!)
  5. Restart IIS
  6. Check PDO support in phoinfo(): “sqlsrv” should be in “enable” list.
  7. Connection String: sqlsrv:Server=19x.1xx.1xx.1xx;Database=lab

Here is my environment: Windows 7 + IIS 7 + SQL Server 2008 R2, PHP 5.3
Believing in latest version from MS is my fault.

Reference:

  1. http://www.ucamc.com/e-learning/computer-skills/146-php-sqlsrv.html
  2. http://www.yiiframework.com/forum/index.php/topic/18946-how-to-connect-mssql-database/
  3. http://www.php.net/manual/en/ref.pdo-sqlsrv.php
  4. http://www.php.net/manual/en/ref.pdo-sqlsrv.connection.php
  5. http://www.yiiframework.com/forum/index.php/topic/42723-could-not-find-driver-sqlsrv/

10/7/2014 Update

Reference:

  1. http://j796160836.pixnet.net/blog/post/32652509-%5Bphp%5D-php-5.4-(for-windows)%E4%B9%8B%E5%BE%8C%E7%89%88%E6%9C%AC%E9%80%A3%E6%8E%A5microsoft-sql-s
  2. http://msdn.microsoft.com/en-us/library/cc296170.aspx
  3. http://www.microsoft.com/en-us/download/details.aspx?id=20098
  4. Install sqlsrv extension in different windows servers (Very detailed):
    http://robsphp.blogspot.co.uk/2012/09/how-to-install-microsofts-sql-server.html
  5. For Codeigniter: http://www.kaweb.co.uk/uncategorized/mssql-server-2005-and-codeigniter/
  6. Complete Codeigniter for sqlsrv: (Replace files in /database/drivers/sqlsrv/)
    http://www.manjustudio.com/2013/01/09/codeigniter-mssql-sqlsrv/
    File: codeIgniter1
  7. Insert and Update UTF-8 into MSSQL (Need to revise driver):
    http://stackoverflow.com/questions/22981997/insert-update-utf8-string-sql-server-codeigniter


     

Settings in PHP Manager:

PHP_ManagerSample Code:

 

 

SQL to Create a New Table

MS SQL:

 

My SQL: