How to use MongoDB column alias in SQL Server SELECT

02 December,2020 by Rambler

Question: I am accessing data through a SQL Server Linked Server . The Linked Server is interfacing a MongoDB ODBC - connected to a MongoDB. The MongoDB ODBC connects to the BI Connector - which in turn extracts some collections. 

When the results are returned using the SQL Server OPENQUERY functionality , the dot nation is used to represent some columns. I want the columns presented without dot notation. How can I return an alias?

Answer: When using OPENQUERY command through SQL Server connecting to MongoDB - it will present with dot notation. 

For Example:

select * from OPENQUERY(MongoDB_TEST,'select * from mydb.DataRequest')

 

may return a column such as in.CompanyId.   Your requirement is to return the column name without the dot. in that case , use a standard sql alias system. Taking the above statement - change to  :

select [in.CompanyId] as companyID from OPENQUERY(MongoDB_TEST,'select * from mydb.DataRequest')

 

Read more on MongoDB & SQL Server Linked Servers

How to fix Msg 7347 - linked server returned data that does not match expected data length for column

 

 


Author: Rambler (http://www.dba-ninja.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 How to use MongoDB column alias in SQL Server SELECT


dba-ninja.com