adding an identity column with out warnings

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

adding an identity column with out warnings

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-01-25 : 08:17:03

Rao Aregaddan writes "Hi All,

Here is my Query.....

While executing the following code,it is showing some warnings.

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
Drop procedure [DBO].[Usp_Extract_OLAP_Data_1]
GO

Create Procedure [DBO].[Usp_Extract_OLAP_Data_1] as

begin

IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID('[DBO].[processed_olap_data_1]') AND OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1)
DROP TABLE [DBO].[processed_olap_data_1]
select * into dbo.processed_olap_data_1 from pubs.dbo.processed_olap_data_1
EXEC ('ALTER TABLE dbo.processed_olap_data_1 ADD generatedid INT IDENTITY')

select * from processed_olap_data_1 order by generatedid

END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
SET ANSI_WARNINGS OFF
GO

Out Put is as follows
----------------------

(141 row(s) affected)

(141 row(s) affected)

Warning: The table 'processed_olap_data_1' has been created but its maximum row size (3873071) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

(141 row(s) affected)

Please guide me on this issue that why iam getting this warning even i set the ansi null and ansi warnings off.
one more thing is that is there any other way to add an
identity column with out using alter statement.and is there any problem with front end guys if it shows like this warnings..."

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-01-25 : 08:36:51

The warning is only telling you that if you tried to put all the data possible into this row, then the insert would fail, I don't think this is an issue with the identity. If you look at your table, you'll find that you either have hundreds of small fields or a few fields with large varchar or text datatypes..

Kristen
Test

22859 Posts

Posted - 2006-01-25 : 08:42:05

As RickD said, but I expect you have a very real problem. Your table is set up to allow data which, if entered in full, would be 3,873,071 bytes in size. The maximum for SQL Server 2000 is 8,060 - so it seems to be to be very UNLIKELY that you will only ever attempt to store less than 8,060 bytes in all records, given the total of all the column specifications.

But if that isn't the case then please ignore!

Kristen

nr
SQLTeam MVY

12543 Posts

Posted - 2006-01-25 : 08:45:52

Instead of
select * into dbo.processed_olap_data_1 from pubs.dbo.processed_olap_data_1
EXEC ('ALTER TABLE dbo.processed_olap_data_1 ADD generatedid INT IDENTITY')

why not
select *, identity(int, 1, 1) as generatedid into dbo.processed_olap_data_1 from pubs.dbo.processed_olap_data_1

Won't get round the warning, which as RickD says is just a warning and won't cause problems unless you try to put more than 8060 bytes into a row.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources