Re: Problem with Stored Procedure that conatins several select statements

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



.

Relevant Pages

  • Re: SQL Best Practices Analyzer Rule: Use of Schema Qualified Tables/Views
    ... > How important is to specify the schame (dbo. ... > application may be improved by specifying schema names." ...
    (comp.databases.ms-sqlserver)
  • Re: One message should result into inserts into several sql-tables
    ... But I'll have to use several stored procedures it seems. ... Since there is a header-line structure ... (even without using transaction in the SP) ... You need to design you schema in the way you capture all the required data. ...
    (microsoft.public.biztalk.general)
  • Re: A philosophical question about inserts
    ... Stored Procedures are the ideal -for all the benefits mentioned above. ... schema from them and have them call stored procedures or views to get the ... DBI/DBD) would prepare/execute: ... The main goal here is to isolate the developer from knowing the database ...
    (comp.databases.ms-sqlserver)
  • Execute Immediate and bind variables
    ... I'm translated SQL Server stored procedures into Oracle. ... The schema name is passed into the stored procedure. ... We concatenate the ... Can I use bind variables in this ...
    (comp.databases.oracle.misc)
  • Re: Problem with Stored Procedure that conatins several select statements
    ... TOP 1 FROM @temptable FOR XML AUTO, ... 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 ... the final SELECT then the response elements are created as I expected. ...
    (microsoft.public.biztalk.general)