December 4, 2003 at 1:56 pm
I have a stored procedure that is designed to return XML as its result.
The stored procedure uses dynamic sql to check for the existence of a record on a linked server.
it does this prior to building the XML output.
Problem:
The exec command sends the selected record from my dynamic call.. This is throwing off my XML.
Question:
Can I keep the result set from my exec command from appearing. Suppress it somehow?
Example Output:
ID
-----------
1
XML_F52E2B61-18A1-11d1-B105-00805F49916B
----------------------------------------
<row RESULT="This is my XML." CODE="0"/>
-----
Everything is getting thrown off because the resultset that appears before my XML.
December 4, 2003 at 7:53 pm
I can think of 2 different approaches I would take in this situation.
1. Split this into two separate sp calls. One to give you if the record exists and a second to just give you the XML.
2. Change the SP to use an output parameter that returns the number of records effected. Then simply check the output variable in your calling application before using the XML.
The drawback with 1 is that you need to have 2 different sets of code. I would probably use the second option as I think it's cleaner and easier to understand.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
December 5, 2003 at 2:28 am
Have you tried using SET NOCOUNT ON? Something like this might help:
CREATE PROC foo
AS
BEGIN
SET NOCOUNT ON
-- Do linked server SQL
SET NOCOUNT OFF
-- Do XML building SQL
END
--
Chris Hedgate @ Extralives (http://www.extralives.com/)
Co-author of Best of SQL Server Central 2002 (http://www.sqlservercentral.com/bestof/)
Articles at sql.nu (http://www.sql.nu)
December 5, 2003 at 9:01 am
chrhedga,
the SET NOCOUNT ON only has to do with displaying the messages of how many rows were affecting in a SQL statement. It doesn't affect whether or not a SELECT statement will actually return it's output.
Anyways...I ended up using a solution much like gljjr's second suggestion.
Generated a temp SP within my stored procedure with an output parameter to capture the result of my select. I then
call the temp SP and assign its output to
a variable within the main SP. Then I destroy the temp SP.. Works fine.
Thanks everyone.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply