modify ALIAS SQL Server Name with SQL 2005

Hello,

I would like create/modify/update the alias SQL server name on the SQL 2005 version.

I had a database mirroring on one base, and when the primary server fail, i would like my secondary alias become as the primary. I need this because of the application whom use my SQL server.

I tried with

sp_dropserver <old_name>
GO
sp_addserver <new_name>, local
GO

But this way doesn't work...

Anyone got an idea to change alias SQL server in transact?

Thanks for your help!!!

Hello,

I've found my answer on this site

http://blogs.developpeur.org/christian/archive/2006/11/04/SQL-Server-_3A00_-Alias-de-serveurs.aspx

We can change the alias SQL Server 2005 directly in the registry.

See u ;)

modify ALIAS SQL Server Name with SQL 2005

Hello,

I would like create/modify/update the alias SQL server name on the SQL 2005 version.

I had a database mirroring on one base, and when the primary server fail, i would like my secondary alias become as the primary. I need this because of the application whom use my SQL server.

I tried with

sp_dropserver <old_name>
GO
sp_addserver <new_name>, local
GO

But this way doesn't work...

Anyone got an idea to change alias SQL server in transact?

Thanks for your help!!!

Hello,

I've found my answer on this site

http://blogs.developpeur.org/christian/archive/2006/11/04/SQL-Server-_3A00_-Alias-de-serveurs.aspx

We can change the alias SQL Server 2005 directly in the registry.

See u ;)

sql

Modify [Permission]

hi guys,
My clients cann't update the sql database through ASP scripts.
How can i give the permission to add and modify the rows and columns?
Thank youYou can use SP_ADDROLEMEMBER and give them db_datawriter fixed db role.

Modifing the row that invokes a trigger from within that trigger

When a row gets modified and it invokes a trigger, we would like to be
able to update the row that was modified inside the trigger. This is
(basically) how we are doing it now:

CREATE TRIGGER trTBL ON TBL
FOR UPDATE, INSERT, DELETE
as
update TBL
set fld = 'value'
from inserted, TBL
where inserted.id= TBL.id

...

This work fine but it seems like it could be optimized. Clearly we are
having to scan the entire table again to update the row. But shouldn't
the trigger already know which row invoked it. Do we have to scan the
table again for this row or is their some syntax that allows us to
update the row that invoked the trigger. If not, why. It seems like
this would be a fairly common task. Thanks.--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1

The trigger does "know" which rows have been updated, they are in the
inserted recordset.

If you want to eliminate table scans put an index on the "joining"
columns between inserted and the updated table: in your case the [id]
column in the TBL table.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmgkHIechKqOuFEgEQKj0ACg/gThmkK3I5FVs014i96EY1KEqyEAniEQ
TeGhjpWGcoYjj51fomXBxth4
=ilXF
--END PGP SIGNATURE--

nosbtr1 wrote:
> When a row gets modified and it invokes a trigger, we would like to be
> able to update the row that was modified inside the trigger. This is
> (basically) how we are doing it now:
> CREATE TRIGGER trTBL ON TBL
> FOR UPDATE, INSERT, DELETE
> as
> update TBL
> set fld = 'value'
> from inserted, TBL
> where inserted.id= TBL.id
> ...
> This work fine but it seems like it could be optimized. Clearly we are
> having to scan the entire table again to update the row. But shouldn't
> the trigger already know which row invoked it. Do we have to scan the
> table again for this row or is their some syntax that allows us to
> update the row that invoked the trigger. If not, why. It seems like
> this would be a fairly common task. Thanks.|||On 21 Apr 2005 14:11:20 -0700, nosbtr1 wrote:

>When a row gets modified and it invokes a trigger, we would like to be
>able to update the row that was modified inside the trigger. This is
>(basically) how we are doing it now:
>CREATE TRIGGER trTBL ON TBL
>FOR UPDATE, INSERT, DELETE
>as
>update TBL
> set fld = 'value'
>from inserted, TBL
> where inserted.id= TBL.id
>...
>This work fine but it seems like it could be optimized. Clearly we are
>having to scan the entire table again to update the row. But shouldn't
>the trigger already know which row invoked it. Do we have to scan the
>table again for this row or is their some syntax that allows us to
>update the row that invoked the trigger. If not, why. It seems like
>this would be a fairly common task. Thanks.

Hi nosbtr1,

1. There is no "special syntax" to find the rowS (note the plural)
affected by the statement that fired the trigger, other than what you
are already using: the inserted and deleted pseudo-tables.

2. Your statement that this will cause a table scan is incorrect - if
you have a PRIMARY KEY constraint, a UNIQUE constraint or an INDEX
defined for the id column in the table, SQL Server can use an index seek
(followed by a bookmark lookup if the index used is nonclustered).

