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

Identifying Virtual Server

I inherited a SQL2005 cluster and while I'm experienced in managing SQL
stand alone I have no experience in a cluster environment. I am trying to
determine the cluster virtual server name, as opposed to instance, so I can
make changes to the service start up configurations. I can't get the utility
to recognize the cluster. How can I determine the proper name to reference?
The instance is the virtual server name. The cluster one is a virtual name
too by the way. @.@.servername
http://msdn2.microsoft.com/en-us/library/aa933172(sql.80).aspx will return
lots of information too.
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Sammi" <again5@.hotmail.com> wrote in message
news:B6852C1F-D37B-485D-ADFB-52500B24C6F5@.microsoft.com...
>I inherited a SQL2005 cluster and while I'm experienced in managing SQL
> stand alone I have no experience in a cluster environment. I am trying to
> determine the cluster virtual server name, as opposed to instance, so I
> can
> make changes to the service start up configurations. I can't get the
> utility
> to recognize the cluster. How can I determine the proper name to
> reference?
>

Identifying Unused Tables

I have inherited support for a database with many used tables. There
isn't any documentation on what is used or dead. I was hoping to run
traces and capture the objectid but the property doesn't work that
way.

Any good ideas would be appreciated.david_0 (dosberg@.yahoo.com) writes:
> I have inherited support for a database with many used tables. There
> isn't any documentation on what is used or dead. I was hoping to run
> traces and capture the objectid but the property doesn't work that
> way.

If the application uses only stored procedures, then it is fairly easy:
you search the code for all tables, and the keep track of which tables
does not give a hit. Alright, so that opens the question if there are
stored procedures which are not used.

If the application sends SELECT statement from the code, your best bet
is probably to search the application code, again once for each table.
If you find no references for a table you may want to make extra
precautions as it could be a lookup table that is being reference in
a foreign-ley constraint or trigger only. Or it may be part of a view
that is not in use.

Using the Profiler and the search the output is a possibility, but there
may be tables which are used in end-of-the-year functions only.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Here is what I came up with that will work for my situation. With a
little rework it could be used in other cases.

It doesn't do anything to detect inline sql on the client side. That
issue doesn't really apply in my situation.

DECLARE @.tbl TABLE(id INT IDENTITY(1,1), tblname VARCHAR(128),
found_flag CHAR(1))
DECLARE @.cnt int
DECLARE @.loop int
DECLARE @.parm varchar(255)

/*
*load variable with tables that aren't in sysdepends
*/
INSERT INTO @.tbl (tblname, found_flag)
SELECT
OBJECT_NAME(a.id),'N'
FROM
sysobjects a LEFT JOIN sysdepends b ON a.id=depid
WHERE
a.type='u' AND b.depid IS NULL
ORDER BY object_name(a.id)

/*
*setup variables for the loop
*/
SELECT @.cnt=MAX(id) FROM @.tbl
SET @.loop=1

/*
*take list of tables with no dependencies and look for job steps
that might reference them.
*/
WHILE @.loop <=@.cnt BEGIN

SELECT @.parm=tblname FROM @.tbl WHERE id=@.loop

IF EXISTS (SELECT job_id FROM msdb..sysjobsteps WHERE
CHARINDEX(@.parm,command)>0)
BEGIN
UPDATE @.tbl SET found_flag='Y' where id=@.loop
END
SET @.loop=@.loop+1
END

/*
*return table names not used in job steps or having object
dependencies
*/
SELECT tblname FROM @.tbl WHERE found_flag='N'

Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns9536EBF5675DYazorman@.127.0.0.1>...
> david_0 (dosberg@.yahoo.com) writes:
> > I have inherited support for a database with many used tables. There
> > isn't any documentation on what is used or dead. I was hoping to run
> > traces and capture the objectid but the property doesn't work that
> > way.
> If the application uses only stored procedures, then it is fairly easy:
> you search the code for all tables, and the keep track of which tables
> does not give a hit. Alright, so that opens the question if there are
> stored procedures which are not used.
> If the application sends SELECT statement from the code, your best bet
> is probably to search the application code, again once for each table.
> If you find no references for a table you may want to make extra
> precautions as it could be a lookup table that is being reference in
> a foreign-ley constraint or trigger only. Or it may be part of a view
> that is not in use.
> Using the Profiler and the search the output is a possibility, but there
> may be tables which are used in end-of-the-year functions only.|||david_0 (dosberg@.yahoo.com) writes:
> Here is what I came up with that will work for my situation. With a
> little rework it could be used in other cases.
>...
> INSERT INTO @.tbl (tblname, found_flag)
> SELECT
> OBJECT_NAME(a.id),'N'
> FROM
> sysobjects a LEFT JOIN sysdepends b ON a.id=depid
> WHERE
> a.type='u' AND b.depid IS NULL
> ORDER BY object_name(a.id)

Note however that sysdepends is at best approxamite. For instance if you
drop and recreate a table, you lose all entries in sysdepends for
the reference, so the table may appear unreferenced.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp