Executing SSIS from stored procedure - need to get return value into ASP.NET

I'm executing an SSIS package using the following stored procedure

ALTER PROC [dbo].[SSISRunBuildSCCDW]ASBEGINDECLARE @.ServerNameVARCHAR(30), @.ReturnValueint, @.Cmdvarchar(1000)SET @.ReturnValue = -1SET @.ServerName ='myserver'SET @.Cmd ='DTExec /SER ' + @.ServerName +' ' +' /SQL ' +'\BuildSCCDW '--Location of the package stored in the mdb --' /CONF "\\ConfigFilePath.dtsConfig" ' +--' /SET \Package.Variables[ImportUserID].Value; ' +--' /U "LoginName" /P "password" 'EXECUTE @.ReturnValue = master..xp_cmdshell @.Cmd, NO_OUTPUTRETURN @.ReturnValue--SELECT @.ReturnValue [Result]END

I'm then using a tableadapter to execute this from my ASP.NET page using the following code,

Protected Sub ExecutePackage()Dim ExecuteAdapterAs New SCC_DAL.RunSSISTableAdapters.SSISRunBuildSCCDWTableAdapter() ExecuteAdapter.SetCommandTimeOut(0)Dim strResultAs String strResult = ExecuteAdapter.Execute() lblResult.Text = strResultEnd Sub

If I remove 'NO_OUTPUT' from my stored procedure and run it the results contain a field named 'output' with all the steps from my package. Then below this is my return value. In my code I can only return the first step of the package results - which tells me nothing useful. I need to be able to return the return value (0-6) in my code.

When I have 'NO_OUTPUT' in my stored procedure and execute it I am left with just the return value. However no value is returned in my code at all although the package does run. I've tried bother RETURN @.ReturnValue and SELECT @.ReturnValue to no avail.

Can someone suggest how I can get the value 0-6 to my code?

Sorted!

Commented out RETURN @.ReturnValue and uncommented the line below it and it works. I had already tried that... bizarre.

Executin SQL statement from variable in functions

Hello All

Within my function depends on value forming a SQL statement

As Example

set @.SqlString = 'select ' + @.ColumnName + ' from ' + @.tableName + ' where ' + @.whereCond

How can i execute this Statement. within function

Thanks in advance

RK

> Within my function depends on value forming a SQL statement

Sorry, no can do, you cannot execute dynamic SQL in a function. Maybe you

meant to use a stored procedure, not a function.

|||

I can change function to stored procedure within storedprocedure how can i execute a dynamic SQL

My requirement is to open cursor with dynamic SQL.
table name, columns list and conditions are depends on user parameters

|||

> My requirement is to open cursor with dynamic SQL.

You need a cursor and dynamic SQL. Fantastic. First, please read this:

http://www.smmarskog.se/dynamic_sql.html

Then, if you can give better specs, maybe we can help.

http://www.aspfaq.com/5006

