I have made a view “my_view” from a SQL Server 2000 to a SQL Server 2005 (both Windows 2003 Server) using linked servers. A Navision 3.7 client is running on the 2000 server and I use the LinkedObject property on a new table called “my_view” of course to access the view (The LinkedInTransaction is of course disabled as it should be). This works fine when retrieving data but when I want to modify a record, I get an error message:
Statement(s) could not be prepared
Incorrect syntax near ‘UPDLOCK’. If this is intended as a part of a table hint, a WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.
I have now tried different solutions and it actually works when I make the view from another SQL 2005 Server and run this through a Navision 4.0 SP3 client (still using linked servers). But I need to access the view from the 3.7 client. Has anyone else experienced this problem and succesfully solved it? My guess is that it is a problem when connecting a SQL 2000 with a SQL 2005 and in this case, there isn’t much to do [:(]
Hmmm … I’m afraid I don’t know a solution for the problem, but maybe some workaround:
When updateting this “Linked Object”, would it be possible to create a TSQL statement (UPDATE …) within the 3.70 Client and execute it via MS ADO? I guess that when doing it the “normal” way via C/AL the C/SIDE is generating a wron query. So, if you could bypass C/SIDE and fire the UPDATE as you need it, would do the job?!
Yes I am also researching the solution you mentioned but I have not fully tried it out yet. The problem is that when Navision makes tables on the SQL server, it does not allows NULL data at all. To copy a record from, say, the Item table directly to the SQL Server is a lot of work and a very long query because every field has to be copied, and it is not very flexible because I need to hard-code the fields into the query which makes the solution very specific for each customer because they might have different table defintions. But certainly, this will be the answer if everythin else fails to work.
Actually I just ran into an issue where I had copied a view from one Database into another (on a 2005 server), and the view would not work. I eventually rememebered that the database I copied from was a 2000 DB restored into 2005. I check the TSQL, made a couple of changes for 2005 and it all worked, so I would pretty much think you are right that there would be problems linking 2000 views to 2005 with Navision.
It is a little strange because I can make the view, I can view the contents of the remote server from Navision 3.7 but I cannot modify the data.
Anyway, I made a workaround where I first copy the data to a local table and then use a SQL module we have made here to just transfer data directly through SQL calls. This works.
Thank you for your input. I think this case is closed now.