x Want to participate? Join the group to interact
Options
  • State Not Answered
  • Replies 9 replies
  • Subscribers 108 subscribers
  • Views 9989 views
  • Users 0 members are here
Browse Forums
Announcements
292
topics
370
replies
Business Object Interface
1k
topics
7k
replies
Core Financial Modules
2k
topics
8k
replies
Customization and Productivity
3k
topics
12k
replies
Distribution, Manufacturing, and Applications
2k
topics
7k
replies
General Discussion
1k
topics
3k
replies
Partner Cloud
43
topics
90
replies
Payroll, Time Tracking, and Job Cost
775
topics
1k
replies
Reporting and Analytics
960
topics
3k
replies
Sage CRM for Sage 100
164
topics
357
replies
Technical and Installation
2k
topics
9k
replies
Year-end Forum
89
topics
237
replies
Frank Fratzke

Using SQL Server Management Studio To Work With Sage 100

Posted By

Ok,

So I'm new to this forum, so I apologize in advance for any breach of protocol.

Basically, I have set up a DSN on my server running MS SQL, and it works fine.  I can see all of my tables (but not fields) for Sage 100 Advanced ERP in SSMS - Great! However, it appears that I can't use the typical functionality of SSMS to see the fields in the database, or to build new views for the Sage ProvideX tables, but that I must actually write the SQL code (which does work).  I'm using Sage Advanced ERP 2013 (Upgrading to 2016 soon), and SQL Server Managament Studio 2014.  My DSN and SQL are both 64-Bit.

My question, is this typical with a Server Linked Sage database?  Part of the reason I wanted to use SSMS was to easily view the database and create the views graphically rather than having to write the SQL code manually.

My goal in creating the views is to create a subset of tables that I work with ALL the time in conjuction with custom Crystal Reports, and to speed up those reports.  In particular, some of the tables I regularly report on include AR_InvoiceHistoryHeader, HistoryDetail, CI_Item, ProductLine and so on.

I am a Crystal Reports manic, but I admit, I'm pretty new to SQL, so any observations, suggestions, or questions would be welcome!

THANKS everyone.

  • 0
    Sadly your reports are not going to magically speed up if you use SQL views. I have gone down this road. Unless you replicate (copy) the Providex database to an MS SQL database, but that is a different scenario.
    You should be able to see the tables in SSMS. However a far better option is using MS Access and linking tables to an Access database.
    Use the SOTAMAS90 ODBC DSN when you write Crystal Reports. Link tables in the order of Detail > Header, not the other way around - for some reason this performs better.
  • 0
    Users who use SQL replicate the entire table into SQL and then base their reports off those tables and NOT the SOTAMAS90 DSN.
    Others do the same using Access.
  • 0 in reply to BigLouie

    Ok, that makes sense.  But here's where I show my ignorance - what the best way to "replicate" either To SQL, or To Access.  I am aware of SWK's MASTransit tool, but are there any do it yourself steps.  VI export?  Any way to schedule replications?  THANK you Sir!!

  • 0 in reply to Frank Fratzke
    I like to use Access. I usually built SQL Specific Pass Through Queries and then create Make Tables queries with the Pass Through as the sources. I build some forms and code depending on if I want to refresh manually or have it done automatically at night.
  • 0 in reply to BigLouie
    Thanks to both "49153" and Big Louie. I'm still working on it and making progress. As I'm not an Access guru either, I found a great article on "Make Table Queries" described above by BigL. Here's the link:

    support.office.com/.../Create-a-make-table-query-787763ba-a9e4-42c0-b09f-98c01014808e

    I'll update you once I'm 100% successful (or maybe even 98%!)
  • 0 in reply to Frank Fratzke
    If your data needs to be Live, go with MS Access and SQL Specific Pass Through queries.
    If your data can be end of previous day accurate, run "select into" queries overnight to move the Providex data into SQL Server, for the full performance benefits of server side processing with SQL Views.
    If you have really big data / reporting requirements that need to be Live, DSD sells a SQL mirroring enhancement.
  • 0 in reply to BigLouie
    Well guys, I'm at 98%. I set up the pass through query, pulled the data into Access and ran my Crystal Report from the new single table that combined the multiple tables I was previously using, and it is FAST!! The next 2% will be getting comfortable with Access programming, and setting up automatic / timed updates from Sage to Access. THANKS again to all.
  • 0

    Personally instead of using SQL Server, I use PostgreSQL because it's faster and cheaper (open source).  It also takes significantly less resources (I run it right on my SAGE 100 server).  To replicate the data, I wrote a program that allows you to replicate the table structures in PostgreSQL and then allows you to define how frequently you want each table updated.  Once the auto-replicate mode is selected, it runs continuously updating the tables once they have "expired" per your settings.  I have no problem sharing the source code.  It's a .NET program, though I wrote and compiled it in Sharp .NET, because it too is free and open source.

  • 0 in reply to mkaney

    I would be interested in taking a look if you want to share.

*Community Hub is the new name for Sage City