I mistakenly posted this in the "programming" forum- reposting here as it
makes more sense to be in this forum...
We have a conversion process where we bcp out a table and then run a C++
program against it and then later bcp the data into a new table.
For a 24 Million row table, the bcp out takes about 83 minutes and the bcp
in takes about 87 minutes.
Is there any performance considerations to be aware of that could possibly
make this run faster (SQL 2000)? For example - I recall in SQL 6.5 that
there was a "select into/bulkcopy" option. Is there anything similar in SQL
2000?
For the bcp out, is there a way to reduce locking (maybe grab a table lock).
Any input would be appreciated.
Thanks in advanceyes,
Drop indexes on the table
change the database recovery mode to bulk logged ( In SQL Enterprise
Manager)
Do the bcp in (Use the TABLOCK hint)
Recreate the indexes
change back to full recovery
Do a transaction log backup..
You can also increase the load time by dividing the input file into multiple
files and firing off several concurrent BCPs. This is available when you use
the TABLOCK hint.
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:#7Ac88OuDHA.2308@.TK2MSFTNGP09.phx.gbl...
> I mistakenly posted this in the "programming" forum- reposting here as it
> makes more sense to be in this forum...
> We have a conversion process where we bcp out a table and then run a C++
> program against it and then later bcp the data into a new table.
> For a 24 Million row table, the bcp out takes about 83 minutes and the bcp
> in takes about 87 minutes.
> Is there any performance considerations to be aware of that could possibly
> make this run faster (SQL 2000)? For example - I recall in SQL 6.5 that
> there was a "select into/bulkcopy" option. Is there anything similar in
SQL
> 2000?
> For the bcp out, is there a way to reduce locking (maybe grab a table
lock).
> Any input would be appreciated.
> Thanks in advance
>
Showing posts with label process. Show all posts
Showing posts with label process. Show all posts
Wednesday, March 28, 2012
optimizing bcp performance
Labels:
bcp,
conversion,
database,
forum-,
microsoft,
mistakenly,
mysql,
optimizing,
oracle,
performance,
process,
programming,
reposting,
sense,
server,
sql
Wednesday, March 21, 2012
Optimizations
I currently have a nightly process that truncates a set of tables prior to
doing a bulk insert from an odbc data source. If these tables have indexes
defined, will the bulk insert step make use of these? The reason I ask is
because I wasn't sure if it is worth performing optimizations (maintenance
plan) immediately after the insert. Any help is appreciated.
--
DrewDrew,
If the data to be imported is huge (a lot of rows), then it is better to
drop the indexes and recreate them after the bulk operation. Remember,
indexes are updates for each row inserted, updated or deleted.
AMB
"Drew" wrote:
> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexes
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew|||Depending on the amount of data being inserted, its probably better to drop
the indexes and recreate them after the bulk insert, especially if one of the
indexes is a clustered index.
Bulk insert is not helped by indexes of any kind, but it sure can be
hindered by them. When the table is truncated, all the pointers in the
indexes are worthless. When the new data comes in, the clustered index (if
exists) has to manuver each row into the same order on the pages as the
clustered index is configured. this will slow down the bulk insert unless the
data is already in the same order.
if the data set is small, then just load the data and do a dbcc indexdefrag
to reindex the table.
"Drew" wrote:
> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexes
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew|||Bulk insert can be faster if you drop the indexes first. However, there are
other things you may want to consider (for example, database recovery mode).
You can search for "optimizing bulk copy performance" in BOL for more detail.
"Drew" wrote:
> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexes
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew|||Thanks for your input everyone. Again, much appreciated.
"Drew" wrote:
> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexes
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew
doing a bulk insert from an odbc data source. If these tables have indexes
defined, will the bulk insert step make use of these? The reason I ask is
because I wasn't sure if it is worth performing optimizations (maintenance
plan) immediately after the insert. Any help is appreciated.
--
DrewDrew,
If the data to be imported is huge (a lot of rows), then it is better to
drop the indexes and recreate them after the bulk operation. Remember,
indexes are updates for each row inserted, updated or deleted.
AMB
"Drew" wrote:
> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexes
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew|||Depending on the amount of data being inserted, its probably better to drop
the indexes and recreate them after the bulk insert, especially if one of the
indexes is a clustered index.
Bulk insert is not helped by indexes of any kind, but it sure can be
hindered by them. When the table is truncated, all the pointers in the
indexes are worthless. When the new data comes in, the clustered index (if
exists) has to manuver each row into the same order on the pages as the
clustered index is configured. this will slow down the bulk insert unless the
data is already in the same order.
if the data set is small, then just load the data and do a dbcc indexdefrag
to reindex the table.
"Drew" wrote:
> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexes
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew|||Bulk insert can be faster if you drop the indexes first. However, there are
other things you may want to consider (for example, database recovery mode).
You can search for "optimizing bulk copy performance" in BOL for more detail.
"Drew" wrote:
> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexes
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew|||Thanks for your input everyone. Again, much appreciated.
"Drew" wrote:
> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexes
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew
Optimizations
I currently have a nightly process that truncates a set of tables prior to
doing a bulk insert from an odbc data source. If these tables have indexes
defined, will the bulk insert step make use of these? The reason I ask is
because I wasn't sure if it is worth performing optimizations (maintenance
plan) immediately after the insert. Any help is appreciated.
Drew
Drew,
If the data to be imported is huge (a lot of rows), then it is better to
drop the indexes and recreate them after the bulk operation. Remember,
indexes are updates for each row inserted, updated or deleted.
AMB
"Drew" wrote:
> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexes
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew
|||Depending on the amount of data being inserted, its probably better to drop
the indexes and recreate them after the bulk insert, especially if one of the
indexes is a clustered index.
Bulk insert is not helped by indexes of any kind, but it sure can be
hindered by them. When the table is truncated, all the pointers in the
indexes are worthless. When the new data comes in, the clustered index (if
exists) has to manuver each row into the same order on the pages as the
clustered index is configured. this will slow down the bulk insert unless the
data is already in the same order.
if the data set is small, then just load the data and do a dbcc indexdefrag
to reindex the table.
"Drew" wrote:
> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexes
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew
|||Bulk insert can be faster if you drop the indexes first. However, there are
other things you may want to consider (for example, database recovery mode).
You can search for "optimizing bulk copy performance" in BOL for more detail.
"Drew" wrote:
> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexes
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew
|||Thanks for your input everyone. Again, much appreciated.
"Drew" wrote:
> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexes
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew
sql
doing a bulk insert from an odbc data source. If these tables have indexes
defined, will the bulk insert step make use of these? The reason I ask is
because I wasn't sure if it is worth performing optimizations (maintenance
plan) immediately after the insert. Any help is appreciated.
Drew
Drew,
If the data to be imported is huge (a lot of rows), then it is better to
drop the indexes and recreate them after the bulk operation. Remember,
indexes are updates for each row inserted, updated or deleted.
AMB
"Drew" wrote:
> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexes
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew
|||Depending on the amount of data being inserted, its probably better to drop
the indexes and recreate them after the bulk insert, especially if one of the
indexes is a clustered index.
Bulk insert is not helped by indexes of any kind, but it sure can be
hindered by them. When the table is truncated, all the pointers in the
indexes are worthless. When the new data comes in, the clustered index (if
exists) has to manuver each row into the same order on the pages as the
clustered index is configured. this will slow down the bulk insert unless the
data is already in the same order.
if the data set is small, then just load the data and do a dbcc indexdefrag
to reindex the table.
"Drew" wrote:
> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexes
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew
|||Bulk insert can be faster if you drop the indexes first. However, there are
other things you may want to consider (for example, database recovery mode).
You can search for "optimizing bulk copy performance" in BOL for more detail.
"Drew" wrote:
> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexes
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew
|||Thanks for your input everyone. Again, much appreciated.
"Drew" wrote:
> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexes
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew
sql
Optimizations
I currently have a nightly process that truncates a set of tables prior to
doing a bulk insert from an odbc data source. If these tables have indexes
defined, will the bulk insert step make use of these? The reason I ask is
because I wasn't sure if it is worth performing optimizations (maintenance
plan) immediately after the insert. Any help is appreciated.
--
DrewDrew,
If the data to be imported is huge (a lot of rows), then it is better to
drop the indexes and recreate them after the bulk operation. Remember,
indexes are updates for each row inserted, updated or deleted.
AMB
"Drew" wrote:
> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexe
s
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew|||Depending on the amount of data being inserted, its probably better to drop
the indexes and recreate them after the bulk insert, especially if one of th
e
indexes is a clustered index.
Bulk insert is not helped by indexes of any kind, but it sure can be
hindered by them. When the table is truncated, all the pointers in the
indexes are worthless. When the new data comes in, the clustered index (if
exists) has to manuver each row into the same order on the pages as the
clustered index is configured. this will slow down the bulk insert unless th
e
data is already in the same order.
if the data set is small, then just load the data and do a dbcc indexdefrag
to reindex the table.
"Drew" wrote:
> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexe
s
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew|||Bulk insert can be faster if you drop the indexes first. However, there are
other things you may want to consider (for example, database recovery mode).
You can search for "optimizing bulk copy performance" in BOL for more detail
.
"Drew" wrote:
> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexe
s
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew|||Thanks for your input everyone. Again, much appreciated.
"Drew" wrote:
> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexe
s
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew
doing a bulk insert from an odbc data source. If these tables have indexes
defined, will the bulk insert step make use of these? The reason I ask is
because I wasn't sure if it is worth performing optimizations (maintenance
plan) immediately after the insert. Any help is appreciated.
--
DrewDrew,
If the data to be imported is huge (a lot of rows), then it is better to
drop the indexes and recreate them after the bulk operation. Remember,
indexes are updates for each row inserted, updated or deleted.
AMB
"Drew" wrote:
> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexe
s
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew|||Depending on the amount of data being inserted, its probably better to drop
the indexes and recreate them after the bulk insert, especially if one of th
e
indexes is a clustered index.
Bulk insert is not helped by indexes of any kind, but it sure can be
hindered by them. When the table is truncated, all the pointers in the
indexes are worthless. When the new data comes in, the clustered index (if
exists) has to manuver each row into the same order on the pages as the
clustered index is configured. this will slow down the bulk insert unless th
e
data is already in the same order.
if the data set is small, then just load the data and do a dbcc indexdefrag
to reindex the table.
"Drew" wrote:
> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexe
s
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew|||Bulk insert can be faster if you drop the indexes first. However, there are
other things you may want to consider (for example, database recovery mode).
You can search for "optimizing bulk copy performance" in BOL for more detail
.
"Drew" wrote:
> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexe
s
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew|||Thanks for your input everyone. Again, much appreciated.
"Drew" wrote:
> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexe
s
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew
Tuesday, March 20, 2012
Optimization
Hi All,
I am writing a script that runs the optimization process. I am not sure how to test it? Any help is greatly appreciated.The optimization process?|||Sorry, script that uses dbcc dbreindex command. I don't want to use optimization that can be setup through maintenance plan.|||Perhaps if you created a table with an index on a guid column, then insert 10,000 rows into it. The GUID index should be considerably fragmented.|||You trying to verify that DBCC is working?
If your script just runs DBCC commands, then pipe the output of the job to a file (good idea anyway). If there are any errors or messages from DBCC you will find them there.|||unless this is a serious application that takes in thousands of records a day you are not going to have to do this too often.
here is some light reading on the subject...
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx|||blindman,
do you have a sample code of how to send a job output to a file?|||In the stored procedure sp_add_jobstep, there is a parameter @.output_file_name. This is for the output file you would configure on the advanced tab of a job step in EM. Here is part of a much larger script that I have:
exec sp_add_jobstep
@.job_id = @.jobid,
@.step_id = 1, --@.step_id
@.step_name = N'DBCC Checks', --@.step_name
@.subsystem = 'TSQL', --@.subsystem
@.command = @.cmd, --@.command
@.cmdexec_success_code = 0, --@.cmdexec_success_code
@.on_success_action = 3, --@.on_success_action
@.on_success_step_id = 0, --@.on_success_step_id
@.on_fail_action = 2, --@.on_fail_action
@.on_fail_step_id = 0, --@.on_fail_step_id
@.database_name = master, --@.database_name
@.database_user_name = dbo, --@.database_user_name
@.retry_attempts = 0, --@.retry_attempts
@.retry_interval = 0, --@.retry_interval
@.output_file_name = @.LogName, --@.output_file_name
@.flags = 4 --@.flags (for output file)
Yeah, I know the comments aren't too helpful. I think I meant to go back and make them prettier at some point. BOL will have the full list of parameters, and their definitions.|||In the Edit Job Step dialog box of Enterprise Manager, go to the Advanced tab and you can set the location of your output file.|||Thank you very much for the help.
I am writing a script that runs the optimization process. I am not sure how to test it? Any help is greatly appreciated.The optimization process?|||Sorry, script that uses dbcc dbreindex command. I don't want to use optimization that can be setup through maintenance plan.|||Perhaps if you created a table with an index on a guid column, then insert 10,000 rows into it. The GUID index should be considerably fragmented.|||You trying to verify that DBCC is working?
If your script just runs DBCC commands, then pipe the output of the job to a file (good idea anyway). If there are any errors or messages from DBCC you will find them there.|||unless this is a serious application that takes in thousands of records a day you are not going to have to do this too often.
here is some light reading on the subject...
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx|||blindman,
do you have a sample code of how to send a job output to a file?|||In the stored procedure sp_add_jobstep, there is a parameter @.output_file_name. This is for the output file you would configure on the advanced tab of a job step in EM. Here is part of a much larger script that I have:
exec sp_add_jobstep
@.job_id = @.jobid,
@.step_id = 1, --@.step_id
@.step_name = N'DBCC Checks', --@.step_name
@.subsystem = 'TSQL', --@.subsystem
@.command = @.cmd, --@.command
@.cmdexec_success_code = 0, --@.cmdexec_success_code
@.on_success_action = 3, --@.on_success_action
@.on_success_step_id = 0, --@.on_success_step_id
@.on_fail_action = 2, --@.on_fail_action
@.on_fail_step_id = 0, --@.on_fail_step_id
@.database_name = master, --@.database_name
@.database_user_name = dbo, --@.database_user_name
@.retry_attempts = 0, --@.retry_attempts
@.retry_interval = 0, --@.retry_interval
@.output_file_name = @.LogName, --@.output_file_name
@.flags = 4 --@.flags (for output file)
Yeah, I know the comments aren't too helpful. I think I meant to go back and make them prettier at some point. BOL will have the full list of parameters, and their definitions.|||In the Edit Job Step dialog box of Enterprise Manager, go to the Advanced tab and you can set the location of your output file.|||Thank you very much for the help.
Wednesday, March 7, 2012
operationg system requirement
I am in the process of learning SQL from scratch and figure it would be helpful if I can run a local server and experiment with it as I move along some book reading. I have a few questions regarding the operating system requirement for running a localhost server and hope some of you can answer to my novice questions. Can I setup a server under Windows XP? when I looked thru the documentation it indicated only windows 2000 and other Microsoft server OS will support a SQL server setup. Why then it also indicate XP as compatible? Is it because it's compatible as a client? If I do have to run a server under a different operationg system, what is the best approach for setting up another server on my only PC system along with my XP. Thanks for any answers and comments in advance.
Does "Windows XP with SP 2 or later" mean that the media center edition 2005 should suffice?
|||
|||so with an athlon 64 X2 dual Core processor 3800+ 2Ghz I should be ordering the
x64 developer version rather than win32? Or would the latter also work (and be compatible with an older processor)
|||
Are you talking about SQL 2005? XP does support SQL 2005, but it depends on which edition you have. See the following link for more information about which edition is supported on which OS. http://www.microsoft.com/sql/prodinfo/sysreqs/default.mspx
|||I'm not completely clear on whether I'm covered for a developer editionDoes "Windows XP with SP 2 or later" mean that the media center edition 2005 should suffice?
|||
Short answer: Yes.
Please see topic "Hardware and Software Requirementse for Installing SQL Server 2005" for more information.
http://msdn2.microsoft.com/en-us/library/ms143506(SQL.90).aspx
|||Short Answer: Thanks|||so with an athlon 64 X2 dual Core processor 3800+ 2Ghz I should be ordering the
x64 developer version rather than win32? Or would the latter also work (and be compatible with an older processor)
|||
x86 can install on X64 machine, but you should install x64 version if you can.
Operation canceled
Hi,
I have a stored procedure which can take quite a long time to process. this
SP is run from a sql server Job.
The SP loops through rows in a table and stores the primary key values of
the rows that where processed in a table varaible such that when the loop
finishes it executes an update statment on the main table to flag each
processed row as processed!
My concerns is that if the user stops the Job while the loop is doing its
work, then the updated rows will not be flagged.
is there a way to trap the cancel request and perform the update statement
before quitting the SP?
regards,Emmanuel
Deny the user sy
min privileges.
"Emmanuel" <emmanuel@.email.com> wrote in message
news:%23yLvVSgKFHA.1176@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a stored procedure which can take quite a long time to process.
this
> SP is run from a sql server Job.
> The SP loops through rows in a table and stores the primary key values of
> the rows that where processed in a table varaible such that when the loop
> finishes it executes an update statment on the main table to flag each
> processed row as processed!
> My concerns is that if the user stops the Job while the loop is doing its
> work, then the updated rows will not be flagged.
> is there a way to trap the cancel request and perform the update statement
> before quitting the SP?
> regards,
>
I have a stored procedure which can take quite a long time to process. this
SP is run from a sql server Job.
The SP loops through rows in a table and stores the primary key values of
the rows that where processed in a table varaible such that when the loop
finishes it executes an update statment on the main table to flag each
processed row as processed!
My concerns is that if the user stops the Job while the loop is doing its
work, then the updated rows will not be flagged.
is there a way to trap the cancel request and perform the update statement
before quitting the SP?
regards,Emmanuel
Deny the user sy
"Emmanuel" <emmanuel@.email.com> wrote in message
news:%23yLvVSgKFHA.1176@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a stored procedure which can take quite a long time to process.
this
> SP is run from a sql server Job.
> The SP loops through rows in a table and stores the primary key values of
> the rows that where processed in a table varaible such that when the loop
> finishes it executes an update statment on the main table to flag each
> processed row as processed!
> My concerns is that if the user stops the Job while the loop is doing its
> work, then the updated rows will not be flagged.
> is there a way to trap the cancel request and perform the update statement
> before quitting the SP?
> regards,
>
Monday, February 20, 2012
openxml seemingly fails to process external entity
I am having a problem getting OPENXML to process some xml that contains an
"external entity" in SQL Server 2000.
Background, which you probably already know:
--
Xml uses "external entities" the way the C language uses "#include": when
an xml processor finds the directive, it refers to an external file whose
contents are to be processed at that point.
I have created a minimal example that illustrates the problem.
1. First, here is some SQL that correctly processes some XML that does
*not* use external entities. The XML is stored in a variable (@.testXml), an
d
a select statement reads this XML and reports some basic facts about the onl
y
data in it, namely, that there is a tag called "DatastreamDownload ":
-- demonstrate validity of simple xml
declare @.testXml varchar(8000)
set @.testXml ='
<?xml version="1.0" ?>
<DatastreamDownload />
'
declare @.idoc int
exec sp_xml_preparedocument @.idoc output, @.testXml
select *
from openxml( @.idoc, '/DatastreamDownload', 0)
exec sp_xml_removedocument @.idoc
Here are the beginnings of the lines I see in SQL Query Analyzer:
id parentid nodetype localname
-- -- -- --
0 NULL 1 DatastreamDownload
(1 row(s) affected)
2. I then stored the XML from @.testXml in a file, simple.xml. The
following 2 lines show the contents of the file, except that I've added 4
spaces to the beginning of each of the lines in this listing (that is, the
prefixed spaces are not in the file):
<?xml version="1.0" ?>
<DatastreamDownload />
3. I created a second file of XML, simpleEntity.xml, that includes
simple.xml by using a external entity. The following 11 lines show the
contents of the file, except that I've added 4 spaces to the beginning of
each of the lines in this listing (that is, the prefixed spaces are not in
the file):
<?xml version="1.0" ?>
<!DOCTYPE just4ExternalEntity
[
<!ENTITY externalEntity SYSTEM "simple.xml">
]
>
<just4ExternalEntity>
<workingTag />
&externalEntity;
</just4ExternalEntity>
You can verify that this external entity stuff is done properly by opening
this second file in Internet Explorer. I see something like this, except
with better colors:
<?xml version="1.0" ?>
<!DOCTYPE just4ExternalEntity (View Source for full doctype...)>
- <just4ExternalEntity>
<workingTag />
<DatastreamDownload />
</just4ExternalEntity>
4. Finally, I copied the working xml from simpleEntity.xml into a SQL
variable, @.xmlDoc. I had to prefix a network path to "simple.xml", to give
the SQL Server a chance of locating simple.xml. I've represented this
network path by "\\unc..." below, although in my actual test I used a valid
(and triple-tested) unc.
Openxml can partially process this code. The first select statement below
reports on the "workingTag" tag, which is defined in the @.xmlDoc variable ,
but fails to report on the sibling "DatastreamDownload" tag, which is define
d
in the external entity.
-- attempt to read the same xml from a disk file using an external entity
declare @.xmlDoc varchar(1000)
set @.xmlDoc = -- code from simpleEntity.xml
'
<?xml version="1.0" ?>
<!DOCTYPE just4ExternalEntity
[
<!ENTITY externalEntity SYSTEM "\\unc...\simple.xml">
]
>
<just4ExternalEntity>
<workingTag />
&externalEntity;
</just4ExternalEntity>
'
declare @.jdoc int
declare @.rc int
exec @.rc = sp_xml_preparedocument @.jdoc output, @.xmlDoc
select @.rc as resultCode
-- lists row, as expected
select *
from openxml( @.jdoc, '/just4ExternalEntity/workingTag', 0)
-- fails to list row
select *
from openxml( @.jdoc, '/just4ExternalEntity/DatastreamDownload', 0)
exec sp_xml_removedocument @.jdoc
Here are the beginnings of the lines I see in SQL Query Analyzer:
resultCode
--
0
(1 row(s) affected)
id parentid nodetype localname
-- -- -- --
7 0 1 workingTag
(1 row(s) affected)
id parentid nodetype localname
-- -- -- --
(0 row(s) affected)
I was expecting to see 1 row in the last result set above, with
"DatastreamDownload" in the "localname" column.
So openxml is failing to process something that Internet Explorer has no
trouble with. As far as I know (and I'm no expert) external entities are a
standard part of XML, and a standards-conforming XML processor should handle
them. So it seems very likely that the processor used by openxml does handl
e
them. It's even possible that openxml and IE are using the same processor,
although that would quite a feat of coordination for a company the size of
Microsoft.
Since external entities are standard, and since IE issues a security warning
when I use one via a network unc, I'm suspecting that there is a privilege
problem in getting openxml to read the external entity.
Thanks for any help.External entities are not supported with OpenXML for a variety of security
related reasons.
Best is to resolve them on the client/midtier side before sending the XML to
the server.
Best regards
Michael
"HolmesDM" <HolmesDM@.discussions.microsoft.com> wrote in message
news:8AF86E16-03D4-49A7-9A20-8133C2B0E109@.microsoft.com...
>I am having a problem getting OPENXML to process some xml that contains an
> "external entity" in SQL Server 2000.
> Background, which you probably already know:
> --
> Xml uses "external entities" the way the C language uses "#include": when
> an xml processor finds the directive, it refers to an external file whose
> contents are to be processed at that point.
>
> I have created a minimal example that illustrates the problem.
> 1. First, here is some SQL that correctly processes some XML that does
> *not* use external entities. The XML is stored in a variable (@.testXml),
> and
> a select statement reads this XML and reports some basic facts about the
> only
> data in it, namely, that there is a tag called "DatastreamDownload ":
> -- demonstrate validity of simple xml
> declare @.testXml varchar(8000)
> set @.testXml ='
> <?xml version="1.0" ?>
> <DatastreamDownload />
> '
> declare @.idoc int
> exec sp_xml_preparedocument @.idoc output, @.testXml
> select *
> from openxml( @.idoc, '/DatastreamDownload', 0)
> exec sp_xml_removedocument @.idoc
>
> Here are the beginnings of the lines I see in SQL Query Analyzer:
> id parentid nodetype localname
> -- -- -- --
--
> 0 NULL 1
> DatastreamDownload
> (1 row(s) affected)
>
> 2. I then stored the XML from @.testXml in a file, simple.xml. The
> following 2 lines show the contents of the file, except that I've added 4
> spaces to the beginning of each of the lines in this listing (that is, the
> prefixed spaces are not in the file):
> <?xml version="1.0" ?>
> <DatastreamDownload />
>
> 3. I created a second file of XML, simpleEntity.xml, that includes
> simple.xml by using a external entity. The following 11 lines show the
> contents of the file, except that I've added 4 spaces to the beginning of
> each of the lines in this listing (that is, the prefixed spaces are not in
> the file):
> <?xml version="1.0" ?>
> <!DOCTYPE just4ExternalEntity
> [
> <!ENTITY externalEntity SYSTEM "simple.xml">
> ]
> <just4ExternalEntity>
> <workingTag />
> &externalEntity;
> </just4ExternalEntity>
> You can verify that this external entity stuff is done properly by opening
> this second file in Internet Explorer. I see something like this, except
> with better colors:
> <?xml version="1.0" ?>
> <!DOCTYPE just4ExternalEntity (View Source for full doctype...)>
> - <just4ExternalEntity>
> <workingTag />
> <DatastreamDownload />
> </just4ExternalEntity>
> 4. Finally, I copied the working xml from simpleEntity.xml into a SQL
> variable, @.xmlDoc. I had to prefix a network path to "simple.xml", to
> give
> the SQL Server a chance of locating simple.xml. I've represented this
> network path by "\\unc..." below, although in my actual test I used a
> valid
> (and triple-tested) unc.
> Openxml can partially process this code. The first select statement below
> reports on the "workingTag" tag, which is defined in the @.xmlDoc variable
> ,
> but fails to report on the sibling "DatastreamDownload" tag, which is
> defined
> in the external entity.
> -- attempt to read the same xml from a disk file using an external
> entity
> declare @.xmlDoc varchar(1000)
> set @.xmlDoc = -- code from simpleEntity.xml
> '
> <?xml version="1.0" ?>
> <!DOCTYPE just4ExternalEntity
> [
> <!ENTITY externalEntity SYSTEM "\\unc...\simple.xml">
> ]
> <just4ExternalEntity>
> <workingTag />
> &externalEntity;
> </just4ExternalEntity>
> '
> declare @.jdoc int
> declare @.rc int
> exec @.rc = sp_xml_preparedocument @.jdoc output, @.xmlDoc
> select @.rc as resultCode
> -- lists row, as expected
> select *
> from openxml( @.jdoc, '/just4ExternalEntity/workingTag', 0)
> -- fails to list row
> select *
> from openxml( @.jdoc, '/just4ExternalEntity/DatastreamDownload',
> 0)
> exec sp_xml_removedocument @.jdoc
>
> Here are the beginnings of the lines I see in SQL Query Analyzer:
> resultCode
> --
> 0
> (1 row(s) affected)
> id parentid nodetype localname
> -- -- -- --
> 7 0 1 workingTag
> (1 row(s) affected)
> id parentid nodetype localname
> -- -- -- --
> (0 row(s) affected)
> I was expecting to see 1 row in the last result set above, with
> "DatastreamDownload" in the "localname" column.
> So openxml is failing to process something that Internet Explorer has no
> trouble with. As far as I know (and I'm no expert) external entities are
> a
> standard part of XML, and a standards-conforming XML processor should
> handle
> them. So it seems very likely that the processor used by openxml does
> handle
> them. It's even possible that openxml and IE are using the same
> processor,
> although that would quite a feat of coordination for a company the size of
> Microsoft.
> Since external entities are standard, and since IE issues a security
> warning
> when I use one via a network unc, I'm suspecting that there is a privilege
> problem in getting openxml to read the external entity.
> Thanks for any help.|||Thanks for the definitive answer, even if it's not exactly the one I'd hoped
for.
"Michael Rys [MSFT]" wrote:
> External entities are not supported with OpenXML for a variety of security
> related reasons.
> Best is to resolve them on the client/midtier side before sending the XML
to
> the server.
> Best regards
> Michael
> "HolmesDM" <HolmesDM@.discussions.microsoft.com> wrote in message
> news:8AF86E16-03D4-49A7-9A20-8133C2B0E109@.microsoft.com...
>
>
"external entity" in SQL Server 2000.
Background, which you probably already know:
--
Xml uses "external entities" the way the C language uses "#include": when
an xml processor finds the directive, it refers to an external file whose
contents are to be processed at that point.
I have created a minimal example that illustrates the problem.
1. First, here is some SQL that correctly processes some XML that does
*not* use external entities. The XML is stored in a variable (@.testXml), an
d
a select statement reads this XML and reports some basic facts about the onl
y
data in it, namely, that there is a tag called "DatastreamDownload ":
-- demonstrate validity of simple xml
declare @.testXml varchar(8000)
set @.testXml ='
<?xml version="1.0" ?>
<DatastreamDownload />
'
declare @.idoc int
exec sp_xml_preparedocument @.idoc output, @.testXml
select *
from openxml( @.idoc, '/DatastreamDownload', 0)
exec sp_xml_removedocument @.idoc
Here are the beginnings of the lines I see in SQL Query Analyzer:
id parentid nodetype localname
-- -- -- --
0 NULL 1 DatastreamDownload
(1 row(s) affected)
2. I then stored the XML from @.testXml in a file, simple.xml. The
following 2 lines show the contents of the file, except that I've added 4
spaces to the beginning of each of the lines in this listing (that is, the
prefixed spaces are not in the file):
<?xml version="1.0" ?>
<DatastreamDownload />
3. I created a second file of XML, simpleEntity.xml, that includes
simple.xml by using a external entity. The following 11 lines show the
contents of the file, except that I've added 4 spaces to the beginning of
each of the lines in this listing (that is, the prefixed spaces are not in
the file):
<?xml version="1.0" ?>
<!DOCTYPE just4ExternalEntity
[
<!ENTITY externalEntity SYSTEM "simple.xml">
]
>
<just4ExternalEntity>
<workingTag />
&externalEntity;
</just4ExternalEntity>
You can verify that this external entity stuff is done properly by opening
this second file in Internet Explorer. I see something like this, except
with better colors:
<?xml version="1.0" ?>
<!DOCTYPE just4ExternalEntity (View Source for full doctype...)>
- <just4ExternalEntity>
<workingTag />
<DatastreamDownload />
</just4ExternalEntity>
4. Finally, I copied the working xml from simpleEntity.xml into a SQL
variable, @.xmlDoc. I had to prefix a network path to "simple.xml", to give
the SQL Server a chance of locating simple.xml. I've represented this
network path by "\\unc..." below, although in my actual test I used a valid
(and triple-tested) unc.
Openxml can partially process this code. The first select statement below
reports on the "workingTag" tag, which is defined in the @.xmlDoc variable ,
but fails to report on the sibling "DatastreamDownload" tag, which is define
d
in the external entity.
-- attempt to read the same xml from a disk file using an external entity
declare @.xmlDoc varchar(1000)
set @.xmlDoc = -- code from simpleEntity.xml
'
<?xml version="1.0" ?>
<!DOCTYPE just4ExternalEntity
[
<!ENTITY externalEntity SYSTEM "\\unc...\simple.xml">
]
>
<just4ExternalEntity>
<workingTag />
&externalEntity;
</just4ExternalEntity>
'
declare @.jdoc int
declare @.rc int
exec @.rc = sp_xml_preparedocument @.jdoc output, @.xmlDoc
select @.rc as resultCode
-- lists row, as expected
select *
from openxml( @.jdoc, '/just4ExternalEntity/workingTag', 0)
-- fails to list row
select *
from openxml( @.jdoc, '/just4ExternalEntity/DatastreamDownload', 0)
exec sp_xml_removedocument @.jdoc
Here are the beginnings of the lines I see in SQL Query Analyzer:
resultCode
--
0
(1 row(s) affected)
id parentid nodetype localname
-- -- -- --
7 0 1 workingTag
(1 row(s) affected)
id parentid nodetype localname
-- -- -- --
(0 row(s) affected)
I was expecting to see 1 row in the last result set above, with
"DatastreamDownload" in the "localname" column.
So openxml is failing to process something that Internet Explorer has no
trouble with. As far as I know (and I'm no expert) external entities are a
standard part of XML, and a standards-conforming XML processor should handle
them. So it seems very likely that the processor used by openxml does handl
e
them. It's even possible that openxml and IE are using the same processor,
although that would quite a feat of coordination for a company the size of
Microsoft.
Since external entities are standard, and since IE issues a security warning
when I use one via a network unc, I'm suspecting that there is a privilege
problem in getting openxml to read the external entity.
Thanks for any help.External entities are not supported with OpenXML for a variety of security
related reasons.
Best is to resolve them on the client/midtier side before sending the XML to
the server.
Best regards
Michael
"HolmesDM" <HolmesDM@.discussions.microsoft.com> wrote in message
news:8AF86E16-03D4-49A7-9A20-8133C2B0E109@.microsoft.com...
>I am having a problem getting OPENXML to process some xml that contains an
> "external entity" in SQL Server 2000.
> Background, which you probably already know:
> --
> Xml uses "external entities" the way the C language uses "#include": when
> an xml processor finds the directive, it refers to an external file whose
> contents are to be processed at that point.
>
> I have created a minimal example that illustrates the problem.
> 1. First, here is some SQL that correctly processes some XML that does
> *not* use external entities. The XML is stored in a variable (@.testXml),
> and
> a select statement reads this XML and reports some basic facts about the
> only
> data in it, namely, that there is a tag called "DatastreamDownload ":
> -- demonstrate validity of simple xml
> declare @.testXml varchar(8000)
> set @.testXml ='
> <?xml version="1.0" ?>
> <DatastreamDownload />
> '
> declare @.idoc int
> exec sp_xml_preparedocument @.idoc output, @.testXml
> select *
> from openxml( @.idoc, '/DatastreamDownload', 0)
> exec sp_xml_removedocument @.idoc
>
> Here are the beginnings of the lines I see in SQL Query Analyzer:
> id parentid nodetype localname
> -- -- -- --
--
> 0 NULL 1
> DatastreamDownload
> (1 row(s) affected)
>
> 2. I then stored the XML from @.testXml in a file, simple.xml. The
> following 2 lines show the contents of the file, except that I've added 4
> spaces to the beginning of each of the lines in this listing (that is, the
> prefixed spaces are not in the file):
> <?xml version="1.0" ?>
> <DatastreamDownload />
>
> 3. I created a second file of XML, simpleEntity.xml, that includes
> simple.xml by using a external entity. The following 11 lines show the
> contents of the file, except that I've added 4 spaces to the beginning of
> each of the lines in this listing (that is, the prefixed spaces are not in
> the file):
> <?xml version="1.0" ?>
> <!DOCTYPE just4ExternalEntity
> [
> <!ENTITY externalEntity SYSTEM "simple.xml">
> ]
> <just4ExternalEntity>
> <workingTag />
> &externalEntity;
> </just4ExternalEntity>
> You can verify that this external entity stuff is done properly by opening
> this second file in Internet Explorer. I see something like this, except
> with better colors:
> <?xml version="1.0" ?>
> <!DOCTYPE just4ExternalEntity (View Source for full doctype...)>
> - <just4ExternalEntity>
> <workingTag />
> <DatastreamDownload />
> </just4ExternalEntity>
> 4. Finally, I copied the working xml from simpleEntity.xml into a SQL
> variable, @.xmlDoc. I had to prefix a network path to "simple.xml", to
> give
> the SQL Server a chance of locating simple.xml. I've represented this
> network path by "\\unc..." below, although in my actual test I used a
> valid
> (and triple-tested) unc.
> Openxml can partially process this code. The first select statement below
> reports on the "workingTag" tag, which is defined in the @.xmlDoc variable
> ,
> but fails to report on the sibling "DatastreamDownload" tag, which is
> defined
> in the external entity.
> -- attempt to read the same xml from a disk file using an external
> entity
> declare @.xmlDoc varchar(1000)
> set @.xmlDoc = -- code from simpleEntity.xml
> '
> <?xml version="1.0" ?>
> <!DOCTYPE just4ExternalEntity
> [
> <!ENTITY externalEntity SYSTEM "\\unc...\simple.xml">
> ]
> <just4ExternalEntity>
> <workingTag />
> &externalEntity;
> </just4ExternalEntity>
> '
> declare @.jdoc int
> declare @.rc int
> exec @.rc = sp_xml_preparedocument @.jdoc output, @.xmlDoc
> select @.rc as resultCode
> -- lists row, as expected
> select *
> from openxml( @.jdoc, '/just4ExternalEntity/workingTag', 0)
> -- fails to list row
> select *
> from openxml( @.jdoc, '/just4ExternalEntity/DatastreamDownload',
> 0)
> exec sp_xml_removedocument @.jdoc
>
> Here are the beginnings of the lines I see in SQL Query Analyzer:
> resultCode
> --
> 0
> (1 row(s) affected)
> id parentid nodetype localname
> -- -- -- --
> 7 0 1 workingTag
> (1 row(s) affected)
> id parentid nodetype localname
> -- -- -- --
> (0 row(s) affected)
> I was expecting to see 1 row in the last result set above, with
> "DatastreamDownload" in the "localname" column.
> So openxml is failing to process something that Internet Explorer has no
> trouble with. As far as I know (and I'm no expert) external entities are
> a
> standard part of XML, and a standards-conforming XML processor should
> handle
> them. So it seems very likely that the processor used by openxml does
> handle
> them. It's even possible that openxml and IE are using the same
> processor,
> although that would quite a feat of coordination for a company the size of
> Microsoft.
> Since external entities are standard, and since IE issues a security
> warning
> when I use one via a network unc, I'm suspecting that there is a privilege
> problem in getting openxml to read the external entity.
> Thanks for any help.|||Thanks for the definitive answer, even if it's not exactly the one I'd hoped
for.
"Michael Rys [MSFT]" wrote:
> External entities are not supported with OpenXML for a variety of security
> related reasons.
> Best is to resolve them on the client/midtier side before sending the XML
to
> the server.
> Best regards
> Michael
> "HolmesDM" <HolmesDM@.discussions.microsoft.com> wrote in message
> news:8AF86E16-03D4-49A7-9A20-8133C2B0E109@.microsoft.com...
>
>
openxml seemingly fails to process external entity
I am having a problem getting OPENXML to process some xml that contains an
"external entity" in SQL Server 2000.
Background, which you probably already know:
Xml uses "external entities" the way the C language uses "#include": when
an xml processor finds the directive, it refers to an external file whose
contents are to be processed at that point.
I have created a minimal example that illustrates the problem.
1. First, here is some SQL that correctly processes some XML that does
*not* use external entities. The XML is stored in a variable (@.testXml), and
a select statement reads this XML and reports some basic facts about the only
data in it, namely, that there is a tag called "DatastreamDownload ":
-- demonstrate validity of simple xml
declare @.testXml varchar(8000)
set @.testXml ='
<?xml version="1.0" ?>
<DatastreamDownload />
'
declare @.idoc int
exec sp_xml_preparedocument @.idoc output, @.testXml
select *
from openxml( @.idoc, '/DatastreamDownload', 0)
exec sp_xml_removedocument @.idoc
Here are the beginnings of the lines I see in SQL Query Analyzer:
id parentid nodetype localname
-- -- -- --
0 NULL 1 DatastreamDownload
(1 row(s) affected)
2. I then stored the XML from @.testXml in a file, simple.xml. The
following 2 lines show the contents of the file, except that I've added 4
spaces to the beginning of each of the lines in this listing (that is, the
prefixed spaces are not in the file):
<?xml version="1.0" ?>
<DatastreamDownload />
3. I created a second file of XML, simpleEntity.xml, that includes
simple.xml by using a external entity. The following 11 lines show the
contents of the file, except that I've added 4 spaces to the beginning of
each of the lines in this listing (that is, the prefixed spaces are not in
the file):
<?xml version="1.0" ?>
<!DOCTYPE just4ExternalEntity
[
<!ENTITY externalEntity SYSTEM "simple.xml">
]
>
<just4ExternalEntity>
<workingTag />
&externalEntity;
</just4ExternalEntity>
You can verify that this external entity stuff is done properly by opening
this second file in Internet Explorer. I see something like this, except
with better colors:
<?xml version="1.0" ?>
<!DOCTYPE just4ExternalEntity (View Source for full doctype...)>
- <just4ExternalEntity>
<workingTag />
<DatastreamDownload />
</just4ExternalEntity>
4. Finally, I copied the working xml from simpleEntity.xml into a SQL
variable, @.xmlDoc. I had to prefix a network path to "simple.xml", to give
the SQL Server a chance of locating simple.xml. I've represented this
network path by "\\unc..." below, although in my actual test I used a valid
(and triple-tested) unc.
Openxml can partially process this code. The first select statement below
reports on the "workingTag" tag, which is defined in the @.xmlDoc variable ,
but fails to report on the sibling "DatastreamDownload" tag, which is defined
in the external entity.
-- attempt to read the same xml from a disk file using an external entity
declare @.xmlDoc varchar(1000)
set @.xmlDoc = -- code from simpleEntity.xml
'
<?xml version="1.0" ?>
<!DOCTYPE just4ExternalEntity
[
<!ENTITY externalEntity SYSTEM "\\unc...\simple.xml">
]
>
<just4ExternalEntity>
<workingTag />
&externalEntity;
</just4ExternalEntity>
'
declare @.jdoc int
declare @.rc int
exec @.rc = sp_xml_preparedocument @.jdoc output, @.xmlDoc
select @.rc as resultCode
-- lists row, as expected
select *
from openxml( @.jdoc, '/just4ExternalEntity/workingTag', 0)
-- fails to list row
select *
from openxml( @.jdoc, '/just4ExternalEntity/DatastreamDownload', 0)
exec sp_xml_removedocument @.jdoc
Here are the beginnings of the lines I see in SQL Query Analyzer:
resultCode
0
(1 row(s) affected)
id parentid nodetype localname
-- -- -- --
7 0 1 workingTag
(1 row(s) affected)
id parentid nodetype localname
-- -- -- --
(0 row(s) affected)
I was expecting to see 1 row in the last result set above, with
"DatastreamDownload" in the "localname" column.
So openxml is failing to process something that Internet Explorer has no
trouble with. As far as I know (and I'm no expert) external entities are a
standard part of XML, and a standards-conforming XML processor should handle
them. So it seems very likely that the processor used by openxml does handle
them. It's even possible that openxml and IE are using the same processor,
although that would quite a feat of coordination for a company the size of
Microsoft.
Since external entities are standard, and since IE issues a security warning
when I use one via a network unc, I'm suspecting that there is a privilege
problem in getting openxml to read the external entity.
Thanks for any help.
External entities are not supported with OpenXML for a variety of security
related reasons.
Best is to resolve them on the client/midtier side before sending the XML to
the server.
Best regards
Michael
"HolmesDM" <HolmesDM@.discussions.microsoft.com> wrote in message
news:8AF86E16-03D4-49A7-9A20-8133C2B0E109@.microsoft.com...
>I am having a problem getting OPENXML to process some xml that contains an
> "external entity" in SQL Server 2000.
> Background, which you probably already know:
> --
> Xml uses "external entities" the way the C language uses "#include": when
> an xml processor finds the directive, it refers to an external file whose
> contents are to be processed at that point.
>
> I have created a minimal example that illustrates the problem.
> 1. First, here is some SQL that correctly processes some XML that does
> *not* use external entities. The XML is stored in a variable (@.testXml),
> and
> a select statement reads this XML and reports some basic facts about the
> only
> data in it, namely, that there is a tag called "DatastreamDownload ":
> -- demonstrate validity of simple xml
> declare @.testXml varchar(8000)
> set @.testXml ='
> <?xml version="1.0" ?>
> <DatastreamDownload />
> '
> declare @.idoc int
> exec sp_xml_preparedocument @.idoc output, @.testXml
> select *
> from openxml( @.idoc, '/DatastreamDownload', 0)
> exec sp_xml_removedocument @.idoc
>
> Here are the beginnings of the lines I see in SQL Query Analyzer:
> id parentid nodetype localname
> -- -- -- --
> 0 NULL 1
> DatastreamDownload
> (1 row(s) affected)
>
> 2. I then stored the XML from @.testXml in a file, simple.xml. The
> following 2 lines show the contents of the file, except that I've added 4
> spaces to the beginning of each of the lines in this listing (that is, the
> prefixed spaces are not in the file):
> <?xml version="1.0" ?>
> <DatastreamDownload />
>
> 3. I created a second file of XML, simpleEntity.xml, that includes
> simple.xml by using a external entity. The following 11 lines show the
> contents of the file, except that I've added 4 spaces to the beginning of
> each of the lines in this listing (that is, the prefixed spaces are not in
> the file):
> <?xml version="1.0" ?>
> <!DOCTYPE just4ExternalEntity
> [
> <!ENTITY externalEntity SYSTEM "simple.xml">
> ]
> <just4ExternalEntity>
> <workingTag />
> &externalEntity;
> </just4ExternalEntity>
> You can verify that this external entity stuff is done properly by opening
> this second file in Internet Explorer. I see something like this, except
> with better colors:
> <?xml version="1.0" ?>
> <!DOCTYPE just4ExternalEntity (View Source for full doctype...)>
> - <just4ExternalEntity>
> <workingTag />
> <DatastreamDownload />
> </just4ExternalEntity>
> 4. Finally, I copied the working xml from simpleEntity.xml into a SQL
> variable, @.xmlDoc. I had to prefix a network path to "simple.xml", to
> give
> the SQL Server a chance of locating simple.xml. I've represented this
> network path by "\\unc..." below, although in my actual test I used a
> valid
> (and triple-tested) unc.
> Openxml can partially process this code. The first select statement below
> reports on the "workingTag" tag, which is defined in the @.xmlDoc variable
> ,
> but fails to report on the sibling "DatastreamDownload" tag, which is
> defined
> in the external entity.
> -- attempt to read the same xml from a disk file using an external
> entity
> declare @.xmlDoc varchar(1000)
> set @.xmlDoc = -- code from simpleEntity.xml
> '
> <?xml version="1.0" ?>
> <!DOCTYPE just4ExternalEntity
> [
> <!ENTITY externalEntity SYSTEM "\\unc...\simple.xml">
> ]
> <just4ExternalEntity>
> <workingTag />
> &externalEntity;
> </just4ExternalEntity>
> '
> declare @.jdoc int
> declare @.rc int
> exec @.rc = sp_xml_preparedocument @.jdoc output, @.xmlDoc
> select @.rc as resultCode
> -- lists row, as expected
> select *
> from openxml( @.jdoc, '/just4ExternalEntity/workingTag', 0)
> -- fails to list row
> select *
> from openxml( @.jdoc, '/just4ExternalEntity/DatastreamDownload',
> 0)
> exec sp_xml_removedocument @.jdoc
>
> Here are the beginnings of the lines I see in SQL Query Analyzer:
> resultCode
> --
> 0
> (1 row(s) affected)
> id parentid nodetype localname
> -- -- -- --
> 7 0 1 workingTag
> (1 row(s) affected)
> id parentid nodetype localname
> -- -- -- --
> (0 row(s) affected)
> I was expecting to see 1 row in the last result set above, with
> "DatastreamDownload" in the "localname" column.
> So openxml is failing to process something that Internet Explorer has no
> trouble with. As far as I know (and I'm no expert) external entities are
> a
> standard part of XML, and a standards-conforming XML processor should
> handle
> them. So it seems very likely that the processor used by openxml does
> handle
> them. It's even possible that openxml and IE are using the same
> processor,
> although that would quite a feat of coordination for a company the size of
> Microsoft.
> Since external entities are standard, and since IE issues a security
> warning
> when I use one via a network unc, I'm suspecting that there is a privilege
> problem in getting openxml to read the external entity.
> Thanks for any help.
|||Thanks for the definitive answer, even if it's not exactly the one I'd hoped
for.
"Michael Rys [MSFT]" wrote:
> External entities are not supported with OpenXML for a variety of security
> related reasons.
> Best is to resolve them on the client/midtier side before sending the XML to
> the server.
> Best regards
> Michael
> "HolmesDM" <HolmesDM@.discussions.microsoft.com> wrote in message
> news:8AF86E16-03D4-49A7-9A20-8133C2B0E109@.microsoft.com...
>
>
"external entity" in SQL Server 2000.
Background, which you probably already know:
Xml uses "external entities" the way the C language uses "#include": when
an xml processor finds the directive, it refers to an external file whose
contents are to be processed at that point.
I have created a minimal example that illustrates the problem.
1. First, here is some SQL that correctly processes some XML that does
*not* use external entities. The XML is stored in a variable (@.testXml), and
a select statement reads this XML and reports some basic facts about the only
data in it, namely, that there is a tag called "DatastreamDownload ":
-- demonstrate validity of simple xml
declare @.testXml varchar(8000)
set @.testXml ='
<?xml version="1.0" ?>
<DatastreamDownload />
'
declare @.idoc int
exec sp_xml_preparedocument @.idoc output, @.testXml
select *
from openxml( @.idoc, '/DatastreamDownload', 0)
exec sp_xml_removedocument @.idoc
Here are the beginnings of the lines I see in SQL Query Analyzer:
id parentid nodetype localname
-- -- -- --
0 NULL 1 DatastreamDownload
(1 row(s) affected)
2. I then stored the XML from @.testXml in a file, simple.xml. The
following 2 lines show the contents of the file, except that I've added 4
spaces to the beginning of each of the lines in this listing (that is, the
prefixed spaces are not in the file):
<?xml version="1.0" ?>
<DatastreamDownload />
3. I created a second file of XML, simpleEntity.xml, that includes
simple.xml by using a external entity. The following 11 lines show the
contents of the file, except that I've added 4 spaces to the beginning of
each of the lines in this listing (that is, the prefixed spaces are not in
the file):
<?xml version="1.0" ?>
<!DOCTYPE just4ExternalEntity
[
<!ENTITY externalEntity SYSTEM "simple.xml">
]
>
<just4ExternalEntity>
<workingTag />
&externalEntity;
</just4ExternalEntity>
You can verify that this external entity stuff is done properly by opening
this second file in Internet Explorer. I see something like this, except
with better colors:
<?xml version="1.0" ?>
<!DOCTYPE just4ExternalEntity (View Source for full doctype...)>
- <just4ExternalEntity>
<workingTag />
<DatastreamDownload />
</just4ExternalEntity>
4. Finally, I copied the working xml from simpleEntity.xml into a SQL
variable, @.xmlDoc. I had to prefix a network path to "simple.xml", to give
the SQL Server a chance of locating simple.xml. I've represented this
network path by "\\unc..." below, although in my actual test I used a valid
(and triple-tested) unc.
Openxml can partially process this code. The first select statement below
reports on the "workingTag" tag, which is defined in the @.xmlDoc variable ,
but fails to report on the sibling "DatastreamDownload" tag, which is defined
in the external entity.
-- attempt to read the same xml from a disk file using an external entity
declare @.xmlDoc varchar(1000)
set @.xmlDoc = -- code from simpleEntity.xml
'
<?xml version="1.0" ?>
<!DOCTYPE just4ExternalEntity
[
<!ENTITY externalEntity SYSTEM "\\unc...\simple.xml">
]
>
<just4ExternalEntity>
<workingTag />
&externalEntity;
</just4ExternalEntity>
'
declare @.jdoc int
declare @.rc int
exec @.rc = sp_xml_preparedocument @.jdoc output, @.xmlDoc
select @.rc as resultCode
-- lists row, as expected
select *
from openxml( @.jdoc, '/just4ExternalEntity/workingTag', 0)
-- fails to list row
select *
from openxml( @.jdoc, '/just4ExternalEntity/DatastreamDownload', 0)
exec sp_xml_removedocument @.jdoc
Here are the beginnings of the lines I see in SQL Query Analyzer:
resultCode
0
(1 row(s) affected)
id parentid nodetype localname
-- -- -- --
7 0 1 workingTag
(1 row(s) affected)
id parentid nodetype localname
-- -- -- --
(0 row(s) affected)
I was expecting to see 1 row in the last result set above, with
"DatastreamDownload" in the "localname" column.
So openxml is failing to process something that Internet Explorer has no
trouble with. As far as I know (and I'm no expert) external entities are a
standard part of XML, and a standards-conforming XML processor should handle
them. So it seems very likely that the processor used by openxml does handle
them. It's even possible that openxml and IE are using the same processor,
although that would quite a feat of coordination for a company the size of
Microsoft.
Since external entities are standard, and since IE issues a security warning
when I use one via a network unc, I'm suspecting that there is a privilege
problem in getting openxml to read the external entity.
Thanks for any help.
External entities are not supported with OpenXML for a variety of security
related reasons.
Best is to resolve them on the client/midtier side before sending the XML to
the server.
Best regards
Michael
"HolmesDM" <HolmesDM@.discussions.microsoft.com> wrote in message
news:8AF86E16-03D4-49A7-9A20-8133C2B0E109@.microsoft.com...
>I am having a problem getting OPENXML to process some xml that contains an
> "external entity" in SQL Server 2000.
> Background, which you probably already know:
> --
> Xml uses "external entities" the way the C language uses "#include": when
> an xml processor finds the directive, it refers to an external file whose
> contents are to be processed at that point.
>
> I have created a minimal example that illustrates the problem.
> 1. First, here is some SQL that correctly processes some XML that does
> *not* use external entities. The XML is stored in a variable (@.testXml),
> and
> a select statement reads this XML and reports some basic facts about the
> only
> data in it, namely, that there is a tag called "DatastreamDownload ":
> -- demonstrate validity of simple xml
> declare @.testXml varchar(8000)
> set @.testXml ='
> <?xml version="1.0" ?>
> <DatastreamDownload />
> '
> declare @.idoc int
> exec sp_xml_preparedocument @.idoc output, @.testXml
> select *
> from openxml( @.idoc, '/DatastreamDownload', 0)
> exec sp_xml_removedocument @.idoc
>
> Here are the beginnings of the lines I see in SQL Query Analyzer:
> id parentid nodetype localname
> -- -- -- --
> 0 NULL 1
> DatastreamDownload
> (1 row(s) affected)
>
> 2. I then stored the XML from @.testXml in a file, simple.xml. The
> following 2 lines show the contents of the file, except that I've added 4
> spaces to the beginning of each of the lines in this listing (that is, the
> prefixed spaces are not in the file):
> <?xml version="1.0" ?>
> <DatastreamDownload />
>
> 3. I created a second file of XML, simpleEntity.xml, that includes
> simple.xml by using a external entity. The following 11 lines show the
> contents of the file, except that I've added 4 spaces to the beginning of
> each of the lines in this listing (that is, the prefixed spaces are not in
> the file):
> <?xml version="1.0" ?>
> <!DOCTYPE just4ExternalEntity
> [
> <!ENTITY externalEntity SYSTEM "simple.xml">
> ]
> <just4ExternalEntity>
> <workingTag />
> &externalEntity;
> </just4ExternalEntity>
> You can verify that this external entity stuff is done properly by opening
> this second file in Internet Explorer. I see something like this, except
> with better colors:
> <?xml version="1.0" ?>
> <!DOCTYPE just4ExternalEntity (View Source for full doctype...)>
> - <just4ExternalEntity>
> <workingTag />
> <DatastreamDownload />
> </just4ExternalEntity>
> 4. Finally, I copied the working xml from simpleEntity.xml into a SQL
> variable, @.xmlDoc. I had to prefix a network path to "simple.xml", to
> give
> the SQL Server a chance of locating simple.xml. I've represented this
> network path by "\\unc..." below, although in my actual test I used a
> valid
> (and triple-tested) unc.
> Openxml can partially process this code. The first select statement below
> reports on the "workingTag" tag, which is defined in the @.xmlDoc variable
> ,
> but fails to report on the sibling "DatastreamDownload" tag, which is
> defined
> in the external entity.
> -- attempt to read the same xml from a disk file using an external
> entity
> declare @.xmlDoc varchar(1000)
> set @.xmlDoc = -- code from simpleEntity.xml
> '
> <?xml version="1.0" ?>
> <!DOCTYPE just4ExternalEntity
> [
> <!ENTITY externalEntity SYSTEM "\\unc...\simple.xml">
> ]
> <just4ExternalEntity>
> <workingTag />
> &externalEntity;
> </just4ExternalEntity>
> '
> declare @.jdoc int
> declare @.rc int
> exec @.rc = sp_xml_preparedocument @.jdoc output, @.xmlDoc
> select @.rc as resultCode
> -- lists row, as expected
> select *
> from openxml( @.jdoc, '/just4ExternalEntity/workingTag', 0)
> -- fails to list row
> select *
> from openxml( @.jdoc, '/just4ExternalEntity/DatastreamDownload',
> 0)
> exec sp_xml_removedocument @.jdoc
>
> Here are the beginnings of the lines I see in SQL Query Analyzer:
> resultCode
> --
> 0
> (1 row(s) affected)
> id parentid nodetype localname
> -- -- -- --
> 7 0 1 workingTag
> (1 row(s) affected)
> id parentid nodetype localname
> -- -- -- --
> (0 row(s) affected)
> I was expecting to see 1 row in the last result set above, with
> "DatastreamDownload" in the "localname" column.
> So openxml is failing to process something that Internet Explorer has no
> trouble with. As far as I know (and I'm no expert) external entities are
> a
> standard part of XML, and a standards-conforming XML processor should
> handle
> them. So it seems very likely that the processor used by openxml does
> handle
> them. It's even possible that openxml and IE are using the same
> processor,
> although that would quite a feat of coordination for a company the size of
> Microsoft.
> Since external entities are standard, and since IE issues a security
> warning
> when I use one via a network unc, I'm suspecting that there is a privilege
> problem in getting openxml to read the external entity.
> Thanks for any help.
|||Thanks for the definitive answer, even if it's not exactly the one I'd hoped
for.
"Michael Rys [MSFT]" wrote:
> External entities are not supported with OpenXML for a variety of security
> related reasons.
> Best is to resolve them on the client/midtier side before sending the XML to
> the server.
> Best regards
> Michael
> "HolmesDM" <HolmesDM@.discussions.microsoft.com> wrote in message
> news:8AF86E16-03D4-49A7-9A20-8133C2B0E109@.microsoft.com...
>
>
Subscribe to:
Posts (Atom)