Altering SQL Field value to Null (DateField)

Hi,
Can someone please help me in resolving this problem.

I am accessing an SQL server from a Web page, when I update a record I sometimes would like to replace a date with a null value. ie. Delete the date in the grid on the Web page and have it remove the date in the database.

I have looked around the web and on this forum and cannot find any information about doing this type of thing.

Someone help would be greatly appreciated.

Thanks..

Regards..

Peter Annandale.Hi Peter,

It depends on the code you're using, but you should be able to set it to DBNull.Value.

If this doesn't work, post your code and we'll try to help you sort it out.

Don|||Don,
since I posted I actually found an article posted by Moorstream in early July about the exact problem I am having. By applying the recommendations of salman_arshad it has fixed my problem.

Thanks for your quick response and assistance.

BTW I had teh right idea with the DBNULL.Value I just wasn't aplying it correctly.

Regards..

Peter Annandale

Altering functions and CHECK constraints

Let's say I create a multi-statement function like this:

CREATE FUNCTION dbo.Test ()
RETURNS @.res TABLE (N int NOT NULL CHECK (N >= 0))
AS
BEGIN

INSERT INTO @.res
SELECT 1

RETURN
END

That works fine. Then I make a change in the function's body, replace the
CREATE FUNCTION with ALTER FUNCTION, and execute the batch. I get an error:

Server: Msg 3729, Level 16, State 3, Procedure Test, Line 9
Cannot ALTER 'dbo.Test' because it is being referenced by object
'CK__Test__N__5D2E32EB'.

Indeed, if I look at the list of dependencies for the function in QA's
object tree, I can see the check constraint referenced in the error
message.

ALTER FUNCTION works fine if I don't specify the CHECK constraint in the
definition of the @.res table.

So it seems that the only way to modify such a function is to drop and
recreate. Is that a known behavior? Is there any particular reason for it?

Thanks.

--
(remove a 9 to reply by email)Dimitri Furman (dfurman@.cloud99.net) writes:
> ALTER FUNCTION works fine if I don't specify the CHECK constraint in the
> definition of the @.res table.
> So it seems that the only way to modify such a function is to drop and
> recreate. Is that a known behavior? Is there any particular reason for it?

I will have to admit that I was not aware of this. As for why, my guess
is that this is an artefact of the metadata structure in SQL Server, and
the SQL Server developers did not write the necessary code to avoid this.

Anyway, the restriction is not there in SQL 2005, so whatever the reason
for this in SQL 2000, it is not likely to be a compelling one.

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

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

alter text column

Hi

I had a text type not null column which i wanted to change to a null column.Writing a simple alter statement gave me an eror cannot change text type column so i tried to rename the original column create a new column with the same name and allowing nulls on it and then copying the contents of the renamed column to the new column and finally deleting the renamed column.

EXEC sp_rename 'TableName.ColumnName', 'ColumnName_old', 'COLUMN'

ALTER TABLE TableName ADD ColumnName text NULL
UPDATE TableName SET ColumnName = ColumnName_old
ALTER TABLE TableName DROP COLUMN ColumnName_old

However when i tried to execute these statements in query analyser on the Update statement it gave me the error that ColumnName_old does not exist.

However then I tried to execute these queries one by one I was able to do that.

Can anybody tell me whats causing the queries to not be executed all at once without giving the ColumnName_old does not exist error cause I wanted to run them on live dbs.

any help would be appreciated.

Himani

Your logic should work with a little change. Add a GO between each batch.

Like:

EXEC sp_rename 'TableName.ColumnName', 'ColumnName_old', 'COLUMN'

GO

ALTER TABLE TableName ADD ColumnName text NULL

GO
UPDATE TableName SET ColumnName = ColumnName_old

GO
ALTER TABLE TableName DROP COLUMN ColumnName_old

GO

After each DML statement executed, you should get what you want.

By the way, it seems you can change the colum with text datatype from not null to allow null directly from the table definition in SQL 2005 Management Studio.

Also, you can directly do the update like this:

UPDATE yourTable

