Optimizations Job and Shrink DB creating HUGE transaction log file

I've noticed for the past two weeks that during the time when my
Optimizations Job and Shrink Database job from my Database Maintenance Plan
run, they are creating some HUGE transaction log file backups. For a 13GB
db, the Optimizations is making a 2+GB tran log. The Shrink job made a 10GB
tran log this morning.
I've never noticed such huge logs before so I'm wondering how I can figure
out why those 2 jobs have just started doing this. I know its these jobs due
to the timing being identical the past two weeks.
Rich
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:9E379E3A-B516-46F7-B9DB-854FF0F2499B@.microsoft.com...
> I've noticed for the past two weeks that during the time when my
> Optimizations Job and Shrink Database job from my Database Maintenance
> Plan
> run, they are creating some HUGE transaction log file backups. For a 13GB
> db, the Optimizations is making a 2+GB tran log. The Shrink job made a
> 10GB
> tran log this morning.
> I've never noticed such huge logs before so I'm wondering how I can figure
> out why those 2 jobs have just started doing this. I know its these jobs
> due
> to the timing being identical the past two weeks.
|||The rebuilding of indexes is normally a fully logged operation as long as
you are in FULL recovery mode. The shrinking is always fully logged. Both of
these can generate lots of log entries. It may be that you have an open long
running tran that is preventing the log files from being truncated and thus
are seeing larger than normal file size. But the real question is why are
you shrinking the DB in the first place. That is destroying all that you
just did by reindexing. See here for more details:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Andrew J. Kelly SQL MVP
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:9E379E3A-B516-46F7-B9DB-854FF0F2499B@.microsoft.com...
> I've noticed for the past two weeks that during the time when my
> Optimizations Job and Shrink Database job from my Database Maintenance
> Plan
> run, they are creating some HUGE transaction log file backups. For a 13GB
> db, the Optimizations is making a 2+GB tran log. The Shrink job made a
> 10GB
> tran log this morning.
> I've never noticed such huge logs before so I'm wondering how I can figure
> out why those 2 jobs have just started doing this. I know its these jobs
> due
> to the timing being identical the past two weeks.
|||Thanks to both of you for posting that article. So should I just turn the
shrink off completely? or maybe only do it once in a great while. I see the
points both of you brought up and the ones brought up in the article. Here
is the command for the shrink job, DBCC SHRINKDATABASE (N'DB1', 0). The
reason its setup to do that is because the person before me set it up like
that...trying to figure out what would be best now.
"Andrew J. Kelly" wrote:

> The rebuilding of indexes is normally a fully logged operation as long as
> you are in FULL recovery mode. The shrinking is always fully logged. Both of
> these can generate lots of log entries. It may be that you have an open long
> running tran that is preventing the log files from being truncated and thus
> are seeing larger than normal file size. But the real question is why are
> you shrinking the DB in the first place. That is destroying all that you
> just did by reindexing. See here for more details:
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Andrew J. Kelly SQL MVP
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:9E379E3A-B516-46F7-B9DB-854FF0F2499B@.microsoft.com...
>
>
|||Rich
Yes , turn it off and if it needs use DBCC SHRINKFILE command instead
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:1F7A6471-4E60-4CA5-BECD-0E96A946AE2A@.microsoft.com...[vbcol=seagreen]
> Thanks to both of you for posting that article. So should I just turn the
> shrink off completely? or maybe only do it once in a great while. I see
> the
> points both of you brought up and the ones brought up in the article.
> Here
> is the command for the shrink job, DBCC SHRINKDATABASE (N'DB1', 0). The
> reason its setup to do that is because the person before me set it up like
> that...trying to figure out what would be best now.
> "Andrew J. Kelly" wrote:
|||Also, here's what my optimizations job says. maybe this will help make it
clearer.
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
55FB40C3-34D7-4E24-84CC-A21DB53F752C -WriteHistory -RebldIdx 100
-RmUnusedSpace 10 1 '
"Rich" wrote:
[vbcol=seagreen]
> Thanks to both of you for posting that article. So should I just turn the
> shrink off completely? or maybe only do it once in a great while. I see the
> points both of you brought up and the ones brought up in the article. Here
> is the command for the shrink job, DBCC SHRINKDATABASE (N'DB1', 0). The
> reason its setup to do that is because the person before me set it up like
> that...trying to figure out what would be best now.
> "Andrew J. Kelly" wrote:
|||yeah, it sounds like i should just kill my shrink job completely. But how
would I know in the future if i need to shrink it manually? Is there a good
way to tell?
"Uri Dimant" wrote:

