How to modify a column data type
-
Hi Again,
I want to change a column's data type from my VB Client using ADO.
In Oracle I could do that using "Modify" in Alter table statement.
But, any equivalent available for SQL Server???
I dont want to use the round trip method of creating an alias table without the column and then adding the column with right datatype using ALTER and then again renaming the table.
Instead I want to do every thing in one or two statements as I have to do the same at several places in my application like that. And more over I wanted to know whether SQL provides that kind of option at all.
I am using SQL Server 6.5 version.
Any suggestions?????????
Thanx in advance
-
Not sure if this is exactly what you're asking for - any DDL can be sent to SQL from ADO using the execute method.
Andy
-
I don't have the docs for SQL 6.5, but SQL 7 and above, DDL would be as follows:
ALTER TABLE <Table Name>
ALTER COLUMN <Column Name> <Data Type> <Nullability>
This can be passed to SQL Server like any other SQL query.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley -
Thanks Brian,
But,
No luck ALTER TABLE <Table Name>
ALTER COLUMN <Column Name> <Data Type> <Nullability>
is not working in SQL Server 6.5. Seems its only works in 7.0 version.
??????????????? whats the solution
-
Upgrade to SQL 2000 (you can run in sql 65 compatability mode until you upgrade the app).
However, this will not get the change you need. In SQL 6.5, you have to rebuild the table. Easiest way,
1.rename the current table (I think you can do this).
2.Create the new table
3. Copy the data
If you cannot rename the table (sorry, do not have a copy of v6.5 here). Then you should make a table that looks like the current table, copy all the data and drop the current table. Now, create a new table and copy the data back.
Note: You probably will need to recompile all views and stored procedures that reference this table. Not an easy task.
If you can spare some $$, I'd purchase a copy of Embarkadero's DBArtisan which will automate this process and create a script for you.
Steve Jones
-
Thanx Jones,
But, I am developing an application for 6.5. So, I may not be able to upgrade.
And writing the script part I can do but had to handle dynamically through ADO and VB Client because the field names and data types will be selected by the user in runtime.
Anyway I knew the procedure for replicating a table by renaming and creating and then inserting. I was just wondering whether any command is available in SQL like in Oracle for that.
Anyway Thank you all for suggestions and comments
-
No luck Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply