Showing posts with label state. Show all posts
Showing posts with label state. Show all posts

Tuesday, March 27, 2012

Cant change datatype??

Hello all,

First of all, I must state that I'm new to SQL Server, but have been a
long-time software guy for quite some time. Please excuse the potential
simplicity of the solution to my problem.

I create a blank database (TestDB) under my server machine (SQL Server 2000
Standard)... I then create a new table (Run_Number) with only one record in
it.

Field(1): Next_Run_Number (bigint), identity w/ seed 1 and increment 1, no
nulls, PK

I can successfully save this new table to disk and everything seems fine...
problem comes up when I go back into the table and change the datatype from
(bigint) to (int). I try and save changes and I get the following error
prompt:

'Run_Number' table
- Unable to create index 'PK_Run_Number'.
ODBC error: [Microsoft][ODBC SQL Server Driver]Invalid cursor state

I've checked and there's plenty of disk space available and the dbase is set
to grow by 10% as necessary. Any ideas what could be going wrong? I'm able
to successfully make this change on my other machine running SQL Server 2000
Personal. Thoughts?

TIA!

-J.C..Hi

I don't really know why you are getting this error. Are you doing the change
directly in Enterprise Manager?
Have you tried removing the PK and setting seed to No before changing the
Datatype from bigint to int and then applied the PK and the seed again after
the change?

Be careful if you change the datatype from bigint to int as bigint values
outside the int value range obviously won't be converted correctly.

HTH, M

"J.C. Flores" <jcflores@.archonwest.com> wrote in message
news:tEm4d.517$zG1.418@.newsread3.news.pas.earthlin k.net...
> Hello all,
> First of all, I must state that I'm new to SQL Server, but have been a
> long-time software guy for quite some time. Please excuse the potential
> simplicity of the solution to my problem.
> I create a blank database (TestDB) under my server machine (SQL Server
> 2000 Standard)... I then create a new table (Run_Number) with only one
> record in it.
> Field(1): Next_Run_Number (bigint), identity w/ seed 1 and increment 1, no
> nulls, PK
> I can successfully save this new table to disk and everything seems
> fine... problem comes up when I go back into the table and change the
> datatype from (bigint) to (int). I try and save changes and I get the
> following error prompt:
> 'Run_Number' table
> - Unable to create index 'PK_Run_Number'.
> ODBC error: [Microsoft][ODBC SQL Server Driver]Invalid cursor state
> I've checked and there's plenty of disk space available and the dbase is
> set to grow by 10% as necessary. Any ideas what could be going wrong?
> I'm able to successfully make this change on my other machine running SQL
> Server 2000 Personal. Thoughts?
> TIA!
> -J.C..
>|||Yes, I'm doing this change in EM. I tried some variations re: removing PK,
disabling seed, etc... but the main point I want to convey is that I don't
have to do this type of disabling in my Personal edition of SQL Server... in
Personal edition, all I do is change bigint to int datatype and i'm able to
save without a hitch. Yet, the Standard edition is giving me problems...
same tables. And just for clarification, the table has only ONE FIELD... no
records (completely empty table).

Are we perhaps seeing issues related to permissions on the Standard edition
server? Maybe I can create tables, but for some reason can't change them
once they're created?

Ideas? I would greatly appreciate any help offered.

-J.C..

