472,353 Members | 1,921 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,353 software developers and data experts.

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

Nov 20 '05 #1
2 2147
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Bennett Haselton | last post by:
I want to get an ASP.Net hosting account with my ISP, and I'm trying to find out what level of access to the server is requried in order for me to...
3
by: David Gray | last post by:
Hello all, Having problems connecting to an Oracle 9i database from within SQL/Server 2000 using the Security/Linked Servers feature. Server1...
0
by: Mark Huebner | last post by:
I have Visual Studio 2003 Professional and MS SQL Server 2000 installed on my laptop. For some reason, I can't create a new database with the...
48
by: ik | last post by:
ERROR after uninstalling SQL Server 2005 Express I get this message, SQLDMO has not been registered. Please re-run your setupand contact your...
1
by: mikew | last post by:
I am working at a company that has been using MS SQL Server, and we are going to be switching over to postgresql next week. (Getting off of Windows...
1
by: David | last post by:
Hi, I have the Visual Studio .NET installed under Windows XP Pro, in my laptop. Right now I practice C#, and I don't know how to access and use the...
14
by: David Tilman | last post by:
I've gone throught the MSDN walkthrough "Creating a Web Application Using Visual C# or Visual Basic". When I run the sample I get "Login failed for...
2
by: Mały Piotruœ | last post by:
Hello, (sorry for my English...) Could you help me please with installing SQL Servera 2005 Express Edition. I downloaded files: SQLEXPR.EXE -...
1
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and...
0
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was...
2
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...
0
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...
0
by: Arjunsri | last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and...
0
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web...
0
BLUEPANDA
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS...
0
by: Rahul1995seven | last post by:
Introduction: In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.