Showing posts with label identity. Show all posts
Showing posts with label identity. Show all posts

Thursday, March 29, 2012

Can't configure Web Service Identity

I recently renamed my server from tmpSrv to tfsSrv (names have been changed to protect the innocent). When I go to the Reporting Services Configuration Manager I have all greens except for the Web Service Identity field.

Trying to set that isn't working...the ASP.Net service account is NT Authority\Network Service, and the application pools defined are using that Network Service account as well. When I hit the "Apply" button I get a message saying:

Setting Web Service Identity

There was an error setting the identity for the web service. The previously set identity will still be used.

I'm trying to install Team Foundation Server on the box, and when I run the tfrsconfig /verify tool I see that the Web Service Identity is not set at all.

I've been battling this for a couple of days now with no effect. I even broke down and gave "Everyone" administrative access on the server in an attempt to fix it.

Thanks for any help

Hello Joe,

I think you'll need to edit the rsreportserver.config file in the directory: X:\Program Files\Microsoft SQL Server\MSSQL.x\Reporting Services\ReportServer. Search for <WebServiceAccount>NT Authority\NetworkService</WebServiceAccount> and you can change it here. I believe this will have to match the security you have setup for the application pool.

Hope this helps.

Jarret

|||Which version of OS and RS are you running? If you click on "tell me more" link in the config tool there should be more messages.

Thursday, March 8, 2012

Cannot Update Identity Column 'xxxx'

Hi,
I have transaction replication setup and the initial snap shot works fine. However after that updates are giving me the above error. Any help would be appreciated. Thanks.
Jeff
Jeff,
this error is not specific to replication - updates of identity columns are
prohibited in all cases, even when the table isn't published (there's a set
IDENTITY_INSERT on but no set IDENTITY_UPDATE on).
HTH,
Paul Ibison
|||Look at the table on the subscriber and verify that the identity column on
the table has the NOT FOR REPLICATION option set. Based on the error, I
would say it does not. Turn this on and see if that resolves the problem.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Cannot Update Identity Column while doing Select statement

Hi All,
i do have stored procedure
in which there select id,name from tablename
id i primary key and Identity seed is there for this column
When i directly query this statement it " select id,name from
tablename" , it shows the results
But when i call the same thing via stored procedure , it says Cannot
update identity colum
exec usp_getvalues , it gives the error
Thanks in Advance
Thomson
Hi
You would not expect this error from the query you have given therefore I
suspect that your stored procedure is doing something different. Post the
code for the store stored procedure and related tables see
http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL in the news
group.
At a guess there is an insert/update statement and you are either including
the identity column or don't have SET IDENTITY_INSERT ON for the table.
John
"thomson" wrote:

> Hi All,
> i do have stored procedure
> in which there select id,name from tablename
> id i primary key and Identity seed is there for this column
>
> When i directly query this statement it " select id,name from
> tablename" , it shows the results
> But when i call the same thing via stored procedure , it says Cannot
> update identity colum
> exec usp_getvalues , it gives the error
> Thanks in Advance
> Thomson
>
|||identity columns are not updateable.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"thomson" <saintthomson@.yahoo.com> wrote in message
news:1159770767.122534.31190@.h48g2000cwc.googlegro ups.com...
> Hi All,
> i do have stored procedure
> in which there select id,name from tablename
> id i primary key and Identity seed is there for this column
>
> When i directly query this statement it " select id,name from
> tablename" , it shows the results
> But when i call the same thing via stored procedure , it says Cannot
> update identity colum
> exec usp_getvalues , it gives the error
> Thanks in Advance
> Thomson
>

Cannot Update Identity Column while doing Select statement

Hi All,
i do have stored procedure
in which there select id,name from tablename
id i primary key and Identity seed is there for this column
When i directly query this statement it " select id,name from
tablename" , it shows the results
But when i call the same thing via stored procedure , it says Cannot
update identity colum
exec usp_getvalues , it gives the error
Thanks in Advance
ThomsonHi
You would not expect this error from the query you have given therefore I
suspect that your stored procedure is doing something different. Post the
code for the store stored procedure and related tables see
http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL in the news
group.
At a guess there is an insert/update statement and you are either including
the identity column or don't have SET IDENTITY_INSERT ON for the table.
John
"thomson" wrote:
> Hi All,
> i do have stored procedure
> in which there select id,name from tablename
> id i primary key and Identity seed is there for this column
>
> When i directly query this statement it " select id,name from
> tablename" , it shows the results
> But when i call the same thing via stored procedure , it says Cannot
> update identity colum
> exec usp_getvalues , it gives the error
> Thanks in Advance
> Thomson
>|||identity columns are not updateable.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"thomson" <saintthomson@.yahoo.com> wrote in message
news:1159770767.122534.31190@.h48g2000cwc.googlegroups.com...
> Hi All,
> i do have stored procedure
> in which there select id,name from tablename
> id i primary key and Identity seed is there for this column
>
> When i directly query this statement it " select id,name from
> tablename" , it shows the results
> But when i call the same thing via stored procedure , it says Cannot
> update identity colum
> exec usp_getvalues , it gives the error
> Thanks in Advance
> Thomson
>

