• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2615
  • Last Modified:

ADP SQLBASE - Link to SQL Server

Hello Experts,

I'm trying to link a SQLBase database from ADP to my SQL Server so that I can use it with other databases in the SQL Server 2000.  However, I'm having issue trying to create the link.  

When linking, in the General tab, I put in the linked Server name, select the Gupta SQLBase OLE DB Provider, put in product name, data source (assume this is a datasource that is already connecting to this database), and provider string which similar to the following:  DSN=SQLBase Island;SERVER=SERVER1;DATABASE=PAYROLL;
UID=JOHN;PWD=XYZZY

However, no luck.  Anyone able to successfully done this or know how to go about getting this to work?
0
holemania
Asked:
holemania
  • 6
  • 5
1 Solution
 
Vadim RappCommented:
General tab - where do you see it? are you doing this from Access? how?
0
 
holemaniaAuthor Commented:
In enterprise manager for SQL Server 2000, I want to link ADP which use SQLBase.  I installed the Gupta driver and created the ODBC which I can connect to the ADP SQLBase database.  However, I would like it to link to SQL Server 2000 so that I can use it with the other databases.

To add in the link server, I went to SQL Server Enterprise Manager and drill down the node to Security-->Linked Servers.  From there, I right click to add a new  linked server.  Once I do that, that's where it allows me to put in the Data Source and Provider String.  I can't seem to connect to it when linking it to my SQL Server 2000, but I can access the data from Excel using the ODBC that I create.

0
 
Vadim RappCommented:
ole db provider string - it's not ODBC; specify it in form

Provider=SQLBaseOLEDB;Data source=myServerAddress;Location=myDataBase;User Id=myUsername;Password=myPassword;

ADP has no place in all this; ADP is thin client providing GUI for SQL Server. It has no own objects, so there's no point in linking to it.

Further, I don't even understand how you linked ADP to SQLBase, since ADP works only with SQL Server and nothing else. If you somehow ODBC-linked the tables from SQLBase, it would be much easier to do with MDB.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
holemaniaAuthor Commented:
I'm not sure what you meant when you said you don't understand how I link ADP to SQLBase.  The version of ADP we use, the database platform is SQLBase so there's no linking involve.  It uses the Gupta SQLBase.  From what our ADP tech told me, the newer ADP uses Oracle.  We have an older version that is SQLBase.

What I'm trying to do is link the ADP server to SQL Server 2000 so that I can update employee information within Kronos base off the information in ADP.  Another way of getting around this is dump the employee table from ADP into another table in SQL through ODBC, but it's not the most efficient.  I was hoping that I can link the ADP server instead.  I know it can be done, I'm just not sure what I'm missing to get it to work.  I have done it with excel file, oracle, and variouse access databases.  However, the SQLBase for ADP is a little funky and would not work correctly.  I can pull the data to an excel file as well through the ODBC and link that excel file to our SQL Server, but again it's not the most efficient way.
0
 
Vadim RappCommented:
We are probably talking about different ADP's. This area, "Microsoft ADP", is dedicated to Microsoft Access Data Projects, which is one of the two types of Microsoft Access applications, another one being MDB. More details at http://office.microsoft.com/en-us/access/HP052731031033.aspx . I guess this is not what you had in mind?
0
 
holemaniaAuthor Commented:
No, I'm talking about ADP Payroll.  http://www.adp.com.  That could be why I wasn't clear on what you kept referring to lol.
0
 
holemaniaAuthor Commented:
Just noticed I click on the zone for Microsoft ADP.  That's wrong on my part.  I should've clarified it more.
0
 
Vadim RappCommented:
ok, so ADP is out of question. Back to the problem - try provider string like in my first comment. There's website for those strings for all kinds of databases - www.connectionstrings.com
0
 
holemaniaAuthor Commented:
No luck and had to do it another way.  I was able to automate a dump from SQLBase to excel file, and then dump excel file to a temp table.  This seems to work.  Not what I was hoping to do, but it's a workaround.  Thanks for the help.
0
 
holemaniaAuthor Commented:
Was a misunderstanding on my part when I put it in the wrong zone.  Only response I got even though it didn't resolve my issue, deserve the credit for time in helping me out.
0
 
Vadim RappCommented:
some notes for the purpose of KB:

1. to tes if particular OLEDB string is working, the easiest is to create a simple vbscript and see if it works. For example,

createobject("adodb.connection").connect( <adodb-string>).execute ("select 1")

2. in the properties of the linked server, make sure that "allow in-process" is checked

3. This link seems to provide the steps that lead to success:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=107277

4. it's possible to configure Excel worksheet as linked server - see MS article 306397.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now