Import Database Schema Keys not imported as expected
Greetings,
When I use Import Database Schema on an SQLServer 2000 database project (from an SQL Server 2000 database server) none of the keys, constraints or indexes are imported against the tables. They all appear to be there but are created as objects at the same level as the tables (i.e. under the Tables folder in the tree view) instead of in the appropriately named folders under each table.
Everything looks good in Query Analyzer and even if I target SQL Server 2005 the import is the same.
All these objects have a red exclamation mark but no errors appear in the error list. Also all my stored procedures and user functions have a red exclamation mark but no errors appear in the error list either.
Furthermore I can't drag & drop to move the keys etc. into their correct locations in the tree view and hand-editing them individually is a daunting prospect.
I have no idea why this database behaves this way as a much larger database, on the same server, appears to import the keys etc. into the correct locations in the tree view.
--
Regards,
Neale NOON
[1123 byte] By [
noonie] at [2008-2-25]
Neale,
Is the database that you are trying to import has different collation to that of database that you could successfully import? If so, as a quick test, can you unload and reload the project and see if it validates the objects correctly? This will help us with narrowing down the issue's root cause.
Thanks!
Krishna
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Greetings Krishna,
All affected databases have the same collation which is also the database default.
Collation=SQL_Latin1_General_CP1_CI_AS,
SQLSortOrder=52,
I will try recreating the project without making any changes and see what happens when I close the solution and reopen it.
--
Regards,
Neale NOON
I am having the same problem as Noonie.
My primary SQL 2000 database fails to import correctly. The indexes, foreign keys, etc are treated as orphans. I tried several other, simpler SQL 2000 databases with fewer tables and they import correctly. As expected the Northwind database imports fine.
Is there some workaround that I can use? Without the ability to import a schema with 300 tables, the product will be worthless to me.
Need help!
Grandpa Dave
Greetings,
This bug in the parser may have something to do with what you are experiencing:-
http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=137843
Once I had changed the code to not use a column list on insert to a table variable the symptomns you describe went away. Also the error on opening the project also went away :-)
My guess is the parser encounters the error but swallows the exception on database import however chokes on the same error when parsing on opening the project.
--
Regards,
Neale NOON
Twenty out of twenty-two SQL 2000 databases had schemas that were importable. Of course, the most important two had the schema import problem identified above. It seems that these two databases have views that point to external databases within the same SQL instance. We use views to span partitioned databases. We also have user-defined functions that utilize these views.
I was able to re-create the invalid schema problem. These are the steps I used:
1) Create new test database from production schema with table structures only. Create SQL 2000 project and all is well.
2) Add views that reference external databases to test database and re-create project. Tables still OK but views are marked with red exclamation point.
3) Add UDFs that use the problem views and re-create project. Now tables are AFU with their indexes marked with red markers. Project is now useless.
Any help that I can get on this would be deeply appreciated. As a consultant, I am pushing my client to invest in Team Services for DB management as opposed to third party vendors.
