Errors adding a column in 64-bit version

I have run into an interesting problem. I have some code that adds a column to an existing table. The column is set as primary key, identity and clustered. On the 32-bit version of SQL 2005 it works fine. It fails on the 64-bit version of 2005 intermittently with a "could not create unique index because duplicate values were found". Kind of odd, considering as an identity field it's creating the values. I was able to recreate the problem with the following schema:

Create table test1

(col1 varchar(20),

col2 varchar(20),

col3 uniqueidentifier default newid())

-- insert data

Declare @.counter int

set @.counter=1

While @.counter < 1000000

BEGIN

insert into test1

values ('Joe','Smith',default)

Set @.counter=@.counter+1

END

-- add column

Alter table test1 add col4 int constraint PK_test1 primary key clustered identity

After Running this, I get this error:

CREATE UNIQUE INDEX terminated because a duplicate key was found for object name 'dbo.Test1' and index name 'PK_test1'. The duplicate key value is (28).

Anybody else run into this? Why would this be happening, and is there any way to fix it? I'm running the 64-bit version of Windows 2003 and the the 64-bit version of SQL Server 2005.

Thanks in advance,

Mark

Could you please file a bug with these details / repro steps at the MSDN Product Feedback Center? I do not have a 64-bit m/c to try this out. Also, please mention the editions of Windows and SQL Server (x64, IA, EMT). Thanks.

Errors 5105-5170 when creating large datafile

Greetings.
We recently added a second RAID array to our four-way server to add another
filegroup to our 400GB database. Using SQL EM, I tried adding a new 400GB
data file to a new filegroup on this new array. It ran for about 12 hours
and then came back with the following two errors:
Error 5105: Device activation error. The physical file name
'f:\MSSQL\data\Clarity_Data2_Data.NDF' may be incorrect. Extending database
by 393000.00 MB on disk 'Clarity_Data2'.
Error 5170: Cannot create file 'f:\MSSQL\data\Clarity_Data2_Data.NDF'
because it already exists. ALTER DATABASE failed. Some disk names listed in
the statement were not found. Check that the names exist and are spelled
correctly before rerunning the statement. Extending database by 393000.00 MB
on disk 'Clarity_Data2'.
First of all, the drive, folder, and now the file do exist. What happened
here to make this fail? Is there a way for me to successfully get this file
added to my database without going through the 12+ hour ALTER again?
Secondly, I would have thought that creating the new datafile would not have
taken that long to run, even though it was 400GB in size. It's a RAID 5
array on its own controller on a four-way box running Windows Server 2003.
Any thoughts?
Thanks.
Dale.
P.S. The only info I found on this in the KB mentioned the DEFAULTLOG and
DEFAULTDATA registry keys. There was no default set up for this server, so I
don't think that is related.Was RAID array already build when you have started to add a new data file ?
I'm sorry for silly question, but when you will create an RAID array, it will
accessible immediately, but RAID controller will build it up in a background.
It always good to wait when building processs will finished before work with
new array.This actually can cause performance issue as well as accessibility
issues.
Regards.
"Dale M." wrote:
> Greetings.
> We recently added a second RAID array to our four-way server to add another
> filegroup to our 400GB database. Using SQL EM, I tried adding a new 400GB
> data file to a new filegroup on this new array. It ran for about 12 hours
> and then came back with the following two errors:
> Error 5105: Device activation error. The physical file name
> 'f:\MSSQL\data\Clarity_Data2_Data.NDF' may be incorrect. Extending database
> by 393000.00 MB on disk 'Clarity_Data2'.
> Error 5170: Cannot create file 'f:\MSSQL\data\Clarity_Data2_Data.NDF'
> because it already exists. ALTER DATABASE failed. Some disk names listed in
> the statement were not found. Check that the names exist and are spelled
> correctly before rerunning the statement. Extending database by 393000.00 MB
> on disk 'Clarity_Data2'.
> First of all, the drive, folder, and now the file do exist. What happened
> here to make this fail? Is there a way for me to successfully get this file
> added to my database without going through the 12+ hour ALTER again?
> Secondly, I would have thought that creating the new datafile would not have
> taken that long to run, even though it was 400GB in size. It's a RAID 5
> array on its own controller on a four-way box running Windows Server 2003.
> Any thoughts?
> Thanks.
> Dale.
> P.S. The only info I found on this in the KB mentioned the DEFAULTLOG and
> DEFAULTDATA registry keys. There was no default set up for this server, so I
> don't think that is related.|||Yes, it was already built. It had been online for most of a day already.
"SkyWalker" wrote:
> Was RAID array already build when you have started to add a new data file ?
> I'm sorry for silly question, but when you will create an RAID array, it will
> accessible immediately, but RAID controller will build it up in a background.
> It always good to wait when building processs will finished before work with
> new array.This actually can cause performance issue as well as accessibility
> issues.
> Regards.
> "Dale M." wrote:
> > Greetings.
> >
> > We recently added a second RAID array to our four-way server to add another
> > filegroup to our 400GB database. Using SQL EM, I tried adding a new 400GB
> > data file to a new filegroup on this new array. It ran for about 12 hours
> > and then came back with the following two errors:
> > Error 5105: Device activation error. The physical file name
> > 'f:\MSSQL\data\Clarity_Data2_Data.NDF' may be incorrect. Extending database
> > by 393000.00 MB on disk 'Clarity_Data2'.
> >
> > Error 5170: Cannot create file 'f:\MSSQL\data\Clarity_Data2_Data.NDF'
> > because it already exists. ALTER DATABASE failed. Some disk names listed in
> > the statement were not found. Check that the names exist and are spelled
> > correctly before rerunning the statement. Extending database by 393000.00 MB
> > on disk 'Clarity_Data2'.
> >
> > First of all, the drive, folder, and now the file do exist. What happened
> > here to make this fail? Is there a way for me to successfully get this file
> > added to my database without going through the 12+ hour ALTER again?
> > Secondly, I would have thought that creating the new datafile would not have
> > taken that long to run, even though it was 400GB in size. It's a RAID 5
> > array on its own controller on a four-way box running Windows Server 2003.
> >
> > Any thoughts?
> >
> > Thanks.
> > Dale.
> >
> > P.S. The only info I found on this in the KB mentioned the DEFAULTLOG and
> > DEFAULTDATA registry keys. There was no default set up for this server, so I
> > don't think that is related.

Errors 5105-5170 when creating large datafile

Greetings.
We recently added a second RAID array to our four-way server to add another
filegroup to our 400GB database. Using SQL EM, I tried adding a new 400GB
data file to a new filegroup on this new array. It ran for about 12 hours
and then came back with the following two errors:
Error 5105: Device activation error. The physical file name
'f:\MSSQL\data\Clarity_Data2_Data.NDF' may be incorrect. Extending database
by 393000.00 MB on disk 'Clarity_Data2'.
Error 5170: Cannot create file 'f:\MSSQL\data\Clarity_Data2_Data.NDF'
because it already exists. ALTER DATABASE failed. Some disk names listed i
n
the statement were not found. Check that the names exist and are spelled
correctly before rerunning the statement. Extending database by 393000.00 M
B
on disk 'Clarity_Data2'.
First of all, the drive, folder, and now the file do exist. What happened
here to make this fail? Is there a way for me to successfully get this file
added to my database without going through the 12+ hour ALTER again?
Secondly, I would have thought that creating the new datafile would not have
taken that long to run, even though it was 400GB in size. It's a RAID 5
array on its own controller on a four-way box running Windows Server 2003.
Any thoughts?
Thanks.
Dale.
P.S. The only info I found on this in the KB mentioned the DEFAULTLOG and
DEFAULTDATA registry keys. There was no default set up for this server, so
I
don't think that is related.Was RAID array already build when you have started to add a new data file ?
I'm sorry for silly question, but when you will create an RAID array, it wil
l
accessible immediately, but RAID controller will build it up in a background
.
It always good to wait when building processs will finished before work with
new array.This actually can cause performance issue as well as accessibility
issues.
Regards.
"Dale M." wrote:

> Greetings.
> We recently added a second RAID array to our four-way server to add anothe
r
> filegroup to our 400GB database. Using SQL EM, I tried adding a new 400GB
> data file to a new filegroup on this new array. It ran for about 12 hours
> and then came back with the following two errors:
> Error 5105: Device activation error. The physical file name
> 'f:\MSSQL\data\Clarity_Data2_Data.NDF' may be incorrect. Extending databa
se
> by 393000.00 MB on disk 'Clarity_Data2'.
> Error 5170: Cannot create file 'f:\MSSQL\data\Clarity_Data2_Data.NDF'
> because it already exists. ALTER DATABASE failed. Some disk names listed
in
> the statement were not found. Check that the names exist and are spelled
> correctly before rerunning the statement. Extending database by 393000.00
MB
> on disk 'Clarity_Data2'.
> First of all, the drive, folder, and now the file do exist. What happened
> here to make this fail? Is there a way for me to successfully get this fi
le
> added to my database without going through the 12+ hour ALTER again?
> Secondly, I would have thought that creating the new datafile would not ha
ve
> taken that long to run, even though it was 400GB in size. It's a RAID 5
> array on its own controller on a four-way box running Windows Server 2003.
> Any thoughts?
> Thanks.
> Dale.
> P.S. The only info I found on this in the KB mentioned the DEFAULTLOG and
> DEFAULTDATA registry keys. There was no default set up for this server, s
o I
> don't think that is related.|||Yes, it was already built. It had been online for most of a day already.
"SkyWalker" wrote:
[vbcol=seagreen]
> Was RAID array already build when you have started to add a new data file
?
> I'm sorry for silly question, but when you will create an RAID array, it w
ill
> accessible immediately, but RAID controller will build it up in a backgrou
nd.
> It always good to wait when building processs will finished before work wi
th
> new array.This actually can cause performance issue as well as accessibili
ty
> issues.
> Regards.
> "Dale M." wrote:
>

Errors 5105-5170 when creating large datafile

Greetings.
We recently added a second RAID array to our four-way server to add another
filegroup to our 400GB database. Using SQL EM, I tried adding a new 400GB
data file to a new filegroup on this new array. It ran for about 12 hours
and then came back with the following two errors:
Error 5105: Device activation error. The physical file name
'f:\MSSQL\data\Clarity_Data2_Data.NDF' may be incorrect. Extending database
by 393000.00 MB on disk 'Clarity_Data2'.
Error 5170: Cannot create file 'f:\MSSQL\data\Clarity_Data2_Data.NDF'
because it already exists. ALTER DATABASE failed. Some disk names listed in
the statement were not found. Check that the names exist and are spelled
correctly before rerunning the statement. Extending database by 393000.00 MB
on disk 'Clarity_Data2'.
First of all, the drive, folder, and now the file do exist. What happened
here to make this fail? Is there a way for me to successfully get this file
added to my database without going through the 12+ hour ALTER again?
Secondly, I would have thought that creating the new datafile would not have
taken that long to run, even though it was 400GB in size. It's a RAID 5
array on its own controller on a four-way box running Windows Server 2003.
Any thoughts?
Thanks.
Dale.
P.S. The only info I found on this in the KB mentioned the DEFAULTLOG and
DEFAULTDATA registry keys. There was no default set up for this server, so I
don't think that is related.
Was RAID array already build when you have started to add a new data file ?
I'm sorry for silly question, but when you will create an RAID array, it will
accessible immediately, but RAID controller will build it up in a background.
It always good to wait when building processs will finished before work with
new array.This actually can cause performance issue as well as accessibility
issues.
Regards.
"Dale M." wrote:

> Greetings.
> We recently added a second RAID array to our four-way server to add another
> filegroup to our 400GB database. Using SQL EM, I tried adding a new 400GB
> data file to a new filegroup on this new array. It ran for about 12 hours
> and then came back with the following two errors:
> Error 5105: Device activation error. The physical file name
> 'f:\MSSQL\data\Clarity_Data2_Data.NDF' may be incorrect. Extending database
> by 393000.00 MB on disk 'Clarity_Data2'.
> Error 5170: Cannot create file 'f:\MSSQL\data\Clarity_Data2_Data.NDF'
> because it already exists. ALTER DATABASE failed. Some disk names listed in
> the statement were not found. Check that the names exist and are spelled
> correctly before rerunning the statement. Extending database by 393000.00 MB
> on disk 'Clarity_Data2'.
> First of all, the drive, folder, and now the file do exist. What happened
> here to make this fail? Is there a way for me to successfully get this file
> added to my database without going through the 12+ hour ALTER again?
> Secondly, I would have thought that creating the new datafile would not have
> taken that long to run, even though it was 400GB in size. It's a RAID 5
> array on its own controller on a four-way box running Windows Server 2003.
> Any thoughts?
> Thanks.
> Dale.
> P.S. The only info I found on this in the KB mentioned the DEFAULTLOG and
> DEFAULTDATA registry keys. There was no default set up for this server, so I
> don't think that is related.
|||Yes, it was already built. It had been online for most of a day already.
"SkyWalker" wrote:
[vbcol=seagreen]
> Was RAID array already build when you have started to add a new data file ?
> I'm sorry for silly question, but when you will create an RAID array, it will
> accessible immediately, but RAID controller will build it up in a background.
> It always good to wait when building processs will finished before work with
> new array.This actually can cause performance issue as well as accessibility
> issues.
> Regards.
> "Dale M." wrote:

Error:208 [SQL Server] Invalid Object Name

Hello
I was adding a line to a store procedure in MS SQL 6.5 when I saved the server gave an error about the stored procedure being more than 255 lines after my edit. I selected NO to save changes but when the Enterprise Manager windows refreshed the stored procedure was gone from the list
I had backed up the text of the SP in a text file so I open a new SP windows, pasted in the text, but received the Error: 208 Invalid Object Name error when I tried to hit save. Rebooted the server...same issue. I have checked at least 10 times and SP is not listed. I have modified nothing about this SP so would think it would just 'go back in.
Anyhelp would be greatly appreciated
Thanks in advance.BK,
Try running a DROP PROCEDURE [myProcName] to see if that wipes it out before
you attempt to create it again.
James Hokes
"BK" <bakzzkk@.charter.net> wrote in message
news:2248F174-8440-442F-8F09-031A679ADE52@.microsoft.com...
> Hello,
> I was adding a line to a store procedure in MS SQL 6.5 when I saved the
server gave an error about the stored procedure being more than 255 lines
after my edit. I selected NO to save changes but when the Enterprise
Manager windows refreshed the stored procedure was gone from the list.
> I had backed up the text of the SP in a text file so I open a new SP
windows, pasted in the text, but received the Error: 208 Invalid Object Name
error when I tried to hit save. Rebooted the server...same issue. I have
checked at least 10 times and SP is not listed. I have modified nothing
about this SP so would think it would just 'go back in.'
> Anyhelp would be greatly appreciated.
> Thanks in advance.|||=?Utf-8?B?Qks=?= (bakzzkk@.charter.net) writes:
> I was adding a line to a store procedure in MS SQL 6.5 when I saved the
> server gave an error about the stored procedure being more than 255
> lines after my edit. I selected NO to save changes but when the
> Enterprise Manager windows refreshed the stored procedure was gone from
> the list.
> I had backed up the text of the SP in a text file so I open a new SP
> windows, pasted in the text, but received the Error: 208 Invalid Object
> Name error when I tried to hit save. Rebooted the server...same issue.
> I have checked at least 10 times and SP is not listed. I have modified
> nothing about this SP so would think it would just 'go back in.'
So which name did SQL Server list in the 208 message? I would guess
that you are trying to create the procedure in the wrong database, and
one or more tables that the procedure refers to does not exist in this
database.
You could also try to create the procedure through a query window.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Hi,
Before creating the procedure , can you login to ISQLW and run the below
select statement on your database
select name from sysobjects where type='p' and name ='name of the procedure'
Incase if the statement return values , use drop proc <proc name> to drop.
After this cut and paste the procedure in the same window and create the
procedure.
Thanks
Hari
MCDBA
"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns94657977BE41Yazorman@.127.0.0.1...
> =?Utf-8?B?Qks=?= (bakzzkk@.charter.net) writes:
> > I was adding a line to a store procedure in MS SQL 6.5 when I saved the
> > server gave an error about the stored procedure being more than 255
> > lines after my edit. I selected NO to save changes but when the
> > Enterprise Manager windows refreshed the stored procedure was gone from
> > the list.
> >
> > I had backed up the text of the SP in a text file so I open a new SP
> > windows, pasted in the text, but received the Error: 208 Invalid Object
> > Name error when I tried to hit save. Rebooted the server...same issue.
> > I have checked at least 10 times and SP is not listed. I have modified
> > nothing about this SP so would think it would just 'go back in.'
> So which name did SQL Server list in the 208 message? I would guess
> that you are trying to create the procedure in the wrong database, and
> one or more tables that the procedure refers to does not exist in this
> database.
> You could also try to create the procedure through a query window.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||A common reason for this in SQL 6.5 is that the sp refers to a temp table...
If it does, you must create the temp table, then create the sp, then drop
the temp table to get the sp created.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"BK" <bakzzkk@.charter.net> wrote in message
news:2248F174-8440-442F-8F09-031A679ADE52@.microsoft.com...
> Hello,
> I was adding a line to a store procedure in MS SQL 6.5 when I saved the
server gave an error about the stored procedure being more than 255 lines
after my edit. I selected NO to save changes but when the Enterprise
Manager windows refreshed the stored procedure was gone from the list.
> I had backed up the text of the SP in a text file so I open a new SP
windows, pasted in the text, but received the Error: 208 Invalid Object Name
error when I tried to hit save. Rebooted the server...same issue. I have
checked at least 10 times and SP is not listed. I have modified nothing
about this SP so would think it would just 'go back in.'
> Anyhelp would be greatly appreciated.
> Thanks in advance.|||Hi Brandon
Can you try using isqlw instead of EM to create the proc from your saved
text file.
If that doesn't help, can you post the text of the procedure?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"BK" <bakzzkk@.charter.net> wrote in message
news:2248F174-8440-442F-8F09-031A679ADE52@.microsoft.com...
> Hello,
> I was adding a line to a store procedure in MS SQL 6.5 when I saved the
server gave an error about the stored procedure being more than 255 lines
after my edit. I selected NO to save changes but when the Enterprise
Manager windows refreshed the stored procedure was gone from the list.
> I had backed up the text of the SP in a text file so I open a new SP
windows, pasted in the text, but received the Error: 208 Invalid Object Name
error when I tried to hit save. Rebooted the server...same issue. I have
checked at least 10 times and SP is not listed. I have modified nothing
about this SP so would think it would just 'go back in.'
> Anyhelp would be greatly appreciated.
> Thanks in advance.