Options

Transferring Sys Tables: Invalid object name 'sysproperties'

jbsoundjbsound Posts: 35
edited September 1, 2006 7:51PM in SQL Toolkit Previous Versions
Am on the home run now. :)

After some initial bumps, I finally have an app going that exetensively uses the toolkit to make database transfers and updates for a client of ours.

However, I ran into an issue with some stored procedures not running properly on a database that I transferred using the toolkit.

Executing those troublesome SPs on the new database responds with an error "Invalid object name 'sysproperties'".

Looking at the System Tables in the new database and the old database, I can see that they are missing from the new database.

How can I transfer those, or recreate them?

Thanks,

JB
· Share on Twitter

Comments

  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hi JB,

    I'm not sure. Sysproperties is a system table which should already exist in the database. If you have a stored procedure that reads directly from sysproperties, you may have migrated from SQL 2000 to SQL 2005, which has a new system table schema (I think it's called sys.properties now)

    Possibly you could fix it by changing the compatibility mode property for the database to level 80 and make it backwards compatibloe with SQL 2000.
    · Share on Twitter
  • Options
    jbsoundjbsound Posts: 35
    Yeah, that's I found too. I was going from a SQL Server 2000 database to SQL Server 2005 Express.

    Initial tests showed that even changing the compatibility mode to 80 (SQL Server 2000) I still didn't see the system tables.

    May be a SQL Server Express limitation, or I just didn't set it up right. I'll have to check further into it.

    Thanks for your time and help again!

    JB
    · Share on Twitter
  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hi JB,

    That's a bit discouraging. If your stored procedures are querying system tables, more than likely you'll end up modifying the stored procedures so that they are also compatible with SQL 2005's information schema model.
    · Share on Twitter
  • Options
    jbsoundjbsound Posts: 35
    Not only that, but it creates an incredibly difficult scenario:

    The main database I have to synchronize with is SQL Server 2000 with SPs that query system tables. Synchronizing it to a SQL Server 2005 Full or Express will break quite a few things, since the system tables are not available.

    If on the SS 2005 side I create SPs querying system views instead of tables for the purpose of restoring functionality to my app, they will be removed during the synchronization process.

    So I would have to create the system view SPs in SS 2005 and then copy them back to SS 2000 so that during the next synchronization they are available again on the SS 2005 side.

    Am I complicating things unecessarily? Is there a way to tell the toolkit to leave SPs in place that only exist on SS 2005 but not on SS 2000?

    Again I'm going one direction, from SS 2000 to SS 2005.

    Thanks,

    JB
    · Share on Twitter
  • Options
    rgribblergribble Posts: 88
    If you know the names of them you could just set the "Selected" property of the difference item to False, and then when you synchronise it should leave those objects alone
    · Share on Twitter
  • Options
    rgribblergribble Posts: 88
    out of interest, for what reasons are you reading the sysobjects table etc in your procedures?
    · Share on Twitter
  • Options
    fordc03fordc03 Posts: 49 Bronze 2
    SQL 2005 has new views, that's correct, but you can still query the old style using select * from sysdatabases instead of doing:

    select * from sys.databases which is the new style.

    however, I wasn't able to find sysproperties in any of my databases, even the restored ones from SQL 2000 in compatibility mode.

    So, what exactly are you trying to sync up? Are you trying to sync up your database? Or are you trying to sync everything including the master database?

    Thanks,
    Christopher
    · Share on Twitter
Sign In or Register to comment.