I'm in the process of trying to convert a database such that all the strings
(VARCHAR) are converted to wide strings (NVARCHAR). I have a script that
accomplishes this by removing all the primary key constraints, converts the
necessary columns, and then replaces the constraints. The script walks the
sysnames table and stores all the constraints in a table variable, and
constructs a script to recreate all the constraings based on the 'xtype'
column from sysindexes (this is based on the system stored procedure
sp_pkeys). The script creates a constraint if the xtype is of type 'PK', or
creates an index based on the INDEXPROPERTY of the index, whether it be
unique, and either clustered or non-clustered.
This works for the most part, but I have found that there are constraints
being created on some columns that did not exist before the conversion. For
example, I have a table which has a primary key on it's identity columns
defined to automatically insert a new value at each insert incremented by 1.
After the conversion, there is an additional constraint placed on this table
which prevents a value of NULL from being added, which should be a problem
due to the IDENTITY column, yet attempting to do an insert on this table
generates an error saying that a NULL value cannot be inserted. I'm not
manually inserting anything, this should just bump the id value by one and
do the insert, but this new constraint prevents this, leaving me with a
table I can no longer insert into.
In another case, I have several varchar columns that have default
constraints (simple text strings), which are also dropped before conversion.
Upon replacing the constraints read from sysnames, I get similar errors
regarding not being able to insert nulls into these columns, which I didn't
get before, as these columns had default values.
My questions are, is it possible to exactly recreate constraints
programmatically? Is there a preffered method for converting databases from
narrow to wide character?
Thanks for any advice,
-GaryPlease do not post the same message to multiple newsgroups independently.sql
Showing posts with label nvarchar. Show all posts
Showing posts with label nvarchar. Show all posts
Tuesday, March 27, 2012
altering tables and constraints
I'm in the process of trying to convert a database such that all the strings
(VARCHAR) are converted to wide strings (NVARCHAR). I have a script that
accomplishes this by removing all the primary key constraints, converts the
necessary columns, and then replaces the constraints. The script walks the
sysnames table and stores all the constraints in a table variable, and
constructs a script to recreate all the constraings based on the 'xtype'
column from sysindexes (this is based on the system stored procedure
sp_pkeys). The script creates a constraint if the xtype is of type 'PK', or
creates an index based on the INDEXPROPERTY of the index, whether it be
unique, and either clustered or non-clustered.
This works for the most part, but I have found that there are constraints
being created on some columns that did not exist before the conversion. For
example, I have a table which has a primary key on it's identity columns
defined to automatically insert a new value at each insert incremented by 1.
After the conversion, there is an additional constraint placed on this table
which prevents a value of NULL from being added, which should be a problem
due to the IDENTITY column, yet attempting to do an insert on this table
generates an error saying that a NULL value cannot be inserted. I'm not
manually inserting anything, this should just bump the id value by one and
do the insert, but this new constraint prevents this, leaving me with a
table I can no longer insert into.
In another case, I have several varchar columns that have default
constraints (simple text strings), which are also dropped before conversion.
Upon replacing the constraints read from sysnames, I get similar errors
regarding not being able to insert nulls into these columns, which I didn't
get before, as these columns had default values.
My questions are, is it possible to exactly recreate constraints
programmatically? Is there a preffered method for converting databases from
narrow to wide character?
Thanks for any advice,
-Gary
Please do not post the same message to multiple newsgroups independently.
(VARCHAR) are converted to wide strings (NVARCHAR). I have a script that
accomplishes this by removing all the primary key constraints, converts the
necessary columns, and then replaces the constraints. The script walks the
sysnames table and stores all the constraints in a table variable, and
constructs a script to recreate all the constraings based on the 'xtype'
column from sysindexes (this is based on the system stored procedure
sp_pkeys). The script creates a constraint if the xtype is of type 'PK', or
creates an index based on the INDEXPROPERTY of the index, whether it be
unique, and either clustered or non-clustered.
This works for the most part, but I have found that there are constraints
being created on some columns that did not exist before the conversion. For
example, I have a table which has a primary key on it's identity columns
defined to automatically insert a new value at each insert incremented by 1.
After the conversion, there is an additional constraint placed on this table
which prevents a value of NULL from being added, which should be a problem
due to the IDENTITY column, yet attempting to do an insert on this table
generates an error saying that a NULL value cannot be inserted. I'm not
manually inserting anything, this should just bump the id value by one and
do the insert, but this new constraint prevents this, leaving me with a
table I can no longer insert into.
In another case, I have several varchar columns that have default
constraints (simple text strings), which are also dropped before conversion.
Upon replacing the constraints read from sysnames, I get similar errors
regarding not being able to insert nulls into these columns, which I didn't
get before, as these columns had default values.
My questions are, is it possible to exactly recreate constraints
programmatically? Is there a preffered method for converting databases from
narrow to wide character?
Thanks for any advice,
-Gary
Please do not post the same message to multiple newsgroups independently.
altering tables and constraints
I'm in the process of trying to convert a database such that all the strings
(VARCHAR) are converted to wide strings (NVARCHAR). I have a script that
accomplishes this by removing all the primary key constraints, converts the
necessary columns, and then replaces the constraints. The script walks the
sysnames table and stores all the constraints in a table variable, and
constructs a script to recreate all the constraings based on the 'xtype'
column from sysindexes (this is based on the system stored procedure
sp_pkeys). The script creates a constraint if the xtype is of type 'PK', or
creates an index based on the INDEXPROPERTY of the index, whether it be
unique, and either clustered or non-clustered.
This works for the most part, but I have found that there are constraints
being created on some columns that did not exist before the conversion. For
example, I have a table which has a primary key on it's identity columns
defined to automatically insert a new value at each insert incremented by 1.
After the conversion, there is an additional constraint placed on this table
which prevents a value of NULL from being added, which should be a problem
due to the IDENTITY column, yet attempting to do an insert on this table
generates an error saying that a NULL value cannot be inserted. I'm not
manually inserting anything, this should just bump the id value by one and
do the insert, but this new constraint prevents this, leaving me with a
table I can no longer insert into.
In another case, I have several varchar columns that have default
constraints (simple text strings), which are also dropped before conversion.
Upon replacing the constraints read from sysnames, I get similar errors
regarding not being able to insert nulls into these columns, which I didn't
get before, as these columns had default values.
My questions are, is it possible to exactly recreate constraints
programmatically? Is there a preffered method for converting databases from
narrow to wide character?
Thanks for any advice,
-GaryPlease do not post the same message to multiple newsgroups independently.
(VARCHAR) are converted to wide strings (NVARCHAR). I have a script that
accomplishes this by removing all the primary key constraints, converts the
necessary columns, and then replaces the constraints. The script walks the
sysnames table and stores all the constraints in a table variable, and
constructs a script to recreate all the constraings based on the 'xtype'
column from sysindexes (this is based on the system stored procedure
sp_pkeys). The script creates a constraint if the xtype is of type 'PK', or
creates an index based on the INDEXPROPERTY of the index, whether it be
unique, and either clustered or non-clustered.
This works for the most part, but I have found that there are constraints
being created on some columns that did not exist before the conversion. For
example, I have a table which has a primary key on it's identity columns
defined to automatically insert a new value at each insert incremented by 1.
After the conversion, there is an additional constraint placed on this table
which prevents a value of NULL from being added, which should be a problem
due to the IDENTITY column, yet attempting to do an insert on this table
generates an error saying that a NULL value cannot be inserted. I'm not
manually inserting anything, this should just bump the id value by one and
do the insert, but this new constraint prevents this, leaving me with a
table I can no longer insert into.
In another case, I have several varchar columns that have default
constraints (simple text strings), which are also dropped before conversion.
Upon replacing the constraints read from sysnames, I get similar errors
regarding not being able to insert nulls into these columns, which I didn't
get before, as these columns had default values.
My questions are, is it possible to exactly recreate constraints
programmatically? Is there a preffered method for converting databases from
narrow to wide character?
Thanks for any advice,
-GaryPlease do not post the same message to multiple newsgroups independently.
Altering column fields with a Stored Procedure
I have some columns of data in SQL server that are of NVARCHAR(420)
format but they are dates. The dates are in DD/MM/YY format. I want to
be able to convert them to our accounting system format which is
YYYYMMDD. I know the format is strange but it will make things easier
in the long run if all of the dates are the same when working between
the 2 different databases. Basically, I need to take a look at the
year portion (with a SUBSTRING function maybe) to see if it is greater
than 50 (there will not be any dates that are less than 1950) and if
it is concatenate 19 with it (ex. 65 = 1965). Then, concatenate the
month and day from the rest to form the date we need in NUMERIC(8).
So, a date of January 17, 2003 (currently in the format of 17/01/03)
would become 20030117. In VB, the function I would write is something
like the following:
/*
Dim sCurrentDate as String
Dim sMon as string
Dim sDay as String
Dim sYear as String
Dim sNewDate as String
format but they are dates. The dates are in DD/MM/YY format. I want to
be able to convert them to our accounting system format which is
YYYYMMDD. I know the format is strange but it will make things easier
in the long run if all of the dates are the same when working between
the 2 different databases. Basically, I need to take a look at the
year portion (with a SUBSTRING function maybe) to see if it is greater
than 50 (there will not be any dates that are less than 1950) and if
it is concatenate 19 with it (ex. 65 = 1965). Then, concatenate the
month and day from the rest to form the date we need in NUMERIC(8).
So, a date of January 17, 2003 (currently in the format of 17/01/03)
would become 20030117. In VB, the function I would write is something
like the following:
/*
Dim sCurrentDate as String
Dim sMon as string
Dim sDay as String
Dim sYear as String
Dim sNewDate as String
sCurrentDate = "17/01/03"
sMon = Mid(sCurrentDate, 4, 2)
sDay = Mid(sCurrentDate, 1, 2)
sYear = Mid(sCurrentDate, 7, 2)
If sYear < 50 Then
sYear = "20" & sYear
ElseIf sYear > 50 Then
sYear = "19" & sYear
End if
sNewDate = sYear & sMon & sDay
*/
I was thinking of doing this in a Stored Procedure but am really rusty
with SQL (it's been since college).
The datatype would end up being NUMERIC(8). How I would write it if I
new how to write it would be: grab the column name prior to the
procedure, create a temp column, format the values, place them into
the temp column, delete the old column, and then rename the temp
column to the name of the column that I grabbed in the beginning of
the procedure. Most likely this is the only way to do it but I have no
idea how to go about it.mwoodward@.quinnpumps.com (Milo Woodward) wrote in message news:<1615a5e3.0308142112.30d6548c@.posting.google.com>...
> I have some columns of data in SQL server that are of NVARCHAR(420)
> format but they are dates. The dates are in DD/MM/YY format. I want to
> be able to convert them to our accounting system format which is
> YYYYMMDD. I know the format is strange but it will make things easier
> in the long run if all of the dates are the same when working between
> the 2 different databases. Basically, I need to take a look at the
> year portion (with a SUBSTRING function maybe) to see if it is greater
> than 50 (there will not be any dates that are less than 1950) and if
> it is concatenate 19 with it (ex. 65 = 1965). Then, concatenate the
> month and day from the rest to form the date we need in NUMERIC(8).
> So, a date of January 17, 2003 (currently in the format of 17/01/03)
> would become 20030117. In VB, the function I would write is something
> like the following:
> /*
> Dim sCurrentDate as String
> Dim sMon as string
> Dim sDay as String
> Dim sYear as String
> Dim sNewDate as String
> sCurrentDate = "17/01/03"
> sMon = Mid(sCurrentDate, 4, 2)
> sDay = Mid(sCurrentDate, 1, 2)
> sYear = Mid(sCurrentDate, 7, 2)
> If sYear < 50 Then
> sYear = "20" & sYear
> ElseIf sYear > 50 Then
> sYear = "19" & sYear
> End if
> sNewDate = sYear & sMon & sDay
> */
> I was thinking of doing this in a Stored Procedure but am really rusty
> with SQL (it's been since college).
> The datatype would end up being NUMERIC(8). How I would write it if I
> new how to write it would be: grab the column name prior to the
> procedure, create a temp column, format the values, place them into
> the temp column, delete the old column, and then rename the temp
> column to the name of the column that I grabbed in the beginning of
> the procedure. Most likely this is the only way to do it but I have no
> idea how to go about it.
I strongly suggest that you rethink your approach, and change the
column to datetime. You can then do date calculations using the
standard functions (DATEADD etc.), compare the values to datetime
variables without conversion, etc. You can use CONVERT() to extract
dates in a particular format for passing to other systems.
Using numeric will give you serious problems in the long run, although
I appreciate that you may have limited control over the data model.
But if you really have no option but to use numeric, then this should
work (assuming that as you said, all dates are 1950 or later):
update dbo.MyTable
set DateColumn = convert(char(8), convert(datetime, DateColumn, 3),
112)
alter table dbo.MyTable
alter column DateColumn numeric(8)
Simon
Tuesday, March 20, 2012
Alter table new column and update
Hi
for MS SQL 2000/2005
I am having a table (an old database, not mine) with char value for the column [localisation]
Users
[name] [nvarchar] (100) NOT NULL ,
[localisation] [nvarchar] (100)NULL
Now i have created a table [Localisation]
Localisation
[id_Localisation] [int] NOT NULL,
[localisation] [nvarchar] (100) NOT NULL
I am adding a new column to Users
ALTER TABLE [Users] ADD
[id_Localisation] int NULL
and I want to update the Column [Users].[id_Localisation] before to drop the column [Users].[Localisation]
something like
UPDATE [Users] SET id_Localisation = (SELECT Localisation.id_Localisation
FROM Localisation FULL OUTER JOIN
Users ON Localisation.Localisation = Users.Localisation)
Users.Localisation can have a NULL value (then no id_localisation return)
but it doesnt work because it returns > 1 row
thank you
how can I do it ?update [Users]
set id_Localisation = t2.id_Localisation
from [Users] t1
inner
join Localisation t2
on t1.Localisation = t2.Localisation|||it works perfectly
thanks a lot
do you thing i have to add a contrainst to this new column ?|||it would be a good idea to declare Users.id_Localisation as a foreign key|||but 5 tables are using this id_Localisation, can i add a FK to each one ?
FK_FK_Users_Localisation
FK_job_Localisation
FK_groups_Localisation
.....
if so
5 times (for each tables)
ALTER TABLE [Users] ADD
id_Localisation int NULL
ALTER TABLE [Users] WITH NOCHECK ADD
CONSTRAINT [FK_Users_Localisation] FOREIGN KEY
(
[id_Localisation]
) REFERENCES [Localisation] (
[id_Localisation]
)
I dont want to apply ON DELETE CASCADE , but to give a Id_localisation = 0 or NULL if a Localisation is deleted, how can i do it
??
thanks again for helping|||I dont want to apply ON DELETE CASCADE, but to give a Id_localisation = 0 or NULL if a Localisation is deleted
You can use ON DELETE SET NULL for that purpose|||but 5 tables are using this id_Localisation, can i add a FK to each one ?yes . ;)|||You can use ON DELETE SET NULL for that purposeunfortunately, not in SQL Server 2000, only in SQL Server 2005|||unfortunately, not in SQL Server 2000, only in SQL Server 2005Ah, right. I checked the wrong manual ;)|||well, i wouldn't exactly call it wrong -- i'm sure it's the right one for SQL Server 2005!!|||thank you
this application must work on 2000 and 2005
for MS SQL 2000/2005
I am having a table (an old database, not mine) with char value for the column [localisation]
Users
[name] [nvarchar] (100) NOT NULL ,
[localisation] [nvarchar] (100)NULL
Now i have created a table [Localisation]
Localisation
[id_Localisation] [int] NOT NULL,
[localisation] [nvarchar] (100) NOT NULL
I am adding a new column to Users
ALTER TABLE [Users] ADD
[id_Localisation] int NULL
and I want to update the Column [Users].[id_Localisation] before to drop the column [Users].[Localisation]
something like
UPDATE [Users] SET id_Localisation = (SELECT Localisation.id_Localisation
FROM Localisation FULL OUTER JOIN
Users ON Localisation.Localisation = Users.Localisation)
Users.Localisation can have a NULL value (then no id_localisation return)
but it doesnt work because it returns > 1 row
thank you
how can I do it ?update [Users]
set id_Localisation = t2.id_Localisation
from [Users] t1
inner
join Localisation t2
on t1.Localisation = t2.Localisation|||it works perfectly
thanks a lot
do you thing i have to add a contrainst to this new column ?|||it would be a good idea to declare Users.id_Localisation as a foreign key|||but 5 tables are using this id_Localisation, can i add a FK to each one ?
FK_FK_Users_Localisation
FK_job_Localisation
FK_groups_Localisation
.....
if so
5 times (for each tables)
ALTER TABLE [Users] ADD
id_Localisation int NULL
ALTER TABLE [Users] WITH NOCHECK ADD
CONSTRAINT [FK_Users_Localisation] FOREIGN KEY
(
[id_Localisation]
) REFERENCES [Localisation] (
[id_Localisation]
)
I dont want to apply ON DELETE CASCADE , but to give a Id_localisation = 0 or NULL if a Localisation is deleted, how can i do it
??
thanks again for helping|||I dont want to apply ON DELETE CASCADE, but to give a Id_localisation = 0 or NULL if a Localisation is deleted
You can use ON DELETE SET NULL for that purpose|||but 5 tables are using this id_Localisation, can i add a FK to each one ?yes . ;)|||You can use ON DELETE SET NULL for that purposeunfortunately, not in SQL Server 2000, only in SQL Server 2005|||unfortunately, not in SQL Server 2000, only in SQL Server 2005Ah, right. I checked the wrong manual ;)|||well, i wouldn't exactly call it wrong -- i'm sure it's the right one for SQL Server 2005!!|||thank you
this application must work on 2000 and 2005
Alter table errors due to statistics
I'm attempting to change a column data type from int to nvarchar(16) on a
production database. When executing:
alter table x alter column y nvarchar(16)
I get the error:
ALTER TABLE ALTER COLUMN y failed because STATISTICS hind_61_3 accesses this
column
I would be forever grateful if someone could tell me how to get around this
issue.
Thanks in advance,
GaryRun:
drop statistics hind_61_3
and then do your ALTER TABLE.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
news:8c236$41c72f45$44a72b52$17509@.msgid.meganewsservers.com...
I'm attempting to change a column data type from int to nvarchar(16) on a
production database. When executing:
alter table x alter column y nvarchar(16)
I get the error:
ALTER TABLE ALTER COLUMN y failed because STATISTICS hind_61_3 accesses this
column
I would be forever grateful if someone could tell me how to get around this
issue.
Thanks in advance,
Gary|||Thank you. If I could trouble you once more, how would this get in there?
We've updated hundreds of customers and have found this error on but one
site...
Again, thank you!
Gary
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OnHXHCt5EHA.344@.TK2MSFTNGP10.phx.gbl...
> Run:
> drop statistics hind_61_3
> and then do your ALTER TABLE.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
> news:8c236$41c72f45$44a72b52$17509@.msgid.meganewsservers.com...
> I'm attempting to change a column data type from int to nvarchar(16) on a
> production database. When executing:
> alter table x alter column y nvarchar(16)
> I get the error:
> ALTER TABLE ALTER COLUMN y failed because STATISTICS hind_61_3 accesses
> this
> column
>
> I would be forever grateful if someone could tell me how to get around
> this
> issue.
> Thanks in advance,
> Gary
>
>|||You probably have auto-create stats and auto-update stats turned on. This
is normal. If SQL Server figures it needs stats on that column, then it
creates them. However, if you decide to alter the column, the stats are a
dependency on that column in the same wan an index or constraint is. You
have to drop those dependencies first before altering the column.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
news:15de1$41c73bd8$44a72b52$18777@.msgid.meganewsservers.com...
Thank you. If I could trouble you once more, how would this get in there?
We've updated hundreds of customers and have found this error on but one
site...
Again, thank you!
Gary
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OnHXHCt5EHA.344@.TK2MSFTNGP10.phx.gbl...
> Run:
> drop statistics hind_61_3
> and then do your ALTER TABLE.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
> news:8c236$41c72f45$44a72b52$17509@.msgid.meganewsservers.com...
> I'm attempting to change a column data type from int to nvarchar(16) on a
> production database. When executing:
> alter table x alter column y nvarchar(16)
> I get the error:
> ALTER TABLE ALTER COLUMN y failed because STATISTICS hind_61_3 accesses
> this
> column
>
> I would be forever grateful if someone could tell me how to get around
> this
> issue.
> Thanks in advance,
> Gary
>
>|||The hind_ statistics are really not statistics, but Hypothetical INDexes,
created by the Index Tuning Wizard, which normally are cleaned up up when
ITW finishes. There are some situations where it doesn't clean up after
itself, so you have to do it with DROP STATISTICS. Since it is a very rare
occurrence to have these left behind, it's not surprising that you don't see
this error very often.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
news:15de1$41c73bd8$44a72b52$18777@.msgid.meganewsservers.com...
> Thank you. If I could trouble you once more, how would this get in there?
> We've updated hundreds of customers and have found this error on but one
> site...
> Again, thank you!
> Gary
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OnHXHCt5EHA.344@.TK2MSFTNGP10.phx.gbl...
>> Run:
>> drop statistics hind_61_3
>> and then do your ALTER TABLE.
>> --
>> Tom
>> ---
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinnaclepublishing.com
>>
>> "Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
>> news:8c236$41c72f45$44a72b52$17509@.msgid.meganewsservers.com...
>> I'm attempting to change a column data type from int to nvarchar(16) on a
>> production database. When executing:
>> alter table x alter column y nvarchar(16)
>> I get the error:
>> ALTER TABLE ALTER COLUMN y failed because STATISTICS hind_61_3 accesses
>> this
>> column
>>
>> I would be forever grateful if someone could tell me how to get around
>> this
>> issue.
>> Thanks in advance,
>> Gary
>>
>
production database. When executing:
alter table x alter column y nvarchar(16)
I get the error:
ALTER TABLE ALTER COLUMN y failed because STATISTICS hind_61_3 accesses this
column
I would be forever grateful if someone could tell me how to get around this
issue.
Thanks in advance,
GaryRun:
drop statistics hind_61_3
and then do your ALTER TABLE.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
news:8c236$41c72f45$44a72b52$17509@.msgid.meganewsservers.com...
I'm attempting to change a column data type from int to nvarchar(16) on a
production database. When executing:
alter table x alter column y nvarchar(16)
I get the error:
ALTER TABLE ALTER COLUMN y failed because STATISTICS hind_61_3 accesses this
column
I would be forever grateful if someone could tell me how to get around this
issue.
Thanks in advance,
Gary|||Thank you. If I could trouble you once more, how would this get in there?
We've updated hundreds of customers and have found this error on but one
site...
Again, thank you!
Gary
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OnHXHCt5EHA.344@.TK2MSFTNGP10.phx.gbl...
> Run:
> drop statistics hind_61_3
> and then do your ALTER TABLE.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
> news:8c236$41c72f45$44a72b52$17509@.msgid.meganewsservers.com...
> I'm attempting to change a column data type from int to nvarchar(16) on a
> production database. When executing:
> alter table x alter column y nvarchar(16)
> I get the error:
> ALTER TABLE ALTER COLUMN y failed because STATISTICS hind_61_3 accesses
> this
> column
>
> I would be forever grateful if someone could tell me how to get around
> this
> issue.
> Thanks in advance,
> Gary
>
>|||You probably have auto-create stats and auto-update stats turned on. This
is normal. If SQL Server figures it needs stats on that column, then it
creates them. However, if you decide to alter the column, the stats are a
dependency on that column in the same wan an index or constraint is. You
have to drop those dependencies first before altering the column.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
news:15de1$41c73bd8$44a72b52$18777@.msgid.meganewsservers.com...
Thank you. If I could trouble you once more, how would this get in there?
We've updated hundreds of customers and have found this error on but one
site...
Again, thank you!
Gary
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OnHXHCt5EHA.344@.TK2MSFTNGP10.phx.gbl...
> Run:
> drop statistics hind_61_3
> and then do your ALTER TABLE.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
> news:8c236$41c72f45$44a72b52$17509@.msgid.meganewsservers.com...
> I'm attempting to change a column data type from int to nvarchar(16) on a
> production database. When executing:
> alter table x alter column y nvarchar(16)
> I get the error:
> ALTER TABLE ALTER COLUMN y failed because STATISTICS hind_61_3 accesses
> this
> column
>
> I would be forever grateful if someone could tell me how to get around
> this
> issue.
> Thanks in advance,
> Gary
>
>|||The hind_ statistics are really not statistics, but Hypothetical INDexes,
created by the Index Tuning Wizard, which normally are cleaned up up when
ITW finishes. There are some situations where it doesn't clean up after
itself, so you have to do it with DROP STATISTICS. Since it is a very rare
occurrence to have these left behind, it's not surprising that you don't see
this error very often.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
news:15de1$41c73bd8$44a72b52$18777@.msgid.meganewsservers.com...
> Thank you. If I could trouble you once more, how would this get in there?
> We've updated hundreds of customers and have found this error on but one
> site...
> Again, thank you!
> Gary
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OnHXHCt5EHA.344@.TK2MSFTNGP10.phx.gbl...
>> Run:
>> drop statistics hind_61_3
>> and then do your ALTER TABLE.
>> --
>> Tom
>> ---
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinnaclepublishing.com
>>
>> "Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
>> news:8c236$41c72f45$44a72b52$17509@.msgid.meganewsservers.com...
>> I'm attempting to change a column data type from int to nvarchar(16) on a
>> production database. When executing:
>> alter table x alter column y nvarchar(16)
>> I get the error:
>> ALTER TABLE ALTER COLUMN y failed because STATISTICS hind_61_3 accesses
>> this
>> column
>>
>> I would be forever grateful if someone could tell me how to get around
>> this
>> issue.
>> Thanks in advance,
>> Gary
>>
>
Alter table errors due to statistics
I'm attempting to change a column data type from int to nvarchar(16) on a
production database. When executing:
alter table x alter column y nvarchar(16)
I get the error:
ALTER TABLE ALTER COLUMN y failed because STATISTICS hind_61_3 accesses this
column
I would be forever grateful if someone could tell me how to get around this
issue.
Thanks in advance,
Gary
Run:
drop statistics hind_61_3
and then do your ALTER TABLE.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
news:8c236$41c72f45$44a72b52$17509@.msgid.meganewss ervers.com...
I'm attempting to change a column data type from int to nvarchar(16) on a
production database. When executing:
alter table x alter column y nvarchar(16)
I get the error:
ALTER TABLE ALTER COLUMN y failed because STATISTICS hind_61_3 accesses this
column
I would be forever grateful if someone could tell me how to get around this
issue.
Thanks in advance,
Gary
|||Thank you. If I could trouble you once more, how would this get in there?
We've updated hundreds of customers and have found this error on but one
site...
Again, thank you!
Gary
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OnHXHCt5EHA.344@.TK2MSFTNGP10.phx.gbl...
> Run:
> drop statistics hind_61_3
> and then do your ALTER TABLE.
> --
> Tom
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
> news:8c236$41c72f45$44a72b52$17509@.msgid.meganewss ervers.com...
> I'm attempting to change a column data type from int to nvarchar(16) on a
> production database. When executing:
> alter table x alter column y nvarchar(16)
> I get the error:
> ALTER TABLE ALTER COLUMN y failed because STATISTICS hind_61_3 accesses
> this
> column
>
> I would be forever grateful if someone could tell me how to get around
> this
> issue.
> Thanks in advance,
> Gary
>
>
|||You probably have auto-create stats and auto-update stats turned on. This
is normal. If SQL Server figures it needs stats on that column, then it
creates them. However, if you decide to alter the column, the stats are a
dependency on that column in the same wan an index or constraint is. You
have to drop those dependencies first before altering the column.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
news:15de1$41c73bd8$44a72b52$18777@.msgid.meganewss ervers.com...
Thank you. If I could trouble you once more, how would this get in there?
We've updated hundreds of customers and have found this error on but one
site...
Again, thank you!
Gary
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OnHXHCt5EHA.344@.TK2MSFTNGP10.phx.gbl...
> Run:
> drop statistics hind_61_3
> and then do your ALTER TABLE.
> --
> Tom
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
> news:8c236$41c72f45$44a72b52$17509@.msgid.meganewss ervers.com...
> I'm attempting to change a column data type from int to nvarchar(16) on a
> production database. When executing:
> alter table x alter column y nvarchar(16)
> I get the error:
> ALTER TABLE ALTER COLUMN y failed because STATISTICS hind_61_3 accesses
> this
> column
>
> I would be forever grateful if someone could tell me how to get around
> this
> issue.
> Thanks in advance,
> Gary
>
>
|||The hind_ statistics are really not statistics, but Hypothetical INDexes,
created by the Index Tuning Wizard, which normally are cleaned up up when
ITW finishes. There are some situations where it doesn't clean up after
itself, so you have to do it with DROP STATISTICS. Since it is a very rare
occurrence to have these left behind, it's not surprising that you don't see
this error very often.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
news:15de1$41c73bd8$44a72b52$18777@.msgid.meganewss ervers.com...
> Thank you. If I could trouble you once more, how would this get in there?
> We've updated hundreds of customers and have found this error on but one
> site...
> Again, thank you!
> Gary
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OnHXHCt5EHA.344@.TK2MSFTNGP10.phx.gbl...
>
sql
production database. When executing:
alter table x alter column y nvarchar(16)
I get the error:
ALTER TABLE ALTER COLUMN y failed because STATISTICS hind_61_3 accesses this
column
I would be forever grateful if someone could tell me how to get around this
issue.
Thanks in advance,
Gary
Run:
drop statistics hind_61_3
and then do your ALTER TABLE.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
news:8c236$41c72f45$44a72b52$17509@.msgid.meganewss ervers.com...
I'm attempting to change a column data type from int to nvarchar(16) on a
production database. When executing:
alter table x alter column y nvarchar(16)
I get the error:
ALTER TABLE ALTER COLUMN y failed because STATISTICS hind_61_3 accesses this
column
I would be forever grateful if someone could tell me how to get around this
issue.
Thanks in advance,
Gary
|||Thank you. If I could trouble you once more, how would this get in there?
We've updated hundreds of customers and have found this error on but one
site...
Again, thank you!
Gary
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OnHXHCt5EHA.344@.TK2MSFTNGP10.phx.gbl...
> Run:
> drop statistics hind_61_3
> and then do your ALTER TABLE.
> --
> Tom
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
> news:8c236$41c72f45$44a72b52$17509@.msgid.meganewss ervers.com...
> I'm attempting to change a column data type from int to nvarchar(16) on a
> production database. When executing:
> alter table x alter column y nvarchar(16)
> I get the error:
> ALTER TABLE ALTER COLUMN y failed because STATISTICS hind_61_3 accesses
> this
> column
>
> I would be forever grateful if someone could tell me how to get around
> this
> issue.
> Thanks in advance,
> Gary
>
>
|||You probably have auto-create stats and auto-update stats turned on. This
is normal. If SQL Server figures it needs stats on that column, then it
creates them. However, if you decide to alter the column, the stats are a
dependency on that column in the same wan an index or constraint is. You
have to drop those dependencies first before altering the column.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
news:15de1$41c73bd8$44a72b52$18777@.msgid.meganewss ervers.com...
Thank you. If I could trouble you once more, how would this get in there?
We've updated hundreds of customers and have found this error on but one
site...
Again, thank you!
Gary
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OnHXHCt5EHA.344@.TK2MSFTNGP10.phx.gbl...
> Run:
> drop statistics hind_61_3
> and then do your ALTER TABLE.
> --
> Tom
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
> news:8c236$41c72f45$44a72b52$17509@.msgid.meganewss ervers.com...
> I'm attempting to change a column data type from int to nvarchar(16) on a
> production database. When executing:
> alter table x alter column y nvarchar(16)
> I get the error:
> ALTER TABLE ALTER COLUMN y failed because STATISTICS hind_61_3 accesses
> this
> column
>
> I would be forever grateful if someone could tell me how to get around
> this
> issue.
> Thanks in advance,
> Gary
>
>
|||The hind_ statistics are really not statistics, but Hypothetical INDexes,
created by the Index Tuning Wizard, which normally are cleaned up up when
ITW finishes. There are some situations where it doesn't clean up after
itself, so you have to do it with DROP STATISTICS. Since it is a very rare
occurrence to have these left behind, it's not surprising that you don't see
this error very often.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
news:15de1$41c73bd8$44a72b52$18777@.msgid.meganewss ervers.com...
> Thank you. If I could trouble you once more, how would this get in there?
> We've updated hundreds of customers and have found this error on but one
> site...
> Again, thank you!
> Gary
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OnHXHCt5EHA.344@.TK2MSFTNGP10.phx.gbl...
>
sql
Alter table db size getting increased.
Hi,
I am doing alter table and updating Nvarchar datatype to Ntext. AS there are
almost
54,09,873 records installation is taking more than 3 hours and log/database
files are getting increased beyond limit. Now there is hardly space left and
still installation is not completed.
Tasks Restore Size After installation
Datafile Size 39000 46529 MB
Log Size 4550 14032 MB
ALTER TABLE [dbo].[xyz] ALTER COLUMN EmailText ntext
54,09,873 Rows affected.
As far as I know and understand Alter basically copy the contents to some
temp table, deletes the table, recreate the table with new definition and
again copy back to original table( Sorry if sequence is wrong ?) and I thi
nk
this is the reason why this script is taking more space and impacting log
size.
IS THERE ANY SOLN OR ALTERNATIVE WHICH CAN MAKE THIS FASTER AND REDUCE THE
SIZE OF DB INCREASE.
--
SanjayYes, you might try dumping the data into an external text file, and then bc
p
or bulk insert it back into the new table structure.
"Sanjay" wrote:
> Hi,
> I am doing alter table and updating Nvarchar datatype to Ntext. AS there a
re
> almost
> 54,09,873 records installation is taking more than 3 hours and log/databas
e
> files are getting increased beyond limit. Now there is hardly space left a
nd
> still installation is not completed.
> Tasks Restore Size After installation
> Datafile Size 39000 46529 MB
> Log Size 4550 14032 MB
> ALTER TABLE [dbo].[xyz] ALTER COLUMN EmailText ntext
> 54,09,873 Rows affected.
> As far as I know and understand Alter basically copy the contents to some
> temp table, deletes the table, recreate the table with new definition and
> again copy back to original table( Sorry if sequence is wrong ?) and I t
hink
> this is the reason why this script is taking more space and impacting log
> size.
> IS THERE ANY SOLN OR ALTERNATIVE WHICH CAN MAKE THIS FASTER AND REDUCE THE
> SIZE OF DB INCREASE.
> --
> Sanjay|||Thanks for your help,
can you help me in syntax and will this be logged in log file.
i never tried that but will this help in reducing time and DB size issues.
"CBretana" wrote:
> Yes, you might try dumping the data into an external text file, and then
bcp
> or bulk insert it back into the new table structure.
> "Sanjay" wrote:
>|||Look up BCP in BOL -- it's a utility that comes w/SQL server.
Setting the logging level to SIMPLE would help too -- look up ALTER
DATABASE; the setting is SET RECOVERY
"Sanjay" wrote:
> Thanks for your help,
> can you help me in syntax and will this be logged in log file.
> i never tried that but will this help in reducing time and DB size issues.
> "CBretana" wrote:
>|||I tried but log file was increased around 5 gb and data file also.
i am doing something like this.
alter database xyz
set recovery simple
alter table and modifying nvarchar to ntext ( around 54,09,873 rows)
set recovery full.
"KH" wrote:
> Look up BCP in BOL -- it's a utility that comes w/SQL server.
> Setting the logging level to SIMPLE would help too -- look up ALTER
> DATABASE; the setting is SET RECOVERY
>
> "Sanjay" wrote:
>
I am doing alter table and updating Nvarchar datatype to Ntext. AS there are
almost
54,09,873 records installation is taking more than 3 hours and log/database
files are getting increased beyond limit. Now there is hardly space left and
still installation is not completed.
Tasks Restore Size After installation
Datafile Size 39000 46529 MB
Log Size 4550 14032 MB
ALTER TABLE [dbo].[xyz] ALTER COLUMN EmailText ntext
54,09,873 Rows affected.
As far as I know and understand Alter basically copy the contents to some
temp table, deletes the table, recreate the table with new definition and
again copy back to original table( Sorry if sequence is wrong ?) and I thi
nk
this is the reason why this script is taking more space and impacting log
size.
IS THERE ANY SOLN OR ALTERNATIVE WHICH CAN MAKE THIS FASTER AND REDUCE THE
SIZE OF DB INCREASE.
--
SanjayYes, you might try dumping the data into an external text file, and then bc
p
or bulk insert it back into the new table structure.
"Sanjay" wrote:
> Hi,
> I am doing alter table and updating Nvarchar datatype to Ntext. AS there a
re
> almost
> 54,09,873 records installation is taking more than 3 hours and log/databas
e
> files are getting increased beyond limit. Now there is hardly space left a
nd
> still installation is not completed.
> Tasks Restore Size After installation
> Datafile Size 39000 46529 MB
> Log Size 4550 14032 MB
> ALTER TABLE [dbo].[xyz] ALTER COLUMN EmailText ntext
> 54,09,873 Rows affected.
> As far as I know and understand Alter basically copy the contents to some
> temp table, deletes the table, recreate the table with new definition and
> again copy back to original table( Sorry if sequence is wrong ?) and I t
hink
> this is the reason why this script is taking more space and impacting log
> size.
> IS THERE ANY SOLN OR ALTERNATIVE WHICH CAN MAKE THIS FASTER AND REDUCE THE
> SIZE OF DB INCREASE.
> --
> Sanjay|||Thanks for your help,
can you help me in syntax and will this be logged in log file.
i never tried that but will this help in reducing time and DB size issues.
"CBretana" wrote:
> Yes, you might try dumping the data into an external text file, and then
bcp
> or bulk insert it back into the new table structure.
> "Sanjay" wrote:
>|||Look up BCP in BOL -- it's a utility that comes w/SQL server.
Setting the logging level to SIMPLE would help too -- look up ALTER
DATABASE; the setting is SET RECOVERY
"Sanjay" wrote:
> Thanks for your help,
> can you help me in syntax and will this be logged in log file.
> i never tried that but will this help in reducing time and DB size issues.
> "CBretana" wrote:
>|||I tried but log file was increased around 5 gb and data file also.
i am doing something like this.
alter database xyz
set recovery simple
alter table and modifying nvarchar to ntext ( around 54,09,873 rows)
set recovery full.
"KH" wrote:
> Look up BCP in BOL -- it's a utility that comes w/SQL server.
> Setting the logging level to SIMPLE would help too -- look up ALTER
> DATABASE; the setting is SET RECOVERY
>
> "Sanjay" wrote:
>
Saturday, February 25, 2012
Alter column
what is the syntax for multiple column modifications ?
alter table abc
alter column x1 nvarchar(10) null
-- works
alter table abc
alter column x1 nvarchar(10) null,
alter column x2 nvarchar(10) null
-- doesn't work or do i have to use the alter table each time ?Want the lazy/easy way to script this?
Make your changes in Enterprise Manager, and then before you save click the icon to script out the changes. Copy the script, cancel your changes, and paster your script in Query Analyzer for editing.
alter table abc
alter column x1 nvarchar(10) null
-- works
alter table abc
alter column x1 nvarchar(10) null,
alter column x2 nvarchar(10) null
-- doesn't work or do i have to use the alter table each time ?Want the lazy/easy way to script this?
Make your changes in Enterprise Manager, and then before you save click the icon to script out the changes. Copy the script, cancel your changes, and paster your script in Query Analyzer for editing.
Alter a table by using a variable as column name
Hi All,
i would like to alter a table by using a @.Variable as Column name like this:
Code Snippet
DECLARE @.VARIABLE AS nvarchar(50)
SET @.VARIABLE = 'Column1'
ALTER TABLE #TempTable ADD
@.VARIABLE AS nvarchar(50)
But it looks like, that i cant alter a table by using a variable as column name?
Can someone help me out?
Greetings,
Andreas
hi,you can use dynamic query e.g.
Code Snippet
DECLARE @.VARIABLE AS nvarchar(50)
SET @.VARIABLE = 'Column1'
declare @.str varchar(4000)
set @.str = 'ALTER TABLE #TempTable ADD ' + @.variable + ' nvarchar(50)'
exec(@.str)
- clintz|||
Well ... that was a fast help!
Big thanks to you, clintz.
I think, i should put some time into dynamic query
Subscribe to:
Posts (Atom)