Calling a SQL script or other solution?

Mindie

We have sort of integrated another SQL database with the NAV database.

When certain fields on the customer card are changed the customer record gets copied to a new table. A SQL script runs every 60 seconds to find these records and imports them into the other SQL database.

I would like this to happen much faster than 60 seconds. We can't make the increments and shorter than that. I was told we can't call the SQL script from NAV so what other ways can we reduce that time?

Mog

let met get this right

- you copy changed customers to kind of a queue table
- then every 60 seconds a SQL script runs and copies them to another database, and there 60 seconds is the limit?

if so, to decrease this limit, you could use NAS with a timer to check the queue table, and if NAS finds records, it starts processing them (maybe by calling a stored procedure on the other database, but you will need something to connect to it, ADO could help you with this)

Mindie

Yes that is correct.

Can NAS call stored procedures?

Mog

Not from scratch, but you can tell him. Take a look at ADO, maybe this could help you.

Waldo

It is possible, yes.
I described it here (and on my blog).

eknraw

I've used triggers to do something similar, taking customer information and pushing that into another SQL database that was replicated to SQL CE clients. That would be nearly instant. The downside being that if the trigger fails for whatever reason the transaction in NAV would also fail. The error messages kicked back to the client don't necessarily lend themselves to the fact it can't update a customer because the trigger just failed, so you'd have to be familiar with what a failure looks like. Permissions can also be a bit tricky. This solution wouldn't be for everyone.

Bill

kine

Or you can use something called "SQL Query Notifications" (e.g. http://www.simple-talk.com/sql/sql-serv ... ification/) which can help external application to detect what was changed...

kriki

[Topic moved from Navision forum to SQL General forum]