Executing Oracle Stored Procedure with output parameters using ADO.NET connection

I am a bit confused by an issue that I am having with executing an Oracle stored procedure (with an output parameter) using an ADO.NET connection object. I am able to get this working using an OLEDB connection, but I have no idea why the ADO.NET connection doesn't work. (Bug, by design, or my ignorance?) Actually, I can even get this to work if I use the .NET Providers for OLE DB\Microsoft OLE DB Provider for ORACLE if we set the connectionType to ADO.NET. This is the error that I am receiving:

[Execute SQL Task] Error: Executing the query "pkg_utility_read.test_out_var " failed with the following error: "The OracleParameterCollection only accepts non-null OracleParameter type objects, not SqlParameter objects.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

It is also worth mentioning that the ORACLE stored procedure has an out parameter with a NUMBER datatype which I think maps to the ADO.NET Int32 datatype. I guess OLE DB datatypes are more closely mapped to ORACLE datatypes. In OLE DB you can set the parameter to double and the ORACLE stored procedure to NUMBER and it works.

Any help on this would be most appriciated.

hi Jason,

I got the same error. What driver are you using? I use .NET provider Oracle client data provider.

Can you also tell me how did you successfully invoke the stored proc using OLEDB for Oracle?

I have an sp with 1 input parameter of type Number and 14 output parameters some of type Number and some Varchar2.

Can you tell me the sql statement syntax to use and the parameter mapping to use. I know that the parameter name starts with 0,1,2...

Please help

thanks.

sandeep

sql

executing a dymamically built string with an OUTPUT parameter

Hi friends,

I have a dynamically built string that i need to execute and set a parameter to. I have come accross help in the books online but I need to set a variable to the answer of the dynamically executed string. It looks as follows:

DECLARE @.sRCDQueryString nvarchar(1000)
DECLARE @.sActivityName varchar(100)
DECLARE @.sRCDParmDefinition nvarchar(500)
DECLARE @.lGLCodeID int
DECLARE @.rRCDUnitValue real

SET @.lGLCOdeID = 1--391
SET @.sActivityName = 'REPPLA'

SET @.sRCDQueryString = 'SELECT ' + @.sActivityName +
' FROM ABCRCDMatrix WHERE GLCodeID = @.GL'
SET @.sRCDParmDefinition = '@.GL int, @.Value real OUTPUT';
EXEC sp_executesql @.sRCDQueryString,@.sRCDParmDefinition,@.GL = @.lGLCodeID, @.Value = @.rRCDUnitValue OUTPUT

select @.rRCDUnitValue

When I execute this code, I get a value by the line in red but it doesn't seem to be allocating a the result correctly to @.rRCDUnitValue since this variable is null

How can i get the result correctly allocated to the variable?

Regards

Use the following statement, you missed the value assignment,

Code Snippet

DECLARE @.sRCDQueryString nvarchar(1000)

DECLARE @.sActivityName varchar(100)

DECLARE @.sRCDParmDefinition nvarchar(500)

DECLARE @.lGLCodeID int

DECLARE @.rRCDUnitValue real

SET @.lGLCOdeID =1--391

SET @.sActivityName = 'REPPLA'

SET @.sRCDQueryString = 'SELECT @.Value=' + @.sActivityName +

' FROM ABCRCDMatrix WHERE GLCodeID = @.GL'

SET @.sRCDParmDefinition = '@.GL int, @.Value real OUTPUT';

EXEC sp_executesql @.sRCDQueryString,@.sRCDParmDefinition,@.GL = @.lGLCodeID, @.Value = @.rRCDUnitValue OUTPUT

select @.rRCDUnitValue

|||

You are declaring a parameter, but that parameter is not being used at all inside the dynamic statement. See this example:

Code Snippet

use northwind

go

declare @.sql nvarchar(4000)

declare @.orderid int

declare @.order_total money

set @.sql = N'select @.order_total = sum(quantity * unitprice * (1.00 - discount)) from dbo.[order details] where orderid = @.orderid'

set @.orderid = 10250

exec dbo.sp_executesql @.sql, N'@.orderid int, @.order_total money OUTPUT', @.orderid, @.order_total OUTPUT

select @.order_total

go

AMB

|||

You got the answers for how to fix yoru code. But why do you need dynamic SQL in the first place? Are you aware of the risks and performance problems (benefits in some cases) with dynamic SQL code?

|||Hi Manivannan,

The @.sActivityName is actually the name of an unknown column that is looked up on another table, wich has been pivoted on the current table i'm trying to do a lookup on.

Thanks for the response
Mike
|||Hi Umachandar,

