Optimizing a large

Hi
I'm running a script that updates the same column on every row in a huge table. The table is being updated with data from another table in another database, but they are in the same SQL instance
The log and data files for the database being read from are on an internal drive (D:\). The log and datafiles for the database being updated are on an external SAN (X:\
Would any of the following speed the process up significantly
1)Remove the indexes from the table being updated
2)Set the recovery model to 'simple' on the database being updated
3)Put the log and data files on the database being updated on different physical disks (not necessary i believe if I perform number 2)
thanks
KevinKevin,
1. If the column that you are updating has an index on it you might
consider dropping it until after the update.
2. This won't affect in any way the number of items logged or the amount of
data logged during an Update. If you do the update in one transaction it
won't matter either way.
3. It's always best to place the Log and Data files on separate drive
arrays. It makes no difference where the log for the one being read from
are since the log is not used for reads.
I would recommend you attempt the Updates in smaller batches. This will
keep the log in check and usually results in a faster overall operation.
You can usually achieve this with a loop of some sort.
--
Andrew J. Kelly
SQL Server MVP
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:715A5D36-5F4F-43D5-8B5B-854B711F3A9A@.microsoft.com...
> Hi,
> I'm running a script that updates the same column on every row in a huge
table. The table is being updated with data from another table in another
database, but they are in the same SQL instance.
> The log and data files for the database being read from are on an internal
drive (D:\). The log and datafiles for the database being updated are on an
external SAN (X:\)
> Would any of the following speed the process up significantly?
> 1)Remove the indexes from the table being updated?
> 2)Set the recovery model to 'simple' on the database being updated?
> 3)Put the log and data files on the database being updated on different
physical disks (not necessary i believe if I perform number 2).
> thanks,
> Kevin|||Thanks for your response
Does the frequency of log checkpoints significantly slow down a long running update statement? Should I create a large transaction log that will not need to dynamically grow and set the recovery interval to a high value so that the frequency of checkpoints is reduced
thanks
Kevin|||Kevin,
First off you should always have the log file larger than it needs to be for
any given operation. Anytime it has to grow it will impact performance to
some degree. As for check points that depends. Checkpoints certainly can
add overhead to the system, especially disk IO and CPU. Whether they have a
large negative effect on your situation is hard to say from here. If you
make a large recovery interval it will most definitely adversely affect the
other users when it does happen as there will be a lot more to do at one
time. The key in your situation is to do the updates in smaller batches.
Andrew J. Kelly
SQL Server MVP
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:4A65CF81-2DD6-4F53-AA7A-E7B00FF50CE2@.microsoft.com...
> Thanks for your response.
> Does the frequency of log checkpoints significantly slow down a long
running update statement? Should I create a large transaction log that will
not need to dynamically grow and set the recovery interval to a high value
so that the frequency of checkpoints is reduced?
> thanks,
> Kevin

Optimizing a large

Hi,
I'm running a script that updates the same column on every row in a huge tab
le. The table is being updated with data from another table in another datab
ase, but they are in the same SQL instance.
The log and data files for the database being read from are on an internal d
rive (D:\). The log and datafiles for the database being updated are on an e
xternal SAN (X:\)
Would any of the following speed the process up significantly?
1)Remove the indexes from the table being updated?
2)Set the recovery model to 'simple' on the database being updated?
3)Put the log and data files on the database being updated on different phys
ical disks (not necessary i believe if I perform number 2).
thanks,
KevinKevin,
1. If the column that you are updating has an index on it you might
consider dropping it until after the update.
2. This won't affect in any way the number of items logged or the amount of
data logged during an Update. If you do the update in one transaction it
won't matter either way.
3. It's always best to place the Log and Data files on separate drive
arrays. It makes no difference where the log for the one being read from
are since the log is not used for reads.
I would recommend you attempt the Updates in smaller batches. This will
keep the log in check and usually results in a faster overall operation.
You can usually achieve this with a loop of some sort.
Andrew J. Kelly
SQL Server MVP
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:715A5D36-5F4F-43D5-8B5B-854B711F3A9A@.microsoft.com...

