Connect SQL Express 2019 to Another DB via ODBC

Question

Connect SQL Express 2019 to Another DB via ODBC

BRinehart

SSC Enthusiast

Points: 187
More actions
February 18, 2024 at 7:26 pm

#4380740

We have several databases in our environment. One is SQL Express 2019. We use MS OLEDB provider to connect to it. We connect to another DB via ODBC. Is it possible to configure the SQL Express DB to connect to and view tables in the ODBC connected DB?
Ideally we would like to write stored procedures and queries that can reference (read only) those tables in the other database.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246965 More actions · Answer 1

I've had a quick look in the documentation and can't see anything that suggests you cannot create a linked server in SQL Server Express. Have you tried? What happened when you did?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

BRinehart SSC Enthusiast Points: 187 More actions · Answer 2

Phil,

I reviewed MS documentation, two SQL Server books and searched this SQL Server on line resource. I cannot find documentation that describes how to link to another server. I may not have used the term "linked server" in my research. I will try again using this search criteria. If you know where this documentation is located, I would appreciate some guidance.

Using your suggestion, I was able to find this info on another site:

https://www.mssqltips.com/sqlservertip/6083/understanding-sql-server-linked-servers/

It describes using Linked Servers in SSMS. I opened this on our SQL Server. There is an ODBC connection to the other database. However, the ODBC connection isn't listed under providers.

This reply was modified 9 months, 1 week ago by BRinehart.

Phil Parkin SSC Guru Points: 246965 More actions · Answer 3

Linked Server is the specific term you are looking for, I think. Take a look at this link, for example:

https://learn.microsoft.com/en-us/sql/relational-databases/linked-servers/create-linked-servers-sql-server-database-engine?view=sql-server-ver16

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

BRinehart SSC Enthusiast Points: 187 More actions · Answer 4

Phil,

Yes, linked server appears to be the correct search term. I reviewed the MS document and attempted to create a new link to the other database via ODBC. After several tries, I've determined that I need to do more research. I'm not familiar with all of the info that SSMS is asking for.

We have a SYSTEM DSN that we typically use to access that database. We use it from an application as well as from Toad Data Point. Those connections are simple. We simply provide the DSN name, username and password. The SSMS link is asking for more information than that. So, I will continue to research.

BRinehart SSC Enthusiast Points: 187 More actions · Answer 5

I don't understand why this is so difficult.

For example, our SQL Server database is on server SVR1. The is a SYSTEM DSN named DSN1 for the ODBC connection to a Progress database on SVR1. The Progress database is on SVR2. I've tried to create the link between the two servers using SSMS and the MS ODBC provider. However, the provider is asking for parameters that I don't have answers for.

I can connect to the Progress database via the SYSTEM DSN named DSN1 from Toad Data Point. It works well. Just can't figure out how to create this Linked Server.

deubel_m SSC-Addicted Points: 465 More actions · Answer 6

It's not that difficult. On the starting page of the dialog choose as server typ "SQL Server". Create a login on the remote sql server. On the Security Tab choose the last option. Type in the login you created and the password and that's it. No need for ODBC or OLE DB. Easy as hell. BTW. it's described in detail on the link phil posted. So reading is the trick ;-).

BRinehart SSC Enthusiast Points: 187 More actions · Answer 7

The remote server is not a SQL Server. It is a Progress database. We created and use a SYSTEM DSN that points to the Progress ODBC driver to access the database. What am I missing?

frederico_fonseca SSCoach Points: 16157 More actions · Answer 8

lots of info on google - here is one https://www.progress.com/tutorials/odbc/connect-to-postgresql-database-from-sql-server-linked-server

BRinehart SSC Enthusiast Points: 187 More actions · Answer 9

Hey, that worked! Thank you!

I reviewed several on-line how -to's but none were as specific as this article. I book marked it for future reference.

Just FYI, the Progress database that I connected to is several years old. Nothing as current as suggested in this article. But the procedure worked. I suspect that this might work for any ODBC data source.

Now all I have to do is figure out how to read those remote tables from SQL Server. I'm working on that.

Thank you again for your assistance.