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

Model generation (for Report Builder)

I have had to add Calculated columns to some tables in my Data Source View in
order to correctly link some tables but this kills the model generation.
For example, although the job table has a CompanyID column, the job_task
table does not - but I need to be able to lookup a status in CompanyReason
which is indexed on CompanyID and PauseReason (this column is in job_task).
So I added 'theCompanyID' as a Calculated columns to job_task with the
following code:
SELECT TOP 1 j.company_id FROM mh4_job_task jt INNER JOIN mh4_job j on
j.job_id = jt.job_id
Makes sense? But when I create a model with it - this is the error message:
--
An error occurred while executing a command.
Message: Cannot perform an aggregate function on an expression containing an
aggregate or a subquery.
Line 5: Incorrect syntax near ')'.
Command:
SELECT
COUNT([notes]),
COUNT(DISTINCT [notes]),
COUNT((SELECT TOP 1 j.company_id FROM job_task jt INNER JOIN job j on
j.job_id = jt.job_id)),
COUNT(DISTINCT (SELECT TOP 1 j.company_id FROM job_task jt INNER JOIN job j
on j.job_id = jt.job_id)),
COUNT('task_status'),
COUNT(DISTINCT 'task_status')
FROM [dbo].[job_task] t
--
Of course, the error is correct - what I need to know is how to either
bypass the column uniqueness test FOR THIS ONE column or another method to
create the link in the Data Source View.
Thank you,
ScottI would use SSIS to get this set up. I have not done such a thing, but since
SSIS can provide report datasources that's the route I would look at.
"ScottB" <ScottB@.discussions.microsoft.com> wrote in message
news:95A89A5B-F36B-4056-96BF-A748B613CA25@.microsoft.com...
>I have had to add Calculated columns to some tables in my Data Source View
>in
> order to correctly link some tables but this kills the model generation.
> For example, although the job table has a CompanyID column, the job_task
> table does not - but I need to be able to lookup a status in CompanyReason
> which is indexed on CompanyID and PauseReason (this column is in
> job_task).
> So I added 'theCompanyID' as a Calculated columns to job_task with the
> following code:
> SELECT TOP 1 j.company_id FROM mh4_job_task jt INNER JOIN mh4_job j on
> j.job_id = jt.job_id
>
> Makes sense? But when I create a model with it - this is the error
> message:
> --
> An error occurred while executing a command.
> Message: Cannot perform an aggregate function on an expression containing
> an
> aggregate or a subquery.
> Line 5: Incorrect syntax near ')'.
> Command:
> SELECT
> COUNT([notes]),
> COUNT(DISTINCT [notes]),
> COUNT((SELECT TOP 1 j.company_id FROM job_task jt INNER JOIN job j on
> j.job_id = jt.job_id)),
> COUNT(DISTINCT (SELECT TOP 1 j.company_id FROM job_task jt INNER JOIN job
> j
> on j.job_id = jt.job_id)),
> COUNT('task_status'),
> COUNT(DISTINCT 'task_status')
> FROM [dbo].[job_task] t
> --
> Of course, the error is correct - what I need to know is how to either
> bypass the column uniqueness test FOR THIS ONE column or another method to
> create the link in the Data Source View.
> Thank you,
> Scott|||Hi,
I am new to SSIS. I have been searching for some examples to
use Expression Builder in SSIS to use derived columns
transformation...I basically want to add a new column to my data source
on the run and add this column as the 2nd column before loading into
destination. How can I achieve this? Plz help. Is there a link where I
can get examples online'

Missing SqlDataReader.GetSqlMetaData() in April CTP

Where did the GetSqlMetaData method go? I am trying to compare the SQL data types of two different columns to ensure compatibility prior to data transfer. The closest thing I can find in the April CTP is the DataReader.GetSchemaTable() which describes the columns' data types in .NET data types, not SQL data types. Any suggestions?

SMX_Mark wrote:

Where did the GetSqlMetaData method go? I am trying to compare the SQL data types of two different columns to ensure compatibility prior to data transfer. The closest thing I can find in the April CTP is the DataReader.GetSchemaTable() which describes the columns' data types in .NET data types, not SQL data types. Any suggestions?

I believe it was cut at the same time as the merge between the server and client provider happened.
Niels