BOND - ADAPT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

BOND - ADAPT

Author

Topic

NickyW
Starting Member

10 Posts

Posted - 2003-04-28 : 10:29:35

Hi,
Does anyone out there have any experience in importing data from Bond - Adapt to SQL server? (I am trying to setup a DTS package to import from Adapt and having problems)

Thanks,
Nicky

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-28 : 15:28:37

I have never used Bond - Adapt, but maybe I can help anyway. What problems are you encountering? What errors? Do you have latest Bond - Adapt drivers installed on the client where the DTS package is being executed?

Tara

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-29 : 11:46:18

Setting up a linked server would be an alternative.

You can then write some queries to bring the data to a stagging area massage it and place the data in its final destination.

Have you tried contacting tech support

Contact us >>
9020 Stony Point Parkway
Suite 320
Richmond, VA 23235
USA
Telephone: 800-882-BOND
Fax: 804-262-5500

Edited by - ValterBorges on 04/29/2003 11:51:27

NickyW
Starting Member

10 Posts

Posted - 2003-04-29 : 12:46:50

Hi Tara & ValterBorges,
Thanks for the responses.
(Sorry VB, I had to delete my previous post it was a bit inaccurate). I'll split this post into 2.

1)Probs. with creating a DTS package.
I have the latest ODBC driver from Bond.
All I am trying to do is import data from Adapt to SQL I have created a DTS package with a connection to Adapt using the their ODBC driver and a connection to SQL. I set up a transform data task. On the source tab I refered to the Adapt DB, (side note for part 2: on the table drop down box for some reason it shows me list of all the tables twice, once as a table and once as a view, which is strange). If I choose a query as the source instead of table/view and pressed preview it displays the data. I set the SQL server on the 'destination' tab. But when I went to the 'tranformation' tab the 'source' box is empty.

As a side piece of information, if I use the same odbc driver to link the Adapt tables to an microsoft Access db it works fine, and I can then run a dts importing to sql with the access as the data source, but I would like to be able to do it directly as using Access in the middle has its own problems.

2)Creating a linked server.
I can create a linked server wihtout a problem and when I run sp_linkedserver, sp_catalogs etc. all seem to show the right info. But everytime I try to run any query I get the error message
"Server: Msg 7315, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' contains multiple tables that match the name 'assignments'."
where 'assignments is the table I am trying to select from.
I assume this is connected to quirk I mentioned in part 1) that DTS displays all the table names twice, once as as view and once as a table.

Any help you can give would be appreciated.
Thanks again,
Nicky

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-29 : 12:49:36

It sounds like the vendor has a problem with their driver, which isn't something that we can help with. You will need to contact them for a fix, hopefully they have one.

Tara

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-29 : 13:01:31

Try it like this

select * from openquery(BOND, 'select * from OWNER.TABLE')

also

what do you get when you run

exec sp_tables_ex 'linkedservername'

Edited by - ValterBorges on 04/29/2003 13:04:29

NickyW
Starting Member

10 Posts

Posted - 2003-04-29 : 13:38:12

Hi,
The output from sp_tables_ex seem fine, it lists all the tables once and doesn't mention any views.

When I try using openquery (with same query that worked in the DTS preview), I get the following error:

Could not process object 'SELECT A003_ID FROM neweziam.SYS03..assignments'. The OLE DB provider 'MSDASQL' indicates that the object has no columns.

Where neweziam is the linked server name, SYS03 is the catalog and A003_ID is the field.

Thanks Again,

Nicky

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-29 : 13:46:15

What does this return

select * from openquery(neweziam, 'select * from SYS03..assignments')

also while were trying things see what this returns
SELECT a.* FROM
OPENROWSET ( 'provider_name'
, { 'datasource' ; 'user_id' ; 'password'
| 'provider_string' }
, { [ catalog. ] [ schema. ] object
| 'query' }
) as A

An easy way to get all the info is to setup a file dsn with the wizard in the control panel and then look at the file it creates.

Edited by - ValterBorges on 04/29/2003 13:53:16

NickyW
Starting Member

10 Posts

Posted - 2003-04-30 : 11:48:21

Hi,
Both of those return the same error:

Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'select A003_ID from assignments'. The OLE DB provider 'MSDASQL' indicates that the object has no columns.

I also think its the ODBC driver which is strange because it connects to Access using the same driver without a problem and on the preview part of the DTS I can retrieve data without a problem.

Any ideas?

Thanks,
Nicky

rayccarmody
Starting Member

1 Post

Posted - 2008-01-30 : 08:15:03

Hi Nicky,

I feel your pain

I am working with the ODBC 9.5 driver and i am trying to write a DTS that does what you do (having a nightmare with tables such as Jobs)

but the driver does not thread very well (actually at all) , U can't get it to work as a linked server .....I am slowly going down the route that i may need to use access to import the data..

I am using SQL 2000 as sql 2005 is not supported...

did you get anywhere with the issue above, any help would be great

Ray

GarryLowther
Starting Member

2 Posts

Posted - 2008-04-25 : 08:42:43

Hi Ray - Could you please provide me with a copy of that ODBS Driver please?

Garry.

quote:
Originally posted by rayccarmody

Hi Nicky,

I feel your pain

I am working with the ODBC 9.5 driver and i am trying to write a DTS that does what you do (having a nightmare with tables such as Jobs)

but the driver does not thread very well (actually at all) , U can't get it to work as a linked server .....I am slowly going down the route that i may need to use access to import the data..

I am using SQL 2000 as sql 2005 is not supported...

did you get anywhere with the issue above, any help would be great

Ray

GarryLowther
Starting Member

2 Posts

Posted - 2008-04-25 : 08:43:25

Hi Nicky

Would it be possible for you to send me a copy of that ODBC Driver please?

Garry.

quote:
Originally posted by NickyW

Hi Tara & ValterBorges,
Thanks for the responses.
(Sorry VB, I had to delete my previous post it was a bit inaccurate). I'll split this post into 2.

1)Probs. with creating a DTS package.
I have the latest ODBC driver from Bond.
All I am trying to do is import data from Adapt to SQL I have created a DTS package with a connection to Adapt using the their ODBC driver and a connection to SQL. I set up a transform data task. On the source tab I refered to the Adapt DB, (side note for part 2: on the table drop down box for some reason it shows me list of all the tables twice, once as a table and once as a view, which is strange). If I choose a query as the source instead of table/view and pressed preview it displays the data. I set the SQL server on the 'destination' tab. But when I went to the 'tranformation' tab the 'source' box is empty.

As a side piece of information, if I use the same odbc driver to link the Adapt tables to an microsoft Access db it works fine, and I can then run a dts importing to sql with the access as the data source, but I would like to be able to do it directly as using Access in the middle has its own problems.

2)Creating a linked server.
I can create a linked server wihtout a problem and when I run sp_linkedserver, sp_catalogs etc. all seem to show the right info. But everytime I try to run any query I get the error message
"Server: Msg 7315, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' contains multiple tables that match the name 'assignments'."
where 'assignments is the table I am trying to select from.
I assume this is connected to quirk I mentioned in part 1) that DTS displays all the table names twice, once as as view and once as a table.

Any help you can give would be appreciated.
Thanks again,
Nicky

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources