[dba-SQLServer] Update query through Access ADP

<back pedal mode on>

WHERE LEN(Contact) = 0

would return a null when Contact is null, not a zero.

the following below will take care of nulls, '', ' ', '     ' and variations
of spaces


UPDATE Projects2
SET ContactEmail = NULL
WHERE ISNULL(LTRIM(RTRIM(@X)),'') = ''



-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of
dmcafee at pacbell.net
Sent: Friday, March 11, 2005 10:14 AM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer] Update query through Access ADP


Steven since you have the answer to your question, I'd like to chime in to
let you know that you can also do something like this:

UPDATE Projects2
SET ContactEmail = NULL
WHERE LEN(Contact) = 0

D


-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of Steve
Erbach
Sent: Friday, March 11, 2005 5:52 AM
To: dba-sqlserver at databaseadvisors.com
Subject: [dba-SQLServer] Update query through Access ADP


Dear Group,

I've been looking through some of a client's ASP application with a
view to making some slight modifications. (There are almost 800 ASP
files. Yikes!)

The back end is SQL Server and I've been using Access 2003 to
construct views and stored procedures. I've been working on a COPY of
the tables on my own SQL Server rather than the customer's live data.

What's got me curious is this: I've been working with an sproc that
updates a column containing an email address. It looks something like
this:

UPDATE Projects2
SET ContactEmail = NULL
WHERE (Contact = '') OR (Contact = ' ') OR (Contact IS NULL)

So if the Contact name doesn't have anything in it, I want the
Contact's email address to be NULL. At present, there's a default
e-mail address in every row.

After the sproc runs I look at the table itself. Well, this table has
133,000 records in it. Access 2003 shows me the first 10,000 by
default. But when I go past the 10,000th record, the e-mail column
still has addresses in it even though the Contact field is empty.

Is there something I'm missing in the sproc? Something like a command
that says, "OK, I really, REALLY want to update the rows beyond
10,000, too."

Regards,

Steve Erbach
Scientific Marketing
Neenah, WI
www.swerbach.com
Security Page: www.swerbach.com/security
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com