Changing the DataSource at Runtime

I have to create a script for changing the datasource at runtime.

Here is my screnario, While development I am using Data source name called "DevDatasource1" and when I am deploying it to other evnironment the datasource name will change let us say "QADatasource".

I have to create a script for changing the datasource(i.e. DevDatasource1 to QADatasource). How I can achieve I this using the setItemdatasource?

You can also set a parameter in your report to accept the specific database or source and use this parameter within your datasource connection string.

Within your report:

My connection string looks like this: ="Data Source=nt33;Initial Catalog=" & Parameters!Database.Value & ";"

This allows me to change the database on the fly, but could change the entire connection string including server.

Location for the code would be Data Tab --> Dataset properties --> Datasource editor --> Connection String:

The bonus is that no matter where the report is run you have the ability to change the datasource.

My parameter is setup to load al list of available databases for the user.

Not what you were looking for specifcally, but it might help.

|||

hi simles

thanks for your reply.

Here is my problem. While deploying my reports on the other server we don't want to create the datasource. Already in the deployment server datasources are created.

what we have to do his while deploying the reports we have to map the existing datasources to the deploying rdl files. Can you explain me how we can achieve this using reports scripts.

|||

So, you have a pre-existing datasource on the server, and you want your newly-published report to use IT instead of the datasource the report was created with, right?

If so, try SetItemDataSources():

http://msdn2.microsoft.com/ru-ru/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.setitemdatasources.aspx

|||

Russell,

Thanks for your reply.

I tried with that setitemdatasources, but it not setting the new datasource name for the rdl.

I don't know were i am making the mistake. please look into the code and correct me if the code is wrong.

Dim dataSources() As DataSource

dataSources = rs.GetItemDataSources("/Reports/Test Matrix")

Dim NewdataSources As new DataSource

Dim dsref As New DataSourceReference

dsref.Reference = "/Data Sources/TestDatasource"

NewdataSources.Item = CType(dsref,DataSourceDefinitionOrReference)

NewdataSources.Name = "NewDatasource"

dataSources(0) = NewdataSources

rs.SetItemDataSources("/Reports/Test Matrix", dataSources)

Simran Raj

Changing the DataSource at Runtime

I have to create a script for changing the datasource at runtime.

Here is my screnario, While development I am using Data source name called "DevDatasource1" and when I am deploying it to other evnironment the datasource name will change let us say "QADatasource".

I have to create a script for changing the datasource(i.e. DevDatasource1 to QADatasource). How I can achieve I this using the setItemdatasource?

You can also set a parameter in your report to accept the specific database or source and use this parameter within your datasource connection string.

Within your report:

My connection string looks like this: ="Data Source=nt33;Initial Catalog=" & Parameters!Database.Value & ";"

This allows me to change the database on the fly, but could change the entire connection string including server.

Location for the code would be Data Tab --> Dataset properties --> Datasource editor --> Connection String:

The bonus is that no matter where the report is run you have the ability to change the datasource.

My parameter is setup to load al list of available databases for the user.

Not what you were looking for specifcally, but it might help.

|||

hi simles

thanks for your reply.

Here is my problem. While deploying my reports on the other server we don't want to create the datasource. Already in the deployment server datasources are created.

what we have to do his while deploying the reports we have to map the existing datasources to the deploying rdl files. Can you explain me how we can achieve this using reports scripts.

|||

So, you have a pre-existing datasource on the server, and you want your newly-published report to use IT instead of the datasource the report was created with, right?

If so, try SetItemDataSources():

http://msdn2.microsoft.com/ru-ru/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.setitemdatasources.aspx

|||

Russell,

Thanks for your reply.

I tried with that setitemdatasources, but it not setting the new datasource name for the rdl.

I don't know were i am making the mistake. please look into the code and correct me if the code is wrong.

Dim dataSources() As DataSource

dataSources = rs.GetItemDataSources("/Reports/Test Matrix")

Dim NewdataSources As new DataSource

Dim dsref As New DataSourceReference

dsref.Reference = "/Data Sources/TestDatasource"

NewdataSources.Item = CType(dsref,DataSourceDefinitionOrReference)

NewdataSources.Name = "NewDatasource"

dataSources(0) = NewdataSources

rs.SetItemDataSources("/Reports/Test Matrix", dataSources)

Simran Raj

sql

Changing the DataSource

After changing the data soource for a working report, the Fields become
"unlinked"; what is the correct way to change a data source or relink fields
after the change'There is a refresh button to the right of the ... in the dataset tab. That
should do it for you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Espo" <Espo@.discussions.microsoft.com> wrote in message
news:FE7D0C8B-9D7F-430E-9A37-10D4A73FBFA3@.microsoft.com...
> After changing the data soource for a working report, the Fields become
> "unlinked"; what is the correct way to change a data source or relink
> fields
> after the change'
>

Changing SSRS reports datasource.

I have a web site that has reports stored in two places.The local site, and in SSRS.The local reports get there data from a class that has a dataset method on it.I would like to store all my reports in SSRS.Is this possible?Can I get just the RDL of a report from the SSRS web service, or change the dataset to what my class generates?

You can adopt one to two approaches:

1) store the RDLC you'll use in a report viewer control on the report server as a resouce.

2) store actual RDL in the report server and use the GetReportDefinition method to obtain the RDL.

What is interesting here is that the data source definitions between the RDLC the control expect and the RDL that the server expects is a little different.

Here's the reference for GetReportDefinition:

http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.getreportdefinition.aspx

-Lukasz

Changing SQL-command with a script

Hello

I want to change the sql-command that is behind a ole db datasource with a script
So that first the script is run and that the sql-command is changed

Somebody who has any idea?

You cannot access object or change their properties from Script in SSIS, a somewhat dramatic change from DTS.

First choice would be to use an Property Expression on the SqlCommand property, but unfortunately this property has not been exposed as an expression.

Next choice would be to use the "SQL Command from variable". You can use a script to set the variable, and the source can be set to use that variable, so job done. I woudl actually be tempted to set the SQL through an expression on the variable. Highlight teh variable in the Variables pane and select the properties grid. You can then set EvaluateAsExpression, followed by the Expression itself.|||Thats indeed a dramatic change

Can you do it then in Microsoft Visual Studio for Application
Thx|||

g4rc wrote:

Thats indeed a dramatic change

Can you do it then in Microsoft Visual Studio for Application
Thx

In the context of SSIS, a script task and VSA are the same thing. So the answer is "No".

You should use the approach outlined by Darren in the third paragraph of his earlier post. Its very easy - far easier than ActiveX scripting.

-Jamie|||can you give me a link to the post
I cannot find it|||

g4rc wrote:

can you give me a link to the post
I cannot find it

Its at the top of this page

i.e. The first reply on this discussion thread.

-Jamie

Changing Report's Datasource

I have one report that I want to have the ability to use different stored
procedures...depending on the the user's criteria they want to see. Is there
an RS method that I can use to change the stored procedure that a report is
using at runtime, or is there a better way to do what I want to do?ALthough I have never tried this,,, you could create a single SP which calls
the other SPs based on the user... OR
in the data set you can put the T-SQL IF statement to choose the SP..
I suspect that the SPs need to return the same result sets however...
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Mike Collins" wrote:
> I have one report that I want to have the ability to use different stored
> procedures...depending on the the user's criteria they want to see. Is there
> an RS method that I can use to change the stored procedure that a report is
> using at runtime, or is there a better way to do what I want to do?

Changing programmatically the datasource of a report

I am having the following problem:
I have a shared data source and a report on the server.
I want to change the data source of the report programmatically but I am
receiving the following error:
â'The required field DataSource is missing from the input structureâ'
I am using the following code:
Dim reference As New ReportServer.DataSourceReference
reference.Reference = "/MyDS"
Dim dss As New ReportServer.DataSource
dss.Item = CType(reference,
ReportServer.DataSourceDefinitionOrReference)
dss.Name = "MyDS"
Dim dsList() As ReportServer.DataSource = New
ReportServer.DataSource(1){}
dsList(1) = dss
dsList(1).Name = "MyDS"
rs.SetReportDataSources("/Test report1", dsList)
The shared datasource and the report are on the root directory and the
report was created with a shared datasource with the name â'MyDSâ'
What could be the problem?where is a value for dsList(0) ?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"CalinV" <CalinV@.discussions.microsoft.com> wrote in message
news:631AD51F-0AD1-4B7A-8C3A-47CE0264A5D5@.microsoft.com...
>I am having the following problem:
> I have a shared data source and a report on the server.
> I want to change the data source of the report programmatically but I am
> receiving the following error:
> "The required field DataSource is missing from the input structure"
> I am using the following code:
> Dim reference As New ReportServer.DataSourceReference
> reference.Reference = "/MyDS"
> Dim dss As New ReportServer.DataSource
> dss.Item = CType(reference,
> ReportServer.DataSourceDefinitionOrReference)
> dss.Name = "MyDS"
> Dim dsList() As ReportServer.DataSource = New
> ReportServer.DataSource(1){}
> dsList(1) = dss
> dsList(1).Name = "MyDS"
> rs.SetReportDataSources("/Test report1", dsList)
> The shared datasource and the report are on the root directory and the
> report was created with a shared datasource with the name "MyDS"
> What could be the problem?
>|||Hi, Dear All,
I have the same problem when I tried to use setDataSource method.
Here is my code:
Dim reference As New DataSourceReference
reference.Reference = "/xxxx/" + DBName
Dim dataSources(1) As DataSource
Dim ds As New DataSource
ds.Item = CType(reference, DataSourceDefinitionOrReference)
ds.Name = DBName
dataSources(0) = ds
' Render arguments
Dim reportPath As String = "/xxx/yyy" +
Request.Params("NumberOfT").ToString()
Dim format As String = "PDF"
Dim historyID As String = Nothing
Dim devInfo As String = "<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>"
' Prepare report parameter.
Dim parameters(1) As ParameterValue
parameters(0) = New ParameterValue
parameters(0).Name = "ReportID"
parameters(0).Value = Request.Params("ReportID").ToString()
parameters(1) = New ParameterValue
parameters(1).Name = "xxx"
parameters(1).Value = Request.Params("xxx").ToString()
Dim rs As New ReportingService
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
rs.SessionHeaderValue = New SessionHeader
rs.SetReportDataSources(reportPath, dataSources) (error happend here)
Actually, I copied it from MSDN sample. I did not see how the DataSources(1)
could be set. Do I need to set DataSources(1)?
Thanks,
Henry
"Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
news:%23f6R1FwkEHA.1652@.TK2MSFTNGP09.phx.gbl...
> where is a value for dsList(0) ?
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "CalinV" <CalinV@.discussions.microsoft.com> wrote in message
> news:631AD51F-0AD1-4B7A-8C3A-47CE0264A5D5@.microsoft.com...
>>I am having the following problem:
>> I have a shared data source and a report on the server.
>> I want to change the data source of the report programmatically but I am
>> receiving the following error:
>> "The required field DataSource is missing from the input structure"
>> I am using the following code:
>> Dim reference As New ReportServer.DataSourceReference
>> reference.Reference = "/MyDS"
>> Dim dss As New ReportServer.DataSource
>> dss.Item = CType(reference,
>> ReportServer.DataSourceDefinitionOrReference)
>> dss.Name = "MyDS"
>> Dim dsList() As ReportServer.DataSource = New
>> ReportServer.DataSource(1){}
>> dsList(1) = dss
>> dsList(1).Name = "MyDS"
>> rs.SetReportDataSources("/Test report1", dsList)
>> The shared datasource and the report are on the root directory and the
>> report was created with a shared datasource with the name "MyDS"
>> What could be the problem?
>>
>|||I think it should be
Dim dataSources(0) As DataSource
instead of
Dim dataSources(1) As DataSource
Please let me know if this works for you.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Henry Wang" <wangyh@.gmail.com> wrote in message
news:OhB3Wf3uEHA.3200@.TK2MSFTNGP14.phx.gbl...
> Hi, Dear All,
> I have the same problem when I tried to use setDataSource method.
> Here is my code:
> Dim reference As New DataSourceReference
> reference.Reference = "/xxxx/" + DBName
> Dim dataSources(1) As DataSource
> Dim ds As New DataSource
> ds.Item = CType(reference, DataSourceDefinitionOrReference)
> ds.Name = DBName
> dataSources(0) = ds
> ' Render arguments
> Dim reportPath As String = "/xxx/yyy" +
> Request.Params("NumberOfT").ToString()
> Dim format As String = "PDF"
> Dim historyID As String = Nothing
> Dim devInfo As String => "<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>"
> ' Prepare report parameter.
> Dim parameters(1) As ParameterValue
> parameters(0) = New ParameterValue
> parameters(0).Name = "ReportID"
> parameters(0).Value = Request.Params("ReportID").ToString()
> parameters(1) = New ParameterValue
> parameters(1).Name = "xxx"
> parameters(1).Value = Request.Params("xxx").ToString()
> Dim rs As New ReportingService
> rs.Credentials = System.Net.CredentialCache.DefaultCredentials
> rs.SessionHeaderValue = New SessionHeader
> rs.SetReportDataSources(reportPath, dataSources) (error happend here)
> Actually, I copied it from MSDN sample. I did not see how the
> DataSources(1) could be set. Do I need to set DataSources(1)?
>
> Thanks,
>
> Henry
> "Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
> news:%23f6R1FwkEHA.1652@.TK2MSFTNGP09.phx.gbl...
>> where is a value for dsList(0) ?
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "CalinV" <CalinV@.discussions.microsoft.com> wrote in message
>> news:631AD51F-0AD1-4B7A-8C3A-47CE0264A5D5@.microsoft.com...
>>I am having the following problem:
>> I have a shared data source and a report on the server.
>> I want to change the data source of the report programmatically but I am
>> receiving the following error:
>> "The required field DataSource is missing from the input structure"
>> I am using the following code:
>> Dim reference As New ReportServer.DataSourceReference
>> reference.Reference = "/MyDS"
>> Dim dss As New ReportServer.DataSource
>> dss.Item = CType(reference,
>> ReportServer.DataSourceDefinitionOrReference)
>> dss.Name = "MyDS"
>> Dim dsList() As ReportServer.DataSource = New
>> ReportServer.DataSource(1){}
>> dsList(1) = dss
>> dsList(1).Name = "MyDS"
>> rs.SetReportDataSources("/Test report1", dsList)
>> The shared datasource and the report are on the root directory and the
>> report was created with a shared datasource with the name "MyDS"
>> What could be the problem?
>>
>>
>|||On kinda same vien, I generate a RDL with a shared datasource. Once
published, it says the datasource is no longer valid. Any advice?
"Lev Semenets [MSFT]" wrote:
> I think it should be
> Dim dataSources(0) As DataSource
> instead of
> Dim dataSources(1) As DataSource
> Please let me know if this works for you.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Henry Wang" <wangyh@.gmail.com> wrote in message
> news:OhB3Wf3uEHA.3200@.TK2MSFTNGP14.phx.gbl...
> > Hi, Dear All,
> >
> > I have the same problem when I tried to use setDataSource method.
> >
> > Here is my code:
> > Dim reference As New DataSourceReference
> >
> > reference.Reference = "/xxxx/" + DBName
> >
> > Dim dataSources(1) As DataSource
> >
> > Dim ds As New DataSource
> >
> > ds.Item = CType(reference, DataSourceDefinitionOrReference)
> >
> > ds.Name = DBName
> >
> > dataSources(0) = ds
> >
> > ' Render arguments
> >
> > Dim reportPath As String = "/xxx/yyy" +
> > Request.Params("NumberOfT").ToString()
> >
> > Dim format As String = "PDF"
> >
> > Dim historyID As String = Nothing
> >
> > Dim devInfo As String => > "<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>"
> >
> > ' Prepare report parameter.
> >
> > Dim parameters(1) As ParameterValue
> >
> > parameters(0) = New ParameterValue
> >
> > parameters(0).Name = "ReportID"
> >
> > parameters(0).Value = Request.Params("ReportID").ToString()
> >
> > parameters(1) = New ParameterValue
> >
> > parameters(1).Name = "xxx"
> >
> > parameters(1).Value = Request.Params("xxx").ToString()
> >
> > Dim rs As New ReportingService
> >
> > rs.Credentials = System.Net.CredentialCache.DefaultCredentials
> >
> > rs.SessionHeaderValue = New SessionHeader
> >
> > rs.SetReportDataSources(reportPath, dataSources) (error happend here)
> >
> > Actually, I copied it from MSDN sample. I did not see how the
> > DataSources(1) could be set. Do I need to set DataSources(1)?
> >
> >
> >
> > Thanks,
> >
> >
> >
> > Henry
> >
> > "Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
> > news:%23f6R1FwkEHA.1652@.TK2MSFTNGP09.phx.gbl...
> >> where is a value for dsList(0) ?
> >>
> >> --
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "CalinV" <CalinV@.discussions.microsoft.com> wrote in message
> >> news:631AD51F-0AD1-4B7A-8C3A-47CE0264A5D5@.microsoft.com...
> >>I am having the following problem:
> >> I have a shared data source and a report on the server.
> >> I want to change the data source of the report programmatically but I am
> >> receiving the following error:
> >> "The required field DataSource is missing from the input structure"
> >> I am using the following code:
> >> Dim reference As New ReportServer.DataSourceReference
> >> reference.Reference = "/MyDS"
> >>
> >> Dim dss As New ReportServer.DataSource
> >>
> >> dss.Item = CType(reference,
> >> ReportServer.DataSourceDefinitionOrReference)
> >> dss.Name = "MyDS"
> >> Dim dsList() As ReportServer.DataSource = New
> >> ReportServer.DataSource(1){}
> >> dsList(1) = dss
> >> dsList(1).Name = "MyDS"
> >> rs.SetReportDataSources("/Test report1", dsList)
> >>
> >> The shared datasource and the report are on the root directory and the
> >> report was created with a shared datasource with the name "MyDS"
> >> What could be the problem?
> >>
> >>
> >>
> >>
> >
> >
>
>|||Could you email me the code?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Eddie" <Eddie@.discussions.microsoft.com> wrote in message
news:AC8BA5F5-2106-4203-9701-FE2AAC528F5F@.microsoft.com...
> On kinda same vien, I generate a RDL with a shared datasource. Once
> published, it says the datasource is no longer valid. Any advice?
> "Lev Semenets [MSFT]" wrote:
>> I think it should be
>> Dim dataSources(0) As DataSource
>> instead of
>> Dim dataSources(1) As DataSource
>> Please let me know if this works for you.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Henry Wang" <wangyh@.gmail.com> wrote in message
>> news:OhB3Wf3uEHA.3200@.TK2MSFTNGP14.phx.gbl...
>> > Hi, Dear All,
>> >
>> > I have the same problem when I tried to use setDataSource method.
>> >
>> > Here is my code:
>> > Dim reference As New DataSourceReference
>> >
>> > reference.Reference = "/xxxx/" + DBName
>> >
>> > Dim dataSources(1) As DataSource
>> >
>> > Dim ds As New DataSource
>> >
>> > ds.Item = CType(reference, DataSourceDefinitionOrReference)
>> >
>> > ds.Name = DBName
>> >
>> > dataSources(0) = ds
>> >
>> > ' Render arguments
>> >
>> > Dim reportPath As String = "/xxx/yyy" +
>> > Request.Params("NumberOfT").ToString()
>> >
>> > Dim format As String = "PDF"
>> >
>> > Dim historyID As String = Nothing
>> >
>> > Dim devInfo As String =>> > "<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>"
>> >
>> > ' Prepare report parameter.
>> >
>> > Dim parameters(1) As ParameterValue
>> >
>> > parameters(0) = New ParameterValue
>> >
>> > parameters(0).Name = "ReportID"
>> >
>> > parameters(0).Value = Request.Params("ReportID").ToString()
>> >
>> > parameters(1) = New ParameterValue
>> >
>> > parameters(1).Name = "xxx"
>> >
>> > parameters(1).Value = Request.Params("xxx").ToString()
>> >
>> > Dim rs As New ReportingService
>> >
>> > rs.Credentials = System.Net.CredentialCache.DefaultCredentials
>> >
>> > rs.SessionHeaderValue = New SessionHeader
>> >
>> > rs.SetReportDataSources(reportPath, dataSources) (error happend here)
>> >
>> > Actually, I copied it from MSDN sample. I did not see how the
>> > DataSources(1) could be set. Do I need to set DataSources(1)?
>> >
>> >
>> >
>> > Thanks,
>> >
>> >
>> >
>> > Henry
>> >
>> > "Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
>> > news:%23f6R1FwkEHA.1652@.TK2MSFTNGP09.phx.gbl...
>> >> where is a value for dsList(0) ?
>> >>
>> >> --
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> rights.
>> >>
>> >>
>> >> "CalinV" <CalinV@.discussions.microsoft.com> wrote in message
>> >> news:631AD51F-0AD1-4B7A-8C3A-47CE0264A5D5@.microsoft.com...
>> >>I am having the following problem:
>> >> I have a shared data source and a report on the server.
>> >> I want to change the data source of the report programmatically but I
>> >> am
>> >> receiving the following error:
>> >> "The required field DataSource is missing from the input structure"
>> >> I am using the following code:
>> >> Dim reference As New ReportServer.DataSourceReference
>> >> reference.Reference = "/MyDS"
>> >>
>> >> Dim dss As New ReportServer.DataSource
>> >>
>> >> dss.Item = CType(reference,
>> >> ReportServer.DataSourceDefinitionOrReference)
>> >> dss.Name = "MyDS"
>> >> Dim dsList() As ReportServer.DataSource = New
>> >> ReportServer.DataSource(1){}
>> >> dsList(1) = dss
>> >> dsList(1).Name = "MyDS"
>> >> rs.SetReportDataSources("/Test report1", dsList)
>> >>
>> >> The shared datasource and the report are on the root directory and
>> >> the
>> >> report was created with a shared datasource with the name "MyDS"
>> >> What could be the problem?
>> >>
>> >>
>> >>
>> >>
>> >
>> >
>>|||I got it, phew.
I tried a couple of things, to make sure it wasn't a bug. First I used a
known working RDL template, which uses a shared datasource. I deleted a node
and put it back in and and published it. I went ahead and set the datasource
to the newly published report. That seems to work, but I still wonder why it
doesn't work as seamless as when publishing from the Designer.
"Lev Semenets [MSFT]" wrote:
> Could you email me the code?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Eddie" <Eddie@.discussions.microsoft.com> wrote in message
> news:AC8BA5F5-2106-4203-9701-FE2AAC528F5F@.microsoft.com...
> > On kinda same vien, I generate a RDL with a shared datasource. Once
> > published, it says the datasource is no longer valid. Any advice?
> >
> > "Lev Semenets [MSFT]" wrote:
> >
> >> I think it should be
> >>
> >> Dim dataSources(0) As DataSource
> >> instead of
> >> Dim dataSources(1) As DataSource
> >>
> >> Please let me know if this works for you.
> >>
> >> --
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "Henry Wang" <wangyh@.gmail.com> wrote in message
> >> news:OhB3Wf3uEHA.3200@.TK2MSFTNGP14.phx.gbl...
> >> > Hi, Dear All,
> >> >
> >> > I have the same problem when I tried to use setDataSource method.
> >> >
> >> > Here is my code:
> >> > Dim reference As New DataSourceReference
> >> >
> >> > reference.Reference = "/xxxx/" + DBName
> >> >
> >> > Dim dataSources(1) As DataSource
> >> >
> >> > Dim ds As New DataSource
> >> >
> >> > ds.Item = CType(reference, DataSourceDefinitionOrReference)
> >> >
> >> > ds.Name = DBName
> >> >
> >> > dataSources(0) = ds
> >> >
> >> > ' Render arguments
> >> >
> >> > Dim reportPath As String = "/xxx/yyy" +
> >> > Request.Params("NumberOfT").ToString()
> >> >
> >> > Dim format As String = "PDF"
> >> >
> >> > Dim historyID As String = Nothing
> >> >
> >> > Dim devInfo As String => >> > "<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>"
> >> >
> >> > ' Prepare report parameter.
> >> >
> >> > Dim parameters(1) As ParameterValue
> >> >
> >> > parameters(0) = New ParameterValue
> >> >
> >> > parameters(0).Name = "ReportID"
> >> >
> >> > parameters(0).Value = Request.Params("ReportID").ToString()
> >> >
> >> > parameters(1) = New ParameterValue
> >> >
> >> > parameters(1).Name = "xxx"
> >> >
> >> > parameters(1).Value = Request.Params("xxx").ToString()
> >> >
> >> > Dim rs As New ReportingService
> >> >
> >> > rs.Credentials = System.Net.CredentialCache.DefaultCredentials
> >> >
> >> > rs.SessionHeaderValue = New SessionHeader
> >> >
> >> > rs.SetReportDataSources(reportPath, dataSources) (error happend here)
> >> >
> >> > Actually, I copied it from MSDN sample. I did not see how the
> >> > DataSources(1) could be set. Do I need to set DataSources(1)?
> >> >
> >> >
> >> >
> >> > Thanks,
> >> >
> >> >
> >> >
> >> > Henry
> >> >
> >> > "Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
> >> > news:%23f6R1FwkEHA.1652@.TK2MSFTNGP09.phx.gbl...
> >> >> where is a value for dsList(0) ?
> >> >>
> >> >> --
> >> >> This posting is provided "AS IS" with no warranties, and confers no
> >> >> rights.
> >> >>
> >> >>
> >> >> "CalinV" <CalinV@.discussions.microsoft.com> wrote in message
> >> >> news:631AD51F-0AD1-4B7A-8C3A-47CE0264A5D5@.microsoft.com...
> >> >>I am having the following problem:
> >> >> I have a shared data source and a report on the server.
> >> >> I want to change the data source of the report programmatically but I
> >> >> am
> >> >> receiving the following error:
> >> >> "The required field DataSource is missing from the input structure"
> >> >> I am using the following code:
> >> >> Dim reference As New ReportServer.DataSourceReference
> >> >> reference.Reference = "/MyDS"
> >> >>
> >> >> Dim dss As New ReportServer.DataSource
> >> >>
> >> >> dss.Item = CType(reference,
> >> >> ReportServer.DataSourceDefinitionOrReference)
> >> >> dss.Name = "MyDS"
> >> >> Dim dsList() As ReportServer.DataSource = New
> >> >> ReportServer.DataSource(1){}
> >> >> dsList(1) = dss
> >> >> dsList(1).Name = "MyDS"
> >> >> rs.SetReportDataSources("/Test report1", dsList)
> >> >>
> >> >> The shared datasource and the report are on the root directory and
> >> >> the
> >> >> report was created with a shared datasource with the name "MyDS"
> >> >> What could be the problem?
> >> >>
> >> >>
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >>
>
>|||Designer does more than just uploading a report.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Eddie" <Eddie@.discussions.microsoft.com> wrote in message
news:5960DE8A-37A4-4C62-A886-E8DDD3881F78@.microsoft.com...
>I got it, phew.
> I tried a couple of things, to make sure it wasn't a bug. First I used a
> known working RDL template, which uses a shared datasource. I deleted a
> node
> and put it back in and and published it. I went ahead and set the
> datasource
> to the newly published report. That seems to work, but I still wonder why
> it
> doesn't work as seamless as when publishing from the Designer.
> "Lev Semenets [MSFT]" wrote:
>> Could you email me the code?
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Eddie" <Eddie@.discussions.microsoft.com> wrote in message
>> news:AC8BA5F5-2106-4203-9701-FE2AAC528F5F@.microsoft.com...
>> > On kinda same vien, I generate a RDL with a shared datasource. Once
>> > published, it says the datasource is no longer valid. Any advice?
>> >
>> > "Lev Semenets [MSFT]" wrote:
>> >
>> >> I think it should be
>> >>
>> >> Dim dataSources(0) As DataSource
>> >> instead of
>> >> Dim dataSources(1) As DataSource
>> >>
>> >> Please let me know if this works for you.
>> >>
>> >> --
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> rights.
>> >>
>> >>
>> >> "Henry Wang" <wangyh@.gmail.com> wrote in message
>> >> news:OhB3Wf3uEHA.3200@.TK2MSFTNGP14.phx.gbl...
>> >> > Hi, Dear All,
>> >> >
>> >> > I have the same problem when I tried to use setDataSource method.
>> >> >
>> >> > Here is my code:
>> >> > Dim reference As New DataSourceReference
>> >> >
>> >> > reference.Reference = "/xxxx/" + DBName
>> >> >
>> >> > Dim dataSources(1) As DataSource
>> >> >
>> >> > Dim ds As New DataSource
>> >> >
>> >> > ds.Item = CType(reference, DataSourceDefinitionOrReference)
>> >> >
>> >> > ds.Name = DBName
>> >> >
>> >> > dataSources(0) = ds
>> >> >
>> >> > ' Render arguments
>> >> >
>> >> > Dim reportPath As String = "/xxx/yyy" +
>> >> > Request.Params("NumberOfT").ToString()
>> >> >
>> >> > Dim format As String = "PDF"
>> >> >
>> >> > Dim historyID As String = Nothing
>> >> >
>> >> > Dim devInfo As String =>> >> > "<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>"
>> >> >
>> >> > ' Prepare report parameter.
>> >> >
>> >> > Dim parameters(1) As ParameterValue
>> >> >
>> >> > parameters(0) = New ParameterValue
>> >> >
>> >> > parameters(0).Name = "ReportID"
>> >> >
>> >> > parameters(0).Value = Request.Params("ReportID").ToString()
>> >> >
>> >> > parameters(1) = New ParameterValue
>> >> >
>> >> > parameters(1).Name = "xxx"
>> >> >
>> >> > parameters(1).Value = Request.Params("xxx").ToString()
>> >> >
>> >> > Dim rs As New ReportingService
>> >> >
>> >> > rs.Credentials = System.Net.CredentialCache.DefaultCredentials
>> >> >
>> >> > rs.SessionHeaderValue = New SessionHeader
>> >> >
>> >> > rs.SetReportDataSources(reportPath, dataSources) (error happend
>> >> > here)
>> >> >
>> >> > Actually, I copied it from MSDN sample. I did not see how the
>> >> > DataSources(1) could be set. Do I need to set DataSources(1)?
>> >> >
>> >> >
>> >> >
>> >> > Thanks,
>> >> >
>> >> >
>> >> >
>> >> > Henry
>> >> >
>> >> > "Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
>> >> > news:%23f6R1FwkEHA.1652@.TK2MSFTNGP09.phx.gbl...
>> >> >> where is a value for dsList(0) ?
>> >> >>
>> >> >> --
>> >> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> >> rights.
>> >> >>
>> >> >>
>> >> >> "CalinV" <CalinV@.discussions.microsoft.com> wrote in message
>> >> >> news:631AD51F-0AD1-4B7A-8C3A-47CE0264A5D5@.microsoft.com...
>> >> >>I am having the following problem:
>> >> >> I have a shared data source and a report on the server.
>> >> >> I want to change the data source of the report programmatically
>> >> >> but I
>> >> >> am
>> >> >> receiving the following error:
>> >> >> "The required field DataSource is missing from the input
>> >> >> structure"
>> >> >> I am using the following code:
>> >> >> Dim reference As New ReportServer.DataSourceReference
>> >> >> reference.Reference = "/MyDS"
>> >> >>
>> >> >> Dim dss As New ReportServer.DataSource
>> >> >>
>> >> >> dss.Item = CType(reference,
>> >> >> ReportServer.DataSourceDefinitionOrReference)
>> >> >> dss.Name = "MyDS"
>> >> >> Dim dsList() As ReportServer.DataSource = New
>> >> >> ReportServer.DataSource(1){}
>> >> >> dsList(1) = dss
>> >> >> dsList(1).Name = "MyDS"
>> >> >> rs.SetReportDataSources("/Test report1", dsList)
>> >> >>
>> >> >> The shared datasource and the report are on the root directory and
>> >> >> the
>> >> >> report was created with a shared datasource with the name "MyDS"
>> >> >> What could be the problem?
>> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>

