SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 pubs on 2012
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bodling
Starting Member

4 Posts

Posted - 03/20/2013 :  09:39:25  Show Profile  Reply with Quote
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

Edited by - bodling on 03/20/2013 09:41:20

robvolk
Most Valuable Yak

USA
15639 Posts

Posted - 03/20/2013 :  14:58:56  Show Profile  Visit robvolk's Homepage  Reply with Quote
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)
Go to Top of Page

bodling
Starting Member

4 Posts

Posted - 03/21/2013 :  05:07:23  Show Profile  Reply with Quote
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

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

Edited by - bodling on 03/21/2013 05:09:24
Go to Top of Page

robvolk
Most Valuable Yak

USA
15639 Posts

Posted - 03/21/2013 :  12:49:05  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page

bodling
Starting Member

4 Posts

Posted - 03/22/2013 :  03:40:43  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.44 seconds. Powered By: Snitz Forums 2000