Showing posts with label update. Show all posts
Showing posts with label update. Show all posts

Sunday, March 25, 2012

Can't attach 2005 DB on Vista - Erland? Anyone? Help me please!

Dear Group

I've installed MSSQL 2005 STD on Vista and now can't attach my
databases.
I've installed SQL SP2 and the SQL Vista Beta Update.
The error I'm getting is:

Unable to open the physical file <Path to MDF>. Operating system error
5: '''5(Error not found)'''. (Microsoft SQL Server, Error: 5120)

Thanks for your help. All your hints are very much appreciated.

Thanks,

Martin N Feuersteiner
Invent2b GroupOn 1 Mar, 14:19, theintrepid...@.hotmail.com wrote:

Quote:

Originally Posted by

Dear Group
>
I've installed MSSQL 2005 STD on Vista and now can't attach my
databases.
I've installed SQL SP2 and the SQL Vista Beta Update.
The error I'm getting is:
>
Unable to open the physical file <Path to MDF>. Operating system error
5: '''5(Error not found)'''. (Microsoft SQL Server, Error: 5120)


Found the Solution on Vista:

- Go to Control Panel and Switch to Classic View
- Click on User Accounts
- Click on Turn User Account Control on or off
- Uncheck use User Account Control (UAC) to help protect your
computer.
- OK
- Restart System

Try again to attach the DB. It should work now.

Hope this helps anyone else having this problem.

Martin
Invent2b Group|||(theintrepidfox@.hotmail.com) writes:

Quote:

Originally Posted by

Found the Solution on Vista:
>
- Go to Control Panel and Switch to Classic View
- Click on User Accounts
- Click on Turn User Account Control on or off
- Uncheck use User Account Control (UAC) to help protect your
computer.
- OK
- Restart System
>
Try again to attach the DB. It should work now.
>
Hope this helps anyone else having this problem.


Maybe a bit heavy-duty. The point with UAC in Vista as I understand it,
is that you should not run as Administrator all the time, only when you
need it. While running as Administrator is convenient, it makes it
easier for any infections your machines may catch to spread. I think
there is a context-menu option "Run As Administrator".

Personally, I have not had any encounter with Vista yet.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On 1 Mar, 22:13, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

(theintrepid...@.hotmail.com) writes:

Quote:

Originally Posted by

Found the Solution on Vista:


>

Quote:

Originally Posted by

- Go to Control Panel and Switch to Classic View
- Click on User Accounts
- Click on Turn User Account Control on or off
- Uncheck use User Account Control (UAC) to help protect your
computer.
- OK
- Restart System


>

Quote:

Originally Posted by

Try again to attach the DB. It should work now.


>

Quote:

Originally Posted by

Hope this helps anyone else having this problem.


>
Maybe a bit heavy-duty. The point with UAC in Vista as I understand it,
is that you should not run as Administrator all the time, only when you
need it. While running as Administrator is convenient, it makes it
easier for any infections your machines may catch to spread. I think
there is a context-menu option "Run As Administrator".
>
Personally, I have not had any encounter with Vista yet.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx


Hi Erland

Correct. It's heavy duty and my first attempt was to right-click on
the Management Studio Icon and to choose the 'Run As Administrator'
option which didn't work.
UAC is indeed a good feature and helps to protect. But following this
process didn't solve the problem and there was no other choice than
having to turn off UAC.
I haven't tried turning UAC on since I attached the databases to see
what its effect is.

To put it short, UAC is good but not in a development environemnt.
Despite having turned it off VS 2005 still warns me that it may not
work correctly, needing Admin rights but it starts fine without any
errors so far. And anyway, what's the REAL risk of having it turned
off. A Vista machine with UAC off won't become less protected than a
XP machine used to be and any additional security software and common
user sense will help protect the same way. I reckon it's rather good
for my dad who clicks on and executes everything he's prompted with.
Bless him and his viruses.

If anyone has a better suggestion to this workaround which will work
with UAC on, please post it here.

All the best,

Martin
Invent2b Group|||(theintrepidfox@.hotmail.com) writes:

Quote:

Originally Posted by

Correct. It's heavy duty and my first attempt was to right-click on
the Management Studio Icon and to choose the 'Run As Administrator'
option which didn't work.
UAC is indeed a good feature and helps to protect. But following this
process didn't solve the problem and there was no other choice than
having to turn off UAC.
I haven't tried turning UAC on since I attached the databases to see
what its effect is.


Having today having had my first encounter with Vista, and I agree that it
is very irritating. (Then again, there seems to be a whole lot of
irritating fluff in Vista. I want to computer to run SQL Server on, I
don't want this "My Music" etc.)

No, I never came as far installing SQL Server. About every second time
I reboot, the machine powers off while Vista is starting. I will probably
change to a different motherboard.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I agree UAC can be annoying on a development box but I've been running Vista
with UAC and haven't had to resort to turning it off. I need to run VS as
administrator. It's optional with SSMS depending on the features I use
(e.g. stop/start services requires admin).

--
Hope this helps.

Dan Guzman
SQL Server MVP

<theintrepidfox@.hotmail.comwrote in message
news:1173031039.763449.115250@.n33g2000cwc.googlegr oups.com...

Quote:

Originally Posted by

On 1 Mar, 22:13, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

> (theintrepid...@.hotmail.com) writes:

Quote:

Originally Posted by

Found the Solution on Vista:


>>

Quote:

Originally Posted by

- Go to Control Panel and Switch to Classic View
- Click on User Accounts
- Click on Turn User Account Control on or off
- Uncheck use User Account Control (UAC) to help protect your
computer.
- OK
- Restart System


>>

Quote:

Originally Posted by

Try again to attach the DB. It should work now.


>>

Quote:

Originally Posted by

Hope this helps anyone else having this problem.


>>
>Maybe a bit heavy-duty. The point with UAC in Vista as I understand it,
>is that you should not run as Administrator all the time, only when you
>need it. While running as Administrator is convenient, it makes it
>easier for any infections your machines may catch to spread. I think
>there is a context-menu option "Run As Administrator".
>>
>Personally, I have not had any encounter with Vista yet.
>>
>--
>Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>>
>Books Online for SQL Server 2005
>athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
>Books Online for SQL Server 2000
>athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx


>
Hi Erland
>
Correct. It's heavy duty and my first attempt was to right-click on
the Management Studio Icon and to choose the 'Run As Administrator'
option which didn't work.
UAC is indeed a good feature and helps to protect. But following this
process didn't solve the problem and there was no other choice than
having to turn off UAC.
I haven't tried turning UAC on since I attached the databases to see
what its effect is.
>
To put it short, UAC is good but not in a development environemnt.
Despite having turned it off VS 2005 still warns me that it may not
work correctly, needing Admin rights but it starts fine without any
errors so far. And anyway, what's the REAL risk of having it turned
off. A Vista machine with UAC off won't become less protected than a
XP machine used to be and any additional security software and common
user sense will help protect the same way. I reckon it's rather good
for my dad who clicks on and executes everything he's prompted with.
Bless him and his viruses.
>
If anyone has a better suggestion to this workaround which will work
with UAC on, please post it here.
>
All the best,
>
Martin
Invent2b Group
>

|||>

Quote:

Originally Posted by

No, I never came as far installing SQL Server. About every second time
I reboot, the machine powers off while Vista is starting. I will probably
change to a different motherboard.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>


How does Vista behave if you install it on a non Vista PC under
Virtual PC 2007?
It does reboot during setup start-up without warning and a blank
screen but else it's much more stable and fine. Just setting up SBS
and some client PCs under Virtual Server on it.
I had that rebooting thing during startup and freezing with three PCs
(non Vista) until it turned out to be a really nasty virus despite
running all sorts of virus checks.
The solution was to actually take the drive out, and stick it as a
slave in another PC with up-to date Free AVG running which actually
detected that bugger.

The only thing that still bugs me with Vista is, and I think it's a
Vista issue, I doesn't list the databases on the SQL Server when
trying to connect (connecting is fine) to an existing instance from
Outlook Business Contact Manager. Checked all the SQL security,
logins, protocols. Any idea?

Monday, March 19, 2012

cannot write to column in updatable RecordSet

Hi. I receive the SQLException:

[Microsoft][SQLServer 2000 Driver for JDBC]Can not update, the specified column is not writable.

when I attempt to call updateString() on a RecordSet column. I have set the Statement object on which I execute the query to obtain the RecordSet to be scrollable and updatable.

My goal is to replace the ID value contained in a particular column of each row with a longer text string that includes the ID value. If I can't alter the column values via the RecordSet, can I write SQL as part of the query to generate the text string?

Thank you.

RajCan You write a little example: what You have and what You want...
And your table's structure... if it is possible. and sqlsrv version.|||Hi. We run SQL Server 2000. I call the code that creates the Statement, executes the ResultSet, and outputs the results in separate methods, so here I include a summary of the code.

Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_I NSENSITIVE, ResultSet.CONCUR_UPDATABLE);

...

rs = statement.executeQuery(queryString);

...

WriteEditSCRLinks(rs);

Here's the definition of WriteEditSCRLinks().

//Replaces each SCR ID with a hyperlink that opens the edit SCR page on the ID.
//Method needs to input an updatable, scrollable RecordSet.
void WriteEditSCRLinks(ResultSet rs) throws SQLException
{
String FieldName = "SCRID";

while (rs.next())
{
String IDValue = rs.getString(FieldName);

String UpdateString = "<A HREF=\"" + SiteConfigHelper.getEditSoftwareChangeRequestURL() + "?" + FieldName + "=" + IDValue + "\">" + IDValue + "</A>";

rs.updateString(FieldName, UpdateString);
}

//Need to move the cursor to the position before the first row
//so as not to break GraphingHelper methods.
rs.beforeFirst();
}

Since someone told me that the JDBC object model does not intend for me to alter the ResultSet if I shall not alter the database, I have to solve this problem in a different way anyway. However, I would appreciate knowing whether the concept of updating the ResultSet differs from the concept of writing to a column.

Thank you.

Raj

Sunday, March 11, 2012

Cannot use subquery in UPDATE statement?

Hi there,
I try to run the following update statement on MS SQL Server 2000 SP4
UPDATE Site
SET Active = 0
WHERE
RID IN (SELECT site.RID AS site_rid
FROM Site INNER JOIN
ManagerSite ms ON site.RID = ms.ArtistSiteID INNER JOIN
Manager m ON ms.ManagerID = m.RID
WHERE (am.UserName = 'iwk1004') AND
(ms.isPrimaryUser = 1))
Running this query I get the message "Subquery returned more than 1
value. This is not permitted when the subquery follows =, !=, <, <= ,
>, >= or when the subquery is used as an expression."
Which is weird, because according to the documentation and examples I
inferred could use UPDATE with the use of IN in the condition and the
subquery returining multiple rows. (sub-query works fine BTW)
Any idea what is going on?
Regards,
Iwaniwanvanderkleijn@.gmail.com wrote:
> Hi there,
> I try to run the following update statement on MS SQL Server 2000 SP4
> UPDATE Site
> SET Active = 0
> WHERE
> RID IN (SELECT site.RID AS site_rid
> FROM Site INNER JOIN
> ManagerSite ms ON site.RID = ms.ArtistSiteID INNER JOIN
> Manager m ON ms.ManagerID = m.RID
> WHERE (am.UserName = 'iwk1004') AND
> (ms.isPrimaryUser = 1))
> Running this query I get the message "Subquery returned more than 1
> value. This is not permitted when the subquery follows =, !=, <, <= ,
> >, >= or when the subquery is used as an expression."
> Which is weird, because according to the documentation and examples I
> inferred could use UPDATE with the use of IN in the condition and the
> subquery returining multiple rows. (sub-query works fine BTW)
> Any idea what is going on?
> Regards,
> Iwan
Your subquery returns multiple rows so change it to return some
aggregation of site.rid.
like
UPDATE Site
SET Active = 0
WHERE
RID IN (SELECT max(site.RID) AS site_rid
FROM Site INNER JOIN
ManagerSite ms ON site.RID = ms.ArtistSiteID INNER JOIN
Manager m ON ms.ManagerID = m.RID
WHERE (am.UserName = 'iwk1004') AND
(ms.isPrimaryUser = 1))
Regards
Amish Shah
http://shahamishm.tripod.com|||Not tested this at all - just an idea
UPDATE Site
SET Active = 0
FROM
(
SELECT site.RID AS site_rid
FROM Site INNER JOIN
ManagerSite ms ON site.RID = ms.ArtistSiteID INNER
JOIN
Manager m ON ms.ManagerID = m.RID
WHERE (am.UserName = 'iwk1004') AND
(ms.isPrimaryUser = 1
) as tRes
WHERE tRes.site_rid = site.RID
/* You could remove site table from the correlated subquery tRes all
together becuase it actually isn't needed and just join
tREs.ArtistSiteID = site.RID */
Or try your original subquery with EXISTS clause rather than IN|||I agree that the message makes no sense with the code shown, since
multiple values are expected after an IN.
This could be written with an EXISTS test using a correlated subquery,
rather than an IN.
UPDATE Site
SET Active = 0
WHERE EXISTS
(SELECT *
FROM ManagerSite ms
JOIN Manager m
ON ms.ManagerID = m.RID
WHERE site.RID = ms.ArtistSiteID
AND am.UserName = 'iwk1004'
AND ms.isPrimaryUser = 1)
Roy Harvey
Beacon Falls, CT
On 7 Aug 2006 03:07:06 -0700, "iwanvanderkleijn@.gmail.com"
<iwanvanderkleijn@.gmail.com> wrote:
>Hi there,
>I try to run the following update statement on MS SQL Server 2000 SP4
>UPDATE Site
>SET Active = 0
> WHERE
> RID IN (SELECT site.RID AS site_rid
> FROM Site INNER JOIN
> ManagerSite ms ON site.RID = ms.ArtistSiteID INNER JOIN
> Manager m ON ms.ManagerID = m.RID
> WHERE (am.UserName = 'iwk1004') AND
>(ms.isPrimaryUser = 1))
>Running this query I get the message "Subquery returned more than 1
>value. This is not permitted when the subquery follows =, !=, <, <= ,
>>, >= or when the subquery is used as an expression."
>Which is weird, because according to the documentation and examples I
>inferred could use UPDATE with the use of IN in the condition and the
>subquery returining multiple rows. (sub-query works fine BTW)
>Any idea what is going on?
>Regards,
>Iwan|||I'm confused about what the [am.] alias is referring to.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<iwanvanderkleijn@.gmail.com> wrote in message
news:1154945225.949531.95440@.i42g2000cwa.googlegroups.com...
> Hi there,
> I try to run the following update statement on MS SQL Server 2000 SP4
> UPDATE Site
> SET Active = 0
> WHERE
> RID IN (SELECT site.RID AS site_rid
> FROM Site INNER JOIN
> ManagerSite ms ON site.RID = ms.ArtistSiteID INNER JOIN
> Manager m ON ms.ManagerID = m.RID
> WHERE (am.UserName = 'iwk1004') AND
> (ms.isPrimaryUser = 1))
> Running this query I get the message "Subquery returned more than 1
> value. This is not permitted when the subquery follows =, !=, <, <= ,
>>, >= or when the subquery is used as an expression."
> Which is weird, because according to the documentation and examples I
> inferred could use UPDATE with the use of IN in the condition and the
> subquery returining multiple rows. (sub-query works fine BTW)
> Any idea what is going on?
> Regards,
> Iwan
>|||Arnie Rowland wrote:
> I'm confused about what the [am.] alias is referring to.
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <iwanvanderkleijn@.gmail.com> wrote in message
> news:1154945225.949531.95440@.i42g2000cwa.googlegroups.com...
> > Hi there,
> >
> > I try to run the following update statement on MS SQL Server 2000 SP4
> >
> > UPDATE Site
> >
> > SET Active = 0
> >
> > WHERE
> > RID IN (SELECT site.RID AS site_rid
> > FROM Site INNER JOIN
> > ManagerSite ms ON site.RID = ms.ArtistSiteID INNER JOIN
> > Manager m ON ms.ManagerID = m.RID
> > WHERE (am.UserName = 'iwk1004') AND
> > (ms.isPrimaryUser = 1))
> >
> > Running this query I get the message "Subquery returned more than 1
> > value. This is not permitted when the subquery follows =, !=, <, <= ,
> >>, >= or when the subquery is used as an expression."
> >
> > Which is weird, because according to the documentation and examples I
> > inferred could use UPDATE with the use of IN in the condition and the
> > subquery returining multiple rows. (sub-query works fine BTW)
> >
> > Any idea what is going on?
> >
> > Regards,
> >
> > Iwan
> >
I misunderstand the prob.
Your query looks ok but am is not referencing any table
Regards
Amish Shah|||I think it is confused over what "Site" referance you are trying to
update because it is name twice. Try the following pattern (tables
from Northwind) where you alias the update table.
update o
set o.quantity = 1
from [Order Details] o
where o.OrderId in
(select [Order Details].OrderID
from [Order Details]
inner join dbo.Orders
on [Order Details].Orderid = Orders.OrderID )
JJ

Cannot use subquery in UPDATE statement?

Hi there,
I try to run the following update statement on MS SQL Server 2000 SP4
UPDATE Site
SET Active = 0
WHERE
RID IN (SELECT site.RID AS site_rid
FROM Site INNER JOIN
ManagerSite ms ON site.RID = ms.ArtistSiteID INNER JOIN
Manager m ON ms.ManagerID = m.RID
WHERE (am.UserName = 'iwk1004') AND
(ms.isPrimaryUser = 1))
Running this query I get the message "Subquery returned more than 1
value. This is not permitted when the subquery follows =, !=, <, <= ,
>, >= or when the subquery is used as an expression."
Which is weird, because according to the documentation and examples I
inferred could use UPDATE with the use of IN in the condition and the
subquery returining multiple rows. (sub-query works fine BTW)
Any idea what is going on?
Regards,
Iwaniwanvanderkleijn@.gmail.com wrote:

> Hi there,
> I try to run the following update statement on MS SQL Server 2000 SP4
> UPDATE Site
> SET Active = 0
> WHERE
> RID IN (SELECT site.RID AS site_rid
> FROM Site INNER JOIN
> ManagerSite ms ON site.RID = ms.ArtistSiteID INNER JOIN
> Manager m ON ms.ManagerID = m.RID
> WHERE (am.UserName = 'iwk1004') AND
> (ms.isPrimaryUser = 1))
> Running this query I get the message "Subquery returned more than 1
> value. This is not permitted when the subquery follows =, !=, <, <= ,
> Which is weird, because according to the documentation and examples I
> inferred could use UPDATE with the use of IN in the condition and the
> subquery returining multiple rows. (sub-query works fine BTW)
> Any idea what is going on?
> Regards,
> Iwan
Your subquery returns multiple rows so change it to return some
aggregation of site.rid.
like
UPDATE Site
SET Active = 0
WHERE
RID IN (SELECT max(site.RID) AS site_rid
FROM Site INNER JOIN
ManagerSite ms ON site.RID = ms.ArtistSiteID INNER JOIN
Manager m ON ms.ManagerID = m.RID
WHERE (am.UserName = 'iwk1004') AND
(ms.isPrimaryUser = 1))
Regards
Amish Shah
http://shahamishm.tripod.com|||Not tested this at all - just an idea
UPDATE Site
SET Active = 0
FROM
(
SELECT site.RID AS site_rid
FROM Site INNER JOIN
ManagerSite ms ON site.RID = ms.ArtistSiteID INNER
JOIN
Manager m ON ms.ManagerID = m.RID
WHERE (am.UserName = 'iwk1004') AND
(ms.isPrimaryUser = 1
) as tRes
WHERE tRes.site_rid = site.RID
/* You could remove site table from the correlated subquery tRes all
together becuase it actually isn't needed and just join
tREs.ArtistSiteID = site.RID */
Or try your original subquery with EXISTS clause rather than IN|||I agree that the message makes no sense with the code shown, since
multiple values are expected after an IN.
This could be written with an EXISTS test using a correlated subquery,
rather than an IN.
UPDATE Site
SET Active = 0
WHERE EXISTS
(SELECT *
FROM ManagerSite ms
JOIN Manager m
ON ms.ManagerID = m.RID
WHERE site.RID = ms.ArtistSiteID
AND am.UserName = 'iwk1004'
AND ms.isPrimaryUser = 1)
Roy Harvey
Beacon Falls, CT
On 7 Aug 2006 03:07:06 -0700, "iwanvanderkleijn@.gmail.com"
<iwanvanderkleijn@.gmail.com> wrote:

>Hi there,
>I try to run the following update statement on MS SQL Server 2000 SP4
>UPDATE Site
>SET Active = 0
> WHERE
> RID IN (SELECT site.RID AS site_rid
> FROM Site INNER JOIN
> ManagerSite ms ON site.RID = ms.ArtistSiteID INNER JOIN
> Manager m ON ms.ManagerID = m.RID
> WHERE (am.UserName = 'iwk1004') AND
>(ms.isPrimaryUser = 1))
>Running this query I get the message "Subquery returned more than 1
>value. This is not permitted when the subquery follows =, !=, <, <= ,
>Which is weird, because according to the documentation and examples I
>inferred could use UPDATE with the use of IN in the condition and the
>subquery returining multiple rows. (sub-query works fine BTW)
>Any idea what is going on?
>Regards,
>Iwan|||I'm confused about what the [am.] alias is referring to.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<iwanvanderkleijn@.gmail.com> wrote in message
news:1154945225.949531.95440@.i42g2000cwa.googlegroups.com...
> Hi there,
> I try to run the following update statement on MS SQL Server 2000 SP4
> UPDATE Site
> SET Active = 0
> WHERE
> RID IN (SELECT site.RID AS site_rid
> FROM Site INNER JOIN
> ManagerSite ms ON site.RID = ms.ArtistSiteID INNER JOIN
> Manager m ON ms.ManagerID = m.RID
> WHERE (am.UserName = 'iwk1004') AND
> (ms.isPrimaryUser = 1))
> Running this query I get the message "Subquery returned more than 1
> value. This is not permitted when the subquery follows =, !=, <, <= ,
> Which is weird, because according to the documentation and examples I
> inferred could use UPDATE with the use of IN in the condition and the
> subquery returining multiple rows. (sub-query works fine BTW)
> Any idea what is going on?
> Regards,
> Iwan
>|||Arnie Rowland wrote:
[vbcol=seagreen]
> I'm confused about what the [am.] alias is referring to.
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <iwanvanderkleijn@.gmail.com> wrote in message
> news:1154945225.949531.95440@.i42g2000cwa.googlegroups.com...
I misunderstand the prob.
Your query looks ok but am is not referencing any table
Regards
Amish Shah|||I think it is confused over what "Site" referance you are trying to
update because it is name twice. Try the following pattern (tables
from Northwind) where you alias the update table.
update o
set o.quantity = 1
from [Order Details] o
where o.OrderId in
(select [Order Details].OrderID
from [Order Details]
inner join dbo.Orders
on [Order Details].Orderid = Orders.OrderID )
JJ

Thursday, March 8, 2012

Cannot update. Database or object are read-only

I have to build application Excel (interface) - SQL Server (backend)

I've created Data Source File "test.dsn" and tested succesfully.
SQL Server database in on the other machine.
This is a code that I am using.

And error message I am geting is "Cannot update. Database or object are read-only".

Dim cn As ADODB.Connection
Dim strSQL As String

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Documents and Settings\pd\Desktop\Development\test.xls;" & _
"Extended Properties=Excel 8.0"


strSQL = "INSERT INTO [DSN = test.dsn].Region Values(5, 'ab');"


cn.Execute strSQL

cn.Close
Set cn = Nothing

Read-only of databse properties is not selected.If I get it right you're attempting to insert a row into excel. As long as I know it's not possible because you cannot update, insert records throught Jet provider. mojza|||Your query is unclear . Understanding you are trying to update region table In sql database .
if that's case you should then rather open an ODBC or OLE DB connection to SQL server

cn.Provider = "sqloledb"
cn.Properties("Data Source") = ServerName
cn.Properties("Initial Catalog") = DatabaseName

' If Windows NT authentication.
cn.Properties("Integrated Security")= "SSPI"

'If SQL Authentication
cn.Properties("User ID")= UserID
cn.Properties("Password") = pwd

' Open the database.
cn.Open

Dim strSQL as string

strSQL "INSERT INTO dbo.Region Values(5, 'ab')"

cn.execute strSQL

cn.close
set cn = nothing

Cannot update the table in SQL Analyzer

I want to update the table
se.g update myTable set t1=0,t2= 0
However, i got the following error
Could not allocate space for object '(SYSTEM table id: -631615181)' in
database 'TEMPDB' because the 'DEFAULT' filegroup is full.Looks like you're either out of hard drive space on the SQL Server, or the
filegroup where your tempdb (and probably master, etc.) are. Check and make
sure you have enough drive space, and maybe run some dbcc on your database
once you're sure you have at least 10% hard drive space free.
- Nevyn
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:%23w1JmfNCFHA.3976@.tk2msftngp13.phx.gbl...
>I want to update the table
> se.g update myTable set t1=0,t2= 0
> However, i got the following error
> Could not allocate space for object '(SYSTEM table id: -631615181)' in
> database 'TEMPDB' because the 'DEFAULT' filegroup is full.
>|||Agnes
1) Restart SQL Server (It will create a new TEMPDB)
2) DBCC SHRINKFILE
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:%23w1JmfNCFHA.3976@.tk2msftngp13.phx.gbl...
> I want to update the table
> se.g update myTable set t1=0,t2= 0
> However, i got the following error
> Could not allocate space for object '(SYSTEM table id: -631615181)' in
> database 'TEMPDB' because the 'DEFAULT' filegroup is full.
>

cannot update table

I have an msde database connected to a microsoft project front end.
I am trying to update a table with values. When I use a stored procedure
everything works fine but when I try and edit the table directly I am warned
that the 'the recordset is not updatable'
Is this 'by design'? or are there permission issues?
I am logged into the database as a user almx who has database creation
rights.
I tried logging into the database as sa but I it makes no difference.
Are there any specific permissions I need to set to allow users to update
the table values directly or is this not possible in a MS Access Project?
June
Access won't allow you to update records unless there's a primary key
or unique index defined on the tables.
--Mary
On Wed, 16 Jun 2004 12:24:34 +0100, "June Macleod"
<junework@.hotmail.com> wrote:

>I have an msde database connected to a microsoft project front end.
>I am trying to update a table with values. When I use a stored procedure
>everything works fine but when I try and edit the table directly I am warned
>that the 'the recordset is not updatable'
>Is this 'by design'? or are there permission issues?
>I am logged into the database as a user almx who has database creation
>rights.
>I tried logging into the database as sa but I it makes no difference.
>Are there any specific permissions I need to set to allow users to update
>the table values directly or is this not possible in a MS Access Project?
>June
>

Cannot update some records on Sql server 2005

Hi,
I have a problem on updating some records on sql server 2005.
I can update the records in the table apart from some records.
Records are added to table by a .net application.
When I try to update some records on the table (1-2 records ~1500 records) sql server shows "executing query" message but it cannot execute the update query for these records.
on the other records updating query works fine.
Do you have any idea?Question moved to SQL Server forum .