changing existind dimension datasource

Anybody knows how to change a the datasource for an existing Dimension?

I've tryed deleting them but since i've imported the cube from a diferent server, if I try to delete the dimension it is also deleted from the cubes.

thanks for the help in advance.

Hugo

Not sure if your question is for AS2000 or AS2005.

If it is AS2000, you can change the datasource connetion string. That means it will affect other objects that pointing to this datasource.

If it is AS2005, you should add a table from different datasource and create cube and dimension as usual. Steps are:

1) Create a primary datasource (where you cube is) and dsv

2) Create a secondary datasource (where your dimension is)

3) In dsv designer, select Add\Remove tables. You see the dialog which has darasource drop down box. Select the secondary datasource and add the dimension table to dsv.

4) You may want to create relationship between fact table and dimension table before you create cube and dimension. After that, just now cube wizard as usual.

Hope this helps

Ken

|||

ok. thanks Ken for the info.

My SQL is a 2k.- Thanks for your quick response. I've tried it and it works.

Changing domain password hoses subscriptions

Recently, I was prompted to change my domain password. Changed service
account for SQL and RS (2000 both) and reports and datasource credentials.
Reports run fine. However, getting the infamous "An internal error occurred
on the report server. See the error log for more details." error on all
subscriptions when nothing else changed. Went in and ran rsconfig to update
with the latest pwd but no joy. I know I'm missing something obvious but am
still futzing with this.
Any thoughts as to what I should be checking/correcting?Just a guess, but if your subscriptions store the report as a pdf in a
directory, could there be permissions problems there?
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"kvanhulle" wrote:
> Recently, I was prompted to change my domain password. Changed service
> account for SQL and RS (2000 both) and reports and datasource credentials.
> Reports run fine. However, getting the infamous "An internal error occurred
> on the report server. See the error log for more details." error on all
> subscriptions when nothing else changed. Went in and ran rsconfig to update
> with the latest pwd but no joy. I know I'm missing something obvious but am
> still futzing with this.
> Any thoughts as to what I should be checking/correcting?
>
>

