synchronising sqlserver with SAGE Line 100 database

Question

Post reply

synchronising sqlserver with SAGE Line 100 database

yogiberr

Hall of Fame

Points: 3679
More actions
August 9, 2005 at 12:29 pm

#165213

hiya,
I know that it'spossible to synchronise 2x sql server databases, but is it possible to do it with the SAGE Line 100 database?
Any advice appreciated
cheers,
yogi
August 12, 2005 at 8:00 am

This was removed by the editor as SPAM

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

steve-204902 SSC Eights! Points: 867 More actions · Answer 1

If Line 100 has ODBC drivers you could try a DTS package?

Regards,

Steve

Tim Pain Ten Centuries Points: 1136 More actions · Answer 2

I'm using DTS with an ODBC driver to pull data out of a Sage Line 50 system into SQLServer.

http://www.triplentry.com have a product that apparently will import and export to Sage, but I have not tried it.

Rgds

Tim

steve-204902 SSC Eights! Points: 867 More actions · Answer 3

Tim have you ever tried setting up a linked server in SQL server to Line 50?

I'm toying with an Access front end which needs linked tables to both Line 50 and SQL and I can do this fine using an MDB file by directly linking the Line 50 with ODBC but I need to use an Access adp project to be able to use the full power of SQL server and the only way I can link to Sage tables in an ADP is by setting Line 50 up as a linked server which it let's me do but I get errors whenever I try to open the tables up.

If you've never tried it don't worry - just thought I'd ask, us Line 50 and SQL integrators are few and far between!

Regards,

Steve

Tim Pain Ten Centuries Points: 1136 More actions · Answer 4

Hi Steve,

I have an Access front end and SQL backend, so am using an ADP route.

Sage was not the reason for going this route, but it does mean the DTS stuff is reasonably easy and works OK.

I have never tried a direct link from Access to Sage.

I'm only going from Sage to SQL/Access. At some point I might have to go the other way, which I anticipate would cause problems, because as far as I know the Sage ODBC drive is read only. Are you managing to writing to Sage?

Rgds

Tim

steve-204902 SSC Eights! Points: 867 More actions · Answer 5

Hi Tim,

I need to pull out real time stock levels from Sage so I need the live link to the Sage tables rather than doing a DTS update or import. I can link Sage tables directly to an Access MDB frontend but then I don't have the full SQL server support that I get with an ADP

The linked server looks OK in enterprise manager but if I try use it from Access it justs gives me errors and when I run a query in query anaylyser it only works if I have a rowcount of 4 or less so it's happy connecting but not at returning more than 4 rows!

The Sage ODBC is pretty much unsupported even by Sage so I suspect it's just not going to work.

You're correct, the Sage ODBC is read only. I have developed a few databases which write to Sage - you need to get the Sage Developer Kit which provides you with the Sage Data Objects which allows you so manipulate data within sage using VB or VB.NET.

I use it from within Access with VB. It's not too bad to use but you need to know your Sage tables and think about the consequences of what you're writing into Sage. For example you could generate an invoice for a stock item but unless you also generate a transaction to adjust the stock levels for that item you're accounts are going to be out!

By the way the Sage developer kit costs around £1500 and then you have to pay an annual fee a bit less than that if you still want access to the developer kit and tech support.

Regards,

Steve

Tim Pain Ten Centuries Points: 1136 More actions · Answer 6

Hi Steve,

The way I have it working at present with SQL and DTS, is that for example, the user opens a customer record form in Access, then clicks to view the customer balance, which runs a SQL stored procedure, that calls a DTS script to read the Sage record for that customer and return the information to the Access form. Not quite real time, but it runs quick enough for the users not to complain.

Much the same for customer reports, again calls a DTS script to read customer balances, and into a SQL stored procedure than is used to populate the report. No problems reading 100's of rows!

I was aware of the Sage developer kit and knew it was reasonably expensive. £1500 is to much to justify for my use, but the £150 approx for the TripleXchange (see link above) I could probably manage. It must then be possible to write the XML or CSV handling procedures in Access VB. At some point I'll find the time to look further into this.

I'm only dealing with Customer account balances from Sage and possibly Sales Invoice from Access to Sage in the future. Stock control etc' is all in Access/SQL, so the table updates in Sage should be reasonably straight forward (I think ).

Rgds

Tim

steve-204902 SSC Eights! Points: 867 More actions · Answer 7

I hadn't thought about doing an update from the Sage record as the user views it Tim, like you say it's not live but it's no more than a couple of seconds old.

I'm fairly new to DTS and can't figure out which of the numerous tasks available is best for updating a SQL table record based on a matching record in Sage. I'm familiar with update queries in Access JET databases where you can join tables but what's the best task to use in DTS to perform an update from Sage?

The Sage developer kit is expensive if you're only wanting it for an in-house project (rather than developing applications for resale) so it might be worth approaching a couple of Sage developers and asking if they'd let you have the developer kit for a fee so you're sharing the cost.

There's only a few files needed and a serial number from Sage to enable 3rd party integration in Line50 so it's not much work for the developer and if you explain that you're just needing a single serial number and the SDO files for your own use they might be willing to help as it softens their £1500 bill!

Regards,

Steve

Tim Pain Ten Centuries Points: 1136 More actions · Answer 8

This is my Sales Ledger Sage Accounts on Stop procedure. It uses a usystablename table to hold the data.

Basically delete the usys table, re-create it and load the data from Sage.

CREATE PROCEDURE usp_SageCustomersOnStop

(

@OldOnStopCount INT OUTPUT,

@NewOnStopCount INT OUTPUT

)

AS

-- Read Sage data

EXEC usp_ExecuteDTS '127.0.0.1', 'SageAccountOnHold-Test', 'sa', 1

-- EXEC usp_ExecuteDTS '127.0.0.1', 'SageAccountOnHold', 'sa', 1

-- Find out how many records are currently on stop

SELECT @OldOnStopCount = (SELECT COUNT(*) AS OldOnStopCount FROM dbo.Customers WHERE (OnStop <> 0))

-- Update ALL Existing customer records

UPDATE dbo.Customers SET OnStop = 0

-- Find out how many records are to be updated

SELECT @NewOnStopCount = (SELECT COUNT(*) AS NewOnStopCount FROM dbo.usysSageOnHold)

-- Update new details

UPDATE dbo.Customers

SET OnStop = 1

FROM Customers INNER JOIN

usysSageOnHold ON Customers.CustomerID = usysSageOnHold.ACCOUNT_REF

/* SET NOCOUNT ON */

RETURN

GO

You can call the DTS procedure using the xp_cmdshell stored procedure, but I did not want to go this route as there are some security issues, so I used the above. This uses the usp_ExecuteDTS and some other stored procedures that I got from somewhere (sorry I can't give credit where it's due, but I can't now find where I copied them from).

If you would like the scripts, just let me know.

Rgds

Tim

steve-204902 SSC Eights! Points: 867 More actions · Answer 9

Thanks for that Tim, I'll give that a go.

I've found a helpfile I had for Sage Data Objects which shows examples of code and what it can do and how to do it - it's not much use without the actual SDO files but if you're just interested in taking a look PM me your email address and I'll forward it on.

Regards,

Steve