Cannot Update Identity Column while doing Select statement

Hi All,
i do have stored procedure
in which there select id,name from tablename
id i primary key and Identity seed is there for this column
When i directly query this statement it " select id,name from
tablename" , it shows the results
But when i call the same thing via stored procedure , it says Cannot
update identity colum
exec usp_getvalues , it gives the error
Thanks in Advance
ThomsonHi
You would not expect this error from the query you have given therefore I
suspect that your stored procedure is doing something different. Post the
code for the store stored procedure and related tables see
http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL in the news
group.
At a guess there is an insert/update statement and you are either including
the identity column or don't have SET IDENTITY_INSERT ON for the table.
John
"thomson" wrote:

> Hi All,
> i do have stored procedure
> in which there select id,name from tablename
> id i primary key and Identity seed is there for this column
>
> When i directly query this statement it " select id,name from
> tablename" , it shows the results
> But when i call the same thing via stored procedure , it says Cannot
> update identity colum
> exec usp_getvalues , it gives the error
> Thanks in Advance
> Thomson
>|||identity columns are not updateable.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"thomson" <saintthomson@.yahoo.com> wrote in message
news:1159770767.122534.31190@.h48g2000cwc.googlegroups.com...
> Hi All,
> i do have stored procedure
> in which there select id,name from tablename
> id i primary key and Identity seed is there for this column
>
> When i directly query this statement it " select id,name from
> tablename" , it shows the results
> But when i call the same thing via stored procedure , it says Cannot
> update identity colum
> exec usp_getvalues , it gives the error
> Thanks in Advance
> Thomson
>

Cannot Update Identity Column

I am running SQL Server 2000 with Service pack 3. I have a database with a
transactional publication that is not setup for immediate or queued updating.
The tables I am replicating have a primary key that is also an identity
column. The publication pushes a subscription to a database on the same
instance of SQL Server. The articles are setup to “Keep the existing table
unchanged”. The tables in the subscriptions are exact copies of the
publication tables except the identity column is setup: (not for
replication). I also have the Identity Seed setup so that neither the
publication nor the subscription will use the same Identity. I have put in a
Check Constraint on all tables to make sure of this. With this setup I can
update the publication table and it replicates fine to the subscription table
I also can update and insert records into the subscription table directly,
but when I update the publication table I get this error when it tries to
replicate to the subscription table: "Cannot Update Identity Column 'Table
Name'. All I want to do is replicate my production databases to an exact copy
that I will use for testing. So I never want to have the subscription update
the publication, but I do want to be able to insert and update the
subscription for testing and have the subscription be up to date with the
publication database. Am I missing something to get this setup correctly or
is there a better way to do this?
Thanks for your help,
Brandon
what columns are you updating? By chance you might be updating the pk or the
identity column. Some apps update each column including the pk or identity
column.
There are two strategies to handle this problem. The first is to comment out
the identity column update you see in the second part of the proc. The
second strategy is to replace the update proc that SQL Server uses with the
proc generated by the output of this command.
sp_scriptdynamicupdproc 1
where 1 is the article id obtained from sysarticles.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Brandon" <Brandon@.discussions.microsoft.com> wrote in message
news:698DC490-4A15-429F-931D-23282017E090@.microsoft.com...
>I am running SQL Server 2000 with Service pack 3. I have a database with a
> transactional publication that is not setup for immediate or queued
> updating.
> The tables I am replicating have a primary key that is also an identity
> column. The publication pushes a subscription to a database on the same
> instance of SQL Server. The articles are setup to "Keep the existing table
> unchanged". The tables in the subscriptions are exact copies of the
> publication tables except the identity column is setup: (not for
> replication). I also have the Identity Seed setup so that neither the
> publication nor the subscription will use the same Identity. I have put in
> a
> Check Constraint on all tables to make sure of this. With this setup I can
> update the publication table and it replicates fine to the subscription
> table
> I also can update and insert records into the subscription table directly,
> but when I update the publication table I get this error when it tries to
> replicate to the subscription table: "Cannot Update Identity Column 'Table
> Name'. All I want to do is replicate my production databases to an exact
> copy
> that I will use for testing. So I never want to have the subscription
> update
> the publication, but I do want to be able to insert and update the
> subscription for testing and have the subscription be up to date with the
> publication database. Am I missing something to get this setup correctly
> or
> is there a better way to do this?
> Thanks for your help,
> Brandon
>
|||I was running a simple update statement in Query Analyzer: "Update Test set
TestColumn = 25 where TestID = 1". The stored procedure which is created by
default was trying to update the primary key like you said, so I commented
that out of the sp and that worked.
Thanks!
Brandon
"Hilary Cotter" wrote:

> what columns are you updating? By chance you might be updating the pk or the
> identity column. Some apps update each column including the pk or identity
> column.
> There are two strategies to handle this problem. The first is to comment out
> the identity column update you see in the second part of the proc. The
> second strategy is to replace the update proc that SQL Server uses with the
> proc generated by the output of this command.
> sp_scriptdynamicupdproc 1
> where 1 is the article id obtained from sysarticles.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Brandon" <Brandon@.discussions.microsoft.com> wrote in message
> news:698DC490-4A15-429F-931D-23282017E090@.microsoft.com...
>
>

Cannot update identity column

I have just taken over a sqlserver 2k database using C# (not to mention I
don't know the language). I have a column in one of the tables that is an
autonumber as well as a primary key. Here is the problem, the autonumber
was created to display the current year and a number. Example, 2004001,
2004002, 2004003, etc. Now that the year has changed, the column is still
generating numbers for 2004, when I try to update the field and change a
record for example: Update ABC set rptnum = '2005001' where rptnum =
'2004257' I get 'Cannot update identity column 'rptnum''. Not only do I
need to change the value to reflect 2005001, I also need the next record
enter to automaticly get the next number ex. 2005002, 2005003, etc.
Thanks in advance,vickie wrote:
> I have just taken over a sqlserver 2k database using C# (not to
> mention I don't know the language). I have a column in one of the
> tables that is an autonumber as well as a primary key. Here is the
> problem, the autonumber was created to display the current year and a
> number. Example, 2004001, 2004002, 2004003, etc. Now that the year
> has changed, the column is still generating numbers for 2004, when I
> try to update the field and change a record for example: Update ABC
> set rptnum = '2005001' where rptnum = '2004257' I get 'Cannot update
> identity column 'rptnum''. Not only do I need to change the value to
> reflect 2005001, I also need the next record enter to automaticly get
> the next number ex. 2005002, 2005003, etc. Thanks in advance,
AutoNumber sounds like an Access term. SQL Server uses IDENTITY
attribute on integer-type columns. THe first thing you should know about
identity columns is that they are not really designed to be intelligent
keys. There are usually used as a surrogate key, where the internal
value is meaningless.
Your inherited application is using them as intelligent keys, which I
don't think is a really good idea. Identity columns cannot be updated
directly. This is by design to prevent chaning values which SQL Server
is trying to make sure are unique.
You can update an identity by using the SET IDENTITY_INSERT ON / OFF
function before the update. In any case, you'll need to reseed the
identity value before you do this to get it to start pulling numbers
from your desired range.
You can reseed the identity using the DBCC CHECKIDENT function. See BOL
for more information on the actual parameters for the call. Reseed
first, leaving enough range for you to make the updates to the incorrect
identity values and then update the old rows. And then reconsider the
use of an identity on this table. Maybe an identity column for the PK
and a data/time column for dating would be a better idea.
David Gugick
Imceda Software
www.imceda.com|||By default, identity inserts are prohibited. However, by calling
SET IDENTITY_INSERT <table> ON/OFF
you can alter this behaviour.
So you may turn identity inserts on, insert a single new record and turn
it off again.
/Jo
vickie wrote:

