Link to home
Start Free TrialLog in
Avatar of willie0-360
willie0-360

asked on

Upgrading SQL 2000 to SQL 2008R2 -- Most Views Do Not Show Query Result

Experts:

I am trying to upgrade a database from SQL 2000 to SQL 2008R2.  I tried the Copy Database Wizard, but it errors out.  Next, I tried the backup-restore method.  

Most things seem to be fine with the backup-restore method.  I can query tables, see the stored procedures, logins, and so on.  However, most of the Views do not give any output when I run:

SELECT TOP 1000 [ViewName]
,[Number]
,[Address]
,[Address2]
,[City]
,[State]
,[Zip]
FROM [DabaseName].[dbo].[ViewName]

From two to three of the Views show output after running the above SELECT, but the rest do not show anything.

How can I get these Views upgraded from SQL 2000 to SQL 2008R2 Enterprise Edition.  This is a side-by-side upgrade, and not an in-place upgrade.

Thanks.
Willie
Avatar of willie0-360
willie0-360

ASKER

I checked the database for corruption, and I did not get any errors or messages indicating corruption.  

I have tried two different backups, and still the same results.

Please help!!!

Thanks.
Willie
ASKER CERTIFIED SOLUTION
Avatar of Tony303
Tony303
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi,

run a dbcc checkdb across the upgraded database. I'm expecting the need to run dbcc updateusage.

Have you changed the page verify option from torn page to checksum?

Have you changed the compatibility settings to SQL 2008R2?

Generally, after a restored, you need to update the stats.

HTH
  David
@Tony303:

I will go over the steps you suggest.  Just give me a couple of days since I have never worked with Views before.  I need to study how to create Views from scratch.  I am learning all about DBA as I go here.

I am also assuming that you want me to do this on the SQL 2008 R2 server and not on the SQL 2000 server.

@David Todd:

I have already done what you are suggesting, but the situation remains the same.  I tried querying the Views before bringing the database to see the SQL-2008-R2 features and after that.  

For anyone out there who is looking to do an upgrade, I will include the post-upgrade steps I took after the migration of the database:

USE DatabaseName;
GO

ALTER DATABASE [DatabaseName] SET COMPATIBILITY_LEVEL = 100;
GO

DBCC CHECKDB (DatabaseName) WITH DATA_PURITY;
GO

DBCC UPDATEUSAGE (DatabaseName);
GO

ALTER DATABASE [DatabaseName] SET PAGE_VERIFY CHECKSUM WITH NO_WAIT;

EXEC sp_msforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN;'

EXEC sp_refreshview N'dbo.ViewName';

EXEC sp_msforeachtable 'DBCC DBREINDEX (''?'','' '',100)';

ALTER DATABASE DatabaseName SET NEW_BROKER WITH ROLLBACK IMMEDIATE;



Thanks to both of you for responding.  It gives me hope to solve this problem.
Willie
Tony303:

This is an update to your suggestions.

1.
Can you create a view from scratch that works? Make one the same as one of the current failing views.

a) I created a View from the same table that one of the failing Views is created, and it works.  I created this View from scratch with no relationship whatsoever with the failing View.  The SELECT statement is different than the one failing.  In general, all of its T-SQL is different.

b) I do not understand what you mean by: “Make one the same as one of the current failing views.”

2.
Can you use the create view tool to make on of the broken views again, right click the view, Script view as, then create to. (you will have to rename the view). Does this work?

This did not work.   No output at all.

3.
How about going one step further than 2 above. Use the Drop and Create to for one of these broken views. Does this work?

This one did not work either.


Any more ideas?

Thanks.
Willie
I think I found out why these Views are failing.  For some reason, the SUSER_SNAME() is not returning the user when the View is queried.  

This is the select where the SUSER_SNAME() function is used:

SELECT     dbo.table1.*, dbo.table2.userid
FROM         dbo.table1 LEFT OUTER JOIN
                      dbo.table2 ON dbo.table1.CountryId = dbo.table2.[CNumber]
WHERE     (dbo.table2.userid = SUSER_SNAME())
GO


However, when I substitute SUSER_SNAME() by any of the users in the dbo.table2.userid column, for example with Peter, directly, it works:

SELECT     dbo.table1.*, dbo.table2.userid
FROM         dbo.table1 LEFT OUTER JOIN
                      dbo.table2 ON dbo.table1.CountryId = dbo.table2.[CNumber]
WHERE     (dbo.table2.userid = 'Peter')
GO


By entering Peter instead of SUSER_SNAME(), the Views work.

What could be causing this not to work with SUSER_SNAME() ?

Thanks.
Willie
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
David:

I ran the command you provided, but it refers to users that have and do not have logins to access the database server.  There were three orphaned users as I was expecting since I did not give them login access.  However, this query refers to users in the userid column in the dbo.table2 table.  For example, table table2 has the following columns:

userid         Name                CNumber       County
Peter           Peter Smith         235               Houston
John            John Doe             394                Boston
Mark           Mark Evans          943               York
Rose           Rose Allen            313                Yonkers



It is those users in column userid that the query is trying to get to.  Those users do not have access to the database and to the SQL 2008 R2 server.  I believe they are read by queries sent from an application.

Thanks.
Willie
I just had an update from the person in charge of the application that connects to this database.  

He says that we use a database on another server that has a table with IDs that give users access to the database.  This other database is a Microsoft Access database.

Now I have to go into the database server and migrate that also.  After that, he says that I need to set up an odbc connection from the server hosting the, let us call the database that we have been working on DB2008R2, to the database that has the table with IDs that gives users access to the database, let us call that other database SQLID database.

In other words, since the beginning of this thread, we have been working with getting the Views working on database DB2008R2.  Now, I just found out that there is another database running on a different server.  This database is SQLID for the sake of our goal here.  I need to migrate SQLID, an Access database, from SQL 2000 to SQL 2008R2, on a different or the same server than/as DB2008R2, and then establish an odbc connection.

Let me go to that server and check on that database.  I believe I will need help setting up that odbc connection.

Thanks.
Willie
Tony303 and David Todd:

I believe I can close this thread successfully.  

Tony303, you helped me point out with your suggestions on doing some fault finding that the method/function SUSER_SNAME() was not returning the current user.  Therefore, causing the Views to fail.

David Todd,  you helped me identify that it the users that SUSER_SNAME() was looking for were not in the same database as the Views, but in some other database.  You provided me with this clue with your suggestion on running the sp_change_users_login 'report' procedure.  

The Views are not properly working yet, but I believe we achieve our goal here since we identified the root of the problem.  The solution, in my opinion, would need a different thread.  This is because my task now is establishing the ODBC connection between the Access 2003 database that hosts those users, that the upgraded database needs for the Views to work, and the upgraded database on SQL 2008 R2.

Thanks both.
Willie
Both solutions are best solutions.  There is not a best solution since I would had not solved this without the other.
Hi

I have a stored procedure which gets executed through job, as a overnight process. The sp picks values from transaction table through aggregate queries and then inserted into a table. Null is handled by upated the variables with 0. We find that sometimes this process fails for some users and 0 is inserted into the table. If manually execute the procedure for the same user the next day, the data is getting udpated. It is Ms Sql Server 2008 R2.

What could be the problem?  

Thanks in advance

Priya
Hi Priya r

You need to ask a new question. And comment here with its link.

Regards
  David