|||looks like the site was down (http://www.smmarskog.se/dynamic_sql.html)

is there a way i can a open cursor with dynamic sql.

Our requirement is we have set of related tables with different data but all integer columns

We are trying to create few statistical functions/procs So user can pass Table name and column name and optionally any conditions, so that my function/procs returns results.

|||

Sorry, typo

http://www.sommarskog.se/dynamic_sql.html

> looks like the site was down

ExecuteScalar() Not Returning Value?

Okay so here's a wierd one. I use SQLYog to peek into/administrate my databases.

I noticed that this chunk of code is not producing a value...

Using ConnAs New MySqlConnection(Settings.MySqlConnectionString)Using CmdAs New MySqlCommand("SELECT COUNT(*) FROM tbladminpermissions WHERE (PermissionFiles LIKE'%?CurrentPage%') AND Enabled=1", Conn)With Cmd.Parameters.Add(New MySqlParameter("?CurrentPage",thisPage))End WithConn.Open()Exists = Cmd.ExecuteScalar()End UsingEnd Using

Exists is declared outside of that block so that other logic can access it. thisPage is a variable declared outside, as well, that contains a simple string, like 'index.aspx'. With the value set to 'index.aspx' a count of 1 should be returned, and is returned in SQLYog.

SELECTCOUNT(*)FROM tbladminpermissionsWHERE (PermissionFilesLIKE'%index.aspx%')AND Enabled=1

This produces a value of 1, but NO value at all is returned from Cmd.ExecuteScalar(). I use this method in MANY places and don't have this problem, but here it rises out of the mist and I can't figure it out. I have no Try/Catch blocks so any error should be evident in the yellow/red error screen, but no errors occur in the server logs on in the application itself.

Does anybody have any ideas?

Try

WHERE (PermissionFiles LIKE'%' + ?CurrentPage + '%')

Jos

|||

That didn't give me the desired result, either. It started returning "every" row that met all criteria but theCurrentPage.

But, thanks to your suggestion, what I ended up with was...

Using ConnAs New MySqlConnection(Settings.MySqlConnectionString)Using CmdAs New MySqlCommand("SELECT COUNT(*) FROM tbladminpermissions WHERE (PermissionFiles LIKE'%" & thisPage & "%') AND Enabled=1 AND Everybody=0", Conn)Conn.Open()Exists = Cmd.ExecuteScalar()End UsingEnd Using

Which works "as intended". Thanks!|||

execute scalar returns firts column from firts row of returned data so use this:

SELECT (SELECTCOUNT(*)FROM tbladminpermissionsWHERE (PermissionFilesLIKE'%index.aspx%')AND Enabled=1)

you can also use:

ifexists(SELECT *FROM tbladminpermissionsWHERE (PermissionFilesLIKE'%index.aspx%')AND Enabled=1)

select 1

else

select 0

which can work faster if you have more than one record whcih meet your criteria

I hope that it will work

sql

Execute stored procedure for each value returned in a query

I have a stored procedure that I am trying to execute passing it each value
returned in a query, but I cannot figure how to do it. I am trying to use
sp_executesql. Here is an example
Query:
(select distinct cust from customer_tbl)
Results:
Cust
--
Cust1
Cust2
Cust3
Cust4
Problem:
I need to execute a stored procedure for each Customer returned passing it
the Cust value. (SP_getsales @.cust='Cust1').
I cannot figure out how to do this. I can generate a script with
sp_executesql, but I cannot simply run the output directly back into isql or
sqlcmd as input. Perhaps I need to rewrite my procedure, but I cannot figur
e
it out.
Thanks,
--
JasonJasonDWilson wrote:
> I have a stored procedure that I am trying to execute passing it each valu
e
> returned in a query, but I cannot figure how to do it. I am trying to use
> sp_executesql. Here is an example
> Query:
> (select distinct cust from customer_tbl)
> Results:
> Cust
> --
> Cust1
> Cust2
> Cust3
> Cust4
> Problem:
> I need to execute a stored procedure for each Customer returned passing
it
> the Cust value. (SP_getsales @.cust='Cust1').
> I cannot figure out how to do this. I can generate a script with
> sp_executesql, but I cannot simply run the output directly back into isql
or
> sqlcmd as input. Perhaps I need to rewrite my procedure, but I cannot fig
ure
> it out.
>
If rewriting the procedure is an option and if the procedure just
executes some data manipulation code based on a parameter then just
substitute that parameter with a JOIN or IN operation (join to
customer_tbl in other words).
If you need help, please post DDL, sample data, required results.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I would ask you to take a note before you read this example - try just
about ANY other option before using these, but in this case, it looks
like you would want a cursor:
DECLARE results cursor
FOR SELECT DISTINCT CUST FROM Customer_tbl
OPEN results
DECLARE @.CurrRow varchar(50)
FETCH NEXT FROM Results INTO @.CurrRow
WHILE @.@.FETCH_STATUS = 0
BEGIN
exec sp_getsales @.cust = @.CurrRow
FETCH NEXT FROM Results INTO @.CurrRow
END
close results
DEALLOCATE Results
Cheers
Will|||Use a cursor...
declare blah cursor for
select distinct cust
from customer_tbl
declare @.cust varchar(50)
open blah
fetch next from blah into @.cust
while @.@.fetch_status = 0
begin
exec sp_getsales @.cust = @.cust
fetch next from blah into @.cust
end
deallocate blah
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"JasonDWilson" <JasonDWilson@.discussions.microsoft.com> wrote in message
news:45C58B26-57BB-4C6B-B0D1-D73473A970C1@.microsoft.com...
>I have a stored procedure that I am trying to execute passing it each value
> returned in a query, but I cannot figure how to do it. I am trying to use
> sp_executesql. Here is an example
> Query:
> (select distinct cust from customer_tbl)
> Results:
> Cust
> --
> Cust1
> Cust2
> Cust3
> Cust4
> Problem:
> I need to execute a stored procedure for each Customer returned passing
> it
> the Cust value. (SP_getsales @.cust='Cust1').
> I cannot figure out how to do this. I can generate a script with
> sp_executesql, but I cannot simply run the output directly back into isql
> or
> sqlcmd as input. Perhaps I need to rewrite my procedure, but I cannot
> figure
> it out.
> Thanks,
> --
> Jason|||try this
xp_execresultset 'select distinct ''SP_getsales @.cust= '' + cast(Cust1 as
varchar(20)) from customer_tbl'
hope this helps
--
"JasonDWilson" wrote:

> I have a stored procedure that I am trying to execute passing it each valu
e
> returned in a query, but I cannot figure how to do it. I am trying to use
> sp_executesql. Here is an example
> Query:
> (select distinct cust from customer_tbl)
> Results:
> Cust
> --
> Cust1
> Cust2
> Cust3
> Cust4
> Problem:
> I need to execute a stored procedure for each Customer returned passing
it
> the Cust value. (SP_getsales @.cust='Cust1').
> I cannot figure out how to do this. I can generate a script with
> sp_executesql, but I cannot simply run the output directly back into isql
or
> sqlcmd as input. Perhaps I need to rewrite my procedure, but I cannot fig
ure
> it out.
> Thanks,
> --
> Jason|||also, don't prefix your stored procedures sp_
read books online for reasons not to do this.|||Is this 2005 only?
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:6FBD5D4E-B4E2-4ED3-AB4E-FCEC9406990B@.microsoft.com...
> try this
> xp_execresultset 'select distinct ''SP_getsales @.cust= '' + cast(Cust1 as
> varchar(20)) from customer_tbl'
> hope this helps
> --
>
>
> "JasonDWilson" wrote:
>
value
use
passing it
isql or
figure|||small change :)
exec master..xp_execresultset 'select distinct ''SP_getsales @.cust= '' +
cast(Cust1 as varchar(20)) from customer_tbl', <database_name>
database name should be in single quotes
hope this helps
--
"Omnibuzz" wrote:
> try this
> xp_execresultset 'select distinct ''SP_getsales @.cust= '' + cast(Cust1 as
> varchar(20)) from customer_tbl'
> hope this helps
> --
>
>
> "JasonDWilson" wrote:
>|||no its there in 2000. But it doesn't appear in the master database. its
undocumented and hidden :)
And after SP4, its no longer an extended stored proc, it just calls
sp_execresultset
--
"Jim Underwood" wrote:

> Is this 2005 only?
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:6FBD5D4E-B4E2-4ED3-AB4E-FCEC9406990B@.microsoft.com...
> value
> use
> passing it
> isql or
> figure
>
>|||One note of caution. Don't use this proc unless you definitely have to. Cos
its undocumented. Its a definite no-no if you are planning to deploy it in
production. Typical use is for granting permissions to a set of users.
I would say its still better to use cursors if no one else comes with a
better solution.
But my suggestion would be to incorporate the looping logic inside the
stored procedure.
Hope this helps.
--
"Omnibuzz" wrote:
> small change :)
>
> exec master..xp_execresultset 'select distinct ''SP_getsales @.cust= '' +
> cast(Cust1 as varchar(20)) from customer_tbl', <database_name>
> database name should be in single quotes
> hope this helps
> --
>
>
> "Omnibuzz" wrote:
>

execute stored procedure (with parameters) with an "exec" command

Hi everybody, I would like to know if it's possible to execute a stored procedure, passing it parameters, using not CommandType.StoredProcedure value of sqlcommand, but CommandType.Text.

I tried to use this:
sqlCmd.CommandType = CommandType.Text
sqlCmd.Parameters.Add(sqlPar)
sqlCmd.ExecuteNonQuery()

With this sql command:
"exec sp ..."

I wasn't able to make it to work, and I don't know if it's possible.

Another question:
if it's not possible, how can I pass a Null value to stored procedure?
This code:
sqlPar = new SqlParameter("@.id", SqlDbType.Int)
sqlPar.Direction = ParameterDirection.Output
cmd.Parameters.Add(sqlPar)

sqlPar = new SqlParameter("@.parent_id", DBNull)
cmd.Parameters.Add(sqlPar)

doesn't work, 'cause I get this error:
BC30684: 'DBNull' is a type and cannot be used as an expression.

How can I solve this?
Bye and thanks in advance.

P.S. I would prefer first method to call a stored procedure ('cause I could call it with 'exec sp null' sql command, solving the other problem), but obviusly if it's possible...=)

Sorry for grammatical mistakes.It's DBNull.Value|||Try:

sqlPar = new SqlParameter("@.parent_id", DBNull.Value)|||Perfect, it works, but now I have another problem.
I get this error:
This SqlTransaction has completed; it is no longer usable.

When I run Commit or Rollback code.|||Are you committing the transaction twice accidentally?|||No, I'm sure.
The only "strange" thing I do it's to call 2 different functions in my class to begin and commit/rollback transaction.|||Then you will have to show some code...|||'CConn is my class
CConn.BeginTransaction()

Dim arParameters as new ArrayList()

Dim sqlParOutput = new SqlParameter("@.id", SqlDbType.Int)
sqlParOutput.Direction = ParameterDirection.Output
arParameters.Add(sqlParOutput)

Dim sqlPar = new SqlParameter("@.parent_id", DBNull.Value)
arParameters.Add(sqlPar)

objTransaction = CConn.ExecuteNonQuery("sp", arParameters, false)

if objTransaction is nothing then objTransaction = CConn.ExecuteNonQuery("...")

if objTransaction is nothing then
CConn.CommitTransaction()
else
CConn.RollbackTransaction()
end if

The Begin, Commit and Rollback functions in CConn class simply call the same functions of SqlConnection object.
The ExecuteNonQuery function override standard function.|||This helps sort of not at all. We do not know what your class is doing. Is the return from CConn.ExecuteNonQuery a transaction object? What is the significance of objTransaction being nothing?|||Sorry, you're right.
Here the class methods:

 Public function BeginTransaction() as SqlTransaction
 Me.Conn = New SqlConnection(Me.sConnStr)
 Me.Conn.Open()
 'Start a local transaction
 Me.myTransaction = Conn.BeginTransaction()
 end function
 Public sub CommitTransaction()
 Me.myTransaction.Commit()
 Me.Conn.Close()
 end sub
 Public function RollbackTransaction() 'transaction as SqlTransaction)
 Me.myTransaction.Rollback()
 Me.Conn.Close()
 end function
 Public function ExecuteNonQuery(ByVal SQL As String, optional sqlParameters as ArrayList = nothing, optional toClose as boolean = true) as object
 Dim objReturn as object
 if Me.Conn is nothing then
 Me.Conn = New SqlConnection(Me.sConnStr)
 Me.Conn.Open()
 else
 if Me.Conn.State <> ConnectionState.Open then Me.Conn.Open()
 end if
 Dim sqlCmd As New SqlCommand(SQL, Me.Conn)
 'Must assign transaction object to Command object for a pending local transaction
 if not Me.myTransaction is nothing then sqlCmd.Transaction = Me.myTransaction
 Try
 if not sqlParameters is nothing then
 sqlCmd.CommandType = CommandType.StoredProcedure
 Dim sqlPar as SqlParameter
 for each sqlPar in sqlParameters
 sqlCmd.Parameters.Add(sqlPar)
 next
 else
 sqlCmd.CommandType = CommandType.Text
 end if
 sqlCmd.ExecuteNonQuery()
 Catch e As Exception
 objReturn = e
 End Try
 'must be clean up?
 if toClose then
 Me.Conn.Close()
 sqlCmd.Dispose()
 Me.Conn.Dispose()
 end if
 return objReturn
 End function

Execute Stored Proc and then return a value

ok I have a stored procedure in my MS-SQL Server database.
It looks something like this....

CREATE PROCEDURE updatePCPartsList
(
@.Description varchar(255),
@.ManCode varchar(255),
@.ProdCode varchar(255),
@.Price decimal(6,2),
@.Comments varchar(255)
)
AS

declare @.IDFound bigint
declare @.LastChangedDate datetime

select @.LastChangedDate = GetDate()
select @.IDFound = PK_ID from PCPartsList where ProdCode = @.ProdCode

if @.IDFound > 0
begin
update PCPartsList set Description = @.Description, ManCode = @.ManCode, ProdCode = @.ProdCode, Price = @.Price, Comments = @.Comments, LastChanged = @.LastChangedDate where PK_ID = @.IDFound
end
else
insert into PCPartsList (Description, ManCode, ProdCode, Price, Comments, LastChanged) values(@.Description, @.ManCode, @.ProdCode, @.Price, @.Comments, @.LastChangedDate)
GO

It executes fine so I know i've done that much right...
But what i'd like to know is how I can then return a value - specifically @.LastDateChanged variable

I think this is a case of i've done the hard part but i'm stuck on the simple part - but i'm very slowly dragging my way through learning SQL.
Someone help?You can add an extra parameter to the call, which you can use as an output variable.

Try the following:

CREATE PROCEDURE updatePCPartsList
(
@.Description varchar(255),
@.ManCode varchar(255),
@.ProdCode varchar(255),
@.Price decimal(6,2),
@.Comments varchar(255),
@.LastChangedDate datetime output
)
AS
declare @.IDFound bigint
select @.LastChangedDate = GetDate()
select @.IDFound = PK_ID from PCPartsList where ProdCode = @.ProdCode
if @.IDFound > 0
begin
update PCPartsList set Description = @.Description, ManCode = @.ManCode, ProdCode = @.ProdCode, Price = @.Price, Comments = @.Comments, LastChanged = @.LastChangedDate where PK_ID = @.IDFound
end
else
insert into PCPartsList (Description, ManCode, ProdCode, Price, Comments, LastChanged) values(@.Description, @.ManCode, @.ProdCode, @.Price, @.Comments, @.LastChangedDate)
GO

When you call the proc you'll need to capture the output to a variable of your choice so do something like:

DECLARE @.LCDate datetime
EXEC updatePCPartsList
@.Description='Dummy Item',
@.ManCode='12121',
@.ProdCode='54321',
@.Price=123,
@.Comments='blah blah',
@.LastChangedDate=@.LCDate OUTPUT

then @.LCDate should contain the result.|||Ok - I kinda see what your doing but i'm not sure if it's what i'm looking for....

First let me explain my situation a bit more....
I'm writing an application (specifically an excel spreadsheet using vba) in which I need to execuite that stored procedure and then return the @.LastChangedDate value to a variable in my VBA.

Can I use your example still?
Would your additional bit of SQL be a second Stored Proc? And would I call that - not my first proc?|||Ah, I assumed you were wanting the result in a SQL local variable, not passed to Excel. I doubt you'd be able to use that modification for your needs

Sadly, I'm no VBA wizard but I'd assume you need to alter your original stored proc to have a "SELECT @.LastChangedDate" at the end so it prints the output you want into the resultset.

Assuming you've used the SQLExecuteQuery command to run the query you should be able to use SQLRetrieve to get the resultset and extract the value from there.

SQLRetrieve allows you to specify an Excel cell range to put the results in and a max rows/cols value (which you'd have set to 1).

The VBA help in Excel has a nice reference for SQLExecuteQuery and SQLRetrieve, with a few examples that may help.

Execute SQL Task Error: no result rowset associated...

I am getting the following error when I execute my sql task:

An error occurred while assigning a value to variable "NullVar": " NO result rowset is associated with the execution of this query. "

I am executing a SP that has one input & one output parameter. The output parameter is returning a single row for debugging if the sp failes.
I tried using Jamie's method:(http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx) to get it to work but keep getting the above error. I have the following variables:

sqlSource (string) := Exec RBCprcsInsertWmsInvTransactionRecords '" + (DT_WSTR,10 ) @.[User::SnapShotDate] + "', NULL"
NullVar (string)

In the execute sql task, I set the ResultSet to single row. I set SQLSourceType = variable & sourcevariable = user::SqlSource. In the result tab, I added a result set, NewResultName with the variable user::NullVar. I tried different configurations with the parameter mappings but nothing seemed to work. I didn't know if i still had to use this if I am using the sqlSource variable to drive the task.

So I am not sure what I am missing here. Anyone have any suggestions?

Thanks!
John

I would try to catch the SP output via parameter mapping in the execute sql task.

For that, you have to change ResultSet=None; delete the result set from result set tab; and add 2 entries in the parameter mapping page; these 2 entries have to be in the same order you are using the parameters in the Exec t-sql command. If you are using OLE DB connection; the parameter name in the parameter mapping page should be 0 and 1.

The SQLSourceType should be DirectInput and the SQLStatment: EXEC RBCprcsInsertWmsInvTransactionRecords @.yourInputParameterName = ?, @.YourOutputParameterName = ? OUTPUT

|||

Rafael,

I was missing how to correctly specify the parameters. Thanks for giving me the correct syntax! I had tried something similar to this before using the variable to pass the sql statement but didn't have it quite right.

Can i add one more question onto this? Do I have to turn on logging inorder to capture this output variable to a log file?

Thanks again!
John

|||

laker_42 wrote:

Can i add one more question onto this? Do I have to turn on logging inorder to capture this output variable to a log file?

Package logging would give you logging information about package execution; I don’t think is the way to go for getting the value of a SSIS variable written into a file. I cannot think on a quick way to do so; but perhaps you could use a script task.

|||Ok, i will do some more digging on that. Thanks for your help!

John

Execute SQL Task Error

I have a stored procedure that accepts an integer as
input and sends a value out as type varchar(600).

When I use Execute SQL task with ADO.Net or ADO Connection
Manager and assign proper values for the parameters and
their direction, the execution of the task returns ONLY 1
character (first character)for the output parameter.

Sounds like a bug since I can't set the string length
anywhere in the data type.

Any help/work arounds would be greatly appreciated it.

ThanksThis is a shot in the dark, but maybe a UCS-2 string (Microsoft's favorite type of Unicode encoding) is being used, and being interpreted as one of the Windows proprietary 8-bit encodings (eg, windows-1252), and the first character is in the ASCII set (eg, a regular letter), so the second byte of its UCS-2 representation is a zero byte, and this is truncating the string.
If you don't follow that, perhaps try changing the return value to nvarchar(600).
Note however, that we're successfully passing back varchar values via Execute SQL
tasks.
|||N.S. Which build you were on?
Another possibility is if you were on an earlier build, we had a known issue using ADO.Net connection in ExecuteSQLTask to get the full string value from out parameters. If that's the case for you, try upgrade to our CTP16 release and you should see the fix.

Thanks|||

I am on Version 8.0.50215.44. I'll upgrade to CTP 16. Thanks.

Execute SQL task editor problem

hi !

I have problem here with sql task editor. i have a stored proceedure with 1 input value and 1 output value which needs to be updated in a variable.

stored proc - exec GetRDate ? , ? output

input value is set in a varaible.

I tried giving 1 input and 1 output in the parameter box and result set as fullresultset 1 value for variable -

" failed with the following error: "No disconnected record set is available for the specified SQL statement.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Task failed: Get Retention Date

what should i give in the parameter and result set

Thanks,

Jasmine

Check out "The ExecuteSQL Task" explained @. http://www.sqlis.com/default.aspx?58
Lots of different examples are discussed.

Also check out the sections 'Using Parameters with Stored Procedures'', 'Getting Values of Return Codes', 'Specifying a Result Set Type', 'Populating a Variable with a Result Set' @. http://msdn2.microsoft.com/en-us/library/ms141003.aspx

Thanks,
Loonysan

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 TaSk

I am trying to run a Direct Input SQL query to SELECT MAX value of ColA. This query when I run on Query Window runs fine - means it is NOT a NULL. I get a max value.

When I run this query on a SSIS package outputing to a variable - I get the error. -

[Execute SQL Task] Error: An error occurred while assigning a value to variable "MAXROWKEYID": "The type of the value being assigned to variable "User::MAXROWKEYID" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. ".

I need this output to be used for the subsequent steps which follows this.

I am using Value Type as Double. This was the default when I migrated this DTS package from SQL 2000. I use the proper Single Row for the Result Set and for outputing the Result Set, I use the correct Variable Name.

Help will be greatly appreciated...

Thanks.

SAM.

Obvious first question, what is the type of ColA?

I had some trouble with populating Double variabls recently, I couldn't load it from a field of type decimal. I had to cast the value as a float first.

-Jamie

|||

ColA is my ROWKEYID - it is bigint datatype.

-Sam

|||

Try making the variable type "Int32" or "Int64".

-Jamie

|||

Kept the value type as Double and when I tried to make this as

SELECT CAST(MAX(ROWKEYID) as FLOAT) from Table on SSIS package, it runs fine, successfully, but the value does not change. I mean, when I run on this Query window -the value is different.

But when I try -

SELECT Max(ROWKEYID) FROM Table and making the variable type as Int32 or Int64, the step fails.

-Sam

|||After debugging the task, I found that the value returned by the MAX function on a bigint column is of type String. Thats why your task failed when you used variables of type Int32, Int64 or Double. I see the same behavior when MAX function is called on a decimal column. However, MAX function on a int column returns Int32 type and float column returns a Double type.

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 Scalar ?

I'm trying to do something like the code below, but it's saying "specified cast is not valid"

If i change the value returned to an "int", it works fine. My issue is, i'd like to get the value returned with more accuracy than an int as there will be 2 decimal places.

protectedfloat getProjectHours(string project)

{

string selectCmd ="SELECT SUM(hours) FROM tasks WHERE project=@.project";

string strConnection =ConfigurationManager.ConnectionStrings["TimeAccountingConnectionString"].ConnectionString;

SqlConnection myConnection =newSqlConnection(strConnection);

SqlCommand myCommand =newSqlCommand(selectCmd, myConnection);

myCommand.Parameters.Add(newSqlParameter("@.project",SqlDbType.VarChar));myCommand.Parameters["@.project"].Value = project;

myConnection.Open();

float total = (float)myCommand.ExecuteScalar();

myConnection.Close();

return total;

}

Could you post the sql function please?

|||

what do mean? it's all up there

|||

If this is the line you are getting an error on, then try this:

double total = Convert.ToDouble(myCommand.ExecuteScalar());

|||

worked perfect! Thanks!

|||

you are welcome...

|||

Oh, sorry. Need more coffee...

Execute same statement for each value in result set

Hi everyone,

My brain refuses to remember the (undocumented?) stored procedure I'm
thinking of. It takes at least two parameters: a sql statement to
execute, and a table name (or something of that nature).

Then, for each value in the table, it executes the sql statement and
passes the value as a parameter.

Can anybody refresh my memory? The functionality may be slighly
different than described, but the principle is the same. Thanks very
much...

-JoeNow that I think about it, rather than a table name, I believe one of
the parameters may be statement that returns a 1 column resultset, as
it would probably make more sense.|||xp_execresultset

More info here:
http://www.rac4sql.net/xp_execresultset.asp

The usual warnings apply about the use of undocumented features.

--
David Portas
SQL Server MVP
--|||Indeed... Thanks a lot...

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