MSSQLError overwriting returning XML

Hello All,
Is there a way to have an xml template call not just return the
MSSQLError processing instructions but to also return a XML that I'm
expected to receive?
I am using Microsoft SQL Server 2000 with latest service pack, SQLXML
2.0, and Microsoft Windows Server 2003.
My xml template calls a stored procedure that inserts some data into
some tables and the stored procedure will always return some other data
back in a XML (using for xml explicit). The table contains unique,
foreign, and primary key constraints, so if the inserted data fails a
constraint (for AuthNo column), the stored procedure will continually
generates a new value for AuthNo and adds it into the table again.
As you can see, if the xml template is called during high volume times,
the database will raise errors on unique key constraint failures. I
need to see my resultant XML, and I don't care about the number of
MSSQLErrors PIs (from testing, I see a min of one PI and a max of 15
PIs). I have two applications calling the xml template in two different
ways, and have another application that explicitly calls the stored
procedure.
When explicitly calling the stored procedure using Query Analyzer, I
see the errors being printed and also my generated XML. I believe that
I always see the generated XML from my C application that calls the XML
template with XMLDOM. I only see the MSQLErrors PI when my ASP page,
using Javascript and Microsoft.XMLDOM, calls the XML template.
In that ASP page scenario, I see that my stored procedure succeeded in
adding data into the table. So somehow, the MSQLErrors PI is
overwriting or needlessly clearing out my stored proc-generated XML.
Can any of you help me figure out how to still have my generated XML be
returned? If there's no errors, I see the sp-generated XML result fine.
Before you ask or make any suggestions, yes, I need those unique
constraints. I cannot use indexes to generate this AuthNo since I have
to conform to a specific algorithm. I do not want to query the table
for existence for an AuthNo to put in since there exist the possibility
that the insert call would be too late because some other app also used
the same AuthNo value.
Please help,
Marcus Grant
There's no way to do this directly, no. You could apply an XSLT to the
results of the template to strip out the errors. That would be my
suggestion.
Irwin
<grantmarcus@.gmail.com> wrote in message
news:1112130864.698392.128370@.o13g2000cwo.googlegr oups.com...
> Hello All,
> Is there a way to have an xml template call not just return the
> MSSQLError processing instructions but to also return a XML that I'm
> expected to receive?
> I am using Microsoft SQL Server 2000 with latest service pack, SQLXML
> 2.0, and Microsoft Windows Server 2003.
> My xml template calls a stored procedure that inserts some data into
> some tables and the stored procedure will always return some other data
> back in a XML (using for xml explicit). The table contains unique,
> foreign, and primary key constraints, so if the inserted data fails a
> constraint (for AuthNo column), the stored procedure will continually
> generates a new value for AuthNo and adds it into the table again.
> As you can see, if the xml template is called during high volume times,
> the database will raise errors on unique key constraint failures. I
> need to see my resultant XML, and I don't care about the number of
> MSSQLErrors PIs (from testing, I see a min of one PI and a max of 15
> PIs). I have two applications calling the xml template in two different
> ways, and have another application that explicitly calls the stored
> procedure.
> When explicitly calling the stored procedure using Query Analyzer, I
> see the errors being printed and also my generated XML. I believe that
> I always see the generated XML from my C application that calls the XML
> template with XMLDOM. I only see the MSQLErrors PI when my ASP page,
> using Javascript and Microsoft.XMLDOM, calls the XML template.
> In that ASP page scenario, I see that my stored procedure succeeded in
> adding data into the table. So somehow, the MSQLErrors PI is
> overwriting or needlessly clearing out my stored proc-generated XML.
> Can any of you help me figure out how to still have my generated XML be
> returned? If there's no errors, I see the sp-generated XML result fine.
> Before you ask or make any suggestions, yes, I need those unique
> constraints. I cannot use indexes to generate this AuthNo since I have
> to conform to a specific algorithm. I do not want to query the table
> for existence for an AuthNo to put in since there exist the possibility
> that the insert call would be too late because some other app also used
> the same AuthNo value.
> Please help,
> Marcus Grant
>
|||The XML returned by the XML template call will only contain the errors.
I traced the output, made a temp ASP page that displays the returning
XML, and added a call to window.open() on the XML that's return on the
real ASP page. All of them do not have my intended, generated XML for
the xml template call.
Stripping the errors would just give me a empty XML.
How would I do this indirectly? Can you validate my statement that the
MSSQLErrors are overwriting what's being returned? This is a problem in
an production environment and we were unaware of this unintended
behavior.
Thank you for answering my question,
Marcus Grant
|||Can you post the template?
Irwin
<grantmarcus@.gmail.com> wrote in message
news:1112705431.302580.77210@.l41g2000cwc.googlegro ups.com...
> The XML returned by the XML template call will only contain the errors.
> I traced the output, made a temp ASP page that displays the returning
> XML, and added a call to window.open() on the XML that's return on the
> real ASP page. All of them do not have my intended, generated XML for
> the xml template call.
> Stripping the errors would just give me a empty XML.
> How would I do this indirectly? Can you validate my statement that the
> MSSQLErrors are overwriting what's being returned? This is a problem in
> an production environment and we were unaware of this unintended
> behavior.
> Thank you for answering my question,
> Marcus Grant
>
|||I'll post a similar template that does the same thing. I'll also post
the schema and the stored procedure. This verison is bare minimum of
what I'm trying to achieve. End result is the same.
Once you create the table, stored proc, and run the xml template,
you'll only see the errors. If you query the table, you'll see the data
being inserted fine.
Because of multithreading / multiapplication, we said "hey, let the
database handle the uniqueness of the authorization number. If it
already exist, we just create a new one."
Watch out for word wrappings. And thanks again for looking into this
issue.
Here's the xml template:
-- START Activity_Add.XML template --
<root xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:header nullvalue="NULLVALUE">
<sql:param name="data">NULLVALUE</sql:param>
</sql:header>
<sql:query client-side-xml="1">
exec Activity_Add @.data
for xml explicit
</sql:query>
</root>
-- END Activity_Add.xml TEMPLATE --
-- START SQL schema / stored proc --
drop table Activity
create table Activity
(
dateseq integer, -- YYYYMMDD
authno integer, -- Unique algorithm for a day
data varchar(10), -- Fluff data
primary key (dateseq,authno) -- For each day, there exists unique
authno
)
GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO
-- Drop the stored procedure if it exists
if exists (select * from sysobjects where id =
object_id(N'[dbo].[Activity_Add]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[Activity_Add]
GO
-- Generate the stored procedure that tries adding a second row with
the same primary key before and then adds it again with a different
key.
CREATE PROCEDURE Activity_Add
(
@.Data As varchar(10)
)
AS
-- Do not return number of rows affected
SET NOCOUNT ON
-- Declare local variables
Declare @.rc integer
Declare @.DateSeq integer
Declare @.AuthNo integer
-- Set default values for local variables
Set @.rc = 0
Set @.DateSeq = 20050405 -- April 5, 2005
Set @.AuthNo = 12345678 -- Cheap unique number for the day
-- Insert the first row into the table
insert into Activity
values
(
@.DateSeq,
@.AuthNo,
@.Data -- Data to add
)
-- This should return success
Set @.rc = @.@.ERROR
-- This is pretty much needless since it's 0
if (0 = @.rc)
begin
-- Now try adding a row with the same primary key
insert into Activity
values
(
@.DateSeq, -- Same dateseq
@.AuthNo, -- Same authno
'data2' -- Different fluff data
)
-- Should fail
Set @.rc = @.@.ERROR
end
if ( (2627 = @.rc) -- Is it a primary key constraint?
or (2601 = @.rc) -- Or a unique key constraint.
)
begin
-- Now generate a different authno to please the constraints
Set @.AuthNo = @.AuthNo + 1
insert into Activity
values
(
@.DateSeq, -- Same dateseq
@.AuthNo, -- Different authno
'data2' -- fluff data to add
)
end
-- Return something in XML form
select
1 As Tag,
NULL As Parent,
@.AuthNo As [RESPONSE!1!AuthNum],
@.rc as [RESPONSE!1!ResponseCode]
-- Return the return code also
return @.rc
GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO
-- END SQL schema / stored proc --
Thanks yet again,
Marcus
|||On a similar note, we just came across as situation where a different
xml template call does return the expected XML while returning an
MSSQLError PI.
The xml template passes in the parameters as is to a stored procedure
that does the real work (similar to my ealier problem and example...
which isn't fixed yet).
For this situation, we accidentally passed in a parameter as using
alpha characters while the underlying stored procedure expects the
parameter to be an int. The MSSQLError PI says there's error in
converting a varchar into an int.
The surprising thing is that I would have expected the execution to
stop right there instead of continuing on, which is what query analyzer
does.
So it seems like I'm having two problems:
1) An error raised by an underlying stored procedure call will cause
the xml template to only display the MSSQLError PIs when it should also
diplay the expected resultant XML.
2) An error raised by the logic in the xml template will display the
MSSQLError PI and continue processing when it should just stop
execution and just return the PI.
Are these bugs in the SQLXML 3.0 and under? Or am I doing things wrong?
Thanks for the help,
Marcus Grant
|||I think this has to do with how we parse the results of Sprocs returning
mutliple resultsets and converting them into XML on the client. I'll have
one of our devs look at it.
Irwin
<grantmarcus@.gmail.com> wrote in message
news:1113254695.680546.203430@.o13g2000cwo.googlegr oups.com...
> On a similar note, we just came across as situation where a different
> xml template call does return the expected XML while returning an
> MSSQLError PI.
> The xml template passes in the parameters as is to a stored procedure
> that does the real work (similar to my ealier problem and example...
> which isn't fixed yet).
> For this situation, we accidentally passed in a parameter as using
> alpha characters while the underlying stored procedure expects the
> parameter to be an int. The MSSQLError PI says there's error in
> converting a varchar into an int.
> The surprising thing is that I would have expected the execution to
> stop right there instead of continuing on, which is what query analyzer
> does.
> So it seems like I'm having two problems:
> 1) An error raised by an underlying stored procedure call will cause
> the xml template to only display the MSSQLError PIs when it should also
> diplay the expected resultant XML.
> 2) An error raised by the logic in the xml template will display the
> MSSQLError PI and continue processing when it should just stop
> execution and just return the PI.
> Are these bugs in the SQLXML 3.0 and under? Or am I doing things wrong?
> Thanks for the help,
> Marcus Grant
>
|||Thanks for looking into this.
In hindsight, we shouldn't have assumed that things that work through
query analyzer will also work with a XML template call.
For people interested, we found a work around for the first problem.
Originally we have a constraint on a table for a unique number per day.
We have an algorithm to determine the unique number (two calls at the
same exact time from different threads will generate the same number).
We do a blind insert into a table with the unique number, check for
unique constraint error, and just loop again indefinitely; creating a
unique number and adding to the table until the insertion is
successful.
Our fix is to disregard the number generation algorithm (luckily we
didn't have to conform to it after all), create a table with a
pre-generated list of unique numbers, and upon inserting to the table,
retrieve a non-used generated number and add it into the table. This
ensures that the unique number will only be added one time per day.
The second problem was just a manual testing bug and shouldn't ever
occur in production.
Thanks,
Marcus Grant