executing a stored procedure in the background

I am new to SQL and to vbscript, none the less I have a need to handle
some data that is stored in a MS SQL2000 data base.
I created a stored procedure that will select data from the SQL and
store it in a text file in a folder where is will be accessed and
processed by another process. I did this using the tools in Enterprise
Manager.
I found an example vbscript to use to execute the stored procedure. I
modified it a bit and, when it is run, the stored procedure is executed
and, utlimately, the text file is placed in the folder.
I have a few details to work out before this project is fully
functional. The major detail at this juncture is getting the stored
procedure to run without presenting dialog boxes or the process status
screen. Which brings me to the point of this topic.
I need some guidelines or pointers to the way a stored procedure is
designed so that when it runs, the process stays in the background and
dialog boxes or process screens do not appear.
Any help is appreciated.
PatrickThis isn't a feature of a stored procedure. It's how you wrote your
application. If you call a stored procedure, passing all the parameters
needed to it, you are going to get a return set of data, which the
application will handle (or not handle) based on how you wrote the app.
There is no dialog box or process screen, unless your app is producing them.
MeanOldDBA
derrickleggett@.hotmail.com
http://weblogs.sqlteam.com/derrickl
When life gives you a lemon, fire the DBA.
"raglin" wrote:

> I am new to SQL and to vbscript, none the less I have a need to handle
> some data that is stored in a MS SQL2000 data base.
> I created a stored procedure that will select data from the SQL and
> store it in a text file in a folder where is will be accessed and
> processed by another process. I did this using the tools in Enterprise
> Manager.
> I found an example vbscript to use to execute the stored procedure. I
> modified it a bit and, when it is run, the stored procedure is executed
> and, utlimately, the text file is placed in the folder.
> I have a few details to work out before this project is fully
> functional. The major detail at this juncture is getting the stored
> procedure to run without presenting dialog boxes or the process status
> screen. Which brings me to the point of this topic.
> I need some guidelines or pointers to the way a stored procedure is
> designed so that when it runs, the process stays in the background and
> dialog boxes or process screens do not appear.
> Any help is appreciated.
> Patrick
>|||raglin (pzelenka@.gmail.com) writes:
> I am new to SQL and to vbscript, none the less I have a need to handle
> some data that is stored in a MS SQL2000 data base.
> I created a stored procedure that will select data from the SQL and
> store it in a text file in a folder where is will be accessed and
> processed by another process. I did this using the tools in Enterprise
> Manager.
> I found an example vbscript to use to execute the stored procedure. I
> modified it a bit and, when it is run, the stored procedure is executed
> and, utlimately, the text file is placed in the folder.
> I have a few details to work out before this project is fully
> functional. The major detail at this juncture is getting the stored
> procedure to run without presenting dialog boxes or the process status
> screen. Which brings me to the point of this topic.
> I need some guidelines or pointers to the way a stored procedure is
> designed so that when it runs, the process stays in the background and
> dialog boxes or process screens do not appear.
It sounds as if the simplest in your case, you would set up a job that
is run from SQL Server Agent. You do this under Management->Jobs in
Enterprise Manager. A job consists of one or more steps and has a schedule.
A step can be as simple as a T-SQL command. In your case it would be
run the VB script. I assume, then, that VB script is the part that receives
the data and creates the file.
If the procedure runs BCP from xp_cmdshell to create the file, then
there is no need for the VBscript thing - you can run the procedure
directly from the job step.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

ExecuteNonQuery crash my application

I'm working on an application with Compact Framework 2 and SQL Server Mobile
2005. When the application start, if there is no data base in its folder, one
is created using CreateDataBase from SqlCeEngine (I'm sure that every object
used for that operation is closed after create the file). After that, it
pulls the data from a SQL Server 2000.
In Windows Mobile 2005, everything works fine... but when my application is
running under Windows CE 4.2, after any synchronization, if I throw any
instruction like "UPDATE" or "INSERT" (not with "DELETE") with
ExecuteNonQuery, the application crashes and exits without an error
notification. When I try to open the application without synchronization
because it's unnecessary, everything works fine. If I try to make another
synchronization, works... but then I throw another instruction with
ExecuteNonQuery, it crashes the same way.
So, my solution was to close the application everytime there is a
synchronization, but that's useless.
I've tried not to create the database with code, dropping all the tables
before the synchronization and it works... but it crashes anyway when I use
ExecuteNonQuery.
?Is there any solution for this? ?Anyone has the same problem?
I too have an application developed in .NET CF 2.0 , with SQL CE 3.0
database. I am executing the application on HP-iPAQ Win CE 4.2 device. When
there is a call to
update, and ExecuteNonQuery, the application crashes.
Synchronization is done with ADO.Net. Is there a way to resolve this?
Thanks,
Sangeetha

Execute dynamic generate SQL with length > 8000

I am now writing a stored procedure that will dynamically generate a
trigger base on a specific table structure. The generated trigger
script will have variable lenght, depends on how many columns are
defined inside the specific table.
I plan to generate the CREATE TIRGGER script on-the-fly and execute it,
but I comes with a problem that, if the script generated is longer than
8000, VARCHAR is simply cannot handle it. While the length of the
script is undeterministic, I cannot split the trigger script into a
constant number of VARCHAR variable. May I know if there is any
workaround on it ?
Thx~>I am now writing a stored procedure that will dynamically generate a
> trigger base on a specific table structure. The generated trigger
> script will have variable lenght, depends on how many columns are
> defined inside the specific table.
And this will really exceed 8000 characters? I think you will find that
this will be impossible to manage. Before you go down this route, I
strongly recommend reading http://www.sommarskog.se/dynamic_sql.html, and
consider generating new triggers in your application as opposed to within a
stored procedure.
However note that you can execute longer strings quite simply.
DECLARE @.sql_1 VARCHAR(8000), @.sql_2 VARCHAR(8000)
SET @.sql_1 = '....'
SET @.sql_2 = '....'
EXEC(@.sql1 + @.sql2)|||John Shum (eurostar@.gmail.com) writes:
> I am now writing a stored procedure that will dynamically generate a
> trigger base on a specific table structure. The generated trigger
> script will have variable lenght, depends on how many columns are
> defined inside the specific table.
> I plan to generate the CREATE TIRGGER script on-the-fly and execute it,
> but I comes with a problem that, if the script generated is longer than
> 8000, VARCHAR is simply cannot handle it. While the length of the
> script is undeterministic, I cannot split the trigger script into a
> constant number of VARCHAR variable. May I know if there is any
> workaround on it ?
I don't know what the purpose is, but to me this sounds like something
I would prefer to do in Perl or Visual Basic. Least of all I would
like to do it in T-SQL.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

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!
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >