How to change the data type of an IDENTITY column

Hi.
I have an existing table with an INT IDENTITY column; it contains some data
rows.
I want to change the identity column type to smallint (the data values are
within range) but am stumped. Here's what I'm trying to do (assume the
identity column is called Id):
Create tempId smallint column
save Id values in tempId
Drop Id column
Recreate Id column as identity smallint.
*** Update Id column with values from tempId
Drop tempId column
As you know, it fails on the Update step above: cannot update identity
column (I've tried set identity_insert on and that makes no difference - now
where's the set identity_update function...)
Can anyone suggest a way of doing it?
Thanks
Michael
Why are you re-creating the column as an IDENTITY? Just create it as
SMALLINT; you can't have it both ways.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:223A4008-4C07-4AF3-BA56-20D93FA17570@.microsoft.com...
> Hi.
> I have an existing table with an INT IDENTITY column; it contains some
data
> rows.
> I want to change the identity column type to smallint (the data values are
> within range) but am stumped. Here's what I'm trying to do (assume the
> identity column is called Id):
> Create tempId smallint column
> save Id values in tempId
> Drop Id column
> Recreate Id column as identity smallint.
> *** Update Id column with values from tempId
> Drop tempId column
> As you know, it fails on the Update step above: cannot update identity
> column (I've tried set identity_insert on and that makes no difference -
now
> where's the set identity_update function...)
> Can anyone suggest a way of doing it?
> Thanks
> Michael
>
>
|||Adam,
Thanks, but it needs to be an identity column. And I can have it both ways!
I've just found a way to do what I want:
Save the data into a temp table
delete the data
drop the int identity column
recreate the identity column as smallint
identity_insert on
insert rows from temp table
identity_insert off
drop temp table.
A bit long winded, but it works. (I've left out minor detail re. constraints
etc.)
Thanks
Michael
"Adam Machanic" wrote:

> Why are you re-creating the column as an IDENTITY? Just create it as
> SMALLINT; you can't have it both ways.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> news:223A4008-4C07-4AF3-BA56-20D93FA17570@.microsoft.com...
> data
> now
>
>
|||Yes, that works.
Keep in mind that you'll have to insert those rows one-by-one with a cursor
or loop if you want to guarantee the same order.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:419FB142-5006-4418-8C59-CCEAFBD440A8@.microsoft.com...
> Adam,
> Thanks, but it needs to be an identity column. And I can have it both
ways!
> I've just found a way to do what I want:
> Save the data into a temp table
> delete the data
> drop the int identity column
> recreate the identity column as smallint
> identity_insert on
> insert rows from temp table
> identity_insert off
> drop temp table.
> A bit long winded, but it works. (I've left out minor detail re.
constraints
> etc.)
> Thanks
> Michael
>
|||Adam,
Sorry I don't understand.
What do you mean by order and why would this be important to me?
Thanks Michael
"Adam Machanic" wrote:

> Yes, that works.
> Keep in mind that you'll have to insert those rows one-by-one with a cursor
> or loop if you want to guarantee the same order.
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> news:419FB142-5006-4418-8C59-CCEAFBD440A8@.microsoft.com...
> ways!
> constraints
>
>
|||"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:BE612CBA-EA74-492B-B157-30B17F4DB131@.microsoft.com...
> Adam,
> Sorry I don't understand.
> What do you mean by order and why would this be important to me?
Sorry, I was thinking of something else totally unrelated! Disregard.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
sql

How to change the data type of an IDENTITY column

Hi.
I have an existing table with an INT IDENTITY column; it contains some data
rows.
I want to change the identity column type to smallint (the data values are
within range) but am stumped. Here's what I'm trying to do (assume the
identity column is called Id):
Create tempId smallint column
save Id values in tempId
Drop Id column
Recreate Id column as identity smallint.
*** Update Id column with values from tempId
Drop tempId column
As you know, it fails on the Update step above: cannot update identity
column (I've tried set identity_insert on and that makes no difference - now
where's the set identity_update function...)
Can anyone suggest a way of doing it?
Thanks
MichaelWhy are you re-creating the column as an IDENTITY? Just create it as
SMALLINT; you can't have it both ways.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:223A4008-4C07-4AF3-BA56-20D93FA17570@.microsoft.com...
> Hi.
> I have an existing table with an INT IDENTITY column; it contains some
data
> rows.
> I want to change the identity column type to smallint (the data values are
> within range) but am stumped. Here's what I'm trying to do (assume the
> identity column is called Id):
> Create tempId smallint column
> save Id values in tempId
> Drop Id column
> Recreate Id column as identity smallint.
> *** Update Id column with values from tempId
> Drop tempId column
> As you know, it fails on the Update step above: cannot update identity
> column (I've tried set identity_insert on and that makes no difference -
now
> where's the set identity_update function...)
> Can anyone suggest a way of doing it?
> Thanks
> Michael
>
>|||Adam,
Thanks, but it needs to be an identity column. And I can have it both ways!
I've just found a way to do what I want:
Save the data into a temp table
delete the data
drop the int identity column
recreate the identity column as smallint
identity_insert on
insert rows from temp table
identity_insert off
drop temp table.
A bit long winded, but it works. (I've left out minor detail re. constraints
etc.)
Thanks
Michael
"Adam Machanic" wrote:
> Why are you re-creating the column as an IDENTITY? Just create it as
> SMALLINT; you can't have it both ways.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> news:223A4008-4C07-4AF3-BA56-20D93FA17570@.microsoft.com...
> > Hi.
> > I have an existing table with an INT IDENTITY column; it contains some
> data
> > rows.
> > I want to change the identity column type to smallint (the data values are
> > within range) but am stumped. Here's what I'm trying to do (assume the
> > identity column is called Id):
> >
> > Create tempId smallint column
> > save Id values in tempId
> > Drop Id column
> > Recreate Id column as identity smallint.
> > *** Update Id column with values from tempId
> > Drop tempId column
> >
> > As you know, it fails on the Update step above: cannot update identity
> > column (I've tried set identity_insert on and that makes no difference -
> now
> > where's the set identity_update function...)
> >
> > Can anyone suggest a way of doing it?
> > Thanks
> > Michael
> >
> >
> >
>
>|||Yes, that works.
Keep in mind that you'll have to insert those rows one-by-one with a cursor
or loop if you want to guarantee the same order.
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:419FB142-5006-4418-8C59-CCEAFBD440A8@.microsoft.com...
> Adam,
> Thanks, but it needs to be an identity column. And I can have it both
ways!
> I've just found a way to do what I want:
> Save the data into a temp table
> delete the data
> drop the int identity column
> recreate the identity column as smallint
> identity_insert on
> insert rows from temp table
> identity_insert off
> drop temp table.
> A bit long winded, but it works. (I've left out minor detail re.
constraints
> etc.)
> Thanks
> Michael
>|||Adam,
Sorry I don't understand.
What do you mean by order and why would this be important to me?
Thanks Michael
"Adam Machanic" wrote:
> Yes, that works.
> Keep in mind that you'll have to insert those rows one-by-one with a cursor
> or loop if you want to guarantee the same order.
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> news:419FB142-5006-4418-8C59-CCEAFBD440A8@.microsoft.com...
> > Adam,
> > Thanks, but it needs to be an identity column. And I can have it both
> ways!
> > I've just found a way to do what I want:
> > Save the data into a temp table
> > delete the data
> > drop the int identity column
> > recreate the identity column as smallint
> > identity_insert on
> > insert rows from temp table
> > identity_insert off
> > drop temp table.
> >
> > A bit long winded, but it works. (I've left out minor detail re.
> constraints
> > etc.)
> > Thanks
> > Michael
> >
>
>|||"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:BE612CBA-EA74-492B-B157-30B17F4DB131@.microsoft.com...
> Adam,
> Sorry I don't understand.
> What do you mean by order and why would this be important to me?
Sorry, I was thinking of something else totally unrelated! Disregard.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--

How to change monitor server in log shipping?

Is there an easy way to change the server that is
monitoring log shipping, in an existing pair? I need to
remove the log shipping monitor server and put the
monitoring on another machine and would prefer not to have
to rebuild 20 maintenence plans!Hi Dave,
This is a manual process. You might want to test this before implementing
(and make backups of msdb)
1. Script the "Log Shipping Alert - Backup" and "Log Shipping Alert -
Restore" jobs from the current monitor server
2. Run the following query on current monitor server and save the output -
select maintenance_plan_id from msdb.dbo.log_shipping_primaries
select secondary_plan_id from msdb.dbo.log_shipping_secondaries
3. Execute the scripts created in Step 1 on the new monitor server (in MSDB)
4. Execute the following stored proc on the new monitor server -
sp_add_log_shipping_primary
@.primary_server_name = '<primary server name>' ,
@.primary_database_name = '<primary database name>'
,@.maintenance_plan_id = '<maintenance Plan ID from first select command
in Step 2>'
,@.backup_threshold = <backup threshold - should be at least 3 times the
freq of backup job>
,@.threshold_alert = '14420'
,@.threshold_alert_enabled = 1
,@.planned_outage_start_time = 0
,@.planned_outage_end_time = 0
,@.planned_outage_weekday_mask = 0
5. Execute the following select statement to get the Primary_ID -
select primary_id from msdb.dbo.log_shipping_primaries where
primary_server_name ='<primary server name>' and primary_database_name = '<primary database
name>'
6. Execute the following stored procedure on the new monitor server -
sp_add_log_shipping_secondary @.primary_id = <primary id retrieved in Step 5>
, @.secondary_server_name = '<Secondary Server name>'
, @.secondary_database_name = '<secondary database name>'
, @.secondary_plan_id = '<Comes from second select statement in Step 2>'
, @.copy_enabled = 1
, @.load_enabled = 1
, @.out_of_sync_threshold = <out-of-sync threshold should be at least 3
times the freq of copy/restore jobs>
, @.threshold_alert = '14421'
, @.threshold_alert_enabled = 1
, @.planned_outage_start_time = 0
, @.planned_outage_end_time = 0
, @.planned_outage_weekday_mask = 0
7. Execute the following statement on the Primary and secondary servers to
get the current monitor login information. Verify that you have 1 for logon
type before proceeding with step 8. If you do not have this field set to 1,
then for the time being we will change the authentication mode to NT and
then we can change this later on once the monitor is completely migrated -
select * from msdb.dbo.log_shipping_monitor
go
8. Execute the following command on both Primary and all secondary servers
to change the monitor information -
delete from msdb.dbo.log_shipping_monitor
go
sp_define_log_shipping_monitor '<New monitor server name>', 1, NULL, 1
go
9. This should complete the migration. Test that the monitor server icon
thing appears under Management tree (Enterprise Manager). When you click
this Log Shipping Monitor, there should be the new pair that you just
added. Once the jobs are working successfully the last copied/loaded file
field will be updated to reflect the changes.
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.

How to change identity to an existing column?

Hi

can anybody tell , Is it possible to alter existing column to add IDENTITY?

Thanks in advance

Yes

It is possible to add Identity Column to a table already have some data. BUT there is no such a command to alter a column to be an IDENTITY Column. You have to go through the following steps.

1. Create a same table with the IDENTITY COLUMN. but with diffrent Name
2. Move all data to the New Table, with IDENTITY INSERT ON option
3. Drop All constraints from the OLD table
4. Drop the OLD table
5. Rename the new table as the OLD table
6. Attach all constraints to the new table ...

Hope that it will help

|||Thank u akbar

How to change an existing UID to autonumbering?

Hullo!

Doubt it matters too much, but I'll just start off by saying that I'm using C# ASP.NET 2.0 in Visual Studio 2005.

I currently have a UID field entitled "ID", and I'd like to make it autonumbering so that users can input stuff into it through a simple web interface and not have to worry about the user's ID.

Anyone know how I could go about doing this? I can't seem to find a way through VS2005's GUI, and I can't seem to find anything on the internet about doing it through an SQL statement.

Thanks!I may have already figured my own problem out - I just have to test something really quick. If it works, I'll come back and tell ya what I did.

How to change a data file name

Hi All,
Is anyone can help me to change a existing data file into a new new name?
Thanks.The logical name of the file, or the actual path and filename of the .NDF file?|||Logical name.|||Alter database DB modify file (name = 'old_name_of_file', newname = 'new_name_of_file')|||It worked good.
Thank you MCrowley, it's very helpful.

how to call webservice from tsql?

Hello!

From an stored proc I want to call an existing web-service.

How to do that?

Must it be done using CLR-procs or is there some way in TSQL?

Greetings

Bjorn

Are you really need this...

CLR is one of the best way to invoke your web service. But i never tested it.

If you use sql server 2000 then the MSXML2.XMLHTTP object & sp_OACreate sp is one of the solution for this requirement.

|||

Here the sample code,

Declare @.Object as Int;

Declare @.ResponseText as Varchar(8000);

Code Snippet

Exec sp_OACreate 'MSXML2.XMLHTTP', @.Object OUT;

Exec sp_OAMethod @.Object, 'open', NULL, 'get',

'http://www.webservicex.com/stockquote.asmx/GetQuote?symbol=MSFT', --Your Web Service Url (invoked)

'false'

Exec sp_OAMethod @.Object, 'send'

Exec sp_OAMethod @.Object, 'responseText', @.ResponseText OUTPUT

Select @.ResponseText

Exec sp_OADestroy @.Object

|||

Hi!

Sorry not writing, that I'm using SS2005...

Makes this some difference?

/Bjorn

|||

The Query (given below) can be used in both versions...

For SQL Server 2005 you have to enable the "OLE Automation" option.

Start-> Programs - > Microsoft SQL Server 2005 -> Configuration Tools ->SQL Server Surface Area Configuration

Click the link Surface Area Configuration for Features

Expand the DB -> Database Engine

Select OLE Automation & Check the checkbox Enable OLE Automation..

After the settings completed execute the code...

|||

Hi!

I have to use post, but how do I get it to work?

I was told I should look at this:

POST /xyzproxy/alarminsert.asmx HTTP/1.1
Host: 192.168.1.111
Content-Type: text/xml; charset=utf-8
Content-Length: length
SOAPAction: "http://tempuri.org/TestWebService"

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlnsoap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<TestWebService xmlns="http://tempuri.org/" />
</soap:Body>
</soap:Envelope>

How do I send a request like that using TSQL?

/Bjorn

How to call SQL server Reports in VB 6.0

Hi
Present we are using crystal reports 8.5 in VB application .
database is SQL server 2000.
How can convert existing Crystall reports into SQl Server reports and How to
call SQL server Reports in VB 6.0 as like Crystal reports.
Would appreciate if any one gives suggestion on this.
Thanks
VenuThere are two ways. One, implement SOAP in VB and use web services (not
trivial). The other is to embed an IE control in your app and use URL
integration.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"venu gopal" <venu gopal@.discussions.microsoft.com> wrote in message
news:910A2BBE-A706-4B5F-90B3-088B7CDDB7F8@.microsoft.com...
> Hi
> Present we are using crystal reports 8.5 in VB application .
> database is SQL server 2000.
> How can convert existing Crystall reports into SQl Server reports and How
> to
> call SQL server Reports in VB 6.0 as like Crystal reports.
> Would appreciate if any one gives suggestion on this.
> Thanks
> Venu
>

How to call a Store Procedure

I have a stored procedure that creates a normalized table from an existing denorm table. So I just need a simple way to call this SP from an aspx page. It would be good though to know how many records were effected, but this is not a requirement.

Pass the name of the stored procedure to a command object and set the CommandType to StoredProcedure. Then use ExecuteNonQuery to run it.

string query = "nameofsp";
string connect = "connectionstring";
using (SqlConnection conn = new SqlConnection(connect))
{
 using (SqlCommand cmd = new SqlCommand(query, conn))
 {
 cmd.CommandType = CommandType.StoredProcedure;
 conn.Open();
 cmd.ExecuteNonQuery();
 }
}

|||

Thanks for the quick reply.

I should have mentioned I'm building the apsx page with vb. How different will that look from the above?

Also, I have a DTS package that I have scheduled to run every week. But it appears I need to allow the user to also kick that off as needed. Can I use the above to call that? Or do I need to first create a SP that calls the DTS package?

Thanks!

|||

Dim dbobj as new SQLConnection(connection_string_here)

dbobj.open()

Dim objCmd as new SQLCommand(sp_name_here, dbobj)

objCmd.CommandType = CommandType.StoredProcedure

objCmd.Parameters.Add("@.Param_Name_Here", Param_Value_Here) 'if any

objCmd.ExecuteNonQuery() 'Use ExecuteScalar if you want to return a single value

objCmd.Dispose()

dbobj.Close()

I would create the procedure first to run the DTS and call it as above.

|||

tomhirt:

I should have mentioned I'm building the apsx page with vb.

That would have been helpful, but not particularly necessary if you just copy and paste the C# code I gave into www.codechanger.com

tomhirt:

How different will that look from the above?

Almost the same:

Dim query As String = "nameofsp"
Dim connect As String = "connectionstring"
Using conn As New SqlConnection(connect)
Using cmd As New SqlCommand(query, conn)
cmd.CommandType = CommandType.StoredProcedure
conn.Open()
cmd.ExecuteNonQuery()
End Using
End Using

I tend to use Using statements for objects that need to be closed and disposed. Saves me remembering to have to do it explicitly each time.

tomhirt:

Also, I have a DTS package that I have scheduled to run every week. But it appears I need to allow the user to also kick that off as needed. Can I use the above to call that? Or do I need to first create a SP that calls the DTS package?

Thanks!

Like Rich said.

|||

Excellent. I did not know I could use something like Code changer. Great refernce.

How to bring existing Database in standy mode of secondary server for logshippin

Hi,

I am doing a logshipping in sqlserver2000.
I have PrimaryServer database A and Secondaryserver A which is already existing. Now if i am trying to establish logshipping with this existing db in secondaryserver it says db A in destination is not in standy mode.

Please help me how to bring the db in standy mode that can be used to do logshipping.

Thanks
Md S.HassanMy guess would be:

RESTORE DATABASE dbName WITH STANDBY|||Hi Roa

I am getting this error message after trying with your suggestion

Server: Msg 155, Level 15, State 1, Line 1
'STANDBY' is not a recognized RESTORE option.