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 problem?

in a stored procedure , i've the following coding
...
create table tbl (
a int, b int
)
insert into tbl values (1, 2)
alter table tbl drop column b
select * from tbl
...
sql server returns "Column name or number of supplied values does not match
table definition."
Could anyone help me please.the table is temp table
"Win" <aaa@.aaa.com> wrote in message
news:#XRiRh1OGHA.2124@.TK2MSFTNGP14.phx.gbl...
> in a stored procedure , i've the following coding
> ...
> create table tbl (
> a int, b int
> )
> insert into tbl values (1, 2)
> alter table tbl drop column b
> select * from tbl
> ...
> sql server returns "Column name or number of supplied values does not
match
> table definition."
> Could anyone help me please.
>|||Win
create table #tbl (
a int, b int
)
insert into #tbl values (1, 2)
GO
alter table #tbl drop column b
GO
select * from #tbl
drop table #tbl
"Win" <aaa@.aaa.com> wrote in message
news:Ohom9m1OGHA.3944@.tk2msftngp13.phx.gbl...
> the table is temp table
> "Win" <aaa@.aaa.com> wrote in message
> news:#XRiRh1OGHA.2124@.TK2MSFTNGP14.phx.gbl...
> match
>|||Cannot alter table '#tbl' because this table does not exist in database
'abc'.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ekwECK2OGHA.3728@.tk2msftngp13.phx.gbl...
> Win
> create table #tbl (
> a int, b int
> )
> insert into #tbl values (1, 2)
> GO
> alter table #tbl drop column b
> GO
> select * from #tbl
> drop table #tbl
>
> "Win" <aaa@.aaa.com> wrote in message
> news:Ohom9m1OGHA.3944@.tk2msftngp13.phx.gbl...
>|||Win
On my machine it works file. What version are you using?
"Win" <aaa@.aaa.com> wrote in message
news:OD1%23CO2OGHA.1696@.TK2MSFTNGP14.phx.gbl...
> Cannot alter table '#tbl' because this table does not exist in database
> 'abc'.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ekwECK2OGHA.3728@.tk2msftngp13.phx.gbl...
>|||The procedure is parsed as a unit, before any execution has taken place. So,
when the SELECT is
parsed, the ALTER hasn't occurred yet, hence the error message. This is expe
cted. If you post the
logic behind doing this, someone might provide an alternative, or you can us
e dynamic SQL
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Win" <aaa@.aaa.com> wrote in message news:%23XRiRh1OGHA.2124@.TK2MSFTNGP14.phx.gbl...darkred">
> in a stored procedure , i've the following coding
> ...
> create table tbl (
> a int, b int
> )
> insert into tbl values (1, 2)
> alter table tbl drop column b
> select * from tbl
> ...
> sql server returns "Column name or number of supplied values does not matc
h
> table definition."
> Could anyone help me please.
>|||my coding is inside a stored procedure
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uToyZl2OGHA.428@.tk2msftngp13.phx.gbl...
> Win
> On my machine it works file. What version are you using?
>
> "Win" <aaa@.aaa.com> wrote in message
> news:OD1%23CO2OGHA.1696@.TK2MSFTNGP14.phx.gbl...
not
>

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

alphanumeric sort on inconsistent values

I am running SQL Server 2000 and must sort on an alphanumeric field. There
is no pattern consistency for the values in the column (let's call it FILENUM
varchar(30)). I am having a difficult time coming up with a solution.
Given a set of filenumbers:
1dd
1
1x4
1cc2
1110-345-720a3
11
380-41-3a
10
Should be sorted as:
1
1cc2
1dd
1x4
10
11
380-41-3a
1110-345-720a3
any assistance would be greatly appreciated.
Thanks,
Tracy
assuming the name of your table is table1, you can execute the following query:
select * from table1 order by ascii(filenum)
"Tracy R via droptable.com" wrote:

> I am running SQL Server 2000 and must sort on an alphanumeric field. There
> is no pattern consistency for the values in the column (let's call it FILENUM
> varchar(30)). I am having a difficult time coming up with a solution.
> Given a set of filenumbers:
> 1dd
> 1
> 1x4
> 1cc2
> 1110-345-720a3
> 11
> 380-41-3a
> 10
> Should be sorted as:
> 1
> 1cc2
> 1dd
> 1x4
> 10
> 11
> 380-41-3a
> 1110-345-720a3
> any assistance would be greatly appreciated.
>
> Thanks,
> Tracy
>

alphanumeric sort on inconsistent values

I am running SQL Server 2000 and must sort on an alphanumeric field. There
is no pattern consistency for the values in the column (let's call it FILENUM
varchar(30)). I am having a difficult time coming up with a solution.
Given a set of filenumbers:
1dd
1
1x4
1cc2
1110-345-720a3
11
380-41-3a
10
Should be sorted as:
1
1cc2
1dd
1x4
10
11
380-41-3a
1110-345-720a3
any assistance would be greatly appreciated.
Thanks,
Tracyassuming the name of your table is table1, you can execute the following query:
select * from table1 order by ascii(filenum)
"Tracy R via SQLMonster.com" wrote:
> I am running SQL Server 2000 and must sort on an alphanumeric field. There
> is no pattern consistency for the values in the column (let's call it FILENUM
> varchar(30)). I am having a difficult time coming up with a solution.
> Given a set of filenumbers:
> 1dd
> 1
> 1x4
> 1cc2
> 1110-345-720a3
> 11
> 380-41-3a
> 10
> Should be sorted as:
> 1
> 1cc2
> 1dd
> 1x4
> 10
> 11
> 380-41-3a
> 1110-345-720a3
> any assistance would be greatly appreciated.
>
> Thanks,
> Tracy
>

alphanumeric sort on inconsistent values

I am running SQL Server 2000 and must sort on an alphanumeric field. There
is no pattern consistency for the values in the column (let's call it FILENU
M
varchar(30)). I am having a difficult time coming up with a solution.
Given a set of filenumbers:
1dd
1
1x4
1cc2
1110-345-720a3
11
380-41-3a
10
Should be sorted as:
1
1cc2
1dd
1x4
10
11
380-41-3a
1110-345-720a3
any assistance would be greatly appreciated.
Thanks,
Tracyassuming the name of your table is table1, you can execute the following que
ry:
select * from table1 order by ascii(filenum)
"Tracy R via droptable.com" wrote:

> I am running SQL Server 2000 and must sort on an alphanumeric field. Ther
e
> is no pattern consistency for the values in the column (let's call it FILE
NUM
> varchar(30)). I am having a difficult time coming up with a solution.
> Given a set of filenumbers:
> 1dd
> 1
> 1x4
> 1cc2
> 1110-345-720a3
> 11
> 380-41-3a
> 10
> Should be sorted as:
> 1
> 1cc2
> 1dd
> 1x4
> 10
> 11
> 380-41-3a
> 1110-345-720a3
> any assistance would be greatly appreciated.
>
> Thanks,
> Tracy
>

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.

Allow NULLS with the lookup transformation?

Hi,

I just wanted to know if there is any way to Allow Null values while doing a lookup on a table in SSIS.

Let me elaborate the situation...

I have a flat file source that has a field called 'code'. I want to lookup in a code table to see if the code in the file is a valid code but the flat file may contain a NULL value as a 'code' (i.e. zero length string which treated as NULL by my package).

My problem is, the SSIS package tries to search for the NULL in the table and the lookup fails and an error is logged as per the business logic but actually NULL is also an acceptable value and the error should not be logged.

I tried inserting a NULL value in the lookup column but that doesn't work. I am not sure but I think I have read somewhere that two null values cannot be compared for equality. I cannot use conditional split to check the null value because I have to use a large number of lookups and a conditional split everywhere will mess up the things.

Is there any way to solve this problem?

try doing an Ignore Failure in the lookup component.|||

Ignore failure will ignore this error for all the values coming in the feed.

I want to ignore only in the case of NULL values. and for values other than NULL (and invalid), it should be redirected to the error output where the error will be logged.

|||Use a conditional split right before the lookup to redirect NULL values. Then after the lookup, you can join the two flows back together.|||Replace the NULL value with an empty string prior to the lookup, add an empty string to your lookup source, then replace the empty string with NULL after the lookup. Or use some other value to represent NULLs, if an empty string won't work. A little kludgy, but it works.|||

yes... It will be a very complex solution. I would probably do that only.

I am just looking for a graceful way to handle this scenario. Please notify me if you come across some links adressing similar issues.

'Allow null values' causes report to run automatically

Hi
I have a long running report. The report is based on a SQL Server 2005
stored procedure, with a single Integer parameter. I want to allow the
user to choose NULL for the parameter, but I don't want the report to
run without the user clicking 'View Report'. But I am finding (from
Report Parameters) when I choose 'Allow null values' there is no
default value of 'None'. I don't want to specify a default value,
because this causes the report to execute immediately.
Has anybody come across this?
cheers,
TJWhat you need, and what would be nice, is a default option of None
(not Null) which doesn't allow the report to run until the user
selects a value. Unfortunatley, the only default options you have
are: Non-queried, From query and Null.
The simplest solution is probably to set a Non-queried default value
that is invalid in your report. This will till cause your report to
run but will return an empty result set. If it's a very simple report
you could then set the visibility of your elements to not show, or
possibly show "Please select a value", if this default invalid value
is given. Otherwise you'll get an empty looking report or, worse yet,
an error; either result would probably be confusing to the user.
Matt Penner
On Jan 2, 7:28 pm, TJ <newsgroups...@.gmail.com> wrote:
> Hi
> I have a long running report. The report is based on a SQL Server 2005
> stored procedure, with a single Integer parameter. I want to allow the
> user to choose NULL for the parameter, but I don't want the report to
> run without the user clicking 'View Report'. But I am finding (from
> Report Parameters) when I choose 'Allow null values' there is no
> default value of 'None'. I don't want to specify a default value,
> because this causes the report to execute immediately.
> Has anybody come across this?
> cheers,
> TJ

Allow Null values

Hello experts,

I’ve got I little problem how the Null values are stored in the Cube.

I’ve got a Dimension what contains an Attribute e.g. “MyPerfectDate”.

Sometimes this Attribute has a Null value.

So far so long no problems.

Now if I try to build a Report with SSRS and found out, that SSAS stored the NULL values as blank or “empty” and change the datatype.

I tried to work with the DataItem and set NullProcesing to “Preverse” or “UnknownMember” but this doesn’t resolve my Problem.

Have someone another idea?

Thanks a lot

Alex

Hello,

Have nobody any idea?

This problem sounds not so complex to me, I think I forgot an option.

Could it be that I forget some information that you couldn’t help me?

Yours sincerely,

Alex

|||

We had a similar problem with a string value containing nulls and empty values in RS. Our solution was to go back to the cube and change the value to "". Since, yours is a date you might want to set the date to 1/1/1753.

|||

Hello phuhn,

Thanks a lot for the response. Today I thought about this option too.

You never found a better solution? I don’t know why my solution didn’t work, because there are some others threads and there is the solution to change the DataItem….

Best regards,

Alex

Allow Null values

Hello experts,

I’ve got I little problem how the Null values are stored in the Cube.

I’ve got a Dimension what contains an Attribute e.g. “MyPerfectDate”.

Sometimes this Attribute has a Null value.

So far so long no problems.

Now if I try to build a Report with SSRS and found out, that SSAS stored the NULL values as blank or “empty” and change the datatype.

I tried to work with the DataItem and set NullProcesing to “Preverse” or “UnknownMember” but this doesn’t resolve my Problem.

Have someone another idea?

Thanks a lot

Alex

Hello,

Have nobody any idea?

This problem sounds not so complex to me, I think I forgot an option.

Could it be that I forget some information that you couldn’t help me?

Yours sincerely,

Alex

|||

We had a similar problem with a string value containing nulls and empty values in RS. Our solution was to go back to the cube and change the value to "". Since, yours is a date you might want to set the date to 1/1/1753.

|||

Hello phuhn,

Thanks a lot for the response. Today I thought about this option too.

