EXECuting sps within sps (using return params)

Hi all,
To optimise certain functionality in my app I want to do a few inserts after another by executing the whole lot in one procedure. I want to use the return param from some procedures (RETURN @.@.IDENTITY) as input for some of the other procedures.

I am getting errors when I compile the proc:
Line 10: Incorrect syntax near the keyword "EXEC"
Same error on Line 11...

CREATE PROCEDURE addTemplateDetail
@.TemplateID int,
@.GroupNameID int,
@.SubGroupNameID int=null,
@.MethodID int,
@.AnalyteID int
AS
DECLARE @.TemplateGroupNameID int
DECLARE @.TemplateMethodID int
SET @.TemplateGroupNameID=(EXEC addTemplateGroupName @.TemplateID, @.GroupNameID)
SET @.TemplateMethodID=(EXEC addTemplateMethod @.TemplateGroupNameID, @.SubGroupNameID, @.MethodID)
EXEC addTemplateAnalyte(@.TemplateMethodID,@.AnalyteID)

I also tried adding brackets around the input params for the EXECed sp's, but that generated even more errors...
Can somebody see what I am doing wrong?
TIA.Never mind.
I changed the two lines with return params to:

EXEC @.TemplateGroupNameID=addTemplateGroupName @.TemplateID, @.GroupNameID
EXEC@.TemplateMethodID=addTemplateMethod @.TemplateGroupNameID, @.SubGroupNameID, @.MethodID

Hope that will somebody else too...sql

ExecuteXMLReader fails with valid xml from sql server. Why?

(From a .net class using sqlcommand)
A stored proc that takes 2 input params is firing on the server (i've
checked using sql profiler). The sp in question produces valid xml (i've
checked by creating a sqlxml virutal dir and allowing url queries - the
result is valid in IE and therefore well formed xml)
The stored proc in question is not a single "select X for xml ..." query. It
is made up of fragments
example of results
<root>
<subnode>
select x ... for xml ...
</subnode>
<subnode>
select x ... for xml ...
</subnode>
...
</root>
Why won't ExecuteXmlReader work with the results?
XR = CMD.ExecuteXmlReader()
(where xr is an xml reader)
this line comes back with the error
"Invalid command sent to ExecuteXmlReader. The command must return an Xml
result."
The command is not invalid! I can see it executing on sql. The result is
valid xml.
I have also tried using the sqlxmlcommand but it claimed I was not passing
parameters in (which I was) so I gave up on that.
I am beginning to hate .net
The concept is great but there is too much pain
trying to connect things together...
Any ideas...?
CODE--
Dim StartDate As String =
ArgsDoc.SelectSingleNode("//Parameter[@.name='startDate']/@.value").Value
Dim EndDate As String =
ArgsDoc.SelectSingleNode("//Parameter[@.name='endDate']/@.value").Value
CMD = New SqlCommand
CMD.Connection = CN
Select Case SubType
Case Nothing
CMD.CommandText = "XXXXX"
CMD.CommandType = CommandType.StoredProcedure
Dim Param As New SqlParameter
CN.Open()
Try
Param.ParameterName = "@.start_date" : Param.Value =
StartDate : CMD.Parameters.Add(Param)
Param = New SqlParameter
Param.ParameterName = "@.end_date" : Param.Value =
EndDate : CMD.Parameters.Add(Param)
Catch ex As System.Exception
System.Diagnostics.Debug.WriteLine(ex.Message)
Finally
End Try
Case Else
'do something
End Select
'--
Try
XR = CMD.ExecuteXmlReader() ' * * * * * * LINE THAT FAILS * * *
* * * *
ResultsDoc = New XmlDocument
ResultsDoc.Load(XR)
Catch ex As System.Exception
System.Diagnostics.Debug.WriteLine(ex.Message)
Finally
End Try
END CODE--
In your description, you create a command like this:

> <root>
> <subnode>
> select x ... for xml ...
> </subnode>
> <subnode>
> select x ... for xml ...
> </subnode>
> ...
> </root>
Where did you do such construction? What type of variable or command are you
using?
One easy solution on Yukon is that you can try is store the result in a
"xml" type variable, and then select from it.
For the ExecuteXmlReader() expecting an "XML" typed data back, instead of
any string that looks like "XML".
Thanks.
Xin
"adolf garlic" wrote:

