Changing column order in a table

This subject has been posted several times, but I haven't seen a good
answer.

Problem:
I want to change the order of the columns in a table using T-SQL only.

Explanation:
After running your code, I want to see the following table...

CREATE TABLE [dbo].[TableName] (
[First_Column] [int] NULL ,
[Second_Column] [varchar] (20) NULL
) ON [PRIMARY]

look like this...

CREATE TABLE [dbo].[TableName] (
[Second_Column] [varchar] (20) NULL ,
[First_Column] [int] NULL
) ON [PRIMARY]

Limitations:
Don't post if your post would fall in the following categories:
1. If you don't think it can be done
2. If you think Enterprise Manager is the only way to do this
3. If you think I should just change the order of my Select
statements
4. If you want to state that order column doesn't matter in a
relational database
5. If you want to ask me why I want to do this

Wish:
Hopefully the answer WON'T involve creating a brand new table, moving
the data from old to new, dropping the old table, then renaming the
new table to the old name. Yes, I can do that. The table I'm working
with is extremely huge -- I don't want to do the data juggling.

Thanks in advance!Dan,

I hope this doesn't fall into your 5 commandments ;-) If by any reason the
order of columns is important to you, you can use a view. I do understand
that I'm not supposed to ask you why you want to do this, but why!!? :-) If
you tell us your original problem then you might be lucky enough to find
somebody that can give you some other alternatives. I'm sure you have a
reason for this. Without revealing much information about what you want to
do it's really hard to find the solution.

Good luck,
Shervin

"Dan Newton" <dnewton@.scriptsave.com> wrote in message
news:280c01a4.0312091312.2f52e02@.posting.google.co m...
> This subject has been posted several times, but I haven't seen a good
> answer.
> Problem:
> I want to change the order of the columns in a table using T-SQL only.
> Explanation:
> After running your code, I want to see the following table...
> CREATE TABLE [dbo].[TableName] (
> [First_Column] [int] NULL ,
> [Second_Column] [varchar] (20) NULL
> ) ON [PRIMARY]
> look like this...
> CREATE TABLE [dbo].[TableName] (
> [Second_Column] [varchar] (20) NULL ,
> [First_Column] [int] NULL
> ) ON [PRIMARY]
> Limitations:
> Don't post if your post would fall in the following categories:
> 1. If you don't think it can be done
> 2. If you think Enterprise Manager is the only way to do this
> 3. If you think I should just change the order of my Select
> statements
> 4. If you want to state that order column doesn't matter in a
> relational database
> 5. If you want to ask me why I want to do this
> Wish:
> Hopefully the answer WON'T involve creating a brand new table, moving
> the data from old to new, dropping the old table, then renaming the
> new table to the old name. Yes, I can do that. The table I'm working
> with is extremely huge -- I don't want to do the data juggling.
> Thanks in advance!|||dnewton@.scriptsave.com (Dan Newton) wrote in message news:<280c01a4.0312091312.2f52e02@.posting.google.com>...
> This subject has been posted several times, but I haven't seen a good
> answer.
> Problem:
> I want to change the order of the columns in a table using T-SQL only.
> Explanation:
> After running your code, I want to see the following table...
> CREATE TABLE [dbo].[TableName] (
> [First_Column] [int] NULL ,
> [Second_Column] [varchar] (20) NULL
> ) ON [PRIMARY]
> look like this...
> CREATE TABLE [dbo].[TableName] (
> [Second_Column] [varchar] (20) NULL ,
> [First_Column] [int] NULL
> ) ON [PRIMARY]
> Limitations:
> Don't post if your post would fall in the following categories:
> 1. If you don't think it can be done
> 2. If you think Enterprise Manager is the only way to do this
> 3. If you think I should just change the order of my Select
> statements
> 4. If you want to state that order column doesn't matter in a
> relational database
> 5. If you want to ask me why I want to do this
> Wish:
> Hopefully the answer WON'T involve creating a brand new table, moving
> the data from old to new, dropping the old table, then renaming the
> new table to the old name. Yes, I can do that. The table I'm working
> with is extremely huge -- I don't want to do the data juggling.
> Thanks in advance!

You will have to create a new table, copy the data, then rename it.
This may be tedious, but it will work and it is supported. The only
'short cut' would be to attempt to hack syscolumns, but since many
columns are 'internal use only' and direct updates to system tables
are not supported, you have no guarantee of success, and no support
from Microsoft if you have problems (which may not appear at first).

Or as Shervin suggested, if you can provide more information on what
your issue is, there may be an alternative solution.

Simon

Changes to order by in sql 2005

Hi, can anyone tell me why the following fails in sql 2005 with the error,
invalid column name 'title2'. This works in all other versions including 200
5
Express.
Select title as title1, title as title2 from title order by [title].title2
I can fix the query to run under 2005 by removing the field aliases, but I
can't find anything to explain why it failed. There are over 1000 queries in
my application, and although this is the only reported failure so far, if
there is a fundamental change regarding aliases or order by clauses, I need
to know so I can fix all affected queries.
ThanksPaul (Paul@.discussions.microsoft.com) writes:
> Hi, can anyone tell me why the following fails in sql 2005 with the
> error, invalid column name 'title2'. This works in all other versions
> including 2005 Express.
> Select title as title1, title as title2 from title order by [title].title2
If fails, because it's incorrect SQL. There is no title2 in title.
It was accepted in SQL 2000, yes. That was a bug.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Specifying a table alias in ORDER BY doesn't make sense, logically. From a l
ogical standpoint the
ORDER BY is performed as one of the last steps of the query (FROM, WHERE, GR
OUP BY, HAVING, SELECT
<col1, col2>, ORDER BY, TOP). Since ORDER BY is after the SELECT, we don't "
see" the base tables any
more, so it doesn't make sense to refer to them. In fact, at this stage, the
re exist only one table,
the table to be returned. But even SQL Server 2005 has some strangeness left
behind, even though it
is stricter than 2000:
--OK in 2000 and 2005
SELECT au_lname AS lname FROM authors AS a ORDER BY a.au_lname
--Fails in 2005, OK in 2000
SELECT au_lname AS lname FROM authors AS a ORDER BY a.lname
--Fails in 2000 and 2005
SELECT au_lname AS lname FROM authors AS a ORDER BY authors.au_lname
--Fails in 2005, OK in 2000
SELECT au_lname AS lname FROM authors AS a ORDER BY authors.lname
Anyhow, search for ORDER BY in below BOL URL (make sure you have the updates
BOL):
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/65eaafa1-9e06-4264-b547-
cbee8013c995.htm
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:FD1F5D8D-F11B-432C-A081-B75036B890A2@.microsoft.com...
> Hi, can anyone tell me why the following fails in sql 2005 with the error,
> invalid column name 'title2'. This works in all other versions including 2
005
> Express.
> Select title as title1, title as title2 from title order by [title].title2
> I can fix the query to run under 2005 by removing the field aliases, but I
> can't find anything to explain why it failed. There are over 1000 queries
in
> my application, and although this is the only reported failure so far, if
> there is a fundamental change regarding aliases or order by clauses, I nee
d
> to know so I can fix all affected queries.
> Thanks
>|||Paul wrote:
> Hi, can anyone tell me why the following fails in sql 2005 with the error,
> invalid column name 'title2'. This works in all other versions including 2
005
> Express.
> Select title as title1, title as title2 from title order by [title].title2
> I can fix the query to run under 2005 by removing the field aliases, but I
> can't find anything to explain why it failed. There are over 1000 queries
in
> my application, and although this is the only reported failure so far, if
> there is a fundamental change regarding aliases or order by clauses, I nee
d
> to know so I can fix all affected queries.
> Thanks
You need to read this section in Books Online:
http://msdn2.microsoft.com/en-us/library/ms143532(SQL.90).aspx
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks for the replies, at least I know what to check for in my other querie
s
now.
Thanks
"Paul" wrote:

> Hi, can anyone tell me why the following fails in sql 2005 with the error,
> invalid column name 'title2'. This works in all other versions including 2
005
> Express.
> Select title as title1, title as title2 from title order by [title].title2
> I can fix the query to run under 2005 by removing the field aliases, but I
> can't find anything to explain why it failed. There are over 1000 queries
in
> my application, and although this is the only reported failure so far, if
> there is a fundamental change regarding aliases or order by clauses, I nee
d
> to know so I can fix all affected queries.
> Thanks
>

Change the Sort Order

Hi,
I hope than somebody can explain me why the report order on the reporting
services desing (Visual Studio), is differen than the query sort order.
Normal SQL Query
1) AA
2) AB
3) A1
4) 1A
Visual Studio Report Design
1) 1A
2) A1
3) AA
4) AB
Can some body help me to change that setting on the
Thanksplease... help me !!!!.....
Edman wrote:
>Hi,
>I hope than somebody can explain me why the report order on the reporting
>services desing (Visual Studio), is differen than the query sort order.
>Normal SQL Query
>1) AA
>2) AB
>3) A1
>4) 1A
> Visual Studio Report Design
>1) 1A
>2) A1
>3) AA
>4) AB
>Can some body help me to change that setting on the
>Thanks
Message posted via http://www.sqlmonster.com|||I almost never set the sort in RS, I set the sort order in my query.
Sometimes I can't do this but when I can, I do.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Edman via SQLMonster.com" <u14374@.uwe> wrote in message
news:554a9135a7fd0@.uwe...
> please... help me !!!!.....
> Edman wrote:
>>Hi,
>>I hope than somebody can explain me why the report order on the reporting
>>services desing (Visual Studio), is differen than the query sort order.
>>Normal SQL Query
>>1) AA
>>2) AB
>>3) A1
>>4) 1A
>> Visual Studio Report Design
>>1) 1A
>>2) A1
>>3) AA
>>4) AB
>>Can some body help me to change that setting on the
>>Thanks
>
> --
> Message posted via http://www.sqlmonster.com

Change the order of columns

By mistake I misplaced the order of my primary keys in some tables and I just dont find the way to move them to the top of each table. This must be something easy. and its ugly to see primary keys on the foot.If you are using the management studio, right click on the table in question and select "Modify" from the context menu. In the work area click the area to the left of the column name you want to move and drag it up or down to its new location.
|||

Thanks, very easy. The problem was that I always make my tables in the Diagram view. and there is no posibility to do that. Only in the table view.

So easy answer.

Change SQL (order by) at runtime?

I'm creating a fairly simple report using the ReportViewer control in
a Windows app. I have credentials and everything working, but I want
to change the SQL at run time. The dataset in the designer has this
SQL:
Select * from CPS.Main Order By Docket
Easy enough, but how can I replace that Docket with something at
runtime. I tried
Select * from CPS.Main Order By ?
and passed in a parameter. It runs, but ignores the Order By.
Am I going about this the wrong way? It's an ODBC database, not
SQLServer if that matters.First, one option for you is to allow the user to sort as they want. User
sortable columns is availabe starting in RS 2005.
Otherwise, what you want to do is to set the data source as an expression.
You are creating a string that equates to the SQL you want.
= "Select * from CPS.Main Order By " & Parameters.MyParam.Value
Be sure you have the field list you want because once you switch to this you
will not get a field list refreshed.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"dgk" <dgk@.somewhere.com> wrote in message
news:rcggs31e1ni0a29noueeetlud5qugmguko@.4ax.com...
> I'm creating a fairly simple report using the ReportViewer control in
> a Windows app. I have credentials and everything working, but I want
> to change the SQL at run time. The dataset in the designer has this
> SQL:
> Select * from CPS.Main Order By Docket
> Easy enough, but how can I replace that Docket with something at
> runtime. I tried
> Select * from CPS.Main Order By ?
> and passed in a parameter. It runs, but ignores the Order By.
>
> Am I going about this the wrong way? It's an ODBC database, not
> SQLServer if that matters.|||On Fri, 29 Feb 2008 12:14:12 -0600, "Bruce L-C [MVP]"
<bruce_lcNOSPAM@.hotmail.com> wrote:
>First, one option for you is to allow the user to sort as they want. User
>sortable columns is availabe starting in RS 2005.
>Otherwise, what you want to do is to set the data source as an expression.
>You are creating a string that equates to the SQL you want.
>= "Select * from CPS.Main Order By " & Parameters.MyParam.Value
>Be sure you have the field list you want because once you switch to this you
>will not get a field list refreshed.
Not option one, each page is a separate law case, the Order By will be
putting those pages in some sort of order.
I was about to tell you that your second approach doesn't work,
because the red ! in the data tab of the designer (VS2005) is greyed
out, but it does work when switching to the Preview tab.
Thanks, problem solved.|||I should have said you don't get a field list AND you can't execute from the
dataset tab.
But, you figured that out yourself.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"dgk" <dgk@.somewhere.com> wrote in message
news:53lgs3l06d1gkp5k2cgrul7c16dq7kam80@.4ax.com...
> On Fri, 29 Feb 2008 12:14:12 -0600, "Bruce L-C [MVP]"
> <bruce_lcNOSPAM@.hotmail.com> wrote:
>>First, one option for you is to allow the user to sort as they want. User
>>sortable columns is availabe starting in RS 2005.
>>Otherwise, what you want to do is to set the data source as an expression.
>>You are creating a string that equates to the SQL you want.
>>= "Select * from CPS.Main Order By " & Parameters.MyParam.Value
>>Be sure you have the field list you want because once you switch to this
>>you
>>will not get a field list refreshed.
>
> Not option one, each page is a separate law case, the Order By will be
> putting those pages in some sort of order.
> I was about to tell you that your second approach doesn't work,
> because the red ! in the data tab of the designer (VS2005) is greyed
> out, but it does work when switching to the Preview tab.
> Thanks, problem solved.
>

Change sort order for SQL 7 SP4

Is their a way to change the sort order. I have a backup
i need to restore, but it says the sort order is not
correct.
Thanks
RickRick,
With SQL 7, I think you'd have to re-install the server & specify the
appropriate sort-order during installation. I assume that you're restoring
to a different server than that from which the backup was taken, hence the
error message.
If you need to transfer a database to a server that you can't re-install,
with different language, sortorder, case sensitivity etc, I think you may
need to do the transfer using DTS or similar.
HTH
--
--
Kind Regards,
Robert A. Ellis, MCSD
Software Developer
"Rick" <rhollis@.fmtsd.com> wrote in message
news:18c501c35dd9$adfb9ba0$7d02280a@.phx.gbl...
> Is their a way to change the sort order. I have a backup
> i need to restore, but it says the sort order is not
> correct.
> Thanks
> Rick|||Hi Rick and Robert,
Actually you need to rebuild the master database (using rebuildm utility)
to change sort order in SQL Server 7.0. Doing this in effect will have a
newly built master database, so all your references to user databases,
logins etc will be lost.
Sincerely,
Yih-Yoon Lee [Microsoft]
Microsoft SQL Server Support
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Change report groups at RunTime

Hi,
Ho can we change the order of groups in Crystal report at runtime through VB code

Thanks in Advance
Manoj.

To make it more clear..
Eg.
Group X
Group Y
Group Z
Group M
...
...

But some time I want the groups like
Group X
Group M
Group Z
Group Y
...
...If you're passing a recordset to Crystal, you could just set the 'ORDER BY' in your query

change Parameter order

Hello,

I'd like to change the order in which Parameters are displayed. When I go to the menu Report - Report Parameters and change the order with the arrows displayed - I'll get the following error msg.:The value expression for the report parameter contains an error:The expression contains a non existing parameter in the report parameter collection.

Any ideas to change the order? Edit the XML File?

Thanks!You're doing it the right way. It sounds like you have dependent parametrs in which case they have to appear in order of dependency.|||Thats right they depent on each other but this was automatically. How can I turn that on or off? And if I turn that off - will my procedure to switch the position of the Parameter fields work?

Thanks!|||

Parameters become dependent when you use parameter A in the query that populates paramter B. This usually happens in the WHERE clause e.g.

SELECT *
FROM param_b_table
where some_column = @.param_a

Removing the WHERE clause should remove the dependency between the parameter and and allow you to reorder them.

But are you sure that removing the dependency is really what you want to do for the sake of ordering?

Obviously you won't be able to put the WHERE clause back in and expect to retain the ordering.

|||I'm not using any SQL Query. I use MS Reporting Services - Design Mode and click my Measures and Dimensions together.|||right so you're querying an Analysis Services 2005 cube using MDX. Fine so in the query design mode for your parameter query, remove the dependency to the other parameters.|||Here is my query. How can I switch the dependencies off. After doing this it is possible to change the order and switching the dependencies back on again?

SELECT NON EMPTY { } ON COLUMNS, NON EMPTY { ([Organisation].[Business Unit Description].[Business Unit Description].ALLMEMBERS * [Organisation].[Room Local Code].[Room Local Code].ALLMEMBERS * [Organisation].[Business Area Description].[Business Area Description].ALLMEMBERS * [Time].[Hour Description].[Hour Description].ALLMEMBERS * [Date].[Date].[Date].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@.OrganisationMainUnitDescription, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.OrganisationRoomLocalCode, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.DateDate, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.OrganisationBusinessAreaDescription, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( { [Organisation].[Room Type Description].&[Recovery room] } ) ON COLUMNS FROM [Clinical Performance]))))) WHERE ( [Organisation].[Room Type Description].&[Recovery room], IIF( STRTOSET(@.OrganisationMainUnitDescription, CONSTRAINED).Count = 1, STRTOSET(@.OrganisationMainUnitDescription, CONSTRAINED), [Organisation].[Main Unit Description].currentmember ) ) CELL PROPERTIES VALUE|||

I'll refer you to my previous answer in which I stated that you can't expect to put the WHERE clause back in and expect to retain the order. This is equivalent to putting back the dependency, you can'y do it and expect to retain ordering.

Once again, parameters with dependency must appear in order of dependency otherwise you'll get an error.

FYI, the dependencies in this query are highlighted. However, this looks like the main dataset query rather than a parameter dataset query and if so this is not the one you want to remove the dependencies from. Paramter order will need to be enforced if the parameters are dependet on each other, so it's the queries that feed the parameters you want to be looking at and not the main dataset query.

SELECT
NON EMPTY { } ON COLUMNS,
NON EMPTY { ([Organisation].[Business Unit Description].[Business Unit Description].ALLMEMBERS * [Organisation].[Room Local Code].[Room Local Code].ALLMEMBERS * [Organisation].[Business Area Description].[Business Area Description].ALLMEMBERS * [Time].[Hour Description].[Hour Description].ALLMEMBERS * [Date].[Date].[Date].ALLMEMBERS ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM (
SELECT ( STRTOSET(@.OrganisationMainUnitDescription, CONSTRAINED) ) ON COLUMNS
FROM (
SELECT ( STRTOSET(@.OrganisationRoomLocalCode, CONSTRAINED) ) ON COLUMNS
FROM (
SELECT ( STRTOSET(@.DateDate, CONSTRAINED) ) ON COLUMNS
FROM (
SELECT ( STRTOSET(@.OrganisationBusinessAreaDescription, CONSTRAINED) ) ON COLUMNS
FROM (
SELECT ( { [Organisation].[Room Type Description].&[Recovery room] } ) ON COLUMNS
FROM [Clinical Performance])))))
WHERE ( [Organisation].[Room Type Description].&[Recovery room], IIF( STRTOSET(@.OrganisationMainUnitDescription, CONSTRAINED).Count = 1, STRTOSET(@.OrganisationMainUnitDescription, CONSTRAINED), [Organisation].[Main Unit Description].currentmember ) )
CELL PROPERTIES VALUE