[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 353
  • Last Modified:

How do I rebuild views after database restore

We recently changed out one of our core database apps. It was a hosted solution and the new one we are hosting. Rather than do a dirty data conversion we opted to rekey the data. Fair enough.
With the previous application, we had (literally) hundreds of custom crystal reports for pulling data easily. Before the migration we pulled the majority of the core reports for hard reference for the future and the service was discontinued.
They have provided a hefty *.bak file from MSSQL server which contained our old database. Our intention is to have it available with our old reports in case of an audit or we need to look up data we may not have reported out earlier.
I have restored the database to a MSSQL 2008 server and can see data in the tables, however if I select rows from the views there is no data available. Of course the reports are built off of the views so they are displaying no data either once relinked.
What am I missing in regards to the Views not populating?
Michael Knight
Michael Knight
1 Solution
check if the databasename before the table names in the queries in the views is correct (in case it has changed after the restore).

To change them, script all views as alter/create by rightclicking on the views in object explorer and execute the script after changing the invalid details.
Anthony PerkinsCommented:
It is difficult to say, I have an idea what may be causing the problem, but can you post a typical VIEW that is using tables that you know have data?  Preferably a simple one, as I suspect if we solve that, it will take care of the rest.
Michael KnightAuthor Commented:

in design view the tables do appear to have the same name, albeit without the 'dbo.' prefix


here's the query for the ARLineItem view:

SELECT     ARLineItemID, SiteID, OwnerID, ContractID, ARPaymentID, TransactionCodeID, TransactionCode, Description, TransactionDate, Amount, Balance, ApplyToLineID, 
                      Reference, GLAccount, PostedDate, NoLateFee, NoFinanceCharge, FinanceChrgApplied, CrdtCrdTransID, UserName, ApplyToChargeID, DueDate, UsageYearID, 
                      VoluntaryCharge, AssessmentID, TransferFromID, IsHistory, HistoryDate
FROM         dbo.t_ARLineItem
WHERE     (SiteID IN
                          (SELECT     SiteID
                            FROM          dbo.SiteSecurity
                            WHERE      (LoginName = CONVERT(varchar, SYSTEM_USER))))

Open in new window

the table t_ARLineItem does have data


you are correct please rezone as necessary.
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Views (except indexed ones) are no more than SQL.
Are there any custom function/procedures using in Views?
Are views encrypted?
Whats about security?
Also - check database options if they are the same (ex. DATEFORMAT).
 Could you script sample View and post here?

Anthony PerkinsCommented:
As I suspected you have orphaned users.  Here is the problem:
 WHERE      (LoginName = CONVERT(varchar, SYSTEM_USER))))

Run the following and you can confirm:
sp_change_users_login 'report'

The users that are displayed do not have the corresponding login.  Add the login back and you should be fine.
Michael KnightAuthor Commented:

There were two users reported out: 'Ray' and 'Queue'
I recreated those logins and tied Ray to his old schema (which I scripted out). Now your query doesn't produce any data but the Views are still returning 0 lines. I think you're probably right about this being a security issue but I don't know how to proceed.

"Are there any custom function/procedures using in Views?"
not sure, Like I said it was a database given to us by a previous service provider. We are no longer using them, but for our records they gave us a *.bak of the database. Sorry, I'm not really used to having to recreate a database that's not one I made so any pointers would be great.

"Are views encrypted?" -No

"Whats about security?"
Well there in lies the rub, when the database is restored all of the previous logins/schemas/roles are recreated with the database, but I'm not sure how those pertain to my current setup

"Could you script sample View and post here?"
Not sure what you mean here, rather than spam a long script do you mean to 'Script View as>>Create'? please advise
Michael KnightAuthor Commented:
Bah, My Bad guys

acperkins was partially correct, the problem was users. However not SQL logins. It must have been late last night, if you look at the query above it's selecting a SiteID from dbo.SiteSecurity (doh) since there was no translation for the currently logged in SQL user nor my Domain/user the query would obviously fail as I did not have permission to view any SiteIDs as far as the View was concerned
Simply adding my Domain/user to that table with appropriate SiteID's populated the View.

Thanks for the quick responses guys

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now