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
>.
>
Showing posts with label update. Show all posts
Showing posts with label update. Show all posts
Monday, March 26, 2012
Monday, March 19, 2012
Optimistic Concurrency Help
Hi,
I'm trying to implement Optimistic Concurrency in asp 2 but so far it has caused me nothing but problems.
First, when doing an UPDATE I tried to use the primary key & a timestamp field which I had in SQL Express.. VS 2005 generated the stored procedures fine however when it came to the actual updating I think there was a problem with the conversion of the timestamp field when it was being stored in a text box (in a FormView control). So.. as a result that failed. And also I checked sooo many places online and haven't been able to find any examples of code where a timestamp was used with success in asp2.
Next, I got ride of the timestamp type (in SQL Express database) and used a datetime and then.. I just implemented Optimistic Concurrency by passing in ALL the values (ie all the original values) like is proposed http://www.asp.net/learn/dataaccess/tutorial21vb.aspx?tabid=63 . This... works however I really do not want to have to pass in ALL these values (ie original and new).
Ideally I would like to be able to use the primary key & the datetime field to handle the Optimistic Concurrency checks where only the original values of both those fields are passed back into the stored procedure. Now.. I tried this as well, but I kept getting an error that suggests that (for some reason) the FormView or DataSource is passing ALL the values (original & new) into the dataset as opposed to only the original primary key & datetime fields & the new set of values.
Can ANYONE offer any help? I really would like not to have to pass in all these values.
Thanks in advance!
Why not add a "last_updated" column that is of type uniqueidentiifer (GUID) and use the NEWID() function to create a new guid whenever the table is updated. Obviously you would need to wrap this in a stored proc that all of your client code uses instead of the table direclty. (or you could use a UPDATE trigger).
Then just have the app compare the guid it retrieved on first read to the one on the row when it goes to do the update. If they dont match, you are done. Make sure to wrap the code in a transaction when you first a) check the guid to see if they match, then b) perform the actual update.
-David
|||Yes, although you will have to set up your stored procedures, and sqldatasource control manually.
First, a timestamp is not a datetime. It's technically a binary object, although it can be converted to/from an integer pretty easily. Also, you can't SET the value of a timestamp field. The whole point of a timestamp column is that the database maintains it. In it's current implementation (logically), every table that has a timestamp column maintains a counter at the table-level. Every time a row is changed, it increments this counter, and uses that value as the timestamp value. So every row that gets changed has a unique number.
As for the error about the datasource/formview passing ALL the values, it's most likely because you have the datasource's conflict detection property set to "CompareAllValues". Change it to "Overwrite". Then only the keys you've specified in the formview/gridview's datakeynames property, and the fields you've bound via a 2-way databind should be passed back.
|||Thanks for the responses!
dwlovell - Even if I use GUID instead of timestamp or datetime, I still have the prob with the update and getting the parameters passed nicely from the datasource to the stored procedure. Unless... If I put a GUID in the table.. and then in VS 2005 chose optimistic concurrency (when creating the tableadapter and stored procedures).. will VS 2005 recognise the GUID field (like it does with the timestamp field) and therefore use only the table key & that GUID fields to compare for the update??
Motley - I know that timestamp and datetime are different, I only used datetime because I (a) timestamp wasn't working and (b) datetime could be used (when tweeked) to implement the optimistic concurrency.
To avoid the error about passing all the values, I had used OverwriteChanges before but my problem with that is that I don't think the datetime field's original value is being passed to the stored procedure successfully. The only original value being passed was the key. Therefore, I'm getting a "SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM." error constantly (I believe that 0/0/0000 00:00:00 - i.e. an invalid value - is being passed from the dataset to the sql stored procedure). This error is linked with the following LOC in the stack trace: "int returnValue = this.Adapter.UpdateCommand.ExecuteNonQuery();".
The strange thing is that when I check the FormView1_ItemUpdating's event, I can see the datatime field's values in both e.NewValues & e.OldValues.. so I can't understand why the value is not being passed to the stored procedure.
A snippet from my <EditItemTemplate> (in the FormView) is below:
<asp:TextBox ID="RTextBox" runat="server" Text='<%# Bind("R")%>'>
</asp:TextBox><br />
RDescription:
<asp:TextBox ID="RDescriptionTextBox" runat="server" Text='<%# Bind("RDescription")%>'>
</asp:TextBox><br />
Lastupdate:
<asp:TextBox ID="LastupdateTextBox" runat="server" Text='<%# Bind("Lastupdate")%>'>
</asp:TextBox>
...
A snippet from my ObjectDataSource is below:
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" ConflictDetection="OverwriteChanges"
DeleteMethod="Delete" InsertMethod="Insert" OldValuesParameterFormatString="original_{0}"
SelectMethod="GetData" TypeName="DataSet1TableAdapters.RTableAdapter" UpdateMethod="Update">
<UpdateParameters>
<asp:Parameter Name="R" Type="Byte" />
<asp:Parameter Name="RDescription" Type="String" />
<asp:Parameter Name="Lastupdate" Type="DateTime" />
<asp:Parameter Name="original_R" Type="Byte" />
<asp:Parameter Name="original_Lastupdate" Type="DateTime" />
</UpdateParameters>
...
Here's a bit from the <UpdateCommand> in the DataSet (not sure it's relevant but.. doesn't hurt and maybe one of you gurus will find an answer to my prob there..):
<Parameters>
<Parameter AllowDbNull="False" AutogeneratedName="" DataSourceName="" DbType="Object" Direction="ReturnValue" ParameterName="@.RETURN_VALUE" Precision="0" Scale="0" Size="0" SourceColumnNullMapping="False" SourceVersion="Current">
</Parameter>
<Parameter AllowDbNull="False" AutogeneratedName="" DataSourceName="" DataTypeServer="tinyint" DbType="Byte" Direction="Input" ParameterName="@.R" Precision="0" ProviderType="TinyInt" Scale="0" Size="0" SourceColumn="R" SourceColumnNullMapping="False" SourceVersion="Current">
</Parameter>
<Parameter AllowDbNull="False" AutogeneratedName="" DataSourceName="" DataTypeServer="varchar(40)" DbType="AnsiString" Direction="Input" ParameterName="@.RDescription" Precision="0" ProviderType="VarChar" Scale="0" Size="0" SourceColumn="RDescription" SourceColumnNullMapping="False" SourceVersion="Current">
</Parameter>
<Parameter AllowDbNull="False" AutogeneratedName="" DataSourceName="" DataTypeServer="datetime" DbType="DateTime" Direction="Input" ParameterName="@.Lastupdate" Precision="0" ProviderType="DateTime" Scale="0" Size="0" SourceColumn="Lastupdate" SourceColumnNullMapping="False" SourceVersion="Current">
</Parameter>
<Parameter AllowDbNull="False" AutogeneratedName="" DataSourceName="" DataTypeServer="tinyint" DbType="Byte" Direction="Input" ParameterName="@.Original_R" Precision="0" ProviderType="TinyInt" Scale="0" Size="0" SourceColumn="R" SourceColumnNullMapping="False" SourceVersion="Original">
</Parameter>
<Parameter AllowDbNull="False" AutogeneratedName="" DataSourceName="" DataTypeServer="datetime" DbType="DateTime" Direction="Input" ParameterName="@.Original_Lastupdate" Precision="0" ProviderType="DateTime" Scale="0" Size="0" SourceColumn="Lastupdate" SourceColumnNullMapping="False" SourceVersion="Original">
</Parameter>
So... any more thoughts??|||
You might try posting this in the ObjectDatasource forum.
You might also try adding a parameter at the top of your list in the ObjectDataSource for the return value. It's possible the parameters are being shifted by one which would obviously try and force a different value into your datetime column and give the error you see (I could be completely wrong on that, I'm not a ObjectDataSource expert).
If this was a SqlDataSource, I would suggest catching the SqlDataSource_Updating event, and check the command object (and it's parameters), to make sure they all look correct at that stage.
|||Well you definitely have shed some light on this! And I thank you greatly for it! (See, even though you're not an ObjectDataSource expert, you still helped!!
This clearly indicates that the parameter is not being passed to the stored procedure and instead, is somehow being lost between the FormView and the ObjectDataSource. I really don't have a clue why this is happening though! To me, it's like (for optimistic concurrency) the ObjectDataSource control is to work when either ALL the original values are being passed in (i.e. ConflictDetection="CompareAllValues") or just the Key (i.e. ConflictDetection="OverwriteChanges") for the update. There seems to be no "mid-point" where one can send only a specific subset of original values to the ObjectDataSourceautomatically.. which really is a bummer!
Now what I'll have to do is get the original Lastupdate value from the form and set Lastupdate input parameter to that value in the ObjectDataSource_Updating event.
I've also taken your suggestion and posted this question in the ObjectDatasource forum.Here's the link for those of you interested.
Thanks again Motley!
|||I've written a tutorial to address this exact issue... after spending days figuring it all out.
http://www.primedigit.com/implementing-optimistic-concurrency-with-sql-timestamps/
Monday, March 12, 2012
Optimise multitable update
Hi
I've got the following scenario:
TableA (4 million rows)
TableB (20 000 rows)
I have two fields on TableA that are the unique fields on TableB,
which I use to set the foreign key from A to B:
UPDATE TableA
SET TableA.B_FK = TableB.B_PK
FROM TableA, TableB
WHERE
TableA.Code = TableB.Code
AND TableA.Name = TableB.Name
Code = varchar(5)
Name = varchar(50)
What would a suitable indexes be to optimise this query as it takes 4
hours to run?
I already have an index on TableA on "Code, Name" and TableB on "B_PK"
- takes 4 hours with these!
Any help? Should I have a covering index on TableB, i.e. "Code, Name,
B_PK" ?
Thanks
Sean
On 25 May 2004 08:14:42 -0700, Sean wrote:
>Hi
>I've got the following scenario:
>TableA (4 million rows)
>TableB (20 000 rows)
>
>I have two fields on TableA that are the unique fields on TableB,
>which I use to set the foreign key from A to B:
>UPDATE TableA
>SET TableA.B_FK = TableB.B_PK
>FROM TableA, TableB
>WHERE
>TableA.Code = TableB.Code
>AND TableA.Name = TableB.Name
>
>Code = varchar(5)
>Name = varchar(50)
>What would a suitable indexes be to optimise this query as it takes 4
>hours to run?
>I already have an index on TableA on "Code, Name" and TableB on "B_PK"
>- takes 4 hours with these!
>Any help? Should I have a covering index on TableB, i.e. "Code, Name,
>B_PK" ?
>Thanks
>Sean
Hi Sean,
Is the current index on TableA(Code, Name) a clustered index? Is it
defined as a unique index?
Do all 20000 rows in TableB match a row in TableA? If so, adding an index
on TableB won't do you any good. If all rows in a table have to be
processed anyway, a table scan is always the best way. If only a few of
the 20000 rows will match, an index on TableB(Code, Name) *might* help,
but I'm not sure. Test it. The covering index you suggest *might* help as
well, but you'll have to test that as well. But, as I said - only if the
majority of rows in TableB will not match against TableA.
Is there an index on TableA(B_FK)? If it is, see if you can remove it;
that saves the time to update this index as the update is carried out.
Check that there are no triggers on TableA. (If you have them, can't
disable them and they're the cause of the long execution, forget about the
query and start optimising the triggers first!)
And the most important thing: Check the execution plan!! From your
description, I would expect a table scan of TableB and an index seek on
the index on TableA(CodaA, Name).
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
I've got the following scenario:
TableA (4 million rows)
TableB (20 000 rows)
I have two fields on TableA that are the unique fields on TableB,
which I use to set the foreign key from A to B:
UPDATE TableA
SET TableA.B_FK = TableB.B_PK
FROM TableA, TableB
WHERE
TableA.Code = TableB.Code
AND TableA.Name = TableB.Name
Code = varchar(5)
Name = varchar(50)
What would a suitable indexes be to optimise this query as it takes 4
hours to run?
I already have an index on TableA on "Code, Name" and TableB on "B_PK"
- takes 4 hours with these!
Any help? Should I have a covering index on TableB, i.e. "Code, Name,
B_PK" ?
Thanks
Sean
On 25 May 2004 08:14:42 -0700, Sean wrote:
>Hi
>I've got the following scenario:
>TableA (4 million rows)
>TableB (20 000 rows)
>
>I have two fields on TableA that are the unique fields on TableB,
>which I use to set the foreign key from A to B:
>UPDATE TableA
>SET TableA.B_FK = TableB.B_PK
>FROM TableA, TableB
>WHERE
>TableA.Code = TableB.Code
>AND TableA.Name = TableB.Name
>
>Code = varchar(5)
>Name = varchar(50)
>What would a suitable indexes be to optimise this query as it takes 4
>hours to run?
>I already have an index on TableA on "Code, Name" and TableB on "B_PK"
>- takes 4 hours with these!
>Any help? Should I have a covering index on TableB, i.e. "Code, Name,
>B_PK" ?
>Thanks
>Sean
Hi Sean,
Is the current index on TableA(Code, Name) a clustered index? Is it
defined as a unique index?
Do all 20000 rows in TableB match a row in TableA? If so, adding an index
on TableB won't do you any good. If all rows in a table have to be
processed anyway, a table scan is always the best way. If only a few of
the 20000 rows will match, an index on TableB(Code, Name) *might* help,
but I'm not sure. Test it. The covering index you suggest *might* help as
well, but you'll have to test that as well. But, as I said - only if the
majority of rows in TableB will not match against TableA.
Is there an index on TableA(B_FK)? If it is, see if you can remove it;
that saves the time to update this index as the update is carried out.
Check that there are no triggers on TableA. (If you have them, can't
disable them and they're the cause of the long execution, forget about the
query and start optimising the triggers first!)
And the most important thing: Check the execution plan!! From your
description, I would expect a table scan of TableB and an index seek on
the index on TableA(CodaA, Name).
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
Optimise multitable update
Hi
I've got the following scenario:
TableA (4 million rows)
TableB (20 000 rows)
I have two fields on TableA that are the unique fields on TableB,
which I use to set the foreign key from A to B:
UPDATE TableA
SET TableA.B_FK = TableB.B_PK
FROM TableA, TableB
WHERE
TableA.Code = TableB.Code
AND TableA.Name = TableB.Name
Code = varchar(5)
Name = varchar(50)
What would a suitable indexes be to optimise this query as it takes 4
hours to run?
I already have an index on TableA on "Code, Name" and TableB on "B_PK"
- takes 4 hours with these!
Any help? Should I have a covering index on TableB, i.e. "Code, Name,
B_PK" ?
Thanks
SeanOn 25 May 2004 08:14:42 -0700, Sean wrote:
>Hi
>I've got the following scenario:
>TableA (4 million rows)
>TableB (20 000 rows)
>
>I have two fields on TableA that are the unique fields on TableB,
>which I use to set the foreign key from A to B:
>UPDATE TableA
>SET TableA.B_FK = TableB.B_PK
>FROM TableA, TableB
>WHERE
> TableA.Code = TableB.Code
> AND TableA.Name = TableB.Name
>
>Code = varchar(5)
>Name = varchar(50)
>What would a suitable indexes be to optimise this query as it takes 4
>hours to run?
>I already have an index on TableA on "Code, Name" and TableB on "B_PK"
>- takes 4 hours with these!
>Any help? Should I have a covering index on TableB, i.e. "Code, Name,
>B_PK" ?
>Thanks
>Sean
Hi Sean,
Is the current index on TableA(Code, Name) a clustered index? Is it
defined as a unique index?
Do all 20000 rows in TableB match a row in TableA? If so, adding an index
on TableB won't do you any good. If all rows in a table have to be
processed anyway, a table scan is always the best way. If only a few of
the 20000 rows will match, an index on TableB(Code, Name) *might* help,
but I'm not sure. Test it. The covering index you suggest *might* help as
well, but you'll have to test that as well. But, as I said - only if the
majority of rows in TableB will not match against TableA.
Is there an index on TableA(B_FK)? If it is, see if you can remove it;
that saves the time to update this index as the update is carried out.
Check that there are no triggers on TableA. (If you have them, can't
disable them and they're the cause of the long execution, forget about the
query and start optimising the triggers first!)
And the most important thing: Check the execution plan!! From your
description, I would expect a table scan of TableB and an index seek on
the index on TableA(CodaA, Name).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
I've got the following scenario:
TableA (4 million rows)
TableB (20 000 rows)
I have two fields on TableA that are the unique fields on TableB,
which I use to set the foreign key from A to B:
UPDATE TableA
SET TableA.B_FK = TableB.B_PK
FROM TableA, TableB
WHERE
TableA.Code = TableB.Code
AND TableA.Name = TableB.Name
Code = varchar(5)
Name = varchar(50)
What would a suitable indexes be to optimise this query as it takes 4
hours to run?
I already have an index on TableA on "Code, Name" and TableB on "B_PK"
- takes 4 hours with these!
Any help? Should I have a covering index on TableB, i.e. "Code, Name,
B_PK" ?
Thanks
SeanOn 25 May 2004 08:14:42 -0700, Sean wrote:
>Hi
>I've got the following scenario:
>TableA (4 million rows)
>TableB (20 000 rows)
>
>I have two fields on TableA that are the unique fields on TableB,
>which I use to set the foreign key from A to B:
>UPDATE TableA
>SET TableA.B_FK = TableB.B_PK
>FROM TableA, TableB
>WHERE
> TableA.Code = TableB.Code
> AND TableA.Name = TableB.Name
>
>Code = varchar(5)
>Name = varchar(50)
>What would a suitable indexes be to optimise this query as it takes 4
>hours to run?
>I already have an index on TableA on "Code, Name" and TableB on "B_PK"
>- takes 4 hours with these!
>Any help? Should I have a covering index on TableB, i.e. "Code, Name,
>B_PK" ?
>Thanks
>Sean
Hi Sean,
Is the current index on TableA(Code, Name) a clustered index? Is it
defined as a unique index?
Do all 20000 rows in TableB match a row in TableA? If so, adding an index
on TableB won't do you any good. If all rows in a table have to be
processed anyway, a table scan is always the best way. If only a few of
the 20000 rows will match, an index on TableB(Code, Name) *might* help,
but I'm not sure. Test it. The covering index you suggest *might* help as
well, but you'll have to test that as well. But, as I said - only if the
majority of rows in TableB will not match against TableA.
Is there an index on TableA(B_FK)? If it is, see if you can remove it;
that saves the time to update this index as the update is carried out.
Check that there are no triggers on TableA. (If you have them, can't
disable them and they're the cause of the long execution, forget about the
query and start optimising the triggers first!)
And the most important thing: Check the execution plan!! From your
description, I would expect a table scan of TableB and an index seek on
the index on TableA(CodaA, Name).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Saturday, February 25, 2012
Operand type clash error
Hello!
I am trying to update a DetailsView but I keep getting the error of Operand type clash: int is incompatible with text. I have double checked the stored procedure but I don't see any obvious offending parameters. ID is the only parameter that is an interger. I am not trying to update the ID at all, just for the filter purposes to update the records.
ALTER PROCEDURE [dbo].[proc_update]
/*
(
@.parameter1 int = 5,
@.parameter2 datatype OUTPUT
)
*/
@.BUILDING_SV float,
@.BUILDING_ADDS float,
@.BUILDING_DEL float,
@.BUILDING_TOTAL float,
@.ME_SV float,
@.ME_ADDS float,
@.ME_DEL float,
@.ME_TOTAL float,
@.PATTERNS_SV float,
@.PATTERNS_ADDS float,
@.PATTERNS_DEL float,
@.PATTERNS_TOTAL float,
@.SS_VALUE float,
@.SS_TOT float,
@.COMMENTS text,
@.OCCUPANCY_TYPE varchar(255),
@.REVISED_BY varchar(255),
@.DRAWINGS float,
@.INVENTORY float,
@.TOTAL float,
@.DIVISION varchar(255),
@.LOCATION varchar(255),
@.LOCATIONCODE varchar(255),
@.ADDRESS1 varchar(255),
@.ADDRESS2 varchar(255),
@.ADDRESS3 varchar(255),
@.ADDRESS4 varchar(255),
@.PROP_ID float,
@.POST_DATE smalldatetime,
@.ID int
AS
/* SET NOCOUNT ON */
UPDATE dbo.PROPERTY_VALUES
SET BUILDING_SV = @.BUILDING_SV, BUILDING_ADDS = @.BUILDING_ADDS, BUILDING_DEL = @.BUILDING_DEL, BUILDING_TOTAL = @.BUILDING_TOTAL, ME_SV = @.ME_SV, ME_ADDS = @.ME_ADDS, ME_DEL = @.ME_DEL, ME_TOTAL = @.ME_TOTAL, PATTERNS_SV = @.PATTERNS_SV, PATTERNS_ADDS = @.PATTERNS_ADDS, PATTERNS_DEL = @.PATTERNS_DEL, PATTERNS_TOTAL = @.PATTERNS_TOTAL, SS_VALUE = @.SS_VALUE, SS_TOT = @.SS_TOT, COMMENTS = @.COMMENTS,OCCUPANCY_TYPE = @.OCCUPANCY_TYPE, REVISED_BY = @.REVISED_BY, DRAWINGS = @.DRAWINGS, INVENTORY = @.INVENTORY, TOTAL = @.TOTAL, DIVISION = @.DIVISION, LOCATION = @.LOCATION, LOCATIONCODE = @.LOCATIONCODE, ADDRESS1 = @.ADDRESS1, ADDRESS2 = @.ADDRESS2, ADDRESS3 = @.ADDRESS3, ADDRESS4 = @.ADDRESS4, PROP_ID = @.PROP_ID, POST_DATE = @.POST_DATE
WHERE [ID] = @.ID
RETURN
Help!
Thanks.
I did more debugging. I discovered that the null values in the DetailsView are passing as "1" to the database. Anyone know how to debug this? Thanks.
|||You probably have some default values set to 1, and "ConvertNullToDefaultValue" set to true (I forget the exact property name, but it's close).
|||Thanks.
I don't recall setting the default values to 1. I am stumped why it is doing this. Is CONVERTNULLTODEFAULTVALUE a part of the codebehind or in the source page?
Thanks!
|||Please post the sqldatasource source (Everything between <asp:SqlDataSource and </asp:SqlDataSource>).|||Here is it.
<asp:SqlDataSourceID="Update"runat="server"
SelectCommand="proc_update_select"UpdateCommand="proc_update"SelectCommandType="StoredProcedure"UpdateCommandType="StoredProcedure">
<UpdateParameters>
<asp:ControlParameterControlID="DetailsView1"Name="BUILDING_SV"PropertyName="SelectedValue"/>
<asp:ControlParameterControlID="DetailsView1"Name="BUILDING_ADDS"PropertyName="SelectedValue"/>
<asp:ControlParameterControlID="DetailsView1"Name="BUILDING_DEL"PropertyName="SelectedValue"/>
<asp:ControlParameterControlID="DetailsView1"Name="BUILDING_TOTAL"PropertyName="SelectedValue"/>
<asp:ControlParameterControlID="DetailsView1"Name="ME_SV"PropertyName="SelectedValue"/>
<asp:ControlParameterControlID="DetailsView1"Name="ME_ADDS"PropertyName="SelectedValue"/>
<asp:ControlParameterControlID="DetailsView1"Name="ME_DEL"PropertyName="SelectedValue"/>
<asp:ControlParameterControlID="DetailsView1"Name="ME_TOTAL"PropertyName="SelectedValue"/>
<asp:ControlParameterControlID="DetailsView1"Name="PATTERNS_SV"PropertyName="SelectedValue"/>
<asp:ControlParameterControlID="DetailsView1"Name="PATTERNS_ADDS"PropertyName="SelectedValue"/>
<asp:ControlParameterControlID="DetailsView1"Name="PATTERNS_DEL"PropertyName="SelectedValue"/>
<asp:ControlParameterControlID="DetailsView1"Name="PATTERNS_TOTAL"PropertyName="SelectedValue"/>
<asp:ControlParameterControlID="DetailsView1"Name="SS_VALUE"PropertyName="SelectedValue"/>
<asp:ControlParameterControlID="DetailsView1"Name="SS_TOT"PropertyName="SelectedValue"/>
<asp:ControlParameterControlID="DetailsView1"Name="COMMENTS"PropertyName="SelectedValue"/>
<asp:ControlParameterControlID="DetailsView1"Name="OCCUPANCY_TYPE"PropertyName="SelectedValue"/>
<asp:ControlParameterControlID="DetailsView1"Name="POST_DATE"PropertyName="SelectedValue"/>
<asp:ControlParameterControlID="DetailsView1"Name="REVISED_BY"PropertyName="SelectedValue"/>
<asp:ControlParameterControlID="DetailsView1"Name="DRAWINGS"PropertyName="SelectedValue"/>
<asp:ControlParameterControlID="DetailsView1"Name="INVENTORY"PropertyName="SelectedValue"/>
<asp:ControlParameterControlID="DetailsView1"Name="TOTAL"PropertyName="SelectedValue"/>
<asp:ControlParameterControlID="DetailsView1"Name="DIVISION"PropertyName="SelectedValue"/>
<asp:ControlParameterControlID="DetailsView1"Name="LOCATION"PropertyName="SelectedValue"/>
<asp:ControlParameterControlID="DetailsView1"Name="LOCATIONCODE"PropertyName="SelectedValue"/>
<asp:ControlParameterControlID="DetailsView1"Name="ADDRESS1"PropertyName="SelectedValue"/>
<asp:ControlParameterControlID="DetailsView1"Name="ADDRESS2"PropertyName="SelectedValue"/>
<asp:ControlParameterControlID="DetailsView1"Name="ADDRESS4"PropertyName="SelectedValue"/>
<asp:ControlParameterControlID="DetailsView1"Name="ADDRESS3"PropertyName="SelectedValue"/>
<asp:ControlParameterControlID="DetailsView1"Name="PROP_ID"PropertyName="SelectedValue"/>
<asp:ControlParameterControlID="DetailsView1"Name="ID"PropertyName="SelectedValue"/>
</UpdateParameters>
</asp:SqlDataSource>
|||Why is every update parameter a ControlParameter? The parameters that are getting their values from being databound should be a plain "Parameter". Also what is the selectedvalue of detailsview1 in your test? 1?
Please change the parameters from type ControlParameter to type Parameter. If the problem still persists, then launch your app, go to the detailsview, enter into edit mode (So the dropdowns show), then post the HTML that one of the dropdowns that is bound to a field that is submitting a 1 by clicksing Tools->View Source in IE, and copying everything between the <SELECT and </SELECT> tags.
|||I did what you said. It now no longer passing the null values as 1! Thank you!
However, it is not updating to the database even though I see the parameters updating. I am using the trace feature and the code below to trace the updating/updated parameters.
ProtectedSub Update_Updating(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles Update.Updating
For xAsInteger = 0To e.Command.Parameters.Count - 1
Trace.Write(e.Command.Parameters(x).ParameterName)
Trace.Write(CStr(e.Command.Parameters(x).Value))
Next
Any Ideas?
EndSub
Protected Sub Update_Updated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles Update.Updated
For x As Integer = 0 To e.Command.Parameters.Count - 1
Trace.Write(e.Command.Parameters(x).ParameterName)
Trace.Write(CStr(e.Command.Parameters(x).Value))
Next
End Sub
Conversion from type 'DBNull' to type 'String' is not valid.
Here is the code for the updated parameters
ProtectedSub Update_Updated(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceStatusEventArgs)Handles Update.Updated
For xAsInteger = 0To e.Command.Parameters.Count - 1
Trace.Write(e.Command.Parameters(x).ParameterName)
Trace.Write(CStr(e.Command.Parameters(x).Value))
Next
EndSub
|||oops sorry for the last post...let me repost again.
I did what you said. It now no longer passing the null values as 1! Thank you!
However, it is not updating to the database even though I see the parameters updating. I am using the trace feature and the code below to trace the updating/updated parameters.
Here's the code for updating parameters
Protected Sub Update_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles Update.Updating
For x As Integer = 0 To e.Command.Parameters.Count - 1
Trace.Write(e.Command.Parameters(x).ParameterName)
Trace.Write(CStr(e.Command.Parameters(x).Value))
Next
End Sub
Protected Sub Update_Updated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles Update.Updated
For x As Integer = 0 To e.Command.Parameters.Count - 1
Trace.Write(e.Command.Parameters(x).ParameterName)
Trace.Write(CStr(e.Command.Parameters(x).Value))
Next
End Sub
Here is the code for the updated parameters
Protected Sub Update_Updated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles Update.Updated
For x As Integer = 0 To e.Command.Parameters.Count - 1
Trace.Write(e.Command.Parameters(x).ParameterName)
Trace.Write(CStr(e.Command.Parameters(x).Value))
Next
End Sub
I got an error for the updated parameters code
Conversion from type 'DBNull' to type 'String' is not valid.
Any Ideas?
|||Help?
It won't update the fields that currently have null values...
Help again? :)
|||Are you getting an error? Is it updating the fields that don't have null values?|||Yes to both.
I am still having the error that I posted above and the update is also updating one of the columns that didn't have a null value with a null value.
Strangely, enough the update works if there are null values but it will still catch the exception of this error Conversion from type 'DBNull' to type 'String' is not valid when I step into the updated parameters. I would like to fix that so there won't be any problems in the future even though it still works. The another problem is that the update function will update this column named BLDG_SV with a null value even though it had a value, previously.
Thanks!!
|||Is the column named BLDG_SV or BUILDING_SV?|||yes sorry it is BUILDING_SV, forgive me.Monday, February 20, 2012
OPENXML Question
How do I return the number of rows inserted/updated using OPENXML?
I tried to use the @.@.ROWCOUNT function, but it always returns a 0.
Generic update example trying to return the # of rows updated:
declare @.i int
exec sp_xml_preparedocument @.i output,
'<mydata>
<test xmlID="3" xmlData="blah blah blah"/>
<test xmlID="1" xmlData="blah"/>
</mydata>'
update test
set test.xmlData = ox.xmlData
from OpenXml(@.i, 'mydata/test')
with (xmlID int, xmlData nvarchar(30)) ox
where test.xmlID = ox.xmlID
RETURN @.@.ROWCOUNT --Returns a 0
exec sp_xml_removedocument @.i
Thanks,It will return the rowcount.
Can you check if the data was really updated.
I think its the data problem.
Or try using print @.@.rowcount as see.
"Robert" wrote:
> How do I return the number of rows inserted/updated using OPENXML?
> I tried to use the @.@.ROWCOUNT function, but it always returns a 0.
> Generic update example trying to return the # of rows updated:
> declare @.i int
> exec sp_xml_preparedocument @.i output,
> '<mydata>
> <test xmlID="3" xmlData="blah blah blah"/>
> <test xmlID="1" xmlData="blah"/>
> </mydata>'
> update test
> set test.xmlData = ox.xmlData
> from OpenXml(@.i, 'mydata/test')
> with (xmlID int, xmlData nvarchar(30)) ox
> where test.xmlID = ox.xmlID
> RETURN @.@.ROWCOUNT --Returns a 0
> exec sp_xml_removedocument @.i
>
> Thanks,
>|||Check my procedure here:
It works for me (rowcount stuff that is)
if exists (select * from sysobjects
where id = object_id('uspTitleUpdate') and sysstat & 0xf = 4)
drop procedure uspTitleUpdate
GO
CREATE PROCEDURE dbo.uspTitleUpdate (
@.xml_doc TEXT ,
@.numberRowsAffected int output --return
)
AS
SET NOCOUNT ON
DECLARE @.hdoc INT -- handle to XML doc
DECLARE @.errorTracker int -- used to "remember" the @.@.ERROR
DECLARE @.updateRowCount int
DECLARE @.insertRowCount int
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @.hdoc OUTPUT, @.XML_Doc
-- build a table (variable table) to store the xml-based result set
DECLARE @.titleupdate TABLE (
identityid int IDENTITY (1,1) ,
title_id varchar(6) ,
title varchar(80) ,
type varchar(32) ,
pub_id varchar(32) ,
price money ,
advance money ,
royalty varchar(32) ,
ytd_sales varchar(32) ,
notes TEXT ,
pubdate datetime ,
--used to differeniate between existing (update) and new ones (insert)
alreadyExists bit DEFAULT 0
)
--the next call will take the info IN the @.hdoc(with is the holder for
@.xml_doc), and put it IN a variableTable
INSERT @.titleupdate
(
title_id ,
title ,
type ,
pub_id ,
price ,
advance ,
royalty ,
ytd_sales ,
notes ,
pubdate ,
alreadyExists
)
SELECT
title_id ,
title ,
type ,
pub_id ,
price ,
advance ,
royalty ,
ytd_sales ,
notes ,
dbo.udf_convert_xml_date_to_datetime (pubdate) ,
0
FROM
-- use the correct XPath .. the second arg ("2" here) distinquishes
-- between textnode or an attribute, most times with
--.NET typed datasets, its a "2"
--This xpath MUST match the syntax of the DataSet
OPENXML (@.hdoc, '/TitlesDS/Titles', 2) WITH (
title_id varchar(6) ,
title varchar(80) ,
type varchar(32) ,
pub_id varchar(32) ,
price money ,
advance money ,
royalty varchar(32) ,
ytd_sales varchar(32) ,
notes TEXT ,
pubdate varchar(32) ,
alreadyExists bit
)
--select * from @.titleupdate
--lets differeniate between existing (update) and new ones (insert)
Update @.titleupdate
SET
alreadyExists = 1
FROM
@.titleupdate tu , titles
WHERE
--this where clause is a little weird, usually you'll must match
--primary key (int or global identifiers)
ltrim(rtrim(upper(titles.title_id))) = ltrim(rtrim(upper(tu.title_id)))
SET NOCOUNT OFF
Update
titles
set
title = tu.title ,
type = tu.type ,
pub_id = tu.pub_id ,
price = tu.price ,
advance = tu.advance ,
royalty = tu.royalty ,
ytd_sales = tu.ytd_sales ,
notes = tu.notes ,
pubdate = tu.pubdate
FROM
@.titleupdate tu , titles
WHERE
ltrim(rtrim(upper(titles.title_id))) = ltrim(rtrim(upper(tu.title_id)))
AND
tu.alreadyExists <> 0
Select @.updateRowCount = @.@.ROWCOUNT
INSERT INTO titles
(
title_id ,
title ,
type ,
pub_id ,
price ,
advance ,
royalty ,
ytd_sales ,
notes ,
pubdate
)
Select
title_id ,
title ,
type ,
pub_id ,
price ,
advance ,
royalty ,
ytd_sales ,
notes ,
pubdate
FROM
@.titleupdate
WHERE
alreadyExists = 0
Select @.insertRowCount = @.@.ROWCOUNT
select @.numberRowsAffected = @.insertRowCount + @.updateRowCount
--select * from titles
SET NOCOUNT OFF
GO
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:3C2C4124-DEE9-4A0E-82CE-FE91CCFDC5AF@.microsoft.com...
> How do I return the number of rows inserted/updated using OPENXML?
> I tried to use the @.@.ROWCOUNT function, but it always returns a 0.
> Generic update example trying to return the # of rows updated:
> declare @.i int
> exec sp_xml_preparedocument @.i output,
> '<mydata>
> <test xmlID="3" xmlData="blah blah blah"/>
> <test xmlID="1" xmlData="blah"/>
> </mydata>'
> update test
> set test.xmlData = ox.xmlData
> from OpenXml(@.i, 'mydata/test')
> with (xmlID int, xmlData nvarchar(30)) ox
> where test.xmlID = ox.xmlID
> RETURN @.@.ROWCOUNT --Returns a 0
> exec sp_xml_removedocument @.i
>
> Thanks,
>
I tried to use the @.@.ROWCOUNT function, but it always returns a 0.
Generic update example trying to return the # of rows updated:
declare @.i int
exec sp_xml_preparedocument @.i output,
'<mydata>
<test xmlID="3" xmlData="blah blah blah"/>
<test xmlID="1" xmlData="blah"/>
</mydata>'
update test
set test.xmlData = ox.xmlData
from OpenXml(@.i, 'mydata/test')
with (xmlID int, xmlData nvarchar(30)) ox
where test.xmlID = ox.xmlID
RETURN @.@.ROWCOUNT --Returns a 0
exec sp_xml_removedocument @.i
Thanks,It will return the rowcount.
Can you check if the data was really updated.
I think its the data problem.
Or try using print @.@.rowcount as see.
"Robert" wrote:
> How do I return the number of rows inserted/updated using OPENXML?
> I tried to use the @.@.ROWCOUNT function, but it always returns a 0.
> Generic update example trying to return the # of rows updated:
> declare @.i int
> exec sp_xml_preparedocument @.i output,
> '<mydata>
> <test xmlID="3" xmlData="blah blah blah"/>
> <test xmlID="1" xmlData="blah"/>
> </mydata>'
> update test
> set test.xmlData = ox.xmlData
> from OpenXml(@.i, 'mydata/test')
> with (xmlID int, xmlData nvarchar(30)) ox
> where test.xmlID = ox.xmlID
> RETURN @.@.ROWCOUNT --Returns a 0
> exec sp_xml_removedocument @.i
>
> Thanks,
>|||Check my procedure here:
It works for me (rowcount stuff that is)
if exists (select * from sysobjects
where id = object_id('uspTitleUpdate') and sysstat & 0xf = 4)
drop procedure uspTitleUpdate
GO
CREATE PROCEDURE dbo.uspTitleUpdate (
@.xml_doc TEXT ,
@.numberRowsAffected int output --return
)
AS
SET NOCOUNT ON
DECLARE @.hdoc INT -- handle to XML doc
DECLARE @.errorTracker int -- used to "remember" the @.@.ERROR
DECLARE @.updateRowCount int
DECLARE @.insertRowCount int
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @.hdoc OUTPUT, @.XML_Doc
-- build a table (variable table) to store the xml-based result set
DECLARE @.titleupdate TABLE (
identityid int IDENTITY (1,1) ,
title_id varchar(6) ,
title varchar(80) ,
type varchar(32) ,
pub_id varchar(32) ,
price money ,
advance money ,
royalty varchar(32) ,
ytd_sales varchar(32) ,
notes TEXT ,
pubdate datetime ,
--used to differeniate between existing (update) and new ones (insert)
alreadyExists bit DEFAULT 0
)
--the next call will take the info IN the @.hdoc(with is the holder for
@.xml_doc), and put it IN a variableTable
INSERT @.titleupdate
(
title_id ,
title ,
type ,
pub_id ,
price ,
advance ,
royalty ,
ytd_sales ,
notes ,
pubdate ,
alreadyExists
)
SELECT
title_id ,
title ,
type ,
pub_id ,
price ,
advance ,
royalty ,
ytd_sales ,
notes ,
dbo.udf_convert_xml_date_to_datetime (pubdate) ,
0
FROM
-- use the correct XPath .. the second arg ("2" here) distinquishes
-- between textnode or an attribute, most times with
--.NET typed datasets, its a "2"
--This xpath MUST match the syntax of the DataSet
OPENXML (@.hdoc, '/TitlesDS/Titles', 2) WITH (
title_id varchar(6) ,
title varchar(80) ,
type varchar(32) ,
pub_id varchar(32) ,
price money ,
advance money ,
royalty varchar(32) ,
ytd_sales varchar(32) ,
notes TEXT ,
pubdate varchar(32) ,
alreadyExists bit
)
--select * from @.titleupdate
--lets differeniate between existing (update) and new ones (insert)
Update @.titleupdate
SET
alreadyExists = 1
FROM
@.titleupdate tu , titles
WHERE
--this where clause is a little weird, usually you'll must match
--primary key (int or global identifiers)
ltrim(rtrim(upper(titles.title_id))) = ltrim(rtrim(upper(tu.title_id)))
SET NOCOUNT OFF
Update
titles
set
title = tu.title ,
type = tu.type ,
pub_id = tu.pub_id ,
price = tu.price ,
advance = tu.advance ,
royalty = tu.royalty ,
ytd_sales = tu.ytd_sales ,
notes = tu.notes ,
pubdate = tu.pubdate
FROM
@.titleupdate tu , titles
WHERE
ltrim(rtrim(upper(titles.title_id))) = ltrim(rtrim(upper(tu.title_id)))
AND
tu.alreadyExists <> 0
Select @.updateRowCount = @.@.ROWCOUNT
INSERT INTO titles
(
title_id ,
title ,
type ,
pub_id ,
price ,
advance ,
royalty ,
ytd_sales ,
notes ,
pubdate
)
Select
title_id ,
title ,
type ,
pub_id ,
price ,
advance ,
royalty ,
ytd_sales ,
notes ,
pubdate
FROM
@.titleupdate
WHERE
alreadyExists = 0
Select @.insertRowCount = @.@.ROWCOUNT
select @.numberRowsAffected = @.insertRowCount + @.updateRowCount
--select * from titles
SET NOCOUNT OFF
GO
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:3C2C4124-DEE9-4A0E-82CE-FE91CCFDC5AF@.microsoft.com...
> How do I return the number of rows inserted/updated using OPENXML?
> I tried to use the @.@.ROWCOUNT function, but it always returns a 0.
> Generic update example trying to return the # of rows updated:
> declare @.i int
> exec sp_xml_preparedocument @.i output,
> '<mydata>
> <test xmlID="3" xmlData="blah blah blah"/>
> <test xmlID="1" xmlData="blah"/>
> </mydata>'
> update test
> set test.xmlData = ox.xmlData
> from OpenXml(@.i, 'mydata/test')
> with (xmlID int, xmlData nvarchar(30)) ox
> where test.xmlID = ox.xmlID
> RETURN @.@.ROWCOUNT --Returns a 0
> exec sp_xml_removedocument @.i
>
> Thanks,
>
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
>
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 INSERT/UPDATE and NTEXT
Hi,
I'm having problems inserting/updating a NTEXT field using OPENXML.
The field always gets a blank value with any parameters it receives.
Here's the (simplified) query:
---
declare @.doc int
-- Actually, it's a parameter
declare @.xml nvarchar(4000);
set @.xml = N'
<Article>
<Id>be60839f-cc33-4a9f-af91-e3bbcb7617ac</Id>
<Content>yada yada yada</Content>
</Article>'
EXEC sp_xml_preparedocument @.doc OUTPUT, @.xml
UPDATE Article
SET Content = new.Content
FROM OPENXML(@.doc, 'Article', 3) WITH Article new
WHERE Article.Id = new.Id
IF @.@.rowcount = 0
INSERT INTO Article
SELECT *
FROM OPENXML(@.doc, 'Article', 3) WITH Article
EXEC sp_xml_removedocument @.doc
---
Oddly, "SELECT * FROM OPENXML(@.doc, 'Article', 3) WITH Article" shows the
value.
Any ideas?
DiegoI think you need to revise your OPENXML syntax a bit. Try the following
instead of what you have:
UPDATE Article
SET Content = new.Content
FROM OPENXML(@.doc, 'Article', 3)
WITH (
id uniqueidentifier 'Id',
content nvarchar(400) 'Content'
) new
WHERE Article.Id = new.Id
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Diego Mijelshon" <no@.thanks.com> wrote in message
news:ei8UgeEGFHA.1188@.tk2msftngp13.phx.gbl...
> Hi,
> I'm having problems inserting/updating a NTEXT field using OPENXML.
> The field always gets a blank value with any parameters it receives.
> Here's the (simplified) query:
> ---
> declare @.doc int
> -- Actually, it's a parameter
> declare @.xml nvarchar(4000);
> set @.xml = N'
> <Article>
> <Id>be60839f-cc33-4a9f-af91-e3bbcb7617ac</Id>
> <Content>yada yada yada</Content>
> </Article>'
> EXEC sp_xml_preparedocument @.doc OUTPUT, @.xml
> UPDATE Article
> SET Content = new.Content
> FROM OPENXML(@.doc, 'Article', 3) WITH Article new
> WHERE Article.Id = new.Id
> IF @.@.rowcount = 0
> INSERT INTO Article
> SELECT *
> FROM OPENXML(@.doc, 'Article', 3) WITH Article
> EXEC sp_xml_removedocument @.doc
> ---
> Oddly, "SELECT * FROM OPENXML(@.doc, 'Article', 3) WITH Article" shows the
> value.
> Any ideas?
> Diego
>|||Adam,
Thanks for your answer, but that didn't fix it.
Besides, the beauty in the "WITH tablename" clause, combined with FOR XML
AUTO, is that I get "free" O/R-M using XML Serializing.
I'll share my solution. It still looks like a bug to me...
---
SELECT *
INTO ##tmp
FROM OPENXML(@.doc, 'Article', 3) WITH Article
UPDATE Article
SET Content = new.Content
FROM ##tmp new
WHERE Article.Id = new.Id
IF @.@.rowcount = 0
INSERT INTO Article
SELECT *
FROM ##tmp
DROP TABLE ##tmp
---
As you can see, inserting in a temporal table does the trick.
Diego
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OxCpmzEGFHA.1476@.TK2MSFTNGP09.phx.gbl...
> I think you need to revise your OPENXML syntax a bit. Try the following
> instead of what you have:
>
> UPDATE Article
> SET Content = new.Content
> FROM OPENXML(@.doc, 'Article', 3)
> WITH (
> id uniqueidentifier 'Id',
> content nvarchar(400) 'Content'
> ) new
> WHERE Article.Id = new.Id
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Diego Mijelshon" <no@.thanks.com> wrote in message
> news:ei8UgeEGFHA.1188@.tk2msftngp13.phx.gbl...
the
>|||"Diego Mijelshon" <no@.thanks.com> wrote in message
news:um$u8aIGFHA.548@.TK2MSFTNGP14.phx.gbl...
> DROP TABLE ##tmp
> ---
> As you can see, inserting in a temporal table does the trick.
I'm glad you found something that works. FYI, you should probably use a
local temporary table (single #) instead of a global one (##) unless you
need access to this same temp table from other processes...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uWtvvjIGFHA.560@.TK2MSFTNGP15.phx.gbl...
> "Diego Mijelshon" <no@.thanks.com> wrote in message
> news:um$u8aIGFHA.548@.TK2MSFTNGP14.phx.gbl...
> I'm glad you found something that works. FYI, you should probably use a
> local temporary table (single #) instead of a global one (##) unless you
> need access to this same temp table from other processes...
Thanks for the correction, I had it backwards :-)
Diego
I'm having problems inserting/updating a NTEXT field using OPENXML.
The field always gets a blank value with any parameters it receives.
Here's the (simplified) query:
---
declare @.doc int
-- Actually, it's a parameter
declare @.xml nvarchar(4000);
set @.xml = N'
<Article>
<Id>be60839f-cc33-4a9f-af91-e3bbcb7617ac</Id>
<Content>yada yada yada</Content>
</Article>'
EXEC sp_xml_preparedocument @.doc OUTPUT, @.xml
UPDATE Article
SET Content = new.Content
FROM OPENXML(@.doc, 'Article', 3) WITH Article new
WHERE Article.Id = new.Id
IF @.@.rowcount = 0
INSERT INTO Article
SELECT *
FROM OPENXML(@.doc, 'Article', 3) WITH Article
EXEC sp_xml_removedocument @.doc
---
Oddly, "SELECT * FROM OPENXML(@.doc, 'Article', 3) WITH Article" shows the
value.
Any ideas?
DiegoI think you need to revise your OPENXML syntax a bit. Try the following
instead of what you have:
UPDATE Article
SET Content = new.Content
FROM OPENXML(@.doc, 'Article', 3)
WITH (
id uniqueidentifier 'Id',
content nvarchar(400) 'Content'
) new
WHERE Article.Id = new.Id
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Diego Mijelshon" <no@.thanks.com> wrote in message
news:ei8UgeEGFHA.1188@.tk2msftngp13.phx.gbl...
> Hi,
> I'm having problems inserting/updating a NTEXT field using OPENXML.
> The field always gets a blank value with any parameters it receives.
> Here's the (simplified) query:
> ---
> declare @.doc int
> -- Actually, it's a parameter
> declare @.xml nvarchar(4000);
> set @.xml = N'
> <Article>
> <Id>be60839f-cc33-4a9f-af91-e3bbcb7617ac</Id>
> <Content>yada yada yada</Content>
> </Article>'
> EXEC sp_xml_preparedocument @.doc OUTPUT, @.xml
> UPDATE Article
> SET Content = new.Content
> FROM OPENXML(@.doc, 'Article', 3) WITH Article new
> WHERE Article.Id = new.Id
> IF @.@.rowcount = 0
> INSERT INTO Article
> SELECT *
> FROM OPENXML(@.doc, 'Article', 3) WITH Article
> EXEC sp_xml_removedocument @.doc
> ---
> Oddly, "SELECT * FROM OPENXML(@.doc, 'Article', 3) WITH Article" shows the
> value.
> Any ideas?
> Diego
>|||Adam,
Thanks for your answer, but that didn't fix it.
Besides, the beauty in the "WITH tablename" clause, combined with FOR XML
AUTO, is that I get "free" O/R-M using XML Serializing.
I'll share my solution. It still looks like a bug to me...
---
SELECT *
INTO ##tmp
FROM OPENXML(@.doc, 'Article', 3) WITH Article
UPDATE Article
SET Content = new.Content
FROM ##tmp new
WHERE Article.Id = new.Id
IF @.@.rowcount = 0
INSERT INTO Article
SELECT *
FROM ##tmp
DROP TABLE ##tmp
---
As you can see, inserting in a temporal table does the trick.
Diego
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OxCpmzEGFHA.1476@.TK2MSFTNGP09.phx.gbl...
> I think you need to revise your OPENXML syntax a bit. Try the following
> instead of what you have:
>
> UPDATE Article
> SET Content = new.Content
> FROM OPENXML(@.doc, 'Article', 3)
> WITH (
> id uniqueidentifier 'Id',
> content nvarchar(400) 'Content'
> ) new
> WHERE Article.Id = new.Id
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Diego Mijelshon" <no@.thanks.com> wrote in message
> news:ei8UgeEGFHA.1188@.tk2msftngp13.phx.gbl...
the
>|||"Diego Mijelshon" <no@.thanks.com> wrote in message
news:um$u8aIGFHA.548@.TK2MSFTNGP14.phx.gbl...
> DROP TABLE ##tmp
> ---
> As you can see, inserting in a temporal table does the trick.
I'm glad you found something that works. FYI, you should probably use a
local temporary table (single #) instead of a global one (##) unless you
need access to this same temp table from other processes...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uWtvvjIGFHA.560@.TK2MSFTNGP15.phx.gbl...
> "Diego Mijelshon" <no@.thanks.com> wrote in message
> news:um$u8aIGFHA.548@.TK2MSFTNGP14.phx.gbl...
> I'm glad you found something that works. FYI, you should probably use a
> local temporary table (single #) instead of a global one (##) unless you
> need access to this same temp table from other processes...
Thanks for the correction, I had it backwards :-)
Diego
Subscribe to:
Posts (Atom)