A great way to Link SQL Server and MongoDB with BI Connector

08 January,2020 by Jack Vamvas

Step 1 - Ensure MongoDB BI Connector is installed and configured . This will support the access to the MongoDB server. How to unlock the secrets of MongoDB BI Connector

Step 2 - Install MongoDB ODBC Driver for BI Connector - provides connectivity between a SQL client and the MongoDB Connector for BI

More details on https://docs.mongodb.com/bi-connector/master/reference/odbc-driver/

Step 3 - Create the System DSN

You'll need the following base information to connect to the MongoDB BI Connector 

Server Name\Port  

User. 

Password

Read further for full  details of the Create System DSN options

Bi_sqlserver

Step 4 - Create the Linked Server

This is some sample code to create the Linked server , that will utilise the System DSN you created in the previous step . The provider to use is the Microsoft OLE DB Provider for ODBC Drivers. The Data source is the name of the Data Source you created. 

You will also need to set the user name and password for the passthrough from the SQL Server Linked Server to MongoDB.The username and password should be supplied by the MongoDB DBA

USE [master]
GO

/****** Object:  LinkedServer [M]    Script Date: 08/01/2020 07:42:49 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'M', @srvproduct=N'', @provider=N'MSDASQL', @datasrc=N'MongoDB_DEV'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'M',@useself=N'False',@locallogin=NULL,@rmtuser=N'userView_read?source=mdb1',@rmtpassword='########'
GO

Once you've confirmed the connection works - via the Test Connection , you're ready to start viewing the MongoDB objects which the user has privileges to view. 

One of the problems I've encountered is when trying to return a MongoDB collection column which is a varchar and the following error is encountered.

OLE DB provider "MSDASQL" for linked server "M" returned message "Requested conversion is not supported.".

The query would be something like : select * from OPENQUERY(M,'select col1 from MyCollection')

 

For this issue a BI Connector switch was created. --MaxVarcharlength.   Add this switch to the start up of mongosqld and the varchar based column data is now available.

Use a value of 8000 . e.g MaxVarcharlength: 8000

From the MongoDB docs "MaxVarcharlenght Specifies the maximum length, in characters, for all varchar fields. If mongosqld encounters a string that is longer than the maximum length, mongosqld truncates it to the maximum length and logs a warning."

 

 

!

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on A great way to Link SQL Server and MongoDB with BI Connector


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer