MSSQL Database problem!URGENT

[riverdusty]

Hi

I have an urgent problem. for some reason, and without intervention, I now get an error when going to sugarcrm. No login page or anything, just the error:
"SQL Error : Invalid object name 'currencies'.SQL Error : Invalid object name 'config'."
mssql 2005, SugarCRM 5.x running on IIS on server 2003.
This is my live installation and I need it up and running ASAP!!

Anybody had this before? know how to fix it? know how it broke like that?

[andopes]

It seems the tables currencies and config had been deleted from database.
Try to configure a clean installation, same version of the current one, then copy these tables from new instance to current one.
Hopefully it will fix the issue.

Regards

[riverdusty]

It seems the tables currencies and config had been deleted from database.
Try to configure a clean installation, same version of the current one, then copy these tables from new instance to current one.
Hopefully it will fix the issue.

Regards

I just queried the database. those tables exist and I can get to the data.
Just as a test I restored yesterdays database backup, but get the same error as above.
I'm assuming at this point that the database is fine and the problem is an access one.
Possibly permission related?

I'll try do a brand new fresh install of SugarCRM including creating a new empty database. I'll then import the data from the old one to the new one. See if that fixes anything and report back. In the mean time, more brain storming please? I'm running out of ideas.

[Angel]

Who is the owner of the tables in question?

It kind of sounds like the owner changed on some (or all) tables and the credentials that Sugar is using to connect does not have access to them.

[riverdusty]

Who is the owner of the tables in question?

It kind of sounds like the owner changed on some (or all) tables and the credentials that Sugar is using to connect does not have access to them.

The name, schema and permissions are identical for all tables. I don't think is a problem specific to those tables (Why it mentions them I don't know)

Interesting thing, if I try use the not working database (full of data ) I get: "login failed for user 'sa' " in my windows system logs. this happens for every time I attempt to connect to sugar from my web browser.

If I reinstall SugarCRM and allow it to create a new database, it works just fine. If i restore an old backup to the new database (to populate it with data of course!) the error in the original post occurs.

I've created a new database and it works fine, identical owner and permissions to the not working one.

Secondly, what is up with the views? I tried to import the data from the not working database to the working database but it can't import the views. I get errors in regard to email1, email2 and email_opt_out on all the views. It refuses to import data.
If i deselect the views and import all the tables (from not working to working database) it executes just fine. I can then log into sugar using the working database, but none of my data is available. It behaves as though the database is still blank.

[Angel]

Are you able to execute any SELECT statements if you login to Management Studio using the same credentials as Sugar? For example: SELECT * FROM config or SELECT * FROM contacts

Have you tried doing a DBCC CheckDB or CheckTable?

How much diskspace is available?

Also, check the actual MDF and LDF files and make sure that they are not configured as compressed files. I've seen people do this with other systems and it makes MSSQL act really funny.

[riverdusty]

Further info
Why doesn't sugar create those views during install? Pictures related
View of old database and newly created (by sugar's install) database, of the views specifically
Views
Preview during import
error if you try import

Deselect the views, and import succeeds.

[riverdusty]

Are you able to execute any SELECT statements if you login to Management Studio using the same credentials as Sugar? For example: SELECT * FROM config or SELECT * FROM contacts

Have you tried doing a DBCC CheckDB or CheckTable?

How much diskspace is available?

Also, check the actual MDF and LDF files and make sure that they are not configured as compressed files. I've seen people do this with other systems and it makes MSSQL act really funny.

SELECT statements worked no problem, read data no problem, logged in as the sugarcrm user.
DBCC didn't find anything wrong. I'm not an expert on syntax, so my use of DBCC and such is limited.

30Gig of diskspace on the drive. I think thats ok

MDF looks fine. LDF, for some reason, was associated with Irfanview... I fixed that... Problem still exists...

Anything else I can check?

[Angel]

I believe the views you are referencing are custom, user created views. I am not aware of Sugar using views for any purpose.

It looks like they've been bound to specific columns which might explain the problem. It is possible they were bound to tables from a previous version and the structure has since changed, hence the problem.

[riverdusty]

I believe the views you are referencing are custom, user created views. I am not aware of Sugar using views for any purpose.

It looks like they've been bound to specific columns which might explain the problem. It is possible they were bound to tables from a previous version and the structure has since changed, hence the problem.

Thats fine. if they're not part of SUGAR then its fine. So importing data without those views should work.
However, if I do import, excluding those views, sugar still thinks the database is empty.
If i restore the database from a backup, I get the error in the original post.

So the Views are not responsible for the original error. any further ideas?
DBCC didn't find anything wrong.