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 script

Hello,
I want to insert a new column in a data table using code (from VB.NET)
Is there a stored procedure or does any example script exists to do this.
I need to take in account any existing keys, indexes, constraints etc which
exist on the table.
Thanks
TimDo you want to:
1) Add a column to an SQL table through .NET? or
2) Add a column to a DataTable in .NET? or
3) Add a column through a DataTable in .NET and have the change persist into
the source table in SQL?
Christian Smith
"Tim Marsden" <TM@.UK.COM> wrote in message
news:eDM073W8DHA.1428@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I want to insert a new column in a data table using code (from VB.NET)
> Is there a stored procedure or does any example script exists to do this.
> I need to take in account any existing keys, indexes, constraints etc
which
> exist on the table.
> Thanks
> Tim
>
>|||Thanks for reply
I want to add a column to a SQL server table.
If I have to do it through a .NET datatable so be it.
Thanks
"Christian Smith" <csmith@.digex.com> wrote in message
news:uB3hKMX8DHA.3200@.TK2MSFTNGP09.phx.gbl...
> Do you want to:
> 1) Add a column to an SQL table through .NET? or
> 2) Add a column to a DataTable in .NET? or
> 3) Add a column through a DataTable in .NET and have the change persist
into
> the source table in SQL?
> Christian Smith
> "Tim Marsden" <TM@.UK.COM> wrote in message
> news:eDM073W8DHA.1428@.TK2MSFTNGP12.phx.gbl...
this.
> which
>|||You should be able to use the standard SQL DDL throught the SqlCommand
class. Adding a column should not affect any of the indexes or constraints
since they could not have possibly been defined to include a column that
does not yet exist. That might be an issue for deleting a column but I
can't imagine that it would affect an addition.
Alternately, I think that there is a way to modify the table structure of a
DataTable in .NET and have it push the change up to a SQL database if they
are linked. I tried to get it to work once before but couldn't get it and
did what I needed a different way.
Sorry that is all I got.
Christian Smith
"Tim Marsden" <TM@.UK.COM> wrote in message
news:OvTUBbX8DHA.3880@.tk2msftngp13.phx.gbl...
> Thanks for reply
> I want to add a column to a SQL server table.
> If I have to do it through a .NET datatable so be it.
> Thanks
>
> "Christian Smith" <csmith@.digex.com> wrote in message
> news:uB3hKMX8DHA.3200@.TK2MSFTNGP09.phx.gbl...
> into
> this.
>|||Hi Tim,
I am reviewing you post and since I have not heard from you for some time,
I wonder whether you have solved you problem or you still have any
questions about that. I agree with our community member's suggestion, that
is to use the DDL of T-SQL as the following example:
use pubs
go
CREATE TABLE dbo.testaddcol1
(
id int NULL
) ON [PRIMARY]
go
ALTER TABLE dbo.testaddcol1 ADD col1 varchar(25) NULL
go
Hope this helps and I am waiting on your replay. Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Thanks for all the suggestions.
I have discovered there is no easy way to alter a table in code, the
complexity of indexes, permissions etc is to great.
The simple functions can be performed easily with ALTER TABLE and a few
stored procedures, he complex stuff, I have left alone.
Tim
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:N994H%23b9DHA.704@.cpmsftngxa07.phx.gbl...
> Hi Tim,
> I am reviewing you post and since I have not heard from you for some time,
> I wonder whether you have solved you problem or you still have any
> questions about that. I agree with our community member's suggestion, that
> is to use the DDL of T-SQL as the following example:
> use pubs
> go
> CREATE TABLE dbo.testaddcol1
> (
> id int NULL
> ) ON [PRIMARY]
> go
> ALTER TABLE dbo.testaddcol1 ADD col1 varchar(25) NULL
> go
> Hope this helps and I am waiting on your replay. Thanks.
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||Hello Tim,
As Christian has stated, you can use standard SQL DDL/DML thru SQL
SqlCommand Class.
You code should pretty much look like this..(modify the table/col
names etc as per your need )
Imports System
Imports System.Data
Imports System.Data.SqlClient
Try
Dim myConnString As String ="User ID=myUID;password=myPWD;Initial
Catalog=pubs;Data Source=mySQLServer"
Dim myAlterQuery As String = "ALTER TABLE dbo.testaddcol1 ADD col1
varchar(25) NULL"
Dim myConnection As New SqlConnection(myConnString)
Dim myCommand As New SqlCommand(myAlterQuery, myConnection)
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
Catch ex As Exception
MessageBox.Show(ex.ToString())
End Try
Thanks for using MSDN Newsgroup.
Vikrant Dalwale
Microsoft SQL Server Support Professional
Microsoft highly recommends to all of our customers that they visit
the http://www.microsoft.com/protect site and perform the three
straightforward steps listed to improve your computers security.
This posting is provided "AS IS" with no warranties, and confers no
rights.
--
>From: "Tim Marsden" <TM@.UK.COM>
>References: <eDM073W8DHA.1428@.TK2MSFTNGP12.phx.gbl>
<uB3hKMX8DHA.3200@.TK2MSFTNGP09.phx.gbl>
>Subject: Re: Alter table script
>Date: Thu, 12 Feb 2004 14:44:42 -0000
>Lines: 40
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
>Message-ID: <OvTUBbX8DHA.3880@.tk2msftngp13.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: host213-122-124-46.in-addr.btopenworld.com
213.122.124.46
>Path:
cpmsftngxa07.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msft
ngp13.phx.gbl
>Xref: cpmsftngxa07.phx.gbl microsoft.public.sqlserver.server:328897
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Thanks for reply
>I want to add a column to a SQL server table.
>If I have to do it through a .NET datatable so be it.
>Thanks
>
>"Christian Smith" <csmith@.digex.com> wrote in message
>news:uB3hKMX8DHA.3200@.TK2MSFTNGP09.phx.gbl...
persist
>into
VB.NET)
do
>this.
constraints etc
>
>

Alter table script

Hello,
I want to insert a new column in a data table using code (from VB.NET)
Is there a stored procedure or does any example script exists to do this.
I need to take in account any existing keys, indexes, constraints etc which
exist on the table.
Thanks
TimDo you want to:
1) Add a column to an SQL table through .NET? or
2) Add a column to a DataTable in .NET? or
3) Add a column through a DataTable in .NET and have the change persist into
the source table in SQL?
Christian Smith
"Tim Marsden" <TM@.UK.COM> wrote in message
news:eDM073W8DHA.1428@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I want to insert a new column in a data table using code (from VB.NET)
> Is there a stored procedure or does any example script exists to do this.
> I need to take in account any existing keys, indexes, constraints etc
which
> exist on the table.
> Thanks
> Tim
>
>|||Thanks for reply
I want to add a column to a SQL server table.
If I have to do it through a .NET datatable so be it.
Thanks
"Christian Smith" <csmith@.digex.com> wrote in message
news:uB3hKMX8DHA.3200@.TK2MSFTNGP09.phx.gbl...
> Do you want to:
> 1) Add a column to an SQL table through .NET? or
> 2) Add a column to a DataTable in .NET? or
> 3) Add a column through a DataTable in .NET and have the change persist
into
> the source table in SQL?
> Christian Smith
> "Tim Marsden" <TM@.UK.COM> wrote in message
> news:eDM073W8DHA.1428@.TK2MSFTNGP12.phx.gbl...
> > Hello,
> >
> > I want to insert a new column in a data table using code (from VB.NET)
> > Is there a stored procedure or does any example script exists to do
this.
> >
> > I need to take in account any existing keys, indexes, constraints etc
> which
> > exist on the table.
> >
> > Thanks
> > Tim
> >
> >
> >
>|||You should be able to use the standard SQL DDL throught the SqlCommand
class. Adding a column should not affect any of the indexes or constraints
since they could not have possibly been defined to include a column that
does not yet exist. That might be an issue for deleting a column but I
can't imagine that it would affect an addition.
Alternately, I think that there is a way to modify the table structure of a
DataTable in .NET and have it push the change up to a SQL database if they
are linked. I tried to get it to work once before but couldn't get it and
did what I needed a different way.
Sorry that is all I got. :)
Christian Smith
"Tim Marsden" <TM@.UK.COM> wrote in message
news:OvTUBbX8DHA.3880@.tk2msftngp13.phx.gbl...
> Thanks for reply
> I want to add a column to a SQL server table.
> If I have to do it through a .NET datatable so be it.
> Thanks
>
> "Christian Smith" <csmith@.digex.com> wrote in message
> news:uB3hKMX8DHA.3200@.TK2MSFTNGP09.phx.gbl...
> > Do you want to:
> >
> > 1) Add a column to an SQL table through .NET? or
> > 2) Add a column to a DataTable in .NET? or
> > 3) Add a column through a DataTable in .NET and have the change persist
> into
> > the source table in SQL?
> >
> > Christian Smith
> >
> > "Tim Marsden" <TM@.UK.COM> wrote in message
> > news:eDM073W8DHA.1428@.TK2MSFTNGP12.phx.gbl...
> > > Hello,
> > >
> > > I want to insert a new column in a data table using code (from VB.NET)
> > > Is there a stored procedure or does any example script exists to do
> this.
> > >
> > > I need to take in account any existing keys, indexes, constraints etc
> > which
> > > exist on the table.
> > >
> > > Thanks
> > > Tim
> > >
> > >
> > >
> >
> >
>|||Hi Tim,
I am reviewing you post and since I have not heard from you for some time,
I wonder whether you have solved you problem or you still have any
questions about that. I agree with our community member's suggestion, that
is to use the DDL of T-SQL as the following example:
use pubs
go
CREATE TABLE dbo.testaddcol1
(
id int NULL
) ON [PRIMARY]
go
ALTER TABLE dbo.testaddcol1 ADD col1 varchar(25) NULL
go
Hope this helps and I am waiting on your replay. Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Thanks for all the suggestions.
I have discovered there is no easy way to alter a table in code, the
complexity of indexes, permissions etc is to great.
The simple functions can be performed easily with ALTER TABLE and a few
stored procedures, he complex stuff, I have left alone.
Tim
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:N994H%23b9DHA.704@.cpmsftngxa07.phx.gbl...
> Hi Tim,
> I am reviewing you post and since I have not heard from you for some time,
> I wonder whether you have solved you problem or you still have any
> questions about that. I agree with our community member's suggestion, that
> is to use the DDL of T-SQL as the following example:
> use pubs
> go
> CREATE TABLE dbo.testaddcol1
> (
> id int NULL
> ) ON [PRIMARY]
> go
> ALTER TABLE dbo.testaddcol1 ADD col1 varchar(25) NULL
> go
> Hope this helps and I am waiting on your replay. Thanks.
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||Hello Tim,
As Christian has stated, you can use standard SQL DDL/DML thru SQL
SqlCommand Class.
You code should pretty much look like this..(modify the table/col
names etc as per your need )
Imports System
Imports System.Data
Imports System.Data.SqlClient
Try
Dim myConnString As String ="User ID=myUID;password=myPWD;Initial
Catalog=pubs;Data Source=mySQLServer"
Dim myAlterQuery As String = "ALTER TABLE dbo.testaddcol1 ADD col1
varchar(25) NULL"
Dim myConnection As New SqlConnection(myConnString)
Dim myCommand As New SqlCommand(myAlterQuery, myConnection)
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
Catch ex As Exception
MessageBox.Show(ex.ToString())
End Try
Thanks for using MSDN Newsgroup.
Vikrant Dalwale
Microsoft SQL Server Support Professional
Microsoft highly recommends to all of our customers that they visit
the http://www.microsoft.com/protect site and perform the three
straightforward steps listed to improve your computer?s security.
This posting is provided "AS IS" with no warranties, and confers no
rights.
>From: "Tim Marsden" <TM@.UK.COM>
>References: <eDM073W8DHA.1428@.TK2MSFTNGP12.phx.gbl>
<uB3hKMX8DHA.3200@.TK2MSFTNGP09.phx.gbl>
>Subject: Re: Alter table script
>Date: Thu, 12 Feb 2004 14:44:42 -0000
>Lines: 40
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
>Message-ID: <OvTUBbX8DHA.3880@.tk2msftngp13.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: host213-122-124-46.in-addr.btopenworld.com
213.122.124.46
>Path:
cpmsftngxa07.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msft
ngp13.phx.gbl
>Xref: cpmsftngxa07.phx.gbl microsoft.public.sqlserver.server:328897
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Thanks for reply
>I want to add a column to a SQL server table.
>If I have to do it through a .NET datatable so be it.
>Thanks
>
>"Christian Smith" <csmith@.digex.com> wrote in message
>news:uB3hKMX8DHA.3200@.TK2MSFTNGP09.phx.gbl...
>> Do you want to:
>> 1) Add a column to an SQL table through .NET? or
>> 2) Add a column to a DataTable in .NET? or
>> 3) Add a column through a DataTable in .NET and have the change
persist
>into
>> the source table in SQL?
>> Christian Smith
>> "Tim Marsden" <TM@.UK.COM> wrote in message
>> news:eDM073W8DHA.1428@.TK2MSFTNGP12.phx.gbl...
>> > Hello,
>> >
>> > I want to insert a new column in a data table using code (from
VB.NET)
>> > Is there a stored procedure or does any example script exists to
do
>this.
>> >
>> > I need to take in account any existing keys, indexes,
constraints etc
>> which
>> > exist on the table.
>> >
>> > Thanks
>> > Tim
>> >
>> >
>> >
>>
>
>