Yes I am aware of the performance knock but I have no choice in this matter to use a dynamic string since the columns I need to lookup/use are unknown at the point of execution since they are looked up from another table..

Essentially they are the result of a table that had been pivoted.

Regards
Mike
|||Ahhh, Thank you indeed sir, you have showed me the mistake and I appreciate it coz my variable now returns a value.

Kind Regards
Mike
sql

execute ssis in sql

Hi,
Using SSIS, how is it possible to execute another ssis package and pass a parameter to it?
For example, I would like to have a sql code that runs the package say "d:\sysappl\CEM\SSIS\CSA.dtsx" and pass a parameter of fileName because the CSA.dtsx package requires a filename.

Something like:
execute "d:\sysappl\CEM\SSIS\CSA.dtsx", varfileName

Thanks

You can use an Execute Process task to execute DTEXEC.exe. If you search for DTEXEC.EXE on Microsoft's site, you'll get a page with all of the parameters necessary to do what you desire.

Or, you can use an Execute Package task along with Parent Package Configurations to pass down a variable value.

Execute SQL Task Editor - Variables

Hi,

Im trying to do an Insert:

INSERT INTO myTable(column1)

VALUES(..)

How can I use my parameter mapping value inside the SQL statement ablove?

Thank you.

Use ? where you want to use the parameters.

INSERT INTO myTable(column1)

VALUES (?)

|||

My SQL Task looks as follows:

Parameter Mapping:

User::ID Direction: Input Data Type: VarChar ParameterName: ID

SQL SourceType:

Direct Input

INSERT INTO Table1(id, name)
VALUES (1,'john')

How can I use my Parameter Mapping value inside the SQL Insert statement (instead of the 1 I put above)?

Thank you.

|||Try using a parameterName of 0 (zero)

Then:
INSERT INTO Table1 (id, name) VALUES (?,'john')|||

It worked!!

Thanks a lot!!

EXECUTE SQL string with parameter and return value

Hi all,

I would like execute an SQL string who calls a stored procedure with param and return a value:

declare @.query nvarchar(50)

set @.query = 'sp_test 1'

declare @.resultat int

exec @.resultat = @.query

select @.resultat

Its returns a error message:

"Could not find stored procedure 'sp_test 1'"

The command

exec(@.query)

works fine, but I can't retreive the return value and I can't do

exec @.resultat = (@.query)

How can I do?

Thanks,

Aurlien

use sp_executesql|||

Thanks very much,

With sp_executesql, the stored procedure is correctly executed, but it don't return the return value...

exec @.resultat = sp_executesql @.requete

@.resultat is still at 0 event if my stored procedure returns other :'(

How can I do?

Thanks very much

|||

check this example i use a cursor.. good luck

CREATE procedure test
as
begin
DECLARE @.AuthorID char(11)
declare @.sql nvarchar(4000)
set @.sql=' SET @.c1 = CURSOR STATIC FOR SELECT au_id FROM authors; OPEN @.c1'--'SELECT au_id FROM authors'

DECLARE @.c1 CURSOR

EXEC sp_executesql N'SET @.c1 = CURSOR STATIC FOR SELECT au_id FROM authors; OPEN @.c1', N'@.c1 cursor OUTPUT', @.c1 OUTPUT

FETCH NEXT FROM @.c1
INTO @.AuthorID
WHILE @.@.FETCH_STATUS = 0
BEGIN

PRINT @.AuthorID

FETCH NEXT FROM @.c1
INTO @.AuthorID
END
CLOSE @.c1
DEALLOCATE @.c1
end

|||

Another exemple when using dynamic queries

declare @.query nvarchar(50)

set @.query = 'select ''' + 'sp_test 1' + ''''

CREATE TABLE #resultat
(resultat sql_variant)

INSERT INTO #resultat exec sp_executesql @.query

select resultat from #resultat

execute query for each row

I want to execute a query for each row in a report since a parameter in that query relies on a field in the row. Is there a way to do this?I used a subreport to do this.

Execute properly even if no parameter value is supplies

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================

-- =============================================
ALTER PROCEDURE [dbo].[Product_FindByParameters]
(
@.Name Varchar(255),
@.ManufactureID bigint,
@.ShortDescription Varchar(255),
@.ManufactureProductID Varchar(255),
@.ItemsInStock bigint,
@.StorePartNumber Varchar(255)

)


AS
BEGIN

SELECT P.ProductId,
P.StorePartNumber,
P.ShortDescription,
P.ManufactureProductID,
P.Name,
P.Price,
P.ItemsInStock,
M.ManufactureName
FROM Product P left join Manufacture M
ON P.ManufactureID=M.ManufactureID
WHERE
( P.Name like '%' + @.Name + '%' OR @.Name is null)
AND (P.ShortDescription LIKE '%' + @.ShortDescription + '%' OR @.ShortDescription is null)
AND( P.ManufactureProductID LIKE '%' + @.ManufactureProductID + '%' OR @.ManufactureProductID is null)
AND (P.ItemsInStock=@.ItemsInStock)
AND (P.ManufactureID = @.ManufactureID OR @.ManufactureID is null)
END

--exec [dbo].[Product_FindByParameters] 'Heavy-Duty ',7,'Compact Size','DC727KA' ,0,''
--exec [dbo].[Product_FindByParameters] 'Heavy',7,'','','',''
--exec [dbo].[Product_FindByParameters] 'Heavy','' ,'','','' ,''

First 2 exec statement gives many data row as result,

But why the last donot give any row ;( ;(

how can i rewrite the stored procedure, such that it gives out put even if i don't supply ManufactureID as input\

kindly help me

Hi There

You miss out 'OR CASE' for 'ItemsInStock'

sujithukvl@.gmail.com:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================

-- =============================================
ALTER PROCEDURE [dbo].[Product_FindByParameters]
(
@.Name Varchar(255),
@.ManufactureID bigint,
@.ShortDescription Varchar(255),
@.ManufactureProductID Varchar(255),
@.ItemsInStock bigint,
@.StorePartNumber Varchar(255)

)


AS
BEGIN

SELECT P.ProductId,
P.StorePartNumber,
P.ShortDescription,
P.ManufactureProductID,
P.Name,
P.Price,
P.ItemsInStock,
M.ManufactureName
FROM Product P left join Manufacture M
ON P.ManufactureID=M.ManufactureID
WHERE
( P.Name like '%' + @.Name + '%' OR @.Name is null)
AND (P.ShortDescription LIKE '%' + @.ShortDescription + '%' OR @.ShortDescription is null)
AND( P.ManufactureProductID LIKE '%' + @.ManufactureProductID + '%' OR @.ManufactureProductID is null)
AND (P.ItemsInStock=@.ItemsInStock OR@.ItemsInStock is null)
AND (P.ManufactureID = @.ManufactureID OR @.ManufactureID is null)
END

--exec [dbo].[Product_FindByParameters] 'Heavy-Duty ',7,'Compact Size','DC727KA' ,0,''
--exec [dbo].[Product_FindByParameters] 'Heavy',7,'','','',''
--exec [dbo].[Product_FindByParameters] 'Heavy','' ,'','','' ,''

|||

In your input parameter, give the default NULL value to ManufactureID. With that, you can do OR @.ManufactureID = NULL so that you can ignore it when the value doesn't provide.

@.ManufactureID bigint = NULL,

Execute procedure

Hello,

I have an SQL procedure as follows:

...

INSERT dbo.Levels (LevelName)
VALUES (@.LevelName)
...

LevelName is an input parameter of nvarchar type.

What should be the best way to execute this procedure from my C# / VB.Net code? And what would it return?

Thanks,

Miguel

Create a SqlConnection to the database.

Use the SqlConnection as a parameter to create a SqlCommand.

Set the command's CommandType to StoredProcedure.

Add a parameter called "@.LevelName" to the command with the value you want to insert.

Call ExecuteNonQuery on the command.

Clean up.

(The indicated SQL won't return any value.)

|||

I always use something like this (or a variation of, like ExecuteSPReturnDataTable, DataSet, etc depeding on the type of proc)

In a data access layer

1public long ExecuteStoredProcedure(string ProcedureName, Object[] Parameters)2 {3long result =new long();4 SqlCommand command;56try7 {8 Logon();910 command =new SqlCommand(ProcedureName, _conn);11 command.CommandType = CommandType.StoredProcedure;1213if (Parameters !=null){14foreach (SqlParameter paramin Parameters)15 {16 command.Parameters.Add(param);17 }18 }19 result = command.ExecuteNonQuery();2021 }22catch (Exception ex)23 {24 result = 0;25 }26finally27 {28try29 {30 Logoff();31 }32catch (Exception ex2)33 {34 }35 }3637return result;38 }

|||

Hi,

Could you please tell me what are the Logon and Logoff functions?

Could you provide me an example?

Anyway, I created a new code but it works not only with SQL database but also with other databases.

I didn't test it yet but here it is:

1' ExecuteStoredProcedure2Public Shared Function ExecuteStoredProcedure(ByVal procedureNameAs String,ByVal ParametersAs Object())As Long34' Create output5Dim outputAs New Long67' Define the connection string8Dim connectionStringAs ConnectionStringSettings = ConfigurationManager.ConnectionStrings("ConnStr")910' Construct an ADO.NET provider factory11Dim dbProviderAs DbProviderFactory = DbProviderFactories.GetFactory(connectionString.ProviderName)1213' Create and define the connection14Dim connectionAs DbConnection = dbProvider.CreateConnection()15 connection.ConnectionString = connectionString.ConnectionString1617' Run command18Try1920' Open the connection21 connection.Open()2223' Create command24Dim commandAs DbCommand = dbProvider.CreateCommand()2526' Define command properties27With command28 .CommandText = procedureName29 .Connection = connection30 .CommandType = CommandType.StoredProcedure31End With3233' Add command parameters34If Not (ParametersIs Nothing)Then35 For Each parameterAs ParameterIn Parameters36 command.Parameters.Add(parameter)37Next38 End If3940' Execute command41 output = command.ExecuteNonQuery4243Catch exAs Exception4445' Define output as 046 output = 04748Finally4950' Close the connection51 connection.Close()5253End Try5455' Return output56Return output5758End Function' ExecuteStoredProcedure

What do you think?

Cheers,

Miguel

|||

My preference would be to use the Enterprise Library 2.0 Data Access Application block.

In it there are procedures for ExecuteStoredProcedure.

It is the neatest way since you not worried about the Data Access part and concentrate on the business intricacies of the project.

Let me know your thoughts.

|||

Hello,

This seems interesting.

Does it work also with Microsoft Access databases and MySQL or only with MS SQL and Oracle?

Thanks,

Miguel

|||

Hello,

Any idea how to run a stored procedure with Enterprise Library 2.0?

Any information on this anywhere?

Thanks,

Miguel

|||

Quoting from the help of the Enterprise Library

"

The application block supplements the code in ADO.NET 2.0 that allows you to use the same code with different database types. It includes classes for SQL Server and Oracle databases. These classes contain code that provides database-specific implementations for features such as parameter handling and cursors. In addition, theGenericDatabase class allows you to use the application block with any configured ADO.NET 2.0DbProviderFactoryobject. You can extend the application block by adding new database types that include database-specific features or that provide a custom implementation of an existing database. The only requirement is that an ADO.NET 2.0DbProviderFactory class exists for the target database.

"

Therefore you have to write code for MS Access and MySQL in the block to extend it.I saw the code for the block. It had classes for SQL server and Oracle only.

Execute Package Task: setting child variables

Hi,
Let's say I have a package taking as parameter "InvoiceID". I want to execute this package as a child in another package. The parent package gets the list of invoices to produce and calls the child package for each entry of the list.

How do I pass the InvoiceID to the child? I know I can use the parent's variables from the child but I don't want the child package to be dependant on the parent package. For example, I might want to execute the "child" package as a stand-alone in development (providing it with a predefined InvoiceID). I might also want to call the same child package from another parent package with other variable names.

What I would like to do is "push" the value instead of "pulling" it. I know it's possible using the command line and the /SET option (ex.: /SET \Package.Variables[InvoiceID].Value;' 184084)... Is it possible using the Execute Package Task?

Thanks

fleo wrote:

Hi,
Let's say I have a package taking as parameter "InvoiceID". I want to execute this package as a child in another package. The parent package gets the list of invoices to produce and calls the child package for each entry of the list.

How do I pass the InvoiceID to the child? I know I can use the parent's variables from the child but I don't want the child package to be dependant on the parent package. For example, I might want to execute the "child" package as a stand-alone in development (providing it with a predefined InvoiceID). I might also want to call the same child package from another parent package with other variable names.

What I would like to do is "push" the value instead of "pulling" it. I know it's possible using the command line and the /SET option (ex.: /SET \Package.Variables[InvoiceID].Value;' 184084)... Is it possible using the Execute Package Task?

Thanks

No, it's not possible using the Execute Package Task. However, using Parent Package Configurations, you can hook the parent variable to a child variable.|||

fleo wrote:

What I would like to do is "push" the value instead of "pulling" it. I know it's possible using the command line and the /SET option (ex.: /SET \Package.Variables[InvoiceID].Value;' 184084)... Is it possible using the Execute Package Task?

You could use execute process task to run the child packages via dtexec and using the /SET option for pushing the parameter. The only problem I see with that is that the master package will not know when the package finish as opposed to Execute package task.

Just an idea you do the test.

|||

We have a "common" stored proc to run SSIS packages (dtexec is called by that sp).
It can be used in a Execute SQL task and we are able to determine when the execution is done and wheter the package was executed successfully. But building the command and retrieving the result is quite a hassle...

The parent/child package configuration will do the trick. The only thing I dislike is the parent being dependent upon the child (maybe I'm from another school of thought).

Thanks all for your input.

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