question

DavidWatson-3216 avatar image
0 Votes"
DavidWatson-3216 asked Shri-6058 commented

Sharepoint Connecting to SQL On Premise

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.

sql-server-generaloffice-sharepoint-onlineoffice-sharepoint-server-administrationoffice-sharepoint-server-customization
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @DavidWatson-3216
Any progress on your troubleshooting?
If you think the replies are helpful to you, please remember to accept them Answer. It will help others who meet the similar question in this forum.

0 Votes 0 ·

Hi @DavidWatson-3216
This thread has been around for a long time. Please remember to accept them Answer if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.
Thank you for understanding! If you have any questions, or updates, please feel free to let me know.

0 Votes 0 ·

1 Answer

itchsun-msft avatar image
0 Votes"
itchsun-msft answered Shri-6058 commented

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. 😊

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

I suspect the problem I am running into is me :). I am not as young as I used to be and find it takes me longer to figure this stuff out now than it used too.

I do generally get the connection string and use it with ADO, but takes time.

I will read some more.

0 Votes 0 ·

Hi @DavidWatson-3216
I'm glad to hear from you.
There's no problem with this direction, I think you just need to take a little time to get to know this feature, I'm sure you can do it. 💪
If there's anything you'd like to know, don't hesitate to ask.



0 Votes 0 ·

Is this direction for SharePoint Online accessing On premise sql server database?

I thought we just have two options if SPO accessing online: 1. move sql to SQL Azure environment 2. Using WCF service to access the sql over the internet(bit risk as to take security precations).

0 Votes 0 ·