473,233 Members | 1,389 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,233 software developers and data experts.

Can't 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..


Jul 20 '05 #1
4 3863
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" <jc******@archonwest.com> wrote in message
news:tE***************@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..

Jul 20 '05 #2
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" <th************@hotmail.com> wrote in message
news:ci**********@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" <jc******@archonwest.com> wrote in message
news:tE***************@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..


Jul 20 '05 #3
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" <jc******@archonwest.com> wrote in message
news:Ux*****************@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" <th************@hotmail.com> wrote in message
news:ci**********@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" <jc******@archonwest.com> wrote in message
news:tE***************@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..



Jul 20 '05 #4
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
Jul 20 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: SoYouKnowBrig | last post by:
Hi All, I am using Microsoft.ApplicationBlocks.Cache.CacheManager to persist a System.Data.Dataset object. This Dataset object has a DataTable that is created from an existing DataTable using...
14
by: Elias Farah | last post by:
Hi All, What are people's experience in timing Query Performance? I have tried in vain, but get many unpredictable results, with execution proceeding in the background and problems of timing...
2
by: s.subbarayan | last post by:
Dear all, I have one peculiar problem with me for which I need ur inputs how to implement it: I have 2 registers each 8 bit wide. The first register stores: Register Map:...
6
by: Dan V. | last post by:
I would like to create a 2D string list (2D ArrayList ???). I would like to pass in a table or query as a parameter and have both columns transform into a 2D ArrayList. When I sort the one...
6
by: Sam | last post by:
Hi Here is my code : Code: Dim dt As New DataTable dt.Columns.Add.ColumnName = "New" dt.Columns("New").DataType = System.Type.GetType("System.String") dt.Columns.Add.ColumnName = "Id"...
1
by: Modica82 | last post by:
I have created a contract first web service, and to test i have created a client (asp.net) to attempt to test that my web service is at least communication. But when i call a method i get the...
7
by: dotnetnoob | last post by:
i keep getting Object references not set to an instance of an object from this code: Private Sub EqBinding() Dim x As Integer x = 0 Do If CStr(arlsType.Item(x)) = "Bacnet Point" Then Dim...
0
by: graju80 | last post by:
I am kind of new to Db2... Question: What are the rules that DB2 uses to determine the right datatype for a particular column for on-the-fly SQL generation? For example...
15
by: =?Utf-8?B?UGhpbCBKb2huc29u?= | last post by:
Hi, This is being called in a C# loop within an ado.net transaction from C# 1.1 code. It is used to write large file data to a SQL Server database in chunks, rather than in one go. I had the...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.