Problem while updating data using SQLXML UpdateGrams

A long time I used SQLXML templates on serverside to get data for my application and update some records one by one.

But now I also need to update data of the whole dataset. So I want to use SQLXML UpdateGrams. (Or DataSet DiffGrams).

But My first attempt failed. I have troulble with identity field. No one solution from forums or articles worked for me . So I tried to implement some sample to post here. It uses Northwind database.

Sample should do 3 steps.

Load data - OK

Make some changes - OK

Save Changes.

But doing this sample I have event more troubles.

please help me. provide some sample or better show me fixes in my one. I tried to comment it and think it is rather simple. you can get it here http://rapidshare.de/files/26676262/SQLXML_Sample1.zip.html or here ftp://demon.selfip.com/SQLXML_Sample1.zip.

It is very urgent, so I'll wait for any replies.

Thanks.

-= Sergey =-

PS.: I'm using SQL Server 2005 to run samples. But productive server will be SQL Server 2000.

Thread is closed

I've found an error. I've copied sample xsd from the bad book

There was an error in schema uri

xmlns:sql="urn:schemas-microsoft-com:mapping-schema"

so SQLXML saw no mapping at all!

no everything is ok. Even identity inserts work properly.

-= Sergey =-

problem when i try to modifie a field of a table in transactional

Hy,
i have the following problem:
i try to modifie an article (table) in a context of transactional
replication with queue updating in two way:
1- first way with sp_repladdcolumn and sp_repldropcolumn:
add a temporary field to the article:
exec sp_repladdcolumn @.source_object='Alc_AllarmiCritici'
,@.column='TempTipoEvento'
,@.typetext='nvarchar(100) NULL'
,@.publication_to_add='Chironpubb',
@.force_reinit_subscription =1
copy the data of the original column in the temporary fields
update Alc_AllarmiCritici set TempTipoEvento=TipoEvento
drop th original column
exec sp_repldropcolumn
@.source_object='alc_allarmicritici',@.column='TipoE vento'
add a new empty column with the name and data type of the temporary
exec sp_repladdcolumn @.source_object='Alc_AllarmiCritici'
,@.column='TipoEvento'
,@.typetext='nvarchar(100) NULL'
,@.publication_to_add='Chironpubb'
copy the date from temporary colum in the new empty column
update Alc_AllarmiCritici set TipoEvento=TempTipoEvento
drop the temporary table
exec sp_repldropcolumn @.source_object='alc_allarmicritici'
,@.column='TempTipoEvento'
then, i start the snapshot agent
the update of the structure is delivered th the subscriver susccessfully
the problem was that the field now is in the last positon in the table and
not in the same position before the updating. Therefore applications doesnt
function becouse the position of the fields have changed.
Does anybody know how i can resove the trouble?
2 - I exclude from replication the article with sp_dropsubscription and
sp_droparticle , i modified the article and then i put the article in the
replication with sp_addsubscription and sp_addarticle.
In this case sincronization from publisher to subscriver is ok.
Sincronization from subscriver to publisher doesn't function. Should i
create triggers for queue updating?
help me please, thankyou
Luca Schiavon
MCSD
(developer)
Luca,
mostly the advice is to make your application not dependant on column order
and to use column names instead. If this is not possible, then you can
change the column order on the publisher and reinitialize the table (drop
the article and then readd).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

problem updating view with instead of trigger

Hi all,
I have created wv_details view that has an instead of update trigger. My
question is how can I update this view?
When I run the following query I get an error - "View 'wv_details' has
an INSTEAD OF UPDATE trigger and cannot be a target of an UPDATE FROM
statement."
UPDATE wv_details
SET REFERENCE=latest.REFERENCE,
[NOTES]=latest.NOTES,
[TITLE]=latest.TITLE,
[LINKACCT]=latest.LINKACCT,
[COUNTRY]=latest.COUNTRY,
[ZIP]=latest.ZIP,
[EXT]=latest.EXT,
[STATE]=latest.STATE,
[ADDRESS1]=latest.ADDRESS1,
[ADDRESS2]=latest.ADDRESS2,
[MERGECODES]=latest.MERGECODES,
[STATUS]=latest.STATUS,
[LASTUSER]=latest.LASTUSER
FROM wv_details latest
JOIN wv_details
ON latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
So I changed this query as shown below. It does not use a FROM clause
anymore. But I get a different error with this query - "The text, ntext, and
image data types are invalid in this subquery or aggregate expression.". The
NOTES field is a text column.
UPDATE wv_details
SET REFERENCE=(SELECT TOP 1 latest.REFERENCE
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[NOTES]=(SELECT TOP 1 latest.NOTES
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[TITLE]=(SELECT TOP 1 latest.TITLE
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[LINKACCT]=(SELECT TOP 1 latest.LINKACCT
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[COUNTRY]=(SELECT TOP 1 latest.COUNTRY
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[ZIP]=(SELECT TOP 1 latest.ZIP
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[EXT]=(SELECT TOP 1 latest.EXT
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[STATE]=(SELECT TOP 1 latest.STATE
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[ADDRESS1]=(SELECT TOP 1 latest.ADDRESS1
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[ADDRESS2]=(SELECT TOP 1 latest.ADDRESS2
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[MERGECODES]=(SELECT TOP 1 latest.MERGECODES
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[STATUS]=(SELECT TOP 1 latest.STATUS
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[LASTUSER]=(SELECT TOP 1 latest.LASTUSER
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
)
So my question is how can I update this view?
Thanks...
-NikhilThere are quite a few issues:
1. You reference the view as if it's either an "inserted" or "deleted"
virtual table. It's not so.
2. You have an Instead Of trigger on the view, you should be updating the
base table within that trigger. You have full access the virtual tables
there.
3. You cannot do (update obj set col =(select top 1 lob_col from ...)).
You're are doing aggregation on the blob which is not allowed (by MS
design).
4. Even if (col=select top 1 lob) is allowed, this update is going to cost
you royally.
You have been posting for a while here. You know it would be easier if you
post ddl+sample data/code+expected output, it would be easier to help you.
http://groups.google.co.uk/groups?h...er+Nikhil+Patel
http://www.aspfaq.com/etiquette.asp?id=5006
-oj
"Nikhil Patel" <donotspam@.nospaml.com> wrote in message
news:eI6SpeYBFHA.2676@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> I have created wv_details view that has an instead of update trigger.
> My question is how can I update this view?
> When I run the following query I get an error - "View 'wv_details' has
> an INSTEAD OF UPDATE trigger and cannot be a target of an UPDATE FROM
> statement."
> UPDATE wv_details
> SET REFERENCE=latest.REFERENCE,
> [NOTES]=latest.NOTES,
> [TITLE]=latest.TITLE,
> [LINKACCT]=latest.LINKACCT,
> [COUNTRY]=latest.COUNTRY,
> [ZIP]=latest.ZIP,
> [EXT]=latest.EXT,
> [STATE]=latest.STATE,
> [ADDRESS1]=latest.ADDRESS1,
> [ADDRESS2]=latest.ADDRESS2,
> [MERGECODES]=latest.MERGECODES,
> [STATUS]=latest.STATUS,
> [LASTUSER]=latest.LASTUSER
> FROM wv_details latest
> JOIN wv_details
> ON latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> So I changed this query as shown below. It does not use a FROM clause
> anymore. But I get a different error with this query - "The text, ntext,
> and image data types are invalid in this subquery or aggregate
> expression.". The NOTES field is a text column.
> UPDATE wv_details
> SET REFERENCE=(SELECT TOP 1 latest.REFERENCE
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [NOTES]=(SELECT TOP 1 latest.NOTES
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [TITLE]=(SELECT TOP 1 latest.TITLE
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [LINKACCT]=(SELECT TOP 1 latest.LINKACCT
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [COUNTRY]=(SELECT TOP 1 latest.COUNTRY
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [ZIP]=(SELECT TOP 1 latest.ZIP
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [EXT]=(SELECT TOP 1 latest.EXT
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [STATE]=(SELECT TOP 1 latest.STATE
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [ADDRESS1]=(SELECT TOP 1 latest.ADDRESS1
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [ADDRESS2]=(SELECT TOP 1 latest.ADDRESS2
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [MERGECODES]=(SELECT TOP 1 latest.MERGECODES
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [STATUS]=(SELECT TOP 1 latest.STATUS
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [LASTUSER]=(SELECT TOP 1 latest.LASTUSER
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> )
>
> So my question is how can I update this view?
> Thanks...
> -Nikhil
>

Problem updating two tables in a transaction.

Background...
In Sql Server 2000 I have two tables T1 & T2, where T2 contains a key to T1.
I have one process P1 that deletes all the records in T2 and then T1, and
then inserts new records into T1 and then T2, all done in a transaction
(default isolation level). When complete, the P1 notifies another process P2
that it has finished, and P2 immediately loads all the data from T1 and then
T2.
Problem...
P2 occasionally fails when loading T2 because it can't find a referenced row
in the data it loaded from T1. However, on a second attempt to load both
tables it succeeds. Subsequent queries on the tables show problems.
Questions...
Is this likely to be a concurrency issue? If so, why is this happening? How
can I fix it?
One thing that might be relevant is that a foreign key constraint between T2
and T1 is not defined in the database. I have limited control over this.Rick (Rick@.nowhere.com) writes:
> In Sql Server 2000 I have two tables T1 & T2, where T2 contains a key to
> T1. I have one process P1 that deletes all the records in T2 and then
> T1, and then inserts new records into T1 and then T2, all done in a
> transaction (default isolation level). When complete, the P1 notifies
> another process P2 that it has finished, and P2 immediately loads all
> the data from T1 and then T2.
> Problem...
> P2 occasionally fails when loading T2 because it can't find a referenced
> row in the data it loaded from T1. However, on a second attempt to load
> both tables it succeeds. Subsequent queries on the tables show
> problems.
> Questions...
> Is this likely to be a concurrency issue? If so, why is this happening?
> How can I fix it?
> One thing that might be relevant is that a foreign key constraint
> between T2 and T1 is not defined in the database. I have limited control
> over this.
I'm afraid that this introduction is not enough to say anything for sure.
Seeing the code would have helped?
Of course, the missing FK constraint is not good, but as long as you know
that the data you insert is consistent, it is not a problem. And, anyway,
if you were to INSERT data into T2 where the key to T1 is missing, it
should keep on failing.
How does P2 access the tables? Does it use NOLOCK or READ UNCOMMITTED?
Could you have P1 running again while P2 is running?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97665332A148Yazorman@.127.0.0.1...
> Rick (Rick@.nowhere.com) writes:
> I'm afraid that this introduction is not enough to say anything for sure.
> Seeing the code would have helped?
> Of course, the missing FK constraint is not good, but as long as you know
> that the data you insert is consistent, it is not a problem. And, anyway,
> if you were to INSERT data into T2 where the key to T1 is missing, it
> should keep on failing.
> How does P2 access the tables? Does it use NOLOCK or READ UNCOMMITTED?
> Could you have P1 running again while P2 is running?
I'm using ado.net, so the code would probably be irrelevant, even if I could
extract it from the many layers.
I was not declaring an explicit locking strategy, but instead depending on
the default (read committed?). I can only assume that when P2 read T1, it
got none or only some of the new data. I'm not sure how this could happen.
The strange thing is that when I changed the isolation level of the P1
transaction to serializable, the problem did not recur. This is troubling.
:-/|||Rick (rick@.nospam.com) writes:
> I'm using ado.net, so the code would probably be irrelevant, even if I
> could extract it from the many layers.
It may be that your code is too complex to be easily understood in a
newsgroup post, but you are wrong to assume that it is irrelevant. Without
code, I can at best guess what you are doing.
Since it helped to set the transaction isolation level to serializable for
the update process, there is obviously something you did not tell us.
My guess is that once instance of P1 runs, alerts P2. P2 starts reading T1.
At the same time a second instance of P2 starts running and deletes the
row in T2.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9767CF95BB51EYazorman@.127.0.0.1...
> Rick (rick@.nospam.com) writes:
> It may be that your code is too complex to be easily understood in a
> newsgroup post, but you are wrong to assume that it is irrelevant. Without
> code, I can at best guess what you are doing.
> Since it helped to set the transaction isolation level to serializable for
> the update process, there is obviously something you did not tell us.
> My guess is that once instance of P1 runs, alerts P2. P2 starts reading
> T1.
> At the same time a second instance of P2 starts running and deletes the
> row in T2.
Thanks Eric. It's important to know that this should not be happening IF the
sequence of events are as I described. I can only then assume, like you,
that they are not. This gives me something to work with.|||"Rick" <Rick@.nowhere.com> wrote in message
news:uHsgDcLMGHA.1124@.TK2MSFTNGP15.phx.gbl...
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns9767CF95BB51EYazorman@.127.0.0.1...
> Thanks Eric. It's important to know that this should not be happening IF
> the sequence of events are as I described. I can only then assume, like
> you, that they are not. This gives me something to work with.
Sorry... ERLAND!!|||Why don't you have a ON DELETE CASCADE constraint? Never depend on app
code to maintain your data integrity. This is one of many reason that
a row in a RDBMS is nothing like a record.
The way you chain code together implies a procedural design and
mindset.

problem updating text datatype

Hi All:
I am having trouble updating a TEXT column (I know that TEXT is a blob but I
am dealing with very large amounts of data that VARCHAR can not hold!) What
I want to happen is all rows from TableA that meet a certain criteria to be
inserted into TableB but if it already exists in TableB then update it
instead - this sounds simple enough and is working fine except that one of
the fields is TEXT datatype. So when I try to update I get an error "The
text, ntext, and image datatypes are invalid in this subquery or aggregate
expression." (Apparently you can not do an update select with a text
datatype) I did a search and found that I need to be using the UPDATETEXT
function - however I am not quite sure how this works. Any suggestions
would be greatly appreciated!
TableA
ID INT
MenuID INT
Content TEXT
TableB
ID INT
MenuID INT
Content TEXT
Thanks!Nancy,
Without seeing the query, it's impossible to know what the
problem is. Post the query that is causing the error, at
least, if you want a more specific answer.
In any case, it is certainly possible to update [text]
with an UPDATE statement. In general, you may want
something like this:
update TableB set
Content = TableA.Content
from TableA join TableB
on TableA.ID = TableB.ID
and TableA.MenuID = TableB.MenuID
insert into TableB(ID, MenuID, Content)
select ID, MenuID, Content
from TableA
where not exists (
select * from TableB as B2
where B2.ID = TableA.ID
and B2.MenuID = TableA.MenuID
)
Steve Kass
Drew University
Nancy Shelley wrote:

> Hi All:
> I am having trouble updating a TEXT column (I know that TEXT is a blob but
I
> am dealing with very large amounts of data that VARCHAR can not hold!) Wha
t
> I want to happen is all rows from TableA that meet a certain criteria to
be
> inserted into TableB but if it already exists in TableB then update it
> instead - this sounds simple enough and is working fine except that one of
> the fields is TEXT datatype. So when I try to update I get an error "The
> text, ntext, and image datatypes are invalid in this subquery or aggregate
> expression." (Apparently you can not do an update select with a text
> datatype) I did a search and found that I need to be using the UPDATETEXT
> function - however I am not quite sure how this works. Any suggestions
> would be greatly appreciated!
>
> TableA
> ID INT
> MenuID INT
> Content TEXT
> TableB
> ID INT
> MenuID INT
> Content TEXT
> Thanks!
>|||Hi Steve:
Thanks for the quick response. Supplying BLOB columns with text or image
data that's less than or equal to 8000 bytes in size is as straightforward
as updating any other type of column (you can use insert/update) but when
values are larger than 8000 you need to use updatetext or writetext. This
is what I was having trouble with - I know the syntax for updating my table
the regular way I just wasn't sure about using updatetext or writetext.
I ended up solving my own problem - my pointer was invalid. I was getting
an invalid pointer reference to the text column.
Thanks for your help!
Nancy
"Steve Kass" <skass@.drew.edu> wrote in message
news:OqsmT2jWFHA.3488@.tk2msftngp13.phx.gbl...
> Nancy,
> Without seeing the query, it's impossible to know what the
> problem is. Post the query that is causing the error, at
> least, if you want a more specific answer.
> In any case, it is certainly possible to update [text]
> with an UPDATE statement. In general, you may want
> something like this:
>
> update TableB set
> Content = TableA.Content
> from TableA join TableB
> on TableA.ID = TableB.ID
> and TableA.MenuID = TableB.MenuID
> insert into TableB(ID, MenuID, Content)
> select ID, MenuID, Content
> from TableA
> where not exists (
> select * from TableB as B2
> where B2.ID = TableA.ID
> and B2.MenuID = TableA.MenuID
> )
> Steve Kass
> Drew University
> Nancy Shelley wrote:
>|||Nancy,
I believe the syntax I suggested works for any length [text] or
[image] column.
You can also update or insert data into a text or image column if you
provide
it as a literal string or binary value. The 8000 byte limit does not
apply to
literals.
SK
Nancy Shelley wrote:

>Hi Steve:
>Thanks for the quick response. Supplying BLOB columns with text or image
>data that's less than or equal to 8000 bytes in size is as straightforward
>as updating any other type of column (you can use insert/update) but when
>values are larger than 8000 you need to use updatetext or writetext. This
>is what I was having trouble with - I know the syntax for updating my tabl
e
>the regular way I just wasn't sure about using updatetext or writetext.
>I ended up solving my own problem - my pointer was invalid. I was getting
>an invalid pointer reference to the text column.
>Thanks for your help!
>Nancy
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:OqsmT2jWFHA.3488@.tk2msftngp13.phx.gbl...
>
>
>

Problem updating table1 with data from table 2

Hi,

I'm trying to loop through 2 tables and match the records from table1 to table2 and update a couple of columns in table2 if there is a match. The below code updates everyone in table2 with the same data from only 1 record. How can I keep looping through both tables?

USE GradData

GO

Declare @.ssn nvarchar(10)

Declare @.testdesc nvarchar(10)

Declare @.testcode nvarchar(10)

Declare @.testdate datetime

Declare @.testscore nvarchar(50)

Declare @.testsource nvarchar(10)

Declare @.testsortkey nvarchar(10)

Declare @.fcode nvarchar(10)

Declare @.ssn2 nvarchar(10)

Declare @.testdesc2 nvarchar(10)

Declare @.testcode2 nvarchar(10)

Declare @.testdate2 datetime

Declare @.testscore2 nvarchar(50)

Declare @.testsource2 nvarchar(10)

Declare @.testsortkey2 nvarchar(10)

Declare @.fcode2 nvarchar(10)

Declare mycursor Cursor For

Select ssn,testDesc1,testCode1,testDate1,testScore1,testSource1,Fcode1,sortkey

From TestScoresRpt

Open mycursor

FETCH NEXT FROM mycursor

INTO @.ssn,@.testDesc,@.testCode,@.testDate,@.testScore,@.testSource,@.Fcode2,@.testsortkey

Declare mycursor2 Cursor For

Select ssn,test_Desc,test_Code,test_Date,test_Score,test_Source_code,Fcode,test_sort_key

From ScoresAll

Open mycursor2

FETCH NEXT FROM mycursor2

INTO @.ssn2,@.testDesc2,@.testCode2,@.testDate2,@.testScore2,@.testSource2,@.Fcode2,@.testsortkey2

WHILE @.@.FETCH_STATUS = 0

BEGIN

IF @.ssn <> @.ssn2

BEGIN

FETCH NEXT FROM mycursor2

INTO @.ssn2,@.testDesc2,@.testCode2,@.testDate2,@.testScore2,@.testSource2,@.Fcode2,@.testsortkey2

END

IF @.ssn = @.ssn2 and @.testsortkey = @.testsortkey2

BEGIN

FETCH NEXT FROM mycursor2

INTO @.ssn2,@.testDesc2,@.testCode2,@.testDate2,@.testScore2,@.testSource2,@.Fcode2,@.testsortkey2

END

IF @.ssn = @.ssn2 and @.testsortkey <> @.testsortkey2

Update TestScoresRpt

set ssn2=@.ssn2,testDesc2=@.testDesc2,testCode2=@.testCode2,testDate2=@.testDate2,testScore2=@.testScore2,testSource2=@.testSource2,Fcode2=@.fcode2,sortkey2=@.testsortkey2

FETCH NEXT FROM mycursor

INTO @.ssn,@.testDesc,@.testCode,@.testDate,@.testScore,@.testSource,@.Fcode2,@.testsortkey

END

CLOSE mycursor

CLOSE mycursor2

DEALLOCATE mycursor

DEALLOCATE mycursor2

If you are updating all rows where (@.ssn = @.ssn2 and @.testsortkey <> @.testsortkey2), then you would have improved performance using a Set based operation.

Please verify that the end result is two (2) virtually duplicate rows.

|||

THE END RESULT WILL BE A SSN WITH 2 DIFFERENT SORT KEYS FOR EACH SSN IN THE TABLE. WHAT DO YOU MEAN BY SET BASED OPERATION?

THANKS,

|||

My mistake for not completely reading your code.

A Set based operation would be making these updates in one query.

Try something like:

UPDATE t
SET
t.ssn2 = s.ssn,
t.testDesc2=s.testDesc2,
t.testCode2=s.testCode2,
t.testDate2=s.testDate2,
t.testScore2=s.testScore2,
t.testSource2=s.testSource2,
t.Fcode2=s.fcode2,
t.sortkey2=s.testsortkey2
FROM TestScoresRpt t
JOIN ScoresAll s
ON t.ssn = s.ssn
WHERE t.testsortkey <> s.testsortkey2

(This is untested, so please test, test.)

Problem updating SQL2005 From MSAccess Form

I have recently migrated from SQL2K to SQL2K5 and have a number of front ends in MSAccess Forms that worked fine with SQL2K but are not working with SQL2K5. I am able to connect and view the data in the SQL table using a file DSN but I am unable to update the records from the MSAccess form in SQL2K5. I have checked all of the permissions and have even given the Login dbo privs. I have also allowed remote access through the Surface Area Configuration Tool. What am I missing?

Thanks in advance for any assistance you can provide.

What are you using as the front end?, Access Project or Access mdb? If it's a Project I don't think they can update in SS2005

I've just completed a big Access/SQL Server 2005 project, leaving the front end as an mdb. There are a few quirks, you can't use Views only SP's and pass through queries, but it still works well.

|||I am using Access mdb. Sql tables are Linked tables in the Access mdb. Thanks for the advice. I'll ensure that there are no views involved.|||You should run one of your queries from the query design grid, try to make a change to the returned result set, if you can make an update , you know it's a forms based problem not permissions.|||Same result using the Design Grid. I am able to display the contents of the table but unable to update or append records from the grid. WHen I try a simple update query I get the error "Operation must use an updatable query".|||Problem solved. For some reason when I originally created the Linked Table I was not prompted to identify a unique key in the table for updating. Recreating the Linked Table with the unique fields identified solved the problem.