I want to read a SQL db from Navision using ADO. The db is the same db as the Navision client is connected to. The reason for this is to overcome the versioning principle. The background for this is that I have a “Master NAS” that starts and stops several other NAS-services. So both the master NAS and the “Slave-NAS” will update the same company-shared table in Navision (a table containgn information about NAS-services. It is the master NAS that must read the shared table with ADO to see if a slave is finished (indicated by updating a field in the table) with the execution. We do this because we only want to have max 2 NAS running at the same time. Of course it’s a license issue. I have no problem working with with the Northwind db from within Navision using ADO. But when I try to access the Navision db a don’t get any data in return. I can open the recordset, but I cant MoveFirst or something like that. It seems to be a permission issue, but the same user can access the Navision db with Query Analyzer. any ideas?
Hi Lars, Can you send the code piece that you used to connect to SQL DB? I think it is possible, in one of project, Through VB I was putting records in Item table using ADO. Thanks Param
ConnStr := ‘Provider=sqloledb;data source=dbsqlnav;database=Navision;Integrated Security=SSPI;’; CREATE(ADOConn); ADOConn.Open(ConnStr); CREATE(ADORst); ADORst.Open(‘Select * from AppServers’,ADOConn,2,3); ADORst.MoveFirst; (This is where it stops) But this works fine: ConnStr := ‘Provider=sqloledb;data source=dbsqlnav;database=Northwind;Integrated Security=SSPI;’; CREATE(ADOConn); ADOConn.Open(ConnStr); CREATE(ADORst); ADORst.Open(‘Select * from Orders’,ADOConn,2,3); ADORst.MoveFirst;
You could also program NAS to do CHANGECOMPANY and update the records that way.
quote:
ADORst.MoveFirst; (This is where it stops)
MoveFirst fails if the table is empty. From MSDN: A call to either MoveFirst or MoveLast when the Recordset is empty (both BOF and EOF are True) generates an error. Technical documentation | Microsoft Learn
quote:
You could also program NAS to do CHANGECOMPANY and update the records that way.
The table I’m updating is common for all companies. The problem is that NAS #1 updates a row with a start time and then waits for NAS #2 to update the same row with a stop time. When that happens NAS #1 stops NAS #2 and then the same thing happens with NAS #3 and so on. SELECTCURRENTVERSION does not seem to help. That is why I went down the ADO-track.
quote:
MoveFirst fails if the table is empty
Yes. And that’s the problem. The question here was why my recordset is empty when the table isn’t. It seems to me like permissions is missing. But the user executing is server admin
More input: If I make a copy of the table by creating it from Query Analyzer it works grate. It’s just the tables created from within Navisions that is a problem. Clearly a permission problem. but where…
I would expect a permission error (“SELECT permission denied…”, code 229) would be given from ADO if it is a permission problem. Does your user have their own AppServers table perhaps, created within their account and owned by them? Tables are scoped by users within a database so each user can have a distinct copy of a table with the same name - by they are totally different tables. By default an unqualified name uses the user’s own table. Navision’s tables are all owned by the db_owner role and are therefore scoped as dbo.tablename (at least if they have been created from within Navision). This is true of both global and per-company tables. Try changing the table name in your SELECT to dbo.AppServers and check for the presence of the AppServers table scoped by the connecting user.
Thanks Robert. You are quite right in Your assumptions. It seems as a reasonable solution, but I have allways been able to perform an OPEN-statement. I tried opening the table with the fully qualified name but no difference. I also added the user connecting to be a member of the db owner role, but no progress. I think You are right, but I must have some other problem also together with the owner thing. Regards //Lars
Problem solved! It’s a locking issue. The problem was that I first assigned a start datetime and modified the record. After that I tried to open the recordset and wait for the second NAS to set the stop datetime. If I commit after my modify it works just fine. Thank You all for Your time and effort.