Just tried your code on my system and it worked fine. Only difference is my connect string

$rConn = odbc_connect("DSN=systemdsn;", "usr", "pwd") ;

But if you are getting a connection, that shouldn't be it.

I am running from a browser so I changed the  echo "$t<br>";  and got a nice long list of table names.

If I add in the "DSN=" it gives me the Unable to Connect error message.  I'm truly stumped.  Not sure why this code isn't working on my end.  Are there any specific settings in the php.ini file that need to be set?  Or any of the System DSN settings that need to be set?

Anyone else?  Still stumped on this one.  Why would the odbc connection not pull any results what so ever?

A couple of things to check in Sage 100 ...

- Is "Enable ODBC Security within Role Maintenance" enabled (System Security | Security tab)

- If so, does the user's Role have correct ODBC permissions (Role Maintenance | ODBC Security tab)

Wedwards

I double checked, and the user's Role does have all of the check boxes checked under the ODBC Security tab.  

Wedwards

I double checked, and the user's Role does have all of the check boxes checked under the ODBC Security tab.  

One other thought is permissions. I have had difficulty getting PHP to work from machines other than the server where SAGE is installed. As long as I run IIS with PHP on the same serve I can access files fine. I have never been able to get permissions work when trying to run them from any other server.

We are a small company with only 25 seats so I get away with it.

Theoretically we should not be running IIS on the same server.

Even this doesn't pull a result....

                  $query = "Select 1 as test";

$rs = odbc_exec($rConn, $query);

odbc_fetch_row($rs);

echo odbc_result($rs, "test");

It makes me think there's some sort of setting somewhere that I'm missing.  Is there anything special in the php.ini file that I need to have set?   I thought I hit everything there, but maybe I didn't.  

Ok... perhaps I'm getting somewhere.  Looks like it's definitely permissions related:  

[ProvideX][ODBC Driver][FILEIO]Table is not accessible

You get that sometimes when you try to pull a works table or the table is locked for processing. Have you ever thought of creating an Access database with SQL Specific Pass Through Queries with the log-in coded in and then create a DSN for that database and PHP on that?

I was able to get past this by setting IIS and PHP up on the MAS server itself, and running the web site from that server.

https://support.na.sage.com/selfservice/viewContent.do?externalId=51211&sliceId=1

Does the ODBC server application or service need to be on to access odbc connection from another computer in the domain? 

No.  The CS-ODBC service is for improved speed when using the Advanced version.  All you need for ODBC is the workstation software installed, a persistent network connection, and appropriate permissions.

So, I should be able to read data from Sage on another server?  ie, if I wanted to show a page in PHP on a different server from the sage application server, but in the same domain, can I do that with the ODBC normal set up? 

Thanks

Chris

Yes, you should be able to read Sage 100 data given the pre-req's I mentioned.  For it to work with a service you have to use system DSN, and avoid mapped drive letters, but those details come out in testing.

Edit:

As added security / abstraction, setting up a linked server to mirror your ODBC data in a SQL Express install might be a good idea for a variety of reasons.

I have done this and it is amazing how fast things can go now.  I had a Sage 100 script that made a new UDT that took about 9 hours every day.  that was to allow Crystal to run against it with report creation within a few minutes.   I can make a similar table in Sql server from linked server mirror tables in just a couple of minutes.  The crystal reports our purchasers use runs against that table within seconds.    

I wish I had known this 5 years ago!

Thank you!

Chris