Querstion: update on joined field

Is it possible to update the filed used in the inner join
Update t1 set t1.name=t2.name2
From t1 inner join t2 on t1.name = t2.nameYes you can update the table. The inner join serves to limit the rows to be
updated. Much easier than writing a WHERE clause for the t1.name clause.
Nathan H. Omukwenyi
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:17C998CC-B9C4-42D0-9650-C7331D3A7613@.microsoft.com...
> Is it possible to update the filed used in the inner join
> Update t1 set t1.name=t2.name2
> From t1 inner join t2 on t1.name = t2.name
>|||On Tue, 6 Jun 2006 19:23:01 -0700, JIM.H. wrote:

>Is it possible to update the filed used in the inner join
>Update t1 set t1.name=t2.name2
>From t1 inner join t2 on t1.name = t2.name
>
Hi Jim,
Yes, this is possible, BUT:
1. The UPDATE ... FROM syntax is not standard and therefor not portable.
The syntax can allso become quite confusing.
2. If a row in the table to be updated is matched by more than one row
in the source table(s), the row will actually be updated several times
and only the "last" result sticks. Since the order of evaluation is
never guaranteed, the result will be undefined. Running such a query
won't cause an error or even a warning message!
Hugo Kornelis, SQL Server MVP

Querstion: update on joined field

Is it possible to update the filed used in the inner join
Update t1 set t1.name=t2.name2
From t1 inner join t2 on t1.name = t2.nameYes you can update the table. The inner join serves to limit the rows to be
updated. Much easier than writing a WHERE clause for the t1.name clause.
Nathan H. Omukwenyi
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:17C998CC-B9C4-42D0-9650-C7331D3A7613@.microsoft.com...
> Is it possible to update the filed used in the inner join
> Update t1 set t1.name=t2.name2
> From t1 inner join t2 on t1.name = t2.name
>|||On Tue, 6 Jun 2006 19:23:01 -0700, JIM.H. wrote:
>Is it possible to update the filed used in the inner join
>Update t1 set t1.name=t2.name2
>From t1 inner join t2 on t1.name = t2.name
>
Hi Jim,
Yes, this is possible, BUT:
1. The UPDATE ... FROM syntax is not standard and therefor not portable.
The syntax can allso become quite confusing.
2. If a row in the table to be updated is matched by more than one row
in the source table(s), the row will actually be updated several times
and only the "last" result sticks. Since the order of evaluation is
never guaranteed, the result will be undefined. Running such a query
won't cause an error or even a warning message!
--
Hugo Kornelis, SQL Server MVP

Queries going to Suspend state with wait type ASYNC_NETWORK_IO

Recently we observed a problem. We are running stored procedure through our c# code. Three machines access the server and update or insert in the required tables in the server. If there is no data in the server, on installing first time our application usually our database is clean. then stored procedure works fine, it takes around 10 to 15 sec to execute. Next time if execute the time goes up to minutes like 15 mins. Next time it goes for hours around 4 hrs. Even to update 4 or 5 records it takes time. Initially we thought it was because of the size of the data and we tried to re tune on indexes, it did not solve. But now what we observe is even with less number of records in server also it wouldn't come of the execution for hours.
Now are executing the just the SP in the SQL manager studio to see the time. That one also is executing for hours. when looked at the activity monitor the process goes to suspend state with wait type ASYNC_NETWORK_IO.

When we comment one of the query is working fine.
Is this something to do with the query I am not sure. If that is the case it should not work every time.

The query makes any sence or is there any way to write it in better way

'UPDATE [server].[dbo].[DocumentMetadata] SET DocumentInfoID = b.DocumentInfoID, [Name] = b.[Name], MetadataType = b.MetadataType,
[Value] = b.[Value], ValueType = b.ValueType
FROM [server].[dbo].[DocumentMetadata] a WITH (UPDLOCK)
INNER JOIN (SELECT c.DocumentInfoID, c.[Name], c.MetadataType, c.[Value], c.ValueType
FROM MACHINENAME.[Client].[dbo].[DocumentMetadata] c
INNER JOIN MACHINENAME.[Client].dbo.DocumentInfo DINF ON c.DocumentInfoID = DINF.DocumentInfoID
INNER JOIN MACHINENAME.[Client].dbo.Path d on DINF.NativeFileID = d.PathID
INNER JOIN MACHINENAME.[Client].dbo.ActiveDataSource ADS ON d.DataSourceID = ADS.DataSourceID
WHERE ADS.ProjectID = ''' + @.ProjID + ''') b
ON a.DocumentInfoID = b.DocumentInfoID AND a.[Name] = b.[Name]'

'INSERT INTO [server].[dbo].[DocumentMetadata]
(DocumentInfoID, [Name], MetadataType, [Value], ValueType)
SELECT c.DocumentInfoID, c.[Name], c.MetadataType, c.[Value], c.ValueType
FROM MACHINENAME.[Client].[dbo].[DocumentMetadata] c
INNER JOIN MACHINENAME.[Client].dbo.DocumentInfo DINF ON c.DocumentInfoID = DINF.DocumentInfoID
INNER JOIN MACHINENAME.[Client].dbo.Path d on DINF.NativeFileID = d.PathID
INNER JOIN MYCLI.[Client].dbo.ActiveDataSource ADS ON d.DataSourceID = ADS.DataSourceID
WHERE ADS.ProjectID = ''' + @.ProjID + '''
AND Ltrim(rtrim(c.DocumentInfoID))+ ltrim(rtrim(c.[Name])) NOT IN
(SELECT Ltrim(rtrim(DocumentInfoID))+ ltrim(rtrim([Name])) FROM [server].[dbo].[DocumentMetadata])'

We have been fighting it out for so many days.
Can anybody help

Thanks
knvdssr

ASYNC_NETWORK_IO means SQL server is waiting for network...

How may rows your select statement for INSERT it is returning?

You are using ltrim and rtrim functions against column which result a table scan and it can't use index...

Try to change the query or just comment the query and test it to make it is the issue.

|||It appears you are using a LINKED SERVER to access another server?

If this is the case, try looking at activity monitor on the source. I would suspect you are getting a deadlock or lock problem or something on the linked server.

The wait you are seeing indicates it is waiting on the linked server to finish the query and send records.

|||When you use 4-part name, all of the data from the remote server is pulled across before the join is formulated. If the remote table is large, you will have to wait for a long time for all the data to cross over. Try using openquery() to only pull/update the necessary remote data.

You can lookup some examples here:
http://groups.google.com/groups?q=sqlserver%20oj%20openquery%20update|||

We commented the query which I mentioned earlier, to see if that one is causing the problem. Then the whole Stored procedure finished executing in minutes.

We changed the the query with our using the NOT IN in that query.

Now first time it is taking only 10 minutes for more than 100,000 records.

But the second one is not coming out at all. This time the server is having lot of rows because is already updated once.

All that we see is the process in suspend state or comes out as timed out.

If I am getting a dead lock on the linked server is there any way to solve it?

Could any body help please

|||

Again, take a look at openquery/openrowset. Bringing the entire resultset back is not recommended when the remote table is large.

quastion about triggers

hi,

I have a few quastions about triggers:

1) I want that to run a specific function or exe, after a row update.

How can I run a function via the trigger?

2) Is ther any trigger that for an updae of a culomn (not a row)?

thanks,

moria

Hi Moria,
First of all, there are differences id you implement the trigger on a CLR basis (2005) or on a TSQL basis (<2005), so next time, try to give some information about your system, that the proposed solution will better fit your needs.
"How can I run a function via the trigger?"
You talked about executing a EXE, therefore you cann call the extended procedure SP_CMDSHELL which is able to execute a program via the command line (More information about that can be found in the BOL)
COMMENT: It′s odd to execute a command in a trigger. Trigger bahve synchronously, while executing the trigger other people using the data won′t be able to change the lock data (depends on your locking, worst case would be table locking). If there is an error in your command (or a long running command), like waiting for input or something, the trigger will never come back, so you have to
keep that in mind.

"Is ther any trigger that for an updae of a culomn (not a row)?"
Well, yes and no. A trigger is fired per statement (not per ROW) and regardless if your "watched" column changed or not, but if you are in the trigger you have the possibility to use the
IF UPDATE(SomeColumn)
DoSomething
But the trigger is fired regardless, if the column changed or not, keep that in mind, if you have more than this statement in your trigger.
HTH, Jens Suessmeyer.

|||

first of all, thank you for the detailed answer.

I am working with SQL 2000, and .NET 2005.

>>"More information about that can be found in the BOL"

what's BOL, and where can I find it?

>>we understand that an exe can block the SQL, but we want to know when a user updates a table so that we will run a certain program to take the new data, analyze it and reload it to another table. if not by an exe, how do you recommend to do it?

>>In case of deleted a column, is the trigger fired? which one?

thanks a lot,

moria.

|||

Hello again,

"what's BOL, and where can I find it?"
http://www.aspfaq.com/show.asp?id=2229