changing datasource to SQL Server

Greetings,
I have a set of reports that were created using the report designer in
Visual Studio. They were originally written to run against an Informix
database, but I now need to change them to run against an SQL Server
DB. I added a new datasource for the SQL Server DB, but couldn't find
*any* way to change the reports to point to this new datasource. I've
worked around this problem by changing the properties of the existing
(Informix) datasource to point to the SQL Server DB, but if anyone
knows how to change *just* the datasource of a report, I'd be very
grateful if they'd let me know.
The queries that the reports execute currently use '?' to indicate an
Informix SQL parameter, but I believe SQL Server uses '@.param_name'
instead. If I replace each ? with @.param_name (in the 'SQL' pane of
the 'Data' tab) and map each @.param_name to the corresponding
ReportParameter, will this work, or is there something else I need to
do?
Thanks in Advance,
DonThe way you change the datasource is by going to the dataset, click on the
... and change the datasource. Or you can edit the rdl. Yes, changing and
remapping the parameters should work. One problem you will have is that RS
will try to create the report parameters so you very well will end up with
extra report parameters that you have to remove. Or, do this. Make sure you
have a copy of the rdl. Open up the report into the designer and go into
code view. Edit the xml with the parameter mapping, save, go to layout and
preview and see if it works.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Don" <donalmurtagh@.yahoo.co.uk> wrote in message
news:aa1c6f54.0504290857.60998e06@.posting.google.com...
> Greetings,
> I have a set of reports that were created using the report designer in
> Visual Studio. They were originally written to run against an Informix
> database, but I now need to change them to run against an SQL Server
> DB. I added a new datasource for the SQL Server DB, but couldn't find
> *any* way to change the reports to point to this new datasource. I've
> worked around this problem by changing the properties of the existing
> (Informix) datasource to point to the SQL Server DB, but if anyone
> knows how to change *just* the datasource of a report, I'd be very
> grateful if they'd let me know.
> The queries that the reports execute currently use '?' to indicate an
> Informix SQL parameter, but I believe SQL Server uses '@.param_name'
> instead. If I replace each ? with @.param_name (in the 'SQL' pane of
> the 'Data' tab) and map each @.param_name to the corresponding
> ReportParameter, will this work, or is there something else I need to
> do?
> Thanks in Advance,
> Don|||Hi Bruce,
Thanks very much for your response. I thought about editing the .rdl
directly in order to change the datasource, but the autogenerated IDs
scared me off - what exactly are these used for?
I'm aware that RS adds report parameters whenever you change an SQL
parameters - it's an annoying "feature", but not catastrophic. Are you
saying that if I replace every reference to the '?' parameters with a
'@.param' in the XML code then I won't have to deal with this?
Thanks Again,
Don|||Yep. I also do this sometimes when I am modifying a complicated SQL
Statement because sometimes with ODBC datasource it loses all the mappings
and I have to remap so I will go in and modify the SQL so the helpful IDE
doesn't mess everything up. Again, make a copy because it is easy to mess up
the xml.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<donalmurtagh@.yahoo.co.uk> wrote in message
news:1114946322.881807.132260@.g14g2000cwa.googlegroups.com...
> Hi Bruce,
> Thanks very much for your response. I thought about editing the .rdl
> directly in order to change the datasource, but the autogenerated IDs
> scared me off - what exactly are these used for?
> I'm aware that RS adds report parameters whenever you change an SQL
> parameters - it's an annoying "feature", but not catastrophic. Are you
> saying that if I replace every reference to the '?' parameters with a
> '@.param' in the XML code then I won't have to deal with this?
> Thanks Again,
> Don
>

