Modify column order

Hello,
I currently have a table with 2 primary keys, and 6
columsn following that. I want to add 2 new columns, that
will also be primary keys. I want to insert these new
columns in position 3 and 4.
Is there a way to do this without dropping the table and
recreating it in the correct column order ?
eg:
Table : dbo.TMCLASS has columns as follows
COUNTRYCODE (pk)
CLASS (pk)
EFFECTIVEDATE
GOODSSERVICES
INTERNATIONALCLASS
ASSOCIATEDCLASSES
CLASSHEADING
CLASSNOTES
PROPERTYTYPE
I want a new structure (PROPERTYTYE and SEQUENCENO added)
Table : dbo.TMCLASS
COUNTRYCODE (pk)
CLASS (pk)
PROPERTYTYPE (pk)
SEQUENCENO (pk)
EFFECTIVEDATE
GOODSSERVICES
INTERNATIONALCLASS
ASSOCIATEDCLASSES
CLASSHEADING
CLASSNOTES
PROPERTYTYPE
I am tryign to avoid dropping and recreating the table as
there is data at client sites.
Thanks,
AlisonDon't think so. I also don't know why you would want to. I see a few
people asking for this aesthetic change. If you qualify all your statements
instead of using * then column order is irrelevant. May look prettier in EM
or other tool but that is about it I thnk.
--
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Alison Bell" <abell@.cpaglobal.com> wrote in message
news:047f01c36552$898f9d40$a601280a@.phx.gbl...
> Hello,
> I currently have a table with 2 primary keys, and 6
> columsn following that. I want to add 2 new columns, that
> will also be primary keys. I want to insert these new
> columns in position 3 and 4.
> Is there a way to do this without dropping the table and
> recreating it in the correct column order ?
> eg:
> Table : dbo.TMCLASS has columns as follows
> COUNTRYCODE (pk)
> CLASS (pk)
> EFFECTIVEDATE
> GOODSSERVICES
> INTERNATIONALCLASS
> ASSOCIATEDCLASSES
> CLASSHEADING
> CLASSNOTES
> PROPERTYTYPE
> I want a new structure (PROPERTYTYE and SEQUENCENO added)
> Table : dbo.TMCLASS
> COUNTRYCODE (pk)
> CLASS (pk)
> PROPERTYTYPE (pk)
> SEQUENCENO (pk)
> EFFECTIVEDATE
> GOODSSERVICES
> INTERNATIONALCLASS
> ASSOCIATEDCLASSES
> CLASSHEADING
> CLASSNOTES
> PROPERTYTYPE
> I am tryign to avoid dropping and recreating the table as
> there is data at client sites.
> Thanks,
> Alison
>|||I don't think there is a way either. Reason for wanting
the specific order is to match "alltables" script
generated by ERwin for data transfer at a later date. It
just means modifying the generated script each time we
make a DB change, as ERwin places the columns in pos 3
and 4 (non modifyable).
Thanks,
Alison
>--Original Message--
>Don't think so. I also don't know why you would want
to. I see a few
>people asking for this aesthetic change. If you qualify
all your statements
>instead of using * then column order is irrelevant. May
look prettier in EM
>or other tool but that is about it I thnk.|||Problem being I must do this via SQL scripting. It is
part of an upgrade script being shipped to client sites.
>--Original Message--
>easiest way is use EM's (pet's tool) table designer. Sql
>2000 will drop and create with your requirements. You
>don't have to do anything.
>>--Original Message--
>>I don't think there is a way either. Reason for
wanting
>>the specific order is to match "alltables" script
>>generated by ERwin for data transfer at a later date.
It
>>just means modifying the generated script each time we
>>make a DB change, as ERwin places the columns in pos 3
>>and 4 (non modifyable).
>>Thanks,
>>Alison
>>--Original Message--
>>Don't think so. I also don't know why you would want
>>to. I see a few
>>people asking for this aesthetic change. If you
qualify
>>all your statements
>>instead of using * then column order is irrelevant.
May
>>look prettier in EM
>>or other tool but that is about it I thnk.
>>.
>.
>|||Then you have to re-create the table (this is what EM does under covers anyhow). There is no
functionality in TSQL (or ANSI SQL, as I remember) to change column order.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Alison Bell" <abell@.cpaglobal.com> wrote in message news:0fe901c366bd$edc2e210$a001280a@.phx.gbl...
> Problem being I must do this via SQL scripting. It is
> part of an upgrade script being shipped to client sites.
> >--Original Message--
> >easiest way is use EM's (pet's tool) table designer. Sql
> >2000 will drop and create with your requirements. You
> >don't have to do anything.
> >
> >>--Original Message--
> >>I don't think there is a way either. Reason for
> wanting
> >>the specific order is to match "alltables" script
> >>generated by ERwin for data transfer at a later date.
> It
> >>just means modifying the generated script each time we
> >>make a DB change, as ERwin places the columns in pos 3
> >>and 4 (non modifyable).
> >>
> >>Thanks,
> >>Alison
> >>
> >>--Original Message--
> >>Don't think so. I also don't know why you would want
> >>to. I see a few
> >>people asking for this aesthetic change. If you
> qualify
> >>all your statements
> >>instead of using * then column order is irrelevant.
> May
> >>look prettier in EM
> >>or other tool but that is about it I thnk.
> >>
> >>.
> >>
> >.
> >

Modification of the primary key !!

Hi

I have binded a DataGradView (DGV) with a Dataset (DS) , i execute and modify on the DataGradView the primary key (IDObj) and also the two other columns, by clicking on the UpDate button i call the function UpDateTable :

public void UpdateTable(string nameTable)
{
// con : ma connection OLE à un fichier Access 2003
// DS : dataSet
// DAdp : Dataadapter
// nameTable : nom de ma table
OleDbCommand comdUPDATE;
string CommandText = "UPDATE " + nameTable + " SET IDObj=@.IDObj ,NameParent=@.NameParent,TypeObj=@.TypeObj WHERE IDObj=@.IDObj";
comdUPDATE=DAdp.UpdateCommand = new OleDbCommand(CommandText, con);
// IDObj : cl principale
comdUPDATE.Parameters.Add(new OleDbParameter("@.IDObj", OleDbType.VarChar, 50));
comdUPDATE.Parameters.Add(new OleDbParameter("@.NameParent", OleDbType.VarChar, 50));
comdUPDATE.Parameters.Add(new OleDbParameter("@.TypeObj", OleDbType.VarChar, 50));
comdUPDATE.Parameters["@.IDObj"].SourceVersion= DataRowVersion.Original; //!!!
comdUPDATE.Parameters["@.NameParent"].SourceVersion= DataRowVersion.Current;
comdUPDATE.Parameters["@.TypeObj"].SourceVersion= DataRowVersion.Current;

comdUPDATE.Parameters["@.IDObj"].SourceColumn = "IDObj";
comdUPDATE.Parameters["@.NameParent"].SourceColumn = "NameParent";
comdUPDATE.Parameters["@.TypeObj"].SourceColumn = "TypeObj";
DataSet modifiedDS = DS.GetChanges(DataRowState.Modified);

try
{
con.Open();
DAdp.Update(modifiedDS.Tables[nameTable]);
DS.Clear();
DAdp.Fill(DS, nameTable);
con.Close();
}
catch (Exception e)
{
MessageBox.Show(e.Message.ToString());
}
finally { con.Close(); }

}

But i have this exception : Concurrency violation : the update command affected 0 of the expected 1 records.

how is it possible to modify a primary key witch is the reference of that modified row?

Thanks for help

The more inportant question is WHY would you ever want to modify a primary key?

Primary keys are commonly used to create relationships between data. If the Primary Key was altered, the relationship would break.

The concurrency violation is because you are attempting to create two rows with the same primary key value -and that is not allowed.

|||

I know that each row has to be identified with that primary key it's like its coordinates.

but sometimes the identification of an object (equipments, tasks..) is wrong and need to be corrected.

is there i way to do it?

|||

Hi,

You are going to alter the database design. It better if you make amendment at database level.

Cheers!!

