Change Ordinal Position Of The Column

Jun 18, 2008

1 have a table with 74 colun.But due to some deletion i have the ordinal postions as
1
2
3
.
.
69
70
72
73
74
76

what i wants is to change the ordinal from 72 to 71,73 to 72,74 to 73,76 to 74

SELECT @field = 0, @maxfield = max(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName
-- handle insert case here
SELECT @field = min(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName and ORDINAL_POSITION > @field
SELECT @bit = (@field - 1 )% 8 + 1
SELECT @bit = power(2,@bit - 1)
SELECT @char = ((@field - 1) / 8) + 1
--IF substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in ('I','D')
IF substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in ('D') -- For the insertion case don't save the iserted data.


I needed it as i have a trigger whihc is adding value in a log table on update but due to this problem it fails for certain column

Trgger is solme thing like

WHILE @field < @maxfield
BEGIN
SELECT @field = min(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName and ORDINAL_POSITION > @field
SELECT @bit = (@field - 1 )% 8 + 1
SELECT @bit = power(2,@bit - 1)
SELECT @char = ((@field - 1) / 8) + 1
--IF substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in ('I','D')
IF substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in ('D') -- For the insertion case don't save the iserted data.
BEGIN
IF @Type not in ('I')
BEGIN
SELECT @fieldname = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName and ORDINAL_POSITION = @field -1
--print('fieldname = '+@fieldname)
SELECT @sql = 'insert Audit (ActionTypeID, RowID, TableName, PK, FieldName, OldValue, NewValue, UpdateDate,UserID, UserName, UserType)'
SELECT @sql = @sql + ' select convert(bigint,' + @ActionTypeID + ')'
SELECT @sql = @sql + ',' + @RowID
SELECT @sql = @sql + ',''' + @TableName + ''''
SELECT @sql = @sql + ',' + @PKSelect
SELECT @sql = @sql + ',''' + @fieldname + ''''
SELECT @sql = @sql + ',convert(varchar(1000),d.' + @fieldname + ')'
SELECT @sql = @sql + ',convert(varchar(1000),i.' + @fieldname + ')'
SELECT @sql = @sql + ',''' + @UpdateDate + ''''
SELECT @sql = @sql + ',' + @UserID
SELECT @sql = @sql + ',''' + @UserName + ''''
SELECT @sql = @sql + ',''' + @UserType + ''''
SELECT @sql = @sql + ' from #ins i full outer join #del d'
SELECT @sql = @sql + @PKCols
SELECT @sql = @sql + ' where i.' + @fieldname + ' <> d.' + @fieldname
SELECT @sql = @sql + ' or (i.' + @fieldname + ' is null and d.' + @fieldname + ' is not null)'
SELECT @sql = @sql + ' or (i.' + @fieldname + ' is not null and d.' + @fieldname + ' is null)'
--print('@sq=====sdfdfsfsdf')--sha
--print(@sql)--sha
EXEC (@sql)
END
END
END

Kamran Shahid
Sr. Software Engineer(MCSD.Net)
www.netprosys.com

View 9 Replies


ADVERTISEMENT

Transact SQL :: Select Column By Ordinal Position

Apr 16, 2012

Is there any way to select columns by ordinal position rather than by name?

Something like
Select t.[1] as col1, t.[2] as col2
FROM table t

The question comes because of a very specific situation where I've got 2 databases that have exactly the same schema at a column/datatype level but the column names are different.   There are a few other ways to skin the cat but I'm interested if it can be done this way - without a join to syscolumns.

View 12 Replies View Related

Getting Data Based On Ordinal Position Of The Column In The Table

Aug 30, 2007

Hi
I am having a problem in auditing the column data in tables.My requirement is i have write a trigger which is capable of auditing the columns which are going to be added in the future also with out using dynamic SQL.is there any way to do so.
I feel if i can get the column data based on ordinal position then it is possible.
Can any body suggest.
My set Up is like this
I have a base_table to be audited.
I have a Audit_spec table which contains name of the table and columns to be audited.
And Audit table which actually captures the table name,column name ,old value and new value.
I have to audit only those columns in the Audit_spec spec.
If schema changes(Like new column added) happens to base_table and I want that column to be audited.with out any changes to my trigger code i should handle the newly added column ..





View 6 Replies View Related

Ordinal Position

Jul 20, 2005

Is there a neat way to find an ordinal value from a table,for example the median or 95th percentile value in a column,without walking through the table in ascending or descendingorder?Thanks,Jim GeissmanCountrywide Home Loans

View 1 Replies View Related

TSQL Change The Position Of A Column?

Dec 16, 2004

We have a little app to help us move applications from developent --> stage --> production. It uploads the files, runs the new stored procedures etc.

One of the things it does is create new columns in existing tables. In EM you can create a new column in any position, but ALTER TABLE seems to only add columns to the end of the table. Is there a way in TSQL to create columns in arbitrary positions in the table? Or do I have to recreate the whole table?

View 2 Replies View Related

Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer Column Ordinal From Name?

Oct 27, 2006

Hi,

I need to access columns from a data flow by ordinal position in a script transformation (I'm parsing an excel file which has several rowsets across the page). The first problem I encountered is the generated BufferWrapper does not expose the columns collection (i.e. Input0Buffer(0) does not work) but I got around that by implementing my own ProcessInputs(InputId, Buffer) method instead of using the wrapper.

My problem now is that the column ordinals are in some random order (i.e. Column "F1" is ordinal 1 but Column "F2" is 243). Where in the object model can I map between the name and the ordinal - it's not jumping out at me?

Dave



PS Why is the script editor modal, it's frustrating having to switch between the Visual Studio environment and the VSA one.

View 3 Replies View Related

Change/Move Row Position In Sql Compact Edition

May 2, 2008

Hello,

I have a table with the column description. Lets imagine that I have the records:

"a"
"b"
"c"

If this was a List and I wanted to move the "c" to the first position, I would simply remove the "c" and insert it in the 0 index position.

How can I do this with sql ce?

I could create another column named position and every time I would want to change the position, I would increment every other positions below. Example, "c" goes to first position:

position - description
1 - "a"
2 - "b"
3 - "c"

->

1+1 - "a"
2+1 - "b"
1- "c"

->
1- "c"
2 - "a"
3- "b"

But if I have lets say 100 thousand rows and I want to move the last one to the first position I have to do the operation 100K times.

Is there a more effective way to do what I want?

Thank you,
ARtur

View 9 Replies View Related

Reporting Services :: How To Change Position Of Parameter Control In SSRS Report

Jun 24, 2015

I want to Change parameter position in SSRS report.I need Start Date and End Date Parameter at same column so client can easily select Start Date and End Date.I have tried a lot but not succeed.

How Can I change the position of Start Date and End Date.I can't change order of other three parameter Select Institutions, Select Author and Course Pack Names parameter because they are internally related to each other(forward dependencies are there).

View 2 Replies View Related

Column Position Sql Table

Jul 26, 2007

 There are about 500 tables in one particular datbase. There are
foreign keys set on most of the tables. I want to change the position
of the primary key column in all those tables. How can I do that
programatically?

View 6 Replies View Related

Can We Interchange The Column Position In Sql

May 26, 2008

HI All,
can we interchange the column position in sql.

View 4 Replies View Related

Swap Column Position

Jul 20, 2005

What is a good method/mechanism to swap the position of multiplecolumns?For instance, tblXZY has the followings columns and respectivepositions:tblXZY======xyzUUID 1fn 2ln 3phone 4email 5city 6state 7....Now, I need to make city as 2 and state as 3. BTW, the tblXYZ tablehas data there. Copy/select all the data into a new table withdesired column position would require constraints re-mapping etc.,which seems quite a bit hassle.Thanks.

View 6 Replies View Related

Add Column In Specific Position

Dec 12, 2007

What is the SQL for adding a column at specific location?

eg
TableA
colA
colB

I want to add colC after colA.


Thanks,
Max

View 1 Replies View Related

Can I Move Some Column Position By Using SQL Command ?

Jun 1, 2007

I created SQL table follow by XSD fileAnd when any users added new column to XSD in ordinal position = 3 But after my program successfully created new column, its position is the last position  What can I do ???? I suspect  why I can't set it (I've looked for solution on MSDN already)even though We can see ordinal position bythis query  SELECT *FROM INFORMATION_SCHEMA.Columns   What can I do for solving ???? Help me please 

View 1 Replies View Related

Any Function That Returns The Position Of A String In A Column?

Dec 12, 2007

Hello!
Is there a function that gets the name of a column and a string as arguments and returns the position of this string in the column given?

Thank you in advance.

View 1 Replies View Related

Stacked Column Chart Label Position Problem

Feb 26, 2008

I have a Stacked Column graph and when we set the Position of the Point Label to be "Top Center", the Point Label always stays in the center of that particular Bar.

On a easier, basic Bar chart, the Point Label setting works correctly.

Has anyone else had a similar issue or know if this is a known issue ?

View 2 Replies View Related

Reporting Services :: Change Column Size Dynamically Depends On Content Of The Column?

Jun 4, 2015

How to change column size dynamically depends on content of the column.

View 2 Replies View Related

Ordinal Question

Aug 29, 2006

Hi guys,

How to produce ordinal numbers in sql query?

Say I have a table for a Building. My floor numbers are intergers, 1,2,3,4 and so on. When I run the query, the result I need is like this:

1st
2nd
3rd
4th
5th
...
23rd
....

How will I do this?

Thank you.

View 17 Replies View Related

Ordinal Number In Results

Jan 13, 2006

I would like to add a field to a query that returns an ordinal number indicating which row of the results it is. Anybuddy know how?
eg. 1,2,3,4,5,6,7....

Thanks,
Carl

View 2 Replies View Related

Sqlmangr.: The Ordinal 29 Could Not Be Located In

Apr 24, 2006

Dear all

I've received the following error on the startup of SQL SERVER. Repeating the error when trying to start Service Manager.

sqlmangr.exe : The ordinal 29 could not be located in the dynamic link library odbcbcp.dll

Can someone help me to solve it

View 1 Replies View Related

SQL Cliesnt Failing Ordinal 29 Not Correct..

May 3, 2004

Machine is Windows XP, Client is SQL2K . I tried rebooting without any luck. Uninstalled , reinstalled. After reboot when I try using SQL Clients (EM or QM) it returns the same errors regarding some Ordinal 29 and a big key from registry.. Please help

View 1 Replies View Related

Transact SQL :: Item Cannot Be Found In Collection To Requested Name Or Ordinal

Sep 1, 2015

I have a storeprocedure( sp_nthWorkingday)  which return a calender date.
eg :
calendardate
9/9/2015

I wrote a dts script for displaying this date.Below is the code.

Function Main()
dim myConn
dim myRecordset
dim iRowCount

[code]....

But I am getting an error on execution.Error : item cannot be found in the collection to the requested name or ordinal  

View 2 Replies View Related

ExecuteReader Error A Parameter Is Missing [Paremeter Ordinal = 1]

Sep 24, 2007

Hello and thanks in advance.

This error was received executing a SqlCeCommand method of ExecuteReader();

I have only found 3 hits in Google about this error and the time it was asked in a MS forum it was not answered. I need an answer, please.

bombar...see code below


SqlCeDataAdapter adp = null;

adp = new SqlCeDataAdapter();

SqlCeCommand cmd;

cmd = _Con.CreateCommand();

cmd.CommandType = CommandType.Text;

cmd.CommandText = "insert into InventoryCounts(DateTime,WarehouseID,PartID,LotID,UserName,Count) VALUES(?,?,?,?,?,?)";

adp.InsertCommand = cmd;

cmd = _Con.CreateCommand();

cmd.Parameters.Clear();

SqlCeParameter p1 = cmd.Parameters.Add("DateTime",SqlDbType.DateTime);

SqlCeParameter p2 = cmd.Parameters.Add("WarehouseID",SqlDbType.NChar,20);

SqlCeParameter p3 = cmd.Parameters.Add("PartID", SqlDbType.NChar,20);

SqlCeParameter p4 = cmd.Parameters.Add("LotID", SqlDbType.NChar,20);

SqlCeParameter p5 = cmd.Parameters.Add("UserName", SqlDbType.NChar,20);

SqlCeParameter p6 = cmd.Parameters.Add("Count", SqlDbType.Int);

p1.Value = Convert.ToString(DateTime.Now);

p2.Value = "Warehouse1";

p3.Value = txtBPartIDShow.Text;

p4.Value = txtBLotIDEntry.Text;

p5.Value = "JHB";

p6.Value = Convert.ToInt32(txtBCountEntry.Text);



cmd = _Con.CreateCommand();

cmd.CommandType = CommandType.Text;

cmd.CommandText = "insert into InventoryCounts(DateTime,WarehouseID,PartID,LotID,UserName,Count) VALUES(?,?,?,?,?,?)";

cmd.Prepare();

cmd.ExecuteReader();

View 4 Replies View Related

SQL Server 2008 :: BCP XML File Format Error Invalid Ordinal For Field 2

Oct 1, 2010

When creating xml fileformat its throwing me error "invalid ordinal".

When created non-xml file format, no error, and was also able to load data file into sql table. Not sure why bcp (Version: 10.50.1600.1) is not able to create xml file format.

C:>BCP "MyGDB.dbo.Items_Import" format nul -f"C:AnkitTempBCPItemsMaster.xml" -x -w -T -S"(Local)"

SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid ordinal for field 2 in xml format file.

Column_nameTypeComputedLengthPrecScaleNullableTrimTrailingBlanksFixedLenNullInSourceCollation
Item Numbervarcharno18 noyesnoSQL_Latin1_General_CP1_CI_AS
Description1nvarcharno80 yes(n/a)(n/a)SQL_Latin1_General_CP1_CI_AS
Description2nvarcharno80 yes(n/a)(n/a)SQL_Latin1_General_CP1_CI_AS
UMvarcharno3 yesyesyesSQL_Latin1_General_CP1_CI_AS

View 1 Replies View Related

Change An Int Column To Identity Column

Dec 17, 2001

Hi, I want to change an int column (not null) to identity column. I tried
the following:

alter table myTable alter column ID int identity(10, 1) not null

But it failed with the error message:

Incorrect syntax near the keyword 'identity'.

Can someone please show me the correct statement (if it exists)>

Many thanks.

View 4 Replies View Related

How To Change A Column Name

Jan 9, 2008

I have created a table and found that i miss named a column. All i want to do is change the column name. But I don't see anything in ALTER TABLE to do that.

How do I simply change a column name?

View 2 Replies View Related

Change Row To Column In A Table

Oct 19, 2001

Hi, guru,

How can I Change all rows into columns from one table? what method can make it easy?

Thank you very much

Tony

View 1 Replies View Related

Change Column Length

Dec 20, 2004

Can you alter a column and make it longer without dropping the column

View 3 Replies View Related

Change Value In A Identity Column

Mar 9, 2004

How can I change the value in a identity column? I cannot use update to change its value.

Thank you

View 11 Replies View Related

Change Row Value As Column Header

Dec 22, 2014

I have two tables namely lu_parameter and tbl_param_values. The lu_parameter table consists of param_id and parameter column containing id numbers for the parameter names.

The tbl_param_values table consists of values corresponding to the parameters with the param_id value as the column header.

I want to join these two tables so that in the result query instead of param_id value as column heading, I need to have the parameter value as the column heading.

I have attached the screenshot of the data for reference. I am not that much aware of sql queries.

[URL] .....

View 1 Replies View Related

Change Column Size

Jul 1, 2006

Hi

I am using one Publisher with many subscribers for Merge replication of a database.
I wanted to change size one column in a table. So I added a dummy column (through Filter column).
Copied data, dropped old column (through Filter column), added new column, copied data, and dropped dummy column.
On subscriber A, replication is OK. On subscriber B, I get the following message.
--------------------
Initializing
Connecting to Publisher 'REPLIC'
Retrieving publication information
Retrieving subscription information
A column was added to or dropped from the replicated table.
The schema script 'exec sp_repladdcolumn '[dbo].[Items]','C1',[NVARCHAR(1000) NULL], '%', 1' could not be propagated to the subscriber.
The schema script 'exec sp_repladdcolumn '[dbo].[Items]','C1',[NVARCHAR(1000) NULL], '%', 1' could not be propagated to the subscriber.
A column was added to or dropped from the replicated table.
--------------------

I made a new snapshot, reintialized subscription with upload, and tried to sychronize again.
But same message.

Question 1. Is there anyway to make replication?
Question 2. If not, can I at least upload data changes before dropping and making new publication?

Imtaar

View 1 Replies View Related

Change Column Names

Jan 31, 2006

i am trying to change column names in a bunch of tables.

why is this not right?

is there any sp that i can use as i have to change this in a lot of tables across two databases?

Alter Table Answers

Change Product NewProduct varchar(35)

View 8 Replies View Related

Change A Column Name In A View

Apr 24, 2008



Hello,
I am going to change a column name YYZ in a VIEW because of typo. It should be named as YYY.
What is best scenario?

Thanks

View 1 Replies View Related

How To Change Column Data As Row

Oct 28, 2007

hi

I have a table like this




Username

Clicks

Impressions

Download


User1

12

23

43


User2

34

33

59


User3

39

88

77


User4

34

43

43
need to change the table like this







User1

User2

User3

User4


clicks

12

42

43

43


Impressions

43

43

43

43


download

43

43

43

43
Thanks
Ezhil

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved