Overwrite query

Can I call web service to overwrite the query ? If yes, how can do it ?Only by republishing the report. Or you can make the query based on an
expression and pass the value in.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
news:F26B1DA5-B0E0-4536-913B-6325421FBBB2@.microsoft.com...
> Can I call web service to overwrite the query ? If yes, how can do it
> ?|||In reporting services books online, I find some class such as
DataSetDefinition, DataSourceDefinition or QueryDefinition. Is there any
other example that teach us to overwrite the reports original query ?
Since I have some reports which the template and parameter are same but with
different select statment.
"Brian Welcker [MSFT]" wrote:
> Only by republishing the report. Or you can make the query based on an
> expression and pass the value in.
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
> news:F26B1DA5-B0E0-4536-913B-6325421FBBB2@.microsoft.com...
> > Can I call web service to overwrite the query ? If yes, how can do it
> > ?
>
>|||When i try to overwrite the query, the following error appear:
The permissions granted to user 'WS-HKG-IT5670A\ASPNET' are insufficient for
performing this operation. -->
Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: The
permissions granted to user 'WS-HKG-IT5670A\ASPNET' are insufficient for
performing this operation. at
Microsoft.ReportingServices.Library.RSService._SetReportDataSources(String
report, DataSource[] dataSources) at
Microsoft.ReportingServices.Library.RSService.SetReportDataSources(Guid
batchId, String report, DataSource[] dataSources) at
Microsoft.ReportingServices.WebServer.ReportingService.SetReportDataSources(String
Report, DataSource[] DataSources) -- End of inner exception stack trace --
at
Microsoft.ReportingServices.WebServer.ReportingService.SetReportDataSources(String Report, DataSource[] DataSources)
Can anyone help me ?
"May Liu" wrote:
> In reporting services books online, I find some class such as
> DataSetDefinition, DataSourceDefinition or QueryDefinition. Is there any
> other example that teach us to overwrite the reports original query ?
> Since I have some reports which the template and parameter are same but with
> different select statment.
> "Brian Welcker [MSFT]" wrote:
> > Only by republishing the report. Or you can make the query based on an
> > expression and pass the value in.
> >
> > --
> > Brian Welcker
> > Group Program Manager
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> > "May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
> > news:F26B1DA5-B0E0-4536-913B-6325421FBBB2@.microsoft.com...
> > > Can I call web service to overwrite the query ? If yes, how can do it
> > > ?
> >
> >
> >|||I have solve it but another problem come out. The error is as follow:
System.Web.Services.Protocols.SoapException:
System.Web.Services.Protocols.SoapException: The required field Extension is
missing from the input structure. -->
Microsoft.ReportingServices.Diagnostics.Utilities.MissingElementException:
The required field Extension is missing from the input structure. at
Microsoft.ReportingServices.DataExtensions.DataSourceInfo.ValidateDefinition()
at
Microsoft.ReportingServices.Library.Soap.DataSourceDefinition.ThisToDataSourceInfo(String
name, String originalName, DataSourceDefinition definition) at
Microsoft.ReportingServices.Library.Soap.DataSourceDefinitionOrReference.ThisToDataSourceInfo(DataSourceDefinitionOrReference
item, String name) at
Microsoft.ReportingServices.Library.Soap.DataSource.ThisToDataSourceInfo(DataSource
dataSource) at
Microsoft.ReportingServices.Library.Soap.DataSource.ThisArrayToDataSourceInfoCollection(DataSource[]
dataSources) at
Microsoft.ReportingServices.Library.RSService._SetReportDataSources(String
report, DataSource[] dataSources) at
Microsoft.ReportingServices.Library.RSService.SetReportDataSources(Guid
batchId, String report, DataSource[] dataSources) at
Microsoft.ReportingServices.WebServer.ReportingService.SetReportDataSources(String
Report, DataSource[] DataSources) -- End of inner exception stack trace --
at
Microsoft.ReportingServices.WebServer.ReportingService.SetReportDataSources(String Report, DataSource[] DataSources)
My code is as follow:
dataSrcDef.ConnectString = "data source=TEST"
dataSrcDef.UserName = "user"
dataSrcDef.Password = "password"
QueryDef.CommandText = txtSQL.Text
dataSetDef.Query = QueryDef
dataSetDef.Name = "DS_DEVE"
rsDataSource(0) = New DataSource
rsDataSource(0).Item = dataSrcDef
rsDataSource(0).Name = "DS Deve"
RptSvc.Credentials = System.Net.CredentialCache.DefaultCredentials
RptSvc.SetReportDataSources(reportPath, rsDataSource)
What happens to my code ? Can anyone help me ?
"May Liu" wrote:
> When i try to overwrite the query, the following error appear:
> The permissions granted to user 'WS-HKG-IT5670A\ASPNET' are insufficient for
> performing this operation. -->
> Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: The
> permissions granted to user 'WS-HKG-IT5670A\ASPNET' are insufficient for
> performing this operation. at
> Microsoft.ReportingServices.Library.RSService._SetReportDataSources(String
> report, DataSource[] dataSources) at
> Microsoft.ReportingServices.Library.RSService.SetReportDataSources(Guid
> batchId, String report, DataSource[] dataSources) at
> Microsoft.ReportingServices.WebServer.ReportingService.SetReportDataSources(String
> Report, DataSource[] DataSources) -- End of inner exception stack trace --
> at
> Microsoft.ReportingServices.WebServer.ReportingService.SetReportDataSources(String Report, DataSource[] DataSources)
> Can anyone help me ?
> "May Liu" wrote:
> > In reporting services books online, I find some class such as
> > DataSetDefinition, DataSourceDefinition or QueryDefinition. Is there any
> > other example that teach us to overwrite the reports original query ?
> > Since I have some reports which the template and parameter are same but with
> > different select statment.
> >
> > "Brian Welcker [MSFT]" wrote:
> >
> > > Only by republishing the report. Or you can make the query based on an
> > > expression and pass the value in.
> > >
> > > --
> > > Brian Welcker
> > > Group Program Manager
> > > SQL Server Reporting Services
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no rights.
> > >
> > > "May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
> > > news:F26B1DA5-B0E0-4536-913B-6325421FBBB2@.microsoft.com...
> > > > Can I call web service to overwrite the query ? If yes, how can do it
> > > > ?
> > >
> > >
> > >|||The error reflects the fact that you need to set the DataSource.Extension to
"SQL". However, you are going down the wrong path - you can't set the query
this way, only the connection string. The query is not directly accessible
from the server.
I would go down the path of dynamically constructing the query in the
report.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
news:586B7F51-D5CD-4CFE-BECF-43393AFD6A12@.microsoft.com...
>I have solve it but another problem come out. The error is as follow:
> System.Web.Services.Protocols.SoapException:
> System.Web.Services.Protocols.SoapException: The required field Extension
> is
> missing from the input structure. -->
> Microsoft.ReportingServices.Diagnostics.Utilities.MissingElementException:
> The required field Extension is missing from the input structure. at
> Microsoft.ReportingServices.DataExtensions.DataSourceInfo.ValidateDefinition()
> at
> Microsoft.ReportingServices.Library.Soap.DataSourceDefinition.ThisToDataSourceInfo(String
> name, String originalName, DataSourceDefinition definition) at
> Microsoft.ReportingServices.Library.Soap.DataSourceDefinitionOrReference.ThisToDataSourceInfo(DataSourceDefinitionOrReference
> item, String name) at
> Microsoft.ReportingServices.Library.Soap.DataSource.ThisToDataSourceInfo(DataSource
> dataSource) at
> Microsoft.ReportingServices.Library.Soap.DataSource.ThisArrayToDataSourceInfoCollection(DataSource[]
> dataSources) at
> Microsoft.ReportingServices.Library.RSService._SetReportDataSources(String
> report, DataSource[] dataSources) at
> Microsoft.ReportingServices.Library.RSService.SetReportDataSources(Guid
> batchId, String report, DataSource[] dataSources) at
> Microsoft.ReportingServices.WebServer.ReportingService.SetReportDataSources(String
> Report, DataSource[] DataSources) -- End of inner exception stack
> trace --
> at
> Microsoft.ReportingServices.WebServer.ReportingService.SetReportDataSources(String
> Report, DataSource[] DataSources)
> My code is as follow:
> dataSrcDef.ConnectString = "data source=TEST"
> dataSrcDef.UserName = "user"
> dataSrcDef.Password = "password"
> QueryDef.CommandText = txtSQL.Text
> dataSetDef.Query = QueryDef
> dataSetDef.Name = "DS_DEVE"
> rsDataSource(0) = New DataSource
> rsDataSource(0).Item = dataSrcDef
> rsDataSource(0).Name = "DS Deve"
> RptSvc.Credentials = System.Net.CredentialCache.DefaultCredentials
> RptSvc.SetReportDataSources(reportPath, rsDataSource)
> What happens to my code ? Can anyone help me ?
> "May Liu" wrote:
>> When i try to overwrite the query, the following error appear:
>> The permissions granted to user 'WS-HKG-IT5670A\ASPNET' are insufficient
>> for
>> performing this operation. -->
>> Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException:
>> The
>> permissions granted to user 'WS-HKG-IT5670A\ASPNET' are insufficient for
>> performing this operation. at
>> Microsoft.ReportingServices.Library.RSService._SetReportDataSources(String
>> report, DataSource[] dataSources) at
>> Microsoft.ReportingServices.Library.RSService.SetReportDataSources(Guid
>> batchId, String report, DataSource[] dataSources) at
>> Microsoft.ReportingServices.WebServer.ReportingService.SetReportDataSources(String
>> Report, DataSource[] DataSources) -- End of inner exception stack
>> trace --
>> at
>> Microsoft.ReportingServices.WebServer.ReportingService.SetReportDataSources(String
>> Report, DataSource[] DataSources)
>> Can anyone help me ?
>> "May Liu" wrote:
>> > In reporting services books online, I find some class such as
>> > DataSetDefinition, DataSourceDefinition or QueryDefinition. Is there
>> > any
>> > other example that teach us to overwrite the reports original query ?
>> > Since I have some reports which the template and parameter are same but
>> > with
>> > different select statment.
>> >
>> > "Brian Welcker [MSFT]" wrote:
>> >
>> > > Only by republishing the report. Or you can make the query based on
>> > > an
>> > > expression and pass the value in.
>> > >
>> > > --
>> > > Brian Welcker
>> > > Group Program Manager
>> > > SQL Server Reporting Services
>> > >
>> > > This posting is provided "AS IS" with no warranties, and confers no
>> > > rights.
>> > >
>> > > "May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
>> > > news:F26B1DA5-B0E0-4536-913B-6325421FBBB2@.microsoft.com...
>> > > > Can I call web service to overwrite the query ? If yes, how can
>> > > > do it
>> > > > ?
>> > >
>> > >
>> > >|||Sorry, I don't understand what you mean. Does reporting service provide web
service that can allow me to change the query dynamically. I see there are
many web service such as DataSourceDfinition, DataSetDefinition or
QueryDefinition. Actually, I am quite confusing about them and their
relationship.
"Brian Welcker [MSFT]" wrote:
> The error reflects the fact that you need to set the DataSource.Extension to
> "SQL". However, you are going down the wrong path - you can't set the query
> this way, only the connection string. The query is not directly accessible
> from the server.
> I would go down the path of dynamically constructing the query in the
> report.
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
> news:586B7F51-D5CD-4CFE-BECF-43393AFD6A12@.microsoft.com...
> >I have solve it but another problem come out. The error is as follow:
> >
> > System.Web.Services.Protocols.SoapException:
> > System.Web.Services.Protocols.SoapException: The required field Extension
> > is
> > missing from the input structure. -->
> > Microsoft.ReportingServices.Diagnostics.Utilities.MissingElementException:
> > The required field Extension is missing from the input structure. at
> > Microsoft.ReportingServices.DataExtensions.DataSourceInfo.ValidateDefinition()
> > at
> > Microsoft.ReportingServices.Library.Soap.DataSourceDefinition.ThisToDataSourceInfo(String
> > name, String originalName, DataSourceDefinition definition) at
> > Microsoft.ReportingServices.Library.Soap.DataSourceDefinitionOrReference.ThisToDataSourceInfo(DataSourceDefinitionOrReference
> > item, String name) at
> > Microsoft.ReportingServices.Library.Soap.DataSource.ThisToDataSourceInfo(DataSource
> > dataSource) at
> > Microsoft.ReportingServices.Library.Soap.DataSource.ThisArrayToDataSourceInfoCollection(DataSource[]
> > dataSources) at
> > Microsoft.ReportingServices.Library.RSService._SetReportDataSources(String
> > report, DataSource[] dataSources) at
> > Microsoft.ReportingServices.Library.RSService.SetReportDataSources(Guid
> > batchId, String report, DataSource[] dataSources) at
> > Microsoft.ReportingServices.WebServer.ReportingService.SetReportDataSources(String
> > Report, DataSource[] DataSources) -- End of inner exception stack
> > trace --
> > at
> > Microsoft.ReportingServices.WebServer.ReportingService.SetReportDataSources(String
> > Report, DataSource[] DataSources)
> >
> > My code is as follow:
> > dataSrcDef.ConnectString = "data source=TEST"
> > dataSrcDef.UserName = "user"
> > dataSrcDef.Password = "password"
> >
> > QueryDef.CommandText = txtSQL.Text
> > dataSetDef.Query = QueryDef
> > dataSetDef.Name = "DS_DEVE"
> >
> > rsDataSource(0) = New DataSource
> > rsDataSource(0).Item = dataSrcDef
> > rsDataSource(0).Name = "DS Deve"
> >
> > RptSvc.Credentials = System.Net.CredentialCache.DefaultCredentials
> > RptSvc.SetReportDataSources(reportPath, rsDataSource)
> >
> > What happens to my code ? Can anyone help me ?
> >
> > "May Liu" wrote:
> >
> >> When i try to overwrite the query, the following error appear:
> >>
> >> The permissions granted to user 'WS-HKG-IT5670A\ASPNET' are insufficient
> >> for
> >> performing this operation. -->
> >> Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException:
> >> The
> >> permissions granted to user 'WS-HKG-IT5670A\ASPNET' are insufficient for
> >> performing this operation. at
> >> Microsoft.ReportingServices.Library.RSService._SetReportDataSources(String
> >> report, DataSource[] dataSources) at
> >> Microsoft.ReportingServices.Library.RSService.SetReportDataSources(Guid
> >> batchId, String report, DataSource[] dataSources) at
> >> Microsoft.ReportingServices.WebServer.ReportingService.SetReportDataSources(String
> >> Report, DataSource[] DataSources) -- End of inner exception stack
> >> trace --
> >> at
> >> Microsoft.ReportingServices.WebServer.ReportingService.SetReportDataSources(String
> >> Report, DataSource[] DataSources)
> >>
> >> Can anyone help me ?
> >>
> >> "May Liu" wrote:
> >>
> >> > In reporting services books online, I find some class such as
> >> > DataSetDefinition, DataSourceDefinition or QueryDefinition. Is there
> >> > any
> >> > other example that teach us to overwrite the reports original query ?
> >> > Since I have some reports which the template and parameter are same but
> >> > with
> >> > different select statment.
> >> >
> >> > "Brian Welcker [MSFT]" wrote:
> >> >
> >> > > Only by republishing the report. Or you can make the query based on
> >> > > an
> >> > > expression and pass the value in.
> >> > >
> >> > > --
> >> > > Brian Welcker
> >> > > Group Program Manager
> >> > > SQL Server Reporting Services
> >> > >
> >> > > This posting is provided "AS IS" with no warranties, and confers no
> >> > > rights.
> >> > >
> >> > > "May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
> >> > > news:F26B1DA5-B0E0-4536-913B-6325421FBBB2@.microsoft.com...
> >> > > > Can I call web service to overwrite the query ? If yes, how can
> >> > > > do it
> >> > > > ?
> >> > >
> >> > >
> >> > >
>
>