Re: Problem with Stored Procedure that conatins several select statements
- From: "Elias Goetz" <elias.goetz@xxxxxxxxxxxxxx>
- Date: Fri, 21 Sep 2007 13:01:34 +0200
I found the source of the problem. The select staments were run against a
linked server as I mentioned.
The Problem was that DTC was not enabled on the other machine. As soon as I
enabled it the schemas was generated properly.
The strange thing to me was that it worked when executing the sp manually
(Query Analzer) and that it created the correct schema information.
Regards,
Elias
"Elias Goetz" <elias.goetz@xxxxxxxxxxxxxx> schrieb im Newsbeitrag
news:eTzNGnB$HHA.3780@xxxxxxxxxxxxxxxxxxxxxxx
yes, the return values seem correct. the code in my last post were just
examples.
Here is the output from the stored proc (I just replaced the names of the
columns and values):
<Schema name="Schema17" xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:datatypes">
<ElementType name="ReturnedValues" content="empty" model="closed">
<AttributeType name="col1" dt:type="string" />
<AttributeType name="col2" dt:type="string" />
<attribute type="col1" />
<attribute type="col2" />
</ElementType>
</Schema>
<ReturnedValues xmlns="x-schema:#Schema17" col1="value1" col2="value2" />
It looks ok to me...
I also tried setting nocount on at the first two select statements and
then off at the last one, but it didn't make a difference.
Regards,
Elias
"Leonid Ganeline" <leo_gan_57@xxxxxxxxxxx> schrieb im Newsbeitrag
news:OKAz%23D7%23HHA.4732@xxxxxxxxxxxxxxxxxxxxxxx
If you test the stored procedures from the SQL Analyser, does it output
the schema as it should?
Seems it returns only one value.
--
Regards,
Leonid Ganeline [BizTalk MVP]
http://geekswithblogs.net/leonidganeline/
"Elias Goetz" <elias.goetz@xxxxxxxxxxxxxx> wrote in message
news:OKkbSN6%23HHA.4828@xxxxxxxxxxxxxxxxxxxxxxx
I created a stored procedure that gets a nvarchar value passed. With that
value I do two lookups against a database (on a linked server).
For example:
DECLARE @var1
SET @var 1 = (SELECT colX FROM SomeOtherTable WHERE colY = @passedValue)
At the end I insert the values (that I stored in temporary variables)
into a temporary table.
The select statement for the SQL Adapter looks like "SELECT col1, col2
TOP 1 FROM @temptable FOR XML AUTO, XMLDATA".
The Problem is that everytime I create a generated schema there is only
one element in the response node called "Success" and not the two
columns in the select statement. Now if I remove the queries I mentioned
before the final SELECT then the response elements are created as I
expected.
Why does the schema generator behave this way? Is it possible to use
multiple select statements in a stored procedure where only one is used
in combination with "XML AUTO ..."?
Regards
Elias
.
- Follow-Ups:
- Re: Problem with Stored Procedure that conatins several select statements
- From: Leonid Ganeline
- Re: Problem with Stored Procedure that conatins several select statements
- References:
- Problem with Stored Procedure that conatins several select statements
- From: Elias Goetz
- Re: Problem with Stored Procedure that conatins several select statements
- From: Leonid Ganeline
- Re: Problem with Stored Procedure that conatins several select statements
- From: Elias Goetz
- Problem with Stored Procedure that conatins several select statements
- Prev by Date: Connecting to Sharepoint Problem
- Next by Date: Re: FTP Receive Port Service Wnd Issue
- Previous by thread: Re: Problem with Stored Procedure that conatins several select statements
- Next by thread: Re: Problem with Stored Procedure that conatins several select statements
- Index(es):
Relevant Pages
|
