DataTime as parameters: Help Needed.

Hello all,
I need to generate a report based on dates(from and to). Is there
anyway I can include a dropdown of datetime in the parameters of the RS
interface.
for example like from mm/dd/yyyy to mm/dd/yyyy.
I have RS 2003 EE with SP1 and have no plans of installing SP2.
Any help will be appreciated.
Thanks a lot
RaviRavi,
You need to create a list of dates as a DS, and then add two params to the
report (well, just add them to the DS for the report itself) and configure
the Params to be query selections from the Dates DS.
Using Adventurewroks DB, the following suffices for selecting particular
records of SalesOrderID, ModifiedDate columns from the SalesOrderDetail
table, by date.
DataSets:
DSDates:
SELECT ModifiedDate, CONVERT(char(20), ModifiedDate, 101) AS dateselect
FROM dbo.SalesOrderDetail
GROUP BY ModifiedDate
ORDER BY ModifiedDate
DS1:
SELECT SalesOrderID, ModifiedDate
FROM dbo.SalesOrderDetail
WHERE (ModifiedDate > @.STARTDATE AND ModifiedDate < @.ENDDATE)
Form:
Table with DS1 as it's source.
Remember to edit the params in Report-Report Parameters, for both @.STARTDATE
AND @.ENDDATE, to be;
From Query; Dataset:DSDates;ValueField:ModifiedDate;LabelField:dateselect.
If you leave out the dateselect column from the first DS then the dropdown
will default to 00:00:00 time, and looks awful.
Hope this helps,
Tony
"Ravi R" wrote:
> Hello all,
>
> I need to generate a report based on dates(from and to). Is there
> anyway I can include a dropdown of datetime in the parameters of the RS
> interface.
> for example like from mm/dd/yyyy to mm/dd/yyyy.
> I have RS 2003 EE with SP1 and have no plans of installing SP2.
> Any help will be appreciated.
> Thanks a lot
> Ravi
>|||Ravi,
In addiditon to my last reply, if you want to make 'intelligent' parameters,
then your end date should be greater than your start date.
To achieve this, complete tasks as per my previous reply and add the
following;
Create a further DS called DSDates2.
SELECT ModifiedDate, CONVERT(char(20), ModifiedDate, 101) AS dateselect
FROM dbo.SalesOrderDetail
GROUP BY ModifiedDate
HAVING (ModifiedDate > @.STARTDATE)
ORDER BY ModifiedDate
and change the source for the @.ENDDATE parameter to point to DSDates2.
You will then find the End Date drop down is disabled until the Start Date
is selected.
If you wish, you can set a default for the Start Date as being the first
date found in the table (Use Top 1 selected from DSDates) for extra
useability.
Hope this assists further,
Tony
"Ravi R" wrote:
> Hello all,
>
> I need to generate a report based on dates(from and to). Is there
> anyway I can include a dropdown of datetime in the parameters of the RS
> interface.
> for example like from mm/dd/yyyy to mm/dd/yyyy.
> I have RS 2003 EE with SP1 and have no plans of installing SP2.
> Any help will be appreciated.
> Thanks a lot
> Ravi
>|||thanks logicalman,
will try that first thing on monday.
Ravi

Datasource: SSIS extension

Hi!
According to msdn http://msdn2.microsoft.com/en-us/library/ms228006
all I need to do to allow SSIS datasource in the report builder is to
uncomment an extension line with SSIS name in the rsreportserver.config. But
this not works on my VS2005 (i tried on both Beta2 and RC instances)... No
one new data provider type occured in the dropdown. I'm disappointed, what's
wrong? If anyone knows what to do to really add SSIS data to a report, send
me a string, Please!I have the same exact problem. Can anyone help?
"Sergei Vedischev" wrote:
> Hi!
> According to msdn http://msdn2.microsoft.com/en-us/library/ms228006
> all I need to do to allow SSIS datasource in the report builder is to
> uncomment an extension line with SSIS name in the rsreportserver.config. But
> this not works on my VS2005 (i tried on both Beta2 and RC instances)... No
> one new data provider type occured in the dropdown. I'm disappointed, what's
> wrong? If anyone knows what to do to really add SSIS data to a report, send
> me a string, Please!
>|||The SSIS data extension is only supported for report designer and report
server, not directly in report builder.
Did you actually install the SSIS components on that machine? Also, in order
to use the SSIS data extension in report designer, you have to uncomment the
SSIS configuration element in the RSReportDesigner.config file.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tom" <Tom@.discussions.microsoft.com> wrote in message
news:676FE940-5A2D-4F35-8CB3-82B5C02B916C@.microsoft.com...
>I have the same exact problem. Can anyone help?
>
> "Sergei Vedischev" wrote:
>> Hi!
>> According to msdn http://msdn2.microsoft.com/en-us/library/ms228006
>> all I need to do to allow SSIS datasource in the report builder is to
>> uncomment an extension line with SSIS name in the rsreportserver.config.
>> But
>> this not works on my VS2005 (i tried on both Beta2 and RC instances)...
>> No
>> one new data provider type occured in the dropdown. I'm disappointed,
>> what's
>> wrong? If anyone knows what to do to really add SSIS data to a report,
>> send
>> me a string, Please!

Datasource properties while publishing report

Hi,
I am having problem where I am uploading report and setting its datasource
properties using rs.setproperties. I had defined datasource but it says â'data
source in not published on report serverâ' or
â'System.Web.Services.Protocols.SoapException: The required field DataSource
is mi
ssing from the input structure. -->
Microsoft.ReportingServices.Diagnostics.Uti
lities.MissingElementException: The required field DataSource is missing
from th
e input structure.â'
. Any idea how to resolve this problem I am also attaching code for your
reference
Dim reference As New DataSourceReference()
reference.Reference = "/Report Project1/EBS"
Dim dataSources(1) As DataSource
Dim ds As New DataSource()
ds.Item = CType(reference, DataSourceDefinitionOrReference)
ds.Name = "EBS"
dataSources(0) = ds
Try
rs.SetReportDataSources(â'abc.rdlâ', dataSources)
Console.WriteLine("New reference set for the report.")
Catch e As SoapException
Console.WriteLine(e.Detail.InnerXml.ToString())
End Try
Thanks in Advance
JasvinderI know this might not totally make sense, but in the line where you declare
the dataSources variable, use the following syntax:
Dim dataSources(0) As DataSource
instead of:
Dim dataSources(1) As DataSource
and I bet it will work for you! Hope it does. -Aaron
"Jasvinder" wrote:
> Hi,
> I am having problem where I am uploading report and setting its datasource
> properties using rs.setproperties. I had defined datasource but it says â'data
> source in not published on report serverâ' or
> â'System.Web.Services.Protocols.SoapException: The required field DataSource
> is mi
> ssing from the input structure. -->
> Microsoft.ReportingServices.Diagnostics.Uti
> lities.MissingElementException: The required field DataSource is missing
> from th
> e input structure.â'
>
> . Any idea how to resolve this problem I am also attaching code for your
> reference
>
> Dim reference As New DataSourceReference()
> reference.Reference = "/Report Project1/EBS"
> Dim dataSources(1) As DataSource
> Dim ds As New DataSource()
> ds.Item = CType(reference, DataSourceDefinitionOrReference)
> ds.Name = "EBS"
> dataSources(0) = ds
>
> Try
> rs.SetReportDataSources(â'abc.rdlâ', dataSources)
> Console.WriteLine("New reference set for the report.")
> Catch e As SoapException
> Console.WriteLine(e.Detail.InnerXml.ToString())
> End Try
>
> Thanks in Advance
> Jasvinder
>|||Hi Aaron,
Yes i had figured out this problem and i am still not understanding how this
can work. Thought of posting messege to MS.
But anyhow thanks for the solution.
Another problem which is coming is "Unhandled
Exception:System.Security.SecurityPermission" I have added try catch block
everywhere but still in the process of finding this error. Moreover its
frequency of coming is not sure. Sometimes it does not comes and sometimes it
comes and vice versa.
Thanks
Jasvinder
"Aaron Williams" wrote:
> I know this might not totally make sense, but in the line where you declare
> the dataSources variable, use the following syntax:
> Dim dataSources(0) As DataSource
> instead of:
> Dim dataSources(1) As DataSource
> and I bet it will work for you! Hope it does. -Aaron
>
> "Jasvinder" wrote:
> > Hi,
> > I am having problem where I am uploading report and setting its datasource
> > properties using rs.setproperties. I had defined datasource but it says â'data
> > source in not published on report serverâ' or
> >
> > â'System.Web.Services.Protocols.SoapException: The required field DataSource
> > is mi
> >
> > ssing from the input structure. -->
> > Microsoft.ReportingServices.Diagnostics.Uti
> >
> > lities.MissingElementException: The required field DataSource is missing
> > from th
> >
> > e input structure.â'
> >
> >
> >
> > . Any idea how to resolve this problem I am also attaching code for your
> > reference
> >
> >
> >
> > Dim reference As New DataSourceReference()
> >
> > reference.Reference = "/Report Project1/EBS"
> >
> > Dim dataSources(1) As DataSource
> >
> > Dim ds As New DataSource()
> >
> > ds.Item = CType(reference, DataSourceDefinitionOrReference)
> >
> > ds.Name = "EBS"
> >
> > dataSources(0) = ds
> >
> >
> >
> > Try
> >
> > rs.SetReportDataSources(â'abc.rdlâ', dataSources)
> >
> > Console.WriteLine("New reference set for the report.")
> >
> > Catch e As SoapException
> >
> > Console.WriteLine(e.Detail.InnerXml.ToString())
> >
> > End Try
> >
> >
> >
> > Thanks in Advance
> > Jasvinder
> >sql

dataSource is different instance of same database(Urgent)

Hi,

I need help......

I am trying to create a report whose datasource will be different instances of the same database.So both the databases contains the same tables with same schema but the thing is that teh data is different.I have to collect the daat from different instances of the same table a(as they contains different different data) and using the collection to generate the report.

It's very very urgent ...plz reply ...

Thanx in advance

HI,

I dont thnk this is possible to do(as far as i know you cant connect a datasource to different database at runtime and also you cant change datasource at runtime). Now to solve your problem i can think of these solutions -

1. Create two datasources(connecting to two different database), and in your report create two datasets connecting to each one of the datasources. Similarly create two instances of tables (or whatever control you want to display) and connect controls to dataets. Now on runtime show/hide the table based on data from which table you want to show.

This is not a verygood choice(actually very poor) and you can achieve this using two copies of same report connecting to different databases and by showing the appropriate report to user.

2. Better approch is to have two tables in the same database like a_1 and a_2 and pass a parametre to your sp specifying which table to be used. In SP you will have to create dynamic sql in which table name will be created based on the parametre passed.

Hope i am able to convey whats there in my mind....

|||

Just came across this blog, it looks like we can dynamically change the connection string(did'nt tried though) -

http://blogs.msdn.com/bimusings/archive/2006/07/20/673051.aspx

Datasource information retrieved from a database

Hi everyone,
I have an assembly deployed on my report server that contains a number of
functions used by my reports. The assembly has full trust and all of the
functions work fine except one.
The purpose of this function is to query a database table for the correct
connection string for the report datasource. The code that returns the
connection string works correctly in a test windows app. When I run the
report through report manager, I get the following error:
Does this mean that a report server can only use the database where it
stores its server information? This would be inconvenient and I would like
to avoid it if possible.
Also, I would prefer my assembly not to have full trust, and instead only
have the permissions necessary to allow it to read from the database where
the connection string information is stored. So far, I have found plenty of
sample code describing the XML that I need to add to grant
FileIOPermissions, but nothing on database-related permissions. Can anyone
point me in the right direction?
Thanks in advance,
Ed AllisonFor anyone else who gets stuck on this, the solution is here:
http://blogs.sqlxml.org/bryantlikes/archive/2004/07/21/845.aspx
"Ed Allison" <ed@.optix.co.uk> wrote in message
news:eMGQtxzUGHA.5996@.TK2MSFTNGP10.phx.gbl...
> Hi everyone,
> I have an assembly deployed on my report server that contains a number of
> functions used by my reports. The assembly has full trust and all of the
> functions work fine except one.
> The purpose of this function is to query a database table for the correct
> connection string for the report datasource. The code that returns the
> connection string works correctly in a test windows app. When I run the
> report through report manager, I get the following error:
> Does this mean that a report server can only use the database where it
> stores its server information? This would be inconvenient and I would
> like to avoid it if possible.
> Also, I would prefer my assembly not to have full trust, and instead only
> have the permissions necessary to allow it to read from the database where
> the connection string information is stored. So far, I have found plenty
> of sample code describing the XML that I need to add to grant
> FileIOPermissions, but nothing on database-related permissions. Can
> anyone point me in the right direction?
> Thanks in advance,
> Ed Allison
>

Datasource error

Hi, i've a problem with calling a report created with reporting service; i get this error :System.Web.Services.Protocols.SoapException : Impossible find datasource...... ( i translate from italian ) I'm sure ip i wrote is right , and , moreover, any ip datasource i put ,i get the same error. Thank you in advance for your help.

Hi,

From your description, it seems that you are using WebService as your datasource of your report, right?

And the error "System.Web.Services.Protocols.SoapException" indicates that the WebService failed to return the result which your report expected.

So I suggest that you should try to check your WebService to see if it can work correctly, we suppose that it returns a dataset typed datasource, then you can just write a simple project, remove the reporting service part, try to see if you can retrieve the return properly.

Thanks.

|||Hi Nai-Dong,
I use the webservice as result, distribuiting report I've developed with SQL Server Development intelligence studio.
I'm doing this for the first time , I'm studying how to use SQLServer reporting service.
The webservice "seems" to me that works properly because I see exposed all the methods, and moreover ,
calling the other methods that don't involve datasource , the report is correctly called.
I'm using XP .
Thank you.|||

Hi,

The webservice "seems" to me that works properly because I see exposed all the methods, and moreover , calling the other methods that don't involve datasource , the report is correctly called.

Well, if so. I think you should try to check if the database is available from the machine which host the web service. Another problem is the account. You should try to check if the account which runs the webservice exist in the web service identity of the report server. Also, try to check if the account which runs the webservice (ASPNET or IUSR_MachineName) has to permission to access the reporting service database. If not, add the account into the users of that database.

Besides, I suggest you to refer the following link, it may be helpful to you.

http://msdn2.microsoft.com/en-us/library/aa179578(SQL.80).aspx

Thanks.

Datasource Error

I'm getting this message when I try to enter an SQL statement for a new
report:
"There is an error in the query. MinimumCapacity must be non-negative."
My SQL statement is:
"CALL GET_INSTALLMENTS_HEADER(?,?)"
I've tried using both IBM DB2 Provider and HiT Softwares OLEDB/400 provider
and get the same error.
Any ideas?
Thanks in advance,
MikeI am going against Sybase using OLEDB so although it is not the same
database as you are going against it is using an OLEDB provider. I do not
use the syntax you are using. I use the following syntax:
pr_test ?, ?
If you already have report parameters created then go to the parameters tab
of the dataset (click on the ...) and map the query parameter to the report
parameter. If in the generic query designer I have found I have to leave the
command type as text rather than setting it to stored procedure.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Mike" <MichaelLopez@.inds.com> wrote in message
news:O7%23%233%23mxEHA.3212@.TK2MSFTNGP09.phx.gbl...
> I'm getting this message when I try to enter an SQL statement for a new
> report:
> "There is an error in the query. MinimumCapacity must be
non-negative."
> My SQL statement is:
> "CALL GET_INSTALLMENTS_HEADER(?,?)"
> I've tried using both IBM DB2 Provider and HiT Softwares OLEDB/400
provider
> and get the same error.
> Any ideas?
> Thanks in advance,
> Mike
>|||Hi, Bruce. Thanks for responding.
That's how I did it, though I discovered it through empirical means.
We've used Crystal Reports for some time, and it's not a bad product at all.
But we always had to jump through hoops to gain access to an IBM AS/400
(iSeries) machine, and forget about accessing stored procedures on the 400.
We never could natively. Yesterday was my first attempt at accessing the
same system using RS and got it to work in about an hour.
That's a good sign. My next step is to figure out how to emulate CR's "Group
Tree" functionality.
Good chance this group will be hearing from me soon.
Thanks again,
Mike
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OGgUbenxEHA.824@.TK2MSFTNGP11.phx.gbl...
>I am going against Sybase using OLEDB so although it is not the same
> database as you are going against it is using an OLEDB provider. I do not
> use the syntax you are using. I use the following syntax:
> pr_test ?, ?
> If you already have report parameters created then go to the parameters
> tab
> of the dataset (click on the ...) and map the query parameter to the
> report
> parameter. If in the generic query designer I have found I have to leave
> the
> command type as text rather than setting it to stored procedure.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Mike" <MichaelLopez@.inds.com> wrote in message
> news:O7%23%233%23mxEHA.3212@.TK2MSFTNGP09.phx.gbl...
>> I'm getting this message when I try to enter an SQL statement for a new
>> report:
>> "There is an error in the query. MinimumCapacity must be
> non-negative."
>> My SQL statement is:
>> "CALL GET_INSTALLMENTS_HEADER(?,?)"
>> I've tried using both IBM DB2 Provider and HiT Softwares OLEDB/400
> provider
>> and get the same error.
>> Any ideas?
>> Thanks in advance,
>> Mike
>>
>sql

Datasource credentails not saved if inside a batch

I am publishing an RDL using webservice. After publishing the report, i
update the embeded datasource for the report using "SetReportDataSources"
method.
If i use a "BatchHeader", with "ExecuteBatch" method the report is not
working. It says cannot login. I have to go the report datasource and retype
the password for the report to work.
But if I donot use "BatchHeader", the same code works.
This issue happens only on one of the reprot servers in our network. The
same code works on other report servers in the network. Is there a settings
in the report server or the server itself that is causing this.
Any help appreciated.I think in RS 2005 it was changed to SetItemDataSources(), anyway here's the
code we use for updating the datasources for the reports during deployment.
We create the datasource once, then for each report we deploy the report
then call this to update the datasource reference. We only use a single
datasource for all reports, so not too complex of an example. Depending on
the environment and version we change the datasource name.
Sub DSChange(ByVal ReportName As String)
Dim reference As new DataSourceReference()
Dim ds As New DataSource()
Dim dataSources() As DataSource
Try
reference.Reference = "/" & ReportDir & "/" & RSDSName
DataSources = rs.GetItemDataSources("/" & ReportDir & "/" &
ReportName)
ds = DataSources(0)
ds.Item = CType(reference, DataSourceDefinitionOrReference)
rs.SetItemDataSources("/" & ReportDir & "/" & ReportName,
DataSources)
Console.WriteLine("datasource reference set")
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub
Steve MunLeeuw
"vRam" <vRam@.discussions.microsoft.com> wrote in message
news:D47581C0-F81B-4C34-A695-A1F95693A668@.microsoft.com...
>I am publishing an RDL using webservice. After publishing the report, i
> update the embeded datasource for the report using "SetReportDataSources"
> method.
> If i use a "BatchHeader", with "ExecuteBatch" method the report is not
> working. It says cannot login. I have to go the report datasource and
> retype
> the password for the report to work.
> But if I donot use "BatchHeader", the same code works.
> This issue happens only on one of the reprot servers in our network. The
> same code works on other report servers in the network. Is there a
> settings
> in the report server or the server itself that is causing this.
> Any help appreciated.
>|||thanks for the reply.
But our implementation is on RS2000.
Again our code is working on all the RS servers except one of them. that too
if we set the datasource within a transaction using BatchHeaders.
"Steve MunLeeuw" wrote:
> I think in RS 2005 it was changed to SetItemDataSources(), anyway here's the
> code we use for updating the datasources for the reports during deployment.
> We create the datasource once, then for each report we deploy the report
> then call this to update the datasource reference. We only use a single
> datasource for all reports, so not too complex of an example. Depending on
> the environment and version we change the datasource name.
>
> Sub DSChange(ByVal ReportName As String)
> Dim reference As new DataSourceReference()
> Dim ds As New DataSource()
> Dim dataSources() As DataSource
> Try
> reference.Reference = "/" & ReportDir & "/" & RSDSName
> DataSources = rs.GetItemDataSources("/" & ReportDir & "/" &
> ReportName)
> ds = DataSources(0)
> ds.Item = CType(reference, DataSourceDefinitionOrReference)
> rs.SetItemDataSources("/" & ReportDir & "/" & ReportName,
> DataSources)
> Console.WriteLine("datasource reference set")
> Catch e As Exception
> Console.WriteLine(e.Message)
> End Try
> End Sub
> Steve MunLeeuw
> "vRam" <vRam@.discussions.microsoft.com> wrote in message
> news:D47581C0-F81B-4C34-A695-A1F95693A668@.microsoft.com...
> >I am publishing an RDL using webservice. After publishing the report, i
> > update the embeded datasource for the report using "SetReportDataSources"
> > method.
> >
> > If i use a "BatchHeader", with "ExecuteBatch" method the report is not
> > working. It says cannot login. I have to go the report datasource and
> > retype
> > the password for the report to work.
> >
> > But if I donot use "BatchHeader", the same code works.
> >
> > This issue happens only on one of the reprot servers in our network. The
> > same code works on other report servers in the network. Is there a
> > settings
> > in the report server or the server itself that is causing this.
> >
> > Any help appreciated.
> >
> >
>
>

Datasource Connection string

Is there option available to dynamically change the connection string
properties of a published report?
--
Thanks
SaranYou could do that through the SOAP API by calling SetDataSourceContents:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_ref_soapapi_service_lz_2ojd.asp
Other approaches for dynamic database connections in RS 2000 have also been
discussed on this newsgroup. E.g.:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=b1a2f8b8-457c-4424-9cfd-2c8269734898&sloc=en-us
FYI: RS 2005 will allow expression-based connection strings.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"PTS" <PTS@.discussions.microsoft.com> wrote in message
news:765E3C18-186B-44AB-AA83-A5BA1EC1BEEE@.microsoft.com...
> Is there option available to dynamically change the connection string
> properties of a published report?
> --
> Thanks
> Saran

datasource connection error

have developed a report on a test windows server machine in SSRS 2005 which
I can access from i./e. http://localhost/reports
DataSourcce is accessed using a sql login
My colleague on his machine can not access. His error is:
An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'MarketAnalytics'.
(rsErrorOpeningConnection)
For more information about this error navigate to the report server on the
local server machine, or enable remote errorsCheck the SRS error logs they should have a better error for you. They can be
found in: C:\Program Files\Microsoft SQL Server\MSSQL.#\Reporting
Services\LogFiles.
--
SQL Server Developer Support Engineer
"arkiboys2" wrote:
> have developed a report on a test windows server machine in SSRS 2005 which
> I can access from i./e. http://localhost/reports
> DataSourcce is accessed using a sql login
> My colleague on his machine can not access. His error is:
> An error has occurred during report processing. (rsProcessingAborted)
> Cannot create a connection to data source 'MarketAnalytics'.
> (rsErrorOpeningConnection)
> For more information about this error navigate to the report server on the
> local server machine, or enable remote errors
>

Datasource Change HELP!

I am working in a dev environment. My report uses 20 different datasets but
they all point to the same datasource. I am moving from Dev to a UAT
environment which points to a different DB. Is there a way to change the
datasource for all 20 datasets from DevDB to UATDB at once or am I stuck with
going in and changing all the datasources for each of the datasets one by one?
Thanks in advance
OSTry opening XML <F7> and doing a find/replace on the DB name. This is
assuming that the new datasource is already defined in the solution and the
table names are the same.
"OriginalStealth" wrote:
> I am working in a dev environment. My report uses 20 different datasets but
> they all point to the same datasource. I am moving from Dev to a UAT
> environment which points to a different DB. Is there a way to change the
> datasource for all 20 datasets from DevDB to UATDB at once or am I stuck with
> going in and changing all the datasources for each of the datasets one by one?
> Thanks in advance
> OS|||Hi,
Have you used as a "shared resources" for all your reports? then it should
not be a problem since you need to just change the database and user
name/password etc.. details at your datasource and you will be up and
running.
Amarnath
"OriginalStealth" wrote:
> I am working in a dev environment. My report uses 20 different datasets but
> they all point to the same datasource. I am moving from Dev to a UAT
> environment which points to a different DB. Is there a way to change the
> datasource for all 20 datasets from DevDB to UATDB at once or am I stuck with
> going in and changing all the datasources for each of the datasets one by one?
> Thanks in advance
> OS|||William,
This really helped me! Thanx a bunch!
OS
"William" wrote:
> Try opening XML <F7> and doing a find/replace on the DB name. This is
> assuming that the new datasource is already defined in the solution and the
> table names are the same.
> "OriginalStealth" wrote:
> > I am working in a dev environment. My report uses 20 different datasets but
> > they all point to the same datasource. I am moving from Dev to a UAT
> > environment which points to a different DB. Is there a way to change the
> > datasource for all 20 datasets from DevDB to UATDB at once or am I stuck with
> > going in and changing all the datasources for each of the datasets one by one?
> >
> > Thanks in advance
> > OS|||How can we programatically change the DB name?
Or
Change them dynamically in the URL?
Prad
Amarnath wrote:
> Hi,
> Have you used as a "shared resources" for all your reports? then it should
> not be a problem since you need to just change the database and user
> name/password etc.. details at your datasource and you will be up and
> running.
> Amarnath
>
> "OriginalStealth" wrote:
> > I am working in a dev environment. My report uses 20 different datasets but
> > they all point to the same datasource. I am moving from Dev to a UAT
> > environment which points to a different DB. Is there a way to change the
> > datasource for all 20 datasets from DevDB to UATDB at once or am I stuck with
> > going in and changing all the datasources for each of the datasets one by one?
> >
> > Thanks in advance
> > OS|||What you should be doing is to have a shared datasource. The first time you
deploy the datasource is deployed too. Then you go to the Report Manager and
change the datasource to point to the production data. From then on when you
deploy the default is that the datasource is not overwritten so you only
need to do this once.
So there should only be one datasource that you have to change once in this
process.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<pradeepmm@.gmail.com> wrote in message
news:1144344167.425837.308400@.u72g2000cwu.googlegroups.com...
> How can we programatically change the DB name?
> Or
> Change them dynamically in the URL?
> Prad
> Amarnath wrote:
>> Hi,
>> Have you used as a "shared resources" for all your reports? then it
>> should
>> not be a problem since you need to just change the database and user
>> name/password etc.. details at your datasource and you will be up and
>> running.
>> Amarnath
>>
>> "OriginalStealth" wrote:
>> > I am working in a dev environment. My report uses 20 different
>> > datasets but
>> > they all point to the same datasource. I am moving from Dev to a UAT
>> > environment which points to a different DB. Is there a way to change
>> > the
>> > datasource for all 20 datasets from DevDB to UATDB at once or am I
>> > stuck with
>> > going in and changing all the datasources for each of the datasets one
>> > by one?
>> >
>> > Thanks in advance
>> > OS
>|||Is there any way to change the datasource for a report on an
execution-by-exection basis?
I have a situation where there are a large numbre of databases and
users need to be able to execute a single report against any of the
databases.
Is there a way to supply the database connection string to the report
when it is executed?sql

Datasource cannot be found after report redeployment

We have an application using the winforms report viewer, and it displays all our reports perfectly untill I need to redeploy a report.

As soon as a report is re-deployed a refresh of the report in the viewer shows the following error message:

An error has occured during report processing. The data souce 'mydatasource' cannot be found.

This error occurs irrespective of electing to re-deploy ot not re-deploy the datasource, and the only solution seems to be to close the report viewer down and restart it.

I can reproduce the same problem hosting the reports in a web browser as well, with a slightly different error message

An error has occurred during report processing. (rsProcessingAborted). The data source 'mydatasource' cannot be found. (rsDataSourceNotFound)

Under RS2000 any changes that were made to a report, would automatically be shown to the user if the report was refreshed, without having to close the browser/application down and restart it.

Can I configure RS2005 to prevent this error occuring ? I have read that RS2005 seems to work a lot more within the IIS session for the user and cache things it thinks are usefull, so can I turn this behaviour off, or make it run like RS 2000 did, which provided me with a stable reporting platform?

If I cannot do this from the RS end of the system, is there any advice on using the winforms control to get around this issue - i've only just started using the control so I am not familiar with all its aspects !

Thanks

Andy

Hi Andy, did you ever resolve this? (I'm experiencing the same problem)

Cheers,

George

Datasource cannot be found after report redeployment

We have an application using the winforms report viewer, and it displays all our reports perfectly untill I need to redeploy a report.

As soon as a report is re-deployed a refresh of the report in the viewer shows the following error message:

An error has occured during report processing. The data souce 'mydatasource' cannot be found.

This error occurs irrespective of electing to re-deploy ot not re-deploy the datasource, and the only solution seems to be to close the report viewer down and restart it.

I can reproduce the same problem hosting the reports in a web browser as well, with a slightly different error message

An error has occurred during report processing. (rsProcessingAborted). The data source 'mydatasource' cannot be found. (rsDataSourceNotFound)

Under RS2000 any changes that were made to a report, would automatically be shown to the user if the report was refreshed, without having to close the browser/application down and restart it.

Can I configure RS2005 to prevent this error occuring ? I have read that RS2005 seems to work a lot more within the IIS session for the user and cache things it thinks are usefull, so can I turn this behaviour off, or make it run like RS 2000 did, which provided me with a stable reporting platform?

If I cannot do this from the RS end of the system, is there any advice on using the winforms control to get around this issue - i've only just started using the control so I am not familiar with all its aspects !

Thanks

Andy

Hi Andy, did you ever resolve this? (I'm experiencing the same problem)

Cheers,

George

DataSource and more than one recordset

I've a report that use a stored procedure as datasource.
This s.p. return 2 recordset. How to access the second recordset in
reporting services? (like Recordset.NextRecordset in VB)
--
OSVALDO COLITTIRS does not support more than one recordset being returned.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Osvaldo Colitti" <OsvaldoColitti@.discussions.microsoft.com> wrote in
message news:14D1D808-F9CE-40ED-8493-CD8B77055E69@.microsoft.com...
> I've a report that use a stored procedure as datasource.
> This s.p. return 2 recordset. How to access the second recordset in
> reporting services? (like Recordset.NextRecordset in VB)
> --
> OSVALDO COLITTI
>|||Bruce is correct in that the native Data Processing Extensions do not
support stored procedures that return more than one recordset - what happens
is that the first recordset is all that your Reporting Services DataSet will
see.
However, you can certainly create a custom Data Processing Extension, and
could then gain access to the other recordsets returned by a stored
procedure in a report. Its not optimal and the best way forward is usually
to re-design the report logic if this is at all possible. Typically what you
need to do in your Data Processing Extension is archive off the recordsets
into a tables that you can then pull them down from seperately for other
Report DataSets in the Report.
Peter Blackburn
Windows Server Systems - SQL Server MVP
Hitchhiker's Guide to SQL Server Reporting Services
http://www.sqlreportingservices.net
"Osvaldo Colitti" <OsvaldoColitti@.discussions.microsoft.com> wrote in
message news:14D1D808-F9CE-40ED-8493-CD8B77055E69@.microsoft.com...
> I've a report that use a stored procedure as datasource.
> This s.p. return 2 recordset. How to access the second recordset in
> reporting services? (like Recordset.NextRecordset in VB)
> --
> OSVALDO COLITTI
>

Datasource = SQL 2000 sp2 (Cross-Database-Ownership Chaining?)

We have a report in RS 2005, data source uses Windows Integrated Security,
Initial Catalog is the database where the stored procedure is. The stored
procedure selects data from a different database on the server. This server
is running SQL Server 200 sp2 (so pre-Cross-Database-Ownership Chaining
option). The users can execute the stored procedure fine so it does not seem
to be a permissions issue to the data. The Report runs fine FROM the Report
Server. But, if I try to run the report from a different machine, I get:
An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'DataSourceNameIsHere'.
(rsErrorOpeningConnection)
For more information about this error navigate to the report server on the
local server machine, or enable remote errors
The report also runs fine if we don't use Windows Integrated Security. The
report runs fine if the datasource (both databases) are on SQL2000 sp3. Is
there anything that we can do to get this working on SQL2000 sp2? I'm not
sure that all systems on this server are supported on sp3 (3a or 4). It hosts
several databases from purchased products.I found this in the Reporting Services Log:
ERROR: Throwing
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
Cannot create a connection to data source 'DataSourceNameIsHere'., ;
Info:
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
Cannot create a connection to data source 'DataSourceNameIsHere'. -->
System.Data.SqlClient.SqlException: Login failed for user 'NT
AUTHORITY\ANONYMOUS LOGON'.
Michelle
"Michelle" wrote:
> We have a report in RS 2005, data source uses Windows Integrated Security,
> Initial Catalog is the database where the stored procedure is. The stored
> procedure selects data from a different database on the server. This server
> is running SQL Server 200 sp2 (so pre-Cross-Database-Ownership Chaining
> option). The users can execute the stored procedure fine so it does not seem
> to be a permissions issue to the data. The Report runs fine FROM the Report
> Server. But, if I try to run the report from a different machine, I get:
> An error has occurred during report processing. (rsProcessingAborted)
> Cannot create a connection to data source 'DataSourceNameIsHere'.
> (rsErrorOpeningConnection)
> For more information about this error navigate to the report server on the
> local server machine, or enable remote errors
> The report also runs fine if we don't use Windows Integrated Security. The
> report runs fine if the datasource (both databases) are on SQL2000 sp3. Is
> there anything that we can do to get this working on SQL2000 sp2? I'm not
> sure that all systems on this server are supported on sp3 (3a or 4). It hosts
> several databases from purchased products.sql

Datasource = SQL 2000 sp2 (Cross-Database-Ownership Chaining?)

We have a report in RS 2005, data source uses Windows Integrated

Security, Initial Catalog is the database where the stored procedure

is. The stored procedure selects data from a different database on the

server. This server is running SQL Server 200 sp2 (so

pre-Cross-Database-Ownership Chaining option). The users can execute

the stored procedure fine so it does not seem to be a permissions issue

to the data. The Report runs fine FROM the Report Server. But, if I try

to run the report from a different machine, I get:

An error has occurred during report processing. (rsProcessingAborted)

Cannot create a connection to data source 'DataSourceNameIsHere'. (rsErrorOpeningConnection)

For more information about this error navigate to the report server on the local server machine, or enable remote errors

The report also runs fine if we don't use Windows Integrated Security.

The report runs fine if the datasource (both databases) are on SQL2000

sp3. Is there anything that we can do to get this working on SQL2000

sp2? I'm not sure that all systems on this server are supported on sp3

(3a or 4). It hosts several databases from purchased products.I found this in the Reporting Services Log:

ERROR: Throwing

Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:

Cannot create a connection to data source 'DataSourceNameIsHere'., ;

Info: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'DataSourceNameIsHere'. > System.Data.SqlClient.SqlException: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

Michelle

Datasource = SQL 2000 sp2 (Cross-Database-Ownership Chaining?)

We have a report in RS 2005, data source uses Windows Integrated

Security, Initial Catalog is the database where the stored procedure

is. The stored procedure selects data from a different database on the

server. This server is running SQL Server 200 sp2 (so

pre-Cross-Database-Ownership Chaining option). The users can execute

the stored procedure fine so it does not seem to be a permissions issue

to the data. The Report runs fine FROM the Report Server. But, if I try

to run the report from a different machine, I get:

An error has occurred during report processing. (rsProcessingAborted)

Cannot create a connection to data source 'DataSourceNameIsHere'. (rsErrorOpeningConnection)

For more information about this error navigate to the report server on the local server machine, or enable remote errors

The report also runs fine if we don't use Windows Integrated Security.

The report runs fine if the datasource (both databases) are on SQL2000

sp3. Is there anything that we can do to get this working on SQL2000

sp2? I'm not sure that all systems on this server are supported on sp3

(3a or 4). It hosts several databases from purchased products.I found this in the Reporting Services Log:

ERROR: Throwing

Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:

Cannot create a connection to data source 'DataSourceNameIsHere'., ;

Info: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'DataSourceNameIsHere'. > System.Data.SqlClient.SqlException: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

Michelle

datasource "on-the-fly"

Hi,
Is it possible to change a datasource (or its connection string) in the
moment of report running?
Thanks.On SQL 2000, the only way is to create an SP which uses linked servers based
on the parameter you pass ie
create proc getdata @.source varchar(10)
as
if @.source = 'a'
select ... from mylinkedserver1.db.dbo.table
else
if @.source
With SQL 2005 you can use a dynamic data source based on a parameter ie. In
the connection string
="data source=" &Parameters!ServerName.Value & ";initial
catalog=AdventureWorks
Hope this helps
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Vladimir Evdokimov" <evesq@.uk2.net> wrote in message
news:OCvqf0kzFHA.2008@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Is it possible to change a datasource (or its connection string) in the
> moment of report running?
> Thanks.
>|||Hello
In SQL 2000 is it possible to supply the datasource (database name etc) as
part of the arguments supplied in the URL?
My application runs off multiple databases corresponding to different
customers and I need to switch the datasource based on the customer user
calling the report.
Any ideas on the best way to accomplish this task in SQL 2000?
Thank you|||Although you can do this with RS 2000 it will be easier in 2005.
In 2000 you have to use dynamic SQL. Use generic query designer (button to
switch to this is to the right of the ...)
declare @.SQL varchar(255)
select @.SQL = 'select table_name from ' + @.Database +
'.information_schema.tables order by table_name'
exec (@.SQL)
Note that @.Database should cause a report parameter called this to be
created. Anyway, this is the concept.
In 2005 you will be able to have dynamic datasources. From 2005 help:
Data Source Expressions
You can put an expression into a connection string to allow users to select
the data source at run time. For example, suppose a multinational firm has
data servers in several countries. With an expression-based connection
string, a user who is running a sales report can select a data source for a
particular country before running the report.
The following example illustrates the use of a data source expression in a
SQL Server connection string. The example assumes you have created a report
parameter named ServerName:
Copy Code
="data source=" &Parameters!ServerName.Value & ";initial
catalog=AdventureWorks
Data source expressions are processed at run time or when a report is
previewed. The expression must be written in Visual Basic. Use the following
guidelines when defining a data source expression:
>>>>>>>>
a.. Design the report using a static connection string. A static
connection string refers to a connection string that is not set through an
expression (for example, when you follow the steps for creating a
report-specific or shared data source, you are defining a static connection
string). Using a static connection string allows you to connect to the data
source in Report Designer so that you can get the query results you need to
create the report.
b.. When defining the data source connection, do not use a shared data
source. You cannot use a data source expression in a shared data source. You
must define a report-specific data source for the report.
c.. Specify credentials separately from the connection string. You can use
stored credentials, prompted credentials, or integrated security.
d.. Add a report parameter to specify a data source. For parameter values,
you can either provide a static list of available values (in this case, the
available values should be data sources you can use with the report) or
define a query that retrieves a list of data sources at run time.
e.. Be sure that the list of data sources share the same database schema.
All report design begins with schema information. If there is a mismatch
between the schema used to define the report and the actual schema used by
the report at run time, the report might not run.
f.. Before publishing the report, replace the static connection string
with an expression. Wait until you are finished designing the report before
you replace the static connection string with an expression. Once you use an
expression, you cannot execute the query in Report Designer. Furthermore,
the field list in the Datasets window and the Parameters list will not
update automatically.
>>>>>>>
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"GavinMc" <GavinMc@.discussions.microsoft.com> wrote in message
news:F7640227-35C6-44CB-89BD-ED283ED242A4@.microsoft.com...
> Hello
> In SQL 2000 is it possible to supply the datasource (database name etc) as
> part of the arguments supplied in the URL?
> My application runs off multiple databases corresponding to different
> customers and I need to switch the datasource based on the customer user
> calling the report.
> Any ideas on the best way to accomplish this task in SQL 2000?
> Thank you
>|||Thanks Bruce, unfortunately I'm stuck with SQL 2000 for the moment...
Just a further enquiry based on the above. To allow for the switching of
databases on the fly for my dataset based on an entered parameter, I created
the following sql:
USE master
declare @.SQL varchar(255), @.mydb varchar(255)
set @.mydb= (SELECT dbID FROM co WHERE (coID = @.co))
select @.SQL='USE ' + @.mydb + ' SELECT accid, accno, accname FROM acc'
exec (@.SQL)
This reads a user defined 'co' table in master that contains a reference to
my target databases, When running a report I then supply a 'coID' to retrieve
the reference to the correct database and this is then used as a variable in
report's underlying dataset to ensure that the correct database is used. I
think this is along the lines of what you suggested above.
My question is whether the select statement needs to be built up as a string
for execution, this will obviously require rewriting all my existing plain
sql statements and inserting concatenation operators around the parameters
etc.
Is there some way that I can declare a parameter for the database I want to
run the report off, and include this at the start of existing sql statements
in the 'use' statement without explicitly declaring a sql str variable?

Datasource

I have several hundred reports that I need to change the Datasource to point
to another server. Without going one report property at a time and changing
the datasource, is there a way to run an update statement on ReportServer
database to use the new target server for the said reports? If running an
update is not advisable then how do I make global change to the reports data
sources?
Thanks in advance for the assistance.
--
Lito DWhy don't you just change the connection string of the current Data Source
instead of creating a new one and changing all of the reports?
--
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
> Thread-Topic: Datasource
> thread-index: AcgQYLyNYww3Wxw0QxS8ZfpBNLID8A==> X-WBNR-Posting-Host: 207.46.19.168
> From: =?Utf-8?B?TElUTw==?= <anynomous@.msn.com>
> Subject: Datasource
> Date: Tue, 16 Oct 2007 18:55:04 -0700
> I have several hundred reports that I need to change the Datasource to
point
> to another server. Without going one report property at a time and
changing
> the datasource, is there a way to run an update statement on ReportServer
> database to use the new target server for the said reports? If running an
> update is not advisable then how do I make global change to the reports
data
> sources?
> Thanks in advance for the assistance.
> --
> Lito D
>|||The problem is that some reports must stay on the live server whereas the
rest should go to another server having previous day old data. I assume
there is a utility ( not too familiar with rs.exec) that I can issue via
command line to change the datasource from one to another then manually
change the few reports that must stay on the live server.
Lito D
"Chris Alton [MSFT]" wrote:
> Why don't you just change the connection string of the current Data Source
> instead of creating a new one and changing all of the reports?
> --
> Chris Alton, Microsoft Corp.
> SQL Server Developer Support Engineer
> This posting is provided "AS IS" with no warranties, and confers no rights.
> --
> > Thread-Topic: Datasource
> > thread-index: AcgQYLyNYww3Wxw0QxS8ZfpBNLID8A==> > X-WBNR-Posting-Host: 207.46.19.168
> > From: =?Utf-8?B?TElUTw==?= <anynomous@.msn.com>
> > Subject: Datasource
> > Date: Tue, 16 Oct 2007 18:55:04 -0700
> >
> > I have several hundred reports that I need to change the Datasource to
> point
> > to another server. Without going one report property at a time and
> changing
> > the datasource, is there a way to run an update statement on ReportServer
> > database to use the new target server for the said reports? If running an
> > update is not advisable then how do I make global change to the reports
> data
> > sources?
> >
> > Thanks in advance for the assistance.
> >
> > --
> > Lito D
> >
>|||If you are using a Shared DataSource for your reports then you are going to
have to create a new Data Source anyway. So if all you have are a few
reports to change that stay on the live server then just change the
existing Shared Data Source to point to the new server and then create a
second Data Source that points to the old server. Then manually change the
few reports to the new Shared Data Source. If you are using embedded Data
Sources in your reports then you've got a tedious job ahead of you. The
following tool might help you out but its a 3rd party tool and I haven't
really used it extensively.
http://www.sqldbatips.com/showarticle.asp?ID=62
--
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
> Thread-Topic: Datasource
> thread-index: AcgRFlfiLyP20tutQlCHtUWElaYfEw==> X-WBNR-Posting-Host: 207.46.19.197
> From: =?Utf-8?B?TElUTw==?= <anynomous@.msn.com>
> References: <A60B2818-F70D-4150-BBA9-41BEC7FCDC93@.microsoft.com>
<4c7dqiMEIHA.360@.TK2MSFTNGHUB02.phx.gbl>
> Subject: RE: Datasource
> Date: Wed, 17 Oct 2007 16:35:04 -0700
> The problem is that some reports must stay on the live server whereas the
> rest should go to another server having previous day old data. I assume
> there is a utility ( not too familiar with rs.exec) that I can issue via
> command line to change the datasource from one to another then manually
> change the few reports that must stay on the live server.
>
> --
> Lito D
>
> "Chris Alton [MSFT]" wrote:
> > Why don't you just change the connection string of the current Data
Source
> > instead of creating a new one and changing all of the reports?
> >
> > --
> > Chris Alton, Microsoft Corp.
> > SQL Server Developer Support Engineer
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> > --
> > > Thread-Topic: Datasource
> > > thread-index: AcgQYLyNYww3Wxw0QxS8ZfpBNLID8A==> > > X-WBNR-Posting-Host: 207.46.19.168
> > > From: =?Utf-8?B?TElUTw==?= <anynomous@.msn.com>
> > > Subject: Datasource
> > > Date: Tue, 16 Oct 2007 18:55:04 -0700
> > >
> > > I have several hundred reports that I need to change the Datasource
to
> > point
> > > to another server. Without going one report property at a time and
> > changing
> > > the datasource, is there a way to run an update statement on
ReportServer
> > > database to use the new target server for the said reports? If
running an
> > > update is not advisable then how do I make global change to the
reports
> > data
> > > sources?
> > >
> > > Thanks in advance for the assistance.
> > >
> > > --
> > > Lito D
> > >
> >
> >
>sql

Datasets with multiple tables..

hey guys, quick question that i hope someone can help steer me in the correct direction.

i've got a failry large report that gets its data from and XML web service.. the dataset that the webservice returns has 6 tables in it (i'm interested in data from 5 of them).. in order to get my fields to show up correctly in my dataset viewer i've created 5 datasets and use the ElementPath in my query to single out the table i want for each dataset.. this works fine about 50% of the time.. the other 50% of the time it craps out on my because some of the data manipulation that the webservice does causes primary key violations.. is the report making 5 calls to the webservice? if it is, is it sending them in multiple threads.. its the only thing i can think of that would cause my key violations..

my sproc that is dying looks similar to this:

delete from dbo.Charges where contractid = @.contract

insert into dbo.charges (columns.....) values (values)

i can see that it would violate the key if both threads were doing the insert simultaneously(sp)..

now.. is there a way to only make one trip to the webservice and use those results in the report, while still having the fields available in the dataset viewer?

thanks

mike

Yes, RS will execute each DataSet in its own thread if the DataSource is not marked to use a transaction. If your RS DataSource is set to execute in a transaction each DataSet will be executed in order in the same thread. In report designer, you can mark a DataSource to use a transaction by checking the "Use single transaction" box in the Data Source dialog.

However, even if all your DataSets are executed in the same thread, each DataSet will still make a seperate call to the web service to retrieve the data.

John

|||

thanks John.. it works as expected now.. is there anyway that i could prevent all the calls to the webservice? ie have a main dataset with all the tables, then pass that dataset to a subreport for each section that uses a specific table?

thanks again for the quick reply

mike