[dba-SQLServer] Re: Add constraint

David Lewis DavidL at sierranevada.com
Thu Mar 31 12:25:14 CST 2005



Ken:

I have not actually done this, but from BOL:



D. Alter a table to add an unverified constraint
This example adds a constraint to an existing column in the table. The
column has a value that violates the constraint; therefore, WITH NOCHECK
is used to prevent the constraint from being validated against existing
rows, and to allow the constraint to be added.

CREATE TABLE doc_exd ( column_a INT) 
GO
INSERT INTO doc_exd VALUES (-1)
GO
ALTER TABLE doc_exd WITH NOCHECK 
ADD CONSTRAINT exd_check CHECK (column_a > 1)
GO
EXEC sp_help doc_exd
GO
DROP TABLE doc_exd
GO



Message: 1
Date: Tue, 29 Mar 2005 11:53:29 -0800
From: "Ken Stoker" <kens.programming at verizon.net>
Subject: [dba-SQLServer] Adding a default to an existing column
To: <dba-sqlserver at databaseadvisors.com>

David, 

Thanks for your reply, but I had tried that already and received and
error stating there was a syntax error near ADD.  Am I missing a comma
or a bracket or something like that?

Ken

-----Original Message-----
From: David Lewis [mailto:DavidL at sierranevada.com]
Sent: Wednesday, March 30, 2005 10:22 AM
To: dba-sqlserver at databaseadvisors.com
Cc: kens.programming at verizon.net
Subject: ADD Constraint


Not sure, but I think you need ADD CONSTRAINT

D. Lewis


Message: 1
Date: Tue, 29 Mar 2005 11:53:29 -0800
From: "Ken Stoker" <kens.programming at verizon.net>
Subject: [dba-SQLServer] Adding a default to an existing column
To: <dba-sqlserver at databaseadvisors.com>
Message-ID: <0IE400I33PSVKK00 at vms042.mailsrvcs.net>
Content-Type: text/plain;	charset="windows-1250"

I have put together the following to add a default to an existing table.
It is telling me there is a syntax error near CONSTRAINT.  I have tried
many different things, this being the latest, getting similar problems.


 

ALTER TABLE TableName

ALTER COLUMN ColumnName UserDefinedType NULL

CONSTRAINT AddOwnerDflt

DEFAULT '100000' WITH VALUES

GO

 

Thanks for any help.

 

Ken

-- 
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.8.6 - Release Date: 3/30/2005
 

-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.8.6 - Release Date: 3/30/2005
 



------------------------------

Message: 3
Date: Wed, 30 Mar 2005 16:54:57 -0600
From: Steve Erbach <erbachs at gmail.com>
Subject: [dba-SQLServer] New SQL Server Registration problem
To: dba-sqlserver at databaseadvisors.com
Message-ID: <39cb22f3050330145440a38e50 at mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1

Dear Group,

I'm baffled. I have an Access 2000 ADP that connects to a certain SQL
Server database. I have an Access 2000 MDB that connects via ODBC to
the same SQL Server database. I have an Access 2003 ADP that connects
to the same database. I have an ASP.NET project that connects to the
same database.

My problem is that I can't seem to connect to the database using SQL
Server Enterprise Manager. I go to add a new Registration and up pops
the 'Registered SQL Server Properties' dialog box. I fill in the IP
address of the SQL Server and select the 'Use SQL Server
Authentication' button, and type in the Login Name and Password. When
I click OK I get the following message:

-----------------------------------------------
SQL Server Enterprise Manager
-----------------------------------------------
? SQL Server registration failed because of the connection failure
displayed below. Do you wish to Modify anyway?

Cannot open user default database. Login failed.
-----------------------------------------------

I've used Enterprise Manager to set up four other SQL Server
connections that all work fine. Any ideas why I can't get EM to create
a new registration?

-- 
Regards,

Steve Erbach
Scientific Marketing
Neenah, WI
www.swerbach.com
Security Page: www.swerbach.com/security


------------------------------

Message: 4
Date: Thu, 31 Mar 2005 08:35:52 -0800
From: Francisco Tapia <fhtapia at gmail.com>
Subject: Re: [dba-SQLServer] New SQL Server Registration problem
To: dba-sqlserver at databaseadvisors.com
Message-ID: <b874372a050331083518b9dc42 at mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1

what MDAC version do you have on your pc? (if you do not know for sure
visit microsoft and get the component checker)


On Wed, 30 Mar 2005 16:54:57 -0600, Steve Erbach <erbachs at gmail.com>
wrote:
> Dear Group,
> 
> I'm baffled. I have an Access 2000 ADP that connects to a certain SQL
> Server database. I have an Access 2000 MDB that connects via ODBC to
> the same SQL Server database. I have an Access 2003 ADP that connects
> to the same database. I have an ASP.NET project that connects to the
> same database.
> 
> My problem is that I can't seem to connect to the database using SQL
> Server Enterprise Manager. I go to add a new Registration and up pops
> the 'Registered SQL Server Properties' dialog box. I fill in the IP
> address of the SQL Server and select the 'Use SQL Server
> Authentication' button, and type in the Login Name and Password. When
> I click OK I get the following message:
> 
> -----------------------------------------------
> SQL Server Enterprise Manager
> -----------------------------------------------
> ? SQL Server registration failed because of the connection failure
> displayed below. Do you wish to Modify anyway?
> 
> Cannot open user default database. Login failed.
> -----------------------------------------------
> 
> I've used Enterprise Manager to set up four other SQL Server
> connections that all work fine. Any ideas why I can't get EM to create
> a new registration?
> 
> --
> 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
> 
> 


-- 
-Francisco
http://pcthis.blogspot.com | PC news with out the jargon!
http://sqlthis.blogspot.com | Tsql and More...


------------------------------

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


End of dba-SQLServer Digest, Vol 25, Issue 17
*********************************************



More information about the dba-SQLServer mailing list