pubs on 2012 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

pubs on 2012

Author

Topic

bodling
Starting Member

4 Posts

Posted - 2013-03-20 : 09:39:25

I'm a novice C# programmer and the book I'm following uses the microsoft pubs database (which can be downloaded here: javascript:hyperlink(http://www.microsoft.com/en-us/download/details.aspx?id=23654);, since I'm using SQL Server 2012 I need help converting the SQL 2000 file to 2012. It is incompatible with 2012.

======================================================================
Here is what I've done:
Try to upgrade the .mdf: [instructions from here]javascript:hyperlink(http://msdn.microsoft.com/en-us/library/hh873188.aspx);
In Visual Studio 2012 I
1. added a connection
2. Data source: Microsoft SQL Server(Sql Client)
3. Server name: (LocalDB)\v11.0
4. Attach the database file
5. Give it a logical name
6. press the OK button.
Then it askes do you want to upgrade the database file now? Yes.
Output:
Database 'PUBS' cannot be upgraded because its non-release version(539) is not supported by this version of SQL-Server..... You must re-create the database.

Try to attach the file in Microsoft SQL Management Studio
Output:
Database 'pubs' cannot be upgraded because its non-release version(539) is not supported by this version of SQL-Server..... You must re-create the database.

Searched google for converting pubs to SQL Server 2012 compatability
======================================================================

Thanks in advance
bodling

robvolk
Most Valuable Yak

15732 Posts

Posted - 2013-03-20 : 14:58:56

Don't use the MDF files. When you run the MSI package on the first link it creates 2 SQL scripts in the same folder, you want instpubs.sql. Before you run it, find the line that has execute sp_dboption 'pubs' ,'trunc. log on chkpt.' ,'true' and remove it or comment it out. The rest of the script should run fine (well, it works on my machine)

bodling
Starting Member

4 Posts

Posted - 2013-03-21 : 05:07:23

Here is what happens when I comment out the line "execute sp_dboption 'pubs' ,'trunc. log on chkpt.' ,'true'":
=================================================================================================================================
Error 1 Could not locate entry in sysdatabases for database 'pubs'. No entry found with that name. Make sure that the name is entered correctly. C:\SQL Server 2000 Sample Databases\instpubs.sql 46 5 Miscellaneous Files
Error 2 Column 'au_id' has an invalid data type. C:\SQL Server 2000 Sample Databases\instpubs.sql 69 19 Miscellaneous Files
Error 3 Column 'title_id' has an invalid data type. C:\SQL Server 2000 Sample Databases\instpubs.sql 117 19 Miscellaneous Files
Error 4 Column 'au_id' has an invalid data type. C:\SQL Server 2000 Sample Databases\instpubs.sql 146 19 Miscellaneous Files
Error 5 Column 'title_id' has an invalid data type. C:\SQL Server 2000 Sample Databases\instpubs.sql 150 19 Miscellaneous Files
Error 6 Column 'title_id' has an invalid data type. C:\SQL Server 2000 Sample Databases\instpubs.sql 189 19 Miscellaneous Files
Error 7 Column 'title_id' has an invalid data type. C:\SQL Server 2000 Sample Databases\instpubs.sql 201 19 Miscellaneous Files
Error 8 Column 'emp_id' has an invalid data type. C:\SQL Server 2000 Sample Databases\instpubs.sql 264 19 Miscellaneous Files

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

robvolk
Most Valuable Yak

15732 Posts

Posted - 2013-03-21 : 12:49:05

Make sure to run the entire script, there are some lines in there exec sp_addtype that need to run first. The only line you need to alter is the one I mentioned.

bodling
Starting Member

4 Posts

Posted - 2013-03-22 : 03:40:43

I executed the script with the curser at the "exec sp_addtype" don't know if its because of that but it ran successfully, and no errors. Yey... Now I have the pubs database running. An by your description it should be ok. Thanks alot for the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources