?
Solved

Setting up ACT! as a linked server

Posted on 2006-06-30
10
Medium Priority
?
431 Views
Last Modified: 2008-01-09
I am trying to set up ACT! as a linked server in SQL2000. I have lots of other linked servers (Sybase, Oracle)
I am using the ACT! OLE DB provider supplied with ACT.
I have registered the dll using regsrvr32
I have created a udl file that tests OK when i run a text connection.
However when I try to list tables in the linked server I get an Error 7403 - could not locate registry entry for OLE DB provider ACTOLEDB.1
help please !
0
Comment
Question by:JohnMalinowski
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 43

Expert Comment

by:Eugene Z
ID: 17017034
<I have registered the dll using regsrvr32>
just make sure you did it on sql server - not your PC?
Did you try to create ODBC DSN on the sql server and create the linked server by usin the DSN?

BTW: who is the Act vendor?
0
 

Author Comment

by:JohnMalinowski
ID: 17017215
thanks for the feedback.
The dll has been registered on the server. ACT is installed on the same server. I am trying to set up the linked server on the SQL server (not via a workstation). Have tried both the wizard and sp_addlinkedserver.
As far as I can see you can't set up ACT using ODBC, it has to be OLEDB.
I'd prefer not to disclose the vendor.
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 17017273
Please use the steps in the articles mentioned below to check if you have the
provider in place.


http://support.microsoft.com/?id=216575 


http://support.microsoft.com/?id=200794 
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:JohnMalinowski
ID: 17017538
exec xp_enum_oledb_providers confirms that the ACT provider is present as 'ACTOLEDB.1'
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 17017702
good,
Can you access the ACT database from the sql server box by any way (VB, VBS, etc.)

Check permissions, UID\PWD, TCP\IP port on the ACT box, etc.
if you have sql server 2000 with >=sp3
you can try to check to act connection:
OPENDATASOURCE
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_oa-oz_2be1.asp
0
 

Author Comment

by:JohnMalinowski
ID: 17018362
Permissions are OK
I have no other apps on the server the try a connection. Might try installing excel.
The only connection that I have tried that works is a MS data link file (udl) and that just gives me a connection test OK.
I've tried OPENDATASOURCE but can't get past a syntax error. I'm beginning to suspect I need to know the precise syntax for the ACT OLEDB when running sp.addlinkedserver.
0
 
LVL 43

Assisted Solution

by:Eugene Z
Eugene Z earned 50 total points
ID: 17019539
try to check vendor help files about sql server
0
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 200 total points
ID: 17030188
Do you have any idea of a table name?

Possibly the facility to list tables doesn't work but you may be able to select from a table.

You could also try Regmon from sysinternals.com to try and identify where its looking in the registry.... maybe its getting it wrong?


But if you purchased the driver then you should be entitled to some support.



0
 

Author Comment

by:JohnMalinowski
ID: 17050065
Many thanks to all for the help - I have now solved the problem.
basically it is a registry entry issue.
The registry entry that gets created by the ACT OLEDB dll has the same data ('ACTOLEDB.1' ) for both the ProgId key and the VersionIndependentProgId key.
I noticed that for the Oracle OLEDB entry, the '.1' part was absent from the VersionIndependentProgId key, so I changed the ACT VersionIndependentProgId key to read 'ACTOLEDB'.
I then set up a new linked server using sp_addlinkedserver specifying @provider=N'ACTOLEDB' and hey presto it now works.
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 17050599
you are welcome
but please read about Grade 'C':

http://www.experts-exchange.com/help.jsp#hi73
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question