Forms error during data lookup - Invalid object name 'information_schema.columns'.

asked on February 13, 2014

I get the error below during a data lookup. I logged into SQL with the user Forms is using to authenticate to the external database and ran the following two queries:

1. select * from INFORMATION_SCHEMA.COLUMNS. This one executes normally.

2. select * from information_schema.columns. This one gives the same error as the Forms application, Invalid object name 'information_schema.columns'. The source database is collated SQL_LATIN1_GENERAL_CP1_CS_AS so I suspect this is a case sensitivity issue.

2/12/2014 9:23:58 PM | Session: ehnpkq3cxctlawgc4e4luqy0
User: ADS\testuser
URL: /Forms/form/prepopulate
Browser: Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; WOW64; Trident/5.0)
IP Address: ::1
Message: ERROR [42S02] [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'information_schema.columns'.
Stack trace: at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at Laserfiche.Forms.EntityModels.Data.MSSqlDBConnector.GetDataSet(String cmdTxt, ICollection`1 parameters)
at Laserfiche.Forms.EntityModels.Data.MSSqlDBConnector.GetTable(String cmdTxt, ICollection`1 parameters)
at E_Forms.Classes.LookupServiceProvider.GetColumnType(OdbcConnector odbcConnect, String tableName, String columnName, DataBaseType databaseType)
at E_Forms.Classes.LookupServiceProvider.PrePopulate(Int32 fieldId)
at E_Forms.Controllers.FormController.PrePopulate(String fieldId, Int32 formId, String uniqueid)
at lambda_method(Closure , ControllerBase , Object[] )
at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters)
at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters)
at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass37.<>c__DisplayClass39.<BeginInvokeActionMethodWithFilters>b__33()
at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass4f.<InvokeActionMethodFilterAsynchronously>b__49()
at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass4f.<InvokeActionMethodFilterAsynchronously>b__49()
at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass37.<BeginInvokeActionMethodWithFilters>b__36(IAsyncResult asyncResult)
at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass25.<>c__DisplayClass2a.<BeginInvokeAction>b__20()
at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass25.<BeginInvokeAction>b__22(IAsyncResult asyncResult)

0 0

replied on February 13, 2014

The following page has some information about collation in SQL server:

http://blog.sqlauthority.com/2007/04/30/case-sensitive-sql-query-search/

while it's not standard, collation can be turned on in SQL server requiring you to use the correct case. Other types of SQL servers can require this to work correctly (oracle in particular)

0 0

View 1 previous reply

replied on February 13, 2014

Thanks for the reply. I understand how collation works but I can't modify the collation of the third party database I am querying so I'm looking for a solution or workaround so that the Laserfiche Forms application is tolerant of both case-sensitive and case-insensitive collations.

0 0

replied on February 13, 2014 • Show version history

I didn't put 2 & 2 together obviously.

Maybe you could try is to create stored procedure that acts as an intermediate step? I did some cursory searching bu didn't find anything on this.

This would be somehting better addressed by the developers to allow you to specify that (or at least to respect the case it is in when creating a lookup in desig mode and then use that again)

0 0

replied on March 31, 2014 • Show version history

My VAR opened a case with Laserfiche and let me know they responded with something along the lines of, this issue is on their to-do list of fixes. In the meantime we are able to work around the issue with stored procedures but we are having to create a lot of them because we use data lookups to this database extensively in most of our forms.

0 0

replied on May 12, 2014

Anyone have any updates to when this may be fixed?

0 0

Question

Question

Forms error during data lookup - Invalid object name 'information_schema.columns'.

Replies

Sign in to reply to this post.