I am in the process of converting some old AREV 3.11 data to SQL Server using OpenInsights datawarehousing feature and am running into a couple of problems.

1) The datatype is not showing up from the attached AREV Tables

2) The generated Create Table scripts are not always usable, when you define a multi-part primary key it adds the primary key keyword to the end of multiple column definitions instead of creating a primary key constraint after the createtable. It will also allow you to specify a text type column as primary key (I know I should know better)

3) Got this figured out, but it's something for discussion - the documentation on setting up the connection to the external database is terrible, would also be nice if Revelation supplied the 16 bit ODBC drivers, I had a hell of a time finding them you don't see many 16 bit apps coming out of the box anymore.

Anybody using this successfully for this purpose?

Thanks

Chuck Pedretti, MCSD

Consultant

Magenic Technlogies

Chuck,

Have you looked at our knowledge base article on SQL Server specifics with Data Warehousing?

Cameron Purdy

Revelation Software

Yes I have, and it does not provide any answers to the questions I am asking.

I have also searched this online forum and your knowledge base and have yet to find anything about these problems.

Hi Chuck,

Anybody using this successfully for this purpose?

I did. I've done warehousing using these tools to Oracle, SQL Server, Access, and even Excel spread-sheets.

I'm sorry the knowledge base article didn't help.

1) The datatype is not showing up from the attached AREV Tables

That is probably because the fields don't have a data type. My guess is the tables were created in an older version of Arev before field types were introduced. (I cannot remember when the types were introduced, but I believe it was for SQL in Arev 2.x.) Please correct me if the assumption is incorrect.

I don't believe that the data types are essential to have in the dictionary, but it does help to have them (so you don't have to fill in everything by hand). Since they are Arev 3.1x tables now, you can use the DICT tool in Arev or the table builder in OpenInsight.

As a good starting point, I suggest running a program that puts in default data types for you. For example:

<code>
function Utility_TypeFields(DictName)

* run Utility_TypeFields ''

$insert Logical
$insert Dict_Equates

open "DICT", DictName to f else return "Error opening ": quote(DictName)

select f
eof=FALSE$
loop
  readnext Key else eof=TRUE$
until eof
  if Key1,1 # '%' then
    read Record from f, Key else return "Error reading ": quote(Key)
    if len(Record) else
      Record=VARCHAR(65531)"
      Record=STRING"
      Record=VARCHAR"
      Record=VARCHAR(65531)"
      write Record to f, Key else return "Error writing ": quote(Key)
    end
  end
repeat

return "Done"

</code>

If your dictionaries have validation/formatting information, you could modify the above program to use that information to determine the type. For example "(D)" or "D" would imply a date.

2) The generated Create Table scripts are not always usable, when you define a multi-part primary key it adds the primary key keyword to the end of multiple column definitions instead of creating a primary key constraint after the createtable. It will also allow you to specify a text type column as primary key (I know I should know better)

The knowledge base article does show how to define SQL Server keys using the sp_primarykey SQL Server stored procedure.

Cameron Purdy

Revelation Software

View this thread on the forum...