> (From a .net class using sqlcommand)
> A stored proc that takes 2 input params is firing on the server (i've
> checked using sql profiler). The sp in question produces valid xml (i've
> checked by creating a sqlxml virutal dir and allowing url queries - the
> result is valid in IE and therefore well formed xml)
> The stored proc in question is not a single "select X for xml ..." query. It
> is made up of fragments
> example of results
> <root>
> <subnode>
> select x ... for xml ...
> </subnode>
> <subnode>
> select x ... for xml ...
> </subnode>
> ...
> </root>
> Why won't ExecuteXmlReader work with the results?
> XR = CMD.ExecuteXmlReader()
> (where xr is an xml reader)
> this line comes back with the error
> "Invalid command sent to ExecuteXmlReader. The command must return an Xml
> result."
> The command is not invalid! I can see it executing on sql. The result is
> valid xml.
> I have also tried using the sqlxmlcommand but it claimed I was not passing
> parameters in (which I was) so I gave up on that.
> I am beginning to hate .net
> The concept is great but there is too much pain
> trying to connect things together...
> Any ideas...?
>
> CODE--
>
> Dim StartDate As String =
> ArgsDoc.SelectSingleNode("//Parameter[@.name='startDate']/@.value").Value
> Dim EndDate As String =
> ArgsDoc.SelectSingleNode("//Parameter[@.name='endDate']/@.value").Value
> CMD = New SqlCommand
> CMD.Connection = CN
> Select Case SubType
> Case Nothing
> CMD.CommandText = "XXXXX"
> CMD.CommandType = CommandType.StoredProcedure
> Dim Param As New SqlParameter
> CN.Open()
> Try
> Param.ParameterName = "@.start_date" : Param.Value =
> StartDate : CMD.Parameters.Add(Param)
> Param = New SqlParameter
> Param.ParameterName = "@.end_date" : Param.Value =
> EndDate : CMD.Parameters.Add(Param)
> Catch ex As System.Exception
> System.Diagnostics.Debug.WriteLine(ex.Message)
> Finally
> End Try
> Case Else
> 'do something
> End Select
> '--
> Try
> XR = CMD.ExecuteXmlReader() ' * * * * * * LINE THAT FAILS * * *
> * * * *
> ResultsDoc = New XmlDocument
> ResultsDoc.Load(XR)
> Catch ex As System.Exception
> System.Diagnostics.Debug.WriteLine(ex.Message)
> Finally
> End Try
>
> END CODE--

execute SQL task with in a For Each Loop

hi guys,

i'm trying to do a bulk insert through a t-sql which accepts 3 params.

i frist run a SQL statement to get a list of DB's to update through a SQL task, then i save the output to a variable object. which is been used by the For each loop.

i've done the mapping with the object fine. but i want to call a SQL task within for each loop, and parse the above results as parameters. i tried parsing it as

exec mysp3 @.[User::fundid], @.[User::dbCode], @.[User::subfund]

where fundiid, dbcode and subfund are user defined variables that gets updated by the ado recordset.

this fails with the error msg:

SSIS package "SP test.dtsx" starting.

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "exec ConnectODBC @.[User::fundid], @.[User::dbcode], @.[User::subfund] failed with the following error: "Incorrect syntax near 'User::fundid'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Task failed: Execute SQL Task

Warning: 0x80019002 at Shred the contents of the variable: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "SP test.dtsx" finished: Failure.

i also tried

exec mysp3 ?, ?, ?

then mapping the inputs still doesn't work

same thing with

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "exec ConnectODBC ?,?,? failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Task failed: Execute SQL Task

Look here:

http://www.sqlis.com/default.aspx?81

|||

Thanks Loom,

just what i wanted. i didn't know you have to interpret your expressions differently depending on the connection type. well now i do

by the way because of these different data conection types, my variable types are different. for example i save my fundid variable as int32. when using a OLE DB connection under parameter mapping there's no int, but numeric and it craps out when i try to do the mapping. is there a way to cast this? when passing to the t-sql?