web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics SL (Archived) / Dynamics SL Database M...
Microsoft Dynamics SL (Archived)

Dynamics SL Database Maintenance - System dbs missing

(0) ShareShare
ReportReport
Posted on by J. Pippin 2

Have live db and test db.  Restored backup copy of live db as test db.  Updated Company & Domain tables appropriately.  Logged in to Database Maintenance 98.290.00.  Cannot see ANY system dbs - not even live db.  Any ideas?  Already checked Company and Domain tables.  All entries are valid.

*This post is locked for comments

I have the same question (0)
  • J. Pippin Profile Picture
    J. Pippin 2 on at

    Additional background info.  SQL 2008 R2.  Dynamics SL 2011 SP1.  SQL authenitcation for all dbs.  Logging in to Database Maintenance with SQL authentication as sa.  Login successful.  Message appears at bottom of Database Maintenance as "Connected to SOL-SQL (ip address here) as sa".  

    Go to Update Databases tab and nothing shows in System Database Name.

  • J. Pippin Profile Picture
    J. Pippin 2 on at

    Okay, since I'm not having any luck with the Database Maintenance program, does anyone out there know which scripts actually run when Update Views is selected in the Database Maintenance?

    I've found a possible listing of scripts in the DBBUILD.INI file, but I'm concerned that some of these are not appropriate.  For example, \Scripts\App\appTables.sql appears to be one of the scripts to run, but if I do it will rebuild all of the tables in my play data.  Not something I want to happen.

    [MSSQL System Scripts]

    Tables=Scripts\Sys\SystemTables.sql

    Indexes=Scripts\Sys\systemindexes.sql;Scripts\Sys\70sIndexUpdate.sql

    Procedures=Scripts\Sys\systemProcs.sql

    Views=Scripts\Sys\systemViews.sql

    Triggers=Scripts\Sys\systemTriggers.sql

    Defaults=

    Constraints=

    Rules=

    Import=Common\Sys\CommonSysImport.lst

    ScriptPath=

    [MSSQL Application Scripts]

    Tables=Scripts\App\appTables.sql;Scripts\App\pjtext.sql

    Indexes=Scripts\App\appIndexes.sql;Scripts\App\appIndexUpdates.sql

    Procedures=Scripts\App\appProcs.sql

    Views=Scripts\App\appViews.sql;Scripts\App\quickqueryViews.sql

    Triggers=Scripts\App\AppTriggers.sql

    Defaults=

    Constraints=

    Rules=

    Import=Common\App\CommonAppImport.lst

    ScriptPath=

  • Community Member Profile Picture
    Community Member on at

    You found the correct place to determine what scripts are run.  However, your assumption about tables being recreated is incorrect.  If you look at the actual SQL script you will see that, for each table, there is an "if exists" qualifier for that table.  The script will only create the table if the table does not already exist.  Some scripts look for a particular field and will execute if that field is not found in the table.

  • J. Pippin Profile Picture
    J. Pippin 2 on at

    On the contrary...the if exists statement says that if this table exists, drop it and create a new blank table.  Just ran the script and that's what it did, as I thought it would.

    I'll be restoring from a backup.

    If you have any specific information on which scripts are run when performing Database Maintenance - Update Views, that would be great.

  • Stuart Nottingham Profile Picture
    Stuart Nottingham 1,226 on at

    I've got a client who's experiencing this. Have you found a resolution to it yet?

  • J. Pippin Profile Picture
    J. Pippin 2 on at

    No resolution yet, although I haven't given up trying.  Database Maintenance is still not displaying any database names - neither System nor Application.  I will update if/when I find a resolution.

    Last know successful use of Database Maintenance (98.290.00) program was 04/2014.  No major Dynamics SL updates between then and now - only YE tax update.  Live db is fully functional, except in this screen.  Test system and app dbs are restored backup copies of live system and app dbs.  Already updated Domain and Company entries in test system db.  Need to run Update Views in order to make test db fully functional.  Attempting to run scripts via SQL server as temporary workaround.

  • Community Member Profile Picture
    Community Member on at

    My apology.  When I went back and read your posting again I see that you referred to the apptables script and not the apptableupdate script.  I was referring to the table update scripts that checks for existence and only runs that portion of the script when the table and/or field does not exist.

    The apptable script is only run when creating a new application database and is not run under any of the update scenarios so I am not sure why you are concerned about that script unless you are using the create database tab.

    It sounds like you actual problem is that you restored the live database as a test database and the system is not seeing that test database.  You indicated that you added to the domain and company tables making entries for the second database.  Did you also run the synchronize all ownership and security process?  We you using SQL authentication or Windows authentication to connect to the database update function?

    I am assuming, by what you said, that this was on the same SQL server and not a whole new server instance or domain.  I have done what you attempted many times with no issues so there is something basic going on here if you cannot see your existing system database and you are connected to the same SQL server instance.  Screen 98.290 should be able to see your system database.  If you connected using Windows authentication, try SQL authentication.

  • J. Pippin Profile Picture
    J. Pippin 2 on at

    Using SQL authentication for everything.  Windows authentication never implemented.  All dbs on same SQL Server.  Database Maintenance connects to SQL server instance successfully.  Cannot see ANY databases - live or test.  Cannot perform either Update Views or Synchronize Ownership.  What I'd really like is to get Database Maintenance working correctly, but not finding any resolutions yet.

  • Suggested answer
    Community Member Profile Picture
    Community Member on at

    Let's step back and think this through a bit.  DB Maint looks for system databases on the SQL instance you specified.  When it finds system databases it then looks for application databases referred to in the system database.  So, if it is not seeing any databases then it is not recognizing your system database as a system database.  We need to figure out why that is.

    I am assuming that you can still get into SL and your live database on the same computer that is giving you the DB Maint issue.  Is that correct?

    What I do not know, for sure, is how DB Maint determines that a SQL database is a system database.  So, on a test system of mine I set up a SQL trace and launched DB Maint. and then looked at activity under database maintenance.  You might want to do the same.  Be sure you are running this out of the master database.

    One of the steps was to execute the following:

    SELECT

    dtb.name AS [Name]

    FROM

    master.sys.databases AS dtb

    ORDER BY

    [Name] ASC

    This gives the program a list of databases to look at.  When I run that statement I get a list of all my system and application databases.  When you run it, do you see your databases?

    The next process it runs is to check the compatibility level on each of the database names it finds.

    It then checks the status of the database to make sure it is active and not in exclusive use mode.

    I am wondering if, perhaps, your system database is in such a status that DB Maint. is ignoring it.  If you can and are willing to run a SQL trace I think you can walk through the steps and determine where the problem exists.

  • Suggested answer
    Community Member Profile Picture
    Community Member on at

    In order for Database Maintenance to see the System Database you will need to make sure that the following tables exist:

    SysDbTableList(1) = "access"

    SysDbTableList(2) = "accessdetrights"

    SysDbTableList(3) = "custom"

    SysDbTableList(4) = "domain"

    SysDbTableList(5) = "impgroup"

    SysDbTableList(6) = "intercompany"

    SysDbTableList(7) = "messages"

    SysDbTableList(8) = "oldcustom"

    SysDbTableList(9) = "pes"

    SysDbTableList(10) = "pvrec"

    SysDbTableList(11) = "registration"

    SysDbTableList(12) = "rptcontrol"

    SysDbTableList(13) = "rptruntime"

    SysDbTableList(14) = "screen"

    SysDbTableList(15) = "subxref"

    SysDbTableList(16) = "template"

    SysDbTableList(17) = "usergrp"

    SysDbTableList(18) = "userrec"

    If you are missing one of the above tables, Database Maintenance will not recognize it as a System database.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics SL (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans