Using Feeds SQL with MSSQL

Hi,

Will this also work to import MSSQL to Drupal using MongoDB for fields? See www.drupal.org/project/mongodb
I want to build a website with hunderdthousands of records. The source info is in MSSQL and I want to use drupal with MongoDB for performance issues.
Could feeds using sql parcer then set the fields in mongodb?
Thanks a lot in advance for your reply
greetings, Martijn

On Step 4, I'm still getting an error 'could not find driver' when I'm setting the query in the SQL parser.

Is there anything else I need to do to tell Drupal about the dblib driver that I've dropped off in includes/database? Is it possible because I'm on openSuSE and the PHP modules don't work the same as in Cent?

Corrected sponsor link

On some servers, the MS SQL driver code may default to a different port than the standard 1433 or you need to put in your own specific port. In that case you need to put the port number into settings.php. However, the driver may not understand the port parameter so try putting the database connection setting as:

          'host' => 'xx.xx.xx.xx:1433',
          'port' => '',

or

          'host' => 'xx.xx.xx.xx,1433',
          'port' => '',

In the Settings for SQL parser page, when I click on the save button, it shows:
could not find driver

I did everything as described above. My mssql info in the phpinfo is as below:

mssql

MSSQL Support enabled
Active Persistent Links 0
Active Links 0
Library version FreeTDS

Directive Local Value Master Value
mssql.allow_persistent On On
mssql.batchsize 0 0
mssql.charset no value no value
mssql.compatability_mode Off Off
mssql.connect_timeout 5 5
mssql.datetimeconvert On On
mssql.max_links Unlimited Unlimited
mssql.max_persistent Unlimited Unlimited
mssql.max_procs Unlimited Unlimited
mssql.min_error_severity 10 10
mssql.min_message_severity 10 10
mssql.secure_connection Off Off
mssql.textlimit Server default Server default
mssql.textsize Server default Server default
mssql.timeout 60 60

Make a few grammar changes

Hello Freefox, I am new to the drupal world. I tried to follow your steps in getting drupal working against MS SQL, but I am stuck at step 4, was hoping you could shed some light on it. The place where you say
1) I presume here that you already have installed the Feeds module
2) Get the "Feeds SQL" module, install and enable it
3) Go into the Feeds UI and select the SQL fetcher

How do we install and enable it? I downloaded the module and unzipped it, but dont see any .sh files to install, also in step 3 you say go to feeds UI, what would the URL be? (localhost/feeds)?? Thank you for your help.

After a day or two of trying everything under the sun, I finally got this working on both OSX and Ubuntu. The key things that were missing from the instructions above were the need to install PDO_DBLIB and the need to configure FreeTDS correctly.

1) GETTING THE CORRECT PHP EXTENSIONS

Firstly, you need to install the right drivers and enable the PHP extensions. The instructions differ depending on which OS you're running, so I’ll give steps for both.

Ubuntu

1. Download and install the PDO_DBLIB library (along with any necessary MSSQL extensions) with:
   sudo apt-get install php5-odbc php5-sybase tdsodbc
2. Check mssql module is installed with:
   php -m
3. Not sure if these steps are needed, but I did them anyway:
   sudo apache2ctl stop
sudo apache2ctl start

OSX (10.9 - Mavericks)

A lot of this was taken from http://goo.gl/1t8v0Q. If you use MAMP then I’ve highlighted where changes are necessary by including some MAMP insctructions below any normal OSX AMP stack instructions.

1. You’ll need to download the latest Xcode from the Mac App Store and then run it and install the command line tools.
2. You'll also need autoconf, so download the latest source at autoconf-latest.tar.gz
   Then, use terminal to navigate to the extracted source directory and run the following commands:

   ./configure
   make
   sudo make install

3. Now on to FreeTDS - the library that will connect to MSSQL. Visit this link to get the latest stable version of FreeTDS: freetds-stable.tgz.
4. Once it’s downloaded and extracted, again in terminal, navigate to the the extracted directory. Now enter the below commands to build and install the driver:
   

   ./configure --prefix=/private/etc/freetds --with-tdsver=8.0 --sysconfdir=/private/etc/freetds/conf
   make
   sudo make install

5. Now its time to build the PHP MSSQL and PDO_DBLIB extensions. Use this link to download the Mavericks PHP source from Apple and then extract the archive: php-5.4.17.tar.bz2 from Apple
   
   
   To build and install the MSSQL extension, in Terminal change to the new MSSQL extension directory (php-5.4.17/ext/mssql) and run the below commands to build the mssql extension:
   
   
   Non-MAMP version

   phpize
   ./configure --with-php-config=/usr/bin/php-config --with-mssql=/usr/local/freetds/
   make
   sudo cp modules/mssql.so /usr/lib/php/extensions/no-debug-non-zts-20100525/

   MAMP version

   phpize
   ./configure --with-php-config=/Applications/MAMP/bin/php/php5.4.4/bin/php-config --with-mssql=/usr/local/freetds/
   make
   sudo cp modules/mssql.so /Applications/MAMP/bin/php/php5.4.4/lib/php/extensions/

6. To build and install the PDO_DBLIB extension, in Terminal change to the new PDO_DBLIB extension directory (php-5.4.17/ext/pdo_dblib) and run the below commands to build the pdo_dblib extension:

   Non-MAMP version

   phpize
   ./configure --with-php-config=/usr/bin/php-config --with-pdo-dblib=/usr/local/freetds/
   make
   sudo cp modules/pdo_dblib.so /usr/lib/php/extensions/no-debug-non-zts-20100525/
   

   MAMP version

   phpize
   ./configure --with-php-config=/Applications/MAMP/bin/php/php5.4.4/bin/php-config --with-pdo-dblib=/usr/local/freetds/
   make
   sudo cp modules/pdo_dblib.so /Applications/MAMP/bin/php/php5.4.4/lib/php/extensions/
   

7. Make changes to the php.ini file so that the extensions are enabled.
   
   
   Non-MAMP version
   The file we need to edit is /etc/php.ini.
   • Open /etc/php.ini in your favourite text editor with root privileges>
   • Search for the line ; extension_dir = "./"
   • Replace the above line with extension_dir = "/usr/lib/php/extensions/no-debug-non-zts-20100525/"
   • In the extensions section, add these two lines if they’re not already present:
     

      extension=mssql.so
      extension=pdo_dblib.so
     

   • The changes will be immediate for PHP CLI but if you are using PHP with Apache you will need to restart Apache (sudo apachectl restart).
     
     

   
   

   MAMP version
   The file we need to edit will be something like /Applications/MAMP/bin/php/php5.4.4/conf/php.ini (depending which version of PHP you use in MAMP).

   • Open /Applications/MAMP/bin/php/php5.4.4/conf/php.ini in your favourite text editor with root privileges
   • In the extensions section, add these two lines if they’re not already present:
     

      extension=mssql.so
      extension=pdo_dblib.so
     

   • Restart Apache within MAMP.
     
     

2) CONFIGURING EVERYTHING

1. Download the Drupal DBLIB library and copy the dblib folder into /includes/database as per the installation instructions.
2. Edit freetds.conf:
   sudo nano /usr/local/freetds/conf/freetds/freetds.conf
3. Add a server alias to the end of the file for the MSSQL server you'd like to connect in to. Ensure you add in the correct host name/ip address, port and instance name. E.g. I added in the following:
   

   # A test MSSQL Server
   [testServer]
           host = 10.211.55.3
           port = 1433
           instance = SQLEXPRESS
           tds version = 7.2

4. In /sites/default/settings.php, add in your external database like below. Importantly, host has to be in the format server_alias\\instance_name, as you set them in freetds.conf above. For my example, server_alias is testServer and the instance_name is SQLEXPRESS, so this becomes testServer\\SQLEXPRESS. Note the double backslash!
   EDIT: As explained in comment 13, I have had a situation where there instance_name wasn't needed. If you're having problems, try removing the '\\instance_name'.

   $databases = array (
     'default' => 
     array (
       'default' => 
       array (
         'database' => 'drupal',
         'username' => 'username',
         'password' => 'password',
         'host' => 'localhost',
         'port' => '',
         'driver' => 'mysql',
         'prefix' => '',
       ),
     ),
     'mssql_test' => 
     array (
       'default' => 
       array (
         'database' => 'database_name',
         'username' => 'username',
         'password' => 'password',
         'host' => 'testServer\\SQLEXPRESS',
         'port' => '',
         'driver' => 'dblib',
         'prefix' => '',
       ),
     ),
   );
   

5. Restart apache and clear your site's cache, and you should be able to use the MSSQL database as you please, including querying it within FeedsSQLParser.

Hope this helps someone else out at some point, as getting it all set up is far from trivial.

Thanks, this saved me a lot of time!

One thing to add, for anyone else setting up Feeds SQL to import data from an external MSSQL database: The column names in your SELECT query used in the Feeds SQL parser/mapping and in the importer node or form cannot include any uppercase characters. See https://drupal.org/node/1763872

Thanks for the information on this subject.

On my local dev machine - Mac OS X Mavericks - I still run into an issue retrieving MS SQL data. I followed the instructions from comment #10, this gave following error message retrieving data from Drupal.

Additional uncaught exception thrown while handling exception.

Original

PDOException: SQLSTATE[] (null) (severity 0) in mssql_sync_test() (line 65 of /Volumes/Data RAID 1/WEB/drupal/7.x/sandbox-7.x/public_html/sites/all/modules/mssql_sync/mssql_sync.module).

Additional

PDOException: SQLSTATE[] (null) (severity 0) in db_select() (line 2520 of /Volumes/Data RAID 1/WEB/drupal/7.x/sandbox-7.x/public_html/includes/database/database.inc).

In basic php-code I'm able to fetch all of my content using default mssql_connect() function and also via the PDO functions.

My PHP version:
PHP 5.4.24 (cli) (built: Jan 19 2014 21:32:15)

Any help?

After one of our systems was moved to a different MSSQL server, I also started getting the SQLSTATE[] (null) (severity 0) message. After lots of trial and error, I eventually solved it by removing the instance name within settings.php.

So, for example:

      'host' => 'testServer\\SQLEXPRESS',

Became:

      'host' => 'testServer',

(Where testServer was set up in the FreeTDS config as explained in my previous comment.)

On the new server, the server name was all that was used within SQL Management (i.e. it was just SERVERNAME, not SERVERNAME\SQLEXPRESS). Maybe this is why the host needed to be updated inside settings.php. (I still left the instance name as SERVERNAME inside of the FreeTDS config.)

Hopefully that will help with your issue.

As a note to others, I've found out that the SQL user you connect with must have their 'Default database' set up to the one you want to connect to. If this is not the case, even if they have access permissions to the database table you'd like to connect into, you'll get an SQL connection error. (Presumably you'd therefore need one user for each database you'd like to connect with.) That got rid of a headache I'd had for some time!

If you do have connection errors, it's also worth checking that the database can accept incoming connections and that the inbound Windows Firewall rules allow your IP to connect in to the MSSQL port.

interestingly I have done all the steps even before I found this post, and I assure you they work. However in my case, as obvious as it may sound when I import from mssql (external server data) to mssql which is holding my drupal system. it imports only one colomn on all rows. I have tried this now for 3 days no success. if some one has any idea why this is so. please help through, this is what i got in my error logs.

SQLSTATE[23000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot insert duplicate key row in object 'dbo.node' with unique index 'vid_unique'.
Original item

array(
'ID1' => '1',
'Name' => 'lule ronald',
'Gender' => 'm',
'School' => 'lubiri',
'Location' => 'kamapa',
'dish' => 'posho',
)

then afterword gave this error

Missing bundle property on entity of type node.
Original item

array(
'ID1' => '2',
'Name' => 'kato husan',
'Gender' => 'm',
'School' => 'old kampala',
'Location' => 'lubaga',
'dish' => 'rice',
)

when it varnished, it went back to importing only a single column.
how can I return all my columns.

Thank you so much!!

I'm on a Debian 7.8 installation and needed to connect to an external database and dblib did the trick!

This was a lifesaver. Thank you.

Comment #10 was a golden contribution to this issue. Thank you for documenting those steps all in one place.

I also ran into issue of comment #13 (also noted/edited in comment #10). After removing instance name, I still had error but this time my freetds.log noted "Login failed for user ... checked my credentials in settings.php and corrected the password to success.

In nutshell found it helpful to enable log file in freetds.conf

I have been successfully using the instructions from #10 with Drupal7 on PHP 5.4 for some time now. I am now looking into upgrading the PHP 7 but I have been having trouble because dblib does not appear to work in PHP 7.

Has anyone migrated Drupal 7 to PHP 7 and successfully connected to mssql?

@bulldozer2003 It's working for me. (Debian, Drupal 7.63, Feeds 7.x-2.0-beta4+14-dev, Feeds SQL 7.x-1.0)

AFAIK we didn't change anything in the setup for accessing the external mssql database (from #10 instructions) when we upgraded to PHP7.

Thanks @brianbrarian What I did end up noticing is that the PHP 7 I was using in CentOS 7 wasn't built with freetds. If you run php -i | grep -i tds does it show your PHP was built with TDS? This is my result from PHP 5.4: PDO Driver for FreeTDS/Sybase DB-lib => enabled

I did find a solution for my situation and it might be advantageous to others since the dblib project isn't being updated. I was able to use the sqlsrv project after installing Microsoft ODBC driver using instructions on stack overflow.

Step by step, what I did was:

1. Download the sqlsrv project
2. Make a symbolic link in includes/database/ to ../../sites/all/modules/sqlsrv/srlsrv
3. Install the MS ODBC drivers and for PHP (link above). (I skipped the last two lines there and created files for each in my php.ini.d directory. YMMV)
4. Alter my database definition in settings.php by changing driver to 'sqlsrv' and adding the fields you left out when using dblib.

@bulldozer2003 Yes, freetds is enabled, as it was when the installations were on PHP 5.6.