• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 639
  • Last Modified:

Understanding the database owner role as it relates to database diagrams --> must be set on the entire database to activate the diagram feature.

Greetings,

I would like to use the Database Diagram feature of SQL 2008 as I have in previous versions.  I'm new to this company and for some reason the database properties have not been set to reflect the owner.  For whatever it is worth, this database is used with Microsoft Great Plains.

I'm concerned that if I set the owner incorrectly, it will throw off the permissions for any application that uses the database.  I'm finding it hard to believe the diagram feature is set up in such a way that it makes it so difficult to use it.

Any input here?  

Thanks

The picture below shows the error messages I get for the backup and production database servers.  I know the one message is harmless and if I say yes to it the diagram feature will be enabled.  I was just hoping to view diagrams using the backup server.....
database-diagram-messages.JPG
0
John500
Asked:
John500
  • 3
  • 2
  • 2
2 Solutions
 
Steve EndowMicrosoft MVP - Dynamics GPCommented:
Hi,

The SQL database owner for all Dynamics GP databases should be the DYNSA login.  If the database was migrated from another server as part of a server upgrade or other type of restore operation, it is possible that the DB owner was not reset after that process.

To change the owner, you can run this script against the Dynamics database and all company databases:

sp_changedbowner 'DYNSA'

If you receive errors with this script, try following the steps in CustomerSource KB Article 869278.

With that said, I don't know that the SQL Server database diagram feature will be of much value to you given Dynamics GP's database design.  There are no foreign key relationships or referential integrity rules on the GP tables, so the diagram will just be a picture of hundreds of tables.

If you want to see a more meaningful representation of the database tables, I would recommend looking at the Dynamics GP SDK, which contains a compiled help file with a Visio-style table diagram.

Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified Professional



sdk.jpg
0
 
John500Author Commented:
Steve,

Thanks.  I was thinking the same thing in regard to the restore operation.  That's exactly what's going on here.  Since I've posted this question, I realized the production server doesn't have a problem with diagrams, but the backup server does.  I believe the database on the backup is restored regularly.

In terms of the Dynamics GP SDK, I am new to this stuf and just now making a connection with the Dynamics database.  In other words, I notice that a database with that name exists but I haven't understood it's purpose.  If you could answer any or all of these questions, that would be great:

1)  Are you saying I should be using this database to understand our production database?
2)  Are you saying that our production database is somehow tied to the Dynamics database?  
3)  Where do I start (from a menu sequence standpoint) in order to start viewing GP table relationships as they pertain to our data?
4)  How about the Cogsdale end of it?  Are those table relationships displayed?

Thanks !
0
 
Steve EndowMicrosoft MVP - Dynamics GPCommented:
Hi,

The DYNAMICS database is a "master" database that keeps track of things that need to be shared across the multiple company databases.  For example, GP logins and company access permissions, the Activity table which shows who is logged in to which companies, and various other central system items.

Your production environment needs to have a DYNAMICS database, and your test environment needs to have a copy of that database as well.  However, when you restore your company database to your test environment, you don't necessarily need to refresh the DYNAMICS database at the same time.

If you don't have a Dynamics database present in a given SQL instance, then GP will not be able to run and will prompt you to run Utilities so that one can be created.

For learning the tables, you may want to start with the Resources window.  Go to Tools -> Resource Descriptions -> Tables.  From the Table Descriptions window, click on the "..." button.  The Table Names window will open.  Change the "Series" drop down to the module you want to learn about, then View By Table Physical Name.

The table names are prefixed by module, such as GL, RM (Receivables Management), PM (Payables Management), POP (Purchase Order Processing), etc.

The tables are numbered in a semi-logical sequence.  Generally the 10000 series tables are for "work" (unposted) transactions.  The 20000 tables (in some modules only) are for posted but "open" transactions (a payables invoice that has not yet been paid), and the 30000 tables are for "history" transactions.  40000 tables are typically setup / configuration tables, such as PM40000, which has the configuration options for Payables.

Within those blocks, you'll find header / line tables, ancillary tables like amounts/quantities, serial/lot tables, application tables, etc.

Don't worry about memorizing specific tables, just understand this structure and use the Resource Descriptions to look them up when you need to.

I'm not sure what "Cogsdale" is, I've never heard of that in the context of GP.  Is that your GP partner?  Or do you have customizations developed by Cogsdale?

In general, customizations are not documented within GP, so you'll need separate offline documentation for any custom tables or GP customizations.

Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified Professional


0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Victoria YudinOwner / Dynamics GP ConsultantCommented:
In addition to the excellent information Steve has provided here, this blog post by David Musgrave on finding table information for Dynamics GP might be useful to you: http://blogs.msdn.com/developingfordynamicsgp/archive/2008/10/05/finding-table-and-field-information-in-microsoft-dynamics-gp.aspx.
0
 
John500Author Commented:
Steve / Victoria,

Sorry so long responding on this.  I was swept in another direction.

Really aprreciate the feedback!  Steve wrote:

>>  For learning the tables, you may want to start with the Resources window.  Go to Tools -> Resource Descriptions -> Tables.

Would believe I'm not even sure what 'Tool's menu you are starting from.  Take a look at the picture below showing Start\Programs\GP\.  I'm assuming this is the launch point for the product, right?  I'm thinking you are referring to the 'Database Maintenance' menu?  If I'm wrong and I should be in GP, I don't see a Tools menu.  The picture on the bottom shows the GP interface.

If I'm supposed to be in the Database Maintenance utility, what Process should I choose when prompted?

Victoria,  I went to the article you provided and I'm in the same boat given the writer's first instruction:

>>  Open the Microsoft Dynamics GP window that contains the data you are interested
>>  and then select Tools >> Integrate >> Table Import to see the tables associated with the Dexterity Form.


Thanks in Advance for the help!
GP.JPG
0
 
Victoria YudinOwner / Dynamics GP ConsultantCommented:
Your frustration is fully justified - in GP 9.0 and prior there was a Tools menu off the main Toolbar in GP.  Starting with GP 10.0 most of the main menus are hidden behind the 'GP' menu, which is the Dynamics GP logo - the leftmost one of the second line of your second screenshot.  Clicking on that will give you a menu with Tools as one of the options.  Many of us 'assume' or skip that first navigation step of the GP button, plus since we may not know what version of GP you're asking about, we skip giving different directions for GP 9.0 vs 10.0.  Sorry about that.
0
 
John500Author Commented:
>>  Your frustration is fully justified ...

Thanks, I feel better!  I found the tool that you and Steve are pointing to.  I'm sure there is much more to read in the article you provided.  However, I think I have to back up further still and understand the relationship between GP and our Billing module/Add-in (Cogsdale).  In other words, I need to report on a particular table in Cogsdale but I don't understand why GP tables aren't holding this data.  Steve put it like this:

>> I'm not sure what "Cogsdale" is, I've never heard of that in the context of GP.  Is that your GP partner?  Or do you have customizations developed by Cogsdale?

I would say it is customizations for GP.  This is a Utilities company or evironment.  Thus, GP probably couldn't  handle the specifics of utilities and Cogsdale picks up the slack.  This leads to a number of questions about table relationships.  Since that's getting outside the scope of this question, I've started another one you guys might be interested in:

http://www.experts-exchange.com/Software/Industry_Specific/Financial/Great_Plains/Q_26152539.html

Thanks again for the help !!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now