C/ODBC and DTS SQL

Running Navision 3.6 C/ODBC is used inconjunction with Excel and it works fine We want to automize the process to get the data in a SQL Server for Business Intelligence Analysis I have connected the DTS package to the Database [using C/ODBC with SERVER option] and is able to see the Files and Views When I select a table [For eg: Table Payment Terms] and click the Transform Button the following error is popped up QUOTE ********** Error Source: Microsoft OLE DB Provider for ODBC Drivers Error Description: [Navision a/s][Navision Attain ODBC Driver]Table Does not exists Context: Error calling OpenRowset on provider *************** UNQUOTE With the Table Currency No Issues With the Table Finace Charge Terms - Error Unexpected extra Token:Terms It looks like the DTS does not like spaces in the Navision Table. How can I resolve this issue?

One way is to make Views of the tables and in the views substitute the spaces and full stops with underscores. Then with the DTS package attach to the view rather than the table. Also dont forget that the Table name is actually CompanyName$TableName if you are using a SQL script.

Do you create the view in MS NAV ? IF so how do you do it

Edit your ODBC DSN and click the “Options” button (bottom right). The “Identifiers” dropdown is probably defaulted to “All Except DOT”, change it to “a-z,A-Z,0-9,_”. This will replace all table and field name characters that are not strictly alphanumeric-or-underscore with the underscore character, including spaces. If there are spaces in the table or view name DTS won’t structure the query properly. (If you leave the “Identifiers” option the way it is you’ll notice you can access tables without spaces in their names just fine). I think DTS follows the ANSI standard for name delimiters and delimits table and field names with square brackets if they have spaces in them. C/ODBC is non-standard and wants such names delimited with double quotes ("). It does not recognize the square bracket as a delimiter and therefore a) can’t find the table name, and b) doesn’t know what to do with the other words. Good luck.

Will Check and get back

Works Fine. As issues with the Data and Time fields of Navision which are giving errors Would you be able to guide me on this [?]

Solved the issue Edit the SQL Statement and convert it to datevalue(DTS… [:D]