If I run the following 3 statements (on sql server 2000):
create table foo (x nvarchar(128))
create unique index ix_foo on foo (x)
alter table foo alter column x nvarchar(128) not null
I get this error:
Server: Msg 5074, Level 16, State 8, Line 1
The index 'ix_foo' is dependent on column 'x'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN x failed because one or more objects access this
column.
Looking in the documentation it says "The altered column cannot be...Used in
an index, unless the column is a varchar, nvarchar, or varbinary data type,
the data type is not changed, and the new size is equal to or larger than
the old size."
Since the column is nvarchar, the data type is not changed, and the new size
is equal to the old size, this should be allowed.
Is the documentation just wrong or have I misunderstood something?
AndyAndy Fish wrote:
> Since the column is nvarchar, the data type is not changed, and the
> new size is equal to the old size, this should be allowed.
> Is the documentation just wrong or have I misunderstood something?
You need to drop the index, alter the column and then recreate the
index, that's the only way.
HTH,
Stijn Verrept.|||Disable the foreign key constraints / drop them. Alter the table (make
sure there are no NULL values in there). Activate recreate the foreign
key relationship.
HTH, Jens Suessmeyer.sql
Showing posts with label column. Show all posts
Showing posts with label column. Show all posts
Sunday, March 25, 2012
Thursday, March 22, 2012
Cant add bit column to unique index
This is for SQL 2000 (SP 2) using Enterprise Manager. I have a table with a unique index comprised of several int fields. The index needs to include an additional bit field that is part of the table. But when I go to modify the index, the bit field name doesn't appear in the Column Name list.
Can anyone shed any light on the problem?
Thanks.
You can not create index on BIT data type and that's the reason you can not see column.|||Where can I find documentation on that? Why is that restriction in place?
|||JigneshP, you definitely can create a unique index using bit fields. I was able to do it via TSQL in Query Analyzer. Here's the sql I used:
DROP INDEX [dbo].[Material].IX_MaterialCREATE UNIQUE INDEX [IX_Material]ON [dbo].[Material] ([MaterialID], [MyBitField])ON [PRIMARY]GO
The bit field is MyBitField. I then verified the index works by inserting data that duplicated another row except for the bit field.
I'm still looking for someone to tell me why I can't add the bit column to an index via Enterprise Manager.|||
I found this link (http://sqlserver2000.databases.aspfaq.com/can-i-create-an-index-on-a-bit-column.html) that shows how to do it via Enterprise Manager. You have to do it from the Tasks menu / Manage Index.
|||Oh Thanks ZLA. Sorry about that.can't add a new measure after the column is added in the DSV ?!?!
Just found one more nice error in SSAS :-)
If i add a column in my facttable, refresh the DSV and drag the new meassure into my meassuregroup i get an error when processing.
"Errors in the high-level relational engine. The data source view does not contain a definition for the 'MEA_BeregnetErhverv' column in the 'dbo_Fakt_Measures' table or view."
Well this is nice, since i just dragged it from that view :-)
Anyone got a workaround for this, except building the DSV and Cube structure from scrap each time ?
I've tried this exact scenario using Adventure Works sample database on a SQL Server AS 2005 SP1 box and it worked just fine, no errors. What version of the product are you using? Have you applied the latest service pack?
--Artur
|||
I've had this problem and after much trail fixed it by reviewing each of the Relationships in the Cube's Dimension Usage.
The problem turned out to be that one of the Measure Group Columns had a caps change since the last time I defined the relationship and AS couldn't match that column to the column in the Data Source View.
You couldn't see that it was invalid until you clicked on the dropdown list and the column name disappeared. (were as normally it would highlight the current selected column from the available columns).
can't add a new measure after the column is added in the DSV ?!?!
Just found one more nice error in SSAS :-)
If i add a column in my facttable, refresh the DSV and drag the new meassure into my meassuregroup i get an error when processing.
"Errors in the high-level relational engine. The data source view does not contain a definition for the 'MEA_BeregnetErhverv' column in the 'dbo_Fakt_Measures' table or view."
Well this is nice, since i just dragged it from that view :-)
Anyone got a workaround for this, except building the DSV and Cube structure from scrap each time ?
I've tried this exact scenario using Adventure Works sample database on a SQL Server AS 2005 SP1 box and it worked just fine, no errors. What version of the product are you using? Have you applied the latest service pack?
--Artur
|||
I've had this problem and after much trail fixed it by reviewing each of the Relationships in the Cube's Dimension Usage.
The problem turned out to be that one of the Measure Group Columns had a caps change since the last time I defined the relationship and AS couldn't match that column to the column in the Data Source View.
You couldn't see that it was invalid until you clicked on the dropdown list and the column name disappeared. (were as normally it would highlight the current selected column from the available columns).
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
[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 the OUTPUT option when passing a constant to a stored p
Hi,
I'm trying to export a text column into a text file using the bcp command
but I keep getting the error message in the subject line. So I checked the
database properties and didn't find anything.
My Code:
bcp "Select Replace(Str(UNITNUM, 12, 0), ' ', '0') FROM PICUadmissions
WHERE Admit_Date>'2005-06-01' and Admit_Date<'2005-07-01'" out
"I:\sttr\STTRLabsIn.txt" -c -T
go
Does anyone know why I am getting this message?
Much Appreciated,
ChiekoError message? Wish I could see it.
ML|||Since your BCP source is a query rather than a table/view, you need to
specify 'queryout' instead of 'out'.
Hope this helps.
Dan Guzman
SQL Server MVP
"chieko" <chieko@.discussions.microsoft.com> wrote in message
news:A82B768C-9367-447E-81DE-8EBFFAFBD62C@.microsoft.com...
> Hi,
> I'm trying to export a text column into a text file using the bcp command
> but I keep getting the error message in the subject line. So I checked the
> database properties and didn't find anything.
> My Code:
> bcp "Select Replace(Str(UNITNUM, 12, 0), ' ', '0') FROM PICUadmissions
> WHERE Admit_Date>'2005-06-01' and Admit_Date<'2005-07-01'" out
> "I:\sttr\STTRLabsIn.txt" -c -T
> go
> Does anyone know why I am getting this message?
> Much Appreciated,
> Chieko
>
I'm trying to export a text column into a text file using the bcp command
but I keep getting the error message in the subject line. So I checked the
database properties and didn't find anything.
My Code:
bcp "Select Replace(Str(UNITNUM, 12, 0), ' ', '0') FROM PICUadmissions
WHERE Admit_Date>'2005-06-01' and Admit_Date<'2005-07-01'" out
"I:\sttr\STTRLabsIn.txt" -c -T
go
Does anyone know why I am getting this message?
Much Appreciated,
ChiekoError message? Wish I could see it.
ML|||Since your BCP source is a query rather than a table/view, you need to
specify 'queryout' instead of 'out'.
Hope this helps.
Dan Guzman
SQL Server MVP
"chieko" <chieko@.discussions.microsoft.com> wrote in message
news:A82B768C-9367-447E-81DE-8EBFFAFBD62C@.microsoft.com...
> Hi,
> I'm trying to export a text column into a text file using the bcp command
> but I keep getting the error message in the subject line. So I checked the
> database properties and didn't find anything.
> My Code:
> bcp "Select Replace(Str(UNITNUM, 12, 0), ' ', '0') FROM PICUadmissions
> WHERE Admit_Date>'2005-06-01' and Admit_Date<'2005-07-01'" out
> "I:\sttr\STTRLabsIn.txt" -c -T
> go
> Does anyone know why I am getting this message?
> Much Appreciated,
> Chieko
>
cannot use datasource column twice in ssas - IBMDB2
We develop ontop of IBMDB2 and cannot use a column twice in the model.
To work arround we have to add for every usage of the column and additional column with new name to an DB2 view and use the new column in the olap model - thats really annoying.
Does someone know a solution to solve this? because ontop of sql server you could use a column N times (for example for: name, key and some other attributes - in with DB2 we have to add 3 columns.) The Error Message we got is "Error in Backend Relational Model - OLEDB Reported unknown Status ' ' for column.".
Our System:
Analysis Services 2005 SP1 ENTERPRISE 64 Bit on Windows 2003 64 Bit connection with the IBMDADB2.1 64Bit OLEDB Provider from the IBM Client Version 8.2.4 to an IBM DB2 Database Version 8.2.4 running on LINUX 64 Bit.
Thanks for your help
HANNES
When do you get the error? Is it at processing time? You can see the query that Analysis Services sends to the Relational system and determine if the query is not formed correctly. This may give you clue to what is wrong.
Are you working around it in the DSV or in an actual DB2 view? An easier workaround would be to use the DSV and add the columns there.
David Botzenhart
|||we get the error at processing time. see below for the full processing response. If we run the query at db2 directly its correct and working - so it must have something todo with anaylsis services or the oledb provider.
Mostly we work arround with the DB2 view..
This is the server processing state information
Thanks HANNES
====================
<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Parallel>
<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Object>
<DatabaseID>DWH_AT</DatabaseID>
<DimensionID>LU AGMT REQUEST SPEC</DimensionID>
</Object>
<Type>ProcessUpdate</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
</Parallel>
</Batch>
Processing Dimension 'Agreement request profile' failed.
Start time: 30.11.2006 09:44:25; End time: 30.11.2006 09:45:21; Duration: 0:00:56
Processing Dimension Attribute '(All)' completed successfully.
Start time: 30.11.2006 09:44:25; End time: 30.11.2006 09:44:25; Duration: 0:00:00
Processing Dimension Attribute 'Longname' completed successfully. 573 rows have been read.
Start time: 30.11.2006 09:44:25; End time: 30.11.2006 09:45:21; Duration: 0:00:56
SQL queries 1
SELECT
DISTINCT
"PIPE01_LU_AGMT_REQUEST_SPEC"."NAME" AS "IPE01_LU_AGMT_REQUEST_SPEC0_0"
FROM "PIPE01"."LU_AGMT_REQUEST_PROFILE" AS "PIPE01_LU_AGMT_REQUEST_SPEC"
Processing Dimension Attribute 'Product group' completed successfully. 19 rows have been read.
Start time: 30.11.2006 09:44:25; End time: 30.11.2006 09:45:21; Duration: 0:00:56
SQL queries 1
SELECT
DISTINCT
"PIPE01_LU_AGMT_REQUEST_SPEC"."SPARTE" AS "IPE01_LU_AGMT_REQUEST_SPEC0_0"
FROM "PIPE01"."LU_AGMT_REQUEST_PROFILE" AS "PIPE01_LU_AGMT_REQUEST_SPEC"
Processing Dimension Attribute 'Asset category' completed successfully. 6 rows have been read.
Start time: 30.11.2006 09:44:25; End time: 30.11.2006 09:45:21; Duration: 0:00:56
SQL queries 1
SELECT
DISTINCT
"PIPE01_LU_AGMT_REQUEST_SPEC"."CODE_GROUP_VALUE" AS "IPE01_LU_AGMT_REQUEST_SPEC0_0"
FROM "PIPE01"."LU_AGMT_REQUEST_PROFILE" AS "PIPE01_LU_AGMT_REQUEST_SPEC"
Processing Dimension Attribute 'Request reason code' completed successfully. 1698 rows have been read.
Start time: 30.11.2006 09:45:21; End time: 30.11.2006 09:45:21; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
"PIPE01_LU_AGMT_REQUEST_SPEC"."EXTERNAL_REFERENCE" AS "IPE01_LU_AGMT_REQUEST_SPEC0_0","PIPE01_LU_AGMT_REQUEST_SPEC"."EXTERNE_DARSTELLUNG" AS "IPE01_LU_AGMT_REQUEST_SPEC0_1","PIPE01_LU_AGMT_REQUEST_SPEC"."NAME" AS "IPE01_LU_AGMT_REQUEST_SPEC0_2"
FROM "PIPE01"."LU_AGMT_REQUEST_PROFILE" AS "PIPE01_LU_AGMT_REQUEST_SPEC"
Processing Dimension Attribute 'Request reason' failed.
Start time: 30.11.2006 09:45:21; End time: 30.11.2006 09:45:21; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
"PIPE01_LU_AGMT_REQUEST_SPEC"."EXTERNAL_REFERENCE" AS "IPE01_LU_AGMT_REQUEST_SPEC0_0","PIPE01_LU_AGMT_REQUEST_SPEC"."EXTERNE_DARSTELLUNG" AS "IPE01_LU_AGMT_REQUEST_SPEC0_1","PIPE01_LU_AGMT_REQUEST_SPEC"."NAME" AS "IPE01_LU_AGMT_REQUEST_SPEC0_2","PIPE01_LU_AGMT_REQUEST_SPEC"."CODE_GROUP_VALUE" AS "IPE01_LU_AGMT_REQUEST_SPEC0_3","PIPE01_LU_AGMT_REQUEST_SPEC"."SPARTE" AS "IPE01_LU_AGMT_REQUEST_SPEC0_4"
FROM "PIPE01"."LU_AGMT_REQUEST_PROFILE" AS "PIPE01_LU_AGMT_REQUEST_SPEC"
Error Messages 1
Fehler im Back-End-Datenbankzugriffsmodul. OLE DB hat den unbekannten Status '' für die 1-Spalte gemeldet. Fehler im OLAP-Speichermodul: Fehler beim Verarbeiten des Request reason-Attributs der Agreement request profile-Dimension aus der DWH_AT-Datenbank.
Errors and Warnings from Response
Fehler im Back-End-Datenbankzugriffsmodul. OLE DB hat den unbekannten Status '' für die 1-Spalte gemeldet.
Fehler im OLAP-Speichermodul: Fehler beim Verarbeiten des Request reason-Attributs der Agreement request profile-Dimension aus der DWH_AT-Datenbank.
Fehler im OLAP-Speichermodul: Der Verarbeitungsvorgang wurde beendet, da die Anzahl von Fehlern, die w?hrend der Verarbeitung ermittelt wurden, die für den Vorgang maximal zul?ssige Anzahl von Fehlern erreicht hat.
Hi Hannes,
I believe that developers in our area recently tested the DB2 v9.1 client driver (IBM OLE DB Provider for DB2, using IBM DB2 ODBC Driver version 9.01.00356), and it resolved this issue.
|||We will test later on and I reply then
thanks Hannes
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.
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
>
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
>
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
>
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...
>
>
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.
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.
Friday, February 24, 2012
Cannot shrink database because of StatBlob data on sysindexes
SQL Server 2000 SP4
Data pages from the image column StatBlob on sysIndexes exist right at the
end of my data file. They are stopping me truncating the end of my datafile.
I had other tables with text columns. These were moved by either copying the
table or bcp/truncate.
It there a way to move the data on sysIndexes without doing a "shrinkfile
move datapages"?
Regards
Paul Cahill
You can try dropping all the statistics and see if it frees up that extent.
Just make sure you know which stats you may want to put back. By the way
this is a good example of one reason why you should keep only the system
objects in the Primary filegroup and place the user objects in secondary
filegroups.
Andrew J. Kelly SQL MVP
"Paul Cahill" <anon@.anon.com> wrote in message
news:ubv2hej3GHA.3492@.TK2MSFTNGP06.phx.gbl...
> SQL Server 2000 SP4
> Data pages from the image column StatBlob on sysIndexes exist right at the
> end of my data file. They are stopping me truncating the end of my
> datafile.
> I had other tables with text columns. These were moved by either copying
> the table or bcp/truncate.
> It there a way to move the data on sysIndexes without doing a "shrinkfile
> move datapages"?
> Regards
> Paul Cahill
>
|||Hi Andrew
How do I drop statistics for indexed columns? The StatBlob values are for
indexes not for stats manually created with CREATE STATISTICS.
I could rebuild all stats on the database but I do not know if this will
move the pages where StatBlob is stored.
I tried doing a full sp_updatestats on a test database and the pages did not
seem to move for sysIndexes
(I use dbcc extentinfo)
Regards
Paul
PS No he tells me
PPS Where does it say in the manual "DO NOT USE PRIMARY"
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eJDoC%23k3GHA.4560@.TK2MSFTNGP05.phx.gbl...
> You can try dropping all the statistics and see if it frees up that
> extent. Just make sure you know which stats you may want to put back. By
> the way this is a good example of one reason why you should keep only the
> system objects in the Primary filegroup and place the user objects in
> secondary filegroups.
> --
> Andrew J. Kelly SQL MVP
> "Paul Cahill" <anon@.anon.com> wrote in message
> news:ubv2hej3GHA.3492@.TK2MSFTNGP06.phx.gbl...
>
|||Ahh, well then you may have to drop the index itself. updating stats most
likely will not do anything since it just updates the row in sysindexes that
already exists. It has no need to move it. Where as is you had a lot of
stats or indexes that you drop there is a potential for the extent they are
on to be deallocated and thus allowing the shrink.
Andrew J. Kelly SQL MVP
"Paul Cahill" <anon@.anon.com> wrote in message
news:uA2U2kl3GHA.696@.TK2MSFTNGP03.phx.gbl...
> Hi Andrew
> How do I drop statistics for indexed columns? The StatBlob values are for
> indexes not for stats manually created with CREATE STATISTICS.
> I could rebuild all stats on the database but I do not know if this will
> move the pages where StatBlob is stored.
> I tried doing a full sp_updatestats on a test database and the pages did
> not seem to move for sysIndexes
> (I use dbcc extentinfo)
> Regards
> Paul
> PS No he tells me
> PPS Where does it say in the manual "DO NOT USE PRIMARY"
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eJDoC%23k3GHA.4560@.TK2MSFTNGP05.phx.gbl...
>
|||Hi Andrew.
Given a values from dbcc ententinfo can this be linked back to the row in
sysIndexes?
I.e. there may be a single index I could recreate that would clear my block.
I had never thought about not using primary for user data.
With hindsight it makes sense. I have not seen any Microsoft documents that
mention this.
It would make more sense if SQL Server simply kept it's system tables on a
private filegroup.
Paul
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e1Qn9Uo3GHA.1256@.TK2MSFTNGP04.phx.gbl...
> Ahh, well then you may have to drop the index itself. updating stats most
> likely will not do anything since it just updates the row in sysindexes
> that already exists. It has no need to move it. Where as is you had a lot
> of stats or indexes that you drop there is a potential for the extent they
> are on to be deallocated and thus allowing the shrink.
> --
> Andrew J. Kelly SQL MVP
> "Paul Cahill" <anon@.anon.com> wrote in message
> news:uA2U2kl3GHA.696@.TK2MSFTNGP03.phx.gbl...
>
|||To be honest I don't know if it can be linked back or not but it would not
be a single index as that would only take up one row in sysindexes. You
would need to clear the entire extent so it can be deallocated. Going
forward in 2005 there is even more reason to separate the filegroups (user
from primary) due to how you deal with partial restores. I have to admit
this is the first time I have seen sysindexes be an issue for shrinking data
files.
Andrew J. Kelly SQL MVP
"Paul Cahill" <noname@.noname.com> wrote in message
news:Oa86zOw3GHA.4972@.TK2MSFTNGP03.phx.gbl...
> Hi Andrew.
> Given a values from dbcc ententinfo can this be linked back to the row in
> sysIndexes?
> I.e. there may be a single index I could recreate that would clear my
> block.
> I had never thought about not using primary for user data.
> With hindsight it makes sense. I have not seen any Microsoft documents
> that mention this.
> It would make more sense if SQL Server simply kept it's system tables on a
> private filegroup.
> Paul
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:e1Qn9Uo3GHA.1256@.TK2MSFTNGP04.phx.gbl...
>
|||Too many text columns. Too many tables. Our system dates back to SQL 6.5.
I noticed SQL 2005 has a text cleanup.
I think the only way out of this for me is a long slow process of moving out
followed by shrinkfile with move data.
Disk space permitting.
It's a big database and it's being logshipped across a wan with litespeed.
Thanks
Paul
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23YJd2Vx3GHA.1588@.TK2MSFTNGP02.phx.gbl...
> To be honest I don't know if it can be linked back or not but it would not
> be a single index as that would only take up one row in sysindexes. You
> would need to clear the entire extent so it can be deallocated. Going
> forward in 2005 there is even more reason to separate the filegroups (user
> from primary) due to how you deal with partial restores. I have to admit
> this is the first time I have seen sysindexes be an issue for shrinking
> data files.
> --
> Andrew J. Kelly SQL MVP
> "Paul Cahill" <noname@.noname.com> wrote in message
> news:Oa86zOw3GHA.4972@.TK2MSFTNGP03.phx.gbl...
>
Data pages from the image column StatBlob on sysIndexes exist right at the
end of my data file. They are stopping me truncating the end of my datafile.
I had other tables with text columns. These were moved by either copying the
table or bcp/truncate.
It there a way to move the data on sysIndexes without doing a "shrinkfile
move datapages"?
Regards
Paul Cahill
You can try dropping all the statistics and see if it frees up that extent.
Just make sure you know which stats you may want to put back. By the way
this is a good example of one reason why you should keep only the system
objects in the Primary filegroup and place the user objects in secondary
filegroups.
Andrew J. Kelly SQL MVP
"Paul Cahill" <anon@.anon.com> wrote in message
news:ubv2hej3GHA.3492@.TK2MSFTNGP06.phx.gbl...
> SQL Server 2000 SP4
> Data pages from the image column StatBlob on sysIndexes exist right at the
> end of my data file. They are stopping me truncating the end of my
> datafile.
> I had other tables with text columns. These were moved by either copying
> the table or bcp/truncate.
> It there a way to move the data on sysIndexes without doing a "shrinkfile
> move datapages"?
> Regards
> Paul Cahill
>
|||Hi Andrew
How do I drop statistics for indexed columns? The StatBlob values are for
indexes not for stats manually created with CREATE STATISTICS.
I could rebuild all stats on the database but I do not know if this will
move the pages where StatBlob is stored.
I tried doing a full sp_updatestats on a test database and the pages did not
seem to move for sysIndexes
(I use dbcc extentinfo)
Regards
Paul
PS No he tells me
PPS Where does it say in the manual "DO NOT USE PRIMARY"
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eJDoC%23k3GHA.4560@.TK2MSFTNGP05.phx.gbl...
> You can try dropping all the statistics and see if it frees up that
> extent. Just make sure you know which stats you may want to put back. By
> the way this is a good example of one reason why you should keep only the
> system objects in the Primary filegroup and place the user objects in
> secondary filegroups.
> --
> Andrew J. Kelly SQL MVP
> "Paul Cahill" <anon@.anon.com> wrote in message
> news:ubv2hej3GHA.3492@.TK2MSFTNGP06.phx.gbl...
>
|||Ahh, well then you may have to drop the index itself. updating stats most
likely will not do anything since it just updates the row in sysindexes that
already exists. It has no need to move it. Where as is you had a lot of
stats or indexes that you drop there is a potential for the extent they are
on to be deallocated and thus allowing the shrink.
Andrew J. Kelly SQL MVP
"Paul Cahill" <anon@.anon.com> wrote in message
news:uA2U2kl3GHA.696@.TK2MSFTNGP03.phx.gbl...
> Hi Andrew
> How do I drop statistics for indexed columns? The StatBlob values are for
> indexes not for stats manually created with CREATE STATISTICS.
> I could rebuild all stats on the database but I do not know if this will
> move the pages where StatBlob is stored.
> I tried doing a full sp_updatestats on a test database and the pages did
> not seem to move for sysIndexes
> (I use dbcc extentinfo)
> Regards
> Paul
> PS No he tells me
> PPS Where does it say in the manual "DO NOT USE PRIMARY"
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eJDoC%23k3GHA.4560@.TK2MSFTNGP05.phx.gbl...
>
|||Hi Andrew.
Given a values from dbcc ententinfo can this be linked back to the row in
sysIndexes?
I.e. there may be a single index I could recreate that would clear my block.
I had never thought about not using primary for user data.
With hindsight it makes sense. I have not seen any Microsoft documents that
mention this.
It would make more sense if SQL Server simply kept it's system tables on a
private filegroup.
Paul
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e1Qn9Uo3GHA.1256@.TK2MSFTNGP04.phx.gbl...
> Ahh, well then you may have to drop the index itself. updating stats most
> likely will not do anything since it just updates the row in sysindexes
> that already exists. It has no need to move it. Where as is you had a lot
> of stats or indexes that you drop there is a potential for the extent they
> are on to be deallocated and thus allowing the shrink.
> --
> Andrew J. Kelly SQL MVP
> "Paul Cahill" <anon@.anon.com> wrote in message
> news:uA2U2kl3GHA.696@.TK2MSFTNGP03.phx.gbl...
>
|||To be honest I don't know if it can be linked back or not but it would not
be a single index as that would only take up one row in sysindexes. You
would need to clear the entire extent so it can be deallocated. Going
forward in 2005 there is even more reason to separate the filegroups (user
from primary) due to how you deal with partial restores. I have to admit
this is the first time I have seen sysindexes be an issue for shrinking data
files.
Andrew J. Kelly SQL MVP
"Paul Cahill" <noname@.noname.com> wrote in message
news:Oa86zOw3GHA.4972@.TK2MSFTNGP03.phx.gbl...
> Hi Andrew.
> Given a values from dbcc ententinfo can this be linked back to the row in
> sysIndexes?
> I.e. there may be a single index I could recreate that would clear my
> block.
> I had never thought about not using primary for user data.
> With hindsight it makes sense. I have not seen any Microsoft documents
> that mention this.
> It would make more sense if SQL Server simply kept it's system tables on a
> private filegroup.
> Paul
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:e1Qn9Uo3GHA.1256@.TK2MSFTNGP04.phx.gbl...
>
|||Too many text columns. Too many tables. Our system dates back to SQL 6.5.
I noticed SQL 2005 has a text cleanup.
I think the only way out of this for me is a long slow process of moving out
followed by shrinkfile with move data.
Disk space permitting.
It's a big database and it's being logshipped across a wan with litespeed.
Thanks
Paul
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23YJd2Vx3GHA.1588@.TK2MSFTNGP02.phx.gbl...
> To be honest I don't know if it can be linked back or not but it would not
> be a single index as that would only take up one row in sysindexes. You
> would need to clear the entire extent so it can be deallocated. Going
> forward in 2005 there is even more reason to separate the filegroups (user
> from primary) due to how you deal with partial restores. I have to admit
> this is the first time I have seen sysindexes be an issue for shrinking
> data files.
> --
> Andrew J. Kelly SQL MVP
> "Paul Cahill" <noname@.noname.com> wrote in message
> news:Oa86zOw3GHA.4972@.TK2MSFTNGP03.phx.gbl...
>
Cannot shrink database because of StatBlob data on sysindexes
SQL Server 2000 SP4
Data pages from the image column StatBlob on sysIndexes exist right at the
end of my data file. They are stopping me truncating the end of my datafile.
I had other tables with text columns. These were moved by either copying the
table or bcp/truncate.
It there a way to move the data on sysIndexes without doing a "shrinkfile
move datapages"?
Regards
Paul CahillYou can try dropping all the statistics and see if it frees up that extent.
Just make sure you know which stats you may want to put back. By the way
this is a good example of one reason why you should keep only the system
objects in the Primary filegroup and place the user objects in secondary
filegroups.
Andrew J. Kelly SQL MVP
"Paul Cahill" <anon@.anon.com> wrote in message
news:ubv2hej3GHA.3492@.TK2MSFTNGP06.phx.gbl...
> SQL Server 2000 SP4
> Data pages from the image column StatBlob on sysIndexes exist right at the
> end of my data file. They are stopping me truncating the end of my
> datafile.
> I had other tables with text columns. These were moved by either copying
> the table or bcp/truncate.
> It there a way to move the data on sysIndexes without doing a "shrinkfile
> move datapages"?
> Regards
> Paul Cahill
>|||Hi Andrew
How do I drop statistics for indexed columns? The StatBlob values are for
indexes not for stats manually created with CREATE STATISTICS.
I could rebuild all stats on the database but I do not know if this will
move the pages where StatBlob is stored.
I tried doing a full sp_updatestats on a test database and the pages did not
seem to move for sysIndexes
(I use dbcc extentinfo)
Regards
Paul
PS No he tells me
PPS Where does it say in the manual "DO NOT USE PRIMARY"
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eJDoC%23k3GHA.4560@.TK2MSFTNGP05.phx.gbl...
> You can try dropping all the statistics and see if it frees up that
> extent. Just make sure you know which stats you may want to put back. By
> the way this is a good example of one reason why you should keep only the
> system objects in the Primary filegroup and place the user objects in
> secondary filegroups.
> --
> Andrew J. Kelly SQL MVP
> "Paul Cahill" <anon@.anon.com> wrote in message
> news:ubv2hej3GHA.3492@.TK2MSFTNGP06.phx.gbl...
>|||Ahh, well then you may have to drop the index itself. updating stats most
likely will not do anything since it just updates the row in sysindexes that
already exists. It has no need to move it. Where as is you had a lot of
stats or indexes that you drop there is a potential for the extent they are
on to be deallocated and thus allowing the shrink.
Andrew J. Kelly SQL MVP
"Paul Cahill" <anon@.anon.com> wrote in message
news:uA2U2kl3GHA.696@.TK2MSFTNGP03.phx.gbl...
> Hi Andrew
> How do I drop statistics for indexed columns? The StatBlob values are for
> indexes not for stats manually created with CREATE STATISTICS.
> I could rebuild all stats on the database but I do not know if this will
> move the pages where StatBlob is stored.
> I tried doing a full sp_updatestats on a test database and the pages did
> not seem to move for sysIndexes
> (I use dbcc extentinfo)
> Regards
> Paul
> PS No he tells me
> PPS Where does it say in the manual "DO NOT USE PRIMARY"
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eJDoC%23k3GHA.4560@.TK2MSFTNGP05.phx.gbl...
>|||Hi Andrew.
Given a values from dbcc ententinfo can this be linked back to the row in
sysIndexes?
I.e. there may be a single index I could recreate that would clear my block.
I had never thought about not using primary for user data.
With hindsight it makes sense. I have not seen any Microsoft documents that
mention this.
It would make more sense if SQL Server simply kept it's system tables on a
private filegroup.
Paul
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e1Qn9Uo3GHA.1256@.TK2MSFTNGP04.phx.gbl...
> Ahh, well then you may have to drop the index itself. updating stats most
> likely will not do anything since it just updates the row in sysindexes
> that already exists. It has no need to move it. Where as is you had a lot
> of stats or indexes that you drop there is a potential for the extent they
> are on to be deallocated and thus allowing the shrink.
> --
> Andrew J. Kelly SQL MVP
> "Paul Cahill" <anon@.anon.com> wrote in message
> news:uA2U2kl3GHA.696@.TK2MSFTNGP03.phx.gbl...
>|||To be honest I don't know if it can be linked back or not but it would not
be a single index as that would only take up one row in sysindexes. You
would need to clear the entire extent so it can be deallocated. Going
forward in 2005 there is even more reason to separate the filegroups (user
from primary) due to how you deal with partial restores. I have to admit
this is the first time I have seen sysindexes be an issue for shrinking data
files.
Andrew J. Kelly SQL MVP
"Paul Cahill" <noname@.noname.com> wrote in message
news:Oa86zOw3GHA.4972@.TK2MSFTNGP03.phx.gbl...
> Hi Andrew.
> Given a values from dbcc ententinfo can this be linked back to the row in
> sysIndexes?
> I.e. there may be a single index I could recreate that would clear my
> block.
> I had never thought about not using primary for user data.
> With hindsight it makes sense. I have not seen any Microsoft documents
> that mention this.
> It would make more sense if SQL Server simply kept it's system tables on a
> private filegroup.
> Paul
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:e1Qn9Uo3GHA.1256@.TK2MSFTNGP04.phx.gbl...
>|||Too many text columns. Too many tables. Our system dates back to SQL 6.5.
I noticed SQL 2005 has a text cleanup.
I think the only way out of this for me is a long slow process of moving out
followed by shrinkfile with move data.
Disk space permitting.
It's a big database and it's being logshipped across a wan with litespeed.
Thanks
Paul
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23YJd2Vx3GHA.1588@.TK2MSFTNGP02.phx.gbl...
> To be honest I don't know if it can be linked back or not but it would not
> be a single index as that would only take up one row in sysindexes. You
> would need to clear the entire extent so it can be deallocated. Going
> forward in 2005 there is even more reason to separate the filegroups (user
> from primary) due to how you deal with partial restores. I have to admit
> this is the first time I have seen sysindexes be an issue for shrinking
> data files.
> --
> Andrew J. Kelly SQL MVP
> "Paul Cahill" <noname@.noname.com> wrote in message
> news:Oa86zOw3GHA.4972@.TK2MSFTNGP03.phx.gbl...
>
Data pages from the image column StatBlob on sysIndexes exist right at the
end of my data file. They are stopping me truncating the end of my datafile.
I had other tables with text columns. These were moved by either copying the
table or bcp/truncate.
It there a way to move the data on sysIndexes without doing a "shrinkfile
move datapages"?
Regards
Paul CahillYou can try dropping all the statistics and see if it frees up that extent.
Just make sure you know which stats you may want to put back. By the way
this is a good example of one reason why you should keep only the system
objects in the Primary filegroup and place the user objects in secondary
filegroups.
Andrew J. Kelly SQL MVP
"Paul Cahill" <anon@.anon.com> wrote in message
news:ubv2hej3GHA.3492@.TK2MSFTNGP06.phx.gbl...
> SQL Server 2000 SP4
> Data pages from the image column StatBlob on sysIndexes exist right at the
> end of my data file. They are stopping me truncating the end of my
> datafile.
> I had other tables with text columns. These were moved by either copying
> the table or bcp/truncate.
> It there a way to move the data on sysIndexes without doing a "shrinkfile
> move datapages"?
> Regards
> Paul Cahill
>|||Hi Andrew
How do I drop statistics for indexed columns? The StatBlob values are for
indexes not for stats manually created with CREATE STATISTICS.
I could rebuild all stats on the database but I do not know if this will
move the pages where StatBlob is stored.
I tried doing a full sp_updatestats on a test database and the pages did not
seem to move for sysIndexes
(I use dbcc extentinfo)
Regards
Paul
PS No he tells me
PPS Where does it say in the manual "DO NOT USE PRIMARY"
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eJDoC%23k3GHA.4560@.TK2MSFTNGP05.phx.gbl...
> You can try dropping all the statistics and see if it frees up that
> extent. Just make sure you know which stats you may want to put back. By
> the way this is a good example of one reason why you should keep only the
> system objects in the Primary filegroup and place the user objects in
> secondary filegroups.
> --
> Andrew J. Kelly SQL MVP
> "Paul Cahill" <anon@.anon.com> wrote in message
> news:ubv2hej3GHA.3492@.TK2MSFTNGP06.phx.gbl...
>|||Ahh, well then you may have to drop the index itself. updating stats most
likely will not do anything since it just updates the row in sysindexes that
already exists. It has no need to move it. Where as is you had a lot of
stats or indexes that you drop there is a potential for the extent they are
on to be deallocated and thus allowing the shrink.
Andrew J. Kelly SQL MVP
"Paul Cahill" <anon@.anon.com> wrote in message
news:uA2U2kl3GHA.696@.TK2MSFTNGP03.phx.gbl...
> Hi Andrew
> How do I drop statistics for indexed columns? The StatBlob values are for
> indexes not for stats manually created with CREATE STATISTICS.
> I could rebuild all stats on the database but I do not know if this will
> move the pages where StatBlob is stored.
> I tried doing a full sp_updatestats on a test database and the pages did
> not seem to move for sysIndexes
> (I use dbcc extentinfo)
> Regards
> Paul
> PS No he tells me
> PPS Where does it say in the manual "DO NOT USE PRIMARY"
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eJDoC%23k3GHA.4560@.TK2MSFTNGP05.phx.gbl...
>|||Hi Andrew.
Given a values from dbcc ententinfo can this be linked back to the row in
sysIndexes?
I.e. there may be a single index I could recreate that would clear my block.
I had never thought about not using primary for user data.
With hindsight it makes sense. I have not seen any Microsoft documents that
mention this.
It would make more sense if SQL Server simply kept it's system tables on a
private filegroup.
Paul
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e1Qn9Uo3GHA.1256@.TK2MSFTNGP04.phx.gbl...
> Ahh, well then you may have to drop the index itself. updating stats most
> likely will not do anything since it just updates the row in sysindexes
> that already exists. It has no need to move it. Where as is you had a lot
> of stats or indexes that you drop there is a potential for the extent they
> are on to be deallocated and thus allowing the shrink.
> --
> Andrew J. Kelly SQL MVP
> "Paul Cahill" <anon@.anon.com> wrote in message
> news:uA2U2kl3GHA.696@.TK2MSFTNGP03.phx.gbl...
>|||To be honest I don't know if it can be linked back or not but it would not
be a single index as that would only take up one row in sysindexes. You
would need to clear the entire extent so it can be deallocated. Going
forward in 2005 there is even more reason to separate the filegroups (user
from primary) due to how you deal with partial restores. I have to admit
this is the first time I have seen sysindexes be an issue for shrinking data
files.
Andrew J. Kelly SQL MVP
"Paul Cahill" <noname@.noname.com> wrote in message
news:Oa86zOw3GHA.4972@.TK2MSFTNGP03.phx.gbl...
> Hi Andrew.
> Given a values from dbcc ententinfo can this be linked back to the row in
> sysIndexes?
> I.e. there may be a single index I could recreate that would clear my
> block.
> I had never thought about not using primary for user data.
> With hindsight it makes sense. I have not seen any Microsoft documents
> that mention this.
> It would make more sense if SQL Server simply kept it's system tables on a
> private filegroup.
> Paul
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:e1Qn9Uo3GHA.1256@.TK2MSFTNGP04.phx.gbl...
>|||Too many text columns. Too many tables. Our system dates back to SQL 6.5.
I noticed SQL 2005 has a text cleanup.
I think the only way out of this for me is a long slow process of moving out
followed by shrinkfile with move data.
Disk space permitting.
It's a big database and it's being logshipped across a wan with litespeed.
Thanks
Paul
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23YJd2Vx3GHA.1588@.TK2MSFTNGP02.phx.gbl...
> To be honest I don't know if it can be linked back or not but it would not
> be a single index as that would only take up one row in sysindexes. You
> would need to clear the entire extent so it can be deallocated. Going
> forward in 2005 there is even more reason to separate the filegroups (user
> from primary) due to how you deal with partial restores. I have to admit
> this is the first time I have seen sysindexes be an issue for shrinking
> data files.
> --
> Andrew J. Kelly SQL MVP
> "Paul Cahill" <noname@.noname.com> wrote in message
> news:Oa86zOw3GHA.4972@.TK2MSFTNGP03.phx.gbl...
>
Cannot shrink database because of StatBlob data on sysindexes
SQL Server 2000 SP4
Data pages from the image column StatBlob on sysIndexes exist right at the
end of my data file. They are stopping me truncating the end of my datafile.
I had other tables with text columns. These were moved by either copying the
table or bcp/truncate.
It there a way to move the data on sysIndexes without doing a "shrinkfile
move datapages"?
Regards
Paul CahillYou can try dropping all the statistics and see if it frees up that extent.
Just make sure you know which stats you may want to put back. By the way
this is a good example of one reason why you should keep only the system
objects in the Primary filegroup and place the user objects in secondary
filegroups.
--
Andrew J. Kelly SQL MVP
"Paul Cahill" <anon@.anon.com> wrote in message
news:ubv2hej3GHA.3492@.TK2MSFTNGP06.phx.gbl...
> SQL Server 2000 SP4
> Data pages from the image column StatBlob on sysIndexes exist right at the
> end of my data file. They are stopping me truncating the end of my
> datafile.
> I had other tables with text columns. These were moved by either copying
> the table or bcp/truncate.
> It there a way to move the data on sysIndexes without doing a "shrinkfile
> move datapages"?
> Regards
> Paul Cahill
>|||Hi Andrew
How do I drop statistics for indexed columns? The StatBlob values are for
indexes not for stats manually created with CREATE STATISTICS.
I could rebuild all stats on the database but I do not know if this will
move the pages where StatBlob is stored.
I tried doing a full sp_updatestats on a test database and the pages did not
seem to move for sysIndexes
(I use dbcc extentinfo)
Regards
Paul
PS No he tells me
PPS Where does it say in the manual "DO NOT USE PRIMARY"
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eJDoC%23k3GHA.4560@.TK2MSFTNGP05.phx.gbl...
> You can try dropping all the statistics and see if it frees up that
> extent. Just make sure you know which stats you may want to put back. By
> the way this is a good example of one reason why you should keep only the
> system objects in the Primary filegroup and place the user objects in
> secondary filegroups.
> --
> Andrew J. Kelly SQL MVP
> "Paul Cahill" <anon@.anon.com> wrote in message
> news:ubv2hej3GHA.3492@.TK2MSFTNGP06.phx.gbl...
>> SQL Server 2000 SP4
>> Data pages from the image column StatBlob on sysIndexes exist right at
>> the end of my data file. They are stopping me truncating the end of my
>> datafile.
>> I had other tables with text columns. These were moved by either copying
>> the table or bcp/truncate.
>> It there a way to move the data on sysIndexes without doing a "shrinkfile
>> move datapages"?
>> Regards
>> Paul Cahill
>|||Ahh, well then you may have to drop the index itself. updating stats most
likely will not do anything since it just updates the row in sysindexes that
already exists. It has no need to move it. Where as is you had a lot of
stats or indexes that you drop there is a potential for the extent they are
on to be deallocated and thus allowing the shrink.
--
Andrew J. Kelly SQL MVP
"Paul Cahill" <anon@.anon.com> wrote in message
news:uA2U2kl3GHA.696@.TK2MSFTNGP03.phx.gbl...
> Hi Andrew
> How do I drop statistics for indexed columns? The StatBlob values are for
> indexes not for stats manually created with CREATE STATISTICS.
> I could rebuild all stats on the database but I do not know if this will
> move the pages where StatBlob is stored.
> I tried doing a full sp_updatestats on a test database and the pages did
> not seem to move for sysIndexes
> (I use dbcc extentinfo)
> Regards
> Paul
> PS No he tells me
> PPS Where does it say in the manual "DO NOT USE PRIMARY"
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eJDoC%23k3GHA.4560@.TK2MSFTNGP05.phx.gbl...
>> You can try dropping all the statistics and see if it frees up that
>> extent. Just make sure you know which stats you may want to put back. By
>> the way this is a good example of one reason why you should keep only the
>> system objects in the Primary filegroup and place the user objects in
>> secondary filegroups.
>> --
>> Andrew J. Kelly SQL MVP
>> "Paul Cahill" <anon@.anon.com> wrote in message
>> news:ubv2hej3GHA.3492@.TK2MSFTNGP06.phx.gbl...
>> SQL Server 2000 SP4
>> Data pages from the image column StatBlob on sysIndexes exist right at
>> the end of my data file. They are stopping me truncating the end of my
>> datafile.
>> I had other tables with text columns. These were moved by either copying
>> the table or bcp/truncate.
>> It there a way to move the data on sysIndexes without doing a
>> "shrinkfile move datapages"?
>> Regards
>> Paul Cahill
>>
>|||Hi Andrew.
Given a values from dbcc ententinfo can this be linked back to the row in
sysIndexes?
I.e. there may be a single index I could recreate that would clear my block.
I had never thought about not using primary for user data.
With hindsight it makes sense. I have not seen any Microsoft documents that
mention this.
It would make more sense if SQL Server simply kept it's system tables on a
private filegroup.
Paul
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e1Qn9Uo3GHA.1256@.TK2MSFTNGP04.phx.gbl...
> Ahh, well then you may have to drop the index itself. updating stats most
> likely will not do anything since it just updates the row in sysindexes
> that already exists. It has no need to move it. Where as is you had a lot
> of stats or indexes that you drop there is a potential for the extent they
> are on to be deallocated and thus allowing the shrink.
> --
> Andrew J. Kelly SQL MVP
> "Paul Cahill" <anon@.anon.com> wrote in message
> news:uA2U2kl3GHA.696@.TK2MSFTNGP03.phx.gbl...
>> Hi Andrew
>> How do I drop statistics for indexed columns? The StatBlob values are for
>> indexes not for stats manually created with CREATE STATISTICS.
>> I could rebuild all stats on the database but I do not know if this will
>> move the pages where StatBlob is stored.
>> I tried doing a full sp_updatestats on a test database and the pages did
>> not seem to move for sysIndexes
>> (I use dbcc extentinfo)
>> Regards
>> Paul
>> PS No he tells me
>> PPS Where does it say in the manual "DO NOT USE PRIMARY"
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:eJDoC%23k3GHA.4560@.TK2MSFTNGP05.phx.gbl...
>> You can try dropping all the statistics and see if it frees up that
>> extent. Just make sure you know which stats you may want to put back.
>> By the way this is a good example of one reason why you should keep only
>> the system objects in the Primary filegroup and place the user objects
>> in secondary filegroups.
>> --
>> Andrew J. Kelly SQL MVP
>> "Paul Cahill" <anon@.anon.com> wrote in message
>> news:ubv2hej3GHA.3492@.TK2MSFTNGP06.phx.gbl...
>> SQL Server 2000 SP4
>> Data pages from the image column StatBlob on sysIndexes exist right at
>> the end of my data file. They are stopping me truncating the end of my
>> datafile.
>> I had other tables with text columns. These were moved by either
>> copying the table or bcp/truncate.
>> It there a way to move the data on sysIndexes without doing a
>> "shrinkfile move datapages"?
>> Regards
>> Paul Cahill
>>
>>
>|||To be honest I don't know if it can be linked back or not but it would not
be a single index as that would only take up one row in sysindexes. You
would need to clear the entire extent so it can be deallocated. Going
forward in 2005 there is even more reason to separate the filegroups (user
from primary) due to how you deal with partial restores. I have to admit
this is the first time I have seen sysindexes be an issue for shrinking data
files.
--
Andrew J. Kelly SQL MVP
"Paul Cahill" <noname@.noname.com> wrote in message
news:Oa86zOw3GHA.4972@.TK2MSFTNGP03.phx.gbl...
> Hi Andrew.
> Given a values from dbcc ententinfo can this be linked back to the row in
> sysIndexes?
> I.e. there may be a single index I could recreate that would clear my
> block.
> I had never thought about not using primary for user data.
> With hindsight it makes sense. I have not seen any Microsoft documents
> that mention this.
> It would make more sense if SQL Server simply kept it's system tables on a
> private filegroup.
> Paul
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:e1Qn9Uo3GHA.1256@.TK2MSFTNGP04.phx.gbl...
>> Ahh, well then you may have to drop the index itself. updating stats
>> most likely will not do anything since it just updates the row in
>> sysindexes that already exists. It has no need to move it. Where as is
>> you had a lot of stats or indexes that you drop there is a potential for
>> the extent they are on to be deallocated and thus allowing the shrink.
>> --
>> Andrew J. Kelly SQL MVP
>> "Paul Cahill" <anon@.anon.com> wrote in message
>> news:uA2U2kl3GHA.696@.TK2MSFTNGP03.phx.gbl...
>> Hi Andrew
>> How do I drop statistics for indexed columns? The StatBlob values are
>> for indexes not for stats manually created with CREATE STATISTICS.
>> I could rebuild all stats on the database but I do not know if this will
>> move the pages where StatBlob is stored.
>> I tried doing a full sp_updatestats on a test database and the pages did
>> not seem to move for sysIndexes
>> (I use dbcc extentinfo)
>> Regards
>> Paul
>> PS No he tells me
>> PPS Where does it say in the manual "DO NOT USE PRIMARY"
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:eJDoC%23k3GHA.4560@.TK2MSFTNGP05.phx.gbl...
>> You can try dropping all the statistics and see if it frees up that
>> extent. Just make sure you know which stats you may want to put back.
>> By the way this is a good example of one reason why you should keep
>> only the system objects in the Primary filegroup and place the user
>> objects in secondary filegroups.
>> --
>> Andrew J. Kelly SQL MVP
>> "Paul Cahill" <anon@.anon.com> wrote in message
>> news:ubv2hej3GHA.3492@.TK2MSFTNGP06.phx.gbl...
>> SQL Server 2000 SP4
>> Data pages from the image column StatBlob on sysIndexes exist right at
>> the end of my data file. They are stopping me truncating the end of my
>> datafile.
>> I had other tables with text columns. These were moved by either
>> copying the table or bcp/truncate.
>> It there a way to move the data on sysIndexes without doing a
>> "shrinkfile move datapages"?
>> Regards
>> Paul Cahill
>>
>>
>>
>|||Too many text columns. Too many tables. Our system dates back to SQL 6.5.
I noticed SQL 2005 has a text cleanup.
I think the only way out of this for me is a long slow process of moving out
followed by shrinkfile with move data.
Disk space permitting.
It's a big database and it's being logshipped across a wan with litespeed.
Thanks
Paul
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23YJd2Vx3GHA.1588@.TK2MSFTNGP02.phx.gbl...
> To be honest I don't know if it can be linked back or not but it would not
> be a single index as that would only take up one row in sysindexes. You
> would need to clear the entire extent so it can be deallocated. Going
> forward in 2005 there is even more reason to separate the filegroups (user
> from primary) due to how you deal with partial restores. I have to admit
> this is the first time I have seen sysindexes be an issue for shrinking
> data files.
> --
> Andrew J. Kelly SQL MVP
> "Paul Cahill" <noname@.noname.com> wrote in message
> news:Oa86zOw3GHA.4972@.TK2MSFTNGP03.phx.gbl...
>> Hi Andrew.
>> Given a values from dbcc ententinfo can this be linked back to the row in
>> sysIndexes?
>> I.e. there may be a single index I could recreate that would clear my
>> block.
>> I had never thought about not using primary for user data.
>> With hindsight it makes sense. I have not seen any Microsoft documents
>> that mention this.
>> It would make more sense if SQL Server simply kept it's system tables on
>> a private filegroup.
>> Paul
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:e1Qn9Uo3GHA.1256@.TK2MSFTNGP04.phx.gbl...
>> Ahh, well then you may have to drop the index itself. updating stats
>> most likely will not do anything since it just updates the row in
>> sysindexes that already exists. It has no need to move it. Where as is
>> you had a lot of stats or indexes that you drop there is a potential for
>> the extent they are on to be deallocated and thus allowing the shrink.
>> --
>> Andrew J. Kelly SQL MVP
>> "Paul Cahill" <anon@.anon.com> wrote in message
>> news:uA2U2kl3GHA.696@.TK2MSFTNGP03.phx.gbl...
>> Hi Andrew
>> How do I drop statistics for indexed columns? The StatBlob values are
>> for indexes not for stats manually created with CREATE STATISTICS.
>> I could rebuild all stats on the database but I do not know if this
>> will move the pages where StatBlob is stored.
>> I tried doing a full sp_updatestats on a test database and the pages
>> did not seem to move for sysIndexes
>> (I use dbcc extentinfo)
>> Regards
>> Paul
>> PS No he tells me
>> PPS Where does it say in the manual "DO NOT USE PRIMARY"
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:eJDoC%23k3GHA.4560@.TK2MSFTNGP05.phx.gbl...
>> You can try dropping all the statistics and see if it frees up that
>> extent. Just make sure you know which stats you may want to put back.
>> By the way this is a good example of one reason why you should keep
>> only the system objects in the Primary filegroup and place the user
>> objects in secondary filegroups.
>> --
>> Andrew J. Kelly SQL MVP
>> "Paul Cahill" <anon@.anon.com> wrote in message
>> news:ubv2hej3GHA.3492@.TK2MSFTNGP06.phx.gbl...
>> SQL Server 2000 SP4
>> Data pages from the image column StatBlob on sysIndexes exist right
>> at the end of my data file. They are stopping me truncating the end
>> of my datafile.
>> I had other tables with text columns. These were moved by either
>> copying the table or bcp/truncate.
>> It there a way to move the data on sysIndexes without doing a
>> "shrinkfile move datapages"?
>> Regards
>> Paul Cahill
>>
>>
>>
>>
>
Data pages from the image column StatBlob on sysIndexes exist right at the
end of my data file. They are stopping me truncating the end of my datafile.
I had other tables with text columns. These were moved by either copying the
table or bcp/truncate.
It there a way to move the data on sysIndexes without doing a "shrinkfile
move datapages"?
Regards
Paul CahillYou can try dropping all the statistics and see if it frees up that extent.
Just make sure you know which stats you may want to put back. By the way
this is a good example of one reason why you should keep only the system
objects in the Primary filegroup and place the user objects in secondary
filegroups.
--
Andrew J. Kelly SQL MVP
"Paul Cahill" <anon@.anon.com> wrote in message
news:ubv2hej3GHA.3492@.TK2MSFTNGP06.phx.gbl...
> SQL Server 2000 SP4
> Data pages from the image column StatBlob on sysIndexes exist right at the
> end of my data file. They are stopping me truncating the end of my
> datafile.
> I had other tables with text columns. These were moved by either copying
> the table or bcp/truncate.
> It there a way to move the data on sysIndexes without doing a "shrinkfile
> move datapages"?
> Regards
> Paul Cahill
>|||Hi Andrew
How do I drop statistics for indexed columns? The StatBlob values are for
indexes not for stats manually created with CREATE STATISTICS.
I could rebuild all stats on the database but I do not know if this will
move the pages where StatBlob is stored.
I tried doing a full sp_updatestats on a test database and the pages did not
seem to move for sysIndexes
(I use dbcc extentinfo)
Regards
Paul
PS No he tells me
PPS Where does it say in the manual "DO NOT USE PRIMARY"
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eJDoC%23k3GHA.4560@.TK2MSFTNGP05.phx.gbl...
> You can try dropping all the statistics and see if it frees up that
> extent. Just make sure you know which stats you may want to put back. By
> the way this is a good example of one reason why you should keep only the
> system objects in the Primary filegroup and place the user objects in
> secondary filegroups.
> --
> Andrew J. Kelly SQL MVP
> "Paul Cahill" <anon@.anon.com> wrote in message
> news:ubv2hej3GHA.3492@.TK2MSFTNGP06.phx.gbl...
>> SQL Server 2000 SP4
>> Data pages from the image column StatBlob on sysIndexes exist right at
>> the end of my data file. They are stopping me truncating the end of my
>> datafile.
>> I had other tables with text columns. These were moved by either copying
>> the table or bcp/truncate.
>> It there a way to move the data on sysIndexes without doing a "shrinkfile
>> move datapages"?
>> Regards
>> Paul Cahill
>|||Ahh, well then you may have to drop the index itself. updating stats most
likely will not do anything since it just updates the row in sysindexes that
already exists. It has no need to move it. Where as is you had a lot of
stats or indexes that you drop there is a potential for the extent they are
on to be deallocated and thus allowing the shrink.
--
Andrew J. Kelly SQL MVP
"Paul Cahill" <anon@.anon.com> wrote in message
news:uA2U2kl3GHA.696@.TK2MSFTNGP03.phx.gbl...
> Hi Andrew
> How do I drop statistics for indexed columns? The StatBlob values are for
> indexes not for stats manually created with CREATE STATISTICS.
> I could rebuild all stats on the database but I do not know if this will
> move the pages where StatBlob is stored.
> I tried doing a full sp_updatestats on a test database and the pages did
> not seem to move for sysIndexes
> (I use dbcc extentinfo)
> Regards
> Paul
> PS No he tells me
> PPS Where does it say in the manual "DO NOT USE PRIMARY"
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eJDoC%23k3GHA.4560@.TK2MSFTNGP05.phx.gbl...
>> You can try dropping all the statistics and see if it frees up that
>> extent. Just make sure you know which stats you may want to put back. By
>> the way this is a good example of one reason why you should keep only the
>> system objects in the Primary filegroup and place the user objects in
>> secondary filegroups.
>> --
>> Andrew J. Kelly SQL MVP
>> "Paul Cahill" <anon@.anon.com> wrote in message
>> news:ubv2hej3GHA.3492@.TK2MSFTNGP06.phx.gbl...
>> SQL Server 2000 SP4
>> Data pages from the image column StatBlob on sysIndexes exist right at
>> the end of my data file. They are stopping me truncating the end of my
>> datafile.
>> I had other tables with text columns. These were moved by either copying
>> the table or bcp/truncate.
>> It there a way to move the data on sysIndexes without doing a
>> "shrinkfile move datapages"?
>> Regards
>> Paul Cahill
>>
>|||Hi Andrew.
Given a values from dbcc ententinfo can this be linked back to the row in
sysIndexes?
I.e. there may be a single index I could recreate that would clear my block.
I had never thought about not using primary for user data.
With hindsight it makes sense. I have not seen any Microsoft documents that
mention this.
It would make more sense if SQL Server simply kept it's system tables on a
private filegroup.
Paul
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e1Qn9Uo3GHA.1256@.TK2MSFTNGP04.phx.gbl...
> Ahh, well then you may have to drop the index itself. updating stats most
> likely will not do anything since it just updates the row in sysindexes
> that already exists. It has no need to move it. Where as is you had a lot
> of stats or indexes that you drop there is a potential for the extent they
> are on to be deallocated and thus allowing the shrink.
> --
> Andrew J. Kelly SQL MVP
> "Paul Cahill" <anon@.anon.com> wrote in message
> news:uA2U2kl3GHA.696@.TK2MSFTNGP03.phx.gbl...
>> Hi Andrew
>> How do I drop statistics for indexed columns? The StatBlob values are for
>> indexes not for stats manually created with CREATE STATISTICS.
>> I could rebuild all stats on the database but I do not know if this will
>> move the pages where StatBlob is stored.
>> I tried doing a full sp_updatestats on a test database and the pages did
>> not seem to move for sysIndexes
>> (I use dbcc extentinfo)
>> Regards
>> Paul
>> PS No he tells me
>> PPS Where does it say in the manual "DO NOT USE PRIMARY"
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:eJDoC%23k3GHA.4560@.TK2MSFTNGP05.phx.gbl...
>> You can try dropping all the statistics and see if it frees up that
>> extent. Just make sure you know which stats you may want to put back.
>> By the way this is a good example of one reason why you should keep only
>> the system objects in the Primary filegroup and place the user objects
>> in secondary filegroups.
>> --
>> Andrew J. Kelly SQL MVP
>> "Paul Cahill" <anon@.anon.com> wrote in message
>> news:ubv2hej3GHA.3492@.TK2MSFTNGP06.phx.gbl...
>> SQL Server 2000 SP4
>> Data pages from the image column StatBlob on sysIndexes exist right at
>> the end of my data file. They are stopping me truncating the end of my
>> datafile.
>> I had other tables with text columns. These were moved by either
>> copying the table or bcp/truncate.
>> It there a way to move the data on sysIndexes without doing a
>> "shrinkfile move datapages"?
>> Regards
>> Paul Cahill
>>
>>
>|||To be honest I don't know if it can be linked back or not but it would not
be a single index as that would only take up one row in sysindexes. You
would need to clear the entire extent so it can be deallocated. Going
forward in 2005 there is even more reason to separate the filegroups (user
from primary) due to how you deal with partial restores. I have to admit
this is the first time I have seen sysindexes be an issue for shrinking data
files.
--
Andrew J. Kelly SQL MVP
"Paul Cahill" <noname@.noname.com> wrote in message
news:Oa86zOw3GHA.4972@.TK2MSFTNGP03.phx.gbl...
> Hi Andrew.
> Given a values from dbcc ententinfo can this be linked back to the row in
> sysIndexes?
> I.e. there may be a single index I could recreate that would clear my
> block.
> I had never thought about not using primary for user data.
> With hindsight it makes sense. I have not seen any Microsoft documents
> that mention this.
> It would make more sense if SQL Server simply kept it's system tables on a
> private filegroup.
> Paul
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:e1Qn9Uo3GHA.1256@.TK2MSFTNGP04.phx.gbl...
>> Ahh, well then you may have to drop the index itself. updating stats
>> most likely will not do anything since it just updates the row in
>> sysindexes that already exists. It has no need to move it. Where as is
>> you had a lot of stats or indexes that you drop there is a potential for
>> the extent they are on to be deallocated and thus allowing the shrink.
>> --
>> Andrew J. Kelly SQL MVP
>> "Paul Cahill" <anon@.anon.com> wrote in message
>> news:uA2U2kl3GHA.696@.TK2MSFTNGP03.phx.gbl...
>> Hi Andrew
>> How do I drop statistics for indexed columns? The StatBlob values are
>> for indexes not for stats manually created with CREATE STATISTICS.
>> I could rebuild all stats on the database but I do not know if this will
>> move the pages where StatBlob is stored.
>> I tried doing a full sp_updatestats on a test database and the pages did
>> not seem to move for sysIndexes
>> (I use dbcc extentinfo)
>> Regards
>> Paul
>> PS No he tells me
>> PPS Where does it say in the manual "DO NOT USE PRIMARY"
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:eJDoC%23k3GHA.4560@.TK2MSFTNGP05.phx.gbl...
>> You can try dropping all the statistics and see if it frees up that
>> extent. Just make sure you know which stats you may want to put back.
>> By the way this is a good example of one reason why you should keep
>> only the system objects in the Primary filegroup and place the user
>> objects in secondary filegroups.
>> --
>> Andrew J. Kelly SQL MVP
>> "Paul Cahill" <anon@.anon.com> wrote in message
>> news:ubv2hej3GHA.3492@.TK2MSFTNGP06.phx.gbl...
>> SQL Server 2000 SP4
>> Data pages from the image column StatBlob on sysIndexes exist right at
>> the end of my data file. They are stopping me truncating the end of my
>> datafile.
>> I had other tables with text columns. These were moved by either
>> copying the table or bcp/truncate.
>> It there a way to move the data on sysIndexes without doing a
>> "shrinkfile move datapages"?
>> Regards
>> Paul Cahill
>>
>>
>>
>|||Too many text columns. Too many tables. Our system dates back to SQL 6.5.
I noticed SQL 2005 has a text cleanup.
I think the only way out of this for me is a long slow process of moving out
followed by shrinkfile with move data.
Disk space permitting.
It's a big database and it's being logshipped across a wan with litespeed.
Thanks
Paul
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23YJd2Vx3GHA.1588@.TK2MSFTNGP02.phx.gbl...
> To be honest I don't know if it can be linked back or not but it would not
> be a single index as that would only take up one row in sysindexes. You
> would need to clear the entire extent so it can be deallocated. Going
> forward in 2005 there is even more reason to separate the filegroups (user
> from primary) due to how you deal with partial restores. I have to admit
> this is the first time I have seen sysindexes be an issue for shrinking
> data files.
> --
> Andrew J. Kelly SQL MVP
> "Paul Cahill" <noname@.noname.com> wrote in message
> news:Oa86zOw3GHA.4972@.TK2MSFTNGP03.phx.gbl...
>> Hi Andrew.
>> Given a values from dbcc ententinfo can this be linked back to the row in
>> sysIndexes?
>> I.e. there may be a single index I could recreate that would clear my
>> block.
>> I had never thought about not using primary for user data.
>> With hindsight it makes sense. I have not seen any Microsoft documents
>> that mention this.
>> It would make more sense if SQL Server simply kept it's system tables on
>> a private filegroup.
>> Paul
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:e1Qn9Uo3GHA.1256@.TK2MSFTNGP04.phx.gbl...
>> Ahh, well then you may have to drop the index itself. updating stats
>> most likely will not do anything since it just updates the row in
>> sysindexes that already exists. It has no need to move it. Where as is
>> you had a lot of stats or indexes that you drop there is a potential for
>> the extent they are on to be deallocated and thus allowing the shrink.
>> --
>> Andrew J. Kelly SQL MVP
>> "Paul Cahill" <anon@.anon.com> wrote in message
>> news:uA2U2kl3GHA.696@.TK2MSFTNGP03.phx.gbl...
>> Hi Andrew
>> How do I drop statistics for indexed columns? The StatBlob values are
>> for indexes not for stats manually created with CREATE STATISTICS.
>> I could rebuild all stats on the database but I do not know if this
>> will move the pages where StatBlob is stored.
>> I tried doing a full sp_updatestats on a test database and the pages
>> did not seem to move for sysIndexes
>> (I use dbcc extentinfo)
>> Regards
>> Paul
>> PS No he tells me
>> PPS Where does it say in the manual "DO NOT USE PRIMARY"
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:eJDoC%23k3GHA.4560@.TK2MSFTNGP05.phx.gbl...
>> You can try dropping all the statistics and see if it frees up that
>> extent. Just make sure you know which stats you may want to put back.
>> By the way this is a good example of one reason why you should keep
>> only the system objects in the Primary filegroup and place the user
>> objects in secondary filegroups.
>> --
>> Andrew J. Kelly SQL MVP
>> "Paul Cahill" <anon@.anon.com> wrote in message
>> news:ubv2hej3GHA.3492@.TK2MSFTNGP06.phx.gbl...
>> SQL Server 2000 SP4
>> Data pages from the image column StatBlob on sysIndexes exist right
>> at the end of my data file. They are stopping me truncating the end
>> of my datafile.
>> I had other tables with text columns. These were moved by either
>> copying the table or bcp/truncate.
>> It there a way to move the data on sysIndexes without doing a
>> "shrinkfile move datapages"?
>> Regards
>> Paul Cahill
>>
>>
>>
>>
>
Sunday, February 19, 2012
Cannot set Default value for column using "Alter column"
Well here's one of those excruciatingly simple obstacles:
In SQL Server 2005 (Mgmt Studio): according to BOL, the syntax to set a default value for an existing column is:
ALTER TABLE MyCustomers ALTER COLUMN CompanyName SET DEFAULT 'A. Datum Corporation'
However, when I Check:
alter table CommissionPayment alter column Amount Set Default 0
I get the error message:
"Incorrect syntax near the keyword 'Set'."
No other combinations of this syntax work.
Help! What am I missing?? DEFAULT is not a column property in T-SQL -- it's actually implemented as a constraint. The correct syntax is: CREATE TABLE #x (id int) ALTER TABLE #x ADD CONSTRAINT DF_id DEFAULT (0) FOR id Where in BOL did you see that syntax? -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Karl Kaiser@.discussions.microsoft.com> wrote in message news:a5caf4ff-8884-4383-960f-b4f61aad2da2@.discussions.microsoft.com...Well here's one of those excruciatingly simple obstacles:In SQL Server 2005 (Mgmt Studio): according to BOL, the syntax to set a default value for an existing column is:[b]ALTER TABLE MyCustomers ALTER COLUMN CompanyName SET DEFAULT 'A. Datum Corporation'[/b]However, when I Check:[b]alter table CommissionPayment alter column Amount Set Default 0[/b]I get the error message:[b]"Incorrect syntax near the keyword 'Set'."[/b]No other combinations of this syntax work.Help! What am I missing?|||The syntax you saw was for SQL Server Mobile Edition. It is not applicable for the other SQL Server editions. So the correct way is to add default as a constraint using syntax posted by Adam.
Friday, February 10, 2012
Cannot re-read data in column 2
Hi,
I'm connecting to my SQL Server via Java in my Notes environment, but when I try to update my Notes-documents I get an error fra SQL Server saying that it can not re-read data from column 2.
Without being absolutely certain - I'm quite sure that the code is valid.
Does anyone know what the error means ?
Thanks in advance
Kim HansenHi,
I'm connecting to my SQL Server via Java in my Notes environment, but when I try to update my Notes-documents I get an error fra SQL Server saying that it can not re-read data from column 2.
Without being absolutely certain - I'm quite sure that the code is valid.
Does anyone know what the error means ?
Thanks in advance
Kim Hansen
there is this KB on this error:
http://support.microsoft.com/default.aspx?kbid=824106
I'm connecting to my SQL Server via Java in my Notes environment, but when I try to update my Notes-documents I get an error fra SQL Server saying that it can not re-read data from column 2.
Without being absolutely certain - I'm quite sure that the code is valid.
Does anyone know what the error means ?
Thanks in advance
Kim HansenHi,
I'm connecting to my SQL Server via Java in my Notes environment, but when I try to update my Notes-documents I get an error fra SQL Server saying that it can not re-read data from column 2.
Without being absolutely certain - I'm quite sure that the code is valid.
Does anyone know what the error means ?
Thanks in advance
Kim Hansen
there is this KB on this error:
http://support.microsoft.com/default.aspx?kbid=824106
Subscribe to:
Comments (Atom)