DTS error - MAS 90 query - 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

SQL Server Development (2000)

DTS error - MAS 90 query

Author

Topic

agrantfan
Starting Member

5 Posts

Posted - 2006-04-11 : 15:56:51

I am using a DTS package. In the data transformation, I have the following SQL query that inserts the resulting rows into a SQL table:

SELECT h.VendorNumber, h.InvoiceNumber,
h.ApVendorName, format(char(10),h.InvoiceDate, 110) as InvoiceDate , d.Amount,
d. GlAcctNumber
FROM AP_12InvHistoryHeader h
, AP_13InvHistoryDetail d
WHERE h.InvoiceNumber = d.InvoiceNumber
AND h.HdrSeq = d.HdrSeq

I get the error:

"Error Description: [ProvideX][ODBC]Expected lexical element not found: FROM"

Any ideas? Currently h.InvoiceDate is in the format YYYY-MM-DD and I am trying to ultimately get it into MM-DD-YYYY format.

Any help is greatly appreciated.

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-11 : 16:06:42

Format is not valid in your query. You need to use CONVERT instead.

Tara Kizer
aka tduggan

agrantfan
Starting Member

5 Posts

Posted - 2006-04-11 : 16:17:13

quote:
Originally posted by tkizer

Format is not valid in your query. You need to use CONVERT instead.

I get the same error message with CONVERT as well.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-11 : 16:22:09

Does the query work in Query Analyzer? Perhaps you need to use INNER JOIN syntax.

Tara Kizer
aka tduggan

agrantfan
Starting Member

5 Posts

Posted - 2006-04-11 : 16:24:36

Unfortunately I cannot test it in Query Analyzer. My source is MAS 90, and I don't have access to this using Query Analyzer.

In my DTS package, I have created a connection to MAS 90, which I have to choose a Company, UserID, and Password for each time I execute the DTS package.

I know the query works without trying to format the date because this query will parse and produces output:

SELECT h.VendorNumber, h.InvoiceNumber,
h.ApVendorName, h.InvoiceDate, d.Amount,
d. GlAcctNumber
FROM AP_12InvHistoryHeader h
, AP_13InvHistoryDetail d
WHERE h.InvoiceNumber = d.InvoiceNumber
AND h.HdrSeq = d.HdrSeq

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-11 : 16:27:07

I guess I don't know what MAS 90 is. Hopefully someone else can help.

Tara Kizer
aka tduggan

agrantfan
Starting Member

5 Posts

Posted - 2006-04-11 : 16:29:08

Tara -- I appreciate you trying! MAS 90 is a financial database. I'm using a 32-bit ODBC driver to connect to it. I don't have access to it via Query Analyzer or this would be much easier.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-04-11 : 22:05:44

MAS 90 is a proprietary database, so you will have to use SQL that conforms to it's version of SQL. You will have to read the vendors documentation for that.

If you want to use T-SQL for your queries, load the data into tables in SQL Server first, and then do T-SQL queries against those tables.

CODO ERGO SUM

agrantfan
Starting Member

5 Posts

Posted - 2006-04-12 : 08:44:24

quote:
Originally posted by Michael Valentine Jones
<snip>

If you want to use T-SQL for your queries, load the data into tables in SQL Server first, and then do T-SQL queries against those tables.

CODO ERGO SUM

Great idea! I never thought of that. Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources