Question

LF Forms and Workflow query "Linked SQL or MySQL Server" possible at all ?

Forms Workflow

Updated October 30, 2014

asked on October 30, 2014

Trying to figure out if it is even remotely possible to use Linked Server feature on SQL server to use with LF Forms or Workflows for Database queries.

Here are few different things I have attempted but am having no luck grabbing data from either a MySQL or MSSQL linked server with Forms and Workflow.

Setup :

SQL 2008 R2[Source SQL server] with 2 Linked Servers, one is MySQL and other is separate[second] MSSQL server. Created separate Views and SP's within the Database that is used as source on the Source SQL Server that are basic select statements to query data from the 2 Linked Servers.

Both the View and SP's can be parsed properly within the Source SQL server, so there is data flow (no security/permission etc. etc issues).

Now when I attempt to use these "views" or the "SP's" created on the source SQL server on LF Forms or Workflow to do fetch data, it fails.

If i modify these Sp's or even teh Views to query tables on the Source SQL server instead of using linked servers, the Forms and Workflows have no issue at all fetching data.

So my question is can LF fetch data from views or procedures created that are querying a "linked server" !!

Why am I attempting this ? Trying to figure out a way to Fetch data from MySQL server and use it for LF Forms, since LF forms does not support anything other than SQL and Oracle.

1 0

Replies

replied on October 30, 2014

Do you get an error? Are the views visible? What exactly fails?

0 0

replied on October 30, 2014

Fetching Data fails from Forms, the forms when adding the lookup rule can actually see the column names properly that view is generating, but you go preview it or even after publishing it the active forms does not fetch the field values.

If get this error message in event log, no error on forms web url it just leaves the fields blank !

URL: /Forms/form/lookup?formId=1
Message: ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "MYSQLDEV".
ERROR [01000] [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB provider "MSDASQL" for linked server "MYSQLDEV" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".

Other errors i see with forms in event log :

URL: /Forms/api/lookup/column?formId=1&sourceId=729
Message: Sequence contains no elements

URL: /Forms/api/lookup/column?formId=1&sourceId=1712
Message: Laserfiche Forms encountered an internal error. Please try again.

0 0

replied on October 30, 2014

Here is the error message i see in event log when using View linked to the second SQL server. Note: that the user we are using for LF forms to connect to source SQL data source also exists on the external/second sql server, even the user I am using to create form or view it exists on the linked SQL server, so there should be no reason for authentication to fail, the view when queried at the source sql does not give any errors !

URL: /Forms/form/lookup?formId=1
Message: ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]The OLE DB provider "SQLNCLI10" for linked server "devdb.mydomain.ca" reported an error. Authentication failed.
ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot initialize the data source object of OLE DB provider "SQLNCLI10" for linked server "devdb.mydomain.ca".
ERROR [01000] [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB provider "SQLNCLI10" for linked server "devdb.mydomain.ca" returned message "Invalid authorization specification".

0 0

replied on October 30, 2014

This looks like a problem with your setup. This link might possibly help. I'm not sure if you set up your linked server using a 32-bit driver and Forms is trying to access it over the 64-bit one, that might be a consideration too.

0 0

replied on October 30, 2014

Like i said if it was linked server issue, it should not be able to parse data correct on sql itself ? And nope both the LF and SQL are 64 bit systems and the linked server setup was created using MySQL 64 bit version. So everything is on 64 bit, unless in the backed LF runs as 32 bit and might be causing grief ?

0 0

You are not allowed to follow up in this post.

Question

Question

LF Forms and Workflow query "Linked SQL or MySQL Server" possible at all ?

Replies

Sign in to reply to this post.