If Exists Schema Fails

Could someone explain why

IF SCHEMA_ID('TestSchema') is nul CREATE SCHEMA TestSchema

Fails with Incorrect syntax near the keyword 'SCHEMA'.

Where as running the following statements

CREATE SCHEMA TestSchema

and

IF SCHEMA_ID('TestSchema') is null SELECT 1

succeeds without a problem.

Thanks

_GJK

Ok. I found the solution from

http://groups.google.com/group/microsoft.public.sqlserver.newusers/browse_thread/thread/69a4e9c45d045120/77c693f5043fbddc?lnk=st&q=schema_id&rnum=1#77c693f5043fbddc

|||

Actually the real root of your problem is that CREATE SCHEMA must be the first statement in a batch. Using dynamic SQL (as suggested in the link above) is a workaround as the dynamic statement itself is considered a new batch.

-Raul Garcia

SDE/T

SQL Server Engine

Identity field fix

I inherited a system with a SQL 2000 DB. We discovered an identity field named barcode with some values that are incorrect. About 1000 of the records contain a barcode field with 13 digits, not forteen as required. This field is a standalone field only used on an ID card. I would like to select those 1000 records and update the barcode field to 14 digits. Is there an easy way to do this? Thx

First, check for tables with a Foreign key relationship to the table under consideration.

In each of the tables with a FK, set the table to CASCADE UPDATES for the FK field.

Then set IDENTITY INSERT ON for the primary table. [ SET IDENTITY INSERT MyTable ON ]

When you complete the corrections, set the IDENTITY INSERT off, and remove the CASCADE UPDATES on the FK fields.

Any other tables that also may have the values from the primary table will have to be manually discovered and corrected.

|||

Hi Arnie

Surely you'd have to remove the IDENTITY property from the column before performing any updates?

The example below doesn't allow updates to an IDENTITY column, failing with the following error:

Msg 8102, Level 16, State 1, Line 3

Cannot update identity column 'ID'.

Thanks
Chris

USE [tempdb]

GO

CREATE TABLE dbo.Test

(

ID INT IDENTITY NOT NULL,

MyField VARCHAR(9)

)

INSERT INTO dbo.Test (MyField)

VALUES ('TestValue')

SET IDENTITY_INSERT dbo.Test ON

UPDATE dbo.Test

SET ID = 2

SET IDENTITY_INSERT dbo.Test OFF

|||

Thanks Chris for catching that (posted before coffee...)

I left out that for this approach to work, it will be necessary to move all of the rows to be updated to a temp table, delete them from the primary table, add another column to the temp table, make the updates to the new column, then with IDENTITY_INSERT ON, add those columns back to the primary table -specifying the new column in place of the original ID column.

IF there were secondary tables with FK relationships, use the temp table to update the FK column in the secondary tables.

|||

Thx, for the suggestions. How about this senario:

I made a Test copy of the DB using the SQL Wizard Export. The Test DB did not contain the Identity property on the Barcode field like the LIVE DB and thus allows me to run a fix for the 1000 records that have a bad barcode field. Once fixed I am thinking of renaming the Test DB to LIVE.

I think Something like the following will solve the problem:

Update Person

Set Barcode = (Barcode + 1000000)

where Barcode < 3001000 (this identitfies the 1000 bad records)

TIA

Identity field fix

I inherited a system with a SQL 2000 DB. We discovered an identity field named barcode with some values that are incorrect. About 1000 of the records contain a barcode field with 13 digits, not forteen as required. This field is a standalone field only used on an ID card. I would like to select those 1000 records and update the barcode field to 14 digits. Is there an easy way to do this? Thx

First, check for tables with a Foreign key relationship to the table under consideration.

In each of the tables with a FK, set the table to CASCADE UPDATES for the FK field.

Then set IDENTITY INSERT ON for the primary table. [ SET IDENTITY INSERT MyTable ON ]

When you complete the corrections, set the IDENTITY INSERT off, and remove the CASCADE UPDATES on the FK fields.

Any other tables that also may have the values from the primary table will have to be manually discovered and corrected.

|||

Hi Arnie

Surely you'd have to remove the IDENTITY property from the column before performing any updates?

The example below doesn't allow updates to an IDENTITY column, failing with the following error:

Msg 8102, Level 16, State 1, Line 3

Cannot update identity column 'ID'.

Thanks
Chris

USE [tempdb]

GO

CREATE TABLE dbo.Test

(

ID INT IDENTITY NOT NULL,

MyField VARCHAR(9)

)

INSERT INTO dbo.Test (MyField)

VALUES ('TestValue')

SET IDENTITY_INSERT dbo.Test ON

UPDATE dbo.Test

SET ID = 2

SET IDENTITY_INSERT dbo.Test OFF

|||

Thanks Chris for catching that (posted before coffee...)

I left out that for this approach to work, it will be necessary to move all of the rows to be updated to a temp table, delete them from the primary table, add another column to the temp table, make the updates to the new column, then with IDENTITY_INSERT ON, add those columns back to the primary table -specifying the new column in place of the original ID column.

IF there were secondary tables with FK relationships, use the temp table to update the FK column in the secondary tables.

|||

Thx, for the suggestions. How about this senario:

I made a Test copy of the DB using the SQL Wizard Export. The Test DB did not contain the Identity property on the Barcode field like the LIVE DB and thus allows me to run a fix for the 1000 records that have a bad barcode field. Once fixed I am thinking of renaming the Test DB to LIVE.

I think Something like the following will solve the problem:

Update Person

Set Barcode = (Barcode + 1000000)

where Barcode < 3001000 (this identitfies the 1000 bad records)

TIA