Copy database fails because of invalid object name in a view?

I'm getting the below error when attempting to copy a database. The view works just fine but the package keeps giving me the below error:

07 3:26:37 PM,4/11/2007 3:26:37 PM,0,0x,ERROR : errorCode=-1073548784 description=Executing the query "
CREATE VIEW [app].[vwbaseTransfer]
AS
Select a.app_id, a.app_year, u.first_name, u.middle_name, u.last_name, app_phoneAM, email, CONVERT(varchar(10), app_dob, 101) as app_dob, app_citizen,
CASE WHEN app_untilDate > GetDate() Then app_addr1 ELSE app_pAddr1 END AS app_addr1,
CASE WHEN app_untilDate > GetDate() Then app_addr2 ELSE app_pAddr2 END AS app_addr2,
CASE WHEN app_untilDate > GetDate() Then app_addr3 ELSE app_pAddr3 END AS app_addr3,
CASE WHEN app_untilDate > GetDate() Then app_addr3 ELSE app_pAddr3 END AS app_addr4,
CASE WHEN app_untilDate > GetDate() Then app_city ELSE app_pcity END AS app_city,
CASE WHEN app_untilDate > GetDate() Then app_state ELSE app_pstate END AS app_state,
CASE WHEN app_untilDate > GetDate() Then app_zipcode ELSE app_pzipcode END AS app_zipcode,
app_sex, app_racecode, app_accept_letter, app_acceptDate, app_orientation_date, app_ssn
FROM applicant a inner join uop_user u on u.id = a.app_id
where app_accept_letter is not null and app_acceptDate is not null

" failed with the following error: "Invalid object name 'applicant'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}
StackTrace: at Microsoft.SqlServer.Management.Dts.DtsTransferProvider.ExecuteTransfer()
at Microsoft.SqlServer.Management.Smo.Transfer.TransferData()

Just out of curiosity, is applicant in a different schema than "app"?|||Same schema|||Maybe it is trying to copy the view before the applicant table?|||

jwelch wrote:

Maybe it is trying to copy the view before the applicant table?

Ooooo, I like that thought!|||That's not good. Anyone tried this before?|||Should I assume that this will not work and manually copy databases?|||

Bogey1 wrote:

Should I assume that this will not work and manually copy databases?

What task are you using?

Also, isn't there a better method of "copying" databases? (That is, a backup/restore process.) Are you going for just the schema, or data as well?|||

Bogey1 wrote:

That's not good. Anyone tried this before?

If you feel like wading through it, you could check your sys.sql_dependencies view to see if it has the dependency between the view and the table. Not sure if that is used to determine the order when db objects are copied, but it might be worth a shot.

|||

I'm using the copy database task. If I do a backup and restore then I manually must reset the logins, correct. I was hoping that a simple copy database would take care of this.

thanks.

Copy Database fails

Hi,

I am getting the error below when I try to copy a database.

OnError,SQLSERVER2005,AUTORIDADE NT\SYSTEM,XX_XX_XX_XX_SQLSERVER2005_Transfer Objects Task,{E2404B46-E96F-47DA-91F7-ACBB914BD89D},{6D380D9F-9A9E-42F6-AE14-B8852DF6B8E9},16/6/2007 22:24:02,16/6/2007 22:24:02,0,0x,ERROR : errorCode=0 description=CREATE DATABASE failed. Some file names listed could not be created. Check related errors. helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}
StackTrace: em Microsoft.SqlServer.Management.Dts.DtsTransferProvider.ExecuteTransfer()
em Microsoft.SqlServer.Management.Smo.Transfer.TransferData()
em Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.TransferDatabasesUsingSMOTransfer()

I am using SQL Server 2005 SP2 (9.0.3042) with Windows XP SP2 inside a virtual machine (Virtual PC 2007 version 6.0.156.0).

I am copying the database to a local drive (C:\) and the error occurs after 45 minutes.

Using SQL Server 2000 takes 40 minutes to copy the same database without problem.

Do you have enough disk space on the virtual hard drive and your physical hard drive?|||

Yes, there is enough space both on the virtual and physical hard drive.

Copy a row in SQL SERVER 2000

Hello all,
I'd like to INSERT a new row based upon a SELECT of an existing row,
I've tried the below but there's a syntax error:
INSERT INTO TableA VALUES (SELECT * FROM TableA WHERE TableA.PK = 13185)
Also, would this work as it will try to INSERT a field with a
duplicate PK. Any suggesting on how this could be best achived?
Thanks,
JYJon
You almost got it
INSERT INTO TableA SELECT * FROM TableA WHERE TableA.PK = 13185
> Also, would this work as it will try to INSERT a field with a
> duplicate PK. Any suggesting on how this could be best achived?
No it would not. Add an IDENTITY property as a surrogate key
"Jon" <JonMYates@.gmail.com> wrote in message
news:1178702349.126759.189880@.l77g2000hsb.googlegroups.com...
> Hello all,
> I'd like to INSERT a new row based upon a SELECT of an existing row,
> I've tried the below but there's a syntax error:
> INSERT INTO TableA VALUES (SELECT * FROM TableA WHERE TableA.PK => 13185)
> Also, would this work as it will try to INSERT a field with a
> duplicate PK. Any suggesting on how this could be best achived?
> Thanks,
> JY
>|||On 9 May, 10:19, Jon <JonMYa...@.gmail.com> wrote:
> Hello all,
> I'd like to INSERT a new row based upon a SELECT of an existing row,
> I've tried the below but there's a syntax error:
> INSERT INTO TableA VALUES (SELECT * FROM TableA WHERE TableA.PK => 13185)
> Also, would this work as it will try to INSERT a field with a
> duplicate PK. Any suggesting on how this could be best achived?
> Thanks,
> JY
Just leave out VALUES and the brackets:
INSERT INTO TableA (col1, col2, col3)
SELECT col1, col2, col3 FROM ...
It makes no sense to duplicate an entire row. You need to supply new
values for at least the key column(s) of the table in question.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||INSERT INTO tabelename (the table you want to copy into)
SELECT * from tablename (the table you want to copy from)
WHERE Field10 = (some condition)

Copy a row in SQL SERVER 2000

Hello all,
I'd like to INSERT a new row based upon a SELECT of an existing row,
I've tried the below but there's a syntax error:
INSERT INTO TableA VALUES (SELECT * FROM TableA WHERE TableA.PK =
13185)
Also, would this work as it will try to INSERT a field with a
duplicate PK. Any suggesting on how this could be best achived?
Thanks,
JY
Jon
You almost got it
INSERT INTO TableA SELECT * FROM TableA WHERE TableA.PK = 13185

> Also, would this work as it will try to INSERT a field with a
> duplicate PK. Any suggesting on how this could be best achived?
No it would not. Add an IDENTITY property as a surrogate key
"Jon" <JonMYates@.gmail.com> wrote in message
news:1178702349.126759.189880@.l77g2000hsb.googlegr oups.com...
> Hello all,
> I'd like to INSERT a new row based upon a SELECT of an existing row,
> I've tried the below but there's a syntax error:
> INSERT INTO TableA VALUES (SELECT * FROM TableA WHERE TableA.PK =
> 13185)
> Also, would this work as it will try to INSERT a field with a
> duplicate PK. Any suggesting on how this could be best achived?
> Thanks,
> JY
>
|||On 9 May, 10:19, Jon <JonMYa...@.gmail.com> wrote:
> Hello all,
> I'd like to INSERT a new row based upon a SELECT of an existing row,
> I've tried the below but there's a syntax error:
> INSERT INTO TableA VALUES (SELECT * FROM TableA WHERE TableA.PK =
> 13185)
> Also, would this work as it will try to INSERT a field with a
> duplicate PK. Any suggesting on how this could be best achived?
> Thanks,
> JY
Just leave out VALUES and the brackets:
INSERT INTO TableA (col1, col2, col3)
SELECT col1, col2, col3 FROM ...
It makes no sense to duplicate an entire row. You need to supply new
values for at least the key column(s) of the table in question.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||INSERT INTO tabelename (the table you want to copy into)
SELECT * from tablename (the table you want to copy from)
WHERE Field10 = (some condition)

Copy a row in SQL SERVER 2000

Hello all,
I'd like to INSERT a new row based upon a SELECT of an existing row,
I've tried the below but there's a syntax error:
INSERT INTO TableA VALUES (SELECT * FROM TableA WHERE TableA.PK =
13185)
Also, would this work as it will try to INSERT a field with a
duplicate PK. Any suggesting on how this could be best achived?
Thanks,
JYJon
You almost got it
INSERT INTO TableA SELECT * FROM TableA WHERE TableA.PK = 13185

> Also, would this work as it will try to INSERT a field with a
> duplicate PK. Any suggesting on how this could be best achived?
No it would not. Add an IDENTITY property as a surrogate key
"Jon" <JonMYates@.gmail.com> wrote in message
news:1178702349.126759.189880@.l77g2000hsb.googlegroups.com...
> Hello all,
> I'd like to INSERT a new row based upon a SELECT of an existing row,
> I've tried the below but there's a syntax error:
> INSERT INTO TableA VALUES (SELECT * FROM TableA WHERE TableA.PK =
> 13185)
> Also, would this work as it will try to INSERT a field with a
> duplicate PK. Any suggesting on how this could be best achived?
> Thanks,
> JY
>|||On 9 May, 10:19, Jon <JonMYa...@.gmail.com> wrote:
> Hello all,
> I'd like to INSERT a new row based upon a SELECT of an existing row,
> I've tried the below but there's a syntax error:
> INSERT INTO TableA VALUES (SELECT * FROM TableA WHERE TableA.PK =
> 13185)
> Also, would this work as it will try to INSERT a field with a
> duplicate PK. Any suggesting on how this could be best achived?
> Thanks,
> JY
Just leave out VALUES and the brackets:
INSERT INTO TableA (col1, col2, col3)
SELECT col1, col2, col3 FROM ...
It makes no sense to duplicate an entire row. You need to supply new
values for at least the key column(s) of the table in question.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||INSERT INTO tabelename (the table you want to copy into)
SELECT * from tablename (the table you want to copy from)
WHERE Field10 = (some condition)

converting varchar to smallmoney

Hi
My ticket engine stores values in varchar. The sql db-field that
corresponds was created as smallmoney.
The below statement works for conversion of "leavedays" if the given
value is entered without any decimal places (E.G. 4)
As soon as a user enters a value that includes decimal places (E.G.
4.5) the conversion will not work. In this case the value 4.5 is
rounded to 5.
What do i have to do to convert the value as it is entered by the user?
Thanks in advance
t.
Statement:
INSERT INTO leavereq (mitarbeiter, startdate, enddate, leavedays,
remainingdays, approvedby, approvedon) SELECT {0} , convert(datetime,
{1}) , convert(datetime, {2}), convert(numeric, {3}), convert(numeric,
{4}),{5}, getdate()
DDL for concerned database:
CREATE TABLE [dbo].[leavereq] (
[mitarbeiter] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[startdate] datetime NULL,
[enddate] datetime NULL,
[leavedays] smallmoney NULL,
[remainingdays] smallmoney NULL,
[approvedon] datetime NULL,
[approvedby] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
ON [PRIMARY]
GO
Why are you converting to numeric when the data type on the table is
smallmoney? I suppose that it would properly if you declare the precision
and scale, but you aren't doing that so the insert fails.
It would be easier (and more correct and less confusing) to perform a
CONVERT(smallmoney,x) within your insert
where x is the value of the data that you are trying to insert.
Are you not using stored procedures to insert the data?
Keith Kratochvil
<thomas@.williams-mail.ch> wrote in message
news:1160570224.519187.73680@.e3g2000cwe.googlegrou ps.com...
> Hi
> My ticket engine stores values in varchar. The sql db-field that
> corresponds was created as smallmoney.
> The below statement works for conversion of "leavedays" if the given
> value is entered without any decimal places (E.G. 4)
> As soon as a user enters a value that includes decimal places (E.G.
> 4.5) the conversion will not work. In this case the value 4.5 is
> rounded to 5.
> What do i have to do to convert the value as it is entered by the user?
> Thanks in advance
>
> t.
>
> Statement:
> INSERT INTO leavereq (mitarbeiter, startdate, enddate, leavedays,
> remainingdays, approvedby, approvedon) SELECT {0} , convert(datetime,
> {1}) , convert(datetime, {2}), convert(numeric, {3}), convert(numeric,
> {4}),{5}, getdate()
> DDL for concerned database:
> CREATE TABLE [dbo].[leavereq] (
> [mitarbeiter] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [startdate] datetime NULL,
> [enddate] datetime NULL,
> [leavedays] smallmoney NULL,
> [remainingdays] smallmoney NULL,
> [approvedon] datetime NULL,
> [approvedby] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> )
> ON [PRIMARY]
> GO
>

converting varchar to smallmoney

Hi
My ticket engine stores values in varchar. The sql db-field that
corresponds was created as smallmoney.
The below statement works for conversion of "leavedays" if the given
value is entered without any decimal places (E.G. 4)
As soon as a user enters a value that includes decimal places (E.G.
4.5) the conversion will not work. In this case the value 4.5 is
rounded to 5.
What do i have to do to convert the value as it is entered by the user?
Thanks in advance
t.
Statement:
INSERT INTO leavereq (mitarbeiter, startdate, enddate, leavedays,
remainingdays, approvedby, approvedon) SELECT {0} , convert(datetime,
{1}) , convert(datetime, {2}), convert(numeric, {3}), convert(numeric,
{4}),{5}, getdate()
DDL for concerned database:
CREATE TABLE [dbo].[leavereq] (
[mitarbeiter] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[startdate] datetime NULL,
[enddate] datetime NULL,
[leavedays] smallmoney NULL,
[remainingdays] smallmoney NULL,
[approvedon] datetime NULL,
[approvedby] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
ON [PRIMARY]
GOWhy are you converting to numeric when the data type on the table is
smallmoney? I suppose that it would properly if you declare the precision
and scale, but you aren't doing that so the insert fails.
It would be easier (and more correct and less confusing) to perform a
CONVERT(smallmoney,x) within your insert
where x is the value of the data that you are trying to insert.
Are you not using stored procedures to insert the data?
--
Keith Kratochvil
<thomas@.williams-mail.ch> wrote in message
news:1160570224.519187.73680@.e3g2000cwe.googlegroups.com...
> Hi
> My ticket engine stores values in varchar. The sql db-field that
> corresponds was created as smallmoney.
> The below statement works for conversion of "leavedays" if the given
> value is entered without any decimal places (E.G. 4)
> As soon as a user enters a value that includes decimal places (E.G.
> 4.5) the conversion will not work. In this case the value 4.5 is
> rounded to 5.
> What do i have to do to convert the value as it is entered by the user?
> Thanks in advance
>
> t.
>
> Statement:
> INSERT INTO leavereq (mitarbeiter, startdate, enddate, leavedays,
> remainingdays, approvedby, approvedon) SELECT {0} , convert(datetime,
> {1}) , convert(datetime, {2}), convert(numeric, {3}), convert(numeric,
> {4}),{5}, getdate()
> DDL for concerned database:
> CREATE TABLE [dbo].[leavereq] (
> [mitarbeiter] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [startdate] datetime NULL,
> [enddate] datetime NULL,
> [leavedays] smallmoney NULL,
> [remainingdays] smallmoney NULL,
> [approvedon] datetime NULL,
> [approvedby] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> )
> ON [PRIMARY]
> GO
>

converting varchar to smallmoney

Hi
My ticket engine stores values in varchar. The sql db-field that
corresponds was created as smallmoney.
The below statement works for conversion of "leavedays" if the given
value is entered without any decimal places (E.G. 4)
As soon as a user enters a value that includes decimal places (E.G.
4.5) the conversion will not work. In this case the value 4.5 is
rounded to 5.
What do i have to do to convert the value as it is entered by the user?
Thanks in advance
t.
Statement:
INSERT INTO leavereq (mitarbeiter, startdate, enddate, leavedays,
remainingdays, approvedby, approvedon) SELECT {0} , convert(datetime,
{1}) , convert(datetime, {2}), convert(numeric, {3}), convert
(numeric,
{4}),{5}, getdate()
DDL for concerned database:
CREATE TABLE [dbo].[leavereq] (
[mitarbeiter] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[startdate] datetime NULL,
[enddate] datetime NULL,
[leavedays] smallmoney NULL,
[remainingdays] smallmoney NULL,
[approvedon] datetime NULL,
[approvedby] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
ON [PRIMARY]
GOWhy are you converting to numeric when the data type on the table is
smallmoney? I suppose that it would properly if you declare the precision
and scale, but you aren't doing that so the insert fails.
It would be easier (and more correct and less confusing) to perform a
CONVERT(smallmoney,x) within your insert
where x is the value of the data that you are trying to insert.
Are you not using stored procedures to insert the data?
Keith Kratochvil
<thomas@.williams-mail.ch> wrote in message
news:1160570224.519187.73680@.e3g2000cwe.googlegroups.com...
> Hi
> My ticket engine stores values in varchar. The sql db-field that
> corresponds was created as smallmoney.
> The below statement works for conversion of "leavedays" if the given
> value is entered without any decimal places (E.G. 4)
> As soon as a user enters a value that includes decimal places (E.G.
> 4.5) the conversion will not work. In this case the value 4.5 is
> rounded to 5.
> What do i have to do to convert the value as it is entered by the user?
> Thanks in advance
>
> t.
>
> Statement:
> INSERT INTO leavereq (mitarbeiter, startdate, enddate, leavedays,
> remainingdays, approvedby, approvedon) SELECT {0} , convert(datetime,
> {1}) , convert(datetime, {2}), convert(numeric, {3}), conve
rt(numeric,
> {4}),{5}, getdate()
> DDL for concerned database:
> CREATE TABLE [dbo].[leavereq] (
> [mitarbeiter] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [startdate] datetime NULL,
> [enddate] datetime NULL,
> [leavedays] smallmoney NULL,
> [remainingdays] smallmoney NULL,
> [approvedon] datetime NULL,
> [approvedby] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> )
> ON [PRIMARY]
> GO
>

converting the varchar value to int

All,
I am running this statement and getting the error below. I am assuming
that the issue is the CLCCHRGE.CCOUNT data type is Varchar and needs to
be converted using a CONVERT clause or a CASE statement but I am not
sure I am on the right path here and if I am then I am not sure of the
syntax.
SELECT CLCHRGE.CCOUNT, CLCHRGE.CPTPRT, CLCHRGE.XCDATE,
CLMSTER.PLNAME, CLMSTER.PFNAME, procdesc, dignosis
FROM CLCHRGE INNER JOIN
CLMATER ON CLCHRGE.CCOUNT = CLMSTER.CCOUNT
WHERE (CLCHRGE.XACDATE BETWEEN '2005-01-01' AND '2005-1-31')
AND (CLCHRGE.CPTPINT = '90801') AND CLCHRGE.CCOUNT in (608685, 477078,
608201, 204739)
order by xacdate, plname desc
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'C171913' to a column of data
type int.Try changing this
CLCHRGE.CCOUNT in (608685, 477078,
608201, 204739)
to this
CLCHRGE.CCOUNT in ('608685', '477078',
'608201', '204739')
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Success!, Thank you for the tip!! You are a Lifesaver!

Converting table date

I am trying to to take data from a table where the Row data contains 24
columns representing hors 12 midnight to 11 PM see (sample below) into a
table that puts 1 hour per row?
Initial table
Name Date 12Min 1Min 2Min 3Min … 11Min
Mickey 12/2/05 625.4 153.2 84635…
Convbert to another table
Name Date Min
Mickey 12/2/05 00:00:00 625.4
Mickey 12/2/05 01:00:00 153.2
Mickey 12/2/05 02:00:00 84635Use unions.
e.g. (note: while you don't need to dateadd(hour,0,[Date]), but this
will work if the datatype is datetime or varchar)
select Name, dateadd(hour, 0, [Date]) as [Date], 12Min as Min from yourtable
union all
select Name, dateadd(hour, 1, [Date]) as [Date], 1Min as Min from yourtable
union all
...
union all
select Name, dateadd(hour, 11, [Date]) as [Date], 11Min as Min from
yourtable
Jim Abel wrote:
> I am trying to to take data from a table where the Row data contains 24
> columns representing hors 12 midnight to 11 PM see (sample below) into a
> table that puts 1 hour per row?
> Initial table
> Name Date 12Min 1Min 2Min 3Min … 11Min
> Mickey 12/2/05 625.4 153.2 84635…
> Convbert to another table
> Name Date Min
> Mickey 12/2/05 00:00:00 625.4
> Mickey 12/2/05 01:00:00 153.2
> Mickey 12/2/05 02:00:00 84635
>

converting mysql 2 sql 2000

how do i go about converting the Mysql Script below to Sql Server Script ??

CREATE TABLE cat (
id int NOT NULL auto_increment,
name char(20) NOT NULL default '',
PRIMARY KEY (id)
);

INSERT INTO cat VALUES (1,'Not Categorised');

ALTER TABLE cat AUTO_INCREMENT = 5;

i want a script that:
creates the table
inserts a row of data and then
sets the increment field to start at 5

i tried using identity(1,1) and
SET IDENTITY_INSERT cat ON;
INSERT INTO cat VALUES (1,'Not Categorised');

but when i enter the data in i get the error below

An explicit value for the identity column in table 'cat' can only be specified when a column list is used and IDENTITY_INSERT is ON.

please can anyone help or advice

thanksOriginally posted by m.inckle
how do i go about converting the Mysql Script below to Sql Server Script ??

CREATE TABLE cat (
id int NOT NULL auto_increment,
name char(20) NOT NULL default '',
PRIMARY KEY (id)
);

INSERT INTO cat VALUES (1,'Not Categorised');

ALTER TABLE cat AUTO_INCREMENT = 5;

i want a script that:
creates the table
inserts a row of data and then
sets the increment field to start at 5

i tried using identity(1,1) and
SET IDENTITY_INSERT cat ON;
INSERT INTO cat VALUES (1,'Not Categorised');

but when i enter the data in i get the error below

An explicit value for the identity column in table 'cat' can only be specified when a column list is used and IDENTITY_INSERT is ON.

please can anyone help or advice

thanks
create table as:
CREATE TABLE cat (
id int NOT NULL ,
name char(20) NOT NULL default '',
PRIMARY KEY (id)
);
then insert the record:
INSERT INTO cat VALUES (1,'Not Categorised');

then go to the enterprise manager->design table and set the identity property of the table to yes with value 5.|||Originally posted by harshal_in
create table as:
CREATE TABLE cat (
id int NOT NULL ,
name char(20) NOT NULL default '',
PRIMARY KEY (id)
);
then insert the record:
INSERT INTO cat VALUES (1,'Not Categorised');

then go to the enterprise manager->design table and set the identity property of the table to yes with value 5.

is there any way to do this in a script ???|||Originally posted by harshal_in
create table as:
CREATE TABLE cat (
id int NOT NULL ,
name char(20) NOT NULL default '',
PRIMARY KEY (id)
);
then insert the record:
INSERT INTO cat VALUES (1,'Not Categorised');

then go to the enterprise manager->design table and set the identity property of the table to yes with value 5.

is there a way of altering an identity column in a table like below

ALTER TABLE cat ALTER COLUMN id INT NOT NULL identity(5,1);

i always get
Incorrect syntax near the keyword 'identity'.|||Originally posted by m.inckle
is there a way of altering an identity column in a table like below

ALTER TABLE cat ALTER COLUMN id INT NOT NULL identity(5,1);

i always get
Incorrect syntax near the keyword 'identity'.
no. as far as my knowledge goes you can't add identity to an existing column thru alter table, u have to add a new column for it.
so the easiest way is to use enterprise manager.