Changing between Identity type and Int type

What is the TSQL command to chanage a column of int type to identity type
and vice versa?
Thanks in advance!
KMYou can't add the IDENTITY property to an existing table - you need to
create a new table with the IDENTITY column. If you have existing data, you
can create it with a different name, INSERT the existing data, drop the
existing table, then rename the new table. Enterprise Manager will do this
for you if you add the property in the table designer.
HTH. Ryan
"krygim" <krygim@.hotmail.com> wrote in message
news:OccfMRAIGHA.2012@.TK2MSFTNGP14.phx.gbl...
> What is the TSQL command to chanage a column of int type to identity type
> and vice versa?
> Thanks in advance!
> KM
>|||To add on, if your table is having millions of data do go for Enterprise
Manager, you can mess up your life doing so. It will remain in a hanged
situation. So better do it manual ways like creating a new table and renamin
g
droping the original one.
Thanks,
Sree
"Ryan" wrote:

> You can't add the IDENTITY property to an existing table - you need to
> create a new table with the IDENTITY column. If you have existing data, yo
u
> can create it with a different name, INSERT the existing data, drop the
> existing table, then rename the new table. Enterprise Manager will do this
> for you if you add the property in the table designer.
> --
> HTH. Ryan
> "krygim" <krygim@.hotmail.com> wrote in message
> news:OccfMRAIGHA.2012@.TK2MSFTNGP14.phx.gbl...
>
>|||But when I insert the existing data into the new table with the identity
column, how can I make sure that the keys are not changed?
(I am thinking of existing data having integer keys and a lot of unused
numbers due to deletions.)
Ryan wrote:

> You can't add the IDENTITY property to an existing table - you need to
> create a new table with the IDENTITY column. If you have existing data, yo
u
> can create it with a different name, INSERT the existing data, drop the
> existing table, then rename the new table. Enterprise Manager will do this
> for you if you add the property in the table designer.
>|||> You can't add the IDENTITY property to an existing table -
No ,you CAN
create table #t
(
col1 char(1)
)
insert into #t values ('a')
insert into #t values ('b')
insert into #t values ('c')
alter table #t add col2 int identity(1,1)
select * from #t
Actually you cannot EDIT/ALTER an IDENTITY property
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:es%23Y6YAIGHA.2628@.TK2MSFTNGP15.phx.gbl...
> You can't add the IDENTITY property to an existing table - you need to
> create a new table with the IDENTITY column. If you have existing data,
> you
> can create it with a different name, INSERT the existing data, drop the
> existing table, then rename the new table. Enterprise Manager will do this
> for you if you add the property in the table designer.
> --
> HTH. Ryan
> "krygim" <krygim@.hotmail.com> wrote in message
> news:OccfMRAIGHA.2012@.TK2MSFTNGP14.phx.gbl...
>|||My understanding of the question was to add the identity property to an
existing INT column, admitidly my opening sentance should have read :-
You can't add the IDENTITY property to an existing column
rather than
You can't add the IDENTITY property to an existing table -
Apologies for any mis-understanding. Either way KM now has an answer to his
question.
HTH. Ryan
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eiljmwAIGHA.1388@.TK2MSFTNGP11.phx.gbl...
> No ,you CAN
> create table #t
> (
> col1 char(1)
> )
> insert into #t values ('a')
> insert into #t values ('b')
> insert into #t values ('c')
> alter table #t add col2 int identity(1,1)
> select * from #t
>
> Actually you cannot EDIT/ALTER an IDENTITY property
>
> "Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:es%23Y6YAIGHA.2628@.TK2MSFTNGP15.phx.gbl...
>|||Ryan,
In the Enterprise Manager, I can create an int column with non-contiguous
(and even duplicated) values in different records, e.g., 1, 31, 1, 7 in 4
records. (Please note that the values are not in ascending order and two of
them are duplicated.) Then later, I can change the column to an identity
column without any value change. Thus I am sure that rather than dropping
and recreating the column, the Enterprise Manager is doing it in another
way. Please correct me know if I am wrong.
KM
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:es%23Y6YAIGHA.2628@.TK2MSFTNGP15.phx.gbl...
> You can't add the IDENTITY property to an existing table - you need to
> create a new table with the IDENTITY column. If you have existing data,
you
> can create it with a different name, INSERT the existing data, drop the
> existing table, then rename the new table. Enterprise Manager will do this
> for you if you add the property in the table designer.
> --
> HTH. Ryan
> "krygim" <krygim@.hotmail.com> wrote in message
> news:OccfMRAIGHA.2012@.TK2MSFTNGP14.phx.gbl...
type
>|||Hi Ferdinand,
To your question:
You should Set identity_insert on on the new table with the identity
before you insert the data.
The Set will last for the session or until you set identity_insert off.
Example:
Create table #source (cola int, colb int)
insert into #source values (1,2)
insert into #source values (3,4)
Create table #dest (cola int identity, colb int)
set identity_insert #dest on
Insert into #dest (cola,colb)
Select cola, colb
From #source
Select *
from #dest
-- drop table #dest
-- drop table #source
"Ferdinand Zaubzer" wrote:

> But when I insert the existing data into the new table with the identity
> column, how can I make sure that the keys are not changed?
> (I am thinking of existing data having integer keys and a lot of unused
> numbers due to deletions.)
>
> Ryan wrote:
>
>|||Run the SQL Profiler as the EM makes the change. You'll be amazed.
ML
http://milambda.blogspot.com/|||> Thus I am sure that rather than dropping
> and recreating the column, the Enterprise Manager is doing it in another
> way. Please correct me know if I am wrong.
>
Enterprise Manager create a new table, loads data from the old table and
them drops the old table to implement this change. You can verify this with
a Profiler trace or by clicking the 'save change script' button. The end
result is as if the identity property was simply added to the column but the
actual technique used is more involved.
Hope this helps.
Dan Guzman
SQL Server MVP
"Krygim" <krygim@.hotmail.com> wrote in message
news:OrPh5wBIGHA.2912@.tk2msftngp13.phx.gbl...
> Ryan,
> In the Enterprise Manager, I can create an int column with non-contiguous
> (and even duplicated) values in different records, e.g., 1, 31, 1, 7 in 4
> records. (Please note that the values are not in ascending order and two
> of
> them are duplicated.) Then later, I can change the column to an identity
> column without any value change. Thus I am sure that rather than dropping
> and recreating the column, the Enterprise Manager is doing it in another
> way. Please correct me know if I am wrong.
> KM
>
> "Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:es%23Y6YAIGHA.2628@.TK2MSFTNGP15.phx.gbl...
> you
> type
>

Change URL

Can any body help me i need to chanage my URL when select my TPM and TPK

i have two option in my Filter 1.TPM

2.TPK

i writen some custome code here but its not working i know some thing wrong in that

its my Code here:-

Public Function access(ByVal parameter As Object) As String

IF parameter Is "TPM" Then

Response.Write("<script language=""Javascript"">window.open ('//localhost/Reportserver/Pages/ReportViewer.aspx?%2fTPM%2fAFM1&rs:Command=Render&rc:Toolbar=true&RUNDATE=" & Parameters!TPM.Value & ' ,'mywindow','location=1,status=1,resizable=yes,scrollbars=1,width=400,height=400');</script>")

ElseIF parameter Is "TPK" Then

Response.Write"<scriptlanguage="javapt"">window.open //localhost/Reportserver/Pages/ReportViewer.aspx?%2fTPM%2fTPM01&rs:Command=Render&rc:Toolbar=true&TPK=" & Parameters!TPK.Value & " ','_blank','location=1,status=1,resizable=yes,scrollbars=1,width=400,height=400');</script>")

End If

End Function

Normal Url is working properly actual problem is i want to change URL depends on parameter selection.Custome code is giving some error like

Error: Response is not declared......

when i written custome code i will declare my parameter=code.access(parameter!sao.value) ? Is it Currect?

Can anybody tell me how to do this one because i really struck here only..

Thanking you.

Your code assumes that you are running in the context of an ASPX page. This isn't the case. The Response object is never declared. So the error message is correct.

|||

Wouldn't it be better if u use the reportview control and embed the report in ur aspx page?
Patrick

|||

thanx for your reply

can u give me some example code.Its actually Report Project only i shoudt use aspx pages

where can i change my custom code can u give me some any example

thanx

kiran

|||

Try reading through this website at:-
http://www.gotreportviewer.com/

|||

it ok that site.Its good

But i didt use any aspx pages in my reports i am using only Business intelligece through i developing my reports only i shoudt use aspx pages any where

i am developing only reports only.

in my report Two Filter i need to change my Url dynamically .This is Report1:

Reortid report Name PD KF TK

1 report1 1 0 0

2 report2 2 2 3

3 report3 2 1 0

Actually Here No need to change anydata Here just change my URL only what ever i select that particual option in my filter

in my filter On option is Change Url filter

in two option in TPM and TPK

This Filter is not related to anything data

this filter job is only change the URL sonly.Suppose i will select TPM option it wil come that related URL only.

when i press PD Data it will take hyper like goes to another report ok

i will select TPK option it will change my URL that related URL only.

i am giving one example:

if(value=TPM)

-its going to what ever declare here jump URL

else if (value=TPK)

-its going to what ever declare here jump URL

end

i dont know how to do this task.can any body

thanx