Cannot Reference Database.Tables Collection By TableName --?

I have been using SMO for a while with no issues... But I just installed the AdventureWorks sample (AdventureWorksDBCI), and when I try to reference a table by name I get a null value:

Code Snippet

db.Tables[7] //this works fine; I can access table props/methods

db.Tables["EmployeePayHistory"] //returns a null

a null value is returned. (not just tables ,but just about any SMO collection).This same syntax works great with many other databases.

This returns true, however:

Code Snippet

db.Tables[7].Name == "EmployeePayHistory" // returns true

So is there some kind of character encoding or something that I need to use when referencing one of these collection items by name?

Try with a schema also :

like this db.Tables["<schema>.EmployeePayHistory"]

that is : db.Tables["dbo.EmployeePayHistory"]

Check the schema name of EmployeePayHistory.

Thanks

Naras.

|||

Yes indeed, the schema was the issue.

I could not access the item in the collection using schema.tablename, but rather using this syntax:

Code Snippet

db.Tables["EmployeePayHistory","HumanResources"]

Thank you for your help!

Cannot install Database Diagrams on AdventureWorks Database

Error Message: Database diagram support objects cannot be installed because this database does not have a valid owner.

How do I create a valid owner for the database?

http://www.sql-server-performance.com/faq/sqlviewfaq.aspx?faqid=196

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Thank you. The compatibility is set to 2005 (90) already. This might be it though:

"The "no valid owner" issue usually comes up when databases are owned by SQL-authentication logins and are upgraded, detached/attached to another server, or restored from backup to another server. The SID (a large number) doesn't match any existing login on the new server, so the owner name remains whatever it was on the old server, but it is marked invalid. Explicitly setting the owner to a valid principal on the server solves the problem."

How do I explicitly set the owner to a valid principal on the server?

|||

TO THE TOP: The compatibility is set correctly. How do I explicitly set the owner to a valid principal on the server?

Cannot Install AdventureWorks database.

I downloaded AdventureWorksDB from the CodePlex website and tried to install it. When the install program tried to attach it to SQLEXPRESS, it gave the error (below). I've also tried attaching the database directly using the SQL Management Studio and got exactly the same error.

I'd never heard of having incompatible database versions for SQL Server before. Am I missing a patch for SQLEXPRESS or ?. Thanks.

=================================================================

The database 'AdventureWorks' cannot be opened because it is version 631. This server supports version 612 and earlier. A downgrade path is not supported.
Could not open new database 'AdventureWorks'. CREATE DATABASE is aborted. (.Net SqlClient Data Provider)

Server Name: HP-MEDIACENTER\SQLEXPRESS
Error Number: 948
Severity: 20
State: 1
Line Number: 1

Sounds like the one in Codepley had been attached to SQL Server 2008, you should post that as a bug to the appropiate persons.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

I got the following response from Microsoft Press that solves the problem:

================================

Thank you for contacting Microsoft Press.

Please make sure you are downloading the correct AdventureWorks database for SQL Server 2005. The CodePlex website contains two versions of the database, one for SQL Server 2008 and one for SQL Server 2005. If you are viewing the AdventureWorks database download release for SQL Server 2008 you must click “SQL Server 2005 SP2a” in the Releases box on the right hand side. You can also use the following link to download the SQL Server 2005 version directly at: http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004.

|||

I faced the same problem and your answer helped me too!

Thanks buddy!

|||

Same issue for me too. It's working now. Thanks very much for posting the answer.

|||Ditto. Fixed me too.

Cannot Install AdventureWorks database.

I downloaded AdventureWorksDB from the CodePlex website and tried to install it. When the install program tried to attach it to SQLEXPRESS, it gave the error (below). I've also tried attaching the database directly using the SQL Management Studio and got exactly the same error.

I'd never heard of having incompatible database versions for SQL Server before. Am I missing a patch for SQLEXPRESS or ?. Thanks.

=================================================================

The database 'AdventureWorks' cannot be opened because it is version 631. This server supports version 612 and earlier. A downgrade path is not supported.
Could not open new database 'AdventureWorks'. CREATE DATABASE is aborted. (.Net SqlClient Data Provider)

Server Name: HP-MEDIACENTER\SQLEXPRESS
Error Number: 948
Severity: 20
State: 1
Line Number: 1

Sounds like the one in Codepley had been attached to SQL Server 2008, you should post that as a bug to the appropiate persons.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

I got the following response from Microsoft Press that solves the problem:

================================

Thank you for contacting Microsoft Press.

Please make sure you are downloading the correct AdventureWorks database for SQL Server 2005. The CodePlex website contains two versions of the database, one for SQL Server 2008 and one for SQL Server 2005. If you are viewing the AdventureWorks database download release for SQL Server 2008 you must click “SQL Server 2005 SP2a” in the Releases box on the right hand side. You can also use the following link to download the SQL Server 2005 version directly at: http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004.

|||

I faced the same problem and your answer helped me too!

Thanks buddy!

|||

Same issue for me too. It's working now. Thanks very much for posting the answer.

Cannot Install AdventureWorks database.

I downloaded AdventureWorksDB from the CodePlex website and tried to install it. When the install program tried to attach it to SQLEXPRESS, it gave the error (below). I've also tried attaching the database directly using the SQL Management Studio and got exactly the same error.

I'd never heard of having incompatible database versions for SQL Server before. Am I missing a patch for SQLEXPRESS or ?. Thanks.

=================================================================

The database 'AdventureWorks' cannot be opened because it is version 631. This server supports version 612 and earlier. A downgrade path is not supported.
Could not open new database 'AdventureWorks'. CREATE DATABASE is aborted. (.Net SqlClient Data Provider)

Server Name: HP-MEDIACENTER\SQLEXPRESS
Error Number: 948
Severity: 20
State: 1
Line Number: 1

Sounds like the one in Codepley had been attached to SQL Server 2008, you should post that as a bug to the appropiate persons.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

I got the following response from Microsoft Press that solves the problem:

================================

Thank you for contacting Microsoft Press.

Please make sure you are downloading the correct AdventureWorks database for SQL Server 2005. The CodePlex website contains two versions of the database, one for SQL Server 2008 and one for SQL Server 2005. If you are viewing the AdventureWorks database download release for SQL Server 2008 you must click “SQL Server 2005 SP2a” in the Releases box on the right hand side. You can also use the following link to download the SQL Server 2005 version directly at: http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004.

|||

I faced the same problem and your answer helped me too!

Thanks buddy!

|||

Same issue for me too. It's working now. Thanks very much for posting the answer.

|||Ditto. Fixed me too.

cannot install AdventureWorks

I downloaded and installed SQL Server 2005 express Edition with advanced services.

My attempt to install the AdventureWorks from "Add or Remove Programs" failed for I was not able to see the "Documentation, Samples, and Sample Databases" node from "Feature Selection".

After reading a little bit I discovered that I had to download AdventureWorks from http://msdn2.microsoft.com/en-us/librry/ms144235.aspx

First I downoladed and installed SqlServerSamples.msi and then AdventureWorksDB.msi however I cannot find AdventureWorks.mdf to attach to my instance? Inside the folder C:\Program Files\Microsoft SQL Server\MSSQ.1\MSSQL\Data I have AdventureWorks_Data.mdf and AdventureWorks_Log

Can you please help?

thanks

Rick

Instead of installing AdventureWorksDB I installed AdventureWorksLT and I was able to attach the db at the first attempt.|||AdventureWorks_Data.mdf IS the AdventureWorks data file -the one to attach to your server. (And you will need the log file (*.ldf)|||

I had both files .mdf and .ldf but when I was trying to attch .mdf I was getting an error. I tried several times, I even uninstalled and re-installed a couple of times and this is the error that I got:

TITLE: Microsoft SQL Server Management Studio Express

Failed to retrieve data for this request. (Microsoft.SqlServer.Express.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)

CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf'. (Microsoft SQL Server, Error: 5123)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=5123&LinkId=20476

BUTTONS:

OK

|||Please verify that the two files are not READ ONLY.|||

nope, they are not.

By the way, I had the same problem on two separate PCs. One is running XP pro and the other one Vista. Both installation were fresh.

|||

Hi Rick,

Verify that your explicit user account has Full Control on both the data and log files. Permission via group membership is not enough, at least on Vista; if your name isn't on the file it fails to attach. Not sure what's happening on the XP system, but this will prevent things from working on Vista because of User Account Control.

Mike

|||Hi Rick,

I am not sure if you have solved the problem or not. But I ran into the same problem and found out that the problem was caused by permission to access the file.

When I installed SQL Server Express, I chose used both Windows Authentication and SQL Server's. I login using SQL Server authentication (sa). When I tried to run the attach database query, I ran into the same problem as yours. I then quit the database and re-login using Windows Authentication. Afterwards, I reran the query and succeeded.

Hope this helps.

Suparman Widjaja
DOOR3.COM
|||That worked!!! I was able to attach the AdventureWorks database by logging in to SqlServer Management Studio using Windows Authentication instead of "sa". Thanks.

cannot install AdventureWorks

I downloaded and installed SQL Server 2005 express Edition with advanced services.

My attempt to install the AdventureWorks from "Add or Remove Programs" failed for I was not able to see the "Documentation, Samples, and Sample Databases" node from "Feature Selection".

After reading a little bit I discovered that I had to download AdventureWorks from http://msdn2.microsoft.com/en-us/librry/ms144235.aspx

First I downoladed and installed SqlServerSamples.msi and then AdventureWorksDB.msi however I cannot find AdventureWorks.mdf to attach to my instance? Inside the folder C:\Program Files\Microsoft SQL Server\MSSQ.1\MSSQL\Data I have AdventureWorks_Data.mdf and AdventureWorks_Log

Can you please help?

thanks

Rick

Instead of installing AdventureWorksDB I installed AdventureWorksLT and I was able to attach the db at the first attempt.|||AdventureWorks_Data.mdf IS the AdventureWorks data file -the one to attach to your server. (And you will need the log file (*.ldf)|||

I had both files .mdf and .ldf but when I was trying to attch .mdf I was getting an error. I tried several times, I even uninstalled and re-installed a couple of times and this is the error that I got:

TITLE: Microsoft SQL Server Management Studio Express

Failed to retrieve data for this request. (Microsoft.SqlServer.Express.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)

CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf'. (Microsoft SQL Server, Error: 5123)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=5123&LinkId=20476

BUTTONS:

OK

|||Please verify that the two files are not READ ONLY.|||

nope, they are not.

By the way, I had the same problem on two separate PCs. One is running XP pro and the other one Vista. Both installation were fresh.

|||

Hi Rick,

Verify that your explicit user account has Full Control on both the data and log files. Permission via group membership is not enough, at least on Vista; if your name isn't on the file it fails to attach. Not sure what's happening on the XP system, but this will prevent things from working on Vista because of User Account Control.

Mike

|||Hi Rick,

I am not sure if you have solved the problem or not. But I ran into the same problem and found out that the problem was caused by permission to access the file.

When I installed SQL Server Express, I chose used both Windows Authentication and SQL Server's. I login using SQL Server authentication (sa). When I tried to run the attach database query, I ran into the same problem as yours. I then quit the database and re-login using Windows Authentication. Afterwards, I reran the query and succeeded.

Hope this helps.

Suparman Widjaja
DOOR3.COM
|||That worked!!! I was able to attach the AdventureWorks database by logging in to SqlServer Management Studio using Windows Authentication instead of "sa". Thanks.

Cannot in install samples.

When I try to install the x64 version of the AdventureWorks sample database I get an error indicating that the file is corrupt (data1.cab). Is there another version or somewhere else I can go to download the sample .msi files? Currently I am going to http://www.microsoft.com/downloads/details.aspx?FamilyId=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en and downloading SqlServerSamples_x64.msi and AdventureWorksDB_x64.msi.

Thank you.

Kevin Burton

rkevinburton@.charter.net

Hi Kevin,

You're right, there is a problem with that .msi file. We're working on a fix this week and should have the site updated with the corrected file in a few days. For more information, see the Sample blog on the same subject here: http://blogs.msdn.com/bonniefe/archive/2006/04/21/580777.aspx#comments

Regards,

Gail

|||

This problem has been fixed. Please give it another try.