Execute Different Stored Procedure Based on Parameter Value

I have been trying to determine if I can run a different stored procedure
base on a parameter value. I have a stored procedure on each of my SQL
Server machines that lists the SQL Server jobs. I would like to be able use
a parameter to list the SQL Server machines, select one of the SQL Server
machines and execute the appropriate stored procedure using a linked server
that I already have setup.
How can this be accomplished?
Thanks in advance!Use the generic query designer and put in T-SQL statements in the designer.
Here is some code as an example that I was just messing around with one day.
declare @.SQL varchar(255)
select @.SQL = 'select name from ' + @.Database + '.dbo.sysobjects where xtype
= ''U'' order by name'
exec (@.SQL)
It should give you an idea on how to do what you want to do.
The above had a report parameter of Database.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Scott Lindsey" <ScottLindsey@.discussions.microsoft.com> wrote in message
news:F06CA48B-9827-4304-A0E4-F43C4AE49F49@.microsoft.com...
> I have been trying to determine if I can run a different stored procedure
> base on a parameter value. I have a stored procedure on each of my SQL
> Server machines that lists the SQL Server jobs. I would like to be able
use
> a parameter to list the SQL Server machines, select one of the SQL Server
> machines and execute the appropriate stored procedure using a linked
server
> that I already have setup.
> How can this be accomplished?
> Thanks in advance!|||Hi,
If this can be done without the usage of the stored procedure on the
remote server, you could try something like this:
DECLARE @.SQLString NVARCHAR(4000)
SELECT @.SQLString = N'SELECT * FROM OPENQUERY (' +
RTRIM(@.ParamServerName) + ',SELECT ..,.., FROM dbo.sysjobs '')'
EXEC sp_executesql @.SQLString
as long as your server is set-up to access the msdb database...|||Bruce,
Thanks for the quick response! I ran into a problem. I put the following
in designer:
DECLARE @.SQL varchar(255);
SELECT @.SQL = 'exec ' + @.Server + 'msdb..USP_ListSQLServerJobs';
EXEC (@.SQL)
I have a report parameter setup called Server and it has two values listed
to select. When I try to preview the report, I get compilation errors saying:
The value expression for the textbox â'job_nameâ' refers to the field
â'job_nameâ'. Report item expressions can only refer to fields within the
current data set scope or, if inside an aggregate, the specified data set
scope.
There is one error for each column that is used in the report.
It's like Reporting Services does not recognize the data that the stored
procedure is returning because it is executing a dynamically prepared SQL
statement.
Is there anything I can do to get around this?
"Bruce L-C [MVP]" wrote:
> Use the generic query designer and put in T-SQL statements in the designer.
> Here is some code as an example that I was just messing around with one day.
> declare @.SQL varchar(255)
> select @.SQL = 'select name from ' + @.Database + '.dbo.sysobjects where xtype
> = ''U'' order by name'
> exec (@.SQL)
> It should give you an idea on how to do what you want to do.
> The above had a report parameter of Database.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Scott Lindsey" <ScottLindsey@.discussions.microsoft.com> wrote in message
> news:F06CA48B-9827-4304-A0E4-F43C4AE49F49@.microsoft.com...
> > I have been trying to determine if I can run a different stored procedure
> > base on a parameter value. I have a stored procedure on each of my SQL
> > Server machines that lists the SQL Server jobs. I would like to be able
> use
> > a parameter to list the SQL Server machines, select one of the SQL Server
> > machines and execute the appropriate stored procedure using a linked
> server
> > that I already have setup.
> >
> > How can this be accomplished?
> >
> > Thanks in advance!
>
>|||Try clicking on the refresh fields button (to the right of the ...) it looks
like the refresh button for IE.
Just to check I took the one I sent you and changed it and clicked on the
field refresh and it worked.
One point, what you have there does not look to me like it will work, you
need to have another period. It looks to me like you will get this:
exec someservermsdb..USP_ListSQLServerJobs
the way you currently have it.
Here is another trick you can do when working on something like this.
Replace your exec to this:
select @.SQL as SQLString
Excute the query and you can now see what you have. Good way to figure out
what is happening.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Scott Lindsey" <ScottLindsey@.discussions.microsoft.com> wrote in message
news:8AE02A33-B33A-4D67-9344-82245378AD1F@.microsoft.com...
> Bruce,
> Thanks for the quick response! I ran into a problem. I put the following
> in designer:
> DECLARE @.SQL varchar(255);
> SELECT @.SQL = 'exec ' + @.Server + 'msdb..USP_ListSQLServerJobs';
> EXEC (@.SQL)
> I have a report parameter setup called Server and it has two values listed
> to select. When I try to preview the report, I get compilation errors
saying:
> The value expression for the textbox 'job_name' refers to the field
> 'job_name'. Report item expressions can only refer to fields within the
> current data set scope or, if inside an aggregate, the specified data set
> scope.
> There is one error for each column that is used in the report.
> It's like Reporting Services does not recognize the data that the stored
> procedure is returning because it is executing a dynamically prepared SQL
> statement.
> Is there anything I can do to get around this?
> "Bruce L-C [MVP]" wrote:
> > Use the generic query designer and put in T-SQL statements in the
designer.
> > Here is some code as an example that I was just messing around with one
day.
> >
> > declare @.SQL varchar(255)
> > select @.SQL = 'select name from ' + @.Database + '.dbo.sysobjects where
xtype
> > = ''U'' order by name'
> > exec (@.SQL)
> >
> > It should give you an idea on how to do what you want to do.
> >
> > The above had a report parameter of Database.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> >
> > "Scott Lindsey" <ScottLindsey@.discussions.microsoft.com> wrote in
message
> > news:F06CA48B-9827-4304-A0E4-F43C4AE49F49@.microsoft.com...
> > > I have been trying to determine if I can run a different stored
procedure
> > > base on a parameter value. I have a stored procedure on each of my
SQL
> > > Server machines that lists the SQL Server jobs. I would like to be
able
> > use
> > > a parameter to list the SQL Server machines, select one of the SQL
Server
> > > machines and execute the appropriate stored procedure using a linked
> > server
> > > that I already have setup.
> > >
> > > How can this be accomplished?
> > >
> > > Thanks in advance!
> >
> >
> >|||Bruce,
> Try clicking on the refresh fields button (to the right of the ...) it looks
> like the refresh button for IE.
I am not getting a <Refresh> button on the Preview tab for the report.
> One point, what you have there does not look to me like it will work, you
> need to have another period. It looks to me like you will get this:
> exec someservermsdb..USP_ListSQLServerJobs
> the way you currently have it.
I am adding the extra period that I need to the value that is returned for
the @.Server parameter.
> Here is another trick you can do when working on something like this.
> Replace your exec to this:
> select @.SQL as SQLString
> Excute the query and you can now see what you have. Good way to figure out
> what is happening.
When I add the "select @.SQL as SQLString" and execute it through Query
Analyzer, I get the SQL that I am expecting. When I execute the SQL, it
works perfectly.
Do you have any other possibilities?
Thanks in advance!
Scott
"Bruce L-C [MVP]" wrote:
> Try clicking on the refresh fields button (to the right of the ...) it looks
> like the refresh button for IE.
> Just to check I took the one I sent you and changed it and clicked on the
> field refresh and it worked.
> One point, what you have there does not look to me like it will work, you
> need to have another period. It looks to me like you will get this:
> exec someservermsdb..USP_ListSQLServerJobs
> the way you currently have it.
> Here is another trick you can do when working on something like this.
> Replace your exec to this:
> select @.SQL as SQLString
> Excute the query and you can now see what you have. Good way to figure out
> what is happening.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Scott Lindsey" <ScottLindsey@.discussions.microsoft.com> wrote in message
> news:8AE02A33-B33A-4D67-9344-82245378AD1F@.microsoft.com...
> > Bruce,
> > Thanks for the quick response! I ran into a problem. I put the following
> > in designer:
> >
> > DECLARE @.SQL varchar(255);
> > SELECT @.SQL = 'exec ' + @.Server + 'msdb..USP_ListSQLServerJobs';
> > EXEC (@.SQL)
> >
> > I have a report parameter setup called Server and it has two values listed
> > to select. When I try to preview the report, I get compilation errors
> saying:
> >
> > The value expression for the textbox 'job_name' refers to the field
> > 'job_name'. Report item expressions can only refer to fields within the
> > current data set scope or, if inside an aggregate, the specified data set
> > scope.
> >
> > There is one error for each column that is used in the report.
> >
> > It's like Reporting Services does not recognize the data that the stored
> > procedure is returning because it is executing a dynamically prepared SQL
> > statement.
> >
> > Is there anything I can do to get around this?
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > Use the generic query designer and put in T-SQL statements in the
> designer.
> > > Here is some code as an example that I was just messing around with one
> day.
> > >
> > > declare @.SQL varchar(255)
> > > select @.SQL = 'select name from ' + @.Database + '.dbo.sysobjects where
> xtype
> > > = ''U'' order by name'
> > > exec (@.SQL)
> > >
> > > It should give you an idea on how to do what you want to do.
> > >
> > > The above had a report parameter of Database.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > >
> > > "Scott Lindsey" <ScottLindsey@.discussions.microsoft.com> wrote in
> message
> > > news:F06CA48B-9827-4304-A0E4-F43C4AE49F49@.microsoft.com...
> > > > I have been trying to determine if I can run a different stored
> procedure
> > > > base on a parameter value. I have a stored procedure on each of my
> SQL
> > > > Server machines that lists the SQL Server jobs. I would like to be
> able
> > > use
> > > > a parameter to list the SQL Server machines, select one of the SQL
> Server
> > > > machines and execute the appropriate stored procedure using a linked
> > > server
> > > > that I already have setup.
> > > >
> > > > How can this be accomplished?
> > > >
> > > > Thanks in advance!
> > >
> > >
> > >
>
>|||The refresh fields button is on the dataset tab. You need to be showing the
fields for your dataset. My understanding of the issue you are having is
that you do not have fields showing for the dataset.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Scott Lindsey" <ScottLindsey@.discussions.microsoft.com> wrote in message
news:37D47654-341C-439E-B3C9-D7DD16D36CDD@.microsoft.com...
> Bruce,
> > Try clicking on the refresh fields button (to the right of the ...) it
looks
> > like the refresh button for IE.
> I am not getting a <Refresh> button on the Preview tab for the report.
> > One point, what you have there does not look to me like it will work,
you
> > need to have another period. It looks to me like you will get this:
> >
> > exec someservermsdb..USP_ListSQLServerJobs
> >
> > the way you currently have it.
> I am adding the extra period that I need to the value that is returned for
> the @.Server parameter.
> > Here is another trick you can do when working on something like this.
> > Replace your exec to this:
> >
> > select @.SQL as SQLString
> >
> > Excute the query and you can now see what you have. Good way to figure
out
> > what is happening.
> When I add the "select @.SQL as SQLString" and execute it through Query
> Analyzer, I get the SQL that I am expecting. When I execute the SQL, it
> works perfectly.
> Do you have any other possibilities?
> Thanks in advance!
> Scott
> "Bruce L-C [MVP]" wrote:
> > Try clicking on the refresh fields button (to the right of the ...) it
looks
> > like the refresh button for IE.
> >
> > Just to check I took the one I sent you and changed it and clicked on
the
> > field refresh and it worked.
> >
> > One point, what you have there does not look to me like it will work,
you
> > need to have another period. It looks to me like you will get this:
> >
> > exec someservermsdb..USP_ListSQLServerJobs
> >
> > the way you currently have it.
> >
> > Here is another trick you can do when working on something like this.
> > Replace your exec to this:
> >
> > select @.SQL as SQLString
> >
> > Excute the query and you can now see what you have. Good way to figure
out
> > what is happening.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Scott Lindsey" <ScottLindsey@.discussions.microsoft.com> wrote in
message
> > news:8AE02A33-B33A-4D67-9344-82245378AD1F@.microsoft.com...
> > > Bruce,
> > > Thanks for the quick response! I ran into a problem. I put the
following
> > > in designer:
> > >
> > > DECLARE @.SQL varchar(255);
> > > SELECT @.SQL = 'exec ' + @.Server + 'msdb..USP_ListSQLServerJobs';
> > > EXEC (@.SQL)
> > >
> > > I have a report parameter setup called Server and it has two values
listed
> > > to select. When I try to preview the report, I get compilation errors
> > saying:
> > >
> > > The value expression for the textbox 'job_name' refers to the field
> > > 'job_name'. Report item expressions can only refer to fields within
the
> > > current data set scope or, if inside an aggregate, the specified data
set
> > > scope.
> > >
> > > There is one error for each column that is used in the report.
> > >
> > > It's like Reporting Services does not recognize the data that the
stored
> > > procedure is returning because it is executing a dynamically prepared
SQL
> > > statement.
> > >
> > > Is there anything I can do to get around this?
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > > > Use the generic query designer and put in T-SQL statements in the
> > designer.
> > > > Here is some code as an example that I was just messing around with
one
> > day.
> > > >
> > > > declare @.SQL varchar(255)
> > > > select @.SQL = 'select name from ' + @.Database + '.dbo.sysobjects
where
> > xtype
> > > > = ''U'' order by name'
> > > > exec (@.SQL)
> > > >
> > > > It should give you an idea on how to do what you want to do.
> > > >
> > > > The above had a report parameter of Database.
> > > >
> > > >
> > > > --
> > > > Bruce Loehle-Conger
> > > > MVP SQL Server Reporting Services
> > > >
> > > >
> > > > "Scott Lindsey" <ScottLindsey@.discussions.microsoft.com> wrote in
> > message
> > > > news:F06CA48B-9827-4304-A0E4-F43C4AE49F49@.microsoft.com...
> > > > > I have been trying to determine if I can run a different stored
> > procedure
> > > > > base on a parameter value. I have a stored procedure on each of
my
> > SQL
> > > > > Server machines that lists the SQL Server jobs. I would like to
be
> > able
> > > > use
> > > > > a parameter to list the SQL Server machines, select one of the SQL
> > Server
> > > > > machines and execute the appropriate stored procedure using a
linked
> > > > server
> > > > > that I already have setup.
> > > > >
> > > > > How can this be accomplished?
> > > > >
> > > > > Thanks in advance!
> > > >
> > > >
> > > >
> >
> >
> >|||Bruce,
Thanks! I found it and everything is working great now. I really
appreciate the help!
Scott
"Bruce L-C [MVP]" wrote:
> The refresh fields button is on the dataset tab. You need to be showing the
> fields for your dataset. My understanding of the issue you are having is
> that you do not have fields showing for the dataset.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Scott Lindsey" <ScottLindsey@.discussions.microsoft.com> wrote in message
> news:37D47654-341C-439E-B3C9-D7DD16D36CDD@.microsoft.com...
> > Bruce,
> > > Try clicking on the refresh fields button (to the right of the ...) it
> looks
> > > like the refresh button for IE.
> > I am not getting a <Refresh> button on the Preview tab for the report.
> >
> > > One point, what you have there does not look to me like it will work,
> you
> > > need to have another period. It looks to me like you will get this:
> > >
> > > exec someservermsdb..USP_ListSQLServerJobs
> > >
> > > the way you currently have it.
> > I am adding the extra period that I need to the value that is returned for
> > the @.Server parameter.
> >
> > > Here is another trick you can do when working on something like this.
> > > Replace your exec to this:
> > >
> > > select @.SQL as SQLString
> > >
> > > Excute the query and you can now see what you have. Good way to figure
> out
> > > what is happening.
> > When I add the "select @.SQL as SQLString" and execute it through Query
> > Analyzer, I get the SQL that I am expecting. When I execute the SQL, it
> > works perfectly.
> >
> > Do you have any other possibilities?
> >
> > Thanks in advance!
> > Scott
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > Try clicking on the refresh fields button (to the right of the ...) it
> looks
> > > like the refresh button for IE.
> > >
> > > Just to check I took the one I sent you and changed it and clicked on
> the
> > > field refresh and it worked.
> > >
> > > One point, what you have there does not look to me like it will work,
> you
> > > need to have another period. It looks to me like you will get this:
> > >
> > > exec someservermsdb..USP_ListSQLServerJobs
> > >
> > > the way you currently have it.
> > >
> > > Here is another trick you can do when working on something like this.
> > > Replace your exec to this:
> > >
> > > select @.SQL as SQLString
> > >
> > > Excute the query and you can now see what you have. Good way to figure
> out
> > > what is happening.
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "Scott Lindsey" <ScottLindsey@.discussions.microsoft.com> wrote in
> message
> > > news:8AE02A33-B33A-4D67-9344-82245378AD1F@.microsoft.com...
> > > > Bruce,
> > > > Thanks for the quick response! I ran into a problem. I put the
> following
> > > > in designer:
> > > >
> > > > DECLARE @.SQL varchar(255);
> > > > SELECT @.SQL = 'exec ' + @.Server + 'msdb..USP_ListSQLServerJobs';
> > > > EXEC (@.SQL)
> > > >
> > > > I have a report parameter setup called Server and it has two values
> listed
> > > > to select. When I try to preview the report, I get compilation errors
> > > saying:
> > > >
> > > > The value expression for the textbox 'job_name' refers to the field
> > > > 'job_name'. Report item expressions can only refer to fields within
> the
> > > > current data set scope or, if inside an aggregate, the specified data
> set
> > > > scope.
> > > >
> > > > There is one error for each column that is used in the report.
> > > >
> > > > It's like Reporting Services does not recognize the data that the
> stored
> > > > procedure is returning because it is executing a dynamically prepared
> SQL
> > > > statement.
> > > >
> > > > Is there anything I can do to get around this?
> > > >
> > > > "Bruce L-C [MVP]" wrote:
> > > >
> > > > > Use the generic query designer and put in T-SQL statements in the
> > > designer.
> > > > > Here is some code as an example that I was just messing around with
> one
> > > day.
> > > > >
> > > > > declare @.SQL varchar(255)
> > > > > select @.SQL = 'select name from ' + @.Database + '.dbo.sysobjects
> where
> > > xtype
> > > > > = ''U'' order by name'
> > > > > exec (@.SQL)
> > > > >
> > > > > It should give you an idea on how to do what you want to do.
> > > > >
> > > > > The above had a report parameter of Database.
> > > > >
> > > > >
> > > > > --
> > > > > Bruce Loehle-Conger
> > > > > MVP SQL Server Reporting Services
> > > > >
> > > > >
> > > > > "Scott Lindsey" <ScottLindsey@.discussions.microsoft.com> wrote in
> > > message
> > > > > news:F06CA48B-9827-4304-A0E4-F43C4AE49F49@.microsoft.com...
> > > > > > I have been trying to determine if I can run a different stored
> > > procedure
> > > > > > base on a parameter value. I have a stored procedure on each of
> my
> > > SQL
> > > > > > Server machines that lists the SQL Server jobs. I would like to
> be
> > > able
> > > > > use
> > > > > > a parameter to list the SQL Server machines, select one of the SQL
> > > Server
> > > > > > machines and execute the appropriate stored procedure using a
> linked
> > > > > server
> > > > > > that I already have setup.
> > > > > >
> > > > > > How can this be accomplished?
> > > > > >
> > > > > > Thanks in advance!
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||Great!
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Scott Lindsey" <ScottLindsey@.discussions.microsoft.com> wrote in message
news:47DEE444-3ABA-46F9-B5DD-0CCBDE617260@.microsoft.com...
> Bruce,
> Thanks! I found it and everything is working great now. I really
> appreciate the help!
> Scott
> "Bruce L-C [MVP]" wrote:
> > The refresh fields button is on the dataset tab. You need to be showing
the
> > fields for your dataset. My understanding of the issue you are having is
> > that you do not have fields showing for the dataset.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> >
> > "Scott Lindsey" <ScottLindsey@.discussions.microsoft.com> wrote in
message
> > news:37D47654-341C-439E-B3C9-D7DD16D36CDD@.microsoft.com...
> > > Bruce,
> > > > Try clicking on the refresh fields button (to the right of the ...)
it
> > looks
> > > > like the refresh button for IE.
> > > I am not getting a <Refresh> button on the Preview tab for the report.
> > >
> > > > One point, what you have there does not look to me like it will
work,
> > you
> > > > need to have another period. It looks to me like you will get this:
> > > >
> > > > exec someservermsdb..USP_ListSQLServerJobs
> > > >
> > > > the way you currently have it.
> > > I am adding the extra period that I need to the value that is returned
for
> > > the @.Server parameter.
> > >
> > > > Here is another trick you can do when working on something like
this.
> > > > Replace your exec to this:
> > > >
> > > > select @.SQL as SQLString
> > > >
> > > > Excute the query and you can now see what you have. Good way to
figure
> > out
> > > > what is happening.
> > > When I add the "select @.SQL as SQLString" and execute it through Query
> > > Analyzer, I get the SQL that I am expecting. When I execute the SQL,
it
> > > works perfectly.
> > >
> > > Do you have any other possibilities?
> > >
> > > Thanks in advance!
> > > Scott
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > > > Try clicking on the refresh fields button (to the right of the ...)
it
> > looks
> > > > like the refresh button for IE.
> > > >
> > > > Just to check I took the one I sent you and changed it and clicked
on
> > the
> > > > field refresh and it worked.
> > > >
> > > > One point, what you have there does not look to me like it will
work,
> > you
> > > > need to have another period. It looks to me like you will get this:
> > > >
> > > > exec someservermsdb..USP_ListSQLServerJobs
> > > >
> > > > the way you currently have it.
> > > >
> > > > Here is another trick you can do when working on something like
this.
> > > > Replace your exec to this:
> > > >
> > > > select @.SQL as SQLString
> > > >
> > > > Excute the query and you can now see what you have. Good way to
figure
> > out
> > > > what is happening.
> > > >
> > > > --
> > > > Bruce Loehle-Conger
> > > > MVP SQL Server Reporting Services
> > > >
> > > > "Scott Lindsey" <ScottLindsey@.discussions.microsoft.com> wrote in
> > message
> > > > news:8AE02A33-B33A-4D67-9344-82245378AD1F@.microsoft.com...
> > > > > Bruce,
> > > > > Thanks for the quick response! I ran into a problem. I put the
> > following
> > > > > in designer:
> > > > >
> > > > > DECLARE @.SQL varchar(255);
> > > > > SELECT @.SQL = 'exec ' + @.Server +
'msdb..USP_ListSQLServerJobs';
> > > > > EXEC (@.SQL)
> > > > >
> > > > > I have a report parameter setup called Server and it has two
values
> > listed
> > > > > to select. When I try to preview the report, I get compilation
errors
> > > > saying:
> > > > >
> > > > > The value expression for the textbox 'job_name' refers to the
field
> > > > > 'job_name'. Report item expressions can only refer to fields
within
> > the
> > > > > current data set scope or, if inside an aggregate, the specified
data
> > set
> > > > > scope.
> > > > >
> > > > > There is one error for each column that is used in the report.
> > > > >
> > > > > It's like Reporting Services does not recognize the data that the
> > stored
> > > > > procedure is returning because it is executing a dynamically
prepared
> > SQL
> > > > > statement.
> > > > >
> > > > > Is there anything I can do to get around this?
> > > > >
> > > > > "Bruce L-C [MVP]" wrote:
> > > > >
> > > > > > Use the generic query designer and put in T-SQL statements in
the
> > > > designer.
> > > > > > Here is some code as an example that I was just messing around
with
> > one
> > > > day.
> > > > > >
> > > > > > declare @.SQL varchar(255)
> > > > > > select @.SQL = 'select name from ' + @.Database + '.dbo.sysobjects
> > where
> > > > xtype
> > > > > > = ''U'' order by name'
> > > > > > exec (@.SQL)
> > > > > >
> > > > > > It should give you an idea on how to do what you want to do.
> > > > > >
> > > > > > The above had a report parameter of Database.
> > > > > >
> > > > > >
> > > > > > --
> > > > > > Bruce Loehle-Conger
> > > > > > MVP SQL Server Reporting Services
> > > > > >
> > > > > >
> > > > > > "Scott Lindsey" <ScottLindsey@.discussions.microsoft.com> wrote
in
> > > > message
> > > > > > news:F06CA48B-9827-4304-A0E4-F43C4AE49F49@.microsoft.com...
> > > > > > > I have been trying to determine if I can run a different
stored
> > > > procedure
> > > > > > > base on a parameter value. I have a stored procedure on each
of
> > my
> > > > SQL
> > > > > > > Server machines that lists the SQL Server jobs. I would like
to
> > be
> > > > able
> > > > > > use
> > > > > > > a parameter to list the SQL Server machines, select one of the
SQL
> > > > Server
> > > > > > > machines and execute the appropriate stored procedure using a
> > linked
> > > > > > server
> > > > > > > that I already have setup.
> > > > > > >
> > > > > > > How can this be accomplished?
> > > > > > >
> > > > > > > Thanks in advance!
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >