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.
| Author |
Topic |
|
tseaman
Starting Member
2 Posts |
 Posted - 2002-10-31 : 22:50:24
|
| Using an ActiveX task I have written a function to open a Pervasive database via ODBC using ADO. I then loop ("Do While not RS.EOF") through the recordset to process each record. However, though the loop executes as many times as there are records, when I reference a field in the recordset, e.g., RS("code"), no data is returned.However, I can reuse the code in another function just changing the table being queried and field names and it works fine.Any idea what's going on? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-31 : 22:55:37
|
| If you're using DTS to open the Pervasive database, and you can connect to it using ADO, why do you need to process it with a recordset in a loop? Can't you import it directly into SQL Server using a regular data transformation? |
 |
|
|
tseaman
Starting Member
2 Posts |
Posted - 2002-10-31 : 23:01:51
|
| I have to do some transformations on the data and load another Pervasive database from it. We are just using DTS as the vehicle. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-31 : 23:14:15
|
| Well, let me put it another way: can you import the data into SQL Server, do the data transformations there, and then export it to the second Pervasive DB?The reason(s) I mention it are A) the problem you're having is pretty bizarre, B) using a regular DTS transform should avoid it, and C) using recordset loops can be very slow, much slower than regular DTS and SQL-based transformations/conversions.The only thing I can guess that would cause the effect you're seeing is if you reached EOF of the recordset and then tried to reference rs("Code"). Maybe, if the Pervasive DB is case-sensitive, the column name may be in the wrong case (I don't know about Pervasive so I can't say). Also the ODBC driver may be the issue, and using an OLE DB provider for Pervasive may fix the problem.I'd at least suggest trying a regular DTS import into a SQL Server table. If that works, you can at least investigate processing/tranforming the data in SQL Server, and then exporting it back to the Pervasive DB, in case doing it through an ActiveX task can't be made to work. |
|
|
|
Tim
Starting Member
392 Posts |
Posted - 2002-11-06 : 21:35:04
|
| is your ODBC driver treating the column names as csae sensitive?Is the column called "code" in the source db or is it "CODE" or "Code" just a thought----Nancy Davolio: Best looking chick at Northwind 1992-2000 |
|
|
|
|
|
|
|
|
ADO recordset data in ActiveX task not available