Alter existing table to add a IDENTITY column

Hi this is my first visit to the MSSQL forum with a question.

Let me explain the scenario,

I have a table say clients table with the structure like id,foo,etc.. and lots of records on it. But the issue is this id column is not an IDENTITY column.
But the values for the Id column dont repeat since it has handled from the application level.Now I need to change this id column as an IDENTITY column with out loosing the records on the table.

If any of you can guide me over this problem, its highly appreciated.
Thanks.Hi
you have 2 choice.

1) Alter table change the column propertyes to identity column but i don't remember if this option keep old id value. you must try.
2) Copy all data of table in another table.
Change the column property Generate an insert statement from the copied dato to new table . Before you execute the insert statement you mast write SET Identity insert ON for the destioantion table.
When you finished execute Set Identity insert On

Hi|||Hi Ajaxrand,

If you have access to enterprise manager, you can browse to the table, right click => design.

In the definition of the table, select the column, set identity on and seed value to the last existing value in the table. Do save.

Next record inserted into to the table will have the next identity column auto incremented.

Obviously take a copy of the table fist and test before changing in the production environment :)

Regards Purple|||

Quote:

Originally Posted by

If you have access to enterprise manager, you can browse to the table, right click => design.

In the definition of the table, select the column, set identity on and seed value to the last existing value in the table. Do save.

I could reach to this step Mytable >> Design Table
But There is nothing called set identity on or seed the value foo bar.|||Hi ajaxrand,

highlight the column (actually a row in this presentation) you are interested in by left clicking the grey square to the left of the column name. With the row highlighted the column detail will be shown in the bottom half of the window with all the things you need.

Regards Purple|||

Quote:

Originally Posted by Purple

Hi ajaxrand,

highlight the column (actually a row in this presentation) you are interested in by left clicking the grey square to the left of the column name. With the row highlighted the column detail will be shown in the bottom half of the window with all the things you need.

Regards Purple

Gotcha, Thanks purple.

I have another Quiz. the Original table is coming from MsAccess and i converted it to MSSQL using import export utilty.
There were nearly 2000 records in the table.with the changes i made to the Table structure (Identity Column) will it change those values in the table.|||Hi Ajaxrand,

as long as you don't change the datatype the identity changes we discussed will not change the data in that column on the table.

I would always suggest to run it in a development environment and check it out first anyway..

Good luck !

Purple|||Hi,

Thanks Purple.
Thanks gpinetto

Regards,
-Ajaxrand

Allow Null Value

Hi,
A given column of my Report (reporting services 2005) contains clients or
null values. I want the user to be able to chose one or more clients for the
parameter, and than show only those records of this client. I also want to
be able to chose "NULL", which will show the records with the null-values.
Also: When the user selects "(select all)" it should show not only those
with a client, but also those with a null value.
How do I have to do this? I tried with adding a Null-value row in my
parameter DataSet, but that didn't work. I also can't set the "Allow Null
Value" for my parameter ("The properties of the currently selected item are
not valid. Please correct all errors before continuing").
Does anybody know how to do this?
Thanks a lot in advance,
Pieterset your data source for the client list to:
select clientId, clientName (or whatever it is)
from clientTable
union select 0, 'All Clients'
union select -1, 'Blank Client'
order by 1
you query needs to take into account the magic values '0' and '-1'.
-T
"Pieter Coucke" <pietercoucke@.hotmail.com> wrote in message
news:uc7DB%23XfGHA.5104@.TK2MSFTNGP04.phx.gbl...
> Hi,
> A given column of my Report (reporting services 2005) contains clients or
> null values. I want the user to be able to chose one or more clients for
> the parameter, and than show only those records of this client. I also
> want to be able to chose "NULL", which will show the records with the
> null-values. Also: When the user selects "(select all)" it should show not
> only those with a client, but also those with a null value.
> How do I have to do this? I tried with adding a Null-value row in my
> parameter DataSet, but that didn't work. I also can't set the "Allow Null
> Value" for my parameter ("The properties of the currently selected item
> are not valid. Please correct all errors before continuing").
> Does anybody know how to do this?
> Thanks a lot in advance,
> Pieter
>

Allow broadcast from CLR Stored Procedure without using permission set 'Unsafe'

Hello,

I develop a database that notifies clients when data changes by sending an UDP broadcast message using an extended stored procedure. Now I want to use a CLR stored procedure to send the UDP broadcast instead:

using System;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.Net.Sockets;

public partial class UserDefinedFunctions

{

[SqlProcedure]

public static void UdpSend(SqlString address, SqlInt32 port, SqlString message)

{

System.Net.Sockets.UdpClient client = new System.Net.Sockets.UdpClient();

byte[] datagram = message.GetUnicodeBytes();

client.Send(datagram, datagram.Length, (string)address, (int)port);

}

};

I have found that to be allowed to send to 255.255.255.255 I must give the assembly permission set 'Unsafe'. If I change to 'External access' I get:

Msg 6522, Level 16, State 1, Procedure UdpSend, Line 0

A .NET Framework error occurred during execution of user defined routine or aggregate 'UdpSend':

System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

System.Security.SecurityException:

at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)

at System.Security.CodeAccessPermission.Demand()

at System.Net.Sockets.Socket.CheckSetOptionPermissions(SocketOptionLevel optionLevel, SocketOptionName optionName)

at System.Net.Sockets.UdpClient.CheckForBroadcast(IPAddress ipAddress)

at System.Net.Sockets.UdpClient.Send(Byte[] dgram, Int32 bytes, String hostname, Int32 port)

at UserDefinedFunctions.UdpSend(SqlString address, SqlInt32 port, SqlString message)

I cannot use permission set 'Unsafe' in production environment, so what I want is to customize the effective permissions with higher resoloution than the three pre-defined permission sets 'Safe', 'External access' and 'Unsafe'. Except from what is allowed by 'Safe' I only want the permissions necessary to send an UDP broadcast.

Anyone who has something like this ?

No, you can not alter any of the existing permission sets. What you can do is, by using CAS, further restricting what a permission set can do by setting (for that assembly) specific CAS policies. However, you still have to create the assembly with whatever permission set, that is required.

Niels
|||You can't modify the built-in SQL CLR permission sets, but you can restrict your assembly's permission grant by using assembly-level permission attributes. However, your assembly would still need to be deployed at the UNSAFE level in order to be granted the SecurityPermission\UnmanagedCode permission that it needs.