I receive the following SQL error when accessing WorkflowGen: A SQL error has occurred .Net SqlClient Data Provider: Invalid column name ‘RES’

Administration Database
1

Solution

If you receive an SQL error indicating .NET SqlClient Data Provider: Invalid column name RES, it’s likely because your SQL Server database collation is set to Latin1\_General_BIN, which is not compatible for use with WorkflowGen. We suggest changing your WorkflowGen database collation to the default SQL_Latin1_General_CP1_CI_AS, since the database installation script uses this collation. To do this:

  1. Back up your WorkflowGen database.

  2. Stop all WorkflowGen services and the IIS web server.

  3. Run the following SQL query to change your database collation:

    ALTER DATABASE wfgen_sql
COLLATE SQL_Latin1_General_CP1_CI_AS;
GO

    Note: Make sure there are no other open connections to the database, otherwise the SQL query will fail.

  4. Run the following SQL query to test the collation change:

    exec sp_executesql N'SELECT TOP 10 RES AS RES, LASTNAME, FIRSTNAME, LASTNAME + '' '' + FIRSTNAME as [REQUESTER] FROM ( SELECT ID_USER_REQUESTER, LASTNAME, FIRSTNAME, COUNT( WFPROCESS_INST.ID_USER_REQUESTER) as res FROM WFPROCESS_INST, USERS WHERE WFPROCESS_INST.ID_USER_REQUESTER=USERS.ID_USER AND WFPROCESS_INST.DATE_OPEN <= @DATE_TO AND WFPROCESS_INST.DATE_OPEN >= @DATE_FROM AND WFPROCESS_INST.ID_PROCESS IN (269,268,267,266,261,260,256) GROUP BY ID_USER_REQUESTER, LASTNAME, FIRSTNAME ) t2 ORDER BY RES DESC',N'@DATE_TO datetime,@DATE_FROM datetime',@DATE_TO='2010-02-12 07:00:00:000',@DATE_FROM='2009-11-14 07:00:00:000'

For more information, contact your SQL Server DBA.