07-26-2012 08:27 AM - edited 07-26-2012 08:52 AM
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
Data Source: Sage_Act (the DSN name)
Any suggestions would be warmly welcomed.
07-26-2012 02:46 PM
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.
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?
07-26-2012 03:06 PM - edited 07-26-2012 03:14 PM
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.
07-26-2012 04:17 PM - edited 07-26-2012 04:19 PM
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:
Now presuming you want to create a link on server2\sql to server1\act7:
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.
07-26-2012 07:14 PM
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.
07-27-2012 12:27 AM
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.
07-27-2012 01:53 AM
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.
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.
07-27-2012 02:17 AM
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?
07-27-2012 02:27 AM
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.