I have a vb com+ object that has a function that is supposed to return some XML from the database. It gets the XML via an ADO stream object. However, when I run the VB from its test harness in the project group it gives the following error;
MSXML3: Reference to undeclared namespace prefix: 'sql'.
My project references has;
MS ActiveX Data Objects 2.6 Library
MS XML v4.0
If I debug print the XML out it before writing it to the stream it gives ;
<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'><sql:header><sql:param name='ID'>
1
</sql:param></sql:header><sql:query>
exec sp100PaAddress_Select @.ID
</sql:query></ROOT>
"Richard P" <anonymous@.discussions.microsoft.com> wrote in message
news:3D95465D-CAD3-41CF-894E-B4077DF9B75C@.microsoft.com...
> I have a vb com+ object that has a function that is supposed to return
some XML from the database. It gets the XML via an ADO stream object.
However, when I run the VB from its test harness in the project group it
gives the following error;
> MSXML3: Reference to undeclared namespace prefix: 'sql'.
Try posting the code that is causing the error...
Bryant
Showing posts with label xml. Show all posts
Showing posts with label xml. Show all posts
Wednesday, March 21, 2012
MSXML parser & SQL Server Memory
The below snippet from SQL Server BOL under the topic "sp_xml_preparedocument" implies that the use of XML can be extremely memory intensive. Is is really the case that MSXML uses 1/8 total SQL Server memory? I find this astounding if true.
"Note A parsed document is stored in the internal cache of SQL Server 2000. The MSXML parser uses one-eighth the total memory available for SQL Server. To avoid running out of memory, run sp_xml_removedocument to free up the memory."
It means that it can use up to 1/8th of the memory. Unless you try to parse
a very large document or never release the allocated memory, you should
never use or reach this limit.
HTH
Michael
"glr" <glr@.discussions.microsoft.com> wrote in message
news:DE9CFF6C-B949-4318-9294-6B4EFB773AF2@.microsoft.com...
> The below snippet from SQL Server BOL under the topic
> "sp_xml_preparedocument" implies that the use of XML can be extremely
> memory intensive. Is is really the case that MSXML uses 1/8 total SQL
> Server memory? I find this astounding if true.
> "Note A parsed document is stored in the internal cache of SQL Server
> 2000. The MSXML parser uses one-eighth the total memory available for SQL
> Server. To avoid running out of memory, run sp_xml_removedocument to free
> up the memory."
>
"Note A parsed document is stored in the internal cache of SQL Server 2000. The MSXML parser uses one-eighth the total memory available for SQL Server. To avoid running out of memory, run sp_xml_removedocument to free up the memory."
It means that it can use up to 1/8th of the memory. Unless you try to parse
a very large document or never release the allocated memory, you should
never use or reach this limit.
HTH
Michael
"glr" <glr@.discussions.microsoft.com> wrote in message
news:DE9CFF6C-B949-4318-9294-6B4EFB773AF2@.microsoft.com...
> The below snippet from SQL Server BOL under the topic
> "sp_xml_preparedocument" implies that the use of XML can be extremely
> memory intensive. Is is really the case that MSXML uses 1/8 total SQL
> Server memory? I find this astounding if true.
> "Note A parsed document is stored in the internal cache of SQL Server
> 2000. The MSXML parser uses one-eighth the total memory available for SQL
> Server. To avoid running out of memory, run sp_xml_removedocument to free
> up the memory."
>
Friday, March 9, 2012
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
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
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
MSSQL2k retrive data from xml
Hello
I have mssql 2k there database and few XML files. I try get data from XML
files and insert into database. How to do that using DTS'
AJAHello AJA,
> I have mssql 2k there database and few XML files. I try get data
> from XML
> files and insert into database. How to do that using DTS'
Here's about a dozen ideas:
http://www.perfectxml.com/articles/xml/importxmlsql.asp
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||>> I have mssql 2k there database and few XML files. I try get data
> Here's about a dozen ideas:
> http://www.perfectxml.com/articles/xml/importxmlsql.asp
Yes i watched there and:
OPENXML does not work because to big file 10+MB or maybe anyone have idea
how to prepare_document using text variable? code which i found opened max
1,5MB xml file
Microsoft Visual Basic no have so can no make project using VB
I was intrested to use this one "DTS ActiveX Script & MSXML 4.0 DOM". I
tried, installed MSXML and copied code but it does not work.
Succesfully execution but no records in destination tables.
I have no idea what was wrong :(
Best Regards
AJA|||Hello AJA,
One thing that I'll suggest is that you get off of SQL Server 2000 DTS and
look seriously at SQL Server 2005's SSIS bits. Much better for working with
XML.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||> One thing that I'll suggest is that you get off of SQL Server 2000 DTS and
> look seriously at SQL Server 2005's SSIS bits. Much better for working
> with XML.
The problem is I can no change MSSQL to 2005 because its not my software.
So i'm looking how to solve it in MSSQL 2k.
Best Regards
AJA|||Hello,
Check out SQLXML technology
:http://msdn2.microsoft.com/en-us/library/aa225763(SQL.80).aspx.
I hope this helps.
Regards,
--
Monica Frintu
"AJA" wrote:
> Hello
> I have mssql 2k there database and few XML files. I try get data from XM
L
> files and insert into database. How to do that using DTS'
>
> AJA
>
I have mssql 2k there database and few XML files. I try get data from XML
files and insert into database. How to do that using DTS'
AJAHello AJA,
> I have mssql 2k there database and few XML files. I try get data
> from XML
> files and insert into database. How to do that using DTS'
Here's about a dozen ideas:
http://www.perfectxml.com/articles/xml/importxmlsql.asp
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||>> I have mssql 2k there database and few XML files. I try get data
> Here's about a dozen ideas:
> http://www.perfectxml.com/articles/xml/importxmlsql.asp
Yes i watched there and:
OPENXML does not work because to big file 10+MB or maybe anyone have idea
how to prepare_document using text variable? code which i found opened max
1,5MB xml file
Microsoft Visual Basic no have so can no make project using VB
I was intrested to use this one "DTS ActiveX Script & MSXML 4.0 DOM". I
tried, installed MSXML and copied code but it does not work.
Succesfully execution but no records in destination tables.
I have no idea what was wrong :(
Best Regards
AJA|||Hello AJA,
One thing that I'll suggest is that you get off of SQL Server 2000 DTS and
look seriously at SQL Server 2005's SSIS bits. Much better for working with
XML.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||> One thing that I'll suggest is that you get off of SQL Server 2000 DTS and
> look seriously at SQL Server 2005's SSIS bits. Much better for working
> with XML.
The problem is I can no change MSSQL to 2005 because its not my software.
So i'm looking how to solve it in MSSQL 2k.
Best Regards
AJA|||Hello,
Check out SQLXML technology
:http://msdn2.microsoft.com/en-us/library/aa225763(SQL.80).aspx.
I hope this helps.
Regards,
--
Monica Frintu
"AJA" wrote:
> Hello
> I have mssql 2k there database and few XML files. I try get data from XM
L
> files and insert into database. How to do that using DTS'
>
> AJA
>
MSSQL2k retrive data from xml
Hello
I have mssql 2k there database and few XML files. I try get data from XML
files and insert into database. How to do that using DTS?
AJA
Hello AJA,
> I have mssql 2k there database and few XML files. I try get data
> from XML
> files and insert into database. How to do that using DTS?
Here's about a dozen ideas:
http://www.perfectxml.com/articles/xml/importxmlsql.asp
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||>> I have mssql 2k there database and few XML files. I try get data
> Here's about a dozen ideas:
> http://www.perfectxml.com/articles/xml/importxmlsql.asp
Yes i watched there and:
OPENXML does not work because to big file 10+MB or maybe anyone have idea
how to prepare_document using text variable? code which i found opened max
1,5MB xml file
Microsoft Visual Basic no have so can no make project using VB
I was intrested to use this one "DTS ActiveX Script & MSXML 4.0 DOM". I
tried, installed MSXML and copied code but it does not work.
Succesfully execution but no records in destination tables.
I have no idea what was wrong
Best Regards
AJA
|||Hello AJA,
One thing that I'll suggest is that you get off of SQL Server 2000 DTS and
look seriously at SQL Server 2005's SSIS bits. Much better for working with
XML.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||> One thing that I'll suggest is that you get off of SQL Server 2000 DTS and
> look seriously at SQL Server 2005's SSIS bits. Much better for working
> with XML.
The problem is I can no change MSSQL to 2005 because its not my software.
So i'm looking how to solve it in MSSQL 2k.
Best Regards
AJA
|||Hello,
Check out SQLXML technology
:http://msdn2.microsoft.com/en-us/library/aa225763(SQL.80).aspx.
I hope this helps.
Regards,
Monica Frintu
"AJA" wrote:
> Hello
> I have mssql 2k there database and few XML files. I try get data from XML
> files and insert into database. How to do that using DTS?
>
> AJA
>
I have mssql 2k there database and few XML files. I try get data from XML
files and insert into database. How to do that using DTS?
AJA
Hello AJA,
> I have mssql 2k there database and few XML files. I try get data
> from XML
> files and insert into database. How to do that using DTS?
Here's about a dozen ideas:
http://www.perfectxml.com/articles/xml/importxmlsql.asp
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||>> I have mssql 2k there database and few XML files. I try get data
> Here's about a dozen ideas:
> http://www.perfectxml.com/articles/xml/importxmlsql.asp
Yes i watched there and:
OPENXML does not work because to big file 10+MB or maybe anyone have idea
how to prepare_document using text variable? code which i found opened max
1,5MB xml file
Microsoft Visual Basic no have so can no make project using VB
I was intrested to use this one "DTS ActiveX Script & MSXML 4.0 DOM". I
tried, installed MSXML and copied code but it does not work.
Succesfully execution but no records in destination tables.
I have no idea what was wrong
Best Regards
AJA
|||Hello AJA,
One thing that I'll suggest is that you get off of SQL Server 2000 DTS and
look seriously at SQL Server 2005's SSIS bits. Much better for working with
XML.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||> One thing that I'll suggest is that you get off of SQL Server 2000 DTS and
> look seriously at SQL Server 2005's SSIS bits. Much better for working
> with XML.
The problem is I can no change MSSQL to 2005 because its not my software.
So i'm looking how to solve it in MSSQL 2k.
Best Regards
AJA
|||Hello,
Check out SQLXML technology
:http://msdn2.microsoft.com/en-us/library/aa225763(SQL.80).aspx.
I hope this helps.
Regards,
Monica Frintu
"AJA" wrote:
> Hello
> I have mssql 2k there database and few XML files. I try get data from XML
> files and insert into database. How to do that using DTS?
>
> AJA
>
MSSQL2K FOR XML Explicit help?
underprocessableHi Ian...
not quite understanding how the different attributes get calculated, here is
both a FOR XML EXPLICIT and FOR XML PATH solution (the later requires SQL
Server 2005, hint hint). You may need to change some of the formatting of
the values to get them to be completely consistent with your expected
results.
FOR XML EXPLICIT:
select 1 as tag, 0 as parent
, '1' as "ExternalProducts!1!AccountID"
, GETUTCDATE() as "ExternalProducts!1!TimeStamp"
, 0 as "ExternalProducts!1!UpdateType"
, '' as "ExternalProducts!1!DependantFilename"
, '07042001.EDN' as "ExternalProducts!1!SourceFilename"
, (select count(ProductID) from Products) as
"ExternalProducts!1!ProductCount"
, (select count(OuterBarcode) from Products)
as"ExternalProducts!1!BarcodeCount"
, NULL as "Products!2!ID!hide"
, NULL as "Products!2!OuterBarcode"
, NULL as "Products!2!Department"
, NULL as "Products!2!SubDepartment"
, NULL as "Products!2!Vat"
, NULL as "Products!2!PromotionType"
, NULL as "Products!2!DiscontinuedDate"
, NULL as "Supplier!3!Reference"
, NULL as "Supplier!3!PreviousReference"
, NULL as "Supplier!3!CostPrice"
, NULL as "Supplier!3!Units"
, NULL as "Supplier!3!MinOrder"
, NULL as "Supplier!3!FutureCostPrice"
, NULL as "Supplier!3!FutureCostPriceChangeDate"
, NULL as "Supplier!3!FutureCostPriceRevertDate"
, NULL as "ProductVariant!4!Barcode"
, NULL as "ProductVariant!4!Description"
, NULL as "ProductVariant!4!Receipt"
, NULL as "ProductVariant!4!RetailPrice"
, NULL as "ProductVariant!4!PriceMarked"
union all
select 2 , 1
, '1' , NULL, NULL, NULL, NULL, NULL, NULL /*ExternalProducts*/
, ProductID
, ISNULL(OuterBarcode, '')
, DepartmentID
, SubDepartmentID
, VatCode
, ''
, ISNULL(CAST(DiscontinuedDate as nvarchar(20)), '') /*Products*/
, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL /*Supplier*/
, NULL, NULL, NULL, NULL, NULL /*ProductVariant*/
from Products
union all
select 3, 2
, '1' , NULL, NULL, NULL, NULL, NULL, NULL /*ExternalProducts*/
, Products.ProductID, NULL, NULL, NULL, NULL, NULL, NULL
/*Products*/
, CostPrices.Reference, '', CostPerOuter, UnitsPerOuter, MinOrderQty
, ISNULL(CAST(FutureCost as nvarchar(20)), '')
, ISNULL(CAST(FutureDate as nvarchar(20)), '')
, ISNULL(CAST(ReversionDate as nvarchar(20)), '') /*Supplier*/
, NULL, NULL, NULL, NULL, NULL /*ProductVariant*/
from CostPrices
join Products on CostPrices.ProductID = Products.ProductID
union all
select 4, 2
, '1' , NULL, NULL, NULL, NULL, NULL, NULL /*ExternalProducts*/
, Products.ProductID, NULL, NULL, NULL, NULL, NULL, NULL
/*Products*/
, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL /*Supplier*/
, rp.BarcodeID, b.Description, b.Receipt, rp.Price
, ISNULL(CAST(rp.FuturePrice as nvarchar(20)), 'False')
/*ProductVariant*/
from RetailPrices rp
join Barcodes b on rp.BarcodeID = b.BarcodeID
join Products on b.ProductID = Products.ProductID
order by "ExternalProducts!1!AccountID", "Products!2!ID!hide", tag
for xml explicit
FOR XML PATH:
select '1' as "@.AccountID"
, GETUTCDATE() as "@.TimeStamp"
, 0 as "@.UpdateType"
, '' as "@.DependantFilename"
, '07042001.EDN' as "@.SourceFilename"
, (select count(ProductID) from Products) as "@.ProductCount"
, (select count(OuterBarcode) from Products) as"@.BarcodeCount"
, ( select ISNULL(OuterBarcode, '') as "@.OuterBarcode"
, DepartmentID as "@.Department"
, SubDepartmentID as "@.SubDepartment"
, VatCode as "@.Vat"
, '' as "@.PromotionType" -- what relational column?
, ISNULL(CAST(DiscontinuedDate as nvarchar(20)), '') as
"@.DiscontinuedDate"
, ( select Reference as "@.Reference"
, '' as "@.PreviousReference" -- how do you calculate
that?
, CostPerOuter as "@.CostPrice"
, UnitsPerOuter as "@.Units"
, MinOrderQty as "@.MinOrder"
, ISNULL(CAST(FutureCost as nvarchar(20)), '') as
"@.FutureCostPrice"
, ISNULL(CAST(FutureDate as nvarchar(20)), '') as
"@.FutureCostPriceChangeDate"
, ISNULL(CAST(ReversionDate as nvarchar(20)), '') as
"@.FutureCostPriceRevertDate"
from CostPrices
where CostPrices.ProductID = Products.ProductID
for xml path('Supplier'), type)
, ( select rp.BarcodeID as "@.Barcode"
, b.Description as "@.Description"
, b.Receipt as "@.Receipt"
, rp.Price as "@.RetailPrice"
, ISNULL(CAST(rp.FuturePrice as nvarchar(20)), 'False') as
"@.PriceMarked" -- how is it calculated?
from RetailPrices rp
join Barcodes b on rp.BarcodeID = b.BarcodeID
where b.ProductID = Products.ProductID
for xml path('ProductVariant'), type)
from Products
for xml path('Products'),type)
for xml path('ExternalProducts')
I hope this helps
Michael
"Ian Ashworth" <spamcatch@.miragesoftware.co.uk> wrote in message
news:eTH0TszhHHA.208@.TK2MSFTNGP05.phx.gbl...
> Hey guys
> Currently Im pulling multiple recordsets back from SQL Server 2000 into
> VB6 and using text parsing techniques to create an XML file in the desired
> format. It does the job but is extremely slow and so I've been pointed in
> the direction of FOR XML EXPLICIT. Unfortunately this totally baffles me
> and I've been struggilng most of the day trying to get my head around it.
> I have attached a script to create the tables/example data and have
> included an example of the desired output in the comments so any help
> would be greatly appreciated.
> Thanks in advance for any assistance that you may provide.
> Ian Ashworth
>
>
not quite understanding how the different attributes get calculated, here is
both a FOR XML EXPLICIT and FOR XML PATH solution (the later requires SQL
Server 2005, hint hint). You may need to change some of the formatting of
the values to get them to be completely consistent with your expected
results.
FOR XML EXPLICIT:
select 1 as tag, 0 as parent
, '1' as "ExternalProducts!1!AccountID"
, GETUTCDATE() as "ExternalProducts!1!TimeStamp"
, 0 as "ExternalProducts!1!UpdateType"
, '' as "ExternalProducts!1!DependantFilename"
, '07042001.EDN' as "ExternalProducts!1!SourceFilename"
, (select count(ProductID) from Products) as
"ExternalProducts!1!ProductCount"
, (select count(OuterBarcode) from Products)
as"ExternalProducts!1!BarcodeCount"
, NULL as "Products!2!ID!hide"
, NULL as "Products!2!OuterBarcode"
, NULL as "Products!2!Department"
, NULL as "Products!2!SubDepartment"
, NULL as "Products!2!Vat"
, NULL as "Products!2!PromotionType"
, NULL as "Products!2!DiscontinuedDate"
, NULL as "Supplier!3!Reference"
, NULL as "Supplier!3!PreviousReference"
, NULL as "Supplier!3!CostPrice"
, NULL as "Supplier!3!Units"
, NULL as "Supplier!3!MinOrder"
, NULL as "Supplier!3!FutureCostPrice"
, NULL as "Supplier!3!FutureCostPriceChangeDate"
, NULL as "Supplier!3!FutureCostPriceRevertDate"
, NULL as "ProductVariant!4!Barcode"
, NULL as "ProductVariant!4!Description"
, NULL as "ProductVariant!4!Receipt"
, NULL as "ProductVariant!4!RetailPrice"
, NULL as "ProductVariant!4!PriceMarked"
union all
select 2 , 1
, '1' , NULL, NULL, NULL, NULL, NULL, NULL /*ExternalProducts*/
, ProductID
, ISNULL(OuterBarcode, '')
, DepartmentID
, SubDepartmentID
, VatCode
, ''
, ISNULL(CAST(DiscontinuedDate as nvarchar(20)), '') /*Products*/
, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL /*Supplier*/
, NULL, NULL, NULL, NULL, NULL /*ProductVariant*/
from Products
union all
select 3, 2
, '1' , NULL, NULL, NULL, NULL, NULL, NULL /*ExternalProducts*/
, Products.ProductID, NULL, NULL, NULL, NULL, NULL, NULL
/*Products*/
, CostPrices.Reference, '', CostPerOuter, UnitsPerOuter, MinOrderQty
, ISNULL(CAST(FutureCost as nvarchar(20)), '')
, ISNULL(CAST(FutureDate as nvarchar(20)), '')
, ISNULL(CAST(ReversionDate as nvarchar(20)), '') /*Supplier*/
, NULL, NULL, NULL, NULL, NULL /*ProductVariant*/
from CostPrices
join Products on CostPrices.ProductID = Products.ProductID
union all
select 4, 2
, '1' , NULL, NULL, NULL, NULL, NULL, NULL /*ExternalProducts*/
, Products.ProductID, NULL, NULL, NULL, NULL, NULL, NULL
/*Products*/
, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL /*Supplier*/
, rp.BarcodeID, b.Description, b.Receipt, rp.Price
, ISNULL(CAST(rp.FuturePrice as nvarchar(20)), 'False')
/*ProductVariant*/
from RetailPrices rp
join Barcodes b on rp.BarcodeID = b.BarcodeID
join Products on b.ProductID = Products.ProductID
order by "ExternalProducts!1!AccountID", "Products!2!ID!hide", tag
for xml explicit
FOR XML PATH:
select '1' as "@.AccountID"
, GETUTCDATE() as "@.TimeStamp"
, 0 as "@.UpdateType"
, '' as "@.DependantFilename"
, '07042001.EDN' as "@.SourceFilename"
, (select count(ProductID) from Products) as "@.ProductCount"
, (select count(OuterBarcode) from Products) as"@.BarcodeCount"
, ( select ISNULL(OuterBarcode, '') as "@.OuterBarcode"
, DepartmentID as "@.Department"
, SubDepartmentID as "@.SubDepartment"
, VatCode as "@.Vat"
, '' as "@.PromotionType" -- what relational column?
, ISNULL(CAST(DiscontinuedDate as nvarchar(20)), '') as
"@.DiscontinuedDate"
, ( select Reference as "@.Reference"
, '' as "@.PreviousReference" -- how do you calculate
that?
, CostPerOuter as "@.CostPrice"
, UnitsPerOuter as "@.Units"
, MinOrderQty as "@.MinOrder"
, ISNULL(CAST(FutureCost as nvarchar(20)), '') as
"@.FutureCostPrice"
, ISNULL(CAST(FutureDate as nvarchar(20)), '') as
"@.FutureCostPriceChangeDate"
, ISNULL(CAST(ReversionDate as nvarchar(20)), '') as
"@.FutureCostPriceRevertDate"
from CostPrices
where CostPrices.ProductID = Products.ProductID
for xml path('Supplier'), type)
, ( select rp.BarcodeID as "@.Barcode"
, b.Description as "@.Description"
, b.Receipt as "@.Receipt"
, rp.Price as "@.RetailPrice"
, ISNULL(CAST(rp.FuturePrice as nvarchar(20)), 'False') as
"@.PriceMarked" -- how is it calculated?
from RetailPrices rp
join Barcodes b on rp.BarcodeID = b.BarcodeID
where b.ProductID = Products.ProductID
for xml path('ProductVariant'), type)
from Products
for xml path('Products'),type)
for xml path('ExternalProducts')
I hope this helps
Michael
"Ian Ashworth" <spamcatch@.miragesoftware.co.uk> wrote in message
news:eTH0TszhHHA.208@.TK2MSFTNGP05.phx.gbl...
> Hey guys
> Currently Im pulling multiple recordsets back from SQL Server 2000 into
> VB6 and using text parsing techniques to create an XML file in the desired
> format. It does the job but is extremely slow and so I've been pointed in
> the direction of FOR XML EXPLICIT. Unfortunately this totally baffles me
> and I've been struggilng most of the day trying to get my head around it.
> I have attached a script to create the tables/example data and have
> included an example of the desired output in the comments so any help
> would be greatly appreciated.
> Thanks in advance for any assistance that you may provide.
> Ian Ashworth
>
>
Monday, February 20, 2012
MSSQL Select commands to FTP
Hello,
Im new to Sql Server Integration Services.
I need to get the data I get from my SQL Select statement into TXT, XML, CSV or Excel file format and then send it with FTP.
What is the easiest method that you recommend that I use?
Thank you very much!!
Data flow to select your records using an OLE DB source. Send those records to a text file (or csv) using the flat file destination.Then in the control flow, use the FTP task to send that file.|||
Thank you very much for the reply.
I know this sounds simple, but do you know any tutorial out there that shows how to do this?
Thanks again in advance.
|||Try looking through the SSIS tutorials. What you need is pretty basic stuff.http://msdn2.microsoft.com/en-us/library/ms167031.aspx|||
Thank you so much.
I have one complexity though.
My SSIS flow is gonna be run every day as a sceduled task.
The first time it runs, it has to send a History (Select Statement 1) but every day after that it just has to get the latest info (Select Statement 2).
How do I do this?
Thank you very much in advance!!
Subscribe to:
Posts (Atom)