SQL Server Linked Server for Sage 2012 [ Edited ]

[ChrisWoods]

Hi,

I'm trying to access the Sage2012 Database from within SQL Server (so we can combine it with other data) which resides on a separate server.

I have successfully created a DSN on the SQL Server server and the test function says it works OK.  This calls the ACT Reader Utility (to provide the ODBC interface) which seems to be happy.

However, when I add try to Add Linked Server within SQL Server Management Studio, it complains that

OLE DB Provider "MSDA" for linked server "SAGE_ACT_SERVER" returned message"[Microsoft][SQL Native Client][SQL Server]Login failed for user "xxx". (Microsoft SQL Server, Error:7399).  

I've tried various combinations of parameters and user credentials to no avail. I have called Sage and they don't support the Reader Utility.

I am currently using the following within the Add Linked Server dialog:

Linked Server: SAGE_ACT_SERVER

Provider: ACTReader

Data Source: Sage_Act (the DSN name)

Provider  String:Empty

Location: Empty

Catalog: Empty

Any suggestions would be warmly welcomed.

Thanks,

Chris.

[wellmet]

Chris,

I did it several years ago using the SA Username/Password combination for a client that I no longer work with so I can't look at their setup to see exactly how I did it.  If I were doing it today I would use Windows Authentication instead of using the ACTREADER.  I just thought I'd respond so you know that it can be done.  I didn't know if anyone else had ever done it so I thought I'd reply.

I'm sure that after seeing this post Vivek will wonder what we're talking about if he doesn't know already and will undoubtedly try it once he figures out what we are talking about and if you haven't figured out how to do it by then perhaps he'll post how to do it.  I'm pretty swamped at the moment or I would give it a go.  I'll be playing with SQL the later part of next week if you haven't figured it out by then I'll see if I can get it working on my system and let you know how I did it.

Stan

P.S.  Vivek if you're reading this what he's talking about can be very useful if someone has two SQL databases and wants to analyze data from both...like in Power Pivot perhaps?

[vivek]

Hehehe! Yeah I had had a look at ot a little while back and yes you are bang on the nose it was directly related to when I was investigating PowerPivots! 

I remember I had had run into problems and I can't remember what they were exactly. Unfortunately I had gotten pulled away to other things and forgot all about it till, well now and I missed this post completely!

I'm pretty sure that as Stan says, if you use Windows authentication you should be able to do this. The reader login does not allow the creation ofo any temp tables etc on the act7 instance or even updates to the system tables. I have a feeling that there might be some updates to the system tables of both instances.

I'll try and have another play and look into it and post back here my story.

[vivek]

Ok had a look again at this and its a lot simpler using the SSMS GUI instead of going nuts using the SP!

Basically ignore using the actreader and use windows authentication. Servers are as follows:

• server1\act7 (the act instance)
• server2\sql

Now presuming you want to create a link on server2\sql to server1\act7:

1. in SSMS on server2 expand server objects and right click on Linked Servers
2. Select New Linked Server
3. Select SQL Server radio button
4. in the Linked Server text box enter server1\act7
5. on the left hand side choose Security
6. Choose the Be made using the login's current security context (presuming that your current login is also admin on server1)
7. Click OK and you've done it! Simples!

I can't believe just how simple it is via the GUI, I kept banging my head with one error after another using the SPs.

*EDIT* Oh yeah one more thing remember to start your Distributed Transaction Coordinator Service on both machines and also set the appropriate RPC true/false values in server options in SSMS.

[wellmet]

Vivek,

Good job!  I didn't think it would take you long.  My client wanted to run reporting on both databases and needed to reference records in the ACT! database in the reports.  He wasn't writing to the database so I wasn't worried that it would cause problems.  It's a useful little trick if you want to simplify reporting.

Stan

[ChrisWoods]

Thanks very much Vivek and Stan - an answer overnight!

I've successully connected to the other server now from SSMS.  

However, I can only see System Tables which seem to be in a folder called System Catalogs which is in a folder called Catalogs.

I can't see any ACT data.

I'm not sure if that is a permission thing - I've had no opportunity to enter a Sage ID and Password or whether its something else.

Any thoughts?

Thanks again,

Chris.

[vivek]

Hi Chris,

I've googled that some have found it helps to restart sql servers. I didn't need to myself last night. Maybe you haven't set the RPC appropriately (I doubt this is the issue though for just reading the tables). I know this next request is a bit of a pain, but coud you post up screen shots of the general tab in SSMS for the linked properties.

Stan,

Hehehe, well you see with PowerPivots joining disparate datasources is inherent in its design so you don't need server linking you can do the connection/joins/relations via Dax. The linked servers is useful to link to SQL 2012 which allows auto refresh of the PowerPivot dataset which has to be done manually with 2008R2. I haven't tried this as yet.

[ChrisWoods]

Hi Vivek,

Thanks for trying to sort this for me.

I'm not keen to post the general tab as this is obviously an open forum.

However, the contents are very simple as the Server Type is set to SQL Server, so most of the fields are greyed out.

The few that there are are as follows

- Linked Server (at the top): Server1\ACT7

- Server Type: SQL Server

The Connection section in the left hand margin has:

- Server: Server2\SQLExpress

- Connection - Domain\user id with sys admin

Does that help?

Chris.

[vivek]

Hi Chris,

No worries public forum: I wasn't thinking...doh!

Ok that seems ok, I initially thought you might have put server2\sqlexpress in place of server1\act7

What do you have for the security settings and the server options.

[ChrisWoods]

Hi Vivek,

Here they are....