"Martin Feuersteiner" <theintrepidfox@.hotmail.com> wrote in message
news:cit885$cfa$1@.titan.btinternet.com...
> Hi
> I don't really know why you are getting this error. Are you doing the
> change directly in Enterprise Manager?
> Have you tried removing the PK and setting seed to No before changing the
> Datatype from bigint to int and then applied the PK and the seed again
> after the change?
> Be careful if you change the datatype from bigint to int as bigint values
> outside the int value range obviously won't be converted correctly.
> HTH, M
> "J.C. Flores" <jcflores@.archonwest.com> wrote in message
> news:tEm4d.517$zG1.418@.newsread3.news.pas.earthlin k.net...
>> Hello all,
>>
>> First of all, I must state that I'm new to SQL Server, but have been a
>> long-time software guy for quite some time. Please excuse the potential
>> simplicity of the solution to my problem.
>>
>> I create a blank database (TestDB) under my server machine (SQL Server
>> 2000 Standard)... I then create a new table (Run_Number) with only one
>> record in it.
>>
>> Field(1): Next_Run_Number (bigint), identity w/ seed 1 and increment 1,
>> no nulls, PK
>>
>> I can successfully save this new table to disk and everything seems
>> fine... problem comes up when I go back into the table and change the
>> datatype from (bigint) to (int). I try and save changes and I get the
>> following error prompt:
>>
>> 'Run_Number' table
>> - Unable to create index 'PK_Run_Number'.
>> ODBC error: [Microsoft][ODBC SQL Server Driver]Invalid cursor state
>>
>> I've checked and there's plenty of disk space available and the dbase is
>> set to grow by 10% as necessary. Any ideas what could be going wrong?
>> I'm able to successfully make this change on my other machine running SQL
>> Server 2000 Personal. Thoughts?
>>
>> TIA!
>>
>> -J.C..
>>
>>
>>
>>|||One thing you might try is to click the 'save change script' button in the
EM table designer and then try to run that script using Query Analyzer.
Perhaps this will help isolate the problem.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"J.C. Flores" <jcflores@.archonwest.com> wrote in message
news:UxY4d.8770$gG4.4258@.newsread1.news.pas.earthl ink.net...
> Yes, I'm doing this change in EM. I tried some variations re: removing
> PK, disabling seed, etc... but the main point I want to convey is that I
> don't have to do this type of disabling in my Personal edition of SQL
> Server... in Personal edition, all I do is change bigint to int datatype
> and i'm able to save without a hitch. Yet, the Standard edition is giving
> me problems... same tables. And just for clarification, the table has
> only ONE FIELD... no records (completely empty table).
> Are we perhaps seeing issues related to permissions on the Standard
> edition server? Maybe I can create tables, but for some reason can't
> change them once they're created?
> Ideas? I would greatly appreciate any help offered.
> -J.C..
>
> "Martin Feuersteiner" <theintrepidfox@.hotmail.com> wrote in message
> news:cit885$cfa$1@.titan.btinternet.com...
>> Hi
>>
>> I don't really know why you are getting this error. Are you doing the
>> change directly in Enterprise Manager?
>> Have you tried removing the PK and setting seed to No before changing the
>> Datatype from bigint to int and then applied the PK and the seed again
>> after the change?
>>
>> Be careful if you change the datatype from bigint to int as bigint values
>> outside the int value range obviously won't be converted correctly.
>>
>> HTH, M
>>
>> "J.C. Flores" <jcflores@.archonwest.com> wrote in message
>> news:tEm4d.517$zG1.418@.newsread3.news.pas.earthlin k.net...
>>> Hello all,
>>>
>>> First of all, I must state that I'm new to SQL Server, but have been a
>>> long-time software guy for quite some time. Please excuse the potential
>>> simplicity of the solution to my problem.
>>>
>>> I create a blank database (TestDB) under my server machine (SQL Server
>>> 2000 Standard)... I then create a new table (Run_Number) with only one
>>> record in it.
>>>
>>> Field(1): Next_Run_Number (bigint), identity w/ seed 1 and increment 1,
>>> no nulls, PK
>>>
>>> I can successfully save this new table to disk and everything seems
>>> fine... problem comes up when I go back into the table and change the
>>> datatype from (bigint) to (int). I try and save changes and I get the
>>> following error prompt:
>>>
>>> 'Run_Number' table
>>> - Unable to create index 'PK_Run_Number'.
>>> ODBC error: [Microsoft][ODBC SQL Server Driver]Invalid cursor state
>>>
>>> I've checked and there's plenty of disk space available and the dbase is
>>> set to grow by 10% as necessary. Any ideas what could be going wrong?
>>> I'm able to successfully make this change on my other machine running
>>> SQL Server 2000 Personal. Thoughts?
>>>
>>> TIA!
>>>
>>> -J.C..
>>>
>>>
>>>
>>>
>>
>>|||Hey, remove the identity seed, change the datatype, enable the
identity seed back. There is a good chance it might fix it. If not
drop table create all over since you said there are no records.
Hope this helps, Girish

Sunday, March 25, 2012

Cant allocate space

Novice MS SQL User

Dell PowerEdge 2300
NT 4 server SP4
MS SQL Server 6.5

i'm trying to upload 250,000 records

Error : 1105, Severity: 17, State: 2
Can't allocate space for object 'Syslogs' in database 'clearance_data' because the 'logsegment' segment is full. If you ran out of space in Syslogs, dump the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to increase the size of the segment.

what i'm planning to do is to add a new hard drive to the server, but HOW?

please help

see attached files (jpeg zip) for
Disk Administration
SQL Server
Devices
Databases
Available space

Thanks in advance(Couldn't read your attached files), but:

You might consider trying some of the following:

Temporarily set the DB 'clearance_data' to truncate on checkpoint until you have loaded your data.

Add or increase the size of the existing transaction log for DB 'clearance_data'.

I assume you are loading 250,000 records into the the DB 'clearance_data'? (If so, try loading fewer records at any one time e.g., load 10 25,000 record data subsets, instead of all 250,000 at once.)

To clear the error 'logsegment' segment is full try: Dump Log 'clearance_data' with Truncate_Only

Finally, RE: " ...add a new hard drive to the server, but HOW?"
What kind of (hardware?) issues are you having with the new hard drive?|||Thanks for the quick response

Finally, RE: " ...add a new hard drive to the server, but HOW?"
What kind of (hardware?) issues are you having with the new hard drive?

please download again the attached file, I used WinZip 8.0

NT Server Setup

4338MB DISK 0 C: - 1083MB Mirror Set E: - 3240 Stripe Set
4338MB DISK 1 C: - 1083MB Mirror Set E: - 3240 Stripe set
4299MB DISK 2 F: - 4299MB Primary Partition

SQL Server Setup
Database devices Data Size Available
clearance_device 5500MB 1290MB
DATA2
master
MSDBData
MSDBLog
Databases
clearance_data
master
model
msdb
pubs
tempdb

if I add, lets say 18GB HD
this will become

18GB DISK 3

can I still add/expand the clearance_device to 18GB more
is this doable?

Thanks again in adavce|||Q1 please download again the attached file, I used WinZip 8.0
Q2 can I still add/expand the clearance_device to 18GB more
is this doable?
A1 This time the error (with the image zip) was "images.zip: Either multipart or corrupt ZIP archive."

A2 You may not actually be able to use 100% of the new volume's free space, (even implementing raw partion devices), but generally, yes.

Note: It appears likely that you have separate devices for both DB Log and DB Data (if so, a good thing). In your particular situation, (if you must perform a single large operation in one step); you must provide the DB LOG sufficient space to complete the operation. Obviously, there must also be sufficient room for the actual DB data itself, as well; so it is possible you may also need to provide additional free data device space to successfully complete your load operation. The reason for mentioning this (in this manner) is that Sql Server 6.x allowed the co-mingling of Log / Data on the same device (generally not a desirable configuration, for a number of reasons).|||I tried downloading the file... yes it was corrupted
I'll try to attached it again
. +------+
4338MB DISK 0 C: - 1083MB Mirror Set | E: - 3240 Stripe Set
4338MB DISK 1 C: - 1083MB Mirror Set | E: - 3240 Stripe Set
. +------+

on those stripe set resides the clearance_data

A2 You may not actually be able to use 100% of the new volume's free space, (even implementing raw partion devices), but generally, yes.

assuming I have installed a new device

how can I configure MS SQL 6.5 to use the new device for clearance_data for expansion

Thanks again

please try to download the new attached file|||RE: Q1 Assuming I have installed a new device, how can I configure MS SQL 6.5 to use the new device for clearance_data for expansion?
Q2 Please try to download the new attached file.

A1 I'm not sure exactly what you are asking (I had assumed hardware mirroring, are you using Sql Server DB mirroring? If so I believe you must break the mirror before altering the DB, then remirror subsequently if possible.). If not, as I recall, you may do everything necessary from Sql EM once the new drive volume is available (visible) to the OS (unless maybe, you have done some intersting things with segments). You could use tsql in isqlw as well, for example: (not tested)

/* Find out the maximum VDevNo (XX): Then XXX = XX + 1 (where XX is the first digit of low byte column data) */

Use Master
Go

exec sp_HelpDevice
Go
Select * From SysDevices
Go

/* G: = 18GB DISK 3*/
/* The following tsql would use half of G for Log and half G for Data: */

Disk INIT
Name = 'Clearance_Data_DataDevG_001',
PhysName = 'G:\MsSql\Data\Clearance_Data_DataDevG001.dat'
VDevNo = XXX,
Size = 4608000 /* 4608000 x 2KB = 9GB */
Go

Disk INIT
Name = 'Clearance_Data_LogDevG_001',
PhysName = 'G:\MsSql\Data\Clearance_Data_LogDevG001.log'
VDevNo = XXX,
Size = 4608000 /* 4608000 x 2KB = 9GB */
Go

/* The following tsql should then Alter the clearance_data DB to use about half of each of the log and data device files created on G. : */

Alter DataBase clearance_data
On Clearance_Data_DataDevG_001 = 4500,
Log On Clearance_Data_LogDevG_001 = 4500
Go

/* - NOTE - */
/* If the above statement fails, then the Log On clause may not be valid to use with Alter Table. In that case, use the following tsql which should work: */

Alter DataBase clearance_data
On Clearance_Data_DataDevG_001 = 4500,
Clearance_Data_LogDevG_001 = 4500
Exec sp_LogDevice 'clearance_data', 'Clearance_Data_LogDevG_001'
Go

A2 The latest file has a zero byte size (downloaded).

Tuesday, March 20, 2012

Can't access default database on start up

Help!
The default database failed on the restore, and was marked
as in load. After trying to get the database out of that
state, the server was stoped and restarted. On the
restart, we now get the message that the default database
can not be accessed, so we can't connect. For some
unknown reason the default database for the admin group
was not Master, but the database that is marked in Load.
How can I know connect to the server?
Thanks
Mike Allen
Novice DBA|
| The default database failed on the restore, and was marked
| as in load. After trying to get the database out of that
| state, the server was stoped and restarted. On the
| restart, we now get the message that the default database
| can not be accessed, so we can't connect. For some
| unknown reason the default database for the admin group
| was not Master, but the database that is marked in Load.
|
| How can I know connect to the server?
|
--
Hi Mike,
If you connect using osql, you can use the /d switch to specify another
database as the default. For more information, please consult your SQL
Server Books Online on the topic "default database."
Hope this helps,
--
Eric Cárdenas
SQL Server support|||Thanks for your help. Even after changing the default db, for the Admin group, I was not able to get connect through EM. So I added my individual userid with a default db of master, and that worked. Now I get to 'fix' the bad database.
Thanks.
>--Original Message--
>| >| The default database failed on the restore, and was marked >| as in load. After trying to get the database out of that >| state, the server was stoped and restarted. On the >| restart, we now get the message that the default database >| can not be accessed, so we can't connect. For some >| unknown reason the default database for the admin group >| was not Master, but the database that is marked in Load.
>| >| How can I know connect to the server?
>| >--
>Hi Mike,
>If you connect using osql, you can use the /d switch to specify another >database as the default. For more information, please consult your SQL >Server Books Online on the topic "default database."
>Hope this helps,
>--
>Eric C=E1rdenas
>SQL Server support
>.
>sql