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


ADVERTISEMENT

How To Change Column Names In The Grid View Programatically?

Aug 31, 2007

I have consistent column names that load into a grid view. I now need to change the names in the grid view programatically. I was originally trying to get cute with SQL to do this, but I've been told my below solution will not work and I'm better off to do this in the presentation layer.  If this is true, remember I'm still wet behind the ears here...how do I do this in vs2005?
Here's my post to the SQL devs to give you an idea what I'm trying to do.
I have a query that grabs fields from a denormalized table. The result is column names Week1, Week2, Week3....Week26.  Users want to see the actual date instead of Week#. So I have a table (lkpdatecaptions ) that contains the fields "fldfieldno" and "Fldcaption". fldfieldno    Fldcaption-----------  --------------11             9/07/200712             9/14/200713            9/21/2007So fieldno 11 represent week1 and so on. So my hope is to update the alias with a query like: Select fldcaption from forecast.tlkpdatecaptionswhere fldfieldno = 11That quey returns the value of 9/7/2007 and is the value I need to represent the coumn alias name.My current query looks like this:SELECT  SUM(forecast.tblforecastdenormalized.fldwk01) AS WEEK1,So can I do something like the following?SELECT  SUM(forecast.tblforecastdenormalized.fldwk01) AS (Select forecast.tlkpdatecaptions.fldcaption from forecast.tlkpdatecaptions where fldfieldno = 11), ...

View 6 Replies View Related

Not Be Able To Change The View

Jan 21, 2008

I have a Database called DATAMART
Login name called ARCTEC and assigned to schema ARCTEC.

Views are ARCTEC.myview1 , ARCTEC.myview2

When i use login ARCTEC I can amend the view.

Im trying to prevent this and only have the login beable to run it.

I right click on the ARCTEC.myview1 permissions and keep adding ARCTEC and clicking on deny and
i still can login and amend the view.

What am doing wrong.

View 2 Replies View Related

HELP - View Permissions Not Change In EM

Nov 6, 2000

Is there a way to set it so that a user can view permissions in EM but not change them? I have tried using the SecurityAdmin role on the database, but this lets the user change the permissions. I really need to be able to do this, is there any way or can anyone make any other suggestions about this i.e., can you place the user in this role yet revoke the ability to commit a change?

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

Data Type Change In SQL View

Jul 20, 2005

I've created a SQL View in SQL 2000 using a table that has columnsdefined as Numeric data type. In the view, I am grouping and summing.When I link the view via ODBC to Access, all of the data types arechanged to text. If I link the original table which the views arecreated from to Access, the data types are correct. So this problemobviously happens when I change the view to Group. Is there a wayaround this? I want the data types to be correct in Access when Ilink to the views.thanks,jim

View 1 Replies View Related

How To Change The Datasource View Of A Mining Model.

Feb 9, 2007

Hi,

I created on model in Business Intelligence Development studio.I wants to change the datasourceview(dsv) assigned for that view.for e.g

My mining Model is StudModel.dmm.

The dsv for that model is StudDSV.dsv.

I wants to change the dsv for that model to marksDSV.dsv.

How to do that in Business Intelligence Development studio.

Thanks,

Karthik.

View 4 Replies View Related

Create Multi Column View From Single Column Data

Jan 9, 2008

I have two tables, one a data table, the other a product table. I want to perform a join on the two tables with values distributed into columns based on the value in the month field.

data_table
product_code month value
350 1 10
350 2 20
350 3 30

product_table
product_code profit_center
350 4520

result_view

product_code profit_center mon1 mon2 mon3
350 4520 10 20 30

My current query gives the following result
result_view

product_code profit_center mon1 mon2 mon3
350 4520 10 0 0
350 4520 0 20 0
350 4520 0 0 30

Any direction toward a solution would be appreciated. Am using SS2005.

View 5 Replies View Related

How To Force View To Change Automatically When Table Schema Changes?

Jan 16, 2004

I am still having problem with making View automatically updates itself when the underlying table schema changes. Running sp_recompile on the view table doesn't seem to work either, as I am still getting old format from the view (in Design mode the view returns the right info, but not when I open the View by doing Open View) even though the underlying schema has changed. Right now I find that I have to go into the View and change it a bit to force a recompilation.

And even if sp_recompile does, it would require that I manually do it each time I change a table. Any idea?

View 4 Replies View Related

View Diagram Pane - How Save Layout After I Change It? 2000

Jul 23, 2005

Hello --Is there a way to configure 2000 so it will save a view layout after Ichange it in the diagram pane?Thanks for any help.Larry Mehl

View 2 Replies View Related

How To Change View Code Or Table Form Stored Procedure + -

Jun 1, 2008

need help with my problem
i have this view
this code change the value field "new_unit" evry month from 1 > 2 > 3 > 4
like this evry 4 month it return to 1 >2..........
------------------------------------------ for example

if i put
unit_date = 01/05/2008
and unit=1
than new_unit=1




Code Snippet

SELECT empID, ShiftType, unit_date, unit, ISNULL(NULLIF ((unit - DATEDIFF(mm, unit_date, '01/01/' + CONVERT(varchar, YEAR(GETDATE())))) % 4, 0), 4)

AS new_unit

FROM dbo.empList




my question is how to create a stored procedure that move forward (all the employee) the "new_unit" field
in +1 OR "unit_date" value MONTH +1


like create stored procedure name "plus" + so if i run this name stored procedure name "plus"

the stored procedure go to the viewor table and change the code view or table value

so i forward all the the "new_unit" or "unit_date" value IN one (change the cycle)+1

it doesn't matter if it change the "unit" value in the table "dbo.empList" or "unit_date" value

the important thing is that i can forward +1 or backward -1

evry time i run the stored procedure i get +1 (in the "new_unit")
and olso
create stored procedure name "minus" + so if i run this name stored procedure name "minus"
this stored procedure that move backward the the "unit" value in the table "dbo.empList" or "unit_date" value in -1

TABLE dbo.empList

empid ShiftType unit_date unit

----------------------------------------------------------------------------
11111 6 01/01/2008 1
2222 8 01/03/2008 4
3333 9 01/04/2008 3

TNX for the help

View 7 Replies View Related

View Field Content Update When Real Table Fields Change

Sep 1, 2005

Hi,I use view to join difference table together for some function. However,when the "real" table fields changed (e.g. add/delete/change field). Theview table still use the "old fields".Therefore everytimes when I change the real table, I also needed open theview table and save it by SQL enterprise manager manually for update theview table field.Can we use a SQL command or other method to update it directly?Regards,Silas

View 4 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

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

Change Column Name Dynamically

Nov 30, 2007



Hi All,
I have a series of tables need to import to server. When creating the target tables, I want to change the columns name as well, for example:
Source table column Target table column


Name FN_Name
Age FN_Age

The problem is I suppose I don't know the columns name in source table, I want to the tasks scan the source table and make the change programmlly.

Which tasks or approaches can be used to implement this?

Thanks
Micror

View 6 Replies View Related

Change Capitalization In Sql Table Column

Apr 10, 2007

I have some data that was entered in the DB in ALL CAPS. I would like to reformat all the rows so that it follows the normal "Capitalization". What is the best way to do this? Shoud I create a console application to accomplish this or is there some way to do it natively in sql server 2005.

View 2 Replies View Related

Change Column Datatype $2,000 To 2000

Jul 30, 2007

Hello,How do I change the datatype of a column in a CSV file.  Preferably in the select statement (apparently Cast, Convert, & Replace functions don't work when selecting from a CSV). I have a page where users upload their CSV files to and then I use SQLBulkCopy to insert all the records into my sql table.  The problem is with columns of money data. EX: "$2,000"  >>  2000The CSV file interprets the "$2,000" as a string of text.  How do I convert it to a decimal so I can insert it into my sql database? 

View 4 Replies View Related

SQL Change Column Data Type

Aug 20, 2007

How do I programatically  change a column (say username) in a table (say tblusers) from varchar(25) to varchar(100)I am looking for something likealter tblusers set username as  varchar(100) I know the above statement in nonsensical but it conveys the idea. 

View 3 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

Change Table Column Width

Mar 14, 2000

I know we could change the data type or width of a table column in SQl 7 database, but I am not certain if this column is a primary key. Can we change a primary key column width? If we can, how about this column as a foreign key in another table? Will it be automatically updated to the same width? Thanks in advance for your input.

View 1 Replies View Related

Change Column Data In All Tables At Once

Sep 17, 2005

I would like to change column data in all tables in a single database at once.

For instance, i have many tables in a database, and about 30% of the tables has the column "orderplace".
And all the data in orderplace (every tables in the database) are "houston". instead of going to each table and
"update table set orderplace = 'dallas' " .... is there anyway that i could run a sql statement and it will update all the tables in database that has the orderplace column to "orderplace = 'dallas' " ?

any ideas ? thanks.

View 5 Replies View Related

Tracking Change History, By Column...

Jun 17, 2004

This one is giving me quite a bit more difficulty then I ever imagined it would...

Essentially I would like to use one table to store the change history for multiple tables. I would like to use an update trigger to check which fields have changed in each record, and write a single record for each field that changed containing the table name, field name, previous value and new value to a history table.

I can't seem to find a good way to do this.

View 9 Replies View Related

Change Output Of A Column Into Hyperlink

Dec 22, 2014

I would like to change the output of a column into a hyperlink however I am not sure that is possible without some further post-processing. I am aware I would have to do:

Code:
select
'<a href="' + URL + '" target="_blank" nav="web">' + tbl_ID.ID + '</a>'
from tbl_ID
however this obviously just exports the text
<a href="[URL]" target="_blank" nav="web">[ID]</a>

This would work fine if I was exporting it into some sort of HTML table however this doesn't work with excel for example. Is there a way to make this possible or would I have to create a macro in excel to add the url with the ID separately?

View 3 Replies View Related

Change Results In Column To Display Different

Jan 30, 2004

I have a column in the following select startement called displayvalue. Displayvalue is a varchar column. Primary everything entered into this column is numeric, but there are times when NR is entered. In the select query if it sees NR, I would like to have NR changed in the resultset to NULL or blank. NR doesn't come up all of the time, but I have not idea how to do this in the select statment.

Here goes..

SELECT SAMPLE.SAMPLEID, SAMPLE.U_WORKORDERNUMBER, SAMPLE.U_SAMPLEDATETIME, SDI.PARAMID, SDI.DISPLAYVALUE
FROM SAMPLE SAMPLE, SDIDATAITEM SDI
WHERE ( (SAMPLE.SAMPLEID = SDI.KEYID1) AND
(SAMPLE.U_WORKORDERNUMBER = '0060') AND (SAMPLE.U_SAMPLEDATETIME > '31-DEC-2003') AND (SDI.PARAMID = 'BOD') )
ORDER BY SAMPLE.SAMPLEID ASC, SDI.PARAMID ASC

View 1 Replies View Related

Identify Change Points In A Column

Apr 18, 2008

DECLARE @EffLevels TABLE (ChangePoint int, Value Int)

INSERT@EffLevels
SELECT'1000', '767' UNION ALL--Changed
SELECT'1000', '675' UNION ALL
SELECT'1001', '600' UNION ALL--Changed
SELECT'1001', '545' UNION ALL
SELECT'1001', '765' UNION ALL
SELECT'1000', '673' UNION ALL--Changed
SELECT'1002', '343' UNION ALL--Changed
SELECT'1002', '413' UNION ALL
SELECT'1002', '334' UNION ALL
SELECT'1001', '823'--Changed

-- My Result should be
-- ChangePointPrevChangePointValue
-- 1000Null767
-- 1001 1000 675
-- 1000 1001 765
-- 1002 1000 343
-- 1001 1002 823

Any suggestion ?

View 9 Replies View Related

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 View Related







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