Linking Datasets

How can you link two datasets on a report that come from different databases.
I tried to create a queried parameter that pulls its value from a dataset1
field and use in the where clause of my dataset2 query. It gets an error
message indicating you cannot have a forward looking parameter. Any help for
this newbie is greatly appreciated.You should look at using subreports.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"rdavis104" <rdavis104@.discussions.microsoft.com> wrote in message
news:5F433F07-C1A9-4B7F-8689-890A5476D8C1@.microsoft.com...
> How can you link two datasets on a report that come from different
> databases.
> I tried to create a queried parameter that pulls its value from a dataset1
> field and use in the where clause of my dataset2 query. It gets an error
> message indicating you cannot have a forward looking parameter. Any help
> for
> this newbie is greatly appreciated.|||Subreports worked great, very easy to setup.
Thanks
"Bruce L-C [MVP]" wrote:
> You should look at using subreports.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "rdavis104" <rdavis104@.discussions.microsoft.com> wrote in message
> news:5F433F07-C1A9-4B7F-8689-890A5476D8C1@.microsoft.com...
> > How can you link two datasets on a report that come from different
> > databases.
> > I tried to create a queried parameter that pulls its value from a dataset1
> > field and use in the where clause of my dataset2 query. It gets an error
> > message indicating you cannot have a forward looking parameter. Any help
> > for
> > this newbie is greatly appreciated.
>
>|||I ran across another snag. When I export the main report (with subreports)
to Excel the cells that should contain the subreport data have "Subreports
within table/matrix cells are ignored" as a value. Not sure if I missed
something or if I am doing something wrong. This report will need to output
to Excel format. Thanks in advance for the help.
"rdavis104" wrote:
> Subreports worked great, very easy to setup.
> Thanks
> "Bruce L-C [MVP]" wrote:
> > You should look at using subreports.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "rdavis104" <rdavis104@.discussions.microsoft.com> wrote in message
> > news:5F433F07-C1A9-4B7F-8689-890A5476D8C1@.microsoft.com...
> > > How can you link two datasets on a report that come from different
> > > databases.
> > > I tried to create a queried parameter that pulls its value from a dataset1
> > > field and use in the where clause of my dataset2 query. It gets an error
> > > message indicating you cannot have a forward looking parameter. Any help
> > > for
> > > this newbie is greatly appreciated.
> >
> >
> >sql

server w/ text field gets Connection Broken error

We have a stored proc on Server B called:

my_sp_server_b it takes 1 parameter a text field as a parameter, with default set to NULL

this proc calls:

my_sp_server_a through a linked server (which happens to be the same server, different DB), it has two parameters: my_id int, my_text text w/ my_text having a default set to NULL

This second stored procedure just selects back an ID that is passed to it (to keep things simple).

If we pass any string value to my_sp_server_b we get the appropriate hardcoded ID passed to my_sp_server_a. If we pass NULL to my_sp_server_b we get the following error:

[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData (CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.

Connection Broken

If we remove the linked server, and just reference my_sp_server_a via the scoped DB, we do not get an error. If we change the data type in both procs to varchar(50) we do not get an error. If we change the data type to nText we still get an error. If we put IF logic into stored procedure: my_sp_server_b to check for NULL in the input parameter and if it true then to pass NULL explicitly to my_sp_server_a we do not get an error.

It seems to be a combination of using a linked server and trying to pass a text (or nText variable) with a NULL value to stored procedure. Sometimes the error changes based on which scenario I described above - but we consistantly receive an error unless we do some of the workarounds described above.

Any ideas?If I change the linked server from SQL Server to ODBC I get this error message:

Server: Msg 0, Level 19, State 1, Line 15
SqlDumpExceptionHandler: Process 244 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.|||Here is the DML:

-- Run on DATABASE_1
CREATE PROCEDURE my_sp_server_a
@.the_id int, @.the_text TEXT = NULL
AS

SELECT @.the_id

GO

-- Run on DATABASE_2
CREATE PROC my_sp_server_b @.my_text TEXT = NULL
AS

EXEC MY_LINKED_SERVER.DATABASE_1.DBO.my_sp_server_a @.the_id = 1, @.the_text = @.my_text

GO