Set yourNewcolumTextAllowNull=youroldcolumnnotAllowNull

HTH

|||

Thanks a lot limno,it worked !!!

:-)

sql

alter table without data type

I would like to set a field to not null which I can do like this: ALTER TABLE table1 ALTER COLUMN field1 BIGINT NOT NULL

My question is there anyway to set this field to NOT NULL without having to specify the data type BIGINT? I'm using dynamic sql and if there was a way then I wouldn't need to go get the data type of the field dynamically and it would save me a step. Thanks.Why are using dynamic SQL in the first place?|||I'm creating tables from a DB2 database and importing it's data. I have to dynamically do this because the tables on the DB2 side are constantly changing.|||So when the tables change (and yes you need to supply the datatype) in DB2, how do you apply the schema changes? Are you using ERWin?

And what do you mean by constantly? Is this a production database?

And what schema changes are you applying...I mean it could get kind of hairy doing everything...indexes, constraints...it could get ugly...

Are you taking those things in to account?|||I'm doing a full refresh (drop/create) of the tables. Not using ERWin, just stored procedures.

"Constantly" may be a bad term. It is a production database (DB2 side) so the changes are minimal, but I do not want to maintain changes on the MSSQL side.

I created a table to relate keys of different tables I'm pulling in. So the schema I'm developing is controlled.

It's looking like the best way to do this is to see if the column needs to be NOT NULL on creation.

Alter table with PRIMARY KEY

I have an existing table (with records) having the ff: structure:

CREATE TABLE [dbo].[TEMP2_WORKORDER] (
[WorkOrderID] [int] IDENTITY (1, 1) NOT NULL ,
[JobType] [varchar] (3) NULL ,
[JobID] [varchar] (10) NULL ,

I want to be modify the structure to add a PRIMARY KEY to the [WorkOrderID] column.

I was using ALTER TABLE but can't get the right syntax. Please help!

ThanksALTER TABLE dbo.TEMP2_WORKORDER ADD CONSTRAINT
PK_testtable PRIMARY KEY CLUSTERED
(
WorkOrderID
)|||Thank you. It did the trick!

ALTER TABLE to Allow Null Values

I have an (Access 2003) database and I'm trying to update the schema of the database to allow null values in a column. The column already exists and currently will not allow null values. This is a distributed application (everyone has their own different MDB file) so I need to be able to modify the column through T-SQL.

My statement to try and do this is:
ALTER TABLE clients ALTER COLUMN state VARCHAR(255) NULL

However, when I view the table after running that SQL statement the table is still not allowing null values. Please don't tell me I need to drop the column before allowing null values.

Thanks,
Ryan

> I have an (Access 2003) database

Do you realize this group is about SQL Server?

AMB

|||Nope I just thought it was about T-SQL I didn't see that it was a sub-group of SQL Server. Sorry.
|||

No need to apologize.

There are differences between Access-SQL and T-SQL.

And of course, some Access applications use SQL Server for the backend (ADP Projects.) So at times, this would be the correct forumn. But for your particular question, one of the many Access forumns or NNTP groups 'might' be a better choice.

ALTER TABLE to add NOT NULL fields

I'm using the following statement to create two new fields in a table:
ALTER TABLE tblActivity ADD LOGUserID [INT] NOT NULL, LOGDATE [DATETIME] NOT
NULL
When I run it in QA, I get this error message:
ALTER TABLE only allows columns to be added that can contain nulls or have a
DEFAULT definition specified. Column 'LOGUserID' cannot be added to table
'tblActivity' because it does not allow nulls and does not specify a DEFAULT
definition.
How can I get those two fields to be created via the ALTER TABLE statement,
I can not use Enterprise Manager, since the statement that I'm using is bein
g
generated by another script to add those fields to all the tables in the
database...You need to specify a default value...
ALTER TABLE tblActivity ADD LOGUserID [INT] NOT NULL, LOGDATE [DATETIME] NOT
NULL
DEFAULT( 0 )
Make sure whatever default value you specify is meaningful to your
application; also, you could drop the DEFAULT constraint afterward...
ALTER TABLE ... DROP CONSTRAINT ...
Example...
create table t (
id int null )
insert t values( 1 )
insert t values( null )
go
alter table t add mycol int not null default( 0 )
go
sp_help t
go
alter table t drop constraint DF__t__mycol__781FBE44
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"scuba79" <scuba79@.discussions.microsoft.com> wrote in message
news:EB8AF4A5-1217-4ADD-8E41-291A1E812B46@.microsoft.com...
> I'm using the following statement to create two new fields in a table:
> ALTER TABLE tblActivity ADD LOGUserID [INT] NOT NULL, LOGDATE [DATETIME]
> NOT
> NULL
> When I run it in QA, I get this error message:
> ALTER TABLE only allows columns to be added that can contain nulls or have
> a
> DEFAULT definition specified. Column 'LOGUserID' cannot be added to table
> 'tblActivity' because it does not allow nulls and does not specify a
> DEFAULT
> definition.
> How can I get those two fields to be created via the ALTER TABLE
> statement,
> I can not use Enterprise Manager, since the statement that I'm using is
> being
> generated by another script to add those fields to all the tables in the
> database...|||To add on to Tony's response, you can specify an explicit constraint name to
make subsequent table maintenance easier:
ALTER TABLE tblActivity
ADD
LOGUserID [INT] NOT NULL
CONSTRAINT DF_tblActivity_LOGUserID DEFAULT( 0 ),
LOGDATE [DATETIME] NOT NULL
CONSTRAINT DF_tblActivity_LOGDATE DEFAULT( GETDATE() )
Hope this helps.
Dan Guzman
SQL Server MVP
"scuba79" <scuba79@.discussions.microsoft.com> wrote in message
news:EB8AF4A5-1217-4ADD-8E41-291A1E812B46@.microsoft.com...
> I'm using the following statement to create two new fields in a table:
> ALTER TABLE tblActivity ADD LOGUserID [INT] NOT NULL, LOGDATE [DATETIME]
> NOT
> NULL
> When I run it in QA, I get this error message:
> ALTER TABLE only allows columns to be added that can contain nulls or have
> a
> DEFAULT definition specified. Column 'LOGUserID' cannot be added to table
> 'tblActivity' because it does not allow nulls and does not specify a
> DEFAULT
> definition.
> How can I get those two fields to be created via the ALTER TABLE
> statement,
> I can not use Enterprise Manager, since the statement that I'm using is
> being
> generated by another script to add those fields to all the tables in the
> database...sql

ALTER Table query

Hi, I just want to know to turn this:
CREATE TABLE [dbo].[tblTierCs] (
[idTierC] [int] NOT NULL ,
[txtNoEmploye] [varchar] (50) COLLATE French_CI_AS NULL ,
[noSubDomain] [int] NOT NULL ,
[txtNameTierC] [varchar] (50) COLLATE French_CI_AS NOT NULL ,
[noOldTierC] [int] NULL ,
[noRSDTierC] [int] NULL
) ON [PRIMARY]
into this:
CREATE TABLE [dbo].[tblTierCs] (
[idTierC] [int] IDENTITY (1, 1) NOT NULL ,
[txtNoEmploye] [varchar] (50) COLLATE French_CI_AS NULL ,
[noSubDomain] [int] NOT NULL ,
[txtNameTierC] [varchar] (50) COLLATE French_CI_AS NOT NULL ,
[noOldTierC] [int] NULL ,
[noRSDTierC] [int] NULL
) ON [PRIMARY]

using an ALTER TABLE query. I tried using:
ALTER TABLE [dbo].[tblTierCs] ALTER COLUMN [idTierC] [int] IDENTITY
(1, 1) NOT NULL but it's not working. Anyone has any idea how I could
do it? Thanks.Heist (advertiseallyouwant@.hotmail.com) writes:
> Hi, I just want to know to turn this:
> CREATE TABLE [dbo].[tblTierCs] (
> [idTierC] [int] NOT NULL ,
> [txtNoEmploye] [varchar] (50) COLLATE French_CI_AS NULL ,
> [noSubDomain] [int] NOT NULL ,
> [txtNameTierC] [varchar] (50) COLLATE French_CI_AS NOT NULL ,
> [noOldTierC] [int] NULL ,
> [noRSDTierC] [int] NULL
> ) ON [PRIMARY]
> into this:
> CREATE TABLE [dbo].[tblTierCs] (
> [idTierC] [int] IDENTITY (1, 1) NOT NULL ,
> [txtNoEmploye] [varchar] (50) COLLATE French_CI_AS NULL ,
> [noSubDomain] [int] NOT NULL ,
> [txtNameTierC] [varchar] (50) COLLATE French_CI_AS NOT NULL ,
> [noOldTierC] [int] NULL ,
> [noRSDTierC] [int] NULL
> ) ON [PRIMARY]
> using an ALTER TABLE query. I tried using:
> ALTER TABLE [dbo].[tblTierCs] ALTER COLUMN [idTierC] [int] IDENTITY
> (1, 1) NOT NULL but it's not working. Anyone has any idea how I could
> do it? Thanks.

You cannot use ALTER TABLE to change a column into IDENTITY column
(except on SQL Server CE!). One way is to rename the table, create
a new and move over the data. You need to have SET IDENTITY_INSERT
on for the table when you move the data.

You can also do it in Enterprise Mangager - which will renamed and
move data behind the scenes.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||What I ended up doing is using SQL Server Entreprise Manager to
"manually" alter the table and then I used the script generator to
create a script I could then used. Thanks.sql

ALTER TABLE MyT ALTER COLUMN IdtyCol t_idty NOT NULL Identity

Hello World,
Is there a way not to drop and create the table with temp table to alter a
column as in the subject?
Thanks,
C TO
> Is there a way not to drop and create the table with temp table to alter a
> column as in the subject?
Ehm, as far as I know, the "subject" should work just fine.
Is there an error that you're getting? If so, what is it?
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com|||YOu have to recreate the column on order to create a identity column:
http://www.windowsitpro.com/Article...2080/22080.html
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"C TO" <CTO@.discussions.microsoft.com> schrieb im Newsbeitrag
news:2193C5FD-CC98-417A-90EC-751F71128B19@.microsoft.com...
> Hello World,
> Is there a way not to drop and create the table with temp table to alter a
> column as in the subject?
> Thanks,
> C TO|||
a
> Ehm, as far as I know, the "subject" should work just fine.
> Is there an error that you're getting? If so, what is it?
Woops, mixed this up with "not null".
Bugger.
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com

Alter table multiple columns?

How do I alter multiple columns with one SQL statement?

I've tried :

ALTER TABLE epcs_benefit_plan ALTER COLUMN

abc1 varchar(3) not null,

abc2 varchar(3) not null

You will have to use multiple ALTER TABLE statement to achieve this.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

ALTER TABLE MODIFY

hi!

i encountered problems when running this code in SQL Query

ALTER TABLE [dbo].[amsSchedule]
MODIFY(CutOff1 datetime NULL,
[FileName] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL)

my aim is to modify the two fields to change its data type. BUt when im trying to run this command in the query analyzer, itsays "incorrect syntax error '(' "

What do i have to do? please help me...thanks

Hi,

ALTER TABLE (...) when modifying a column only supports one change at a time.

HTH, Jens Suessmeyer.

|||

Hi Jens!

Thanks a lot for the tip...now i know what to do since u told me that alter table only supports one change at a time...

thanks a lot!

ALTER TABLE (ADD column question)

Hi All,
I need add one column in one table, but this table already have rows, and
this new column need be NOT NULL and UNIQUE CONSTRAINT, how I can add this
column with values?
Have any way to do this?
SQL Server 2005
--
ThanksHi
I cannot test it on SQL Server 2005 right now but I did some testing on SQL
Server 2000
CREATE TABLE #Test (col1 INT)
--Insert some data
INSERT INTO #Test VALUES (1)
INSERT INTO #Test VALUES (2)
--Alter table
ALTER TABLE #Test ADD col2 INT IDENTITY(1,1) NOT NULL
GO
ALTER TABLE #Test ADD CONSTRAINT my_coms UNIQUE NONCLUSTERED (col2)
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:%23xqMSNGEGHA.2708@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I need add one column in one table, but this table already have rows, and
> this new column need be NOT NULL and UNIQUE CONSTRAINT, how I can add this
> column with values?
> Have any way to do this?
> --
> SQL Server 2005
> --
> Thanks
>|||"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:<#xqMSNGEGHA.2708@.TK2MSFTNGP11.phx.gbl>...
> Hi All,
> I need add one column in one table, but this table already have rows, and
> this new column need be NOT NULL and UNIQUE CONSTRAINT, how I can add this
> column with values?
> Have any way to do this?
> --
> SQL Server 2005
> --
> Thanks
>
You can add a non-nullable column by specifying a default and then dropping
it afterwards. Example:
ALTER TABLE tbl
ADD x INTEGER NOT NULL
CONSTRAINT df_tbl_x DEFAULT (0) ;
ALTER TABLE tbl DROP CONSTRAINT df_tbl_x ;
As for adding the unique constraint, obviously you'll have to populate the
column with unique values first. You haven't told us what this data is or
where it comes from so it's hard to help you with that. Is this supposed to
be a surrogate key? Are you aware of the IDENTITY feature in SQL Server?
David Portas
SQL Server MVP
--|||As David Says, If you want to add a new column which is not null, you MUSt
provide a default non null value - otherwise what will the value for the
existing value for rows be - except null... Afterwords, you can drop the
default if you wish...
This may run long, becuase will have to re-write all of the existing rows.
Also watch your tran log..
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"ReTF" wrote:

> Hi All,
> I need add one column in one table, but this table already have rows, and
> this new column need be NOT NULL and UNIQUE CONSTRAINT, how I can add this
> column with values?
> Have any way to do this?
> --
> SQL Server 2005
> --
> Thanks
>
>|||>> I need add one column in one table, but this table already have rows, and
this new column need be NOT NULL and UNIQUE , how I can add this column wit
h values? <<
You can use an ALTER TABLE to add the columns. If you also have a
DEFAULT, you will get a single value; if not, you will get a NULL. Use
the NULL, so you can find problems after the UPDATE.
You have a serious problem because someone missed a key in their data
model. You will need to update the new column with the new key, based
on some rule that matches it to the existing key.
Once those values are in place, you then need to check to see that
there are no NULLs and that all values are unique. Then use another
ALTER TABLE to add UNIQUE NOT NULL constraints.
I did this once when merging two inventory systems that used different
part numbers for the same items. It is a pain and you will probalby
have some errors.|||> You can use an ALTER TABLE to add the columns. If you also have a
> DEFAULT, you will get a single value; if not, you will get a NULL. Use
> the NULL, so you can find problems after the UPDATE.
Not unless you use the IDENTITY property or NEWID().
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1136385177.110647.17120@.o13g2000cwo.googlegroups.com...
> You can use an ALTER TABLE to add the columns. If you also have a
> DEFAULT, you will get a single value; if not, you will get a NULL. Use
> the NULL, so you can find problems after the UPDATE.
> You have a serious problem because someone missed a key in their data
> model. You will need to update the new column with the new key, based
> on some rule that matches it to the existing key.
> Once those values are in place, you then need to check to see that
> there are no NULLs and that all values are unique. Then use another
> ALTER TABLE to add UNIQUE NOT NULL constraints.
> I did this once when merging two inventory systems that used different
> part numbers for the same items. It is a pain and you will probalby
> have some errors.
>

alter column to not null that has null values

I have to change numeric columns in 2005 table to not null and default value 0.

What I usually do is an update on the columns setting value to 0 where is null. I know you can use 'with values' when adding a column with default 0 and not null to an existing table.

Can something like this be done for altering a column or do I need to do the update?

Thanks

You need to use UPDATE first and then ALTER. ALTER TABLE table ALTER COLUMN only supports changing the type definition, collation and nullability.

alter column for every tables

I got one same tables in 10 database, and I need to amend one field from
'null' to 'not null'
Can I do it by script ' Please helpAgnes
Do the table have data?
If you don't
CREATE TABLE #Test (col1 INT NULL)
--No data
ALTER TABLE #Test ALTER COLUMN col1 INT NOT NULL
If you do have a data
CREATE TABLE t(c1 int null)
insert t values(1)
SELECT c1 INTO tA FROM t
SELECT ISNULL(c1, 0) AS c1 INTO tB FROM t
EXEC sp_help tA
EXEC sp_help tB
Note
USE database_name;
EXEC sp_rename ...
Unfortunately, sp_rename doesn't allow you to specify the database name,
rather it will run in the context of the "current" database.
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:eLUNghRMGHA.3896@.TK2MSFTNGP15.phx.gbl...
>I got one same tables in 10 database, and I need to amend one field from
>'null' to 'not null'
> Can I do it by script ' Please help
>

Alter column causing log to fill

I'm trying to simply change a column definition from Null to Not Null. It's
a multi million row table. I've already checked to make sure there are no
nulls for any rows and a default has been created for the column. My log is
set to autogrow and as the alter column colname char(6) Not Null runs the
log begins to grow. If I use no check BOL say the optimizer won't consider
the change. How can I change the nullability of a column that currently
contains no nulls without using up extreme amounts of log space?

DannyDanny (istdrs@.flash.net) writes:
> I'm trying to simply change a column definition from Null to Not Null.
> It's a multi million row table. I've already checked to make sure there
> are no nulls for any rows and a default has been created for the column.
> My log is set to autogrow and as the alter column colname char(6) Not
> Null runs the log begins to grow. If I use no check BOL say the
> optimizer won't consider the change. How can I change the nullability
> of a column that currently contains no nulls without using up extreme
> amounts of log space?

I guess the reason that the log grows, is that SQL Server needs to update
internal data structures in each page on the table. For each row there
is a bitmap that specifies which columns in the row that have the NULL
value. If you take make one column NOT NULL, then the bitmap is affected,
at least if it is not the last column in the map.

CHECK/NOCHECK has nothing to do with it, verifying the constraint does not
take any log space. (And SQL Server won't let you to say that a column is
NOT NULL without checking it, to save its own sanity.)

So the only other option to ALTER TABLE, is to take the long way: Rename
the table, create a new table, insert over, restore indexes, triggers and
constraints, move referencing foreign keys and drop the old table. When
you insert data over, you can do it in batches, and with the database
in simple recovery, the log growth will not be equally excessive. A
variation of this with even less log usage may be to bulk out the data,
and load the new table with BCP.

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

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

Alter a column to be the table identity column

i have a table
table1
column1 int not null
column2 char not nul
column3 char
i want to script a change for table1 to alter column1 to be the table identity column. not primary.you can not. You must create a new table with an identity column already defined and copy your data from the old to the new, drop your old table and rename your new table|||Just a curious question then why is that i can use the enterprise manager to do it?
the tables in question have data in them which is why i dont want to drop them.|||Just a curious question then why is that i can use the enterprise manager to do it?
the tables in question have data in them which is why i dont want to drop them.

Care to guess what Enterprise Mangler is about to do in the background?|||Care to guess what Enterprise Mangler is about to do in the background?

it is really curious that the enterprise mangler does use to do this operation. I had a whole of tables to convert to identity once and I traced the operation that EM does and there is a lot of weird stuff in their that does not pan out in the QA.

as for dudes question. you need to do a an

INSERT INTO MyNewTable
SELECT ... FROM MyOldTable

before you drop the old table.|||Well since it was the client that messed the tables up by importing them into another db then importing them back without selecting all objects, i showed them how to fix them using the EM and said to get after it. (1242 tables in all which is why i was trying to find a way of scripting it). i did use another clients db to create all the indexes and sent to them to use.
anyways thanks for all the time and help.

Alter a column to allow null or not null values if meets criteria

I'm very new working with SQL server and I'm trying to create a check
constraint that will allow the an specific field to be null only if the
result on a second field is zero and not null if this result is greater
than zero.
My boss is pushing me to implement this criteria on my SQL database
right away please Help...For example:
ALTER TABLE your_table
ADD CONSTRAINT ck_constraint_name
CHECK ((col1 IS NULL AND col2 = 0)
OR (col1 IS NOT NULL AND col2 > 0)) ;
I hope your boss intends that you apply this to a TEST system and TEST
the impact rather than go right away into production...
David Portas
SQL Server MVP
--|||Thanks David, It works perfect you are the best...|||May be easier to write a trigger that examines the inserted table to check
for the values.
"imagabo" <imagabo@.hotmail.com> wrote in message
news:1130424448.351441.168020@.g47g2000cwa.googlegroups.com...
> I'm very new working with SQL server and I'm trying to create a check
> constraint that will allow the an specific field to be null only if the
> result on a second field is zero and not null if this result is greater
> than zero.
> My boss is pushing me to implement this criteria on my SQL database
> right away please Help...
>

AllowNulls overrides default?

If I have a table column type smalldate checked to allow NULLs and also have
a default of getdate(), will this column value always be NULL if nothing is
inserted into it upon record insertion of other columns?
It seems to be. Must I delete that column if I want to not allow NULLs?
Thanks,
BrettAllowing null will not override default. See below:
CREATE TABLE #t (c1 int, c2 datetime null default getdate())
INSERT INTO #t(c1, c2) values(1, null)
INSERT INTO #t(c1) values(1)
INSERT INTO #t(c1, c2) values(1, DEFAULT)
SELECT * FROM #t
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Brett" <no@.spam.net> wrote in message news:%23fgmZWIKFHA.2880@.TK2MSFTNGP09.phx.gbl...[colo
r=darkred]
> If I have a table column type smalldate checked to allow NULLs and also ha
ve a default of
> getdate(), will this column value always be NULL if nothing is inserted in
to it upon record
> insertion of other columns?
> It seems to be. Must I delete that column if I want to not allow NULLs?
> Thanks,
> Brett
>[/color]|||Well, I always get NULLs in that column rather than the default.
Brett
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e6Bl3cIKFHA.3500@.TK2MSFTNGP14.phx.gbl...
> Allowing null will not override default. See below:
> CREATE TABLE #t (c1 int, c2 datetime null default getdate())
> INSERT INTO #t(c1, c2) values(1, null)
> INSERT INTO #t(c1) values(1)
> INSERT INTO #t(c1, c2) values(1, DEFAULT)
> SELECT * FROM #t
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Brett" <no@.spam.net> wrote in message
> news:%23fgmZWIKFHA.2880@.TK2MSFTNGP09.phx.gbl...
>|||Did you run my script?
What application are you using?
Did you Profiler trace the application to see the INSERT statement?
My guess is that the applications specifies NULL for the column in the INSER
T statement.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Brett" <no@.spam.net> wrote in message news:uo1DmpIKFHA.3788@.tk2msftngp13.phx.gbl...darkred">
> Well, I always get NULLs in that column rather than the default.
> Brett
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:e6Bl3cIKFHA.3500@.TK2MSFTNGP14.phx.gbl...
>|||Brett wrote:
> Well, I always get NULLs in that column rather than the default.
> Brett
>
Please post your complete insert statement.
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:u5elsKKKFHA.2784@.TK2MSFTNGP09.phx.gbl...
> Brett wrote:
> Please post your complete insert statement.
> --
> David Gugick
> Imceda Software
> www.imceda.com
I think this is occuring because I added this column after the table was
created. I had to allow NULLs in order to add the column. Let me research
a little more and I will post my results.
Thanks,
Brett|||Brett wrote:
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:u5elsKKKFHA.2784@.TK2MSFTNGP09.phx.gbl...
> I think this is occuring because I added this column after the table
> was created. I had to allow NULLs in order to add the column. Let
> me research a little more and I will post my results.
> Thanks,
> Brett
I just tested with an added column and I get the default value when
inserted. We'll await your post.
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:ONOXbmKKFHA.3356@.TK2MSFTNGP12.phx.gbl...
> Brett wrote:
> I just tested with an added column and I get the default value when
> inserted. We'll await your post.
>
But if you create a table, add four records then add the new column with a
default and allow nulls, what values does it have? I tried this and the
results are as follows:
Adding new records to the table I mention in this post does get the default.
Those in the table before I added this new column have NULLs. I couldn't
add the new column without allowing NULLs because of the previous records.
Thanks,
Brett|||Brett wrote:
> But if you create a table, add four records then add the new column
> with a default and allow nulls, what values does it have? I tried
> this and the results are as follows:
> Adding new records to the table I mention in this post does get the
> default. Those in the table before I added this new column have
> NULLs. I couldn't add the new column without allowing NULLs because
> of the previous records.
Ok, I think what you are saying is that the existing rows that were in
the table before you added the new column do not pick up the default.
That's expected behavior. Defaults are only applied on inserts when the
column is left out of the insert. There is no way for SQL Server to know
whether those NULLs in the existing columns were intentionally put
there. You'll need to run an update on the table and change the values
you need changed to the default.
David Gugick
Imceda Software
www.imceda.com

allowing null values

I have a flat file that I'm reading from and loading my tables with. In that file I have a column that has numbers (2000,1999,1998 and so on) and the column that they are being loaded into is defined as an INT. The issue I'm running into is that the first 50 or so rows in the flat file is empty for this column so I'm getting an error message. If I put numbers in that column in the flat file it works, if i remove them it fails. How can I allow for NULL values for my INT column on the database table?

here is the error I'm getting:

[OLE DB Destination [182]] Error: There was an error with input column "SalesYear" (7259) on input "OLE DB Destination Input" (195). The column status returned was: "The value could not be converted because of a potential loss of data.".What is the format of the flat file?

CSV? Tab delimited? Fixed width?|||

I got it to work, I had to go into the flat file connection and make some changes there on the field. Once I did that it loads correctly.

|||

IGotyourdotnet wrote:

I got it to work, I had to go into the flat file connection and make some changes there on the field. Once I did that it loads correctly.

Can you share what kind of changes you made to perhaps help others down the road?

Thanks.|||

I just changed the data type of the column. So instead of creating a derived column for it, I just changed it in the flat file connection manager.

|||I have a similar problem.Could you tell me what datatype you used in Flat file connection manager and the data type used in databse?|||

remsid wrote:

I have a similar problem.Could you tell me what datatype you used in Flat file connection manager and the data type used in databse?

