Note the following differences from the code for Warehousing the PRODUCTS Table to Microsoft Access:
User Name is Required for Connection Object. Unlike Access, which does not require a user name, SQL Server requires a user name. Other connection parameters may be required, depending on the way SQL Server is set up. The connection is set up after successful execution of the code below:
hXO = XOInstance ("PUBS", 'ODBC', 'sa', '', '', XO_NOTRANS$)
SQL Server Data Types are Different from Microsoft Access Data Types. You might think that because both databases are from Microsoft and SQL is supposed to be the industry standard for relational databases, SQL Server and Access data types are the same. This is far from the truth, as is illustrated by the code below to create the table. In Access, a character type field can be specified either as Text() or Varchar() and Text() fields can be used as a primary key. In SQL Server, Text fields do not have a length attribute and cannot be used as a primary key. In SQL Server, monetary data is assigned the Money data type; in Access, monetary data is assigned the Currency data type. Finally, in SQL Server, date and time type data have the datetime data type; while Access uses the date data type. These are only a few of the many differences in data type behavior between the two databases. Each database has its own peculiarities in this regard. Consult your database documentation. Below is the code to build the query string for creating the table in SQL Server, with the data types highlighted:
Script = "create table oi_products ("
Script := " id varchar(22) primary key not null, "
Script := " description varchar(50), "
Script := " price money, "
Script := " image_filename varchar(50), "
Script := " order_date datetime);"
Code the proper Oconv() calls when converting the OpenInsight values. Although in this case the output conversions work the same in both conversions, this is not always the case. If the data does not convert correctly, this is an area to check.
