SQL operation in Server Explorer differ from Enterprise Manager

[AH]

Dear all, I am facing this 'bug' that really drive me nut. I created a trigger for Update script and tested in Enterprise manager to ensure it function correctly. However, when I used both VB or Server explorer to update the table, it gives me error, Couldn't VB just act as a intermidary to make the call and SQL server is supposed to do all the jobs??
To explain it in a simple way, I have 3 TABLES, TABLE1 with column C1(key), C2(char) & C3 (bit). TABLE2 with C1(key) & C2(key) and TABLE3 with C1(key) and C2(key). I wrote a update trigger to fire if C3 changes from 0 to 1. It inserted records into TABLE3 from TABLE2 where TABLE1.C1 = TABLE2.C1. Below are tables with sample data.

TABLE1 C1 C2 C3
A 123 0
B 234 0
C 456 0
TABLE2 C1 C2
A XXX
B XXX
B YYY
B ZZZ
C JJJ

TABLE3 C1 C2

If I change C3 to 1 in the 1st record in TABLE1, it work fine and 1st record in TABLE2 was inserted to TABLE3 in both Enterprise Manager & VS. However, if I change C3 to 1 in the 2nd record, Enterprise Manager will correctly inserted 3 reocrds into TABLE3 but in VS, it flags error 'Key column information is insufficient or incorrect. Too many rows were affected by update'

Could anyone please advise how to stop VS from 'try to be clever' ??

my trigger script as below:

CREATE TRIGGER Table1_Trigger1
ON dbo.Table1
FOR UPDATE
AS
IF UPDATE (c1)
declare @C3 as bit
declare @Key as char(10)
set @c3 = (select c3 from inserted)
set @key = (select c1 from INSERTED)

if @c3 = 1
INSERT INTO Table3
(C1, C2)
SELECT C1, c2
FROM Table2
WHERE C1= @Key)

Thank you

[Cor]

Hi AH,

Did you ask this question also in the newsgroup.
You are doing this with VS.net maybe positioning on the VB interface, but I see only SQL code in your message and no VB.

Adonet
<news://msnews.microsof t.com/microsoft.publi c.dotnet.framew ork.adonet>

Web interface:

<http://communities2.mi crosoft.com/communities/newsgroups/en-us/?dg=microsoft.p ublic.dotnet.fr amework.adonet>
I hope this helps a little bit?

Cor

[Cameron Slade [MSFT]]

Hi Cor,

The behavior you are seeing is more of difference between OLE DB andODBC. Enterprise Manager uses ODBC and VS uses OLEDB by default. If you connect to SQL Server via the OLEDB
Provider for ODBC you will see the same behavior as in Enterprise Manager.

In the scenario where multiple rows get updated by the trigger ODBC returns 1 record as being affected and OLE DB returns 3 records being affected. The problem is the VS tools think you have
only updated one row but OLE DB reports 3 rows have been changed. To be safe we consider this situation an error. There are a couple work arounds. On is to do the update statements your self
in VS. Just open SQL Pane on the VS Query Designer, right click, and select change type update. The other possibility is to add SET NOCOUNT ON at the beginning of your trigger.

Hope this helps,

Cameron Slade
Microsoft VSData Team
--

This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm

Note: For the benefit of the community-at-large, all responses to this message are best directed to the newsgroup/thread from which they originated.
--------------------

From: "AH" <ha*****@hotmai l.com>
Subject: SQL operation in Server Explorer differ from Enterprise Manager
Date: Wed, 25 Feb 2004 12:17:31 +0800
Lines: 174
MIME-Version: 1.0
Content-Type: multipart/alternative;
boundary="----=_NextPart_000_ 0008_01C3FB99.5 78BBE90"
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Message-ID: <#j************ **@TK2MSFTNGP09 .phx.gbl>
Newsgroups: microsoft.publi c.dotnet.langua ges.vb
NNTP-Posting-Host: cm95.omega224.m axonline.com.sg 218.186.224.95
Path: cpmsftngxa06.ph x.gbl!TK2MSFTNG P08.phx.gbl!TK2 MSFTNGP09.phx.g bl
Xref: cpmsftngxa06.ph x.gbl microsoft.publi c.dotnet.langua ges.vb:184516
X-Tomcat-NG: microsoft.publi c.dotnet.langua ges.vb

Dear all, I am facing this 'bug' that really drive me nut. I created a trigger for Update script and tested in Enterprise manager to ensure it function correctly. However, when I used both VB or Server explorer to update the table, it gives me error, Couldn't VB just act as a intermidary to make the call and SQL server is supposed to do all the jobs??To explain it in a simple way, I have 3 TABLES, TABLE1 with column C1(key), C2(char) & C3 (bit). TABLE2 with C1(key) & C2(key) and TABLE3 with C1(key) and C2(key). I wrote a update trigger to fire if C3 changes from 0 to 1. It inserted records into TABLE3 from TABLE2 where TABLE1.C1 = TABLE2.C1. Below are tables with sample data.TABLE1 C1 C2 C3
A 123 0
B 234 0
C 456 0
TABLE2 C1 C2
A XXX
B XXX
B YYY
B ZZZ
C JJJ
TABLE3 C1 C2
If I change C3 to 1 in the 1st record in TABLE1, it work fine and 1st record in TABLE2 was inserted to TABLE3 in both Enterprise Manager & VS. However, if I change C3 to 1 in the 2nd record, Enterprise Manager will correctly inserted 3 reocrds into TABLE3 but in VS, it flags error 'Key column information is insufficient or incorrect. Too many rows were affected by update'Could anyone please advise how to stop VS from 'try to be clever' ??
my trigger script as below:
CREATE TRIGGER Table1_Trigger1
ON dbo.Table1
FOR UPDATE
AS
IF UPDATE (c1)
declare @C3 as bit
declare @Key as char(10)
set @c3 = (select c3 from inserted)
set @key = (select c1 from INSERTED)
if @c3 = 1
INSERT INTO Table3
(C1, C2)
SELECT C1, c2
FROM Table2
WHERE C1= @Key)
Thank you