Issues with connecting from .NET to Sybase ASE 12

[Ian Allchin]

If anyone out there has been successful in connecting to Sybase ASE 12
from Visual Studio .NET 2003 either in a Windows Forms or Reporting
Services project, can you please help me.

.....

In summary, I wish to:

(a) create a Windows Forms application using VB.NET (Visual Studio
..NET 2003 and .NET Framework 1.1) that will connect to Sybase ASE 12.0
to execute stored procedures.

(b) create a SQL Server Reporting Services report using Visual Studio
..NET 2003, .NET Framework 1.1 and SQL Server Reporting Services that
will connect to Sybase ASE 12.0 also via stored procedures.

I have Sybase 12 Client installed on my development machine and am
able to connect to a test server from this machine using SQL
Advantage.

My code for (a) is:

Dim sybaseconn As System.Data.OleDb.OleDbConnection

Dim sybaseconnstring As String =
"Provider=Sybase.ASEOLEDBProvider;Server Name=XXXXXX;Initial
Catalog=YYYYYY;User ID=UUUUUU;Password=PPPPPP;"

Try

sybaseconn = New System.Data.OleDb.OleDbConnection(sybaseconnstring)

sybaseconn.Open()

Catch ex As Exception

MsgBox(ex.Message)

Finally

Select Case sybaseconn.State

Case ConnectionState.Broken, ConnectionState.Closed

Case Else

sybaseconn.Close()

sybaseconn.Dispose()

End Select

sybaseconn = Nothing

End Try

When I run this, I receive the following message on the Open() line:

"The 'Sybase.ASEOLEDBProvider' provider is not registered on the local
machine."

and this is the exception:

?ex

{System.InvalidOperationException}

[System.InvalidOperationException]:
{System.InvalidOperationException}

HelpLink: Nothing

InnerException: {System.Data.OleDb.OleDbException}

Message: "The 'Sybase.ASEOLEDBProvider' provider is not registered
on the local machine."

Source: "System.Data"

StackTrace: " at
System.Data.OleDb.OleDbConnection.CreateProviderError(Int32 hr)

at System.Data.OleDb.OleDbConnection.CreateProvider(OleDbConnectionString
constr)

at System.Data.OleDb.OleDbConnection.Open()

at Sybase_Stuff.Form1.Button1_Click(Object sender, EventArgs e) in
C:\Documents and Settings\allchini\My Documents\Visual Studio
Projects\Sybase Stuff\Form1.vb:line 98"

TargetSite: {System.Reflection.RuntimeMethodInfo}



My problem with (b) is as follows:

In my Reporting Services project in Visual Studio .NET 2003 I attempt
to set up a shared data source by selecting Add New Datasource. This
displays the Data Link Properties dialog. On the Provider tab, I
select "Sybase ASE OLE DB Provider". When I press Next or OK, I
receive the following message:



Microsoft Data Link Error

Provider is no longer available. Ensure that the provider is
installed properly.


Can you tell me how to achieve (a) and/ or (b)?

Thanks

Ian Allchin

 

[Ian Allchin]

Further to my earlier posting, I contacted Sybase who were helpful. I
have resolved the first issue. My Sybase installation was corrupted,
so I uninstalled completely. I then installed Sybase ASE 12.5.2 PC
Client which includes the ASE ADO .NET Provider. I have been able to
write a .NET Windows Forms application using this provider in very
much the same way as the SQL Server .NET Provider.

Information about the Sybase ASE .NET Data Provider can be found at:

http://sybooks.sybase.com/onlinebooks/group-adonet/asnetg0110e/adonet

I AM STILL LOOKING FOR HELP ON HOW TO CONNECT TO SYBASE ASE 12 FROM A
SQL SERVER REPORTING SERVICES PROJECT. PLEASE RESPOND IF YOU HAVE ANY
INFORMATION.

Ian Allchin
Alpha Ventures Limited, UK

 

[Robert Bruckner [MSFT]]

I have not done it myself, but this should get you started with using Sybase
ASE 12 in Reporting Services:

* First, you have to make sure the ASE data provider is installed correctly,
so that the report designer in VS.NET and report server inside ASP.NET are
able to load the dlls of the data provider at runtime. Note: there could be
file system permissions issues which prevent ASP.NET from accessing the ASE
data provider dlls. You might want to try to copy the dlls directly into the
report designer and report server installation directories.

* close down all VS.NET sessions before modifying rsReportDesigner.config
* backup the existing rsReportDesigner.config and rsReportServer.config

* Add these entries to rsReportDesigner.config in the Data section:
<Extension Name="Sybase"
Type="Sybase.Data.AseClient.AseConnection,Sybase.Data.AseClient"/>
.... and in the Designer section:
<Extension Name="Sybase"
Type="Microsoft.ReportDesigner.Design.GenericQueryDesigner,Microsoft.Reporti
ngServices.Designer"/>

* Stop the ReportServer Windows service
* Add this entry to rsReportServer.config in the Data section:
<Extension Name="Sybase"
Type="Sybase.Data.AseClient.AseConnection,Sybase.Data.AseClient"/>
* Restart ReportServer Windows service
* Reset IIS/ASP.NET by running iisreset from the command line

There are several people on this newsgroups who have indicated they are
using Sybase. Not sure if they have tried the managed Sybase provider.

You might also want to read this thread:
http://msdn.microsoft.com/newsgroup...2812e9-65de-4007-a142-b689522d4c93&sloc=en-us
As indicated in the thread, it should work on the report server, but I'm not
sure about the report designer. It will depend on how the Sybase ASE
provider is implemented internally.

Documentation on the config files:
http://msdn.microsoft.com/library/d.../RSPROG/htm/rsp_prog_extend_dataproc_8iqq.asp

 

[Bruce Loehle-Conger]

I have used both the ODBC and OLEDB providers. I have not used the dotnet
managed provider. Correct me if I am wrong but it would only use the
provider if you used the generic designer. At runtime it would use it but
during development only the oledb provider?

Bruce L-C

 

[Robert Bruckner [MSFT]]

If you register any managed data provider in report designer and select it
in the data source dialog, the designer will only enable the generic query
designer. The toggle icon for the visual query designer will be disabled
(because the visual query designer is not capable of working with managed
data providers right now as discussed in the referenced thread below).

--
This posting is provided "AS IS" with no warranties, and confers no rights.

I have used both the ODBC and OLEDB providers. I have not used the dotnet
managed provider. Correct me if I am wrong but it would only use the
provider if you used the generic designer. At runtime it would use it but
during development only the oledb provider?

Bruce L-C

I have not done it myself, but this should get you started with using Sybase
ASE 12 in Reporting Services:

* First, you have to make sure the ASE data provider is installed correctly,
so that the report designer in VS.NET and report server inside ASP.NET are
able to load the dlls of the data provider at runtime. Note: there could be
file system permissions issues which prevent ASP.NET from accessing the ASE
data provider dlls. You might want to try to copy the dlls directly into the
report designer and report server installation directories.

* close down all VS.NET sessions before modifying rsReportDesigner.config
* backup the existing rsReportDesigner.config and rsReportServer.config

* Add these entries to rsReportDesigner.config in the Data section:
<Extension Name="Sybase"
Type="Sybase.Data.AseClient.AseConnection,Sybase.Data.AseClient"/>
... and in the Designer section:
<Extension Name="Sybase"

Type="Microsoft.ReportDesigner.Design.GenericQueryDesigner,Microsoft.Reporti

ngServices.Designer"/>

* Stop the ReportServer Windows service
* Add this entry to rsReportServer.config in the Data section:
<Extension Name="Sybase"
Type="Sybase.Data.AseClient.AseConnection,Sybase.Data.AseClient"/>
* Restart ReportServer Windows service
* Reset IIS/ASP.NET by running iisreset from the command line

There are several people on this newsgroups who have indicated they are
using Sybase. Not sure if they have tried the managed Sybase provider.

You might also want to read this thread:

http://msdn.microsoft.com/newsgroup...2812e9-65de-4007-a142-b689522d4c93&sloc=en-us

As indicated in the thread, it should work on the report server, but I'm not
sure about the report designer. It will depend on how the Sybase ASE
provider is implemented internally.

Documentation on the config files:

http://msdn.microsoft.com/library/d.../RSPROG/htm/rsp_prog_extend_dataproc_8iqq.asp

 

[Ian Allchin]

First of all thanks to Robert and Bruce for your comments. The DBA
and I have followed the instructions regarding modifying the designer
and server installations, have made some progress on this issue but
are still experiencing some problems ...

On the server, the Sybase .NET provider does not appear in the list of
available connection types within the New Data Source page. We still
just have: Microsoft SQL Server, OLE DB, Oracle and ODBC.

On the client, I have some mixed results:

Sybase IS available as a type in the shared datasource dialog. This
looked encouraging to start with.

I have set up a shared datasource using a connection that I know works
with the ASE .NET provider (tested in a Windows Forms project) and
then tried to used that shared datasource.

When I create a new dataset, with the shared dataset, the only command
type that is available is text (whereas for SQL Server there are 3
options including Stored Procedure).

Within the Generic Query Designer, the Stored Procedure command type
is available.

When I try to create a dataset using a simple text command such as
"SELECT field1 FROM dbo.table1", field1 appears in the field list and
if I press the exclamation mark button field1 is retrieved from the
database OK. However, if I add field1 into a table on the layout tab
and then try to switch to the preview tab, I receive the following
error message:

An error has occurred during report processing.
Operation is not valid due to the current state of the object.

I have also attempted various ways of executing a Sybase stored
procedure with a single integer parameter, using both text and stored
procedure command types, none of which will preview. The most common
message I receive is:

The procedure expected paramater <@param_name> which was not provided.

Since it is recognising it is a procedure, and picking up the expected
parameter, this also seems promising, but there appear to be mapping
problems.

Ultimately, I want to re-use existing Sybase stored procedures (with
parameters of various data types) within reporting services, so I need
to resolve this.

