How To Catch Output from SP / Function

I want to catch
the resultset from a stored procedure
or
a table output parameter from a stored procedure
or
a table output from a function
into
a varchar variable.
Any tip?
Not possible?
(it is for mailing the result from a query)
/k"kurt sune" <apa@.apa.com> wrote in message
news:uNdetTxGFHA.2616@.tk2msftngp13.phx.gbl...
>I want to catch
> the resultset from a stored procedure
> or
> a table output parameter from a stored procedure
You cannot have a table output parameter from a stored procedure

> or
> a table output from a function
> into
> a varchar variable.
> Any tip?
> Not possible?
Not Possible.
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"kurt sune" <apa@.apa.com> wrote in message
news:uNdetTxGFHA.2616@.tk2msftngp13.phx.gbl...
>I want to catch
> the resultset from a stored procedure
> or
> a table output parameter from a stored procedure
> or
> a table output from a function
> into
> a varchar variable.
> Any tip?
> Not possible?
>
> (it is for mailing the result from a query)
> /k
>|||You cannot do that.
You can catch the output from a SP into a #Table. Thats the max you can do.
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"kurt sune" <apa@.apa.com> wrote in message
news:uNdetTxGFHA.2616@.tk2msftngp13.phx.gbl...
> I want to catch
> the resultset from a stored procedure
> or
> a table output parameter from a stored procedure
> or
> a table output from a function
> into
> a varchar variable.
> Any tip?
> Not possible?
>
> (it is for mailing the result from a query)
> /k
>|||On Fri, 25 Feb 2005 09:35:12 +0100, kurt sune wrote:
(snip)
>Any tip?
>Not possible?
Hi Kurt,
Roji and Vinod already informed you that what you want is not possible.

>(it is for mailing the result from a query)
But if that's what you want, then you should check out xp_sendmail in
Books Online.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

How to capture out param?

I'd like to execute a stored procedure that needs 5 parameters. One of
them is an OUTPUT parameter. What does the
exec sp_name...
code, executed in QA SQL Server 2000, look like for this? Also, if the
OUTPUT parameter is declared last in the spoc, how can I call it by
name as the first parameter in my exec code?
Thanks,
BrettAssuming you have 5 parameters & the 5th one is an OUTPUT paramter, you can
call the procedure like:
EXEC usp @.p1, @.p2, @.p3, @.p4, @.p5 OUT
SELECT @.p5 ;
I know what you posted is just a sample, but in case hadn't noted, avoid
using sp_ prefix for stored procedures sicen they have certain unfavorable
implications.
Anith

How to callapse rows together

I'm need to collaspe a few rows together and output one row per group. The rows come from a flat file and are already sorted by orderid. Basically these are transactions with just the changed fields in them all unchanged fields are null (blank). They need to collapsed down to show the final state of all changed fields per order. Obviously the aggregate transform won't work in this situation since the goal is to collaspe instead of aggregate. In a language, I would normally buffer the previous row and apply the logic as needed until the orderid changed and then write out the new row. Then input file has millions of rows so I also didn't want to cursor through it updating individual rows in a table.

Example:

Orderid StatusCd OrderDt OrdrAmt

1 A 01/01/06 10

1 B

2 X 02/02/06 20

3 03/03/06 30

3 03/04/06

Collapse as

1 B 01/01/06 10

2 X 02/02/06 20

3 03/04/06

Danny,

The logic that you say you would apply in a programming language can be applied in exactly the same in SSIS by using a script component. I recommend you take a look at building an asynchronous script component.

-Jamie

How to call Oracle Stored Procedure which has an output parameter from SSIS?

I will really appreciate if someone can post step by step process to call an Oracle Stored Proc from SSIS. Here is the Stored Proc Spec:

PROCEDURE Interface_Begin

(p_from_dttmOUT varchar2,

p_error_codeOUT number,

p_error_textOUTvarchar2,

p_proc_nameOUT varchar2);

please check this

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1711335&SiteID=1

http://microsoftdw.blogspot.com/2005/11/parameterized-queries-against-oracle.html

-Nikul

|||

Could you please be more specific? I tried the following:

1) The stored proc spec is as follows:

Procedure testing(myDate OUT varchar2);

2) Created a Data Flow Task.

3) Created 2 variables called myStoredProc & myDate at the package level.

4) In the value for myDate variable i set it to myDate.

5) In the properties for myStoredProc variable i changed the EvaluateAsExpression property to True.

6) In the expression for myStoredProc variable i have the following:

"declare myDate varchar2(50); begin sa.testing(" + @.[User::myDate] + "); end;"

7) Now inside my Data Flow Task i have a Ole Db DataSource connection set to Native OLE DB\Microsoft Ole Db

Provider for Oracle.

8) Data Access Mode set to SQL Command from Variable.

9) The value of the Variable Name is User::myStoredProc.

10) Now when i hit preview i get the following error Message.

No disconnected record set is available for the specified SQL statement.

I am not sure what's wrong here. Could someone help me?

|||Anyone?|||Try doing this from a Execute SQL task, not a Source component. Source components expect a recordset, not an output parameter.|||

Hi there,

As Jwelch said use Execute SQL Task and select Single Row in Recordset option.

I think this will iron out your issue.

Please specify if this does not work.

Thanks

|||

Thanks guys. Now i am able to execute it successfully. But i am getting some junk characters in the Output parameters. Here is the stored proc definition:

create or replace procedure testing(myDate OUT varchar2)
IS
BEGIN
myDate := 'hey';
return;
END;

This should return 'hey' but i am getting this:


)

Any ideas?

How to call a stored procedure on MS Sqlserver from web forms and get back output

Hi,

How to call a stored procedure on MS Sqlserver from web forms and get back output

Thanx,

Nishu

Within the blue navigation bar at the top of this website, you will see a link to theASP.NET Tutorials. There you will find a large number of tutorials aboutPerforming Data Access.

While you are new to ASP.NET, you should find that most of your questions will be answered--with code samples--inside the Tutorials. Please keep them in mind.

How to build dynamic Xquery

Hello

I am trying to use the xml.query() method to output xml. Is there any way of storing the xqueries themselves in the database?

This works:

SELECT Col.query('
<Root>
<Header>
{
for $e in Report/PolicyBatchRef
return $e/PolicyBatchRef
}
</Header>
<NewElement>
{
for $e in Report/PolicyBatchRef/Locations/Location
return $e
}
</NewElement>
</Root>
')
FROM xmltest where id = 1

but this doesn't:

declare @.xquery nvarchar(max)
set @.xquery = '<Root>
<Header>
{
for $e in Report/PolicyBatchRef
return $e/PolicyBatchRef
}
</Header>
<NewElement>
{
for $e in Report/PolicyBatchRef/Locations/Location
return $e
}
</NewElement>
</Root>'
SELECT col.query(@.xquery) from xmltest where id = 1

I get the error

Msg 8172, Level 16, State 1, Line 4
The argument 1 of the xml data type method "query" must be a string literal.

Same thing happens when I store the xquery in the DB.

Any ideas?

Thanks very much

The string argument to the query function must be a string literal, so you cannot pass it in as a parameter. You have a few options:

1. Create UDF's that encapsulate the SELECT and the xquery, and invoke these.

2. Store strings that represent the SELECT and the xquery and invoke them at runtime using sp_executesql

3. store the strings that represent the xquery and combine them with the strings for the SELECT statement, and execute with sp_executesql. This has the most potential for SQL injection since you are constructing SQL dynamically with string concatenation. This method should be used only if the other two ways cannot be used.

|||

You can dynamically create XPath queries using sql variables.

I have successfully used something like

declare @.Date varchar(10)

set @.Date = replace(convert(varchar(10), getdate(), 121), '-', '')

WITH XMLNAMESPACES( 'https://www,somewhere.com/Bureau' AS "Bureau")

SELECT

AggDefaultAmount = convert(varchar(50), ResponseXML.query('sum(/BureauResponse/Bureau:ND07/Bureau:ND07/Bureau:Amount[../Bureau:InformationDate<sql:variable("@.Date")])'))

FROM

DB..testxml WITH (NOLOCK)

Adapt adopt and improve.

|||

Hi,

I need to a have a Store procedure that takes xml as input and it stores in my database tables. Using the nodes() method and value() method, I am able to solve this but only issue I have is these methods take arguments only as string literals. So, I have to hard code the xquery in the SP. I expect to read the xquery from a table and fetch it in a variable within the SP and pass it as the parameter to the Value() and nodes() method.

Please advice.

Sample code I have as below

declare @.xmldoc xml

SET @.xmldoc = '<customer><name>John</name><city>New York</city></customer>'

--This select works

SELECT

T.C.value('name[1]',varchar(50))

T.C.value('city[1]',varchar(50))

FROM @.xmldoc.nodes('/customer') AS T(C)

--But this does not work when I try to specify xquery using a variable as below

declare @.xquery_name varchar(100),@.xquery_city varchar(100), @.xquery_cust varchar(100)

SELECT @.xquery_name = 'name[1]', @.xquery_city = 'city[1]', @.xquery_cust = '/customer'

SELECT

T.C.value(@.xquery_name ,varchar(50))

T.C.value('@.xquery_city',varchar(50))

FROM @.xmldoc.nodes(@.xquery_cust) AS T(C)

Please help me out.