Mistery with UPDATE statement

I have a table gVendor that has a field IsActive. It just shows if vendor is
active or not. When I update gVendor informatoin I leave IsActive out and
hanlde it in an other SP.
The problem is that eventhough I don't update that fields it resets to false
(0).
I can't figure out why.
Thank you.
Shimon.
Here is SP.
ALTER PROCEDURE [dbo].[g_pVendorUpdate]
(@.VendorId [int],
@.CompanyName [varchar](60),
@.VendorTypeId [tinyint],
@.AccountId [int],
@.TaxId [varchar](30),
@.ContactName [varchar](50),
@.Phone [varchar](25),
@.Fax [varchar](25),
@.Email [varchar](100),
@.Address [varchar](100),
@.City [varchar](50),
@.State [varchar](25),
@.Zip [varchar](10),
@.IsUtility [bit],
@.Note [varchar](500))
AS UPDATE [gVendor]
SET [CompanyName] = @.CompanyName,
[VendorTypeId] = @.VendorTypeId,
[AccountId] = @.AccountId,
[TaxId] = @.TaxId,
[ContactName] = @.ContactName,
[Phone] = @.Phone,
[Fax] = @.Fax,
[Email] = @.Email,
[Address] = @.Address,
[City] = @.City,
[State] = @.State,
[Zip] = @.Zip,
[IsUtility] = @.IsUtility,
[Note] = @.Note
WHERE
( [VendorId] = @.VendorId)
If I add IsActive=IsActive then it will work.
Here is the table
CREATE TABLE [dbo].[gVendor](
[VendorId] [int] IDENTITY(1,1) NOT NULL,
[CompanyName] [varchar](60) COLLATE Cyrillic_General_CI_AS NOT NULL,
[VendorTypeId] [tinyint] NOT NULL CONSTRAINT [DF_gVendor_VendorTypeId]
DEFAULT (0),
[IsActive] [bit] NOT NULL CONSTRAINT [DF_gVendor_IsActive] DEFAULT (1),
[AccountId] [int] NULL,
[TaxId] [varchar](30) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
[DF_gVendor_TaxId] DEFAULT (''),
[ContactName] [varchar](50) COLLATE Cyrillic_General_CI_AS NOT NULL
CONSTRAINT [DF_gVendor_ContactName] DEFAULT (''),
[Phone] [varchar](25) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
[DF_gVendor_Phone] DEFAULT (''),
[Fax] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT
[DF_gVendor_Fax] DEFAULT (''),
[Email] [varchar](100) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
[DF_gVendor_Email] DEFAULT (''),
[Address] [varchar](100) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
[DF_gVendor_Address] DEFAULT (''),
[City] [varchar](50) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
[DF_gVendor_City] DEFAULT (''),
[State] [varchar](25) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
[DF_gVendor_State] DEFAULT (''),
[Zip] [char](10) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
[DF_gVendor_Zip] DEFAULT (''),
[Balance] [money] NOT NULL CONSTRAINT [DF_gVendor_Balance] DEFAULT (0),
[IsUtility] [bit] NOT NULL,
[Note] [varchar](500) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
[DF_gVendor_Memo] DEFAULT (''),
CONSTRAINT [PK_gVendor] PRIMARY KEY CLUSTERED
(
[VendorId] ASC
) ON [PRIMARY],
CONSTRAINT [unq_gVendorName] UNIQUE NONCLUSTERED
(
[CompanyName] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GODoes it do this when you are in QA or SSMS and make sure it happens there.
Then consider tracing your calls to see that something odd isn't taking
place. Nothing here seems to look fishy, but trace a set of calls
(including outputting values) and post it.

> If I add IsActive=IsActive then it will work.
Not sure that I understand this either. Is VendorId not the primary key?
It says it is in the script. The only thing this should to is eliminate
null rows. Since if IsActive is null then isActive=isActive is UNKNOWN,
which is not TRUE, and any NOT TRUE rows are eliminated.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Shimon Sim" <shimonsim048@.community.nospam> wrote in message
news:uVrhBMeJGHA.964@.tk2msftngp13.phx.gbl...
>I have a table gVendor that has a field IsActive. It just shows if vendor
>is active or not. When I update gVendor informatoin I leave IsActive out
>and hanlde it in an other SP.
> The problem is that eventhough I don't update that fields it resets to
> false (0).
> I can't figure out why.
> Thank you.
> Shimon.
> Here is SP.
> ALTER PROCEDURE [dbo].[g_pVendorUpdate]
> (@.VendorId [int],
> @.CompanyName [varchar](60),
> @.VendorTypeId [tinyint],
> @.AccountId [int],
> @.TaxId [varchar](30),
> @.ContactName [varchar](50),
> @.Phone [varchar](25),
> @.Fax [varchar](25),
> @.Email [varchar](100),
> @.Address [varchar](100),
> @.City [varchar](50),
> @.State [varchar](25),
> @.Zip [varchar](10),
> @.IsUtility [bit],
> @.Note [varchar](500))
> AS UPDATE [gVendor]
> SET [CompanyName] = @.CompanyName,
> [VendorTypeId] = @.VendorTypeId,
> [AccountId] = @.AccountId,
> [TaxId] = @.TaxId,
> [ContactName] = @.ContactName,
> [Phone] = @.Phone,
> [Fax] = @.Fax,
> [Email] = @.Email,
> [Address] = @.Address,
> [City] = @.City,
> [State] = @.State,
> [Zip] = @.Zip,
> [IsUtility] = @.IsUtility,
> [Note] = @.Note
> WHERE
> ( [VendorId] = @.VendorId)
> If I add IsActive=IsActive then it will work.
> Here is the table
> CREATE TABLE [dbo].[gVendor](
> [VendorId] [int] IDENTITY(1,1) NOT NULL,
> [CompanyName] [varchar](60) COLLATE Cyrillic_General_CI_AS NOT NULL,
> [VendorTypeId] [tinyint] NOT NULL CONSTRAINT [DF_gVendor_VendorTypeId]
> DEFAULT (0),
> [IsActive] [bit] NOT NULL CONSTRAINT [DF_gVendor_IsActive] DEFAULT (1),
> [AccountId] [int] NULL,
> [TaxId] [varchar](30) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
> [DF_gVendor_TaxId] DEFAULT (''),
> [ContactName] [varchar](50) COLLATE Cyrillic_General_CI_AS NOT NULL
> CONSTRAINT [DF_gVendor_ContactName] DEFAULT (''),
> [Phone] [varchar](25) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
> [DF_gVendor_Phone] DEFAULT (''),
> [Fax] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> CONSTRAINT [DF_gVendor_Fax] DEFAULT (''),
> [Email] [varchar](100) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
> [DF_gVendor_Email] DEFAULT (''),
> [Address] [varchar](100) COLLATE Cyrillic_General_CI_AS NOT NULL
> CONSTRAINT [DF_gVendor_Address] DEFAULT (''),
> [City] [varchar](50) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
> [DF_gVendor_City] DEFAULT (''),
> [State] [varchar](25) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
> [DF_gVendor_State] DEFAULT (''),
> [Zip] [char](10) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
> [DF_gVendor_Zip] DEFAULT (''),
> [Balance] [money] NOT NULL CONSTRAINT [DF_gVendor_Balance] DEFAULT (0),
> [IsUtility] [bit] NOT NULL,
> [Note] [varchar](500) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
> [DF_gVendor_Memo] DEFAULT (''),
> CONSTRAINT [PK_gVendor] PRIMARY KEY CLUSTERED
> (
> [VendorId] ASC
> ) ON [PRIMARY],
> CONSTRAINT [unq_gVendorName] UNIQUE NONCLUSTERED
> (
> [CompanyName] ASC
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>|||This is the WORST code I have seen in ws!!
None of your data elements names comply with ISO-11179 standards. Can
you tell us why you violated them? You do known the baiscs of your
trade, don't you'
What is a G-vendor and why is he not like a regular vendor? Why did
you think that Dr. Codd is wrong and that IDENTITY can ever be a key'
Why do your vendors not have an industry standard code, like a DUNS
numbers? Did you know that the DEFAULT clause comes before the NOT
NULL constraint in Standard SQL? That money has screwed up math? Etc.
Why do you think that a data element name like "foobar_type_id" is
a meaningful name? How can a thing be both a type code and an
identifier? Why did you use BIT data types in an RDBMS -- were you an
assembly language programmer? Did you confuse 1950's files with the
relational model?
How did you get a phone number that is VARCHAR(25) when the
international standard is CHAR(15)? An address line of CHAR(100), when
the International Postal Union allows only CHAR(35)? The rest of your
DDL is un-researched and unusable.
I could not find a single column in your DDL that was right. Please
stop coding and get some help.|||> How did you get a phone number that is VARCHAR(25) when the
> international standard is CHAR(15)? An address line of CHAR(100), when
> the International Postal Union allows only CHAR(35)? The rest of your
> DDL is un-researched and unusable.
For our edification, where is this documented? I can not seem to find a
good source of such things.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1138681735.224930.83760@.g43g2000cwa.googlegroups.com...
> This is the WORST code I have seen in ws!!
> None of your data elements names comply with ISO-11179 standards. Can
> you tell us why you violated them? You do known the baiscs of your
> trade, don't you'
> What is a G-vendor and why is he not like a regular vendor? Why did
> you think that Dr. Codd is wrong and that IDENTITY can ever be a key'
> Why do your vendors not have an industry standard code, like a DUNS
> numbers? Did you know that the DEFAULT clause comes before the NOT
> NULL constraint in Standard SQL? That money has screwed up math? Etc.
>
> Why do you think that a data element name like "foobar_type_id" is
> a meaningful name? How can a thing be both a type code and an
> identifier? Why did you use BIT data types in an RDBMS -- were you an
> assembly language programmer? Did you confuse 1950's files with the
> relational model?
> How did you get a phone number that is VARCHAR(25) when the
> international standard is CHAR(15)? An address line of CHAR(100), when
> the International Postal Union allows only CHAR(35)? The rest of your
> DDL is un-researched and unusable.
> I could not find a single column in your DDL that was right. Please
> stop coding and get some help.
>|||Hi Shimon,
Welcome to use MSDN Managed Newsgroup! This is Justin.
I checked the script of the table and SP but did not find anything abnormal.
If you run this SP in Query Analyzer, will it have the same problem? If so,
please check whether there is any update trigger defined on the table.
And please capture a Profiler trace when running the SP so that we could
know what is exactly going on behind the scene. Thus, we may able to find
the cause of this problem.
If you have any question, please feel free to let me know.
Thanks & Regards,
Justin Shen
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a w to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others: https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/defaul...rnational.aspx.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "Shimon Sim" <shimonsim048@.community.nospam>
| Subject: Mistery with UPDATE statement
| Date: Mon, 30 Jan 2006 16:20:31 -0500
| Lines: 146
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
| X-RFC2646: Format=Flowed; Original
| Message-ID: <uVrhBMeJGHA.964@.tk2msftngp13.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.programming
| NNTP-Posting-Host: ool-43530893.dyn.optonline.net 67.83.8.147
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.programming:577395
| X-Tomcat-NG: microsoft.public.sqlserver.programming
|
| I have a table gVendor that has a field IsActive. It just shows if vendor
is
| active or not. When I update gVendor informatoin I leave IsActive out and
| hanlde it in an other SP.
| The problem is that eventhough I don't update that fields it resets to
false
| (0).
| I can't figure out why.
| Thank you.
| Shimon.
| Here is SP.
| ALTER PROCEDURE [dbo].[g_pVendorUpdate]
|
| (@.VendorId [int],
|
| @.CompanyName [varchar](60),
|
| @.VendorTypeId [tinyint],
|
| @.AccountId [int],
|
| @.TaxId [varchar](30),
|
| @.ContactName [varchar](50),
|
| @.Phone [varchar](25),
|
| @.Fax [varchar](25),
|
| @.Email [varchar](100),
|
| @.Address [varchar](100),
|
| @.City [varchar](50),
|
| @.State [varchar](25),
|
| @.Zip [varchar](10),
|
| @.IsUtility [bit],
|
| @.Note [varchar](500))
|
| AS UPDATE [gVendor]
|
| SET [CompanyName] = @.CompanyName,
|
| [VendorTypeId] = @.VendorTypeId,
|
| [AccountId] = @.AccountId,
|
| [TaxId] = @.TaxId,
|
| [ContactName] = @.ContactName,
|
| [Phone] = @.Phone,
|
| [Fax] = @.Fax,
|
| [Email] = @.Email,
|
| [Address] = @.Address,
|
| [City] = @.City,
|
| [State] = @.State,
|
| [Zip] = @.Zip,
|
| [IsUtility] = @.IsUtility,
|
| [Note] = @.Note
|
| WHERE
|
| ( [VendorId] = @.VendorId)
|
| If I add IsActive=IsActive then it will work.
|
| Here is the table
| CREATE TABLE [dbo].[gVendor](
|
| [VendorId] [int] IDENTITY(1,1) NOT NULL,
|
| [CompanyName] [varchar](60) COLLATE Cyrillic_General_CI_AS NOT NULL,
|
| [VendorTypeId] [tinyint] NOT NULL CONSTRAINT [DF_gVendor_VendorTypeId]
| DEFAULT (0),
|
| [IsActive] [bit] NOT NULL CONSTRAINT [DF_gVendor_IsActive] DEFAULT (1),
|
| [AccountId] [int] NULL,
|
| [TaxId] [varchar](30) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
| [DF_gVendor_TaxId] DEFAULT (''),
|
| [ContactName] [varchar](50) COLLATE Cyrillic_General_CI_AS NOT NULL
| CONSTRAINT [DF_gVendor_ContactName] DEFAULT (''),
|
| [Phone] [varchar](25) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
| [DF_gVendor_Phone] DEFAULT (''),
|
| [Fax] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT
| [DF_gVendor_Fax] DEFAULT (''),
|
| [Email] [varchar](100) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
| [DF_gVendor_Email] DEFAULT (''),
|
| [Address] [varchar](100) COLLATE Cyrillic_General_CI_AS NOT NULL
CONSTRAINT
| [DF_gVendor_Address] DEFAULT (''),
|
| [City] [varchar](50) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
| [DF_gVendor_City] DEFAULT (''),
|
| [State] [varchar](25) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
| [DF_gVendor_State] DEFAULT (''),
|
| [Zip] [char](10) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
| [DF_gVendor_Zip] DEFAULT (''),
|
| [Balance] [money] NOT NULL CONSTRAINT [DF_gVendor_Balance] DEFAULT (0),
|
| [IsUtility] [bit] NOT NULL,
|
| [Note] [varchar](500) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
| [DF_gVendor_Memo] DEFAULT (''),
|
| CONSTRAINT [PK_gVendor] PRIMARY KEY CLUSTERED
|
| (
|
| [VendorId] ASC
|
| ) ON [PRIMARY],
|
| CONSTRAINT [unq_gVendorName] UNIQUE NONCLUSTERED
|
| (
|
| [CompanyName] ASC
|
| ) ON [PRIMARY]
|
| ) ON [PRIMARY]
|
| GO
|
|
||||>> Why did
Oh dear, we still have not got it into your thick skull about surrogate KEYS
and the use of the IDENTITY property!
Stop holding people back, Codd's stuff is really great foundations but we
need to evolve as business and the industry evolves with different
challenges.
Stop using Doctorine! It might help the majority of your dated and
unworkable examples and bring your skill set up-to-date, we are no longer in
the 80's!
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1138681735.224930.83760@.g43g2000cwa.googlegroups.com...
> This is the WORST code I have seen in ws!!
> None of your data elements names comply with ISO-11179 standards. Can
> you tell us why you violated them? You do known the baiscs of your
> trade, don't you'
> What is a G-vendor and why is he not like a regular vendor? Why did
> you think that Dr. Codd is wrong and that IDENTITY can ever be a key'
> Why do your vendors not have an industry standard code, like a DUNS
> numbers? Did you know that the DEFAULT clause comes before the NOT
> NULL constraint in Standard SQL? That money has screwed up math? Etc.
>
> Why do you think that a data element name like "foobar_type_id" is
> a meaningful name? How can a thing be both a type code and an
> identifier? Why did you use BIT data types in an RDBMS -- were you an
> assembly language programmer? Did you confuse 1950's files with the
> relational model?
> How did you get a phone number that is VARCHAR(25) when the
> international standard is CHAR(15)? An address line of CHAR(100), when
> the International Postal Union allows only CHAR(35)? The rest of your
> DDL is un-researched and unusable.
> I could not find a single column in your DDL that was right. Please
> stop coding and get some help.
>|||Thanks for questions.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1138681735.224930.83760@.g43g2000cwa.googlegroups.com...
> This is the WORST code I have seen in ws!!
> None of your data elements names comply with ISO-11179 standards. Can
> you tell us why you violated them? You do known the baiscs of your
> trade, don't you'
> What is a G-vendor and why is he not like a regular vendor?
The database that I was using was used for other application as well and in
order to avoid collision I used prefix. It is common this days you can check
any MS databases for application like ASPNET or BizTalkl Server(i think)
> Why did
> you think that Dr. Codd is wrong and that IDENTITY can ever be a key'
I never saw an article from Dr. Codd on this. The books that I used have
identity as a primary key.
> Why do your vendors not have an industry standard code, like a DUNS
> numbers?
Wasn't part of requirements.

> Did you know that the DEFAULT clause comes before the NOT
> NULL constraint in Standard SQL?
This is generated code from SSMS

> That money has screwed up math?
What'

> Etc.
>
> Why do you think that a data element name like "foobar_type_id" is
> a meaningful name?
again generated code.

> How can a thing be both a type code and an
> identifier? Why did you use BIT data types in an RDBMS -- were you an
> assembly language programmer?
BIT is a SQL Server data type.

> Did you confuse 1950's files with the
> relational model?
I wasn't born then.
> How did you get a phone number that is VARCHAR(25) when the
> international standard is CHAR(15)?
Client didn't care about standards. Needed to include extentions and extra
field I didn't want to manage. It was OK with them and me.
> An address line of CHAR(100), when
> the International Postal Union allows only CHAR(35)?
What company are you working for that you need to know all this stuff?
I didn't need. It doesn't affect size of database in any way. So what is a
problem?

> The rest of your
> DDL is un-researched and unusable.
That is good I need to answere less questions.
> I could not find a single column in your DDL that was right. Please
> stop coding and get some help.
>|||Thank you for your answer.
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:OC$t65hJGHA.1028@.TK2MSFTNGP11.phx.gbl...
> Does it do this when you are in QA or SSMS and make sure it happens there.
> Then consider tracing your calls to see that something odd isn't taking
> place. Nothing here seems to look fishy, but trace a set of calls
> (including outputting values) and post it.
Yes. I ran the code on SSMS and got the problem again. I will try to post
results later.
But From all the posts it does seem that I am missing something simple.
I don't see any triggers execting in the trace in Profiler. Will they show
in there?

>
> Not sure that I understand this either. Is VendorId not the primary key?
> It says it is in the script. The only thing this should to is eliminate
> null rows. Since if IsActive is null then isActive=isActive is UNKNOWN,
> which is not TRUE, and any NOT TRUE rows are eliminated.
Yes the vendor is primary key.
I ment is it set part of code
UPDATE gVendor SET...
IsActive=IsActive
WHERE...
IsActive is never NULL it has default 1. But after update I get it 0.

> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often
> convincing."
> (Oscar Wilde)
> "Shimon Sim" <shimonsim048@.community.nospam> wrote in message
> news:uVrhBMeJGHA.964@.tk2msftngp13.phx.gbl...
>|||"Justin Shen[MSFT]" <v-yishen@.online.microsoft.com> wrote in message
news:W0EwMTiJGHA.3944@.TK2MSFTNGXA02.phx.gbl...
> Hi Shimon,
> Welcome to use MSDN Managed Newsgroup! This is Justin.
> I checked the script of the table and SP but did not find anything
> abnormal.
> If you run this SP in Query Analyzer, will it have the same problem?
Yes.
>If so,
> please check whether there is any update trigger defined on the table.
I don't see any. But if there are any trigggers would the show in Profiler
trace?

> And please capture a Profiler trace when running the SP so that we could
> know what is exactly going on behind the scene. Thus, we may able to find
> the cause of this problem.
> If you have any question, please feel free to let me know.
> Thanks & Regards,
> Justin Shen
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> Business-Critical Phone Support (BCPS) provides you with technical phone
> support at no charge during critical LAN outages or "business down"
> situations. This benefit is available 24 hours a day, 7 days a w to all
> Microsoft technology partners in the United States and Canada.
> This and other support options are available here:
> BCPS:
> https://partner.microsoft.com/US/te...erview/40010469
> Others: https://partner.microsoft.com/US/te...upportoverview/
> If you are outside the United States, please visit our International
> Support page:
> http://support.microsoft.com/defaul...rnational.aspx.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> --
> | From: "Shimon Sim" <shimonsim048@.community.nospam>
> | Subject: Mistery with UPDATE statement
> | Date: Mon, 30 Jan 2006 16:20:31 -0500
> | Lines: 146
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
> | X-RFC2646: Format=Flowed; Original
> | Message-ID: <uVrhBMeJGHA.964@.tk2msftngp13.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.programming
> | NNTP-Posting-Host: ool-43530893.dyn.optonline.net 67.83.8.147
> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
> | Xref: TK2MSFTNGXA02.phx.gbl
> microsoft.public.sqlserver.programming:577395
> | X-Tomcat-NG: microsoft.public.sqlserver.programming
> |
> | I have a table gVendor that has a field IsActive. It just shows if
> vendor
> is
> | active or not. When I update gVendor informatoin I leave IsActive out
> and
> | hanlde it in an other SP.
> | The problem is that eventhough I don't update that fields it resets to
> false
> | (0).
> | I can't figure out why.
> | Thank you.
> | Shimon.
> | Here is SP.
> | ALTER PROCEDURE [dbo].[g_pVendorUpdate]
> |
> | (@.VendorId [int],
> |
> | @.CompanyName [varchar](60),
> |
> | @.VendorTypeId [tinyint],
> |
> | @.AccountId [int],
> |
> | @.TaxId [varchar](30),
> |
> | @.ContactName [varchar](50),
> |
> | @.Phone [varchar](25),
> |
> | @.Fax [varchar](25),
> |
> | @.Email [varchar](100),
> |
> | @.Address [varchar](100),
> |
> | @.City [varchar](50),
> |
> | @.State [varchar](25),
> |
> | @.Zip [varchar](10),
> |
> | @.IsUtility [bit],
> |
> | @.Note [varchar](500))
> |
> | AS UPDATE [gVendor]
> |
> | SET [CompanyName] = @.CompanyName,
> |
> | [VendorTypeId] = @.VendorTypeId,
> |
> | [AccountId] = @.AccountId,
> |
> | [TaxId] = @.TaxId,
> |
> | [ContactName] = @.ContactName,
> |
> | [Phone] = @.Phone,
> |
> | [Fax] = @.Fax,
> |
> | [Email] = @.Email,
> |
> | [Address] = @.Address,
> |
> | [City] = @.City,
> |
> | [State] = @.State,
> |
> | [Zip] = @.Zip,
> |
> | [IsUtility] = @.IsUtility,
> |
> | [Note] = @.Note
> |
> | WHERE
> |
> | ( [VendorId] = @.VendorId)
> |
> | If I add IsActive=IsActive then it will work.
> |
> | Here is the table
> | CREATE TABLE [dbo].[gVendor](
> |
> | [VendorId] [int] IDENTITY(1,1) NOT NULL,
> |
> | [CompanyName] [varchar](60) COLLATE Cyrillic_General_CI_AS NOT NULL,
> |
> | [VendorTypeId] [tinyint] NOT NULL CONSTRAINT [DF_gVendor_VendorTypeId]
> | DEFAULT (0),
> |
> | [IsActive] [bit] NOT NULL CONSTRAINT [DF_gVendor_IsActive] DEFAULT (1),
> |
> | [AccountId] [int] NULL,
> |
> | [TaxId] [varchar](30) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
> | [DF_gVendor_TaxId] DEFAULT (''),
> |
> | [ContactName] [varchar](50) COLLATE Cyrillic_General_CI_AS NOT NULL
> | CONSTRAINT [DF_gVendor_ContactName] DEFAULT (''),
> |
> | [Phone] [varchar](25) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
> | [DF_gVendor_Phone] DEFAULT (''),
> |
> | [Fax] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> CONSTRAINT
> | [DF_gVendor_Fax] DEFAULT (''),
> |
> | [Email] [varchar](100) COLLATE Cyrillic_General_CI_AS NOT NULL
> CONSTRAINT
> | [DF_gVendor_Email] DEFAULT (''),
> |
> | [Address] [varchar](100) COLLATE Cyrillic_General_CI_AS NOT NULL
> CONSTRAINT
> | [DF_gVendor_Address] DEFAULT (''),
> |
> | [City] [varchar](50) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
> | [DF_gVendor_City] DEFAULT (''),
> |
> | [State] [varchar](25) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
> | [DF_gVendor_State] DEFAULT (''),
> |
> | [Zip] [char](10) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
> | [DF_gVendor_Zip] DEFAULT (''),
> |
> | [Balance] [money] NOT NULL CONSTRAINT [DF_gVendor_Balance] DEFAULT (0),
> |
> | [IsUtility] [bit] NOT NULL,
> |
> | [Note] [varchar](500) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
> | [DF_gVendor_Memo] DEFAULT (''),
> |
> | CONSTRAINT [PK_gVendor] PRIMARY KEY CLUSTERED
> |
> | (
> |
> | [VendorId] ASC
> |
> | ) ON [PRIMARY],
> |
> | CONSTRAINT [unq_gVendorName] UNIQUE NONCLUSTERED
> |
> | (
> |
> | [CompanyName] ASC
> |
> | ) ON [PRIMARY]
> |
> | ) ON [PRIMARY]
> |
> | GO
> |
> |
> |
>|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1138681735.224930.83760@.g43g2000cwa.googlegroups.com...
> This is the WORST code I have seen in ws!!
>
You post this same insult twice a day.
Have you ever tried constructive criticism?
I'll refer you to Dale Carnegie, who, although often a bit touchy-feely for
my taste, is the undisputed authority on the topic.