OLE DB connection string in SharePoint for Sybase ASA

[Beth]

I'm trying to use an "OLE DB custom connection string" as required in
SharePoint to connect to my Sybase Adaptive Server Anywhere database. I have
tried EVERY syntax possible, and depending on which syntax I use, I get
different errors. According to http://www.connectionstrings.com, the syntax
for OLE DB should be a simple:

Provider=ASAProv;Data Source=amaasa1

As I understand it, the Data Source is the server name, and NOT an ODBC dsn.
I ddo have system dsn's set up on both machines. I've tried adding all the
other possible parameters such as uid, pwd, etc. etc. I even set up a MS udl
and let it "build" a string from that, and tried that, but it didn't work
either. Sybase has a dbping utility that I can ping that ASA server from the
SharePoint box, of course, that doesn't use OLE DB (or any provider). I have
checked the settings and added the required info for the Proxy Server and
configured the data retrieval services. All to no avail. Does anyone have
ANY suggestions on how to use a custom OLE DB connection string to connect to
a database in SharePoint? This was SO EASY via ODBC in regular FP. HEP!
Thanks!

 

[Kevin Spencer]

You may get different errors, but you only get ONE error per Connection
String. It would help to know what these "different errors" ARE. The purpose
of an error message is to help diagnose the problem.

As you haven't posted any, I will take a (gulp!) wild guess. Do you have the
OLE DB provider for your database installed on your machine?

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Neither a follower nor a lender be.

 

[Beth]

EEEK! Don't GULP! The messages are very generic, I WISH they were more
specific! Yes, I have manually done the regsvr32 cmd to install the OLE DB
provider for the ASA db. I have checked the registry and both ASAProv and
ASAProv.80 are both there (I have tried using both). Also, I have tried
MSDASQL, but really don't understand how that works...and oledb provider for
odbc...???

Anyway let me be more specific. EEK! I open the SharePoint website via
FrontPage. I open a new blank page and click on Data, Insert Data. On the
right, the task pane comes up and where it says Database Connections(0) is
where I WANT it to say Database Connections(1). In the old FrontPage I
was able to add my db quite easily. Anyhoo, I click on "Add to Catalog"
and up pops (like Magic) a Data Source Properties Window. I type in
something meaningless in the Name box under the General Tab. On the Source
Tab, I click Configure Database Connection and that's where the fun starts.
Since our illustrious management won't buy us SQL server, we must use our
"approved" Sybase ASA server/database. Therefore, I mark "use custom
connection string" radio button and click on edit. when I try the simplest
string (Provider=ASAProv;Data Source=amaasa1 here is the error I get:

Server Error: An error occurred while retrieving the list of Databases from
amaasa1; The Web server cannot connect to the specified data source because
the query may contain errors, the data source may be unavailable or
configured improperly, or your Web site is behind a proxy server or firewall.

If your Web site is behind a proxy server or firewall, verify that your
server administrator (that's me) has configured the Data Retrieval Services
HTTP proxy server setting for Microsoft Windows SharePoint Services (I have).

That's pretty much the 'standard' error I get... I pulled up MS Article
#831092 "You Receive a "Server Error: An error occurred while retrieving a
list of Databaes" error message when you open a Windows SharePoint Services
Web site in FrontPage 2003 and configure a database connection" and did
everything in the article, except for the two SQL Server items directly below
the example of an ODBC connection string.

When I put quotes around the same short connection string, I get this:

Server Error: An error occurred while retrieving the list of Databases from
amaasa1: The server for the data source returned a non-specific error when
trying to execute your query. Check the format and content of your query and
try again. If the problem persists, contact the server administrator (lucky
me, eh?)

When I try syntax similar to that listed as an example in above-mentioned
article, I get get the same errors as above.

Thank you SOO SOOO MUCH for helping me.
Beth

 

[Kevin Spencer]

Thanks Beth. That was very helpful.

I wen to the Sybase site and found a reference to Connection String
parameters. You might want to review it:

http://sybooks.sybase.com/onlineboo...neric__BookTextView/5012;td=50;pt=5012;uf=0#X

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Neither a follower nor a lender be.

 

[Kevin Spencer]

BTW, you can also use Access, which won't cost your company any money,
unless nobody there has Access!

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Neither a follower nor a lender be.

 

[Beth]

Yes Kevin, I have reviewed that page and tried many alternate connection
strings. Unfortunately, none of them work.
Can you maybe give me an example of how to use the MSFT OLE DB driver for
odbc (MSDASQL) in that SharePoint connection string setting?
beth

 

[Thomas A. Rowe]

Beth, if you are using SharePoint Services under Windows 2003 server, the only databases supported
are MS SQL 2000 Server SP3 or MS SQL Server 2000 Desktop Engine.

See the following for SharePoint Requirements:
http://www.microsoft.com/resources/documentation/wss/2/all/adminguide/en-us/stsb01.mspx

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WEBMASTER Resources(tm)

FrontPage Resources, WebCircle, MS KB Quick Links, etc.
==============================================

 

[Beth]

Hi Thomas and thanks for jumping in. The server running SharePoint IS
running under MS SQL server sp3...that's required for SharePoint to run. The
SharePoint stuff is installed and running ok, as far as I know. The database
I'm trying to connect to is corporate data on a different application server
that is running Sybase Adaptive Server Anywhere 8.0. The way I understand
the MSFT documentation, I should be able to connect to all different kinds of
databases, i.e, oracle, db2, Sybase, etc. to create a "data view". We are
not allowed to use the MSSQL server for data due to licensing issues, we are
required to use our sybase app server. And Kevin, thanks for suggesting
Access, but this database is much too big for Access, although we do use
Access quite a bit for front-ends. Any help is SO GREATLY appreciated! I
just can't believe this was such a 'piece of cake' connecting to a database
in FrontPage with FPSE and so dang difficult thru SharePoint! Thank you
again, I appreciate you both! ~beth

 

[Kevin Spencer]

Yes Kevin, I have reviewed that page and tried many alternate connection

strings. Unfortunately, none of them work.
Can you maybe give me an example of how to use the MSFT OLE DB driver for
odbc (MSDASQL) in that SharePoint connection string setting?

It wouldn't help. Each DBMS has its own proprietary set of Connection String
parameters. Have you tried contacting Sybase technical support?

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Neither a follower nor a lender be.