By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,559 Members | 1,154 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,559 IT Pros & Developers. It's quick & easy.

SQL operation in Server Explorer differ from Enterprise Manager

P: n/a
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

Nov 20 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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.microsoft.com/microsoft.public.dotnet.framework.adonet>

Web interface:

<http://communities2.microsoft.com/communities/newsgroups/en-us/?dg=microsoft.public.dotnet.framework.adonet>
I hope this helps a little bit?

Cor

Nov 20 '05 #2

P: n/a
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*****@hotmail.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.578BBE90"
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.public.dotnet.languages.vb
NNTP-Posting-Host: cm95.omega224.maxonline.com.sg 218.186.224.95
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTN GP09.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.languages.vb:184516
X-Tomcat-NG: microsoft.public.dotnet.languages.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


Nov 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.