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 *********************************************