You may need to recreate views manually

Hints, Tips and Tricks

Technical Hints Tips and Tricks that cover customization and development using Sage CRM. API usage and coding are covered.

You may need to recreate views manually

  • Comments 13
  • Likes
Please note that this is not an exhaustive discussion and but simply represents the things that come to my mind when seeing this error. 

Why does it appear?

The can be several reasons why this message appears and it usually comes when there has been a change in the configuration of the system. This maybe as a result of an upgrade, or if the SQL password for the "sa" user has been changed, and the same change has not been affected in CRM.

There are some helpful questions to ask when this happens.

Does the error appear for all users?

If the system administrator can logon but another user can not, you will have to turn on the system logs and see what happens. Start looking at the SQL logs and see if the requests are getting to the database. It may give you enough information to sort out the problem.

Is your database running?

UnableToConnectToDB
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied
Driver: MSSQL
Server: UKTRAINING
Database: CRM
LoginName: sa You may need to recreate views manually.

This is symptomatic of a database server that is not running. Can you connect using query analyser or other SQL tool?

If the database is already running.. stop and start the database service and retry.

Have you just restored a backedup database?

The error message can appear when you have a mismatch between database and CRM versions. For example you may get the message if you have restored an earlier Sage CRM version database to an install of a later version  of Sage CRM.

Is CRM trying to connect to the correct database?

Go to start\run\regedit\HKEY_LOCAL_MACHINE\SOFTWARE\eware\config click on the CRM beneath that and see the right panel see that default databaseserver name is exactly you have, if it is not, then change it manually. You'll need to do an IISRESET before you try and logon again.

Have you changed the database password?

UnableToConnectToDB
Login failed for user 'sa'
Driver: MSSQL
Server: UKTRAINING
Database: CRM
LoginName: sa You may need to recreate views manually.

This can be symptomatic of when the "sa" password was entered correctly but contains a special character, such as $, in the beginning.

Or more alarmingly it can be symptomatic of someone or something (e.g. a Virus, worm etc) changing the password on the database server.

Note:
If you need to change the "sa" password, the correct thing to do is to logon to CRM, change the database password under Administration>System>Database first, then reset the password in MSSQL, and finally reset IIS and logon to CRM again.

But if the database "sa" password has been reset first, then it will not be known to CRM, so you will not be able to connect to the database. Then you'll need to reset the password in MS SQL to the previous password.

But what if you don't know the database "sa" password? You will first need to reset the "sa" password to something you do know. Then you will have to reset the password in the CRM registry. (Backup the registry before doing this!!!).

In the registry browse to HKEY_LOCAL_MACHINE | SOFTWARE | eWare | Config | [CRM], where [CRM] is the name of the CRM instance.

You can set the DatabasePassword to the new database password in plain text. Do an IISRESET, then you should be able to logon to CRM. Once you have logged on to CRM go and reenter the new password and it will be saved back into the registry in encrypted form.

But how do I recreate views manually?

There is a view in the Sage CRM database called "vsentinel". When CRM first starts up (after an IISRESET) and it finds that vsentinel is not present, it will use this as the trigger to force the recreation of the views based on the definitions stored in meta data. Once the other views have been recreated it then creates the trigger view "vsentinel" again.

To force the recreation of the views do this:

1. Do IISRESET from the Run prompt.
2. Go to the SQL Manager. Take a backup of your CRM SQL Database first.
3. Now through SQL Manager itself, select the CRM SQL Database instance. There you will find an option Views which lists all the Views against it.
4. Select the user view "vsentinel". All CRM views are prefixed with ‘v’.
5. Press the Delete key from the keyboard and drop the view.
6. After the IISRESET, try to login to CRM and see whether you are able to log in to CRM now or not.
 
Have you been editing the Themes CSS files in Sage CRM?
 
This can seem surprising as it has nothing to do with the database and everything to do with a corrupt CSS file being in the Themes folder.
 
As part of developing a theme a cascading stylesheet will have to be created and edited that contains the style settings for the new look and feel that is being constructed.  But there is a section of the stylesheet that has to be preserved.  This is clearly marked in the existing stylesheets.
 
e.g.
 
C:\Program Files\Sage\CRM\CRM\WWWRoot\Themes\ergonomic.css
 
/*
  CRM GREEN CUSTOM SETTINGS
  DO NOT CHANGE FORMAT BETWEEN 'CRM SETTINGS' TAGS
  [CRM SETTINGS]
    [INITIALIZATION]
      //theme file version
      VERSION = 1.0
    [COLORS DEFINITION]
      //wsEmailclient
      COLORCAPTIONBLUE=#6795C3
      //TSearchEntryGroup.GetEditHtmlFromContent
...
    [FONTS]
      //WrbTriPage, WsEmailClient
      FONTFAMILY=MS P-Mincho
...
  [CRM SETTINGS]
*/
The values within this section maybe changed BUT the structure must be preserved.  If this section is corrupted or is absent in any css file contained in the Themes folder
 
C:\Program Files\Sage\CRM\CRM\WWWRoot\Themes\
 
then it will generate the 'You may need to recreate views manually' message when someone tries to first logon after IIS has been reset.  It does not matter is the CSS file is not referenced in translations. All CSS files in the folder are scanned.  The origin of the error may be shown in the system log in this case.

So What happens if I still can't log on?

Remember this was not an exhaustive article. Don't panic. Think about what has just changed in the system? Have you applied a new component? Can you restore to the prior state? Have a good look through the forums and knowledge base and then log a case with support. It is almost certain that your problem has been seen before and that support will know what to do.

I hope that this article has helped.

If you have further hints and tips based on your experience then please feel free to post them as comments to this blog article.
Comments
  • I am alarmed that the sa password is the the one being used to login to the server. It is an administrative password only and should never be granted to an application. This is an example of elevated priveledge and is a security threat.

  • what if im upgrading from CRM 6.2 to CRM 7.0 what would be the solution to such error: you may need to recreate views manually ?

  • I've installed CRM 7.1a on Windows Server 2008 R2 (x64) machine (IIS is 7.5).  The installation was performed with Sample Database and worked fine.  But, when I change the database settings to connect to my real database, it gives an "You may need to recreate views manually" error.  The CRM 7.1a works perfect if I connect it to the same database from within Windows XP machine.  I think, the reason is in the IIS, but I tried approaches you suggested and nothing helps.

    Have you any idea, what can I do more to solve this issue?

  • The first port of call should be to check the CRM system logs - failing that, contact your friendly neighbourhood CRM support team.

  • I am using crm 7.1 with sql server r2 2008. i got the same issue ' You may need to recreate views manually.' when i try to access from outside, but it works fine if i access in my local network (localhost).

    I have tried the suggestion above, reset iis and recreate view many times, but it still can't help me. So please give me some more advise on this.

    Regards.

  • I just had a new variation on this. The logs said "Stack/crc empty" and "SQLClasses->DatabaseLogin:ERROR:Illegal operation attempted on a registry key that has been marked for deletion" in the system and sql logs respectively. A good old IISreset fixed the problem. Posting this in case anyone else ever gets this.

  • Caili, Thanks for the share!  My hunch was that was something to do with AppPool.  

  • I had this, annoyingly, on my OWN account.  

    I traced it to a bad login query (in the sql log) that would probably time out (but take ~90s to run via SSMS):

    SELECT * FROM ( select * , ROW_NUMBER() over(ORDER BY  comm_datetime, Comm_Communicationid) AS rowranking  from vListCommunication  WITH (NOLOCK)  WHERE ( CmLi_Comm_UserID=27) AND ((COALESCE(Comm_TargetListID, 0) = 0) or (cmli_Comm_CompanyID =-1)) AND comm_type <> 'RecuMaster' AND  ((COALESCE(Comm_Private, N'') = N'') OR ((COALESCE(Comm_Private, N'') <> N'') AND (CmLi_Comm_UserId = 27)))  ) as A  WHERE rowranking > 0 and rowranking  Log In and Session Preferences -> Log Me In To .

    Anyways, based on all of the above, the original message was more that it couldn't handle the necessary login tasks (due to timing out), and it was fortunately a matter of figuring out what was supposed to happen based on existing login preferences.

  • Sorry, a good chunk of my comment got left out.  Should be:

    ...as A WHERE rowranking > 0 and rowranking

  • ... Well, you get the gist.

    Looking that UserID up in the UserSettings table showed that UserID=27 had NSet_LoginTo (USet_Key) = “todo” (USet_Value), and was the only one to be like that. Manually changing it to “MyEWare” solved the issue and allowed a successful login. This corresponds with User Preferences / Log In and Session Preferences / Log Me In To Dashboard.

  • Thanks for the feedback.

  • Jeff,

    I have a question related to views in CRM 7.3.  I'm creating a view via the Administration > Customization > Primary Entity Path for reporting/gadget purposes.  I noticed by observing some of the native sql scripts in this area of the system that aliases are not used.  My issue is that the view was written in SSMS (Sql Server management studio) and is written in such a way that it joins on the company table twice.  Additionally, i'm using a Case statement in my Select clause so i need the alias to distinguish it from the other columns in my query.  Should I just re-write the view?   Is it not possible to insert a view into CRM that cause on the same table twice?  Any assistance you can provide would be greatly appreciated.  

  • Greg

    I think that question could be best posted in the forums.  But as for the use of alias then Sage CRM is happy for both column and table aliases to be used.  Have a look at the Company view vAtRiskCustomers.