ALTER SP if exists in all databases

Hi,
I am trying to alter an SP in all my dB's. I am trying to loop through all
the databases ,however my script always returns false when it checks if the
SP exists (even though the SP exists)...for some reason it appears to be
still in master db even though I change db inside the cursor.
use master
declare @.AlterText = "ALTER PROCEDURE ..." --SP
declare @.dbName
declare c1 cursor for
select [name] from sysdatabases
open c1
fetch c1 into @.dbname
while(@.@.fetch_status = 0)
begin
exec('use ' + @.dbname)
if Exists(select name from sysobjects where name = 'sp_mysp' )
begin
print @.dbname
--exec (@.AlterText)
end
else
begin
print 'SP does not exist in '+@.dbname
end
fetch next from c1 into @.dbname
end
close c1
deallocate c1the scope of exec is only till the execution of that command.
"Mike" wrote:

> Hi,
> I am trying to alter an SP in all my dB's. I am trying to loop through all
> the databases ,however my script always returns false when it checks if th
e
> SP exists (even though the SP exists)...for some reason it appears to be
> still in master db even though I change db inside the cursor.
> use master
> declare @.AlterText = "ALTER PROCEDURE ..." --SP
> declare @.dbName
> declare c1 cursor for
> select [name] from sysdatabases
> open c1
> fetch c1 into @.dbname
> while(@.@.fetch_status = 0)
> begin
> exec('use ' + @.dbname)
> if Exists(select name from sysobjects where name = 'sp_mysp' )
> begin
> print @.dbname
> --exec (@.AlterText)
> end
> else
> begin
> print 'SP does not exist in '+@.dbname
> end
> fetch next from c1 into @.dbname
> end
> close c1
> deallocate c1|||Mike,
You only change the db for the exec() statement.
The current database after the exec() statement
is still master, since the USE result doesn't affect
the current database of the exec statement's caller.
A good way to do this kind of thing is to generate
all the statements you need to run by a single
query, then inspect the results and copy them by
hand and re-run them as a batch. For example,
here, you would run the output of
select
replace(replace('
use $db
goo
alter proc abc (
@.a int
) as
...
goo
','$db',quotename(name)),'goo','go')
from sysdatabases
The quotename() function protects against SQL injection
attacks from maliciously-named databases intended to cause
damage when scripts like yours are run.
You could select these strings with a cursor and execute them
also.
Be warned, however, that if your procedure does have the
name sp_something, you may run into surprises, because there
are some special name resolution rules for procedures whose
names begin with sp_. That prefix should not be used for
user-defined stored procedures.
Steve Kass
Drew Univeristy
Mike wrote:

>Hi,
>I am trying to alter an SP in all my dB's. I am trying to loop through all
>the databases ,however my script always returns false when it checks if the
>SP exists (even though the SP exists)...for some reason it appears to be
>still in master db even though I change db inside the cursor.
>use master
>declare @.AlterText = "ALTER PROCEDURE ..." --SP
>declare @.dbName
>declare c1 cursor for
>select [name] from sysdatabases
>open c1
>fetch c1 into @.dbname
>while(@.@.fetch_status = 0)
>begin
>exec('use ' + @.dbname)
>if Exists(select name from sysobjects where name = 'sp_mysp' )
> begin
> print @.dbname
> --exec (@.AlterText)
> end
>else
> begin
> print 'SP does not exist in '+@.dbname
> end
>fetch next from c1 into @.dbname
>end
>close c1
>deallocate c1
>|||By the way. Functionality you try to achieve with the script can be done by
using the following.
sp_msforeachdb 'use ? if Exists(select name from sysobjects where name =
''sp_mysp'' ) begin print ''?'' end'
Hope this helps.
--
"Omnibuzz" wrote:
> the scope of exec is only till the execution of that command.
> --
>
>
> "Mike" wrote:
>