Community
Showing results for 
Search instead for 
Do you mean 
Reply

How to connect to ACT! with the sa password..

Accepted Solution Solved
Copper Contributor
Posts: 15
Country: United States
Accepted Solution

How to connect to ACT! with the sa password..

Hi All;

I have a client who has purchased the sa password so I can do some maintanence on their DB.  I have several questions:

1. Do you have to use the ACT! OLEDB provider or can you use the MS OLEDB provider?
2. Can you use remote connections?
3. Can you hook up with SQL Server Management Studio Express - if so how?

There is no documentation on this with the sa password docs.

Can anyone provide an example of the connection string to use, as I don't seem to be having any luck with the standard connection string remotely using the MS OLEDB provider.

Thanks in advance.

Accepted Solutions
Solution
Accepted by topic author George Wilcox
‎09-25-2015 03:20 AM
Copper Contributor
Posts: 15
Country: United States

Re: How to connect to ACT! with the sa password..

Apparently, my client did a reinstall and the sa password changed - once regenerated with the sa password tool, it worked fine.  Here's an interesting bit of trivia for you:  The total number of tables and views in ACT! 2010 is 698!  The total fields in this particular database is 7793!  This is a tribute to the absurdly over "normalized" and WAY too complex data structure brought about by developers who's goal is personal indispensibility at the expense of product performance and usability.  Small wonder it takes so long to load, is slow and gobbles up so many resources!  It's like taking a Ferrari with a V-12 (SQL Server) and pulling the spark plug wires from 10 of the cylinders!

View solution in original post


All Replies
Platinum Elite Contributor
Posts: 14,384
Country: Australia

Re: How to connect to ACT! with the sa password..

OLEDB doesn't require the SA password

You CANNOT use the SA pass to "do maintenance" on a database. You cannot write or modify the database with it as you will damage the database.

 

You might have a look at these:

http://blog.glcomputing.com.au/2009/07/connecting-to-act-by-sage-data.html

http://blog.glcomputing.com.au/2009/04/getting-table-structure-and-links-from.html

Copper Contributor
Posts: 15
Country: United States

Re: How to connect to ACT! with the sa password..

I'm a very experienced software developer and database guy.  I won't break it, but rather than learn a .NET language and the SDK, I'd like to use the languages I know - SQL and VB to parse the Mr., Mrs., etc out of the contact field into the Prefix field and also to parse titles out of the Contact field and put them where they belong.  This won't affect or break any relational delicacies. 

Before my client purchased the sa password, I had a telephone conversation with an ACT! sales engineer, who assured me that I could do what I wanted; the sa password would give me full access to the SQL tables to read and write as I like, but that the client would lose support from ACT! for having manipulated their own data!    My client is fine with that.  This is also borne out by the following post, in which David Lunsford specifically states that to manipulate data you need the sa password:

http://community.act.com/sage/board/message?board.id=ACT&message.id=13695&query.id=1320707#M13695

There is also a mention on another post that you can hook up to it with SQL Server Management Studio Express with the sa password, so can anybody please let me know what I need to know?
Platinum Elite Contributor
Posts: 6,653
Country: USA

Re: How to connect to ACT! with the sa password..


George Wilcox wrote:
I'm a very experienced software developer and database guy.  I won't break it, but rather than learn a .NET language and the SDK, I'd like to use the languages I know - SQL and VB to parse the Mr., Mrs., etc out of the contact field into the Prefix field and also to parse titles out of the Contact field and put them where they belong.  This won't affect or break any relational delicacies. 
 
The ACT! program already provides that functionality by entering the appropriate prefixes, titles, etc., in preferences (Tools | Preferences | General tab).

 

Roy Laudenslager
ACT! Certified Consultant
ACT! Report Expert
Durkin Impact Report Designer
www.techbenders.com
royel@techbenders.com
541-343-8129
Copper Contributor
Posts: 15
Country: United States

Re: How to connect to ACT! with the sa password..


Roy_Laudenslage wrote:

The ACT! program already provides that functionality by entering the appropriate prefixes, titles, etc., in preferences (Tools | Preferences | General tab).

Hi Ron;

I'm aware of that, and that's the idea moving forward.  The  problem is that the client imported 60K+ records (and used them extensively) with the Mr./Mrs./Ms./Dr/Prof/Hon. and titles, Jr./Sr/President, etc all in the contact field.  If I can hook up with the sa password, I can have all of that parsed out and put in the right fields inside of a half hour with a few update queries in SQL Server Mnaagement Studio Express or VB.

Platinum Elite Contributor
Posts: 14,384
Country: Australia

Re: How to connect to ACT! with the sa password..

Other than being not allowed under the EULA, I suspect you'll run into other problems in doing that.
Platinum Elite Contributor
Posts: 6,653
Country: USA

Re: How to connect to ACT! with the sa password..

I'm not sure what your expectations are but the contact field needs to retain the full name plus any prefixes and suffixes. Have you thought about making sure the name preferences includes all the necessary prefixes and suffixes and then copying the contact field to a temporary field (Edit | Replace) and then copying it back to see if it now parses out the prefix and suffix?
Roy Laudenslager
ACT! Certified Consultant
ACT! Report Expert
Durkin Impact Report Designer
www.techbenders.com
royel@techbenders.com
541-343-8129
Copper Contributor
Posts: 15
Country: United States

Re: How to connect to ACT! with the sa password..

Adding the prefix or suffix field data to records without them doesn't add them to the Contact field, so this is not a concatenation.  The data should be parsable if I can get the OLEDB connection to the database.  Has anyone out there done that?  What good is the sa password, if there is no documentation on how to use it? 8>(
Platinum Elite Contributor
Posts: 14,384
Country: Australia

Re: How to connect to ACT! with the sa password..

The documentation with it specifically says that the EULA prohibits writing to the database with it...
Copper Contributor
Posts: 15
Country: United States

Re: How to connect to ACT! with the sa password..

Mike - I had the conversation with ACT!'s sales engineer, who said it was OK, if the customer accepts that they won't get support from ACT! if they do it.  (Strange, to charge an extra $500 to not do something!)  Even here on the board, ACT!'s own people say you need to use the sa password to make wholesale changes to your data.  It is, afterall, the client's data, not ACT!'s, so if that's what they want, that's what they should get.  We will back it up and if anything bad happens, restore it and figure out where the problem came from.  I have already taken apart the schema to be fully aware of the relationships and keys, child tables, etc.  No big deal given the correct information....