Sage 100

I have a customer on 2018.2 Standard

They are creating a simple GL Journal Entry Visual Integrator import ( I already did it in 30 seconds with CSV )  and want to attach to a SQL table. I believe they created a SQL view. When I attach I can see the fields and everything looks good until I try to test read ( or actually import ).

Their SQL person thinks it's because this table is actually Sage.vw_Current_Journal_Entry
I don't see a way to do anything except click and accept the table from the connection.

- Am I missing something
- Does VI / ODBC work when connected to a SQL view? 

I very rarely use anything but CSV for all of the above reasons which cause me to take on another project to debug the data source connection, etc.





------------------------------
Wayne Schulz - Schulz Consulting - 860-516-8990
------------------------------

The customer's SQL tech created another view that appears to work.

His feedback: 

Non-default SQL schema doesn't appear to work

Does anyone have a Sage link or explanation as to if/why this might be true? I am now tasked with asking Sage why it's not working. 

My guess is that Sage is going to give me the deer in the headlights....



------------------------------
Wayne Schulz - Schulz Consulting - 860-516-8990
------------------------------

If I'm right, blame the SQL admin who set up the SQL permissions to the database. 


------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------

Original Message:
Sent: 04-26-2019 09:35
From: Wayne Schulz
Subject: VI with ODBC from SQL

The customer's SQL tech created another view that appears to work.

His feedback:

Non-default SQL schema doesn't appear to work

Does anyone have a Sage link or explanation as to if/why this might be true? I am now tasked with asking Sage why it's not working.

My guess is that Sage is going to give me the deer in the headlights....



------------------------------
Wayne Schulz - Schulz Consulting - 860-516-8990
------------------------------

I think he changed the schema from SAGE to DBO

His comment is that he doesn't think the Sage VI / ODBC connection to a SQL tables works on anything but the default

------------------------------
Wayne Schulz - Schulz Consulting - 860-516-8990
------------------------------

Original Message:
Sent: 04-26-2019 09:45
From: Kevin Moyes
Subject: VI with ODBC from SQL

If I'm right, blame the SQL admin who set up the SQL permissions to the database.


------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------


Original Message:
Sent: 04-26-2019 09:35
From: Wayne Schulz
Subject: VI with ODBC from SQL

The customer's SQL tech created another view that appears to work.

His feedback:

Non-default SQL schema doesn't appear to work

Does anyone have a Sage link or explanation as to if/why this might be true? I am now tasked with asking Sage why it's not working.

My guess is that Sage is going to give me the deer in the headlights....



------------------------------
Wayne Schulz - Schulz Consulting - 860-516-8990

Not all ODBC has a schema segment (server\instance.database.schema.table).
It does not appear as though the Sage interface handles the special case with SQL and non-dbo schemas.  Changing the default schema for the login might help.
Someone savvy enough in SQL to want to separate out objects into different schemas should not be unfamiliar with this kind of complication.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------

Original Message:
Sent: 04-26-2019 10:09
From: Wayne Schulz
Subject: VI with ODBC from SQL

I think he changed the schema from SAGE to DBO

His comment is that he doesn't think the Sage VI / ODBC connection to a SQL tables works on anything but the default

------------------------------
Wayne Schulz - Schulz Consulting - 860-516-8990
------------------------------


Original Message:
Sent: 04-26-2019 09:45
From: Kevin Moyes
Subject: VI with ODBC from SQL

If I'm right, blame the SQL admin who set up the SQL permissions to the database.


------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON


Original Message:
Sent: 04-26-2019 09:35
From: Wayne Schulz
Subject: VI with ODBC from SQL

The customer's SQL tech created another view that appears to work.

His feedback:

Non-default SQL schema doesn't appear to work

Does anyone have a Sage link or explanation as to if/why this might be true? I am now tasked with asking Sage why it's not working.

My guess is that Sage is going to give me the deer in the headlights....



------------------------------
Wayne Schulz - Schulz Consulting - 860-516-8990

Security can definitely be an issue.  Also, VI does not like to see schemas in the data source.  This will usually result in the Error you are getting.  To get around it, after setting the view as your data source in vi, go ahead and select the fields you will be using, and assign them as you normally do in the VI Data tab.
Then, go to Tables - Modify, to edit the SQL Statement.

You should see something like the following:

SELECT   Sage.vw_Current_Journal_Entry.PostDate
                 ,Sage.vw_Current_Journal_Entry.SourceJournal
                 ,Sage.vw_Current_Journal_Entry.AccountKey
                  ,Sage.vw_Current_Journal_Entry.DebitAmt
                  ,Sage.vw_Current_Journal_Entry.CreditAmt
FROM Sage.vw_Current_Journal_Entry

