Multi comboboxes

Hi,
My instructions are to add more functionality to current reports by adding a
second dropdown. The reports currently have one combobox that lists
Application names. When an Application is chosen the report opens with all
the data associated with that Application.
Now there is a request to add a second combo that would only be visible if
certain choices were made on the first combo. The choice from the second
combo open the report with a more limited set of values from the same source.
Currently stored procedures are used for the combos and the data returned
from choices uses stored procedures with parameters.
Is this possible to do and what is the best plan of attack?
Thanks,
--
FairfieldIf you have the first parameter in the second dataset query, it will
refresh the second list when the first parameter is changed.
So if your first parameter is called Team then the 2nd dataset query
might be something like;
EXEC dbo.sp_GetMyData2 @.Team
This should give you what you want.
Chris
Fairfield wrote:
> Hi,
> My instructions are to add more functionality to current reports by
> adding a second dropdown. The reports currently have one combobox
> that lists Application names. When an Application is chosen the
> report opens with all the data associated with that Application.
> Now there is a request to add a second combo that would only be
> visible if certain choices were made on the first combo. The choice
> from the second combo open the report with a more limited set of
> values from the same source.
> Currently stored procedures are used for the combos and the data
> returned from choices uses stored procedures with parameters.
> Is this possible to do and what is the best plan of attack?
> Thanks,|||Chris,
Thanks for the suggestion about using the first parameter in the second
query. That is a good idea.
But I think I need to clarify the situation a bit to make sure that I'm
fully explaining the situation. Normally the report is driven off of the
first query. However if certain choices are made then they want the second
combo to be visible and the report to run off of that query. Does this make
sense?
Thanks,
--
Fairfield
"|||You do not have that level of control of the parameters. It is possible to
hide parameters, it is not possible to hide/show on demand.
You can decide whether to use the second parameter or not (an easy way is to
send both to a stored procedure and make the decision there). Also, your
query can be an expression so based on the values of the parameters then
create the queries on the fly.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Fairfield" <Fairfield@.discussions.microsoft.com> wrote in message
news:38DD9B46-AD22-4926-B78A-1A8F3A9A2101@.microsoft.com...
> Chris,
> Thanks for the suggestion about using the first parameter in the second
> query. That is a good idea.
> But I think I need to clarify the situation a bit to make sure that I'm
> fully explaining the situation. Normally the report is driven off of the
> first query. However if certain choices are made then they want the
> second
> combo to be visible and the report to run off of that query. Does this
> make
> sense?
> Thanks,
> --
> Fairfield
>
> "|||Thanks Bruce,
My thinking here is slowly catching up to what we really need. It is 2
levels of choices. The dropdown lists all the choices but on some of them
there needs to be a second choice. There are far too many to list all in
one. An example might be listing cities in the dropdown and then having a
second dropdown open if there are multiple zip codes for that city. Smaller
towns might have 1 or 2 but large cities could have hundreds. The report
would show data on the particular zip code. In this case at the zipcode
level there could be hundreds to choose from so they can't be listed just in
the first dropdown.
Does this make sense? How can this kind of choice by made? It is something
like a treeview.
Thanks,
--
Fairfield
"Bruce L-C [MVP]" wrote:
> You do not have that level of control of the parameters. It is possible to
> hide parameters, it is not possible to hide/show on demand.
> You can decide whether to use the second parameter or not (an easy way is to
> send both to a stored procedure and make the decision there). Also, your
> query can be an expression so based on the values of the parameters then
> create the queries on the fly.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Fairfield" <Fairfield@.discussions.microsoft.com> wrote in message
> news:38DD9B46-AD22-4926-B78A-1A8F3A9A2101@.microsoft.com...
> > Chris,
> >
> > Thanks for the suggestion about using the first parameter in the second
> > query. That is a good idea.
> >
> > But I think I need to clarify the situation a bit to make sure that I'm
> > fully explaining the situation. Normally the report is driven off of the
> > first query. However if certain choices are made then they want the
> > second
> > combo to be visible and the report to run off of that query. Does this
> > make
> > sense?
> > Thanks,
> >
> > --
> > Fairfield
> >
> >
> > "
>
>|||Did you try and make queries that have (with a union) a static entry item
(like "NONE"), if it doesn't result in anything, and then default it to an
equation. That equation will determine if the default should be NONE or the
1st item?
--
"Everyone knows something you don't know"
"Fairfield" wrote:
> Thanks Bruce,
> My thinking here is slowly catching up to what we really need. It is 2
> levels of choices. The dropdown lists all the choices but on some of them
> there needs to be a second choice. There are far too many to list all in
> one. An example might be listing cities in the dropdown and then having a
> second dropdown open if there are multiple zip codes for that city. Smaller
> towns might have 1 or 2 but large cities could have hundreds. The report
> would show data on the particular zip code. In this case at the zipcode
> level there could be hundreds to choose from so they can't be listed just in
> the first dropdown.
> Does this make sense? How can this kind of choice by made? It is something
> like a treeview.
> Thanks,
> --
> Fairfield
>
> "Bruce L-C [MVP]" wrote:
> > You do not have that level of control of the parameters. It is possible to
> > hide parameters, it is not possible to hide/show on demand.
> >
> > You can decide whether to use the second parameter or not (an easy way is to
> > send both to a stored procedure and make the decision there). Also, your
> > query can be an expression so based on the values of the parameters then
> > create the queries on the fly.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Fairfield" <Fairfield@.discussions.microsoft.com> wrote in message
> > news:38DD9B46-AD22-4926-B78A-1A8F3A9A2101@.microsoft.com...
> > > Chris,
> > >
> > > Thanks for the suggestion about using the first parameter in the second
> > > query. That is a good idea.
> > >
> > > But I think I need to clarify the situation a bit to make sure that I'm
> > > fully explaining the situation. Normally the report is driven off of the
> > > first query. However if certain choices are made then they want the
> > > second
> > > combo to be visible and the report to run off of that query. Does this
> > > make
> > > sense?
> > > Thanks,
> > >
> > > --
> > > Fairfield
> > >
> > >
> > > "
> >
> >
> >|||I think a combination of my original suggestion and Davids is the best
you can hope for. As Bruce said, you can't conditionally show/hide a
parameter itself at run-time. So you'll just have to limit the options
in the second parameter based on the 1st parameter and whatever other
conditions required.
Chris
David Bienstock wrote:
> Did you try and make queries that have (with a union) a static entry
> item (like "NONE"), if it doesn't result in anything, and then
> default it to an equation. That equation will determine if the
> default should be NONE or the 1st item?|||Thanks for all the help. The 2 dropdowns are now populating correctly and
I'm happy with the way they are working. However a new problem is popping
up. I know it's simply my inexperience with Reporting Services and will soon
seem quite simple.
The first combo chooses the AppID. When the user makes a choice most of the
time the 2nd combo is not filled. But on certain choices the second is
filled. The user then makes a selection on the second and a report should be
returned.
The second combo actually output an AppID, just like the first combo, only
it is different number. The problem is that the report dataset only uses the
AppID from the first combo and doesn't change to the second.
I'm trying to figure out code or a function to force it to use the new
AppID and ignore it when the 2nd combo is null. My experience is in classic
vb and vba and DTS scripts. This all just doesn't seem as intuitive. I
purchased the Hitchhiker book last night and this has helped my understanding.
Thanks
--
Fairfield
"Chris McGuigan" wrote:
> I think a combination of my original suggestion and Davids is the best
> you can hope for. As Bruce said, you can't conditionally show/hide a
> parameter itself at run-time. So you'll just have to limit the options
> in the second parameter based on the 1st parameter and whatever other
> conditions required.
> Chris
>
> David Bienstock wrote:
> > Did you try and make queries that have (with a union) a static entry
> > item (like "NONE"), if it doesn't result in anything, and then
> > default it to an equation. That equation will determine if the
> > default should be NONE or the 1st item?
>|||Fairfield,
This is an easy one;
In SQL, assuming the parameters return integer values (just change
datatype accordingly and add single quotes if a text value);
Select * From MyTable Where AppId = IsNull(@.Combo2, @.Combo1)
or you can use VB to build the query, but this is slightly less
efficient;
="Select * From MyTable Where AppId = " +
IIf(IsDBNull(Parameters!Combo2.Value), Parameters!Combo1.Value,
Parameters!Combo2.Value)
Chris
Fairfield wrote:
> Thanks for all the help. The 2 dropdowns are now populating
> correctly and I'm happy with the way they are working. However a new
> problem is popping up. I know it's simply my inexperience with
> Reporting Services and will soon seem quite simple.
> The first combo chooses the AppID. When the user makes a choice most
> of the time the 2nd combo is not filled. But on certain choices the
> second is filled. The user then makes a selection on the second and
> a report should be returned.
> The second combo actually output an AppID, just like the first combo,
> only it is different number. The problem is that the report dataset
> only uses the AppID from the first combo and doesn't change to the
> second.
> I'm trying to figure out code or a function to force it to use the
> new AppID and ignore it when the 2nd combo is null. My experience is
> in classic vb and vba and DTS scripts. This all just doesn't seem as
> intuitive. I purchased the Hitchhiker book last night and this has
> helped my understanding.
> Thanks|||Thanks for all the help. It's working now.
--
Fairfield
"Fairfield" wrote:
> Hi,
> My instructions are to add more functionality to current reports by adding a
> second dropdown. The reports currently have one combobox that lists
> Application names. When an Application is chosen the report opens with all
> the data associated with that Application.
> Now there is a request to add a second combo that would only be visible if
> certain choices were made on the first combo. The choice from the second
> combo open the report with a more limited set of values from the same source.
> Currently stored procedures are used for the combos and the data returned
> from choices uses stored procedures with parameters.
> Is this possible to do and what is the best plan of attack?
> Thanks,
> --
> Fairfield

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 GrantThere'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.googlegroups.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.googlegroups.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.googlegroups.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

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