Try a character data type.

allowing null values

I have a flat file that I'm reading from and loading my tables with. In that file I have a column that has numbers (2000,1999,1998 and so on) and the column that they are being loaded into is defined as an INT. The issue I'm running into is that the first 50 or so rows in the flat file is empty for this column so I'm getting an error message. If I put numbers in that column in the flat file it works, if i remove them it fails. How can I allow for NULL values for my INT column on the database table?

here is the error I'm getting:

[OLE DB Destination [182]] Error: There was an error with input column "SalesYear" (7259) on input "OLE DB Destination Input" (195). The column status returned was: "The value could not be converted because of a potential loss of data.".What is the format of the flat file?

CSV? Tab delimited? Fixed width?|||

I got it to work, I had to go into the flat file connection and make some changes there on the field. Once I did that it loads correctly.

|||

IGotyourdotnet wrote:

I got it to work, I had to go into the flat file connection and make some changes there on the field. Once I did that it loads correctly.

Can you share what kind of changes you made to perhaps help others down the road?

Thanks.|||

I just changed the data type of the column. So instead of creating a derived column for it, I just changed it in the flat file connection manager.

|||

I have a similar problem.Could you tell me what datatype you used in Flat file connection manager and the data type used in databse?|||

remsid wrote:

I have a similar problem.Could you tell me what datatype you used in Flat file connection manager and the data type used in databse?

Try a character data type.