Cannot update replication once live - have to recreate!

I have set up a transactional replication from SQL2000 (W2K) to SQL2005
(W2K3). Once it's running, if I add a new field to the publisher, it doesn't
appear in the subscriber tables. I have tried everything including
re-inititalising the job, stopping and starting agents - nothing works. All I
am left with is deleting then recreating the entire replication job which is
time consuming and annoying.
Somebody out there must have the answer to me inadequacy!
Thanks in advance,
Will, Leeds UK
Will,
I need to understand how you did this. How did you add the new field? Did
you use sp_repladdcolumn?
Also, when you say reinitializing the job I'm not sure what you mean. Do you
mean restart the snapshot agent? If so, this is not reinitialization. If the
subscription is marked for reinitialization, this is something different.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hi, basically we replicate live databases onto a separate server for
reporting. Occasionally we modify the live database (add a new field say) and
need the replication job to reflect this change. At the moment, every time we
make a change we have to remove replication, delete the reporting database
copy, then re-add replication. The live databases are on SQL 2000 and
reporting copies on SQL2005.
I haven't tried sp_repladdcolumn yet. I have just been using various actions
from SQL Management Studio.
I am a little confused as to the 'official method' to achieve what I want!
What I do know is that our current method works BUT is very tedious...
Thanks
Will
"Paul Ibison" wrote:

> Will,
> I need to understand how you did this. How did you add the new field? Did
> you use sp_repladdcolumn?
> Also, when you say reinitializing the job I'm not sure what you mean. Do you
> mean restart the snapshot agent? If so, this is not reinitialization. If the
> subscription is marked for reinitialization, this is something different.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>

Cannot Update Records that contain NULL Values!

Smile

Hi,

I am trying to use a formView with an update button to update individual records in an sql database. (when i click update it doesnt perform the update and just refreshes the page. ) One of the fields in my records is a NULL - this is also one of the fields that i need to update. When i manually go into the database and enter some data, and then go back to my form, it updates fine, but as soon as i delete the data from the field, it returns to NULL and im back to square one.

Any Ideas on how to get around this problem?

THanks

Then what's wrong? Since you delete the data from the field, it should be set to NULL. What do you expect to get after deleting the data? And what's meaing of "im back to square one"|||Fix your update statement. If you've used the wizard with "Check Original Values", then you are correct, it fails with nulls. You have to manually modify the update to handle nulls.|||

yep.. i got it now... i had to change "compare all values" to "overwrite changes". That makes sense. you cant compare null values to each other.

Thanks

Cannot update record through vb or Enterprise manager

When trying to update a specific row in a table an error
occurs.
[Microsoft][ODBC SQL SERVER DRIVER][SQL SERVER]
SQLDumExceptionhandler. Process 51 generated fatal
exception c0000005 ACCEPTION_ACCESS_VIOLATION. SQL Server
is terminating this process.
What could be the reason for this? There are no relations
hips or constraints on and between any tables.What build of SQL Server is it ? Would suggest
I would run DBCC CHECKDB to check for corruption / specifically DBCC
CHECKTABLE on the table being queried
I would then look at the last SQL Server Errorlog when it happened and see
if there are any matches on http://www.microsoft.com/support for kb's that
have a similar pattern ie on the Short Stack Dump info reported. Below is
an example - so if you had this output I would search the kb and google
(groups and web) for Fill6Xdata . Be aware that some of the function calls
are quite generic so watch out for false positives.
Ideally you should open a case with PSS
-
Short Stack Dump
0069EF5F Module(sqlservr+0029EF5F) (Fill6xData(unsigned char *,class
CXVariant *,class CTypeInfo const *,unsigned long *)+0000009A)
0069BEDE Module(sqlservr+0029BEDE) (intnl_paramdata(struct srv_proc
*,int)+000000DB)
regards,
Andy.
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:249c701c46020$9cdb8a70$a601280a@.phx
.gbl...
> When trying to update a specific row in a table an error
> occurs.
> [Microsoft][ODBC SQL SERVER DRIVER][SQL SERVER]
> SQLDumExceptionhandler. Process 51 generated fatal
> exception c0000005 ACCEPTION_ACCESS_VIOLATION. SQL Server
> is terminating this process.
> What could be the reason for this? There are no relations
> hips or constraints on and between any tables.

Cannot update record through vb or Enterprise manager

When trying to update a specific row in a table an error
occurs.
[Microsoft][ODBC SQL SERVER DRIVER][SQL SERVER]
SQLDumExceptionhandler. Process 51 generated fatal
exception c0000005 ACCEPTION_ACCESS_VIOLATION. SQL Server
is terminating this process.
What could be the reason for this? There are no relations
hips or constraints on and between any tables.
What build of SQL Server is it ? Would suggest
I would run DBCC CHECKDB to check for corruption / specifically DBCC
CHECKTABLE on the table being queried
I would then look at the last SQL Server Errorlog when it happened and see
if there are any matches on http://www.microsoft.com/support for kb's that
have a similar pattern ie on the Short Stack Dump info reported. Below is
an example - so if you had this output I would search the kb and google
(groups and web) for Fill6Xdata . Be aware that some of the function calls
are quite generic so watch out for false positives.
Ideally you should open a case with PSS
-
Short Stack Dump
0069EF5F Module(sqlservr+0029EF5F) (Fill6xData(unsigned char *,class
CXVariant *,class CTypeInfo const *,unsigned long *)+0000009A)
0069BEDE Module(sqlservr+0029BEDE) (intnl_paramdata(struct srv_proc
*,int)+000000DB)
regards,
Andy.
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:249c701c46020$9cdb8a70$a601280a@.phx.gbl...
> When trying to update a specific row in a table an error
> occurs.
> [Microsoft][ODBC SQL SERVER DRIVER][SQL SERVER]
> SQLDumExceptionhandler. Process 51 generated fatal
> exception c0000005 ACCEPTION_ACCESS_VIOLATION. SQL Server
> is terminating this process.
> What could be the reason for this? There are no relations
> hips or constraints on and between any tables.

Cannot update record in database.

Hello,

I was stuck when update database using SqlDataAdapter. I have several textboxes in my webform1. When wepform1 is loaded, they will display user's information. The user can only input content in two textboxes. When user clicks the update button, I hope to update this record in SQL Server 2000 database. However, the program didn't work. There was no error message reported but when I stepped into the code, I found out that dataadapter's update method didn't succeed. I got 0 rows affected. Can I get some advice from someone ?

Here is the code:

string StrUpdateCmd = "Update Table1 Set Hphone = @.Hphone, Cphone = @.Cphone, Team = @.Team Where emailname =@.emailname ";

SqlDataAdapter UpdateDa = new SqlDataAdapter("Select * from Table1",SqlConnection1);
UpdateDa.UpdateCommand = new SqlCommand(StrUpdateCmd, SqlConnection1);

//Add parameters of update command.
SqlParameter prm1 = UpdateDa.UpdateCommand.Parameters.Add("@.Hphone",SqlDbType.NVarChar, 16);
prm1.Value = txtHphone.Text ;

SqlParameter prm2 = UpdateDa.UpdateCommand.Parameters.Add("@.Cphone",SqlDbType.NVarChar, 16);
prm2.Value = txtCphone.Text ;

SqlParameter prm3 = UpdateDa.UpdateCommand.Parameters.Add("@.Team",SqlDbType.NVarChar, 16);
prm3.Value = this.DropDownList1.SelectedItem.Value ;

SqlParameter prm4 = UpdateDa.UpdateCommand.Parameters.Add("@.emailname",SqlDbType.NVarChar, 50);
prm4.Value = txtEmail.Text;

try
{
DataSet dataset2 = new DataSet();

//Open database connection.
SqlConnection1.Open();
dataset2.Clear();
UpdateDa.Fill(dataset2, "Table1");

this.DataGrid1.DataSource = dataset2.Tables[0] ;
DataGrid1.DataBind ();

//Update database
int ret =UpdateDa.Update(dataset2,"Table1");
if( ret == 1 )
{
ShowMessage("Update succeed!");
}
else
{
ShowMessage("There is an error in updating ");
}

UpdateDa.Fill(dataset2);

//Show data after update.
this.DataGrid1.DataSource = dataset2;
DataGrid1.DataBind ();
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlConnection1.Close();
}In your Page_Load event handler, do you only bind from the database when IsPostback==false?

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.

Cannot update ID because of no existing relationship

I have one table Phone and a table SmsMessage that are linked by the Cellnumber. Cellnumber is the primary key in Phone.

For some reason in the table Phone the Cellnumbers are stored with extra spaces like: '+27000000000 ', but in the SmsMessage table the same value is stored as '+27000000000'. However when I want perform an update to trim the Cellnumbers, I get the message 'Cannot modify values Cellnumber in Phone because there are dependent values in SmsMessage.

The fact is that there are no dependent values, but for MSSQL '+27000000000' and '+27000000000 ' is the same??! Note that the function Len(Cellnumber) gives me the length of the string WITHOUT the spaces as well.

Even if I remove all relationships from Phone, I still get the same error. Are there more places in MSSQL where relationships are stored besides the Diagrams?

Or is there a command that tells MSSQL to ignore all relationships for the next query?

Any ideas?I suspect that the data is stored in the column without the trailing spaces, although it may be presented with spaces added. This is common when retrieving data in columnar form. To test this, retrieve an expression rather than the raw column to see what you actually get, something like:SELECT myColumn, '[' + myColumn + ']'
FROM myTableThe real relationship information is stored in system tables in the form of constraints. The graphic representation is easier to understand, but the constraints are the "final answer" in terms of the relationships that MS-SQL enforces.

-PatP