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
John500Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Tax / Financial Software

From novice to tech pro — start learning today.