3. Your trigger won't do anything on delete operations. First because
the inserted pseudotable is always empty on a delete, and second becuase
the rows you are attempting to modify are already removed from the
table.

4. Why not rewrite the UPDATE statement above to the ANSI-compliant
alternative:

UPDATE Tbl
SET Fld = 'value'
WHERE EXISTS (SELECT *
FROM inserted
WHERE inserted.id = Tbl.id)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||You could use an instead of trigger, in this way you actually control
the way the data is updated in the table.

the code is much more complex though|||nosbtr1 (nosbtr1@.yahoo.com) writes:
> When a row gets modified and it invokes a trigger, we would like to be
> able to update the row that was modified inside the trigger. This is
> (basically) how we are doing it now:
> CREATE TRIGGER trTBL ON TBL
> FOR UPDATE, INSERT, DELETE
> as
> update TBL
> set fld = 'value'
> from inserted, TBL
> where inserted.id= TBL.id
> ...
> This work fine but it seems like it could be optimized. Clearly we are
> having to scan the entire table again to update the row. But shouldn't
> the trigger already know which row invoked it.

A trigger fires once per statement, and thus many rows may be affected.

The main performance thing with triggers is that one should be aware of
that the inserted/deleted tables are fairly slow. If you trigger makes
frequent references to these tables, it's a good idea to insert the data
into the pseudo tables into table variables, and work with these instead.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

moderate sql procedure question

In SQL Server 20:

Lets say I have a table for address. I create a stored procedure to update any value in the database, i.e.:

CREATE PROCEDURE [SP_UPDATE_T_Site]
(
@.old_I_SiteID bigint,
@.new_V_SiteName varchar(50),
@.new_V_Address1 varchar(50),
@.new_V_Address2 varchar(50),
@.new_V_Address3 varchar(50),
@.new_V_TownCity varchar(50),
@.new_I_RegionID bigint,
@.new_V_Postcode varchar(10)
)
AS
UPDATE T_Sites SET
[V_SiteName] = @.new_V_SiteName,
[V_Address1] = @.new_V_Address1,
[V_Address2] = @.new_V_Address2,
[V_Address3] = @.new_V_Address3,
[V_TownCity] = @.new_V_TownCity,
[I_RegionID] =@.new_I_RegionID ,
[V_PostCode] = @.new_V_Postcode
WHERE
I_SiteID= @.old_I_SiteID

GO

Now, lets say that the user only changes one value, e.g. Address1. Is is possible to only get this one value to update instead of passing all the values back and updating them all, i.e.:

EXEC SP_UPDATE_T_Site @.I_SiteID='2', @.Address1="...."

I know I can set default values and check these, but this would be too much work for the ammount of tables I have. Is there an easy way of doing this?

jagdipa wrote:

In SQL Server 20:

Lets say I have a table for address. I create a stored procedure to update any value in the database, i.e.:

CREATE PROCEDURE [SP_UPDATE_T_Site]
(
@.old_I_SiteID bigint,
@.new_V_SiteName varchar(50),
@.new_V_Address1 varchar(50),
@.new_V_Address2 varchar(50),
@.new_V_Address3 varchar(50),
@.new_V_TownCity varchar(50),
@.new_I_RegionID bigint,
@.new_V_Postcode varchar(10)
)
AS
UPDATE T_Sites SET
[V_SiteName] = @.new_V_SiteName,
[V_Address1] = @.new_V_Address1,
[V_Address2] = @.new_V_Address2,
[V_Address3] = @.new_V_Address3,
[V_TownCity] = @.new_V_TownCity,
[I_RegionID] =@.new_I_RegionID ,
[V_PostCode] = @.new_V_Postcode
WHERE
I_SiteID= @.old_I_SiteID

GO

Now, lets say that the user only changes one value, e.g. Address1. Is is possible to only get this one value to update instead of passing all the values back and updating them all, i.e.:

EXEC SP_UPDATE_T_Site @.I_SiteID='2', @.Address1="...."

I know I can set default values and check these, but this would be too much work for the ammount of tables I have. Is there an easy way of doing this?

hi jagdipa,
we're on the same situation on that one, I've decided since then to use ado.net to update optional data, and use only stored procs if the data to be update/inserted is consistent; meaning it updates all column and not a few...

|||Once approach you could try is this:

CREATE PROCEDURE [SP_UPDATE_T_Site]
(
@.old_I_SiteID bigint,
@.new_V_SiteName varchar(50) = NULL,
@.new_V_Address1 varchar(50) = NULL,
@.new_V_Address2 varchar(50) = NULL,
@.new_V_Address3 varchar(50) = NULL,
@.new_V_TownCity varchar(50) = NULL,
@.new_I_RegionID bigint = NULL,
@.new_V_Postcode varchar(10) = NULL
)
AS
UPDATE T_Sites SET
[V_SiteName] = ISNULL(@.new_V_SiteName,[V_SiteName]),
[V_Address1] = ISNULL(@.new_V_Address1,[V_Address1]),
[V_Address2] = ISNULL(@.new_V_Address2,[V_Address2]),
[V_Address3] = ISNULL(@.new_V_Address3,[V_Address3]),
[V_TownCity] = ISNULL(@.new_V_TownCity,[V_TownCity]),
[I_RegionID] = ISNULL(@.new_I_RegionID,[I_RegionID]),
[V_PostCode] = ISNULL(@.new_V_Postcode,[V_PostCode])
WHERE
I_SiteID= @.old_I_SiteID

GO

|||good one terry, i've used default values before but never thought on this implementation.. sweet..|||

Why is it not convenient to pass all the values in? How are you calling the Stored Proc?

If you use an Address object with Save method that calls the stored Proc, it should not be an issue to pass all the values to the Proc because all the values should be loaded into the instance of the Adress object that is calling Save.

|||

meantown2 wrote:

Why is it not convenient to pass all the values in? How are you calling the Stored Proc?

If you use an Address object with Save method that calls the stored Proc, it should not be an issue to pass all the values to the Proc because all the values should be loaded into the instance of the Adress object that is calling Save.

hi meantown, the poster wants to update only a selected column, if he/she is going to pass all values he might update columns he doesn't want to update. The reply of terry will prevent him/her from doing that, passing only selected parameters.

|||The reason I asked this was not really for this stored procedure (I just used this one for an easy example).

I have a B2B website where there are a lot of text fields on one webform. The data entered could grow. To try to improve effieciecy, I wanted to only pass the values that have changed.

This does mean a little extra work on the webform though. I will have to go through each value and check if it has changed.

So that leads me to my next question (this is a hard question to put down in writing, but I've tried my best to put my point across):

Lets say I have loaded the old values into a webform.
Then the user changes some of these and hits Save.
Now I need to check whether the values have changed. I could do this by comparing the values against the ones in the database - but this means an extra database access to retrieve the old values. Is there a way I can use the viewstate and somehow compare the values entered by the user against the ones stored in the viewstate?

Note: I dont want to put a load of invisible input fields everywhere and save the old values in that.

Thanks in advance for any help.

Jagdip

Mixing parameter syntax in Execute SQL task

Hi all,

As part of the logging process for data input, I want to update two fields in a logging table. The first is a datetime, derived from looking up the maximum value in another table (the table I've just imported), and the second is an integer - the number of rows captured in a variable during the task.

I can do this in two separate Execute SQL tasks as follows:

Task 1 syntax

DECLARE @.maxDate datetime
SELECT @.maxDate = max(dtLastChangedDate)
FROM dbo.tblCancel_RAW

UPDATE dbo.tblLogging
SET PreviousFilterValue = CurrentFilterValue,
CurrentFilterValue = ISNULL(CAST ( @.maxdate as varchar(25)),CurrentFilterValue),
DateSourceTableLastRead = GetDate(),
RowsReturned= -1
WHERE SourceTableName = 'cancel'

Task 2 Syntax, with the variable user::rowsimported mapped to parameter 0

UPDATE dbo.tblLogging
SET
RowsReturned= ?
WHERE SourceTableName = 'cancel'

However I cannot make this work with a single SQL statement such as

DECLARE @.maxDate datetime
SELECT @.maxDate = max(dtLastChangedDate)
FROM dbo.tblCancel_RAW

UPDATE dbo.tblLogging
SET PreviousFilterValue = CurrentFilterValue,
CurrentFilterValue = ISNULL(CAST ( @.maxdate as varchar(25)),CurrentFilterValue),
DateSourceTableLastRead = GetDate(),
RowsReturned= ?
WHERE SourceTableName = 'cancel'

because no matter how I try to map the parameter (0,1,2,3,4 etc) the task fails.

Is this behaviour by design, is it a bug, or is there something I've missed?

Thanks as ever,

Richard

Richard,

What error message do you get?

-Jamie

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.

Missing SET keyword.

Using the following update command I get the following message

UPDATE Last, First, [Card Number], [Phone Number], IDKey
FROM dbo.LibUserS

error:

Missing SET keyword.
Unable to parse query text.

incorrect syntax near ','

thats correct. The update statement requires the fields to update as well as on which record you want to update. Example:

UPDATE [TableName]

SET [FieldName] = someNewValue

WHERE [fieldName] = SomeValue

typically:

UPDATE [dbo.LibUserS]

SET Last = @.p1,

SET First = @.p2,

SET [Card Number] = @.p3,

SET [Phone Number] = @.p4

WHERE IDKey = @.IDValue

the @.parameter is the parameter you supply in the query on the command object (OleDbCommand or SqlCommand, whichever database connection/driver you are using) so it can take those values and replace them with the @.parameter "placeholder" if you like.

|||Helped, perfect, Thanks!