quote:

---

> Hi,
> I'm running a script that updates the same column on every row in a huge

---

table. The table is being updated with data from another table in another
database, but they are in the same SQL instance.

quote:

---

> The log and data files for the database being read from are on an internal

---

drive (D:\). The log and datafiles for the database being updated are on an
external SAN (X:\)

quote:

---

> Would any of the following speed the process up significantly?
> 1)Remove the indexes from the table being updated?
> 2)Set the recovery model to 'simple' on the database being updated?
> 3)Put the log and data files on the database being updated on different

---

physical disks (not necessary i believe if I perform number 2).

quote:

---

> thanks,
> Kevin

---

|||Thanks for your response.
Does the frequency of log checkpoints significantly slow down a long running
update statement? Should I create a large transaction log that will not nee
d to dynamically grow and set the recovery interval to a high value so that
the frequency of checkpoint
s is reduced?
thanks,
Kevin|||Kevin,
First off you should always have the log file larger than it needs to be for
any given operation. Anytime it has to grow it will impact performance to
some degree. As for check points that depends. Checkpoints certainly can
add overhead to the system, especially disk IO and CPU. Whether they have a
large negative effect on your situation is hard to say from here. If you
make a large recovery interval it will most definitely adversely affect the
other users when it does happen as there will be a lot more to do at one
time. The key in your situation is to do the updates in smaller batches.
Andrew J. Kelly
SQL Server MVP
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:4A65CF81-2DD6-4F53-AA7A-E7B00FF50CE2@.microsoft.com...

quote:

---

> Thanks for your response.
> Does the frequency of log checkpoints significantly slow down a long

---

running update statement? Should I create a large transaction log that will
not need to dynamically grow and set the recovery interval to a high value
so that the frequency of checkpoints is reduced?

quote:

---

> thanks,
> Kevin

---

optimizer problem

Hi,
We have a table having 3.2 million rows having primary key
clustered index on id column ...update statistics is done
with fullscan(100%)...
when we are running:
select count(*) from table1 ...it is taking about 4
minutes to return the result...when i see the statistics
io it shows that it is doing scan count:728...
How can this be doing scan count 728 on 2 cpu machine and
takes 4 min just to return count?
Thanks
--HarvinderIf it actually is a scan count of 728, that is not the same as Logical
reads. It means that SQL Server is accessing the table 728 times, and this
usually implies some sort of join.
Can you SET STATISTICS PROFILE ON and show us the output so we can see the
query plan in addition to the statistics?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"harvinder" <hs@.metratech.com> wrote in message
news:026401c3522b$6b4cea20$a601280a@.phx.gbl...
> Hi,
> We have a table having 3.2 million rows having primary key
> clustered index on id column ...update statistics is done
> with fullscan(100%)...
> when we are running:
> select count(*) from table1 ...it is taking about 4
> minutes to return the result...when i see the statistics
> io it shows that it is doing scan count:728...
> How can this be doing scan count 728 on 2 cpu machine and
> takes 4 min just to return count?
> Thanks
> --Harvinder
>|||That was my other question...howcome it is doing 728 scan
count instead of 1 clustered index scan...i am pasting the
output of showplan :
select count(*) from tab1
|--Compute Scalar(DEFINE:([Expr1002]=Convert
([globalagg1004])))
|--Stream Aggregate(DEFINE:([globalagg1004]=SUM
([partialagg1003])))
|--Parallelism(Gather Streams)
|--Stream Aggregate(DEFINE:
([partialagg1003]=Count(*)))
|--Clustered Index Scan(OBJECT:([dm].
[dbo].[tab1].[pk_tab1]))
Thanks
--Harvinder
>--Original Message--
>If it actually is a scan count of 728, that is not the
same as Logical
>reads. It means that SQL Server is accessing the table
728 times, and this
>usually implies some sort of join.
>Can you SET STATISTICS PROFILE ON and show us the output
so we can see the
>query plan in addition to the statistics?
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"harvinder" <hs@.metratech.com> wrote in message
>news:026401c3522b$6b4cea20$a601280a@.phx.gbl...
>> Hi,
>> We have a table having 3.2 million rows having primary
key
>> clustered index on id column ...update statistics is
done
>> with fullscan(100%)...
>> when we are running:
>> select count(*) from table1 ...it is taking about 4
>> minutes to return the result...when i see the
statistics
>> io it shows that it is doing scan count:728...
>> How can this be doing scan count 728 on 2 cpu machine
and
>> takes 4 min just to return count?
>> Thanks
>> --Harvinder
>
>.
>|||I was actually hoping for the STATISTICS PROFILE output in addition to the
exact STATISTICS IO that I assumed you were already collecting.
My guess at this point (without seeing the STATISTICS IO output) is that
the high scan count is related to the fact that the query is being processed
in parallel.
The large amount of time is probably because of the clustered index scan. A
clustered index scan is exactly the same as a table scan, so to get the
results of count(*) SQL Server has to look at every row on every page. How
many rows and how many pages are in this table? Does the query include a
WHERE clause? What is the result of your count(*) query?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"harvinder" <hs@.metratech.com> wrote in message
news:051501c35230$b8aed800$a301280a@.phx.gbl...
> That was my other question...howcome it is doing 728 scan
> count instead of 1 clustered index scan...i am pasting the
> output of showplan :
> select count(*) from tab1
> |--Compute Scalar(DEFINE:([Expr1002]=Convert
> ([globalagg1004])))
> |--Stream Aggregate(DEFINE:([globalagg1004]=SUM
> ([partialagg1003])))
> |--Parallelism(Gather Streams)
> |--Stream Aggregate(DEFINE:
> ([partialagg1003]=Count(*)))
> |--Clustered Index Scan(OBJECT:([dm].
> [dbo].[tab1].[pk_tab1]))
> Thanks
> --Harvinder
> >--Original Message--
> >If it actually is a scan count of 728, that is not the
> same as Logical
> >reads. It means that SQL Server is accessing the table
> 728 times, and this
> >usually implies some sort of join.
> >Can you SET STATISTICS PROFILE ON and show us the output
> so we can see the
> >query plan in addition to the statistics?
> >
> >--
> >HTH
> >--
> >Kalen Delaney
> >SQL Server MVP
> >www.SolidQualityLearning.com
> >
> >
> >"harvinder" <hs@.metratech.com> wrote in message
> >news:026401c3522b$6b4cea20$a601280a@.phx.gbl...
> >> Hi,
> >>
> >> We have a table having 3.2 million rows having primary
> key
> >> clustered index on id column ...update statistics is
> done
> >> with fullscan(100%)...
> >> when we are running:
> >> select count(*) from table1 ...it is taking about 4
> >> minutes to return the result...when i see the
> statistics
> >> io it shows that it is doing scan count:728...
> >> How can this be doing scan count 728 on 2 cpu machine
> and
> >> takes 4 min just to return count?
> >>
> >> Thanks
> >> --Harvinder
> >>
> >
> >
> >.
> >|||if your system is a Xeon or Xeon MP, and HT is enabled,
and you have a parallel execution plan
try OPTION (MAXDOP 1)
better yet, disabled HT
>--Original Message--
>Hi,
>We have a table having 3.2 million rows having primary
key
>clustered index on id column ...update statistics is done
>with fullscan(100%)...
>when we are running:
>select count(*) from table1 ...it is taking about 4
>minutes to return the result...when i see the statistics
>io it shows that it is doing scan count:728...
>How can this be doing scan count 728 on 2 cpu machine and
>takes 4 min just to return count?
>Thanks
>--Harvinder
>.
>

opinion on preventing duplicate record insertion

Hi, i need an opinion on this...to prevent the duplicate record in db,i am using unique constraints for a column or combination of column as the case may be.By reading this articlehttp://aspalliance.com/687_Preventing_Duplicate_Record_Insertion_on_Page_Refresh.5 , i get the feeling that its not such a good idea..i am wondering,what does it imply?Does it mean that unique constraints are not reliable enough?Does it mean,it may break and let the duplicate record inserted,even though its not suppose to?I am using SQL server 2005

I have read Dino's article on dup recs and i have still not understood it completely.. i am looking for some not so complex ,full proof method,to prevent duplicate record insertion by clicking refresh or multiple (careless)clicking on submit...thanks ..

You essentially have two points of validation for the duplication.. at the code level, and at the database level.
You should employ both to good effect so that you do not get any and more importantly you control what happens when it already exists.

Unique constraints work - if you've set the correct ones, you will never get a duplicate entry in the database.
You should make sure you have them set.

Next, on a stored procedure that inserts the record, you can either try to insert and an error will occur because of the constraint and a SqlException will be generated that will bubble back to your code.
OR, you can use the IF EXISTS code that is suggested in the link you sent.
This checks if those valuse already exists in the database and doesn't try the insert.
I would advise that you also apply this to your logic.

On the code level, you can store Session or Viewstate Variables to flag that the update has happened as suggested in the fourth page of that article.

So you can incorporate all three so that

1. It checks the session variable and doesn't allow for duplicates
2. If it "slips the net" here, OR you later change your UI code, the stored procedure IF EXISTS catches and handles
3. If logic later changes, different sp used, your Unique Constraint will eventualy catch and throw an exception.

You are ensuing at all points that the problem cannot occur.

|||

"unique constraints" are reliable - the problem is handling the duplication condition. In my opinion the first method in the article is the preferred method - that of detecting the duplicate before the constraint does.

Operation on DateTime

Hi there
Presently I have a table with a DateTime column as the following:
Col1
2003/12/01 00:05:00
2003/12/01 00:10:00
2003/12/01 00:15:00
2003/12/01 00:20:00
What I want to do is to separate the date and time into two columns,
so it becomes:
Col1 Col2 Col3
2003/12/01 00:05:00 2003/12/01 00:05:00
2003/12/01 00:10:00 2003/12/01 00:10:00
2003/12/01 00:15:00 2003/12/01 00:15:00
2003/12/01 00:20:00 2003/12/01 00:20:00
.
.
..
I am wondering which way is easier - making a program to do this (e.g. in
C#)
or using SQL queries. Thanks for any suggestions.
YuelinDepends where you need the values, you can use the RIGHT Function for that,
just use aa format that also represents the time format and Cut of the 8
right Characters from it, therell you be.
Or you go by:
Select Getdate(),
Convert(varchar(10),getdate(),111),Conve
rt(varchar(10),getdate(),114)
But IMHO formatting should be a client work.
--
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Yuelin Liang" <yuelinliang@.hotmail.com> schrieb im Newsbeitrag
news:uacpnCsWFHA.3184@.TK2MSFTNGP15.phx.gbl...
> Hi there
> Presently I have a table with a DateTime column as the following:
> Col1
> 2003/12/01 00:05:00
> 2003/12/01 00:10:00
> 2003/12/01 00:15:00
> 2003/12/01 00:20:00
>
> What I want to do is to separate the date and time into two columns,
> so it becomes:
> Col1 Col2 Col3
> 2003/12/01 00:05:00 2003/12/01 00:05:00
> 2003/12/01 00:10:00 2003/12/01 00:10:00
> 2003/12/01 00:15:00 2003/12/01 00:15:00
> 2003/12/01 00:20:00 2003/12/01 00:20:00
> .
> .
> ..
> I am wondering which way is easier - making a program to do this (e.g. in
> C#)
> or using SQL queries. Thanks for any suggestions.
> Yuelin
>|||Hi
You can write the query this way
UPDATE <TABLE>
SET
col2 = convert(varchar(10), col1,102),
col3 = convert(varchar(8), col1,108)
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Yuelin Liang" wrote:

> Hi there
> Presently I have a table with a DateTime column as the following:
> Col1
> 2003/12/01 00:05:00
> 2003/12/01 00:10:00
> 2003/12/01 00:15:00
> 2003/12/01 00:20:00
>
> What I want to do is to separate the date and time into two columns,
> so it becomes:
> Col1 Col2 Col3
> 2003/12/01 00:05:00 2003/12/01 00:05:00
> 2003/12/01 00:10:00 2003/12/01 00:10:00
> 2003/12/01 00:15:00 2003/12/01 00:15:00
> 2003/12/01 00:20:00 2003/12/01 00:20:00
> ..
> ..
> ...
> I am wondering which way is easier - making a program to do this (e.g. in
> C#)
> or using SQL queries. Thanks for any suggestions.
> Yuelin
>
>|||update <table>
set
col2 = convert(varchar(10),col1,111),
col3 = convert(varchar(10),col1,108)
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Yuelin Liang" wrote:

> Hi there
> Presently I have a table with a DateTime column as the following:
> Col1
> 2003/12/01 00:05:00
> 2003/12/01 00:10:00
> 2003/12/01 00:15:00
> 2003/12/01 00:20:00
>
> What I want to do is to separate the date and time into two columns,
> so it becomes:
> Col1 Col2 Col3
> 2003/12/01 00:05:00 2003/12/01 00:05:00
> 2003/12/01 00:10:00 2003/12/01 00:10:00
> 2003/12/01 00:15:00 2003/12/01 00:15:00
> 2003/12/01 00:20:00 2003/12/01 00:20:00
> ..
> ..
> ...
> I am wondering which way is easier - making a program to do this (e.g. in
> C#)
> or using SQL queries. Thanks for any suggestions.
> Yuelin
>
>|||Thanks for the advice.
The original table I have doesn't have the column 2 and column 3, I need to
populate the two columns based on the datetime data in the column 1.
How to do this in T-SQL?
Thanks
Yuelin
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:24C0B6EE-354B-41EA-9AD6-7CB11AC3B52C@.microsoft.com...
> update <table>
> set
> col2 = convert(varchar(10),col1,111),
> col3 = convert(varchar(10),col1,108)
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Yuelin Liang" wrote:
>|||Then you either have to extend the table before updating then table, or you
insert the new data in a new table via:
Select
col1,
convert(varchar(10),col1,111) as col2,
convert(varchar(10),col1,108) as col3
Into NewTable
From Yourtable
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Yuelin Liang" <yuelinliang@.hotmail.com> schrieb im Newsbeitrag
news:OYPTy0sWFHA.1508@.tk2msftngp13.phx.gbl...
> Thanks for the advice.
> The original table I have doesn't have the column 2 and column 3, I need
> to
> populate the two columns based on the datetime data in the column 1.
> How to do this in T-SQL?
> Thanks
> Yuelin
>
> "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> news:24C0B6EE-354B-41EA-9AD6-7CB11AC3B52C@.microsoft.com...
>|||Hi,
Then u can use this way
SELECT col1, convert(varchar(10),col1,111) col2,
convert(varchar(10),col1,108) col3
FROM <TABLE>
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Yuelin Liang" wrote:

> Thanks for the advice.
> The original table I have doesn't have the column 2 and column 3, I need t
o
> populate the two columns based on the datetime data in the column 1.
> How to do this in T-SQL?
> Thanks
> Yuelin
>
> "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> news:24C0B6EE-354B-41EA-9AD6-7CB11AC3B52C@.microsoft.com...
>
>|||The problem with changing the structure of a physical table to meet some
presentation requirement is that different applications or users have
different requirements. For example, what if one user wants YYYY/MM/DD and
another wants MM/DD/YYYY? Also, the transformed representation of a value
is ususally not as storage efficient as the native data format. Rather than
modify your existing physical table, leave the date as datetime in the table
and implement the transformations as Views. The convert function can be used
to represent datetime as a string in various formats.
"Yuelin Liang" <yuelinliang@.hotmail.com> wrote in message
news:uacpnCsWFHA.3184@.TK2MSFTNGP15.phx.gbl...
> Hi there
> Presently I have a table with a DateTime column as the following:
> Col1
> 2003/12/01 00:05:00
> 2003/12/01 00:10:00
> 2003/12/01 00:15:00
> 2003/12/01 00:20:00
>
> What I want to do is to separate the date and time into two columns,
> so it becomes:
> Col1 Col2 Col3
> 2003/12/01 00:05:00 2003/12/01 00:05:00
> 2003/12/01 00:10:00 2003/12/01 00:10:00
> 2003/12/01 00:15:00 2003/12/01 00:15:00
> 2003/12/01 00:20:00 2003/12/01 00:20:00
> .
> .
> ..
> I am wondering which way is easier - making a program to do this (e.g. in
> C#)
> or using SQL queries. Thanks for any suggestions.
> Yuelin
>

OPENXML Question

I have an text column in a table that was intended to store XML data about queries that users submitted in our database. The problem appears that the application inserting records into the table doesn't do a great job of formatting the XML and I am having a great deal of trouble getting workable results.

The XML data in the table looks like:
<request>
<name>LastName</name>
<oper>=</oper>
<value>Smith</value>
<name>FirstName</name>
<oper>=</oper>
<value>John</value>
<name>MiddleName</name>
<oper>=</oper>
<value>Q.</value>
</request>

Note the groups of three elements (name, oper, value). These always appear sequentially in the data and there are always a fixed number of elements. If an element has no data, then it contains the value 'null' (ie, <value>null</value>).

I want to get a result like:

Col1 Col2 Col3
======== ====== ========
LastName = Smith
FirstName = John
MiddleName = Q

When I execute OPENXML, I specify option 2 (element centric) and the following WITH clause:

WITH (name varchar(10),
oper varchar(10),
value varchar(50) )

I get a single row returned. I have also tried:

WITH (name varchar(10) '@.name',
oper varchar(10) '@.oper',
value varchar(50) '@.value')

I get a single row returned (with null values).

I've tried lots of other permutations, but nothing so far has worked. The only thing that has been modestly successful is to create and Edge Table (exclude the WITH clause), but that's really hard for me to work with. Another option is to insert a <line num="#">...<line> around each group of three elements (name, oper, value). That worked well, but it's a pain to implement (it means going back and updating all rows where the XML data exists and inserting the proper data.

I am by no means an XML guru, so if anyone has a suggestion, I'd love to hear it.

Regards,

hmscottI think you are struggling because you have assigned meaning to the order of elements in your source xml file. To paraphrase Brett: the order of elements in an xml file *should* have no meaning. Assigning meaning to the order of elements leads to fragile and inflexible code (dependent on element order to work correctly).

a better xml design would be something that groups each tuple, but (as you mention above) that means you have to change the xml.

For instance:

<request>
<var name="LastName" oper="=" value="Smith"/>
...
</request>

so that it's unambiguous which name/oper/value goes together.|||I think you are struggling because you have assigned meaning to the order of elements in your source xml file. To paraphrase Brett: the order of elements in an xml file *should* have no meaning. Assigning meaning to the order of elements leads to fragile and inflexible code (dependent on element order to work correctly).

I didn't assign meaning to the order of anything. This is legacy code that's been around since before my time (and which also means that the developer whose neck I wish to wring has long since departed).

I know what I would do to prevent the problem; what can I do to deal with the data as it is (about 1,000,000+ rows)?

Regards,

hmscott|||well, this works, but I'm not terribly proud of it. It's certainly fragile... :)

declare @.names table (id int identity(1,1), name varchar(10))
declare @.opers table (id int identity(1,1), oper varchar(10))
declare @.values table (id int identity(1,1), value varchar(10))

DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<request>
<name>LastName</name>
<oper>=</oper>
<value>Smith</value>
<name>FirstName</name>
<oper>=</oper>
<value>John</value>
<name>MiddleName</name>
<oper>=</oper>
<value>Q.</value>
</request>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc

insert into @.names (name) SELECT * FROM OPENXML (@.idoc, '/request/name',1) WITH (name varchar(10) '.')
insert into @.opers (oper) SELECT * FROM OPENXML (@.idoc, '/request/oper',1) WITH (oper varchar(10) '.')
insert into @.values (value) SELECT * FROM OPENXML (@.idoc, '/request/value',1) WITH (value varchar(10) '.')

EXEC sp_xml_removedocument @.idoc

select n.name, o.oper, v.value from
@.names n
join @.opers o on o.id=n.id
join @.values v on v.id=n.id|||well, this works, but I'm not terribly proud of it. It's certainly fragile... :)

Hey, thanks. I hadn't thought of trying it that way.
:beer:

That's what I get for beating my head against the wall for too long.

Regards,

hmscott|||glad to help. note that my idea falls down completely if there is a missing name,oper, or value element anywhere in your file, because then it would group the wrong elements together.

that's what I meant by it being fragile. :)|||glad to help. note that my idea falls down completely if there is a missing name,oper, or value element anywhere in your file, because then it would group the wrong elements together.

that's what I meant by it being fragile. :)

That's understood. Actually, the way the data is organized, I think I can throw this inside a cursor. It still will fail in the event of a missing element, but perhaps a bit of error checking will capture that.

Thanks again.

hmscott

OPENXML Invalid column name error on update

I have the stored procedure below which complies and works OK as long
as the field RxID is NOT set as an identity column. When the field is set as
an identity column then I get the error Invalid column name 'RxID'.
It's very easy to reproduce this issue, is this a bug, is there any work
around?
CREATE Procedure UpdateRxDetail
@.doc text
AS
DECLARE @.hdoc int
EXEC sp_xml_preparedocument @.hdoc OUTPUT, @.doc
BEGIN TRANSACTION
UPDATE rxDetail
SET
rxDetail.FormattedErrorString = XMLTABLE.FormattedErrorString,
rxDetail.StatusID = XMLTABLE.StatusID
FROM OPENXML(@.hdoc, '//errInfo')
WITH rxDetail XMLTABLE
WHERE rxDetail.RxID = XMLTABLE.RxID
COMMIT
EXEC sp_xml_removedocument @.hdoc
RETURN
GOPlease post the XML you want to parse.
ML
http://milambda.blogspot.com/|||If you replace your UPDATE statement with this
SELECT XMLTABLE.*
FROM OPENXML(@.hdoc, '//errInfo')
WITH rxDetail XMLTABLE
you'll see that RxID only appears when it's
not an identity. AFAIK, there's no way round this.
You'll have to change "WITH rxDetail"
to explicitly list the XML<->column mappings
yourself. Something like this
UPDATE rxDetail
SET
rxDetail.FormattedErrorString = XMLTABLE.FormattedErrorString,
rxDetail.StatusID = XMLTABLE.StatusID
FROM OPENXML(@.hdoc, '//errInfo')
WITH (FormattedErrorString varchar(10),
StatusID int,
RxID int) XMLTABLE
WHERE rxDetail.RxID = XMLTABLE.RxID|||Thanks Mark - that did the trick!
"markc600@.hotmail.com" wrote:

> If you replace your UPDATE statement with this
> SELECT XMLTABLE.*
> FROM OPENXML(@.hdoc, '//errInfo')
> WITH rxDetail XMLTABLE
> you'll see that RxID only appears when it's
> not an identity. AFAIK, there's no way round this.
> You'll have to change "WITH rxDetail"
> to explicitly list the XML<->column mappings
> yourself. Something like this
> UPDATE rxDetail
> SET
> rxDetail.FormattedErrorString = XMLTABLE.FormattedErrorString,
> rxDetail.StatusID = XMLTABLE.StatusID
> FROM OPENXML(@.hdoc, '//errInfo')
> WITH (FormattedErrorString varchar(10),
> StatusID int,
> RxID int) XMLTABLE
> WHERE rxDetail.RxID = XMLTABLE.RxID
>

OpenXML Identity Insert

I am inserting data into a table using the OpenXML command (see query below). The table that I am inserting it into has an identity column named SOFTWARE_UID. I need to return an xmldoc with the identifier included for each row inserted. Does anyone have a clue how I can do that efficiently? I know I can do it with a cursor, but if that were the case, then why not just do x inserts with an @.@.identity call? I am looking to do this in an efficient way, preferebly with an out of box function (such as @.@.identities).

Declare @.XMLString varchar(max)

Set @.XMLString = '<IT_SOFTWARE>

<SOFT name="Soft1" status="1"/>

<SOFT name="Soft2" status="1"/>

<SOFT name="Soft3" status="1"/>

</IT_SOFTWARE>'

declare @.XmlHandle int

EXEC sp_xml_preparedocument @.XmlHandle output, @.XMLString

Insert Into INFORMATION_SYSTEMS.dbo.IT_SOFTWARE

SELECT SOFTWARE_NAME,SOFTWARE_STATUS_ID

FROM OPENXML (@.XmlHandle, '/IT_SOFTWARE/SOFT',1)

WITH ( SOFTWARE_NAME varchar(max) '@.name',

SOFTWARE_STATUS_ID int '@.status')

EXEC sp_xml_removedocument @.XmlHandle

As you are using varchar(max) I'll assume your are
using SQL Server 2005

This should give you your results from your
table along with the identities

select SOFTWARE_NAME as "@.name",
SOFTWARE_STATUS_ID as "@.status",
SOFTWARE_UID as "@.UID"
FROM INFORMATION_SYSTEMS.dbo.IT_SOFTWARE
FOR XML PATH('SOFT'), ROOT('IT_SOFTWARE'), TYPE

You can also change from using OPENXML and
sp_xml_preparedocument/sp_xml_removedocument to this

declare @.xml xml
set @.xml=@.XMLString

Insert Into INFORMATION_SYSTEMS.dbo.IT_SOFTWARE(SOFTWARE_NAME,SOFTWARE_STATUS_ID)
select R.n.value('@.name','varchar(max)') as SOFTWARE_NAME,
R.n.value('@.status','int') as SOFTWARE_STATUS_ID
from @.xml.nodes('/IT_SOFTWARE/SOFT') as R(n)

|||

If you are after only the newly inserted rows you
can use the output..into clause in the insert statement

DECLARE @.IT_SOFTWARE table (
SOFTWARE_UID int,
SOFTWARE_NAME varchar(max),
SOFTWARE_STATUS_ID int);

Insert Into INFORMATION_SYSTEMS.dbo.IT_SOFTWARE(SOFTWARE_NAME,SOFTWARE_STATUS_ID)
output inserted.SOFTWARE_UID,
inserted.SOFTWARE_NAME,
inserted.SOFTWARE_STATUS_ID
into @.IT_SOFTWARE(SOFTWARE_UID,SOFTWARE_NAME,SOFTWARE_STATUS_ID)
select R.n.value('@.name','varchar(max)') as SOFTWARE_NAME,
R.n.value('@.status','int') as SOFTWARE_STATUS_ID
from @.xml.nodes('/IT_SOFTWARE/SOFT') as R(n)

select SOFTWARE_NAME as "@.name",
SOFTWARE_STATUS_ID as "@.status",
SOFTWARE_UID as "@.UID"
FROM @.IT_SOFTWARE
FOR XML PATH('SOFT'), ROOT('IT_SOFTWARE'), TYPE

|||I used some code from both. I never knew about the output command. Thank you for the help.