Solved

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

Posted on 2014-07-18
13
208 Views
Last Modified: 2014-11-18
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
0
Comment
Question by:willie0-360
13 Comments
 

Author Comment

by:willie0-360
ID: 40206595
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
0
 
LVL 12

Accepted Solution

by:
Tony303 earned 250 total points
ID: 40206661
Hi Willie,

Seems a strange situation.

Can you do a bit of fault finding for us please.

1.
Can you create a view from scratch that works? 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?

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?

Thanks
Tony
0
 
LVL 35

Expert Comment

by:David Todd
ID: 40206947
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
0
 

Author Comment

by:willie0-360
ID: 40207835
@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
0
 

Author Comment

by:willie0-360
ID: 40208119
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
0
 

Author Comment

by:willie0-360
ID: 40208206
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
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 250 total points
ID: 40208212
Hi,

Do you have all the users in this database mapped to logins, ie
sp_change_user_login 'report'
should show orphaned users.

HTH
  David
0
 

Author Comment

by:willie0-360
ID: 40208364
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
0
 

Author Comment

by:willie0-360
ID: 40209754
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
0
 

Author Comment

by:willie0-360
ID: 40211573
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
0
 

Author Closing Comment

by:willie0-360
ID: 40211585
Both solutions are best solutions.  There is not a best solution since I would had not solved this without the other.
0
 

Expert Comment

by:priya r
ID: 40442053
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
0
 
LVL 35

Expert Comment

by:David Todd
ID: 40449224
Hi Priya r

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

Regards
  David
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now