Bug with SQL Server 2005 Management studio

Hi,

I'm having a really annoying problem with SQL Server 2005 Management studio. I've just imported a very large database with 1,175,966 records in org_details table. During the import primary didn't transfer, so I thought I'd use management studio to alter the table and reset a primary key. however I get the following error. I want to restart the identity at 2213364 but I get the following error, also if its only 1 by 1. I've read it can be related to log file however I've increase the size to 8 GB.

ERROR:

'org_details' table
- Unable to create index 'PK_org_details'.
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

This is really frustrating as I don't remember having issue like this with SQL Server 2000.

Thanks help or assistance.

Unfortunately, the error message doesn't let you know which timeout setting expired.

My guess is the table designer timeout. In Management Studio, go to

Tools|Options|Designers|Table and Database Designers

Uncheck "Override connection string time-out value for table designer updates." (The default is checked, with an override value of 30 seconds. That could be too short for you.)

Two other timeout settings that might be issues are mentioned in the answer to this similar question:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=291327&SiteID=1

Steve Kass

Drew University

http://www.stevekass.com

|||

The root cause of this problem is you have choosen Import/Export wizard to transfer data which will only transfer data not the exact schema like PK,FK,Check Constraints etc. So when you want exact schema in the target database , script the source database and run the script in target db and then use IMport/Export to transfer data to the newly created tables. I would suggest you to use Script to create PK when the table is huge.

Madhu

|||

Thank you, you make a very valid point. The problem is related to a client of mine who has an access database, yes I said access with over 1 million records. In my opinion SQL management studio should support the manipulation of large databases.Yes it’s a large database however database’s these-days are large, lets be realistic a 1 million plus database isn’t uncommon.

I'm looking for a solution to SQL Server 2005 management studio problem not a scripting alternative, although thank you anyway.

|||thankyou I changed the timeout to 240 seconds now the transaction works.

Bug in ISNUMERIC ?

strange thing I just ran into, not sure if this is a bug or what ... but pretty annoying.
In MS SQLServer 2000 :

SELECT (ISNUMERIC('0E010101'))

returns "1"

but

SELECT CAST ('0E010101' AS numeric)

returns "Error converting data type varchar to numeric"

any idea?ISNUMERIC tests whether a value can be converted to a number of any type.

CAST ('0E010101' AS numeric) attempts to specifically convert the string to the SQL NUMERIC data type.|||I think he was addressing the fact that there was an 'E' in the numeric string.

Hypothesis:
the E is recognized as the exponent value from a float.
so it includes it.

I dont know but it sounds good to me.|||No, same happens with D.
I thought it could be Hexadecimal, but it doesn't seem to work with ABCFGH
?

Originally posted by Ruprect
Hypothesis:
the E is recognized as the exponent value from a float.
so it includes it.

Could Be|||Originally posted by Ruprect
I think he was addressing the fact that there was an 'E' in the numeric string.

Hypothesis:
the E is recognized as the exponent value from a float.
so it includes it.

I dont know but it sounds good to me.

I tried the following statement and it gives a 0 so i am going with my Hypothesis
SELECT (ISNUMERIC('0A010101'))|||try SELECT (ISNUMERIC('0D010101'))

Originally posted by Ruprect
I tried the following statement and it gives a 0 so i am going with my Hypothesis
SELECT (ISNUMERIC('0A010101'))|||The strange thing is that SELECT (ISNUMERIC('0DE010101')) returns 0
but
SELECT (ISNUMERIC('0D010101'))
or
SELECT (ISNUMERIC('0E010101'))
return 1

Originally posted by Tontxu
try SELECT (ISNUMERIC('0D010101'))|||Books Online ISNUMERIC
ISNUMERIC returns 1 when the input expression evaluates to a valid integer, floating point number, money or decimal type; otherwise it returns 0. A return value of 1 guarantees that expression can be converted to one of these numeric types.

i even tried different combiantions and added an e to the end it appears that it's reading the values with on char in the second position
no i need to create a script that trys a brute force on every combination

nahhh|||I don't know much about number but I think Ruprect is right on this one...

I don't know what that D stands for either,.. but apparently it stands for something...|||i retracted my d statement
i thought that it could have been indicative of some type of money
danish kroners or whatever they buy their crap with.|||the only thing I know is that it's not only the second position as my initial problem was with
SELECT (ISNUMERIC('0102D121'))

my problem was :

DECLARE @.Temp nvachar(25)

SELECT @.Temp = '0000D121'
SELECT
CASE
WHEN ISNUMERIC (@.Temp) THEN CONVERT(nvarchar(20),convert(numeric,@.Temp))
ELSE @.Temp
END

the problem is that @.Temp is gonna be a colum from a table so I can predict the values and I'm trying to remove the leading '0'

Originally posted by rokslide
I don't know much about number but I think Ruprect is right on this one...

I don't know what that D stands for either,.. but apparently it stands for something...|||there is a bug with converting to numeric.

try:

select convert(float,1.7256e+06)
select convert(float,'1.7256e+06')
select convert(numeric(38,19),1.7256e+06)
select convert(numeric(38,19),'1.7256e+06')

in query analyzer...

Bill|||Originally posted by rokslide
I don't know much about number but I think Ruprect is right on this one...

I don't know what that D stands for either,.. but apparently it stands for something... Great zot! No old C programmers around anymore, or what ?!?!

E is the exponent introducer for real (float*4, 7 digit precision) values. D is the introducer for double (float*8, 53 digit precision) values. M is a suffix for (packed) decimal, and even IsNumeric() fails to process M correctly.

-PatP|||A history lesson!

Do old C programmers ever die? Or does their legacy go on?|||Damn...learned something new again...|||I Win!!!!

and i guessed

we are the champions! We Are the champions!Playing in the background|||Now that you have won, Ruprect, you can keep your prize and walk away, or you can swap your prize for what is in thread number 3. ;-)|||Hold on! I demand a recount.|||winning is more than actually doing something.. it's making everyone believe that you did something and first no less.

and welll keep on fighting til the end... ba bummmm:o
we are the champions :o
we are the champions :o
:o
of the world......:o

sorry. it must be all the cough syrup i had at lunch today...

:p

Bug in a Stored Procedure [sys].[sp_dbmmonitorupdate]

Hi guys,

I found an annoying bug in the system sproc [sys].[sp_dbmmonitorupdate]. There in line
308 : set @.database_name = db_name( @.database_id )
it should basically state
set @.database_name = QUOTENAME(db_name( @.database_id ))
because if a database has a space or point in the name this causes the sproc to fail with the error:
"Incorrect syntax near '.'."

has someone any idea how to change this sys sproc?
or an idea for a workaround?

thanks...
darkook, let me ask the other way around:

i cannot change the sys sproc, can I? in that case I need a sproc which is deployed in the msdb (there is a check in the sp_dbmmonitorupdate which says it must be executed in the context of the msdb). how ever, I understand that no sprocs should be deployed in msdb. any ideas how to solve this problem?

thx!