Sharepoint Connecting to SQL On Premise

David Watson 1 Reputation point
2020-08-26T00:12:01.947+00:00

Hi

I am sorry if this question is already answered, I suspect my more practical leaning approach is missing something in the instructions I have read.

I have an established SQL Server not currently hosted on Azure. In this SQL Server I have things like my client list, sites and water bodies we look after. The SQL Server also handles my remote field staff needs such as time cards, work requests and so on.

One thing the SQL Server doesnt do well, is handle my quotes process, I have tried to build models for quoting but somehow it fails.

After considering our current practices and looking at this issue, a SharePoint Site with document library solves most of the issues I am aiming to build for.

However when I add a document to the library I want to also add data such as Client, Site and Water Body data already in SQL Server. Seems to me likely I should connect the relevant tables from SQL to a list in SharePoint to enable the document library to be populated correctly and save a lot of double up of data entry.

In reading into this further I am getting very lost with BCS, BCDM and so on.

I have the provider data and the connection string.

Can someone direct me to a resource that steps through the set up for this type of connection? I don't know I am really missing something here.

SharePoint Server
SharePoint Server
A family of Microsoft on-premises document management and storage systems.
2,216 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,682 questions
SharePoint
SharePoint
A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
9,606 questions
SharePoint Server Management
SharePoint Server Management
SharePoint Server: A family of Microsoft on-premises document management and storage systems.Management: The act or process of organizing, handling, directing or controlling something.
2,798 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Itch Sun-MSFT 2,556 Reputation points
    2020-08-26T07:25:25.863+00:00

    In fact, your needs are essentially the same as the functionality of the Business Connectivity Services.

    SharePoint has a type of List called the External List which is used for displaying data that comes from Business Connectivity Services (BCS). You can connect with any external source (in your case it is a SQL database table) and can create a list from that source.

    Based on your description, you seem to have a general understanding of this feature. What problems did you encounter with the actual deployment?

    You can refer to the following article for a more specific understanding of BCS:

    Get started with Business Connectivity Services in SharePoint

    You also need to understand how to create external content type for SQL Server:

    Create external content types for SQL Server in SharePoint

    Finally, you can refer to the following article, which uses SharePoint designer. This may be more convenient for your operation.

    SharePoint List From SQL Server Table

    SharePoint Business Connectivity Service – Create External Content Type and External List

    Please note: Microsoft provides third-party contact information to help you find technical support. This contact information may change without notice. Microsoft does not guarantee the accuracy of this third-party contact information.

    If the response is helpful, please click "Accept Answer" and upvote it. 😊