> I have just taken over a sqlserver 2k database using C# (not to mention I
> don't know the language). I have a column in one of the tables that is an
> autonumber as well as a primary key. Here is the problem, the autonumber
> was created to display the current year and a number. Example, 2004001,
> 2004002, 2004003, etc. Now that the year has changed, the column is still
> generating numbers for 2004, when I try to update the field and change a
> record for example: Update ABC set rptnum = '2005001' where rptnum =
> '2004257' I get 'Cannot update identity column 'rptnum''. Not only do I
> need to change the value to reflect 2005001, I also need the next record
> enter to automaticly get the next number ex. 2005002, 2005003, etc.
> Thanks in advance,
>|||Hopefully, the person who designed this table got fired - or sent on a
course to learn something about SQL Server! ;-)
Using IDENTITY (the proper term for an "autonumber" column). as a
"meaningful" key is very wrong. I suggest that your best bet is to add
a DATETIME column (if you don't have one already) to represent the date
and then ignore the IDENTITY values. Set a default of the current date
on the DATETIME column. That will likely require some application
changes but the alternative of trying to control the IDENTITY value is
a very messy kludge.
The short term fix is to use DBCC CHECKIDENT to change the identity
seed value:
DBCC CHECKIDENT (YourTable, RESEED, 2005001)
Then delete and re-insert the rows that have the wrong ID (You cannot
UPDATE an IDENTITY value).
If you do resort to the kludge rather than fix the table properly then
you will obviously have the same problem again next year.
Hope this helps.
David Portas
SQL Server MVP
--|||First the preaching. The identity property of a column is not good for what
you are doing with it. It is only an effective tool for building a
surrogate key. It is not meant to be modified, so it makes a bad value to
show to users. Reconsider how you are doing this, and just make the column
an integer that you insert values into. I also assume that you probably
don't want the chance of getting gaps in the sequence either, which is
common with identities.
As for what you actually asked: You cannot (as the error says) update
identity values. You will have to recreate your table, insert the existing
values (can't be more than a thousand rows, apparently.) You can change
the seed:
dbcc checkident(<tablename>,reseed,20050000)
Here is a test script to show you it in action.
create table testIdentity
(
id int identity (2004001,1),
value varchar(10)
)
insert into testIdentity
values ('stuff')
insert into testIdentity
values ('stuff')
insert into testIdentity
values ('stuff')
insert into testIdentity
values ('stuff')
select * from testIdentity
/*
id value
-- --
2004001 stuff
2004002 stuff
2004003 stuff
2004004 stuff
*/
dbcc checkident(testIdentity,reseed,2005000)
insert into testIdentity
values ('stuff')
insert into testIdentity
values ('stuff')
insert into testIdentity
values ('stuff')
insert into testIdentity
values ('stuff')
select * from testIdentity
/*
id value
-- --
2004001 stuff
2004002 stuff
2004003 stuff
2004004 stuff
2005001 stuff
2005002 stuff
2005003 stuff
2005004 stuff
*/
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"vickie" <victoria.wheaton@.ngc.com> wrote in message
news:c6e431cc1937df694405d2d97d6d6c6e@.lo
calhost.talkaboutsoftware.com...
>I have just taken over a sqlserver 2k database using C# (not to mention I
> don't know the language). I have a column in one of the tables that is an
> autonumber as well as a primary key. Here is the problem, the autonumber
> was created to display the current year and a number. Example, 2004001,
> 2004002, 2004003, etc. Now that the year has changed, the column is still
> generating numbers for 2004, when I try to update the field and change a
> record for example: Update ABC set rptnum = '2005001' where rptnum =
> '2004257' I get 'Cannot update identity column 'rptnum''. Not only do I
> need to change the value to reflect 2005001, I also need the next record
> enter to automaticly get the next number ex. 2005002, 2005003, etc.
> Thanks in advance,
>|||Yeah, but the question was can you UPDATE the identity value. Which you
cannot, even with this setting set.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Jo Siffert" <jo.siffert@.gmx.net> wrote in message
news:eEb4AYtCFHA.1296@.TK2MSFTNGP10.phx.gbl...
> By default, identity inserts are prohibited. However, by calling
> SET IDENTITY_INSERT <table> ON/OFF
> you can alter this behaviour.
> So you may turn identity inserts on, insert a single new record and turn
> it off again.
> /Jo
> vickie wrote:
>|||>> You can update an identity by using the SET IDENTITY_INSERT ON / OFF
Are you sure?
Identity columns are non-updateable. The only way one could alter the
identity column value is to delete and insert the same data within a single
transaction. But then, any existing references on this column could all be
messed up.
Anith|||Anith Sen wrote:
> Are you sure?
> Identity columns are non-updateable. The only way one could alter the
> identity column value is to delete and insert the same data within a
> single transaction. But then, any existing references on this column
> could all be messed up.
Oops. Talking about updates and inserts before coffee is a bad idea.
David Gugick
Imceda Software
www.imceda.com|||On 4 Feb 2005 08:33:38 -0800, David Portas wrote:
(snip)
>If you do resort to the kludge rather than fix the table properly then
>you will obviously have the same problem again next year.
Or a much bigger problem if the company fares well and more than 999 new
rows are inserted during this year......
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||The fifth labor of Hercules was to clean the stables of King Augeas in
a single day. The Augean stables held thousands of animals and were
over a mile long. This story has a happy ending for three reasons: (1)
Hercules solved the problem in a clever way (2) Hercules got one tenth
of the cattle for his work (3) At the end of the story of the Labors of
Hercules, he got to kill the bastard that gave him this job.
You have all of the horse manure and probably cannot kill the guy who
screwed this up. Bummer.
Rows are not records; fields are not columns; tables are not files.
More importantly, autonumbers (IDENTITY, GUID, ROWID, et al) are not
keys by definition. They are exposed physical locators and have
nothing to do the logicl data model.
You need to start over and move the data to a new schema. If you
business logic is to use the 1950's IBM magnetic tape label codes, then
write a procedure to build them.
I also hope that your business rules are not to grow beyond 1000
transactions per year -- plan stagnation and failure rather than
letting it happen.

Wednesday, March 7, 2012

Cannot sync when Auto Identity Ranges Enabled?

I have been pulling my hair out with this for some time now:
I have a small db which I need to merge replicate with a number of PocketPC
devices. NT Authentication is used.
If I do not enable the automatic identity range option, the PocketPC clients
can connect without error. However, if I enable this (which I need to), the
PocketPC user gets a host of errors:
1. (Code 80004005) A call to SQL Server Reconciler failed.
2. (Code 80045007) The merge process could not retrieve article information
for publication 'Survey'
3. (Code 0) {call sp_MShelpmergearticles(?,8000000,?)}
4. (Code 4833) Could not connect to server 'SERVER1' because 'SQLAdmin' is
not defined as a remote login at the server.
SQLAdmin is our Service startup account. It is a member of the Administrator
group.
The user account being used to connect is a standard domain user account.
If I add the user to the Administrators group the process succeeds
everytime. I have checked permissions on the SQLCE folder, and snapshot
folders and AFAIK they are correct (i.e. the user has write permission).
It looks like a permission problem, but I dont know where. It seems the only
solution to this is to add the user to the Administrators group (totally
unacceptable!).
Any suggestions because I am at a complete loss?
Cheers,
James Goodman
Did you re-run your merge agent? I've had no problem using a domain user
account, particularly if set up for anonymous subscribers. Also, you should
be getting a 280xx error number back from the errors collection in the .Net
code. The MSDN site has a list that precisely defines what each of those
errors mean.
"James Goodman" <jamesATnorton-associates.co.ukREMOVE> wrote in message
news:ufn1ZWObFHA.1404@.TK2MSFTNGP09.phx.gbl...
>I have been pulling my hair out with this for some time now:
> I have a small db which I need to merge replicate with a number of
> PocketPC devices. NT Authentication is used.
> If I do not enable the automatic identity range option, the PocketPC
> clients can connect without error. However, if I enable this (which I need
> to), the PocketPC user gets a host of errors:
> 1. (Code 80004005) A call to SQL Server Reconciler failed.
> 2. (Code 80045007) The merge process could not retrieve article
> information for publication 'Survey'
> 3. (Code 0) {call sp_MShelpmergearticles(?,8000000,?)}
> 4. (Code 4833) Could not connect to server 'SERVER1' because 'SQLAdmin' is
> not defined as a remote login at the server.
> SQLAdmin is our Service startup account. It is a member of the
> Administrator group.
> The user account being used to connect is a standard domain user account.
>
> If I add the user to the Administrators group the process succeeds
> everytime. I have checked permissions on the SQLCE folder, and snapshot
> folders and AFAIK they are correct (i.e. the user has write permission).
> It looks like a permission problem, but I dont know where. It seems the
> only solution to this is to add the user to the Administrators group
> (totally unacceptable!).
>
> Any suggestions because I am at a complete loss?
> --
> Cheers,
> James Goodman
>