> Rich
> Yes , turn it off and if it needs use DBCC SHRINKFILE command instead
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:1F7A6471-4E60-4CA5-BECD-0E96A946AE2A@.microsoft.com...
>
>
|||Rich
If you run out of space on disk so that's is probably time to shrink the
data but it is short term solution as you know you shrin the file will be
grown again.
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:91CF5109-F0BE-4349-9FE2-DB17A431535C@.microsoft.com...[vbcol=seagreen]
> yeah, it sounds like i should just kill my shrink job completely. But how
> would I know in the future if i need to shrink it manually? Is there a
> good
> way to tell?
> "Uri Dimant" wrote:
|||ok, so i should kill my actual shrink job, but is that -RmUnusedSpace part in
my Optimizations job ok or would that need to be removed as well. the full
command is in my previous posts.
"Uri Dimant" wrote:

> Rich
> If you run out of space on disk so that's is probably time to shrink the
> data but it is short term solution as you know you shrin the file will be
> grown again.
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:91CF5109-F0BE-4349-9FE2-DB17A431535C@.microsoft.com...
>
>
|||Well you should be careful of editing the job itself. I would open the
wizard and uncheck the options there and the wizard will edit the
appropriate jobs to account for it.
Andrew J. Kelly SQL MVP
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:80288B98-E7ED-4270-93D5-9F90914D1F6A@.microsoft.com...[vbcol=seagreen]
> ok, so i should kill my actual shrink job, but is that -RmUnusedSpace part
> in
> my Optimizations job ok or would that need to be removed as well. the
> full
> command is in my previous posts.
> "Uri Dimant" wrote:

Opteron vs Xeon

I've recently been attempting to put into production some Itanium based
servers. I'm running (amongst other things) Remedy, which means highly
serialised transactions (thus limiting the effect of the Itanium) and has
lead me to discover that the low clock speed on the Itanium is causing the
application to run slower (the biggest test of this was a basic bulk insert
into a table with no indexes that would run 50% slower on a 4x1.6GHZ
Itanium vs a 2x2.4GHZ Xeon).
I'm now looking into going to back to a 32bit system, folks are touting the
benefits of the Opteron processor as opposed to the Xeon, stating that the
performance difference is pretty large.
My question is, does the slower clock speed on the Opteron translate into
the same problems that I was experiencing on the Itanium, or am I actually
going to find better i/o performance through the AMD processor?
Thanks
Nic
On Thu, 01 Sep 2005 04:20:11 -0700, Nicholas Cain
<nicholas.cain@.nospam.t-mobile.com> wrote:
>I've recently been attempting to put into production some Itanium based
>servers. I'm running (amongst other things) Remedy, which means highly
>serialised transactions (thus limiting the effect of the Itanium) and has
>lead me to discover that the low clock speed on the Itanium is causing the
>application to run slower (the biggest test of this was a basic bulk insert
>into a table with no indexes that would run 50% slower on a 4x1.6GHZ
>Itanium vs a 2x2.4GHZ Xeon).
>I'm now looking into going to back to a 32bit system, folks are touting the
>benefits of the Opteron processor as opposed to the Xeon, stating that the
>performance difference is pretty large.
>My question is, does the slower clock speed on the Opteron translate into
>the same problems that I was experiencing on the Itanium, or am I actually
>going to find better i/o performance through the AMD processor?
Seems unlikely that CPU speed is really the limiting factor on a bulk
load.
J.
|||JXStern <JXSternChangeX2R@.gte.net> wrote in
news:i7rdh114n67mjuc7uor55clv95k5f8a3uq@.4ax.com:

> Seems unlikely that CPU speed is really the limiting factor on a bulk
> load.
> J.
>
I've had the gurus at HP look and tell me that this is the limiting factor
(after a lot of consideration and followup with MS).
I didn't believe it myself, however all indications point to that problem.
|||I've also seen high CPU utilization with bulk inserts, at least the
fully-logged variety.
Hope this helps.
Dan Guzman
SQL Server MVP
"Nicholas Cain" <nicholas.cain@.nospam.t-mobile.com> wrote in message
news:Xns96C45AE8629B4nicholascainnospamtm@.207.46.2 48.16...
> JXStern <JXSternChangeX2R@.gte.net> wrote in
> news:i7rdh114n67mjuc7uor55clv95k5f8a3uq@.4ax.com:
>
> I've had the gurus at HP look and tell me that this is the limiting factor
> (after a lot of consideration and followup with MS).
> I didn't believe it myself, however all indications point to that problem.
|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in
news:ulSIjWvrFHA.1168@.TK2MSFTNGP11.phx.gbl:

> I've also seen high CPU utilization with bulk inserts, at least the
> fully-logged variety.
>
The cpu utilisation was extremely high on the Itanium, the majority of that
was kernel usage.
Changing the max degree of parallelism made no difference, nor did setting
offsets on the disk, nor sp4, adding numa options, setting affinity masks
or anything.
The bulk insert itself was a single 1.5GB file into a table with no
indexes. The db itself was in simple recovery mode, db and logs on seperate
luns on a Hitachi XP1024 SAN with a 40GB cache.
Performance speeds for the bulk insert were idnetical on both a Dell and HP
Itanium based system with the same specs.
|||I was acutally looking at the dual core.
I guess my best course of action would be to throw a Xeon and a Opteron in
a head to head and see what comes out as the leader.
"Coldman" <nomorespam@.mail.com> wrote in
news:#AAvQowrFHA.528@.TK2MSFTNGP09.phx.gbl:

> hehe - me again
> and u can get double core opterons - and have 2 cpu while paying
> licence for 1,
> and there r IMHO no double core Xeons
>
|||good idea
y dont u post the results here after the test
"Nicholas Cain" <nicholas.cain@.nospam.t-mobile.com> wrote in message
news:Xns96C477204FE4Bnicholascainnospamtm@.207.46.2 48.16...
>I was acutally looking at the dual core.
> I guess my best course of action would be to throw a Xeon and a Opteron in
> a head to head and see what comes out as the leader.
>
> "Coldman" <nomorespam@.mail.com> wrote in
> news:#AAvQowrFHA.528@.TK2MSFTNGP09.phx.gbl:
>
|||If you are going to do that you should test a dual core Pentium against the
dual core Opteron. I have several clients with single core Opterons and
they are very happy with them but I think Dual Core processors will rule the
earth very soon<g>.
Andrew J. Kelly SQL MVP
"Nicholas Cain" <nicholas.cain@.nospam.t-mobile.com> wrote in message
news:Xns96C477204FE4Bnicholascainnospamtm@.207.46.2 48.16...
>I was acutally looking at the dual core.
> I guess my best course of action would be to throw a Xeon and a Opteron in
> a head to head and see what comes out as the leader.
>
> "Coldman" <nomorespam@.mail.com> wrote in
> news:#AAvQowrFHA.528@.TK2MSFTNGP09.phx.gbl:
>
|||but there r no dual core Xeons yet i think, and Pentium 4 lacks server class
motherboards(w/ a lot of 64bit slots and dual power connectors)
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e9qbrgxrFHA.2272@.TK2MSFTNGP11.phx.gbl...
> If you are going to do that you should test a dual core Pentium against
> the dual core Opteron. I have several clients with single core Opterons
> and they are very happy with them but I think Dual Core processors will
> rule the earth very soon<g>.
> --
> Andrew J. Kelly SQL MVP
>
> "Nicholas Cain" <nicholas.cain@.nospam.t-mobile.com> wrote in message
> news:Xns96C477204FE4Bnicholascainnospamtm@.207.46.2 48.16...
>

Opinions on Option (Keepfixed Plan)

I've got this situation where a certain large stored proc (that's used
all day long) just compiles way too often, to the point where it hurts
the performance. I've tried all the tricks I could find to reduce the
number of compilations. So my last option is to use Option (Keepfixed
Plan) hint.
Are there any downsides to using Option (Keepfixed Plan)?
Regards
Hi Frank
Have you read these article to determine the cause of the recompilations and
address the actual reasons?
Troubleshooting stored procedure recompilation
http://support.microsoft.com/kb/243586
How to identify the cause of recompilation in an SP:Recompile event
http://support.microsoft.com/kb/308737
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Frank Rizzo" <none@.none.com> wrote in message
news:uuunQQy5GHA.4064@.TK2MSFTNGP03.phx.gbl...
> I've got this situation where a certain large stored proc (that's used all
> day long) just compiles way too often, to the point where it hurts the
> performance. I've tried all the tricks I could find to reduce the number
> of compilations. So my last option is to use Option (Keepfixed Plan)
> hint.
> Are there any downsides to using Option (Keepfixed Plan)?
> Regards
|||Kalen Delaney wrote:
> Hi Frank
> Have you read these article to determine the cause of the recompilations and
> address the actual reasons?
> Troubleshooting stored procedure recompilation
> http://support.microsoft.com/kb/243586
> How to identify the cause of recompilation in an SP:Recompile event
> http://support.microsoft.com/kb/308737
Yep, I did all these. I decreased SP:Recompile events in Profiler
substantially, however, they were still happening quite a bit.
Inserting Option (Keepfixed Plan) got rid of the SP:Recompile events and
(anecdotally) increased the performance of the stored proc. In Perfmon,
however, I still get a large number for the SQL Statistic/SQL
Compilations. It must mean something else than what is reflected by the
SP:Recompile event in Profiler.
|||Frank Rizzo wrote:
> Kalen Delaney wrote:
> Yep, I did all these. I decreased SP:Recompile events in Profiler
> substantially, however, they were still happening quite a bit.
> Inserting Option (Keepfixed Plan) got rid of the SP:Recompile events and
> (anecdotally) increased the performance of the stored proc. In Perfmon,
> however, I still get a large number for the SQL Statistic/SQL
> Compilations. It must mean something else than what is reflected by the
> SP:Recompile event in Profiler.
If the recompilations are caused by a large amount of
inserts/updates/deletes, then you could experiment with turning off
auto-update statistics and run a schedule to manually update the
statistics at a time that is convenient for you.
Gert-Jan
|||Gert-Jan Strik wrote:
> Frank Rizzo wrote:
> If the recompilations are caused by a large amount of
> inserts/updates/deletes, then you could experiment with turning off
> auto-update statistics and run a schedule to manually update the
> statistics at a time that is convenient for you.
I turned off the auto-update stats from the get go, but the problem is
still happening.

> Gert-Jan

OPENXML with a namespace

Sorry, I've tried to search to find my answer, but I just can't quite figure
it out... I'm new to querying XML in a stored proc...
I have an XML file that looks like:
<Members xmlns="http://tempuri.org/template.xsd">
<Member>
<OperationType>Insert</OperationType>
</Member>
<Member>
<OperationType>Update</OperationType>
</Member>
</Members>
there's more to it, but I've simplified it down.
and in my stored proc, I'm trying this: (assume that @.idoc is already
declared and @.FileContents contains the contents of the xml file I'm
reading)
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.FileContents, '<Members
xmlns="http://tempuri.org/template.xsd"/>'
SELECT *
FROM OPENXML(@.idoc, '/Members/Member',2)
WITH (OperationType varchar(30))
EXEC sp_xml_removedocument @.idoc
I get no results...
If I take the xmlns declaration out of the <Members> element in the source
file, it works fine and I get a list of the OperationType elements.
What am I missing?
Thanks much,
Sheryl
Hi SL,
Welcome to the MSDN newsgroup.
Regarding on the OPENXML with namespace problem, based on my research, we
can use the third parameter of the sp_xml_prparedocument procedure to
specify namespace info. Also, in our sequental xpath, we need to explicitly
identify namespace through a namespace prefix. For detailed info, you can
refer to the following MSDN document and web article:
#How To: Use OpenXML on an Xml document with a default namespace
http://www.sqlxml.org/faqs.aspx?faq=101
#sp_xml_preparedocument
http://msdn.microsoft.com/library/en...asp?frame=tru
e
Hope this helps.
Regards,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
|||That was perfect, thanks!
"Steven Cheng[MSFT]" <stcheng@.online.microsoft.com> wrote in message
news:GubgkovKGHA.3680@.TK2MSFTNGXA02.phx.gbl...
> Hi SL,
> Welcome to the MSDN newsgroup.
> Regarding on the OPENXML with namespace problem, based on my research, we
> can use the third parameter of the sp_xml_prparedocument procedure to
> specify namespace info. Also, in our sequental xpath, we need to
> explicitly
> identify namespace through a namespace prefix. For detailed info, you can
> refer to the following MSDN document and web article:
>
> #How To: Use OpenXML on an Xml document with a default namespace
> http://www.sqlxml.org/faqs.aspx?faq=101
> #sp_xml_preparedocument
> http://msdn.microsoft.com/library/en...asp?frame=tru
> e
> Hope this helps.
> Regards,
> Steven Cheng
> Microsoft Online Support
> Get Secure! www.microsoft.com/security
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>
>
|||You're welcome SL,
Regards,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

OPENXML Stored Procedure

I've already posted this question once but I'm posting it again, re-written to be more clear than the previous post as I have not received any replies...
We will be receiving several XML documents of various types. We need to shred these documents and get the data into SQL. That's fine, I can and have done that but each time it means I have to create very specific insert query for a particular document typ
e. Our system now must be extended to become more generic so that we can add new document types instantly (or pretty close to it). So, I've created a very generic database structure that includes three tables. One table holds attributes (table column name
s), associated XML node name, data type and the foreign key relating to the document types table. The last table holds the value of the node and the attribute id.
The problem I'm having is with building a dynamic OPENXML statement.
Here's the code (abridged):
INSERT case_oce
SELECT * FROM OPENXML(@.xmlDoc,'/official_copy',2)
WITH (
(SELECT attribute_name, attribute_type, document_node
FROM v_xml_case_documents WHERE)
)
Here is a sample of the results I'm trying to achieve, however, dynamically through the select statement above:
INSERT case_oce
SELECT * FROM OPENXML(@.xmlDoc,'/official_copy',2)
WITH (
co_property_lender nvarchar(255) '/official_copy/title_number',
co_property_price decimal '/official_copy/property_register/price',
co_property_legal_description nvarchar(4000) '/official_copy/property_register/legal_description',
co_property_address_house_name nvarchar(50) '/official_copy/property_address/house_name',
co_property_address_house_number nvarchar(50) '/official_copy/property_address/house_number',
co_property_address_street_name nvarchar(50) '/official_copy/property_address/street_name'
)
Do you know of a better way or any way to do this? Is this even possible with OPENXML? Does anyone have any ideas/comments/examples?
TIA
Denise White
You have to build up a string in T-SQL that you then execute using EXECUTE.
The WITH clause is a constant expression since we need to know the SQL
rowset format at compiletime.
Best regards
Michael
"mizwhite" <anonymous@.discussions.microsoft.com> wrote in message
news:1C7757ED-0787-48AA-9EDE-FF7BD36F7DAE@.microsoft.com...
> I've already posted this question once but I'm posting it again,
> re-written to be more clear than the previous post as I have not received
> any replies...
> We will be receiving several XML documents of various types. We need to
> shred these documents and get the data into SQL. That's fine, I can and
> have done that but each time it means I have to create very specific
> insert query for a particular document type. Our system now must be
> extended to become more generic so that we can add new document types
> instantly (or pretty close to it). So, I've created a very generic
> database structure that includes three tables. One table holds attributes
> (table column names), associated XML node name, data type and the foreign
> key relating to the document types table. The last table holds the value
> of the node and the attribute id.
> The problem I'm having is with building a dynamic OPENXML statement.
> Here's the code (abridged):
> INSERT case_oce
> SELECT * FROM OPENXML(@.xmlDoc,'/official_copy',2)
> WITH (
> (SELECT attribute_name, attribute_type, document_node
> FROM v_xml_case_documents WHERE)
> )
>
> Here is a sample of the results I'm trying to achieve, however,
> dynamically through the select statement above:
> INSERT case_oce
> SELECT * FROM OPENXML(@.xmlDoc,'/official_copy',2)
> WITH (
> co_property_lender nvarchar(255) '/official_copy/title_number',
> co_property_price decimal '/official_copy/property_register/price',
> co_property_legal_description nvarchar(4000)
> '/official_copy/property_register/legal_description',
> co_property_address_house_name nvarchar(50)
> '/official_copy/property_address/house_name',
> co_property_address_house_number nvarchar(50)
> '/official_copy/property_address/house_number',
> co_property_address_street_name nvarchar(50)
> '/official_copy/property_address/street_name'
> )
>
> Do you know of a better way or any way to do this? Is this even possible
> with OPENXML? Does anyone have any ideas/comments/examples?
> TIA
> Denise White
>
|||Thanks for your reply Michael. I am still, however, in need of an answer to my original problem. I need to build the field list dynamically (i.e., co_property_lender nvarchar(255) '/official_copy/title_number', etc.) as I have stored this data in the data
base for each document type. I obviously can't set a query result set to a variable, thus my problem remains.
Thanks for your help.
Denise.
|||I've been working further on this problem. As suggested, I've built up a T-SQL string and used EXECUTE. It works but not when I try to obtain my field list dynamically.
--Source XML Document
DECLARE @.doc nvarchar (4000)
SET @.doc ='<official_copy><property_register><property_addr ess><house_name>Little Brae</house_name><house_number>50</house_number><street_name>98 North Brink</street_name><district_name /><post_town>Wisbech</post_town><county>Cambridgeshire</county><postc
ode1>OL2</postcode1><postcode2>5BY</postcode2></property_address></property_register></official_copy>'
--Load XML document
DECLARE @.idoc int
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
--Dynamically generated SQL statement
DECLARE @.sqlStatement nvarchar(4000)
--Our main params that we would like to pass/control
DECLARE @.primaryXPath nvarchar(255)
DECLARE @.selectFieldList varchar(2000)
--First Test
SET @.primaryXPath = '/official_copy'
/* this works --
SET @.selectFieldList = '[house_name] NVARCHAR(100) ''/official_copy/property_register/property_address/house_name'',
[house_number] NVARCHAR(100) ''/official_copy/property_register/property_address/house_number'''
*/
SET @.selectFieldList = 'SELECT attribute_name, attribute_type, document_node
FROM v_xml_case_documents WHERE cd_type_id = 1'
SET @.sqlStatement = 'SELECT * FROM OPENXML (@.idoc, @.primaryXPath, 2) WITH (' + @.selectFieldList + ')'
EXECUTE sp_executesql @.sqlStatement,
@.params = N'@.idoc INT, @.primaryXPath nvarchar(255), @.selectFieldList varchar(8000)',
@.idoc=@.idoc, @.primaryXPath=@.primaryXPath, @.selectFieldList=@.selectFieldList
|||If you multiple rows in the table that defines your @.selectFieldList variable then you should open a cursor and append the values you need to it...
_Randal
|||Thanks Randal. I have made use of your suggestion to use a cursor and I've incorporated that with the code I already had for building a dynamic SQL query.
I'm getting closer to a working solution.The problem now is that this code is coming back with the following error:
"Syntax error converting the nvarchar value 'INSERT INTO case_documents(cd_attribute) SELECT * FROM OPENXML(' to a column of data type int." -- Obviously, the error message is pretty straightforward but I cannot understand why it would return this as an
error. I have tried referencing the @.idoc handle both with and without the ' ' characters. Still no joy. Is anyone else having similar problems with building a dynamic field list in OPENXML? - - Any additional suggestions? Examples? ;)
Here is my revised code:
DECLARE @.caseID int
DECLARE @.CaseTypeID int
DECLARE @.selectFieldList nvarchar(4000)
DECLARE @.sqlStatement nvarchar(4000)
DECLARE @.fieldName nvarchar(4000)
DECLARE @.xmlRoot nvarchar(255)
SET @.caseID = 123456
SET @.CaseTypeID = 1
SET @.xmlRoot = '/official_copy'
-- Create a test table. This table schema is used by OPENXML as the
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
DECLARE @.primaryXPath nvarchar(255)
-- XML Input
SET @.doc ='
<official_copy><property_register><property_addres s><house_name>Little Brae</house_name><street_name>98 North Brink</street_name><correspondence><something>Little Brae</something></correspondence></property_address></property_register></official_copy>
'
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
-- BEGIN LOOP
DECLARE @.retcode int
DECLARE importCase CURSOR
FOR (SELECT document_node FROM case_document_attribute WHERE cd_type_id = @.CaseTypeID)
OPEN importCase
FETCH NEXT FROM importCase INTO @.primaryXPath
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
IF (@.@.FETCH_STATUS <> -2)
BEGIN
SET @.selectFieldList = @.primaryXPath
SET @.sqlStatement = 'INSERT INTO case_documents(cd_attribute) '
SET @.sqlStatement = @.sqlStatement + 'SELECT * FROM OPENXML('
SET @.sqlStatement = @.sqlStatement + @.iDoc
SET @.sqlStatement = @.sqlStatement + ','
SET @.sqlStatement = @.sqlStatement + ''''+ @.xmlRoot + ''''
SET @.sqlStatement = @.sqlStatement + ',2)'
SET @.sqlStatement = @.sqlStatement + ' WITH ('
SET @.sqlStatement = @.sqlStatement + 'cd_attribute text ' + '''' + @.primaryXPath + '''' + ')'
/*SET @.sqlStatement = 'INSERT INTO case_documents '
SET @.sqlStatement = @.sqlStatement + 'SELECT * FROM OpenXml(' + @.iDoc + ','
SET @.sqlStatement = @.sqlStatement + '''' + @.primaryXPath + ''''
SET @.sqlStatement = @.sqlStatement + ',2)'
SET @.sqlStatement = @.sqlStatement + 'WITH (' + '''' + @.primaryXPath + '''' + ')'*/
EXEC sp_executesql @.sqlStatement
--PRINT @.sqlStatement
END
FETCH NEXT FROM importCase INTO @.primaryXPath
END
CLOSE importCase
DEALLOCATE importCase
-- unload doc
EXEC sp_xml_removedocument @.idoc
***
Many many thanks.
Denise.
|||This is not really an OpenXML issue anymore. This error message just says
that you cannot concatenate an integer to a string type since the string
value does not become an int.
Try:
SET @.sqlStatement = @.sqlStatement + Cast(@.iDoc as nvarchar(10))
Best regards
Michael
"mizwhite" <anonymous@.discussions.microsoft.com> wrote in message
news:9B4FD306-4311-41CC-86E5-EB3F0670BDF6@.microsoft.com...
> Thanks Randal. I have made use of your suggestion to use a cursor and I've
> incorporated that with the code I already had for building a dynamic SQL
> query.
> I'm getting closer to a working solution.The problem now is that this code
> is coming back with the following error:
> "Syntax error converting the nvarchar value 'INSERT INTO
> case_documents(cd_attribute) SELECT * FROM OPENXML(' to a column of data
> type int." -- Obviously, the error message is pretty straightforward but
> I cannot understand why it would return this as an error. I have tried
> referencing the @.idoc handle both with and without the ' ' characters.
> Still no joy. Is anyone else having similar problems with building a
> dynamic field list in OPENXML? - - Any additional suggestions? Examples?
> ;)
> Here is my revised code:
> DECLARE @.caseID int
> DECLARE @.CaseTypeID int
> DECLARE @.selectFieldList nvarchar(4000)
> DECLARE @.sqlStatement nvarchar(4000)
> DECLARE @.fieldName nvarchar(4000)
> DECLARE @.xmlRoot nvarchar(255)
> SET @.caseID = 123456
> SET @.CaseTypeID = 1
> SET @.xmlRoot = '/official_copy'
> -- Create a test table. This table schema is used by OPENXML as the
> DECLARE @.idoc int
> DECLARE @.doc varchar(1000)
> DECLARE @.primaryXPath nvarchar(255)
> -- XML Input
> SET @.doc ='
> <official_copy><property_register><property_addres s><house_name>Little
> Brae</house_name><street_name>98 North
> Brink</street_name><correspondence><something>Little
> Brae</something></correspondence></property_address></property_register></official_copy>
> '
> -- Create an internal representation of the XML document.
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> -- BEGIN LOOP
> DECLARE @.retcode int
> DECLARE importCase CURSOR
> FOR (SELECT document_node FROM case_document_attribute WHERE cd_type_id =
> @.CaseTypeID)
> OPEN importCase
> FETCH NEXT FROM importCase INTO @.primaryXPath
> WHILE (@.@.FETCH_STATUS <> -1)
> BEGIN
> IF (@.@.FETCH_STATUS <> -2)
> BEGIN
> SET @.selectFieldList = @.primaryXPath
> SET @.sqlStatement = 'INSERT INTO case_documents(cd_attribute) '
> SET @.sqlStatement = @.sqlStatement + 'SELECT * FROM OPENXML('
> SET @.sqlStatement = @.sqlStatement + @.iDoc
> SET @.sqlStatement = @.sqlStatement + ','
> SET @.sqlStatement = @.sqlStatement + ''''+ @.xmlRoot + ''''
> SET @.sqlStatement = @.sqlStatement + ',2)'
> SET @.sqlStatement = @.sqlStatement + ' WITH ('
> SET @.sqlStatement = @.sqlStatement + 'cd_attribute text ' + '''' +
> @.primaryXPath + '''' + ')'
>
> /*SET @.sqlStatement = 'INSERT INTO case_documents '
> SET @.sqlStatement = @.sqlStatement + 'SELECT * FROM OpenXml(' + @.iDoc + ','
> SET @.sqlStatement = @.sqlStatement + '''' + @.primaryXPath + ''''
> SET @.sqlStatement = @.sqlStatement + ',2)'
> SET @.sqlStatement = @.sqlStatement + 'WITH (' + '''' + @.primaryXPath +
> '''' + ')'*/
>
> EXEC sp_executesql @.sqlStatement
> --PRINT @.sqlStatement
> END
> FETCH NEXT FROM importCase INTO @.primaryXPath
> END
> CLOSE importCase
> DEALLOCATE importCase
> -- unload doc
> EXEC sp_xml_removedocument @.idoc
>
> ***
> Many many thanks.
> Denise.
|||"mizwhite" <anonymous@.discussions.microsoft.com> wrote in message
news:1C7757ED-0787-48AA-9EDE-FF7BD36F7DAE@.microsoft.com...
> I've already posted this question once but I'm posting it again,
re-written to be more clear than the previous post as I have not received
any replies...
> We will be receiving several XML documents of various types. We need to
shred these documents and get the data into SQL. That's fine, I can and have
done that but each time it means I have to create very specific insert query
for a particular document type. Our system now must be extended to become
more generic so that we can add new document types instantly (or pretty
close to it).
Hi!
If I understand you correctly, you have data coming in several XML files of
different formats, but with the same contents. You want these contents put
into a table.
If so, have you considered using XSLT to transform the different XML files
to a common format? Then you could leave your stored procedure alone and
still be very generic.
Just a thought...
- Kristoffer -
|||Kristoffer,
Yes, we have XML data coming in different formats but also with differing contents. The data does need to be extracted and placed in a database. What we've devised, is a generic database structure that assumes that each document type (i.e., home remortgag
e, home sale, transfer of equity, etc.) has an XML schema associated with it. We have a mapping table that basically stores the schemas for each document type (table column name, XPath node path and document type ID).
When we receive a document, a stored procedure will execute, a document type parameter is passed so we know what schema to query from the database and what I am trying to do now is output the XML fieldlist dynamically from that query. This will give us a
generic system that is able to accomodate many types of documents with little programming effort (aside from the schema and adding the schema to the mapping table in the DB).
Any thoughts?
Here's the latest code incase anyone is interested:
DECLARE @.caseID int
DECLARE @.CaseTypeID int
DECLARE @.selectFieldList nvarchar(4000)
DECLARE @.sqlStatement nvarchar(4000)
DECLARE @.fieldName nvarchar(4000)
DECLARE @.xmlRoot nvarchar(255)
SET @.caseID = 123456
SET @.CaseTypeID = 1
SET @.xmlRoot = '/official_copy'
-- Create a test table. This table schema is used by OPENXML as the
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
DECLARE @.primaryXPath nvarchar(255)
-- XML Input
SET @.doc ='
<official_copy><property_register><property_addres s><house_name>Little Brae</house_name><street_name>98 North Brink</street_name><correspondence><something>Little Brae</something></correspondence></property_address></property_register></official_copy>
'
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
-- BEGIN LOOP
DECLARE @.retcode int
DECLARE importCase CURSOR
FOR (SELECT document_node FROM case_document_attribute WHERE cd_type_id = @.CaseTypeID)
OPEN importCase
FETCH NEXT FROM importCase INTO @.primaryXPath
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
IF (@.@.FETCH_STATUS <> -2)
BEGIN
SET @.selectFieldList = @.primaryXPath
SET @.sqlStatement = 'INSERT INTO case_documents(cd_attribute) '
SET @.sqlStatement = @.sqlStatement + 'SELECT * FROM OPENXML('
SET @.sqlStatement = @.sqlStatement + Cast(@.iDoc as nvarchar(100))
SET @.sqlStatement = @.sqlStatement + ','
SET @.sqlStatement = @.sqlStatement + ''''+ @.xmlRoot + ''''
SET @.sqlStatement = @.sqlStatement + ',2)'
SET @.sqlStatement = @.sqlStatement + ' WITH ('
SET @.sqlStatement = @.sqlStatement + 'cd_attribute nvarchar(255) ' + '''' + @.primaryXPath + '''' + ')'
/*SET @.sqlStatement = 'INSERT INTO case_documents '
SET @.sqlStatement = @.sqlStatement + 'SELECT * FROM OpenXml(' + @.iDoc + ','
SET @.sqlStatement = @.sqlStatement + '''' + @.primaryXPath + ''''
SET @.sqlStatement = @.sqlStatement + ',2)'
SET @.sqlStatement = @.sqlStatement + 'WITH (' + '''' + @.primaryXPath + '''' + ')'*/
EXEC sp_executesql @.sqlStatement
PRINT @.sqlStatement
END
FETCH NEXT FROM importCase INTO @.primaryXPath
END
CLOSE importCase
DEALLOCATE importCase
EXEC sp_xml_removedocument @.idoc
TIA,
Denise