Hi all, I’ve been frustrated in my attempts to getting a clean “quite” SQL statment sent from Navision to MS SQL database via ado AUTOMATION CONNECTION variable. At issue is when my SQL UPDATE command sends a record which may generate duplicate key, or nulls in required fields. This causes a SQL popup error response. I know I could attempt to write verification BEFORE sending the UPDATE, but I don’t want to have to code for any/all situations that might cause a SQL error reponse. I’d rather check AFTER to see if the record got inserted/updated. I am attempting to send numerous records to the database via an automated scheduled task, and I don’t want any popups to stall the process. Would love to hear anyone’s strategy for quietly submitting SQL statments and avoiding any popup responses. There must be somthing simple I am missing. Otherwise, would appreciate any tips/comments for potiental strategies. Cheers, Eric
What’s the difference? Either you check an end result and handle an error, or you check a prereq before you submit a query. You have to program it either way… Is there a reason why you can’t simply write some Navision code to do what you need?
What’s the difference? Well, the data is coming inbound in a text flat file and is being read in. I would have to code field validation for all positions, such as valid dates, numeric, nulls, garbage chars, etc … before I tried to insert it. Whereas in checking afterwards, I only need to query if record exists or if a change exists. If it failed I can just add it to a report which a human user can review in the morning. I may not have been clear, but the native database is the Navision database, not a MS SQL backend. That’s why I am doing this via automation ado object. Eric
So you’re in a C/SIDE Navision database, and you need to update data in an external SQL database? My point was that you’d have to program what happens after you get an error back from the ADO object. You still wouldn’t know what the problem is. I can see now that you’re not interested in WHAT went wrong, just that it WENT wrong and let the user know about it. This depends on the return value of the ADO object, or if the object has an error event or someting. I haven’t worked with ADO myself, so I’m afraid I can’t help you out with the details there. What I would do is probably turn the process around. So try to retrieve a record with the same value as what you get in the text file, and then create/modify accordingly, maybe code the comparison before you submit the query. I still think that you should consider programming the validation. During the time it took you to write this post, you could have written a number of datatype validation functions.
Why use ADO? Can’t You set up the server as a linked server and build views in the Navision database and tables in C/SIDE that You link to the views. Then You can work with the data that resides outside the Navision db as if it was inside the Navision db.
Eric can’t use SQL views because he is not on SQL.
Then he has a good reason to migrate to SQL [:)]
Carmody, really you have two options, they are listed below, either move to SQL, or do What Daniel suggests.