Changing DataSource

HI,
I am having a problem and its very urgent,
i have made a report in test database it worked fine, now i wana run it on live database, i have changes its datasource and updated it as well, but agin its piking data from test database.
i cant understand why????
i have refreshed the report, logged in logged off , evry thing possible i have done but it is picking data from test database :(

can any one help me?i have made the TEST database offline now its show error message that TEST data base is offline......i could not understand why it is going for TEST database....when i have updated datasource with LIVE one....!!!

every where it is shown LIVE database but when i m goin to refresh it it goes for TEST :(

plzzzzzzzzzzzzzzz can any 1 help ???|||Problem resolved :)
it was my mistake i was not maping all tables one by one...!! was just maping database...!!!

Changing DatasetName and Datasource programatically

Hello,
Does anyone know if it's possible to programatically change the
datasetName and/or dataSource via either the reportViewer.dll or
through report parameters?
My situation is that I need to be able to switch the database and query
statement from an active database to an archive database, based on a
user selection of whether or not they want to report off the
active/archived database. The report itself and the fields used in it
are identically named in both the archive and active database.
Thus, I'm trying to avoid having to create a duplicate report where the
only difference is the datasource/dataset. Is this possible?
Thank you for any help!You could write a custom data processing extension (DPE). There's probably
an easier way but that would definitely work... althought it isn't
straightfoward or simple.
"kevin.haynie@.gmail.com" wrote:
> Hello,
> Does anyone know if it's possible to programatically change the
> datasetName and/or dataSource via either the reportViewer.dll or
> through report parameters?
> My situation is that I need to be able to switch the database and query
> statement from an active database to an archive database, based on a
> user selection of whether or not they want to report off the
> active/archived database. The report itself and the fields used in it
> are identically named in both the archive and active database.
> Thus, I'm trying to avoid having to create a duplicate report where the
> only difference is the datasource/dataset. Is this possible?
> Thank you for any help!
>|||Hi Kevin,
The easiest way is through report parameters, as you said. All you have
to do is build conditional code around the parameters, and make it
select which DB the user wants to see a report of.
Lance M.
kevin.haynie@.gmail.com wrote:
> Hello,
> Does anyone know if it's possible to programatically change the
> datasetName and/or dataSource via either the reportViewer.dll or
> through report parameters?
> My situation is that I need to be able to switch the database and query
> statement from an active database to an archive database, based on a
> user selection of whether or not they want to report off the
> active/archived database. The report itself and the fields used in it
> are identically named in both the archive and active database.
> Thus, I'm trying to avoid having to create a duplicate report where the
> only difference is the datasource/dataset. Is this possible?
> Thank you for any help!

Changing Data Sources Quick and Dirty

Has anyone stumbled on a quick easy way to change all report DataSources to
one specific shared DataSource? Any cool tricks or scripts or whatnot would
be appreciated. Even just a reply saying that there is no easy way to do it
would be great so I can give up searching for an answer.You could do it manually from the datasources...I am sure you have
figured that part out. You don't actually have to go to the reports to
do this though. I guess there would be a way to change the guid that
represents the datasource on the report in the SQL database. I doubt
that Microsoft will support you if you opt to go that route though. I
would say...practice on a non production machine first. Not sure if
that is much help.|||Timm wrote:
> Has anyone stumbled on a quick easy way to change all report
DataSources to
> one specific shared DataSource? Any cool tricks or scripts or whatnot
would
> be appreciated. Even just a reply saying that there is no easy way to
do it
> would be great so I can give up searching for an answer.
The book "Hitchhiker's guide to reporting service" has a code snippet ,
you need to call soap api SetReportDataSources ,I use it to change
datasouce after I created the reports|||Really? What page is it on?
"ottawa111" wrote:
> Timm wrote:
> > Has anyone stumbled on a quick easy way to change all report
> DataSources to
> > one specific shared DataSource? Any cool tricks or scripts or whatnot
> would
> > be appreciated. Even just a reply saying that there is no easy way to
> do it
> > would be great so I can give up searching for an answer.
> The book "Hitchhiker's guide to reporting service" has a code snippet ,
> you need to call soap api SetReportDataSources ,I use it to change
> datasouce after I created the reports
>|||You can use this code instead the book
Dim reference As New ReportServer.DataSourceReference
reference.Reference = "/MyDS"
Dim dss As New ReportServer.DataSource
dss.Item = CType(reference,
ReportServer.DataSourceDefinitionOrReference)
dss.Name = "MyDS"
Dim dsList() As ReportServer.DataSource = New
ReportServer.DataSource(1){}
dsList(1) = dss
dsList(1).Name = "MyDS"
rs.SetReportDataSources("/Test report1", dsList)
"Timm" wrote:
> Really? What page is it on?
> "ottawa111" wrote:
> >
> > Timm wrote:
> > > Has anyone stumbled on a quick easy way to change all report
> > DataSources to
> > > one specific shared DataSource? Any cool tricks or scripts or whatnot
> > would
> > > be appreciated. Even just a reply saying that there is no easy way to
> > do it
> > > would be great so I can give up searching for an answer.
> >
> > The book "Hitchhiker's guide to reporting service" has a code snippet ,
> > you need to call soap api SetReportDataSources ,I use it to change
> > datasouce after I created the reports
> >
> >