|||

Yes, you just update the table and set the Primary Key column equal to the new value.

However, if that new value already exists in the table, you will get an error -like the error you are currently getting. There cannot be two rows in the table with the exact same primary key value.

However, in your query, there is a problem:

" SET IDObj=@.IDObj ,NameParent=@.NameParent,TypeObj=@.TypeObj WHERE IDObj=@.IDObj"[/quote]

This is written like this: SET A = B WHERE A = B

I hope you can see the problem. You need two parameters, one for the old value to use in the WHERE clause, and another for the new value assignment. Perhaps more like:

Code Snippet

UPDATE MyTable
SET
IDObj = @.NewIDObj,
NameParent = @.NameParent,
TypeObj = @.TypeObj
WHERE IDObj = @.OldIDObj

|||

I'll trey this :

MyUpdateCommd.Parameters["@.OldIDObj"].SourceVersion =DataRowVersion.Original;

and let :

MyUpdateCommd.Parameters["@.NewIDObj"].SourceVersion =DataRowVersion.Current;

is it that?

|||That looks like it should work.|||

Hi Gays

always same exception.

A) what i have :

i binded i DataGradeView with Dataset.

i try to modify value of any columns.

the Update command failed and exception rised.

B) What i code :

string CommandText =
"UPDATE " + nameTable +
" SET IDObj=@.IDObj , NameParent=@.NameParent, TypeObj=@.TypeObj"+
" WHERE IDObj=@.IDObjOrig ";

I indicate the VersionSource wich is "Current" for all parameters exept for @.IDObjOrig that is "Original"

i did not use the value property of parameters because i modify by editing on Datagradview then i call the UpDate method of the DataAdapter by passing my dataset and the name of my table.

? exception......I don't understand ..

Model definitions

I've inherited a database from a vendor. This database has primary keys defined but not any foreign keys to other tables. The vendor establishes all their relationships in their code.

I've created a simple model off of the base tables, but can only report on one table at a time which makes perfect sense. What is the best way to create a more usable model?

Create views with foreign keys defined and then use the views as my source for the dsv? or can I define relationships within the model itself? I have not been able to figure out how to add a foreign key in the model Can this be done?

1. Load your model into Model Designer.
2. Edit DSV - manually add foreign keys your tables
3. Regen the model - roles should automatically appear.

Mixed mode cluster?

Does any one have experiences with Mixed mode cluster?
I am trying to add a windows 2003 node as primary node to an existing
window 2000 active passive cluster.
Here is what i did:
From WINDOWS 2000 Active/Passive SQL 2000 cluster, Evicted the PRimary node.
Loaded WINDOWS 2003 onto the primary node.
It is a new intstall of WINDOWS 2003 not upgrade from 2000 to 2003.
NOw i am trying to join this node to the cluster but it is not able to find
the cluster service on the primary node which has windows 2003.
And i get the error 0x87000b.
Thanks
Anand
Primary and Secondary nodes are a concept from SQL 7.0 and do not apply to
this situation. You need to run the cluster wizard on the Windows 2003 node
and add it to the existing cluster. This is assuming the new node has
proper access to the shared storage system.
I am not sure if it will work. The only time I have run a mixed mode
cluster was while I was upgrading the hosts. Once I upgraded, I went back
and rebuilt each node from scratch, just so I would have a clean install of
Windows 2003.
Good luck.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Anand Musunur" <AnandMusunur@.discussions.microsoft.com> wrote in message
news:33E8488F-86D5-46DD-AF7B-09E6BA5645EA@.microsoft.com...
> Does any one have experiences with Mixed mode cluster?
> I am trying to add a windows 2003 node as primary node to an existing
> window 2000 active passive cluster.
> Here is what i did:
> From WINDOWS 2000 Active/Passive SQL 2000 cluster, Evicted the PRimary
node.
> Loaded WINDOWS 2003 onto the primary node.
> It is a new intstall of WINDOWS 2003 not upgrade from 2000 to 2003.
> NOw i am trying to join this node to the cluster but it is not able to
find
> the cluster service on the primary node which has windows 2003.
> And i get the error 0x87000b.
> Thanks
> Anand
sql

Missing Primary Keys

HI Folks ,

I have a problem with the primary keys in my main Db , I want to setup replication and looks like someone tampered with my database by removing the primary keys.and in order to setup replication i need this table to have primary keys .THere are duplicates in that table but they are nessecary...and for this reason the primary keys do not want to "stick" when i try and specify them.Can anybody help

BurnerWithout the PK, transactional replication won't work. You could use snapshot replication if you wanted.

If your table needs to have duplicate primary keys, you have a design problem. At some point, you'll probably need to fix it.

-PatP|||Hi pat ,

I know taht it wont work. What I want to know is ; is there a way to "restore" the primary keys ?What i did now was create another table with the exact same design ..specified my primary keys..but what i want to do now is import all my data into this table including my duplicates ..is there a way to do this ...using a script or something ..cause dts will not allow duplicates to be importing because of the primary key set on the table .|||I'm confused. I think that you wnat ALTER TABLE ADD CONSTRAINT if you want to put the PK constraint back on the table.

If you are trying to clean out the table, you can use:SELECT *
FROM icky_table AS a
WHERE 1 < (SELECT Count(*)
FROM icky_table AS b
WHERE b.PK = a.PK)This will show you the duplicate rows based on the PK values. You'll have to sort out which of those rows you want to keep and which you want to discard.

-PatP

MISSING FUNCTIONALITY? PLEASE HELP

I think I am missing something, and this something I could achieve with Enterprise Manager. How do I copy one object, lke a table, including primary keys, indexed and triggers, plus data, from one SQL Server to another, or a set of tables? I am at a loss. Of I choose "Copy Data", that is not the same because the table data is copied, but nothing else, not even the primary key gets transferred. So the arriving table is --quite frankly-- useless. What do I do with a table that lacks keys, constraints, indexes and triggers?. On the other hand, I don't see any click-and-shoot way to transfer the missing information for one single or a few tables together. If I script the table, the table gets dropped and recreated, so I lose the data. In the older Enterprise Manager, you cold choose "transfer objects", and everything moved along, data and script, so the arriving table could be used immediately.
Am I going blind or somebody dropped the ball here?

There is a "Transfer Objects Task" in SSIS which does this. I don't know if it gets used by the import/export wizard cos I've never looked at it but the fact that you can't find it probably means that it isn't in which case you'll have to build your own package to do this. Which won't be at all difficult.

-Jamie|||Jamie is correct. The transfer objects functionality does not appear in the wizard, but is available in the SSIS package designer.|||[Transfer SQL Server Objects Task] Error: Execution failed with the following error: "Cannot apply value null to property Login: Value cannot be null..".

To make the problem more clear: I don't need to transfer logins, just the tables, views, stored procedures, user data types, user-defined functions and defaults.

I think that Microsoft watered down the Enterprise Manager, and is doing exactly what they did with VB6 on Vb.NET, take away the best of the tool: in that case it was change-and-contine, and in the case, the wizards became useless. In any case, I built a package and get the error above all the time, no matter what database I intend transfer from SQL 2000. How do I get around this? I can not start working until I transfer my objects and data, so I need some help here, not just some philosophical advice.
If it takes a lot of steps to achieve what we did with one single wizard, the somebody dropped the ball at Microsof. The "Export Data" should allow the user to select all the details of the transfer. I manage close to 20 SQL Server production machines, and I use that transfer wizard day in and day out.|||Funny you bring this up, we were doing some migrations earlier. I have blogged about it here: http://jason.blogsource.com/

Hope this helps steer you in the right direction...|||I read you blog but I can not guess what the reaso for my error is. Any ideas?|||I was referring to your earlier problem: where you could get tbl structures but not data.... The method I illustrated does add extra steps but is a way if you are desperate.|||

So I assume that you in fact used a package to transfer entire databases from SQL 2000 to SQL 2005 and it worked? you never got the error that I am getting?

Message: Execution failed with the following error: "Cannot apply value null to property Login: Value cannot be null..".

I wonder what is possibly the matter.