how do I alter the column with index on it

hi,
I have a sqlserver 2000 db. I want to alter two columns
from one of the tables to not allow nulls.
but these 2 columns (one if char, and the other is
smalldatetime), one have clustered index on it, and the
other one is part of a combined index of two columns.
is the only way for the column alteration is to drop the
index first, and then re-create it later?
many thanks!
JJ
Hi,
Altering the data type of one column which has participated in an index is
not supported. If the modified column participates in an index, the only
type changes that are allowed are increasing the length of a
variable-length type (for example, VARCHAR(10) to VARCHAR(20)), changing
nullability of the column, or both. I believe you have to drop the original
index and rebuild a new index.
Thanks
Hari
MCDBA
"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:2131001c45a37$30e96600$a501280a@.phx.gbl...
> hi,
> I have a sqlserver 2000 db. I want to alter two columns
> from one of the tables to not allow nulls.
> but these 2 columns (one if char, and the other is
> smalldatetime), one have clustered index on it, and the
> other one is part of a combined index of two columns.
> is the only way for the column alteration is to drop the
> index first, and then re-create it later?
> many thanks!
> JJ
|||You can user table designer shipped with SQL server client tools or
Visuastudio to change the table.
Lishil, VSDATA Team
--
>Content-Class: urn:content-classes:message
>From: "JJ Wang" <anonymous@.discussions.microsoft.com>
>Sender: "JJ Wang" <anonymous@.discussions.microsoft.com>
>Subject: how do I alter the column with index on it
>Date: Thu, 24 Jun 2004 15:04:16 -0700
>Lines: 15
>Message-ID: <2131001c45a37$30e96600$a501280a@.phx.gbl>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="iso-8859-1"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>Thread-Index: AcRaNzDpwp7EieNJRQCcEtOkif2wyg==
>Newsgroups:
microsoft.public.sqlserver.programming,microsoft.p ublic.sqlserver.tools
>Path: cpmsftngxa10.phx.gbl
>Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.tools:24659
microsoft.public.sqlserver.programming:453163
>NNTP-Posting-Host: tk2msftngxa13.phx.gbl 10.40.1.165
>X-Tomcat-NG: microsoft.public.sqlserver.tools
>hi,
>I have a sqlserver 2000 db. I want to alter two columns
>from one of the tables to not allow nulls.
>but these 2 columns (one if char, and the other is
>smalldatetime), one have clustered index on it, and the
>other one is part of a combined index of two columns.
>is the only way for the column alteration is to drop the
>index first, and then re-create it later?
>many thanks!
>JJ
>
|||thanks Lishil. I know the tool to alter it, I just want
to know whether I have to drop the index first before I
alter the column. please see my privious email for detail.
thanks.
JJ
>--Original Message--
>You can user table designer shipped with SQL server
client tools or
>Visuastudio to change the table.
>Lishil, VSDATA Team
>
>--
>microsoft.public.sqlserver.programming,microsoft. public.sq
lserver.tools[vbcol=seagreen]
microsoft.public.sqlserver.tools:24659
>microsoft.public.sqlserver.programming:453163
>.
>
|||thanks Hari. I fear so too. oh well...
thanks.
JJ
>--Original Message--
>Hi,
>Altering the data type of one column which has
participated in an index is
>not supported. If the modified column participates in an
index, the only
>type changes that are allowed are increasing the length
of a
>variable-length type (for example, VARCHAR(10) to VARCHAR
(20)), changing
>nullability of the column, or both. I believe you have to
drop the original
>index and rebuild a new index.
>
>--
>Thanks
>Hari
>MCDBA
>"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2131001c45a37$30e96600$a501280a@.phx.gbl...
>
>.
>
|||Ok, you already get answer from hari_prasad_k@.hotmail.com.
You do need to drop index in you case.
Lishi Liu, VSData team
--
>Content-Class: urn:content-classes:message
>From: "JJ Wang" <anonymous@.discussions.microsoft.com>
>Sender: "JJ Wang" <anonymous@.discussions.microsoft.com>
>References: <2131001c45a37$30e96600$a501280a@.phx.gbl>
<$AXF2GuXEHA.2352@.cpmsftngxa06.phx.gbl>
>Subject: RE: how do I alter the column with index on it
>Date: Thu, 8 Jul 2004 18:24:14 -0700
>Lines: 61
>Message-ID: <2962a01c46553$72445bb0$a501280a@.phx.gbl>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="iso-8859-1"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>Thread-Index: AcRlU3JEKPTFyjEUT129z6+ojcBcmg==
>Newsgroups:
microsoft.public.sqlserver.tools,microsoft.public. sqlserver.programming
>Path: cpmsftngxa06.phx.gbl
>Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.programming:456466
microsoft.public.sqlserver.tools:24467
>NNTP-Posting-Host: tk2msftngxa13.phx.gbl 10.40.1.165
>X-Tomcat-NG: microsoft.public.sqlserver.tools
>thanks Lishil. I know the tool to alter it, I just want
>to know whether I have to drop the index first before I
>alter the column. please see my privious email for detail.
>thanks.
>JJ
>client tools or
>lserver.tools
>microsoft.public.sqlserver.tools:24659
>

How check if constraint is enabled/disabled?

How do I check if a constraint is disabled or enabled?
Constraints have been enabled and disabled by
ALTER TABLE x NOCHECK/CHECK CONSTRAINTS ALL
but I like to see the status of the constraints.check the sysconstraints table :)|||You can use the OBJECTPROPERTY() function for that.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mikael Be" <MikaelBe@.discussions.microsoft.com> wrote in message
news:7FB01968-C939-41FE-B327-EA5D9E6C6385@.microsoft.com...
> How do I check if a constraint is disabled or enabled?
> Constraints have been enabled and disabled by
> ALTER TABLE x NOCHECK/CHECK CONSTRAINTS ALL
> but I like to see the status of the constraints.|||Sure check out OBJECTPROPERTY in BOL, specifically the CnstIsDisabled
property name.
"Mikael Be" wrote:
> How do I check if a constraint is disabled or enabled?
> Constraints have been enabled and disabled by
> ALTER TABLE x NOCHECK/CHECK CONSTRAINTS ALL
> but I like to see the status of the constraints.|||Thank you guys, the OBJECTPROPERTY helped me.
I only needed this simple information.
select Constr = fk.name,Status = CASE OBJECTPROPERTY ( object_id(fk.name),'CnstIsDisabled')
WHEN 1 THEN 'Disabled'
ELSE 'Enabled'
END
from (select name from sysobjects
where xtype = 'F') as fk
order by name
/Mikael Be
"Mikael Be" wrote:
> How do I check if a constraint is disabled or enabled?
> Constraints have been enabled and disabled by
> ALTER TABLE x NOCHECK/CHECK CONSTRAINTS ALL
> but I like to see the status of the constraints.

How can I use create or alter statements with ODBC and Microsoft Access ODBC Driver (*mdb)?

Hi,

I am using VB.NET 2005 and set up an ODBC connection via ODBC.ODBCConnection to a MDB database. Therefor, I use the "Microsoft Access ODBC Driver (*.mdb)".

When I set up a ODBCCommand like "ALTER DATABASE..." or "CREATE TABLE..." and issue it with the com.ExecuteNonQuery() command, I get an error from ODBC driver, that a SQL statement has to begin with SELECT, INSERT, UPDATE or DELETE.

How can I use DDL statements via ODBC?

I would appreciate if you could help me to use ODBC for that - no OLE, no ADO.

Thanks for help!

Regards,

Stefan D.

Once upon a time (if I recall correctly -and even if I don't it makes for a good 'story')...

OBDC (Open DataBase Connectivity) was established as a result of a consortium of product vendors working to find a common API to allow users using a common 'language' (ANSI SQL) to access data in any vendors database. So far, the supported ANSI SQL is limited to SELECT INSERT, UPDATE, DELETE statements.

Each vendor has proprietary extensions to the SQL Language to create/define/change/remove the database objects.

Short version -you can't.

Your choices are to use the vendor specific database management tool to create the tables, and then use ODBC to handle the data operations,

(OR), embrace ADO, or even OLEDB.

|||

Ah, that's a pity, but thanks for your answer!

So then I have to use ADO. Is there a way to get access to any database format that is stored in files (like .mdb) without having to install a driver for it on a standard windows XP installation? For OLE to mdb I have to install the JET-Engine, am I right?

Thanks for answer again!

|||

This may be wrong, but, I think that the full JET functionality is included in MDAC 2.x, and that MDAC 2.x is automatically included with and installed with Windows OS (since Win 2000/XP) and service packs.

|||Alternatively, you might also consider using SQL Server Express or SQL Server CE as your backend database and then using the System.Data.SqlClient as your provider API. This would also be beneficial in that your developement experience would be greatly simplified with the data scenario support that is provided in Visual Studio 2005.|||

Well, if it is true that JET is installed with Windows service pack, then I wonder why I get an ISAM not found error...

|||

My software is already supporting MySQL and Oracle back ends (Oracle is in its XE 10g version also free) with a single class working with ODBC. That's the reason why I wanted to use ODBC to access the mdb, too.

In addition to the two server-based solutions now I want a third, file-based solution which is slim enough for a standard business laptop (which are server-based solutions definitely not) and does not need any extra installation effort (like an extra driver or database server).

So, thanks for your reply, but I still insist on a file-based solution. Any ideas?

|||

Stephan,

You might explore SQL Server CE (or maybe it's now called 'Mobile'). Very small footprint, very small demand on resources -and yet suprisingly a lot of functionality.

And as i indicated before, the JET drivers are automatically installed on Windows 2000 and above.

|||

ISAM not found errors are often a result of incorrect or improperly formed connection strings.

Check this thread for one type of problem.

|||

That's the reason why I wanted to use ODBC to access the mdb, too.

You CAN use ODBC to access the mdb file -SELECT, INSERT, UPDATE, DELETE works the same as in MySQL or ORACLE.

You just can't use ODBC to CREATE TABLES, etc.

You have to use JET (ADO / OLEDB) for that.

|||

Great, thank you!

After you've convinced me that Jet is really installed in standard windows installation, I came closer to my error. Didn't know that the OLE driver does not like additionally specified parameters in that string than those it needs.

Now it works, thanks!

This problem is thus resolved.

Thanks much to all for your help!

|||

After you've convinced me that the driver is really installed in an original windows installation, I came close to the real error. My connection string was indeed malformulated.

Thanks to all for their help!

This issue is thus resolved!

How can I use create or alter statements with ODBC and Microsoft Access ODBC Driver (*mdb)?

Hi,

I am using VB.NET 2005 and set up an ODBC connection via ODBC.ODBCConnection to a MDB database. Therefor, I use the "Microsoft Access ODBC Driver (*.mdb)".

When I set up a ODBCCommand like "ALTER DATABASE..." or "CREATE TABLE..." and issue it with the com.ExecuteNonQuery() command, I get an error from ODBC driver, that a SQL statement has to begin with SELECT, INSERT, UPDATE or DELETE.

How can I use DDL statements via ODBC?

I would appreciate if you could help me to use ODBC for that - no OLE, no ADO.

Thanks for help!

Regards,

Stefan D.

Once upon a time (if I recall correctly -and even if I don't it makes for a good 'story')...

OBDC (Open DataBase Connectivity) was established as a result of a consortium of product vendors working to find a common API to allow users using a common 'language' (ANSI SQL) to access data in any vendors database. So far, the supported ANSI SQL is limited to SELECT INSERT, UPDATE, DELETE statements.

Each vendor has proprietary extensions to the SQL Language to create/define/change/remove the database objects.

Short version -you can't.

Your choices are to use the vendor specific database management tool to create the tables, and then use ODBC to handle the data operations,

(OR), embrace ADO, or even OLEDB.

|||

Ah, that's a pity, but thanks for your answer!

So then I have to use ADO. Is there a way to get access to any database format that is stored in files (like .mdb) without having to install a driver for it on a standard windows XP installation? For OLE to mdb I have to install the JET-Engine, am I right?

Thanks for answer again!

|||

This may be wrong, but, I think that the full JET functionality is included in MDAC 2.x, and that MDAC 2.x is automatically included with and installed with Windows OS (since Win 2000/XP) and service packs.

|||Alternatively, you might also consider using SQL Server Express or SQL Server CE as your backend database and then using the System.Data.SqlClient as your provider API. This would also be beneficial in that your developement experience would be greatly simplified with the data scenario support that is provided in Visual Studio 2005.|||

Well, if it is true that JET is installed with Windows service pack, then I wonder why I get an ISAM not found error...

|||

My software is already supporting MySQL and Oracle back ends (Oracle is in its XE 10g version also free) with a single class working with ODBC. That's the reason why I wanted to use ODBC to access the mdb, too.

In addition to the two server-based solutions now I want a third, file-based solution which is slim enough for a standard business laptop (which are server-based solutions definitely not) and does not need any extra installation effort (like an extra driver or database server).

So, thanks for your reply, but I still insist on a file-based solution. Any ideas?

|||

Stephan,

You might explore SQL Server CE (or maybe it's now called 'Mobile'). Very small footprint, very small demand on resources -and yet suprisingly a lot of functionality.

And as i indicated before, the JET drivers are automatically installed on Windows 2000 and above.

|||

ISAM not found errors are often a result of incorrect or improperly formed connection strings.

Check this thread for one type of problem.

|||

That's the reason why I wanted to use ODBC to access the mdb, too.

You CAN use ODBC to access the mdb file -SELECT, INSERT, UPDATE, DELETE works the same as in MySQL or ORACLE.

You just can't use ODBC to CREATE TABLES, etc.

You have to use JET (ADO / OLEDB) for that.

|||

Great, thank you!

After you've convinced me that Jet is really installed in standard windows installation, I came closer to my error. Didn't know that the OLE driver does not like additionally specified parameters in that string than those it needs.

Now it works, thanks!

This problem is thus resolved.

Thanks much to all for your help!

|||

After you've convinced me that the driver is really installed in an original windows installation, I came close to the real error. My connection string was indeed malformulated.

Thanks to all for their help!

This issue is thus resolved!