extended properties on upsizing

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

Other Forums

MS Access

extended properties on upsizing

Author

Topic

jleiker
Starting Member

13 Posts

Posted - 2005-10-11 : 15:32:53

server error....object is invalid. extended properties are not permitted on "whatevertable" or the object does not exist.

I'm getting a TON of these errors when trying to upsize from ms access 2003 to SQL server on backend tables. What is this error? I'm not sure how to fix exactly or know what to look for?

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-10-12 : 12:39:16

Could be that there is a relationship between it and another table in the extended propertys. This of course will not be existing when the table is created.
Most of the time it is simpler just to Build the table and use bulk import than the wiz.

Jim
Users <> Logic

jleiker
Starting Member

13 Posts

Posted - 2005-10-12 : 13:33:14

where would I find the extended properties then? I just can't tell from the code what the issue(s) are? I'm attaching the error if anyone has any input on it? I'm not sure where to start?

EXEC sp_addextendedproperty N'Name', N'ACCOUNTNUMBERTBL_local', N'user', N'dbo', N'table', N'ACCOUNTNUMBERTBL'

EXEC sp_addextendedproperty N'Updatable', N'True', N'user', N'dbo', N'table', N'ACCOUNTNUMBERTBL'

EXEC sp_addextendedproperty N'DateCreated', N'9/28/2000 7:30:00 AM', N'user', N'dbo', N'table', N'ACCOUNTNUMBERTBL'

EXEC sp_addextendedproperty N'LastUpdated', N'10/11/2005 12:34:46 PM', N'user', N'dbo', N'table', N'ACCOUNTNUMBERTBL'

EXEC sp_addextendedproperty N'Attributes', N'0', N'user', N'dbo', N'table', N'ACCOUNTNUMBERTBL'

EXEC sp_addextendedproperty N'RecordCount', N'4', N'user', N'dbo', N'table', N'ACCOUNTNUMBERTBL'

EXEC sp_addextendedproperty N'MS_OrderByOn', N'False', N'user', N'dbo', N'table', N'ACCOUNTNUMBERTBL'

EXEC sp_addextendedproperty N'MS_Orientation', N'0', N'user', N'dbo', N'table', N'ACCOUNTNUMBERTBL'

EXEC sp_addextendedproperty N'MS_DefaultView', N'2', N'user', N'dbo', N'table', N'ACCOUNTNUMBERTBL'

EXEC sp_addextendedproperty N'Attributes', N'2', N'user', N'dbo', N'table', N'ACCOUNTNUMBERTBL', N'column', N'Account Number'

EXEC sp_addextendedproperty N'CollatingOrder', N'1033', N'user', N'dbo', N'table', N'ACCOUNTNUMBERTBL', N'column', N'Account Number'

EXEC sp_addextendedproperty N'Type', N'10', N'user', N'dbo', N'table', N'ACCOUNTNUMBERTBL', N'column', N'Account Number'

EXEC sp_addextendedproperty N'Name', N'Account Number', N'user', N'dbo', N'table', N'ACCOUNTNUMBERTBL', N'column', N'Account Number'

EXEC sp_addextendedproperty N'OrdinalPosition', N'0', N'user', N'dbo', N'table', N'ACCOUNTNUMBERTBL', N'column', N'Account Number'

EXEC sp_addextendedproperty N'Size', N'50', N'user', N'dbo', N'table', N'ACCOUNTNUMBERTBL', N'column', N'Account Number'

EXEC sp_addextendedproperty N'SourceField', N'Account Number', N'user', N'dbo', N'table', N'ACCOUNTNUMBERTBL', N'column', N'Account Number'

EXEC sp_addextendedproperty N'SourceTable', N'ACCOUNTNUMBERTBL_local', N'user', N'dbo', N'table', N'ACCOUNTNUMBERTBL', N'column', N'Account Number'

EXEC sp_addextendedproperty N'DataUpdatable', N'False', N'user', N'dbo', N'table', N'ACCOUNTNUMBERTBL', N'column', N'Account Number'

EXEC sp_addextendedproperty N'Required', N'False', N'user', N'dbo', N'table', N'ACCOUNTNUMBERTBL', N'column', N'Account Number'

EXEC sp_addextendedproperty N'AllowZeroLength', N'False', N'user', N'dbo', N'table', N'ACCOUNTNUMBERTBL', N'column', N'Account Number'

EXEC sp_addextendedproperty N'ColumnWidth', N'2295', N'user', N'dbo', N'table', N'ACCOUNTNUMBERTBL', N'column', N'Account Number'

EXEC sp_addextendedproperty N'ColumnOrder', N'0', N'user', N'dbo', N'table', N'ACCOUNTNUMBERTBL', N'column', N'Account Number'

EXEC sp_addextendedproperty N'ColumnHidden', N'False', N'user', N'dbo', N'table', N'ACCOUNTNUMBERTBL', N'column', N'Account Number'

EXEC sp_addextendedproperty N'MS_Description', N'Account Number of Positions for HR', N'user', N'dbo', N'table', N'ACCOUNTNUMBERTBL', N'column', N'Account Number'

EXEC sp_addextendedproperty N'MS_DisplayControl', N'109', N'user', N'dbo', N'table', N'ACCOUNTNUMBERTBL', N'column', N'Account Number'

EXEC sp_addextendedproperty N'MS_RowSourceType', N'Table/View/StoredProc', N'user', N'dbo', N'table', N'ACCOUNTNUMBERTBL', N'column', N'Account Number'

EXEC sp_addextendedproperty N'MS_RowSource', N'ACCOUNTNUMBERTBL', N'user', N'dbo', N'table', N'ACCOUNTNUMBERTBL', N'column', N'Account Number'

EXEC sp_addextendedproperty N'MS_BoundColumn', N'1', N'user', N'dbo', N'table', N'ACCOUNTNUMBERTBL', N'column', N'Account Number'

EXEC sp_addextendedproperty N'MS_ColumnCount', N'1', N'user', N'dbo', N'table', N'ACCOUNTNUMBERTBL', N'column', N'Account Number'

EXEC sp_addextendedproperty N'MS_ColumnHeads', N'False', N'user', N'dbo', N'table', N'ACCOUNTNUMBERTBL', N'column', N'Account Number'

EXEC sp_addextendedproperty N'MS_ListRows', N'8', N'user', N'dbo', N'table', N'ACCOUNTNUMBERTBL', N'column', N'Account Number'

EXEC sp_addextendedproperty N'MS_ListWidth', N'0twip', N'user', N'dbo', N'table', N'ACCOUNTNUMBERTBL', N'column', N'Account Number'

EXEC sp_addextendedproperty N'MS_LimitToList', N'0', N'user', N'dbo', N'table', N'ACCOUNTNUMBERTBL', N'column', N'Account Number'

EXEC sp_addextendedproperty N'MS_IMEMode', N'0', N'user', N'dbo', N'table', N'ACCOUNTNUMBERTBL', N'column', N'Account Number'

EXEC sp_addextendedproperty N'MS_IMESentMode', N'3', N'user', N'dbo', N'table', N'ACCOUNTNUMBERTBL', N'column', N'Account Number'

EXEC sp_addextendedproperty N'UnicodeCompression', N'True', N'user', N'dbo', N'table', N'ACCOUNTNUMBERTBL', N'column', N'Account Number'

jleiker
Starting Member

13 Posts

Posted - 2005-10-12 : 13:40:15

Yes you know what ...there is for account number and some others now that I'm reviewing your reply....we have a lot of 'little' tables that are referenced from this employee table. So......I really need to use the upsizing wizard ....we have tons of tables and a lot of joins etc. etc. Only upsizing the backend right now.
Any other suggestion on getting around this? I assume the same will happen on the related tables?

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-10-12 : 13:45:11

OK I will try again.

You will not be able to use "upsize" to do tables with these errors.

You need to build the SQL table first then Bulk import the data from the .mdb table.

Note: I have not seen the Upsize wizard work for any but the simplest DBs and then it would drop things like the primary-key.

The "relationships used in access are not realy needed in SQL as you can define it on all SPs Views and Functions.

Jim
Users <> Logic

jleiker
Starting Member

13 Posts

Posted - 2005-10-12 : 13:52:54

Ok...what if I turn off the 'preserve table relationships' option or whatever that is? Then re-create the relationships after I'm done?

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-10-12 : 14:01:42

You can try.

I get the feeling someone told you this would be a simple transition, its not but is worth all the effort when you are done.

The point is that all the relationships that you needed in Access to build Query’s are not really needed in SQL.

You actually tell SQL the relationship as part of the "query". Look up inner and outer joins in BOL.

Also word of advice use stored procedures to access your data, do not go direct to the tables from the front end.

Jim
Users <> Logic

jleiker
Starting Member

13 Posts

Posted - 2005-10-12 : 14:36:20

I do have a db guy that will help. We have over 100 tables and tons of data, and can't even tell you how huge the frontend is. Now I'm overwhelmed.

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-10-12 : 14:41:53

Don’t be discouraged you are making the correct choice for the long run.

The data is the easy part once the tables are set up you can use the bulk import function of SQL to bring it over.

My conversion was almost a 1000 tables and Query’s and I did it myself so I know how you feel.

Jim
Users <> Logic

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-10-12 : 14:48:46

I just Though of something that might work.

Create a DB in SQL with no tables.
Create an access .adp and connect it to that DB.
Now go into your .mdb and export the tables (one at atime) to the new .adp [these will now be SQL with no extended properties]

Note : You will have to go back and put in primary keys and default values before they are usefull.

Jim
Users <> Logic

jleiker
Starting Member

13 Posts

Posted - 2005-10-12 : 14:48:57

Ok you have humbled me! 1000 tables. Man.
Alright I'll start again here. thanks for your advice and input.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources