Altering XML Schema Collection Problem

Hi,
I'm trying to add an extra element into a schema collection and keep getting
an error message:
"Msg 9455, Level 16, State 1, Line 1
XML parsing: line 3, character 1, illegal qualified name character
"
The SQL I'm using is:
alter xml schema collection dbo.EmployeeSchemaCollection ADD N'
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
<xsd:element name="Employee">
<xsd:complexType>
<xsd:element name="HireDate" type="xsd:datetime" />
</xsd:complexType>
</xsd:element>
</xsd:schema>'
Can anyone help?
ThanksI don't know this stuff much however should not be a ">" in the end of the
second line? (<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema")
--
Ekrem Ã?nsoy
"John S" <js162@.newsgroup.nospam> wrote in message
news:F7FE9209-3176-4742-97AD-9689A20085A1@.microsoft.com...
> Hi,
> I'm trying to add an extra element into a schema collection and keep
> getting
> an error message:
> "Msg 9455, Level 16, State 1, Line 1
> XML parsing: line 3, character 1, illegal qualified name character
> "
> The SQL I'm using is:
> alter xml schema collection dbo.EmployeeSchemaCollection ADD N'
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> <xsd:element name="Employee">
> <xsd:complexType>
> <xsd:element name="HireDate" type="xsd:datetime" />
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>'
> Can anyone help?
> Thanks|||It's so easy to miss the obvious!
Thanks
JS
"Ekrem Ã?nsoy" wrote:
> I don't know this stuff much however should not be a ">" in the end of the
> second line? (<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema")
> --
> Ekrem Ã?nsoy
>
> "John S" <js162@.newsgroup.nospam> wrote in message
> news:F7FE9209-3176-4742-97AD-9689A20085A1@.microsoft.com...
> > Hi,
> >
> > I'm trying to add an extra element into a schema collection and keep
> > getting
> > an error message:
> > "Msg 9455, Level 16, State 1, Line 1
> > XML parsing: line 3, character 1, illegal qualified name character
> > "
> >
> > The SQL I'm using is:
> >
> > alter xml schema collection dbo.EmployeeSchemaCollection ADD N'
> > <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> > <xsd:element name="Employee">
> > <xsd:complexType>
> > <xsd:element name="HireDate" type="xsd:datetime" />
> > </xsd:complexType>
> > </xsd:element>
> > </xsd:schema>'
> >
> > Can anyone help?
> >
> > Thanks
>sql

altering unique index to primary key

Is there a way to alter a unique clustered index in a table to a primary key
with some magic alter statement?
What I want to avoid (if possible) is to run drop/create statement, just to
make already unique clustered index to a Primary key.
I appreciate your reply. I have sql server 2000 SP4.Hi James
I don't think this possible with command. Why do you want to change this?
John
"James" wrote:

> Is there a way to alter a unique clustered index in a table to a primary k
ey
> with some magic alter statement?
> What I want to avoid (if possible) is to run drop/create statement, just t
o
> make already unique clustered index to a Primary key.
> I appreciate your reply. I have sql server 2000 SP4.
>
>|||I wanted to replicate these tables via Transactional replication and it
requires a Primary key. Since the tables are big, I wanted to save some time
if that was possible.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:F688407C-5A69-4B1E-B0E7-76100DE23F5E@.microsoft.com...[vbcol=seagreen]
> Hi James
> I don't think this possible with command. Why do you want to change this?
> John
> "James" wrote:
>|||Hi James,
> I wanted to replicate these tables via Transactional replication and it
> requires a Primary key.
>
Are you saying you created the tables without a primary key? Is that
something you regularly do?
Ruud de Koter.

altering unique index to primary key

Is there a way to alter a unique clustered index in a table to a primary key
with some magic alter statement?
What I want to avoid (if possible) is to run drop/create statement, just to
make already unique clustered index to a Primary key.
I appreciate your reply. I have sql server 2000 SP4.Hi James
I don't think this possible with command. Why do you want to change this?
John
"James" wrote:
> Is there a way to alter a unique clustered index in a table to a primary key
> with some magic alter statement?
> What I want to avoid (if possible) is to run drop/create statement, just to
> make already unique clustered index to a Primary key.
> I appreciate your reply. I have sql server 2000 SP4.
>
>|||I wanted to replicate these tables via Transactional replication and it
requires a Primary key. Since the tables are big, I wanted to save some time
if that was possible.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:F688407C-5A69-4B1E-B0E7-76100DE23F5E@.microsoft.com...
> Hi James
> I don't think this possible with command. Why do you want to change this?
> John
> "James" wrote:
>> Is there a way to alter a unique clustered index in a table to a primary
>> key
>> with some magic alter statement?
>> What I want to avoid (if possible) is to run drop/create statement, just
>> to
>> make already unique clustered index to a Primary key.
>> I appreciate your reply. I have sql server 2000 SP4.
>>|||Hi James,
> I wanted to replicate these tables via Transactional replication and it
> requires a Primary key.
>
Are you saying you created the tables without a primary key? Is that
something you regularly do?
Ruud de Koter.

Altering the identity seed of a table

Hi,

Im trying to alter the identity seed of a table in a script and I cant work out how to do so without doing it the way Enterprise Manager does it - ie create a tmp table with the new id, populate it with data and set constraints etc, then drop the original table and rename the tmp one.

This is pretty hard to script for arbitrary tables automatically, so I was wondering if there is some way to do it with an ALTER TABLE script?

cheers
Pete StoreyDBCC Checkident.|||Thanks!

altering temporary tables

hi all,
i have a simple stored procedure that takes input an integer..then inside it
it creates a temporary table & adds columns which are equal in number to the
integer that was inputed.
but iam encountering variuos error when i execute this procedure
here is the code(a very simple & small stored procedure):
create procedure mySolution @.colCount int as
declare @.i int
declare @.currTable varchar(10)
declare @.query nvarchar(255)
set @.i = 0
while @.i <= @.colCount
begin
set @.i = @.i + 1
if (@.i % 15 = 1)
begin
set @.query = 'create table #tasksecf'+cast((@.i/15) as nvarchar(255))+'
(empid int)'
set @.currTable = '#taskecf'+cast((@.i/15) as NVARCHAR(255))
exec(@.query)
end
set @.query = 'alter table '+@.currTable+' add col'+ cast(@.i as varchar)+ '
nvarchar(255)'
exec(@.query)
end
here are the errors :
if i execute this line
exec mySolution '15'
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf1' because this table does not exist in database
'MyPracticeDB'.Perhaps you should tell us what are you actually trying to achieve.
Each EXEC statement runs in a different context in which the local temporary
table created in the main procedure does not exist. Using a global table
would work, but I would advise against that - it may lead to other issues.
ML
http://milambda.blogspot.com/|||Hmmm, I think that temp table doesnt exists outside of the execution od
dynamic string and that would cause errors you get.
What are you trying to do with this sp? Perhaps you could explain the
requirement so we can try to find alternative solutions?
MC
"kishore bondada" <kishore bondada@.discussions.microsoft.com> wrote in
message news:361D5746-7137-4361-8BD0-D77150AF7326@.microsoft.com...
> hi all,
> i have a simple stored procedure that takes input an integer..then inside
> it
> it creates a temporary table & adds columns which are equal in number to
> the
> integer that was inputed.
> but iam encountering variuos error when i execute this procedure
> here is the code(a very simple & small stored procedure):
> create procedure mySolution @.colCount int as
> declare @.i int
> declare @.currTable varchar(10)
> declare @.query nvarchar(255)
> set @.i = 0
> while @.i <= @.colCount
> begin
> set @.i = @.i + 1
> if (@.i % 15 = 1)
> begin
> set @.query = 'create table #tasksecf'+cast((@.i/15) as nvarchar(255))+'
> (empid int)'
> set @.currTable = '#taskecf'+cast((@.i/15) as NVARCHAR(255))
> exec(@.query)
> end
> set @.query = 'alter table '+@.currTable+' add col'+ cast(@.i as varchar)+ '
> nvarchar(255)'
> exec(@.query)
> end
>
> here are the errors :
> if i execute this line
> exec mySolution '15'
> Server: Msg 4902, Level 16, State 1, Line 1
> Cannot alter table '#taskecf0' because this table does not exist in
> database
> 'MyPracticeDB'.
> Server: Msg 4902, Level 16, State 1, Line 1
> Cannot alter table '#taskecf0' because this table does not exist in
> database
> 'MyPracticeDB'.
> Server: Msg 4902, Level 16, State 1, Line 1
> Cannot alter table '#taskecf0' because this table does not exist in
> database
> 'MyPracticeDB'.
> Server: Msg 4902, Level 16, State 1, Line 1
> Cannot alter table '#taskecf0' because this table does not exist in
> database
> 'MyPracticeDB'.
> Server: Msg 4902, Level 16, State 1, Line 1
> Cannot alter table '#taskecf0' because this table does not exist in
> database
> 'MyPracticeDB'.
> Server: Msg 4902, Level 16, State 1, Line 1
> Cannot alter table '#taskecf0' because this table does not exist in
> database
> 'MyPracticeDB'.
> Server: Msg 4902, Level 16, State 1, Line 1
> Cannot alter table '#taskecf0' because this table does not exist in
> database
> 'MyPracticeDB'.
> Server: Msg 4902, Level 16, State 1, Line 1
> Cannot alter table '#taskecf0' because this table does not exist in
> database
> 'MyPracticeDB'.
> Server: Msg 4902, Level 16, State 1, Line 1
> Cannot alter table '#taskecf0' because this table does not exist in
> database
> 'MyPracticeDB'.
> Server: Msg 4902, Level 16, State 1, Line 1
> Cannot alter table '#taskecf0' because this table does not exist in
> database
> 'MyPracticeDB'.
> Server: Msg 4902, Level 16, State 1, Line 1
> Cannot alter table '#taskecf0' because this table does not exist in
> database
> 'MyPracticeDB'.
> Server: Msg 4902, Level 16, State 1, Line 1
> Cannot alter table '#taskecf0' because this table does not exist in
> database
> 'MyPracticeDB'.
> Server: Msg 4902, Level 16, State 1, Line 1
> Cannot alter table '#taskecf0' because this table does not exist in
> database
> 'MyPracticeDB'.
> Server: Msg 4902, Level 16, State 1, Line 1
> Cannot alter table '#taskecf0' because this table does not exist in
> database
> 'MyPracticeDB'.
> Server: Msg 4902, Level 16, State 1, Line 1
> Cannot alter table '#taskecf0' because this table does not exist in
> database
> 'MyPracticeDB'.
> Server: Msg 4902, Level 16, State 1, Line 1
> Cannot alter table '#taskecf1' because this table does not exist in
> database
> 'MyPracticeDB'.
>|||I've never tries t his, but perhaps you can switch your context to
tempdb and alter from there...once you find it.|||No, the problem is pre-compile, e.g. the parser can't find #table because
#table hasn't been created yet.
An alternative is to complete the whole chunk in dynamic SQL. Here is one
approach thatuses a permanent table tied to the SPID, this way you don't
have to do everything inside a single EXEC() (but you will still have to use
dynamic SQL to reference the table name):
DECLARE @.colCount INT;
SET @.colCount = 5;
DECLARE @.i INT,
@.TableName VARCHAR(32),
@.Query NVARCHAR(4000);
SET @.TableName = 'TaskSecF_'+RTRIM(@.@.SPID);
SET @.Query = 'IF OBJECT_ID('''+@.TableName+''') IS NOT NULL
DROP TABLE '+@.TableName+';
SELECT col1 = CONVERT(NVARCHAR(255), N'''')';
SET @.i = 1;
WHILE @.i <= @.colCount
BEGIN
SET @.i = @.i + 1
SET @.query = @.query + ',col'+RTRIM(@.i)+' = CONVERT(NVARCHAR(255), N'''')'
END
SET @.query = @.query + ' INTO '+@.TableName+' WHERE 1=2;'
EXEC(@.query)
EXEC('SELECT * FROM '+@.TableName);
...
EXEC('DROP TABLE '+@.TableName);
"MJKulangara" <RutgersDBA@.gmail.com> wrote in message
news:1139324385.781046.242250@.g43g2000cwa.googlegroups.com...
> I've never tries t his, but perhaps you can switch your context to
> tempdb and alter from there...once you find it.
>|||hi all,
first of all thank you all for your valuable replies. As asked by you iam
explaining the requirement a bit more clearly here:
Iam supposed to write a stored Procedure that takes input, an integer ' N '
and inside it create some temp tables, where each temp table contains ' X '
number of columns with datatype NVARCHAR(255).
since the SQL Server 2000 MAXROWSIZE limit is 8060 bytes, each temp table
can atmaximum contain 15 to 16 fields only (with datatype NVARCHAR(255)).
now if the user gives an input as integer 37, then
these temp tables should be created
#taskecf1 with fields col1 .... col15
#taskecf2 with fields col16 .... col30
#taskecf3 with fields col31 .... col37
and iam supposed to create temp tables only (not ordinary tables, i should
follow this coz, told by my project guide), after i complete this task, this
stored procedure is given to another student who will add his code to do
inserts & updates on these temp tables. hope this provides enough explanatio
n.
so i request you all to help me with this .
here is the code :
create procedure mySolution @.colCount int as
declare @.i int
declare @.currTable varchar(10)
declare @.query nvarchar(255)
set @.i = 0
while @.i <= @.colCount
begin
set @.i = @.i + 1
if (@.i % 15 = 1)
begin
set @.query = 'create table #tasksecf'+cast((@.i/15) as nvarchar(255))+'
(empid int)'
set @.currTable = '#taskecf'+cast((@.i/15) as NVARCHAR(255))
exec(@.query)
end
set @.query = 'alter table '+@.currTable+' add col'+ cast(@.i as varchar)+ '
nvarchar(255)'
exec(@.query)
end
if i execute this
exec mySolution '15'
here are the errors :
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf1' because this table does not exist in database
'MyPracticeDB'|||i got the work done with the help of global temporary tables.
but some of you commented that it would lead to some other issues...can u
please elaborate what they are?
"ML" wrote:

> Perhaps you should tell us what are you actually trying to achieve.
> Each EXEC statement runs in a different context in which the local tempora
ry
> table created in the main procedure does not exist. Using a global table
> would work, but I would advise against that - it may lead to other issues.
>
> ML
> --
> http://milambda.blogspot.com/|||Well, its global. That means that all processes 'see' and affect those
tables. If there are two calls of your procedure at the same time, you'll be
in trouble.
MC
"kishore bondada" <kishorebondada@.discussions.microsoft.com> wrote in
message news:89E8BD16-C428-4C77-9BBB-CC2D4CDEC055@.microsoft.com...
>i got the work done with the help of global temporary tables.
> but some of you commented that it would lead to some other issues...can u
> please elaborate what they are?
> "ML" wrote:
>|||Try executing your script in two separate sessions step-by-step - i.e. creat
e
the object in the first session, then in the second, make the changes in the
first, then in the second, etc.
After each step check the state of the object (perhaps from a third
connection). You'll see what I mean.
Even this situation can be remedied by giving the objects unique names (e.g.
including SPID in the table name), but this only leads to more dynamic SQL.
Is this the purpose of this exercise?
In real-life situations the requirements for a data model are known in
advance and allow for more efficient planning. But that's another story. :)
ML
http://milambda.blogspot.com/|||i thank u all for ur valuable suggestions..
i solved the uniqueness issue of global temporary tables by using your idea
of including the @.@.SPID as part of the table name when creating the
tables...please inform me of any other issues involved
regards,
kishore
"ML" wrote:

> Try executing your script in two separate sessions step-by-step - i.e. cre
ate
> the object in the first session, then in the second, make the changes in t
he
> first, then in the second, etc.
> After each step check the state of the object (perhaps from a third
> connection). You'll see what I mean.
> Even this situation can be remedied by giving the objects unique names (e.
g.
> including SPID in the table name), but this only leads to more dynamic SQL
.
> Is this the purpose of this exercise?
> In real-life situations the requirements for a data model are known in
> advance and allow for more efficient planning. But that's another story. :
)
>
> ML
> --
> http://milambda.blogspot.com/

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.sql

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.

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.

altering table with default value

Hi, How to alter a table with default value?
I am using the below statement, But, it is not working..Any pointers?
Thx..
----------
alter table action_item ALTER COLUMN STATUS default 0ALTER TABLE ACTION_ITEM ADD CONSTRAINT
DF_ACTION_ITEM_STATUS DEFAULT 0 FOR STATUS
GO
UPDATE ACTION_ITEM SET STATUS =0 where STATUS IS NULL
GO

Altering table structure with out deleting replication ?

Dear Members
Is there a way 2 update the table structure that is part of an article
without deleting the replication ?
Best Regards
Shahid Saleem
*** Sent via Developersdex http://www.codecomments.com ***
Shahid,
the best you can do is sp_repladdcolumn and sp_repldropcolumn. Combinations
of these can be used to alter existing column definitions (see
http://www.replicationanswers.com/AddColumn.asp). This all changes in SQL
Server 2005.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Altering table structure by comparing it to a MODEL table

Hi,

I have an application which needs to use the MODEL database (This is

created by me and it acts as a template) to synchronize the tables,

stored procedures, triggers, indexes across multiple client database.

Basically, if there is a structural change to the MODEL database the

client databases need to be updated. For example, If I create a new

table or alter a table or modify a stored procedure or modify an index

the client databases need to be updated when I run the sync routine.

So far I have managed to create/drop tables, create/drop columns, create/drop stored procedures and create/drop triggers.

I need to be able to drop all relationships (constraints) and indexes

and recreate them. I need to know how this can be done using SMO. I can

do it using TSQL but I want to know if there is an alternative.

Thanks in advance.I figured it out ...

#Region " Relationships "
Private Sub DropRelationships(ByVal db As Database)
For Each tbl As Table In db.Tables
If tbl.ForeignKeys.Count > 0 Then
For Each key As ForeignKey In tbl.ForeignKeys
key.MarkForDrop(True)
Next
tbl.Alter()
End If
Next
End Sub

Private Sub CreateRelationships(ByVal AlterDB As Database, ByVal ModelDB As Database)
For Each tbl As Table In ModelDB.Tables
If tbl.ForeignKeys.Count > 0 Then
For Each key As ForeignKey In tbl.ForeignKeys
For Each col As ForeignKeyColumn In key.Columns
Dim rc As Column
Dim fk As ForeignKey
fk = New ForeignKey(AlterDB.Tables(tbl.Name), key.Name)
For Each c As Column In AlterDB.Tables(key.ReferencedTable).Columns
If c.InPrimaryKey Then
rc = c
End If
Next
Dim fkc As ForeignKeyColumn
fkc = New ForeignKeyColumn(fk, col.Name, rc.Name)
fk.Columns.Add(fkc)

fk.ReferencedTable = key.ReferencedTable
fk.ReferencedTableSchema = key.ReferencedTableSchema

fk.Create()
Next
Next
End If
Next
End Sub
#End Regionsql

Altering table structure by comparing it to a MODEL table

Hi,

I have an application which needs to use the MODEL database (This is

created by me and it acts as a template) to synchronize the tables,

stored procedures, triggers, indexes across multiple client database.

Basically, if there is a structural change to the MODEL database the

client databases need to be updated. For example, If I create a new

table or alter a table or modify a stored procedure or modify an index

the client databases need to be updated when I run the sync routine.

So far I have managed to create/drop tables, create/drop columns, create/drop stored procedures and create/drop triggers.

I need to be able to drop all relationships (constraints) and indexes

and recreate them. I need to know how this can be done using SMO. I can

do it using TSQL but I want to know if there is an alternative.

Thanks in advance.I figured it out ...

#Region " Relationships "
Private Sub DropRelationships(ByVal db As Database)
For Each tbl As Table In db.Tables
If tbl.ForeignKeys.Count > 0 Then
For Each key As ForeignKey In tbl.ForeignKeys
key.MarkForDrop(True)
Next
tbl.Alter()
End If
Next
End Sub

Private Sub CreateRelationships(ByVal AlterDB As Database, ByVal ModelDB As Database)
For Each tbl As Table In ModelDB.Tables
If tbl.ForeignKeys.Count > 0 Then
For Each key As ForeignKey In tbl.ForeignKeys
For Each col As ForeignKeyColumn In key.Columns
Dim rc As Column
Dim fk As ForeignKey
fk = New ForeignKey(AlterDB.Tables(tbl.Name), key.Name)
For Each c As Column In AlterDB.Tables(key.ReferencedTable).Columns
If c.InPrimaryKey Then
rc = c
End If
Next
Dim fkc As ForeignKeyColumn
fkc = New ForeignKeyColumn(fk, col.Name, rc.Name)
fk.Columns.Add(fkc)

fk.ReferencedTable = key.ReferencedTable
fk.ReferencedTableSchema = key.ReferencedTableSchema

fk.Create()
Next
Next
End If
Next
End Sub
#End Region

Altering Table Replicated

How can i change my Table Structure that is replicated?

I need to add a new field.

In SQL Server 2005 you can use alter table syntax to add/remove/change columns in a replicated table. Check out the following link for more information -- http://msdn2.microsoft.com/en-us/library/ms151870.aspx

If you are using SQL 2000, you are limited to the functionality of sp_repladdcolumn and sp_repldropcolumn. SQL Server 2000 Books Online will give you more information on the syntax of these procs.

Hope this helps,

Tom

This posting is provided "AS IS" with no warranties, and confers no rights.

Altering table & trans. replication

Hi,

How can I modify table with publication (change of one column length)
without completely breaking replication.

Thanks in advance"Wagner" <wagner@.email.t-com.hr> wrote in message
news:1x0k6ml5is0vs$.mmdq6nunj5l6$.dlg@.40tude.net.. .
> Hi,
> How can I modify table with publication (change of one column length)
> without completely breaking replication.

Besides the method you found, I've also done the following:

Create a NEW column of the type you want, call it foo_temp.

Copy data into it.

Then sp_repldropcolumn on the existing column.

Then sp_repladdcolumn with the same name, but new definition.

Copy data back.

> Thanks in advance

Altering Table

Hai All.

I want to know ,is there any way to modify a table's field like adding of new field to a table.
If any one have idea plz enlighten me.
Bye

Regards,
Karthik.AYou can do this via Enterprise Manager, or via a T-SQL script (ALTER TABLE). Look in Books Online for the syntax:

You can download from here if you do not have it already:
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Cheers
Ken

Altering stored procedure which is part of sys schema

I am trying to alter sys.sp_helpmergeconflictrows which is part fof sys schema and is in System Stored Procedures.

Reason why I need this is because Conglict Viewer in merge replication fails to show data from one of my tables, because aforementioned sp fails during execution. It fails because sql query is declared as nvarchar(4000) and it needs to be longer. So, I tired to change it to nvarchar(max), but I cannot.

I tried few things in order to gain permission to alter that sp, but I fial always.

Can it be done at all, and if can, how?

Thanks

If you have a bug in the conflict viewer (or more specifically this stored proc), you should open a ticket with MS Tech Support to have this resolved.

Bryan

sql

Altering SQL Server 2000 table design

I'm trying to do a simple alteration to the table design of one of our
SQL 2k tables, simply changing an identity row so that its not 'not
for replication', and its taking absolutely ages to do so, and stops
the sql server from working.

Whilst it's attempting the update, no one can access the database, the
sqlservr.exe memory usage shoots up and enterprise manager reports a
not responding status. Eventually after about 10 minutes, it bombs out
reporting,

Unable to modify table
Could not allocate space for object 'Tmp_TableName' in database
'DBNAME' because the 'PRIMARY' filegroup is full.

The table i'm attempting to change has only about 4000 records so
there's not a huge amount of data.

Any ideas what's causing this and how i can get around it?

A similar thing happens when i attempt to change the length of a
varchar too.

Thanks in advance for any suggestions

Dan Williams."Dan Williams" <dan_williams@.newcross-nursing.com> wrote in message
news:2eac5d02.0406040735.5d88d033@.posting.google.c om...
> I'm trying to do a simple alteration to the table design of one of our
> SQL 2k tables, simply changing an identity row so that its not 'not
> for replication', and its taking absolutely ages to do so, and stops
> the sql server from working.
> Whilst it's attempting the update, no one can access the database, the
> sqlservr.exe memory usage shoots up and enterprise manager reports a
> not responding status. Eventually after about 10 minutes, it bombs out
> reporting,
> Unable to modify table
> Could not allocate space for object 'Tmp_TableName' in database
> 'DBNAME' because the 'PRIMARY' filegroup is full.
> The table i'm attempting to change has only about 4000 records so
> there's not a huge amount of data.
> Any ideas what's causing this and how i can get around it?
> A similar thing happens when i attempt to change the length of a
> varchar too.
> Thanks in advance for any suggestions
> Dan Williams.

Unfortunately, ALTER TABLE doesn't allow you to modify IDENTITY columns, so
there's no way to remove the NOT FOR REPLICATION option without recreating
the table. Behind the scenes, Enterprise Manager will create a new table,
set IDENTITY_INSERT ON, INSERT the rows from the existing table, drop the
original table, then rename the new one. Tmp_TableName is the 'working'
table that will be renamed after the existing TableName is dropped.

With a large table, this can be a slow process requiring a lot of disk
space, but 4000 rows doesn't sound like much data (unless you have
text/image columns perhaps). Anyway, the error message is clear - no more
space in the filegroup. So you need to add space by expanding the existing
database file(s). If you can't do this for some reason, then one solution
might be to use bcp.exe or DTS to export the data to a flat file, drop and
recreate the table yourself, then import the data.

Finally, as a general remark, Enterprise Manager hides a lot of what it's
really doing from you, so many people prefer to use Query Analyzer as much
as possible, since then you have complete control over what you're doing.

Simon|||Thanks for the response.

Having done a bit more research on Google i managed to find this:-

run this in your publication database.
Here I am setting the identity column for the jobs table to NFR

sp configure 'allow updates', 1
GO
reconfigure with override
GO
update syscolumns set colstat = colstat | 0x0008 where colstat &
0x0001 <> 0 and colstat & 0x0008 = 0 and id=object id('jobs')
GO
sp configure 'allow updates', 0

Anyone know the value to set colstat too, so as to disable the NFR,
and just make it a normal IDENTITY value?

I also found this web site which was a good reference.

http://www.winnetmag.com/SQLServer/...2080/22080.html

Having clicked on the 'Save Change Script' button of Enterprise
Manager when attempting to do this, I see what you mean about the
amount of work that EM actually does.

Thanks again

Dan.

> Unfortunately, ALTER TABLE doesn't allow you to modify IDENTITY columns, so
> there's no way to remove the NOT FOR REPLICATION option without recreating
> the table. Behind the scenes, Enterprise Manager will create a new table,
> set IDENTITY_INSERT ON, INSERT the rows from the existing table, drop the
> original table, then rename the new one. Tmp_TableName is the 'working'
> table that will be renamed after the existing TableName is dropped.
> With a large table, this can be a slow process requiring a lot of disk
> space, but 4000 rows doesn't sound like much data (unless you have
> text/image columns perhaps). Anyway, the error message is clear - no more
> space in the filegroup. So you need to add space by expanding the existing
> database file(s). If you can't do this for some reason, then one solution
> might be to use bcp.exe or DTS to export the data to a flat file, drop and
> recreate the table yourself, then import the data.
> Finally, as a general remark, Enterprise Manager hides a lot of what it's
> really doing from you, so many people prefer to use Query Analyzer as much
> as possible, since then you have complete control over what you're doing.
> Simon|||"Dan Williams" <dan_williams@.newcross-nursing.com> wrote in message
news:2eac5d02.0406041501.37b74d00@.posting.google.c om...
> Thanks for the response.
> Having done a bit more research on Google i managed to find this:-
> run this in your publication database.
> Here I am setting the identity column for the jobs table to NFR
> sp configure 'allow updates', 1
> GO
> reconfigure with override
> GO
> update syscolumns set colstat = colstat | 0x0008 where colstat &
> 0x0001 <> 0 and colstat & 0x0008 = 0 and id=object id('jobs')
> GO
> sp configure 'allow updates', 0
>
> Anyone know the value to set colstat too, so as to disable the NFR,
> and just make it a normal IDENTITY value?
> I also found this web site which was a good reference.
> http://www.winnetmag.com/SQLServer/...2080/22080.html
> Having clicked on the 'Save Change Script' button of Enterprise
> Manager when attempting to do this, I see what you mean about the
> amount of work that EM actually does.
> Thanks again
> Dan.

<snip
Based on the query above, you need an XOR operation to remove NOT FOR
REPLICATION:

update syscolumns
set colstat = colstat ^ 8
where colstat & 1 <> 0
and colstat & 8 <> 0
and id =object_id('jobs')

But be very careful with this - Microsoft does not support modifications to
system tables (see "System Tables" in Books Online), and the colstat column
is not documented (see "syscolumns"). So if you have problems, then you're
on your own - dropping and recreating the table is the supported, reliable
method.

Simon

Altering SQL Field value to Null (DateField)

Hi,
Can someone please help me in resolving this problem.

I am accessing an SQL server from a Web page, when I update a record I sometimes would like to replace a date with a null value. ie. Delete the date in the grid on the Web page and have it remove the date in the database.

I have looked around the web and on this forum and cannot find any information about doing this type of thing.

Someone help would be greatly appreciated.

Thanks..

Regards..

Peter Annandale.Hi Peter,

It depends on the code you're using, but you should be able to set it to DBNull.Value.

If this doesn't work, post your code and we'll try to help you sort it out.

Don|||Don,
since I posted I actually found an article posted by Moorstream in early July about the exact problem I am having. By applying the recommendations of salman_arshad it has fixed my problem.

Thanks for your quick response and assistance.

BTW I had teh right idea with the DBNULL.Value I just wasn't aplying it correctly.

Regards..

Peter Annandale

Altering objects

This is a question regarding a general problem of scripting DB changes\alteration:

I'm writing script which contain few (lets say 2) DDL statements, which depend one on the other.

Each statement has it own "Go" command afterwards, for executing it.

I'm writing a script to alter a field and make it not null:

"Alter <table name> alter column <column name> int not null"

, so I can define it afterwards as a primary key, and the next statement add a primary key constraint on this filed:

Alter table <table name>

ADD CONSTRAINT <name of constraint> PRIMARY KEY (name of field).

It all goes fine if I run the statements separatelly !!, but when I try to run them in a script, there's an error message saying that a nullable field cannot be defined as primary key (I don't have the exact syntax here, I'm on a few days vacation).

It seems like the sql server dosen't recognize "quick enough" the first alter of the field, to become not null, so it can allow the field to be primary key.

Is it a problem of unupdated system tables ?

It seems like this is a general problems, because it happens to me also ina nother case, when I try to disable a table trigger, so I can delete one of it's records (the trigger dosen't allow deleting records).

Please support.

Guy

Is it a case of a missing [ GO ] between the ALTER statements, or missing parentheses around the column name?

This seems to work without error.

Code Snippet

CREATE TABLE MyTable
( RowID int IDENTITY,
MyData varchar(20),
MyCol2 char(1)
)
GO

ALTER TABLE MyTable
ALTER COLUMN MyCol2 int NOT NULL
GO

ALTER TABLE MyTable
ADD CONSTRAINT PK_MyCol2 PRIMARY KEY ( MyCol2 )
GO

|||

Guy, this is interesting - particularly as you mention that you are including GO between each statement.

Could you post your complete script to the forum for review?

Thanks

Chris

|||

Arnie,

Thanks for your reply.

The code you published is almost the same I have, except for the create table which dosen't exists in my code (in my case the table is laready exists), and also - in my script, there are few more statements. Still - dosen't work.

I'm actually looking for maybe a DBCC\system command, which can update the status of the relevant sys tables, maybe something like the DBCC UPDATEUSAGE is doing to the info of the sp_spaceused, to make the sysindexes synch with the actual data in the table. Any other solution will be helpfull.

|||

Chris,

Thank you for your reply.

I don't have the exact code here (I'm on a few days vacation), but what I generally do is (pseudo code)

Start transaction

statement 1

Go

error handeling (if @.@.error<>0 goto errorhandle

GO)

statment 2

Go

error handeling (if @.@.error<>0 goto errorhandle

GO)

errorhandle:

rollback transction

commit transaction

As I mentioned, the code run with no problems if I run it step by step, and if I fix the is null statement separatelly (run it before, seperatelly, to make it not null), then the all script run with no problems.

|||

Since the table exists, it would be helpful to post both the table DDL as well as the actual procedure code.

No, there is not a DBCC command that would do what you are asking.

The problem apparantly exists in the information we can't see.

|||

The problem appears to be due to the following:

The GO immediately after each of your IF @.@.ERROR checks. You're trying to jump to a label (errorhandle) which is declared outside of the current batch. Labels can only be referenced within the batch in which they are declared.

Altering multiple objects schema

Hi,

I need to change the schema of the stored procedures of several databases.

Is there a way to put the alter schema statement within a loop that automaticaly processes all the stored procedures in a given database ?

thank you

Probably your best option is to use a cursor. You can find more information about them in BOL (http://msdn2.microsoft.com/en-us/library/ms180169.aspx)

-Raul Garcia

SDE/T

SQL Server Engine

|||

You can also try doing something like this. If NEWSCHEMA is the schema you want to transfer all the procedures to the following query should help

declare @.querystring nvarchar(MAX)

set @.querystring=''

select @.querystring=@.querystring+' ALTER SCHEMA NEWSCHEMA TRANSFER ' + schema_name(schema_id) + '.' + name from sys.procedures

exec(@.querystring)

Either way, you will have to use dynamic sql.