Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 4th, 2005, 08:46 AM
Registered User
 
Join Date: Apr 2005
Location: , , .
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default identity column

I have exported the tables in the database from my pc to other server by using enterprise manager tool DTS program.
after exporting the tables i found that the columns originally with int IDENTITY were appearing as simple ints, without the identity tag.

i then tried to alter table with the following syntax.

alter table tbl
alter column "a" int identity(1,1)

it gives error "incorrect syntax near identity

pls help as to what will be the right query.
Thanks.
Rohit Sant.

Reply With Quote
  #2 (permalink)  
Old June 5th, 2005, 03:39 AM
Imar's Avatar
Wrox Author
Points: 72,038, Level: 100
Points: 72,038, Level: 100 Points: 72,038, Level: 100 Points: 72,038, Level: 100
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,080
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

I am not sure if you can do this with an ALTER table statement.

I tried this out by changing a column in an existing table and then looked at the change script that the Sql Server Enterprise Manager suggested. That script creates a new table with the new columns, copies the data in the new table and drops the old table.

If you want to try this out yourself: make a change to a table in EM and then click the Save Script icon....

HtH,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Reply With Quote
  #3 (permalink)  
Old June 5th, 2005, 11:57 PM
Registered User
 
Join Date: Apr 2005
Location: , , .
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

i have a database prepared at my side. i have to put it on server side. i did that through DTS facility. all tables got successfully copied with the data. only thing remained is that the identity fields with int datatype were just int datatypes at client side without the identity property. on my side everything is correct. what should i do so that on the client side i make the fields as identity ones sitting from my end. i hope you got what i meant to say.

is there any way through EM, by which i can access the database of client from my pc and make necessary changes in client's database tables.

thanks.


Reply With Quote
  #4 (permalink)  
Old June 6th, 2005, 12:25 AM
Friend of Wrox
Points: 4,332, Level: 27
Points: 4,332, Level: 27 Points: 4,332, Level: 27 Points: 4,332, Level: 27
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2003
Location: , NJ, USA.
Posts: 1,348
Thanks: 0
Thanked 5 Times in 5 Posts
Default

If you use Copy SQL Server Objects task.. The identity column will stay intact.

Reply With Quote
  #5 (permalink)  
Old June 6th, 2005, 08:27 AM
Registered User
 
Join Date: Apr 2005
Location: , , .
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

thanks,both worked.

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Identity Column mike_remember SQL Server 2000 3 July 9th, 2007 07:12 PM
checking column is either identity or not. g_vamsi_krish SQL Server 2000 2 March 17th, 2006 12:38 PM
getting identity column from the table g_vamsi_krish SQL Server 2000 1 March 15th, 2006 05:05 PM
Identity column jbenson001 SQL Server 2000 5 April 12th, 2005 02:01 PM



All times are GMT -4. The time now is 10:02 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.