Sage Line 50 ODBC link to SQL Express 2008

Ironic Namesake · 5 Feb 2012 at 10:45

This is kind of following on from a thread made some time ago (http://forums.overclockers.co.uk/showthread.php?t=18321770) but thought I'd start a new one as it isn't really related to Access anymore.

I'm trying to move my current setup into SQL (learning curve - totally new to SQL!) and am getting stuck at how to get the ODBC data from Sage into SQL Express.

In Access I have "Linked Tables" connected to the ODBC driver which works find (read only) this is exactly what I want in SQL Express too. From doing research it seems I need to add "Linked Server" and this is where I'm not having any luck.

The Sage installation where data files are stored are on another machine (both XP Pro) on the same network - Sage works fine on the machine I'm trying to get the data from, and I can use the User DSN and System DSN in ODBC Data Source Adminsitrator in Access to pull all the tables in, so I'm sure the network isn't the issue here.

Below is a screenshot of the setting in SQL Express

Which returns the following when I try to add it (Authentication failed) :

I can get around that by changing the login to "Be made using the login's current security context" as below. This lets me add the Linked Server without generating any error messages.

Once the server is added, I can't actually see a list of tables or anything - get a similar error "Access Denied"

Funnily enough when I try to test connection it tells me "The test connection to the linked server succeeded"

I'm not sure what else to try here ... any tips for getting me past this?

I guess in theory I could create an ODBC Link to Access then link that to SQL , but would prefer to do it the above method if I could get this to work!

mogwai · 5 Feb 2012 at 22:23

A Linked Server in regards to SQL Server lets you run a query against a database on another server.

However what I think you should be looking at is either creating a database and using the import wizard to pull all the tables in.

Or use the Upsizing to SQL server wizard that is in Access.

Ironic Namesake · 6 Feb 2012 at 11:12

Thanks for the reply.

But if I use an import wizard (where is that actually in SQL Express?) the data won't automatically be updated will it?

edscdk · 6 Feb 2012 at 11:18

cannot say I fully read your post properly and I cannot see the screen shots, however if the general gist is one machine connects ok but another will not make sure you have set the SQLExpress instance to allow remote connections... I have been messing with sqlexpress a lot in the last week and its something a lot of poeple seem to be caught out by (despite it being enabled by default on my systems it appears to not always be the case...)

just google "sqlexpress enable remote connections" its jsut a tick box

Ironic Namesake · 6 Feb 2012 at 11:38

edscdk said:
cannot say I fully read your post properly and I cannot see the screen shots, however if the general gist is one machine connects ok but another will not make sure you have set the SQLExpress instance to allow remote connections... I have been messing with sqlexpress a lot in the last week and its something a lot of poeple seem to be caught out by (despite it being enabled by default on my systems it appears to not always be the case...)

just google "sqlexpress enable remote connections" its jsut a tick box

Actually connecting to the Sage ODBC Data Source is the issue here, connecting to SQL Express is no problem

Remote connections are set up properly