Reporting Service and .Net Data provider -- sql server reporting services

sql server reporting services : Reporting Service and .Net Data provider

Bruce Loehle-Conger
9/16/2004 10:46:13 AM

Currently two dot net dataproviders are used by RS. SQL Server and Oracle.
Your other options are ODBC and Oledb providers (I have used both of these
as well as going to SQL Server).

What is it you are trying to do? If you want to do your own data provider
then a better way is to create a data extension. That is supported and many
people have done it. I know that there is some interface that has to be
supported for the data provider to work within the designer. The Sybase
dotnet provider did not support the needed interface and can not be used
within the designer.

Bruce L-C

[quoted text, click to view]
Type="Microsoft.ReportDesigner.Design.GenericQueryDesigner,Microsoft.Reporti
ngServices.Designer"/>
[quoted text, click to view]

DD
9/16/2004 6:33:53 PM

Question: why .Net Data provider does not work with Reporting Service.

I have created sample .Net Data Provider as recommented:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontemplatefiles.asp

I added string name and version number and added assembly into GAC.
Console test application works fine.

but Reporting service gives me error when I create DataSoure:
---------------------------
Microsoft Development Environment
---------------------------
A connection cannot be made to the database.
Set and test the connection string.

after [OK] press. I can se error message in the Data page: "The query could
not be loaded. Verify your connection and querry string."

Why?

I have modified
"C:\Program Files\Microsoft SQL Server\80\Tools\Report
Designer\RSReportDesigner.config"
<Data>
<Extension Name="TEMP"
Type="TemplateProvider.TemplateConnection,TemplateProvider,Version=1.2.1.1,
Culture=neutral, PublicKeyToken=b2520ee0b52bee09"/>
....
<Designer>
<Extension Name="TEMP"
Type="Microsoft.ReportDesigner.Design.GenericQueryDesigner,Microsoft.ReportingServices.Designer"/>
....

and
"C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\RSReportServer.config"
<Data>
<Extension Name="TEMP"
Type="TemplateProvider.TemplateConnection,TemplateProvider,Version=1.2.1.1,
Culture=neutral, PublicKeyToken=b2520ee0b52bee09"/>

When I creating new DataSource I can see provider "TEMP", [Edit] is
disabled.

Please point me what can be wrong. I just want to test is Report Servises
works with .Net Data provider.

thanks in advance,
Darius D

Bruce Loehle-Conger
9/20/2004 10:36:56 AM

All I know is that for a Sybase dotnet managed provider to work in the
development environment it needed to implement the Reporting Services Data
Extension interface.

One other point that I know is true at least for the Oracle managed
provider. From the development UI the Oracle managed provider is only used
from the Generic Query designer. Otherwise it uses the Oledb provider. The
reason for this is that RS is that the query designer they are using does
not natively support dotnet managed providers so that is why the managed
provider is only used from the generic query designer. I would assume the
same would hold true for your custome managed provider.

Bruce L-C

[quoted text, click to view]

DD
9/20/2004 6:17:37 PM

I got it working. I mean custom .Net data provider with Reporting service.
I dont want to use data extensions because I need to uses custom .net
provider and as I know reporting service should support that.
But I connot find information how to do it.
Now I have such problem:

When I use Generic Query Designer I can write select queries I need and I
get results I want.
But I want to start to use VDTQueryDesigner.
When I press on [Generic Query Designer] which I expect should query schema
I get error:
---------------------------
Microsoft Development Environment
---------------------------
The following exception has occurred:
NullReferenceException: Object reference not set to an instance of an
object.
---------------------------
OK
---------------------------

My custom .Net data Provider implements customDataReader where I pass
DataSet. DataSet I filling using such code:

DataSet pDataSet = new DataSet();
string aConnString = "User ID = USR; Persist Security Info=False;
database=DB; server=SRV; Connect Timeout=30";
SqlConnection aConnection = new SqlConnection( aConnString );
SqlCommand aCommand = new SqlCommand(sCmd, aConnection);
aConnection.Open();
SqlDataAdapter aSqlDataAdapter = new SqlDataAdapter();
aSqlDataAdapter.SelectCommand = aCommand;
if (pBehaviour == CommandBehavior.Default)
{
aSqlDataAdapter.Fill(pDataSet, "REZULT_TABLE");
}
else if (pBehaviour == CommandBehavior.SchemaOnly)
{
aSqlDataAdapter.FillSchema(pDataSet, SchemaType.Source);
}
//needs try/catch/finally
aConnection.Close();
return pDataSet;

When [Generic Query Designer] is clicked
aSqlDataAdapter.FillSchema(pDataSet, SchemaType.Source);
is executed. Custom DataReader loops over public Type GetFieldType(int i)
( implements IDataReader )
after if jumps to void IDisposable.Dispose() of custom DataReader and after
return of this function I got the exeption printed above.

I have the feeling I load not correct schema information or my custom Data
provider does not implement( or does it bad) all needed functions

Any comments?
Thanks in advance,
Darius D

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server reporting services

home · blog · groups

Questions? Comments? Contact the dn