If this is an external program you sure have to use the exe, but what about wrting a entry in a table that something changed on the program running on an at basis or as a service checks the data, does its recalculation (or whatever) and marks the process as done in the table ? Thats are designing question which cannot be decided on the basis of the only few information given.

HTH, Jens Suessmeyer.

Qry: update person_course datetimestamp = 10/6/1900 12:00:01 AM fails!

I am trying to modify data in a SQL Server database using the query below. But it fails; why??

update person_course datetimestamp = '10/6/1900 12:00:01 AM' where personid > 470

I get
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'datetimestamp'.You need the key word SET

update person_course SET datetimestamp = '10/6/1900 12:00:01 AM' where personid > 470|||Thanks. I saw it after I had sent the message. Aaarrrgh!!! Long day I guess.

q; Transaction in trigger

Hello,
I have one insert and one update in my insert update trigger. Is there any
problem if I use Begin Transaction, Rolback transaction or Commit Transaction
if both insert and update do not succeed?
A Trigger is automatically enrolled in a TRANSACTION, and is committed upon
completion -so do NOT start or commit a TRANSACTION, only use ROLLBACK when
appropriate.
If you posted your proposed Trigger code, and a description of what you what
to happen or not happen, we may be able to better advise you.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:683679AA-4909-468A-9088-E2A0A7BF470E@.microsoft.com...
> Hello,
> I have one insert and one update in my insert update trigger. Is there any
> problem if I use Begin Transaction, Rolback transaction or Commit
> Transaction
> if both insert and update do not succeed?
>
|||You can use SAVE TRANSACTION to do a partial rollback in a trigger, assuming
the insert that fired the trigger is to be allowed, otherwise there's no
need to do any of this because a ROLLBACK in the trigger will roll back the
insert and update of the trigger and the initiating insert.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

q; Transaction in trigger

Hello,
I have one insert and one update in my insert update trigger. Is there any
problem if I use Begin Transaction, Rolback transaction or Commit Transactio
n
if both insert and update do not succeed?A Trigger is automatically enrolled in a TRANSACTION, and is committed upon
completion -so do NOT start or commit a TRANSACTION, only use ROLLBACK when
appropriate.
If you posted your proposed Trigger code, and a description of what you what
to happen or not happen, we may be able to better advise you.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:683679AA-4909-468A-9088-E2A0A7BF470E@.microsoft.com...
> Hello,
> I have one insert and one update in my insert update trigger. Is there any
> problem if I use Begin Transaction, Rolback transaction or Commit
> Transaction
> if both insert and update do not succeed?
>|||You can use SAVE TRANSACTION to do a partial rollback in a trigger, assuming
the insert that fired the trigger is to be allowed, otherwise there's no
need to do any of this because a ROLLBACK in the trigger will roll back the
insert and update of the trigger and the initiating insert.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

q; Transaction in trigger

Hello,
I have one insert and one update in my insert update trigger. Is there any
problem if I use Begin Transaction, Rolback transaction or Commit Transaction
if both insert and update do not succeed?A Trigger is automatically enrolled in a TRANSACTION, and is committed upon
completion -so do NOT start or commit a TRANSACTION, only use ROLLBACK when
appropriate.
If you posted your proposed Trigger code, and a description of what you what
to happen or not happen, we may be able to better advise you.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:683679AA-4909-468A-9088-E2A0A7BF470E@.microsoft.com...
> Hello,
> I have one insert and one update in my insert update trigger. Is there any
> problem if I use Begin Transaction, Rolback transaction or Commit
> Transaction
> if both insert and update do not succeed?
>|||You can use SAVE TRANSACTION to do a partial rollback in a trigger, assuming
the insert that fired the trigger is to be allowed, otherwise there's no
need to do any of this because a ROLLBACK in the trigger will roll back the
insert and update of the trigger and the initiating insert.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

q; sp_executesql and speed

Hello,
I am using sp_executesql this to pass parameter to sql string and I am
seeing deadlock between sp_prepexec which does UPDATE with another UPDATE
done by another process. When it comes to speed and deadlock, would you
recomand not using sp_executesql?Hi
I don't think you blocking is caused by sp_executesql. Look at what you are
executing and make sure that it is properly optimized and the transactions
are short.
John
"JIM.H." wrote:
> Hello,
> I am using sp_executesql this to pass parameter to sql string and I am
> seeing deadlock between sp_prepexec which does UPDATE with another UPDATE
> done by another process. When it comes to speed and deadlock, would you
> recomand not using sp_executesql?
>

q; sp_executesql and speed

Hello,
I am using sp_executesql this to pass parameter to sql string and I am
seeing deadlock between sp_prepexec which does UPDATE with another UPDATE
done by another process. When it comes to speed and deadlock, would you
recomand not using sp_executesql?Hi
I don't think you blocking is caused by sp_executesql. Look at what you are
executing and make sure that it is properly optimized and the transactions
are short.
John
"JIM.H." wrote:

> Hello,
> I am using sp_executesql this to pass parameter to sql string and I am
> seeing deadlock between sp_prepexec which does UPDATE with another UPDATE
> done by another process. When it comes to speed and deadlock, would you
> recomand not using sp_executesql?
>

q; INSRET/UPDATE trigger

Hi,
I user RAISERROR(@.Error, 16, 1) in the INSRET/UPDATE trigger, does this
rollback record in the table? It seems it does and I am wondering if there i
s
any workaround.
Thanks,Jim - can you post up the trigger code. I'm wondering if there is a try
catch block and a rollback statement in the catch section? In that case you
could use print/xp_logevent depending on your actual requirement.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Hi Paul,
Thanks you very much, no try check, here is the code:
-- duplicate record check
IF EXISTS (
SELECT *
FROM myTable i INNER JOIN con c ON i.ch = c.B_ch
WHERE i.tID=@.tID
)
Begin
SET @.ErrorD = 'Dup Record'
RAISERROR(@.ErrorD, 16, 1)
Return
End
"Paul Ibison" wrote:

> Jim - can you post up the trigger code. I'm wondering if there is a try
> catch block and a rollback statement in the catch section? In that case yo
u
> could use print/xp_logevent depending on your actual requirement.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>|||Jim,
the raiserror statement can't be doing the rollback with this severity. In
your trigger there is no reference to the INSERTED table - I'm wondering if
this is this really the trigger code? With the reference to @.tID it looks
more like stored procedure code. Also, please check to see if there are any
other triggers?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||On Fri, 29 Dec 2006 10:19:01 -0800, JIM.H. wrote:

>Hi,
>I user RAISERROR(@.Error, 16, 1) in the INSRET/UPDATE trigger, does this
>rollback record in the table?
Hi Jim,
It definitely does not. Run the following code in Sql Server Management
Studio or Query Analyzer to see for yourself:
CREATE TABLE Test
(a int NOT NULL PRIMARY KEY);
go
CREATE TRIGGER TestTrig
ON Test AFTER INSERT
AS
RAISERROR('This is a test', 16, 1);
go
INSERT INTO Test (a)
VALUES (1);
SELECT *
FROM Test;
go
DROP TABLE Test;
go

> It seems it does and I am wondering if there is
>any workaround.
Is it possible that your client automatically requests a rollback upon
seeing the error condition? I know some clients (Access, for instance)
that make a habit of rolling back first and asking questions later.
Hugo Kornelis, SQL Server MVP|||JIM.H. wrote:
> Hi Paul,
> Thanks you very much, no try check, here is the code:
> -- duplicate record check
> IF EXISTS (
> SELECT *
> FROM myTable i INNER JOIN con c ON i.ch = c.B_ch
> WHERE i.tID=@.tID
> )
> Begin
> SET @.ErrorD = 'Dup Record'
> RAISERROR(@.ErrorD, 16, 1)
> Return
> End
>
>
Unfortunately you only posted part of the trigger code but this looks
suspiciously like your code is at fault. I suspect your trigger fails
to operate correctly when multiple rows are updated because you don't
appear to be referencing the INSERTED table properly. That might
explain some unpredictable behaviour.
If you want help to rewrite the trigger we'll need a better spec - read
my signature.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

q; INSRET/UPDATE trigger

Hi,
I user RAISERROR(@.Error, 16, 1) in the INSRET/UPDATE trigger, does this
rollback record in the table? It seems it does and I am wondering if there is
any workaround.
Thanks,
Jim - can you post up the trigger code. I'm wondering if there is a try
catch block and a rollback statement in the catch section? In that case you
could use print/xp_logevent depending on your actual requirement.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hi Paul,
Thanks you very much, no try check, here is the code:
-- duplicate record check
IF EXISTS (
SELECT *
FROM myTable i INNER JOIN con c ON i.ch = c.B_ch
WHERE i.tID=@.tID
)
Begin
SET @.ErrorD = 'Dup Record'
RAISERROR(@.ErrorD, 16, 1)
Return
End
"Paul Ibison" wrote:

> Jim - can you post up the trigger code. I'm wondering if there is a try
> catch block and a rollback statement in the catch section? In that case you
> could use print/xp_logevent depending on your actual requirement.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||Jim,
the raiserror statement can't be doing the rollback with this severity. In
your trigger there is no reference to the INSERTED table - I'm wondering if
this is this really the trigger code? With the reference to @.tID it looks
more like stored procedure code. Also, please check to see if there are any
other triggers?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

q; how to improve this transaction

Hello,
I have four different transactions such as below and I do one insert and one
update in each transaction and it seem it is slow and creates deadlock with
the user interface.
These transactions are performed against the tables that users are accessing
with another user interface. I have following two questions:
1. T2.TextField1 and TextField2 = @.TextField2 are Ok, Nok fields so I did
not put index since only two distinct values. Should I put indexes on these
fields?
2. Can I make this transaction let user interface do its task in case
accessing the same rows, I can start transaction again but I do not want
users get disturbed?
.
BEGIN TRANSACTION pTrans
BEGIN
INSERT INTO T1
(fields)
SELECT (fields)
FROM T2 INNER JOIN View1 ON T2.TrID = View1.MyTableID
WHERE (T2.TextField1 = @.TrType AND T2.TextField2 = @.TextField2)
UPDATE T2
SET TextField2 = 'Ok', TextField2Date=@.MyRunDateTime
FROM T2
WHERE (TextField1 = @.TrType AND TextField2 = @.TextField2)
IF @.@.ERROR <> 0
BEGIN
rollback transaction pTrans
return(-1)
END
ELSE
BEGIN
commit transaction pTrans
END
ENDadd a composite, non-clustered index on textfield1,textfield2.
otherwise, you will have to do a table scan for each update.
>|||On Wed, 12 Jul 2006 16:19:01 -0700, JIM.H.
<JIMH@.discussions.microsoft.com> wrote:
>I have four different transactions such as below and I do one insert and one
>update in each transaction and it seem it is slow and creates deadlock with
>the user interface.
>These transactions are performed against the tables that users are accessing
>with another user interface. I have following two questions:
>1. T2.TextField1 and TextField2 = @.TextField2 are Ok, Nok fields so I did
>not put index since only two distinct values. Should I put indexes on these
>fields?
>2. Can I make this transaction let user interface do its task in case
>accessing the same rows, I can start transaction again but I do not want
>users get disturbed?
How long does the transaction take if all alone on the machine?
How much IO does it do (from profiler or "set statistic io on" in
query analyzer)?
Are you familiar with the nolock hint?
Do you have access to the code for the other program accessing the
database? That's the one that might need the nolock.
You might want to do the selects for one or both statements (the
update does an implicit select) and put the results into temp files or
@.tables, then insert and update from them, to minimize locking times.
Depends on times and data volumes and PKs.
Josh

q:INSRET/UPDATE trigger

Hi,

I user RAISERROR(@.Error, 16, 1) in the INSRET/UPDATE trigger, does this rollback record in the table? It seems it does and I am wondering if there is any workaround.

Thanks,

No, that shouldn't rollback INSERT/UPDATE to the table, unless you define the trigger as "INSTEAD OF" trigger. You can take a look atCREATE TRIGGER

Q: Trigger - reference to fields in "inserted" ?

Hi all,

I have a ranking system where I wish to update the ranking every time a result is reported. Performance is no issue what-so-ever. More specifically, two players are to devide their points after each series of games to reflect the fraction of over-all games that each player have won.

I've written the trigger below, but Visual Web Developer 2005 Express (SQL Server 2005 Express) complains about the references to the 'inserted'-table.

I find it very difficult to transform the code below to something that looks like the examples found in documentation.

Could someone get me started in the right direction?

Thanks in advance,

Anders

create trigger result_insertonresult

after insert as

begin

declare@.won1as int

declare@.won2as int

declare@.oldRank1as float

declare@.oldRank2as float

declare@.oldranksumas float

select@.won1 =sum(wongames1)fromresultwhereplayer1 = inserted.player1andplayer2=inserted.player2

select@.won2 =sum(wongames2)fromresultwhereplayer1 = inserted.player1andplayer2=inserted.player2

select@.oldrank1 = RankfromRankingInfowherememberid = inserted.playerid1

select@.oldrank2 = RankfromRankingInfowherememberid = inserted.playerid2

set@.oldranksum = @.oldrank1 + @.oldrank2

updaterankingInfosetRank = @.won1 / ( @.won1+@.won2) * @.oldranksumwherememberid = inserted.player1

updaterankingInfosetRank = @.won2 / ( @.won1+@.won2) * @.oldranksumwherememberid = inserted.player2

end

Hello Anders,

tha fact is theinserted table is but a table, so you need JOIN-ing to it as you would for any standard table...

Hope this helps. -LV