none
How to display a drop down column with values/data from a SQL Table?

    Question

  • Hi,

    I am trying to display a drop down column in an External List with data/values from a SQL table.

    How can I do that?

    Troubleshooting:

    • Checked: Allow user form templates to use authentication information contained in data connection files
    • I can't log in to SQL even though I am using an account with a sysadmin permissions
    Tuesday, February 14, 2017 7:21 PM

Answers

  • Hi,

    If you want to know why log in is failed. You can check with various way. Simple one  for SQL connectivity is to create an empty text file, with a file extension of "UDL". You can create it in Notepad. And it can have any name. I use "TestSQLConnection.UDL"

    Save it on the desktop of a windows PC and double click it.

    A "Data Link Properties" dialog box will pop up where you can enter the IP address of the SQL server and also a SQL username and password.

    Click the "Test Connection" button to see if you can connect.

    Check this URL for security log details to get more details like why it failed.

    https://www.mssqltips.com/sqlservertip/1735/auditing-failed-logins-in-sql-server/


    KRISHANA KUMAR

    SharePoint Architect

    Mosstechnet-kk.com

     

    Please click "Mark As Answer" if this post solves your problem or "Vote As Helpful" if it was useful

    • Proposed as answer by Victoria XiaModerator Thursday, February 23, 2017 2:27 AM
    • Marked as answer by Andy__G Wednesday, March 22, 2017 5:23 PM
    Wednesday, February 15, 2017 5:47 PM

All replies

  • Hi,

    Can you check if you have any named instance of SQL, are you able to make test connection with SQL from your computer to make user SQL is working fine and you have proper credential.

    You are going to create an InfoPath template having chosen Database template under Advanced Form Templates in InfoPath Designer.

    This form is nothing more than Filler form [ you need to open this form always from InfoPath client ] with forced on creation receive/secondary/read-only data connection to a database (you could have created Filler form and then any other data connections and "advanced" additions).

    You can publish any kind of form to Sharepoint (or anywhere else). But the filler form cannot be run in browser (through IPFS, Sharepoint Server's InfoPath Form Service). When the user opens (creates, updates, edits) XML forms of filler type/compatibility from Sharepoint, they are opened in client/filler form only.

    Client/filler forms use another protocol for connecting which does not have double hop authentication issues which have browser forms. If you would want to change this form to web browser form, then from browser this kind of form has to use web service to connect to a database.

    Note that browser-enabled forms can be run both from browser (and this is available only in enterprise Sharepoint Server's IIPFS), on server, and Filler (on client machines), while filler forms can be run only from Filler (but not from browser).

    If you want to open your InfoPath form in Browser and want to have a dropdown list you can design the form with browser opening enabled. Here you can create a secondary data connection to fetch data from SQL server and bind this data to your dropdown list on InfoPath form.

    https://support.office.com/en-us/article/Add-a-data-connection-to-a-Microsoft-SQL-Server-database-c26193cc-8520-4941-adec-1df637bbf03a?ui=en-US&rs=en-US&ad=US&fromAR=1

    Check what we can do with InfoPath and SQL, there are some limitations.

    http://www.infopathdev.com/blogs/patrick/archive/2010/01/29/infopath-gaps-in-sharepoint-and-sql.aspx


    KRISHANA KUMAR

    SharePoint Architect

    Mosstechnet-kk.com

     

    Please click "Mark As Answer" if this post solves your problem or "Vote As Helpful" if it was useful

    Tuesday, February 14, 2017 8:47 PM
  • Hi,

    Thanks for replying.

    I am using a sysadmin account to all my SQL instances and getting error: login failed

    What else can I do?

    Tuesday, February 14, 2017 9:15 PM
  • Hi,

    If you want to know why log in is failed. You can check with various way. Simple one  for SQL connectivity is to create an empty text file, with a file extension of "UDL". You can create it in Notepad. And it can have any name. I use "TestSQLConnection.UDL"

    Save it on the desktop of a windows PC and double click it.

    A "Data Link Properties" dialog box will pop up where you can enter the IP address of the SQL server and also a SQL username and password.

    Click the "Test Connection" button to see if you can connect.

    Check this URL for security log details to get more details like why it failed.

    https://www.mssqltips.com/sqlservertip/1735/auditing-failed-logins-in-sql-server/


    KRISHANA KUMAR

    SharePoint Architect

    Mosstechnet-kk.com

     

    Please click "Mark As Answer" if this post solves your problem or "Vote As Helpful" if it was useful

    • Proposed as answer by Victoria XiaModerator Thursday, February 23, 2017 2:27 AM
    • Marked as answer by Andy__G Wednesday, March 22, 2017 5:23 PM
    Wednesday, February 15, 2017 5:47 PM