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

Can't access linked server, except from

Hi, (excuse my poor english).
I want to access data stored on a SQL2000 server using
"SELECT * FROM openquery(SQL2000srv, ..." from a
connection on my SQL2005 server.
I checked "Allow remote connections" on both servers, and
configured Nammed pipes as prefered protocol.
Security is Windows authentification only for both servers.
If I test my query on Management studio on my 2005 server,
everything is OK. But when I test from any other machine,
I have this error : (sorry, I translated it from french :
OLE DB 'SQLNCLI' from linked server returns 'Connection timeout'.
Msg 5, level 16, state 1, Line 0
Nammed pipes : can't open connection to SQL Server [5]. ".).
I can connect to both servers using any connection, so it's not
a user security problem, I think.
Do you have any clues ?
Thank you,
Jid.Hi Jide,
you have to ensure that Named Pipes is turned on as a Client Protocol.
You do this by opening up SQL Server Configuration Manager from the Sql
Server program group.
Underneath the SQL Native Client Configuration you find the Client Protocols
- here the Named Pipes protocol have to be enabled...
That may be the reason - at least its disabled at default.
Regards,
Mogens Nielsen
WM-data Danmark
"jidehem" wrote:

> Hi, (excuse my poor english).
> I want to access data stored on a SQL2000 server using
> "SELECT * FROM openquery(SQL2000srv, ..." from a
> connection on my SQL2005 server.
> I checked "Allow remote connections" on both servers, and
> configured Nammed pipes as prefered protocol.
> Security is Windows authentification only for both servers.
> If I test my query on Management studio on my 2005 server,
> everything is OK. But when I test from any other machine,
> I have this error : (sorry, I translated it from french :
> OLE DB 'SQLNCLI' from linked server returns 'Connection timeout'.
> Msg 5, level 16, state 1, Line 0
> Nammed pipes : can't open connection to SQL Server [5]. ".).
> I can connect to both servers using any connection, so it's not
> a user security problem, I think.
> Do you have any clues ?
> Thank you,
> Jidé.
>
>|||Mogens Nielsen - WM-Data A/S wrote:
> Hi Jide,
> you have to ensure that Named Pipes is turned on as a Client Protocol.
> You do this by opening up SQL Server Configuration Manager from the
> Sql Server program group.
> Underneath the SQL Native Client Configuration you find the Client
> Protocols - here the Named Pipes protocol have to be enabled...
> That may be the reason - at least its disabled at default.
Nammed pipes was already my prefered protocol for both servers
and in Client configuration utility. But thank you for your answer.
Another idea ?
Jid.|||Thought it was the simple problem like not turning named pipes on. I don't
think I've got any other good ideas...
Well, you could try connect by using an IP-adress instead of using the
server name, but then again - you shouldn't be able to connect through the
enterprise manager.
I think this is not of much help...
Maybe someone else has a clue !?
"jidehem" wrote:

> Mogens Nielsen - WM-Data A/S wrote:
> Nammed pipes was already my prefered protocol for both servers
> and in Client configuration utility. But thank you for your answer.
> Another idea ?
> Jidé.
>
>|||Mogens Nielsen - WM-Data A/S wrote:
> I think this is not of much help...
> Maybe someone else has a clue !?
Thank you again for your answers. I found the problem :
it's was "double hop" authentification problem.
In active directory, you need to activate delegation for
the user of the account of the SQL Server service, and for
the computer.
I have manually registred with setspn my linked server:
setspn -A MSSQLSvc/server.mydomaine.local:1433 account
(and not :
setspn -A MSSQLSvc/server:1433 account
nor
setspn -A MSSQLSvc/server:1433 mydomaine\account).
Now, all is OK.
Jid.