Any help would be greatly appreciated.

Ian Allchin
Alpha Ventures Limited, UK

 

[Ian Allchin]

Hi Santiago

Your posting sounds as if you are having some success as you say "it
works". I am also having some issues as described in my most recent
posting on 20 August 2004. For me, reports where the dataset is built
from even text commands and stored procedures with or without
parameters are not working (although if I press the exclamation mark
button, the rows are returned in the grid at the bottom of the generic
query designer). Are you experiencing the same problems or have you
managed to successfully preview or deploy any reports using the Sybase
..NET Provider? If you have, I would be really grateful if you would
describe exactly how you have done it.

If I have any more news, I will post back.

Many thanks

Ian Allchin
Alpha Ventures Limited, UK

 

[Bruce Loehle-Conger]

If you can't get the dotnet provider working then I suggest trying the ODBC
or the OLEDB provider. I had an issue with the OLEDB provider, if you setup
multiple OLEDB sources it always wants to use the first one. The Sybase site
had a workaround but it did not work for me (maybe I didn't implement the
workaround correctly). I didn't want to waste anymore time so I went with
the ODBC. The ODBC has worked well and consistantly for me. The only issue I
have had is with stored procedure with parameters. I am currently going
against 11.9.2 backend with a 12.5.2 client. I am upgrading the server to
12.5.2 shortly and hope that the stored procedure problem goes away (I
believe someone that had posted previously had stored procedures working
with ODBC against a 12.5 server). Anyway, I suggest giving the ODBC driver a
chance.

Wait a minute, I just re-read your posting there. You say you get rows
returned. Have you tried the refresh fields button (it looks like the
refresh a web page button in IE). Try that and see if you get your field
list. If you are returning rows then things should be OK.

Bruce L-C

 

[Ian Allchin]

Bruce

Thanks for the information regarding ODBC and OLEDB. I have already
tried OLEDB with some success (ie I did manage to call stored
procedures and preview reports), although I had some minor issues with
using 12.5.2 client against 12.0 server. I will revisit this and ODBC
if I cannot resolve the current issues with the ADO .NET route, which
I what Sybase recommended.

My current situation with the ADO .NET provider is that I can connect
to a database and execute a non parameterised text command within the
generic query designer - eg SELECT * FROM tablename. My field list
appears without me pressing refresh fields and if I press the
exclamation mark button I see my rows returned to the grid. However,
when I add fields to a table control on the layout tab and then try to
switch to the preview tab, I receive an error message:

An error has occurred during report processing.
Operation is not valid due to the current state of the object.

I have also tried EXEC storedprocedurename 1 (where 1 is a hard coded
argument) as a text command. Reporting services brings back my
expected field list and returns rows in the grid. However, when I add
fields to a table control on the layout tab and then try to switch to
the preview tab, I receive the same error message.

The reason I am using text commands is that this appears to be the
only option that is fully available and other attempts to deal with
stored procedures have caused different error messages (see my posting
20 August 2004).

Stop Press:
I am able to build and deploy the report. When selecting the report
on the portal, I receive a more detailed error message:

An error has occurred during report processing. (rsProcessingAborted)
An attempt has been made to use a data extension 'Sybase' that is not
registered for this report server. (rsDataExtensionNotFound)

OK, so that may explain the "invalid state of the object".

Does anyone know what we have to do to register it correctly? Also,
should the stored procedure option be available as a command type like
with SQL Server?

Thanks

Ian Allchin
Alpha Ventures Limited UK

 

[Bruce Loehle-Conger]

Sorry I can't help anymore with this. I would like to use the dotnet
provider for Sybase as well if it can be made to work.

Bruce L-C

 

[Ian Allchin]

I have now had a response from Microsoft technical support. They said
that the Sybase ADO .NET Provider is not a Reporting Services Data
Extension. These are two different things. Sybase would need to
provide a Reporting Services Data Extension.

The Microsoft technical support person recommended trying OLEDB and
only using ODBC if OLEDB was unsuccessful. I intend to follow this
advice, but I will also contact Sybase and ask if they have any plans
to provide a Reporting Services Data Extension and post back.

Ian Allchin
Alpha Ventures Limited, UK

 

[Bruce Loehle-Conger]

Interesting. I thought all that was needed was a dotnet data provider.
Didn't know that it was more than that. Thanks for posting back on this.

Bruce L-C

 

[Ian Allchin]

I have contacted Sybase. They are now considering whether to
implement a Reporting Services Data Extension. A decision is expected
within the next few months and I will post back if I receive any news
on this. In the meantime, I will be going back to try OLEDB and ODBC.

Ian Allchin
Alpha Ventures Limited, UK

 

[Bruce Loehle-Conger]

Thanks for the update. If you have any problems with either OLEDB or ODBC
post back here. I have used ODBC extensively with Sybase and am now
migrating to 12.5.2. Others have used OLEDB successfully (and I will be
giving that a try shortly as well).

Thanks again, now I won't waste any time with with the dotnet provider.

Bruce L-C