You never found a better solution? I don’t know why my solution didn’t work, because there are some others threads and there is the solution to change the DataItem….

Best regards,

Alex

Allow Null Value

Hi,
A given column of my Report (reporting services 2005) contains clients or
null values. I want the user to be able to chose one or more clients for the
parameter, and than show only those records of this client. I also want to
be able to chose "NULL", which will show the records with the null-values.
Also: When the user selects "(select all)" it should show not only those
with a client, but also those with a null value.
How do I have to do this? I tried with adding a Null-value row in my
parameter DataSet, but that didn't work. I also can't set the "Allow Null
Value" for my parameter ("The properties of the currently selected item are
not valid. Please correct all errors before continuing").
Does anybody know how to do this?
Thanks a lot in advance,
Pieterset your data source for the client list to:
select clientId, clientName (or whatever it is)
from clientTable
union select 0, 'All Clients'
union select -1, 'Blank Client'
order by 1
you query needs to take into account the magic values '0' and '-1'.
-T
"Pieter Coucke" <pietercoucke@.hotmail.com> wrote in message
news:uc7DB%23XfGHA.5104@.TK2MSFTNGP04.phx.gbl...
> Hi,
> A given column of my Report (reporting services 2005) contains clients or
> null values. I want the user to be able to chose one or more clients for
> the parameter, and than show only those records of this client. I also
> want to be able to chose "NULL", which will show the records with the
> null-values. Also: When the user selects "(select all)" it should show not
> only those with a client, but also those with a null value.
> How do I have to do this? I tried with adding a Null-value row in my
> parameter DataSet, but that didn't work. I also can't set the "Allow Null
> Value" for my parameter ("The properties of the currently selected item
> are not valid. Please correct all errors before continuing").
> Does anybody know how to do this?
> Thanks a lot in advance,
> Pieter
>

allow null on a multi value parameter

Hi,
I don't seem to be able to set "allow null" on a multi value parameter.
I have this parameter with default values from a stored procedure, I want
the report to be able to generate without having something selected from the
multi value parameter. How is this possible?
Bjorn.multi value parameter does not allow NULL.
Just add an empty string to your select statement in SP.
Bjorn wrote:
> Hi,
> I don't seem to be able to set "allow null" on a multi value parameter.
> I have this parameter with default values from a stored procedure, I want
> the report to be able to generate without having something selected from the
> multi value parameter. How is this possible?
> Bjorn.

Allow Blank Values for Strings

I have a data set in Reporting Services Report (2003)

if (@.prmAudit = 0 or @.prmCellType = 0)
BEGIN
select top 5000 * from NECCCUSTAUDIT.dbo.vw_cust_audit_billing_problem b where datediff(d,@.prmStartDate,b.fld_date_time) >=0 and datediff(d,@.prmEndDate,b.fld_date_time) <=0 and b.fld_problem_type like @.prmType + '%'
END
if (@.prmAudit = 1 or @.prmType = 0)
BEGIN
select top 5000 * from NECCCUSTAUDIT.dbo.vw_cust_audit_cell_problem b
where (DATEDIFF(d, fld_date_time, @.prmStartDate) <= 0)
AND (DATEDIFF(d, fld_date_time, @.prmEndDate) >= 0) and fld_problem_type not like 'A%'
AND b.fld_problem_type like @.prmCellType + '%'

END

--

When I preview my report

Enter the Fist Date, Last Date, prmAudit, prmType

>> I do not select anything for prmCellType

There is no data seen, it is totally blank.

>> When I select FistDate, LastDate, prmAudit, prmType and prmCellType

It works all fine.

Question: I want my query to accept @.prmType as blank, so I went to Reports-->ReportParameter and allowed for Blank. This parameter is a string.

Still my Report does not do what I want?

Please could somebody help me correct my query or give a better solution.

Thank you,

Does the "allow blank" option work in preview, but not when republishing the report on the report server?

In that case, you first have to delete the report from the report server and then publish again. If you just republish over an existing report, parameter settings will be merged with the previous parameter settings (and in this case, the allow blank option will be ignored).

-- Robert