[?] Access data from some different datasources

Hello,
How to access data from some different [shared] datasources within a
dataset?
I'm going to have 3 datasets in a report:
- 1st dataset creates a table (SELECT INTO) in the SharedDataSource1
- 2nd dataset creates a table (SELECT INTO) in the SharedDataSource2
- 3rd dataset queries both created tables and displayes data.
How to?
Thanks,
Lucy.This functionality is not available in RS 2000 but is on wishlist for a
future release. However, you can do SELECT...FROM OPENQUERY(...) to get the
desired functionality.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lusy Crown" <evesq@.uk2.net> wrote in message
news:eiTzOeTZEHA.1048@.tk2msftngp13.phx.gbl...
> Hello,
> How to access data from some different [shared] datasources within a
> dataset?
> I'm going to have 3 datasets in a report:
> - 1st dataset creates a table (SELECT INTO) in the SharedDataSource1
> - 2nd dataset creates a table (SELECT INTO) in the SharedDataSource2
> - 3rd dataset queries both created tables and displayes data.
> How to?
> Thanks,
> Lucy.
>|||Thank you for information. I can use OPENQUERY, but I have to know in
run-time a connection string of desired DataSource. How to get it in Custom
Code if I know a datasource name only?
Thakns,
Lucy.
"Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote in message
news:OMiqMrTZEHA.3508@.TK2MSFTNGP09.phx.gbl...
> This functionality is not available in RS 2000 but is on wishlist for a
> future release. However, you can do SELECT...FROM OPENQUERY(...) to get
the
> desired functionality.
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.|||If I understand you correctly, the connection string will be created for you
when you specify the target database server name while creating a new
dataset.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lusy Crown" <evesq@.uk2.net> wrote in message
news:uMbDOmXZEHA.3716@.TK2MSFTNGP11.phx.gbl...
> Thank you for information. I can use OPENQUERY, but I have to know in
> run-time a connection string of desired DataSource. How to get it in
Custom
> Code if I know a datasource name only?
> Thakns,
> Lucy.
>
> "Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote in message
> news:OMiqMrTZEHA.3508@.TK2MSFTNGP09.phx.gbl...
> > This functionality is not available in RS 2000 but is on wishlist for a
> > future release. However, you can do SELECT...FROM OPENQUERY(...) to get
> the
> > desired functionality.
> >
> > --
> > Ravi Mumulla (Microsoft)
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Ravi,
Let imagine I have to query DataSource1 from a DataSet2 based on
DataSource2. I can type in DataSet2
="Select * from OPENQUERY(" & {DataSet1 Connection String} &
"..............) Order by 1)"
-- or similar, for example.
The problem is that I do not know at report design time what exact server
name, database, credentials will choose an end-user (a customer). However I
need to mention a DataSource1's connetion string in DataSet2 during report
design. I supposed it could be done using custom code and tried to use
GetDataSourceContents, but I did something wrong and always has errors.
Thanks,
Lucy.
"Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote in message
news:OAd7j3XZEHA.808@.tk2msftngp13.phx.gbl...
> If I understand you correctly, the connection string will be created for
you
> when you specify the target database server name while creating a new
> dataset.
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services|||Thanks for the clarification. What you're for is an expression-based
connection string. RS2000 does not natively support this - this is on wish
list for a future release. Here are some other options you could consider
besides the OPENQUERY functionality:
1. Use Use dynamic query text with four-part naming such as: ="select * from
" & Parameters!ServerName.Value & "." & Parameters!DatabaseName.Value & "."
& Parameters!Owner.Value & "." & ".TableName".
2. Write a custom data processing extension. Refer to the following link for
details:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_extend_dataproc_5c2q.asp?frame=true
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lusy Crown" <evesq@.uk2.net> wrote in message
news:%23rx5MFYZEHA.4092@.TK2MSFTNGP11.phx.gbl...
> Ravi,
> Let imagine I have to query DataSource1 from a DataSet2 based on
> DataSource2. I can type in DataSet2
> ="Select * from OPENQUERY(" & {DataSet1 Connection String} &
> "..............) Order by 1)"
> -- or similar, for example.
> The problem is that I do not know at report design time what exact server
> name, database, credentials will choose an end-user (a customer). However
I
> need to mention a DataSource1's connetion string in DataSet2 during report
> design. I supposed it could be done using custom code and tried to use
> GetDataSourceContents, but I did something wrong and always has errors.
> Thanks,
> Lucy.
> "Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote in message
> news:OAd7j3XZEHA.808@.tk2msftngp13.phx.gbl...
> > If I understand you correctly, the connection string will be created for
> you
> > when you specify the target database server name while creating a new
> > dataset.
> >
> > --
> > Ravi Mumulla (Microsoft)
> > SQL Server Reporting Services
>