Just copy the whole statement to Notepad, or some text editor.  Then Alias the table and schema (I'm using CJ as my Alias).  The above SELECT statement would look like the following:
SELECT   CJ.PostDate
                  ,CJ.SourceJournal
                  ,CJ.AccountKey
                  ,CJ.DebitAmt
                 , CJ.CreditAmt
FROM Sage.vw_Current_Journal_Entry AS CJ

Copy the revised statement back to your SQL Statement tab in VI.  You should be good to go.

------------------------------
Chris St. Amand
Arizona Accounting & Information Systems
------------------------------

Editing the query is a good thought... (although I don't think you need the "as" in the FROM line).

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------

Original Message:
Sent: 04-26-2019 10:12
From: Chris St. Amand
Subject: VI with ODBC from SQL

Security can definitely be an issue.  Also, VI does not like to see schemas in the data source.  This will usually result in the Error you are getting.  To get around it, after setting the view as your data source in vi, go ahead and select the fields you will be using, and assign them as you normally do in the VI Data tab.
Then, go to Tables - Modify, to edit the SQL Statement.

You should see something like the following:

SELECT   Sage.vw_Current_Journal_Entry.PostDate
                 ,Sage.vw_Current_Journal_Entry.SourceJournal
                 ,Sage.vw_Current_Journal_Entry.AccountKey
                  ,Sage.vw_Current_Journal_Entry.DebitAmt
                  ,Sage.vw_Current_Journal_Entry.CreditAmt
FROM Sage.vw_Current_Journal_Entry

Just copy the whole statement to Notepad, or some text editor.  Then Alias the table and schema (I'm using CJ as my Alias).  The above SELECT statement would look like the following:
SELECT   CJ.PostDate
                  ,CJ.SourceJournal
                  ,CJ.AccountKey
                  ,CJ.DebitAmt
                 , CJ.CreditAmt
FROM Sage.vw_Current_Journal_Entry AS CJ

Copy the revised statement back to your SQL Statement tab in VI.  You should be good to go.

------------------------------
Chris St. Amand
Arizona Accounting & Information Systems
------------------------------

Nice fix Chris!!

Jim

Sent from my iPhone. Please excuse any typos.

------Original Message------

Security can definitely be an issue.  Also, VI does not like to see schemas in the data source.  This will usually result in the Error you are getting.  To get around it, after setting the view as your data source in vi, go ahead and select the fields you will be using, and assign them as you normally do in the VI Data tab.
Then, go to Tables - Modify, to edit the SQL Statement.

You should see something like the following:

SELECT   Sage.vw_Current_Journal_Entry.PostDate
                 ,Sage.vw_Current_Journal_Entry.SourceJournal
                 ,Sage.vw_Current_Journal_Entry.AccountKey
                  ,Sage.vw_Current_Journal_Entry.DebitAmt
                  ,Sage.vw_Current_Journal_Entry.CreditAmt
FROM Sage.vw_Current_Journal_Entry

Just copy the whole statement to Notepad, or some text editor.  Then Alias the table and schema (I'm using CJ as my Alias).  The above SELECT statement would look like the following:
SELECT   CJ.PostDate
                  ,CJ.SourceJournal
                  ,CJ.AccountKey
                  ,CJ.DebitAmt
                 , CJ.CreditAmt
FROM Sage.vw_Current_Journal_Entry AS CJ

Copy the revised statement back to your SQL Statement tab in VI.  You should be good to go.

------------------------------
Chris St. Amand
Arizona Accounting & Information Systems
------------------------------

Thank you - this has been very helpful!

------------------------------
Wayne Schulz - Schulz Consulting - 860-516-8990
------------------------------

Once I get this working from the Visual Integrator menu - any words of wisdom on running it: 

a. From the command line -- I've done this before but unsure how to manage the login to the ODBC data source when they are using my Windows login.

b. Same question, but with Windows Scheduler

With both of the above my main question is authentication ( I have the command line from the KB which I've used previously )

------------------------------
Wayne Schulz - Schulz Consulting - 860-516-8990
------------------------------

Original Message:
Sent: 04-26-2019 11:07
From: Wayne Schulz
Subject: VI with ODBC from SQL

Thank you - this has been very helpful!

------------------------------
Wayne Schulz - Schulz Consulting - 860-516-8990
------------------------------

------Original Message------

Once I get this working from the Visual Integrator menu - any words of wisdom on running it: 

a. From the command line -- I've done this before but unsure how to manage the login to the ODBC data source when they are using my Windows login.

b. Same question, but with Windows Scheduler

With both of the above my main question is authentication ( I have the command line from the KB which I've used previously )

------------------------------
Wayne Schulz - Schulz Consulting - 860-516-8990
------------------------------

Click the key icon on the first tab of the VI job and you can store the SQL login / password there.  Beware that if authentication fails, these values are included in a pop-up error message as plain text.  Set up a read only SQL login just for VI so you don't inadvertently share a secret password (if the database server is somehow unavailable).
DSN used is needed on the workstation / machine running the import.  Configuration uses the Server DSN, but run-time uses the client DSN's.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------

Original Message:
Sent: 04-26-2019 11:20
From: Wayne Schulz
Subject: VI with ODBC from SQL

Once I get this working from the Visual Integrator menu - any words of wisdom on running it:

a. From the command line -- I've done this before but unsure how to manage the login to the ODBC data source when they are using my Windows login.

b. Same question, but with Windows Scheduler

With both of the above my main question is authentication ( I have the command line from the KB which I've used previously )

------------------------------
Wayne Schulz - Schulz Consulting - 860-516-8990
------------------------------


Original Message:
Sent: 04-26-2019 11:07
From: Wayne Schulz
Subject: VI with ODBC from SQL

Thank you - this has been very helpful!

------------------------------
Wayne Schulz - Schulz Consulting - 860-516-8990