Hello and Happy New Year!

Has anyone out there successfully used the Adaptive Server Anywhere
Provider 8.0 as a DTS Connection? I can get a connection via ODBC, but
cannot seem to get one to work by using Sybase's provider (called
ASAProv). I *thought* this would have been easy but I am facing a major
problem(s):

When the connection is attempted to be opened, I get a dialog popping up
at runtime entitled "Connect to Adaptive Server Anywhere" and it prompts
me for connection parameters. What is very strange is that this dialog
IS the same dialog as Sybase uses for entering ODBC connection
parameters - heck there is even a tab on this dialog labeled ODBC but
the Data Source Name edit box is grayed out (disabled). If I enter in
my server name, database name, user id, password and specify TCPIP for
the links, then I do get a connection. However, then my first
DataPumpTask fails with an "Unspecified error". I did some research as
to what this "Unspecified error" might mean and there seems to be too
many possible causes to try and determine it right now... Anyhow, on a
whim I decided to create an ODBC DSN that contained the server name,
database name, user id, password, and TCPIP specified for link
(essentially duplicating the data I entered in the dialog that was
displayed to me at runtime when my DTS package was executed). My
thought was that somehow DTS was looking to find an ODBC DSN in any
case, regardless of the fact that I was asking it to create a connection
using OLEDB via Sybase's ASAProv - not an ODBC Connection. Guess what,
that did prevent the connection dialog from being displayed at runtime,
but I still get that unspecified error.

I am left to believe that I either do not have my connection defined
correctly or Sybase's driver will not work with DTS. Has anyone
successfully used Sybase's driver with DTS??? BTW, my version of Sybase
is "Sybase Adaptive Server Anywhere Network Server Version 8.0.2.4122".

You might suggest that I stick with ODBC, but I am experiencing horrible
performance and am hoping that using OLEDB would offer a performance
improvement.

Thanks in advance!
-Joey

Joey Ebright wrote:
To those who have the same question - I was able to get the connection
going. I had to go to the last page of the Data Link Properties in the
DTS designer (entitled All) and set the Extended Properties. This is
what I put in there - eng=myservername;links=TCPIP

To do this in code (this is Delphi Code, but VB and others is VERY
similar) - you do the following:

oConnection.ConnectionProperties.Item('Extended Properties').Value :=
'eng=SanMateoCons;links=TCPIP';

I am still running into some problems actually using the provider, but I
am getting good connections. The problems I am running into with using
the provider are that -

1. The OLEDB provider likes owner and table names wrapped in quotes.
For example select * from dba.table_one must be like select * from
"dba"."table_one" or "dba".table_one - it seems either will work...

2. I get an error during the firing of insert statements against the
ASA8 server - error 80004005: Option value out of range. I have no idea
why at this point. I can successfully call a stored procedure that
performs updates, but the insert statements (created via DTS DataPump
Tasks) fail....

*Anyone have any ideas ???*

Here is what my log looks like when it fails:

** REQUEST conn: 9260824 STMT_PREPARE "SET TEMPORARY
OPTION BLOCKING=ON

SET TEMPORARY OPTION BLOCKING_TIMEOUT=0"

** DONE conn: 9260824 STMT_PREPARE Stmt=65545

** REQUEST conn: 9260824 STMT_DESCRIBE_OUTPUT Stmt=-1

** DONE conn: 9260824 STMT_DESCRIBE_OUTPUT

** REQUEST conn: 9260824 STMT_DESCRIBE_INPUT Stmt=-1

** DONE conn: 9260824 STMT_DESCRIBE_INPUT

** REQUEST conn: 9260824 STMT_EXECUTE Stmt=-1

** DONE conn: 9260824 STMT_EXECUTE

** REQUEST conn: 9260824 STMT_DROP Stmt=65545

** DONE conn: 9260824 STMT_DROP

** REQUEST conn: 9260824 COMMIT

** DONE conn: 9260824 COMMIT

** REQUEST conn: 9260824 COMMIT

** DONE conn: 9260824 COMMIT

** REQUEST conn: 9260824 STMT_PREPARE "begin transaction
synchronization"

** DONE conn: 9260824 STMT_PREPARE Stmt=65546

** REQUEST conn: 9260824 STMT_DESCRIBE_OUTPUT Stmt=-1

** DONE conn: 9260824 STMT_DESCRIBE_OUTPUT

** REQUEST conn: 9260824 STMT_DESCRIBE_INPUT Stmt=-1

** DONE conn: 9260824 STMT_DESCRIBE_INPUT

** REQUEST conn: 9260824 STMT_EXECUTE Stmt=-1

** DONE conn: 9260824 STMT_EXECUTE

** REQUEST conn: 9260824 STMT_DROP Stmt=65546

** DONE conn: 9260824 STMT_DROP

** REQUEST conn: 9260824 COMMIT

** DONE conn: 9260824 COMMIT

** REQUEST conn: 9260824 COMMIT

** DONE conn: 9260824 COMMIT

** REQUEST conn: 9260824 STMT_PREPARE "select * from
"DBA"."TB_CORE_DAILY""

** DONE conn: 9260824 STMT_PREPARE Stmt=65547

** REQUEST conn: 9260824 STMT_DESCRIBE_OUTPUT Stmt=-1

** DONE conn: 9260824 STMT_DESCRIBE_OUTPUT

** REQUEST conn: 9260824 STMT_DESCRIBE_INPUT Stmt=-1

** DONE conn: 9260824 STMT_DESCRIBE_INPUT

** REQUEST conn: 9260824 STMT_EXECUTE Stmt=-1

** WARNING conn: 9260824 code: 111 "Statement cannot be executed"

** DONE conn: 9260824 STMT_EXECUTE

** REQUEST conn: 9260824 CURSOR_OPEN Stmt=65547

** DONE conn: 9260824 CURSOR_OPEN Crsr=65548

** REQUEST conn: 9260824 CURSOR_CLOSE Crsr=65548

** DONE conn: 9260824 CURSOR_CLOSE

** REQUEST conn: 9260824 COMMIT

** DONE conn: 9260824 COMMIT

** REQUEST conn: 9260824 STMT_DROP Stmt=65547

** DONE conn: 9260824 STMT_DROP

** REQUEST conn: 9260824 DISCONNECT

** DONE conn: 9260824 DISCONNECT

Joey Ebright wrote:
I have been working with Sybase on this issue. Looks like it might be a
bug in their OLEDB provider but we are not sure. Stay tuned...