Alternate coloring in Groups

Hi,
im able to get the alternate coloring in tables using the code
=iif(RowNumber(Nothing) Mod 2,"WhiteSmoke", "LightGrey")
when i insert a group in the table for the weekenddate.the alternate
coloring of rows has disappeared...
i cant get where im going wrong...
im grouping the record based on the weekend date...
Thanks in advamce for ur help,Using RowNumber can be a bit hit and miss.
I use a bit of code to handle this, it also works at any group level.
In report properties|Code type the following;
Public Dim Lv(4) As Boolean
Public Function Switch(ByRef Value As Boolean) As Boolean
Value = Not Value
Return Value
End Function
In the BackgroundColor property of the first cell on a row use the
following expression;
=IIf(Code.Switch(Code.Lv(1)),"WhiteSmoke","LightGrey")
In all subsequent cells use;
=IIf(Code.Lv(1),"WhiteSmoke","LightGrey")
Use different subscripts with Lv for different group levels.
This also works in matrix regions too.
Chris
CCP wrote:
> Hi,
> im able to get the alternate coloring in tables using the code
> =iif(RowNumber(Nothing) Mod 2,"WhiteSmoke", "LightGrey")
> when i insert a group in the table for the weekenddate.the alternate
> coloring of rows has disappeared...
> i cant get where im going wrong...
> im grouping the record based on the weekend date...
> Thanks in advamce for ur help,|||Thank u very much Chris ,
It worked...
"Chris McGuigan" wrote:
> Using RowNumber can be a bit hit and miss.
> I use a bit of code to handle this, it also works at any group level.
> In report properties|Code type the following;
> Public Dim Lv(4) As Boolean
> Public Function Switch(ByRef Value As Boolean) As Boolean
> Value = Not Value
> Return Value
> End Function
> In the BackgroundColor property of the first cell on a row use the
> following expression;
> =IIf(Code.Switch(Code.Lv(1)),"WhiteSmoke","LightGrey")
> In all subsequent cells use;
> =IIf(Code.Lv(1),"WhiteSmoke","LightGrey")
> Use different subscripts with Lv for different group levels.
> This also works in matrix regions too.
> Chris
>
> CCP wrote:
> > Hi,
> > im able to get the alternate coloring in tables using the code
> > =iif(RowNumber(Nothing) Mod 2,"WhiteSmoke", "LightGrey")
> > when i insert a group in the table for the weekenddate.the alternate
> > coloring of rows has disappeared...
> > i cant get where im going wrong...
> > im grouping the record based on the weekend date...
> >
> > Thanks in advamce for ur help,
>|||Chris,
Can i sort the records within the groups'
Thanks,
"CCP" wrote:
> Thank u very much Chris ,
> It worked...
>
> "Chris McGuigan" wrote:
> > Using RowNumber can be a bit hit and miss.
> >
> > I use a bit of code to handle this, it also works at any group level.
> >
> > In report properties|Code type the following;
> >
> > Public Dim Lv(4) As Boolean
> >
> > Public Function Switch(ByRef Value As Boolean) As Boolean
> > Value = Not Value
> > Return Value
> > End Function
> >
> > In the BackgroundColor property of the first cell on a row use the
> > following expression;
> >
> > =IIf(Code.Switch(Code.Lv(1)),"WhiteSmoke","LightGrey")
> >
> > In all subsequent cells use;
> >
> > =IIf(Code.Lv(1),"WhiteSmoke","LightGrey")
> >
> > Use different subscripts with Lv for different group levels.
> > This also works in matrix regions too.
> >
> > Chris
> >
> >
> >
> > CCP wrote:
> >
> > > Hi,
> > > im able to get the alternate coloring in tables using the code
> > > =iif(RowNumber(Nothing) Mod 2,"WhiteSmoke", "LightGrey")
> > > when i insert a group in the table for the weekenddate.the alternate
> > > coloring of rows has disappeared...
> > > i cant get where im going wrong...
> > > im grouping the record based on the weekend date...
> > >
> > > Thanks in advamce for ur help,
> >
> >|||Sure, right click the row tag of the relevant group, click edit group
and you will see a sorting tab.
Chris
CCP wrote:
> Chris,
> Can i sort the records within the groups'
> Thanks,
> "CCP" wrote:
> > Thank u very much Chris ,
> > It worked...
> >
> >
> > "Chris McGuigan" wrote:
> >
> > > Using RowNumber can be a bit hit and miss.
> > >
> > > I use a bit of code to handle this, it also works at any group
> > > level.
> > >
> > > In report properties|Code type the following;
> > >
> > > Public Dim Lv(4) As Boolean
> > >
> > > Public Function Switch(ByRef Value As Boolean) As Boolean
> > > Value = Not Value
> > > Return Value
> > > End Function
> > >
> > > In the BackgroundColor property of the first cell on a row use the
> > > following expression;
> > >
> > > =IIf(Code.Switch(Code.Lv(1)),"WhiteSmoke","LightGrey")
> > >
> > > In all subsequent cells use;
> > >
> > > =IIf(Code.Lv(1),"WhiteSmoke","LightGrey")
> > >
> > > Use different subscripts with Lv for different group levels.
> > > This also works in matrix regions too.
> > >
> > > Chris
> > >
> > >
> > >
> > > CCP wrote:
> > >
> > > > Hi,
> > > > im able to get the alternate coloring in tables using the code
> > > > =iif(RowNumber(Nothing) Mod 2,"WhiteSmoke", "LightGrey")
> > > > when i insert a group in the table for the weekenddate.the
> > > > alternate coloring of rows has disappeared...
> > > > i cant get where im going wrong...
> > > > im grouping the record based on the weekend date...
> > > >
> > > > Thanks in advamce for ur help,
> > >
> > >

Alter table weird bug?

I have created the following test SQL code to illustrate a real
problem I have with some SQL code.

CREATE TABLE JCTable ( CustomerName varchar(50) )
ALTER TABLE JCTable ADD CustomerNo int
INSERT INTO JCTable ( CustomerName , CustomerNo ) VALUES ( 'Jon Combe'
, 1 )
INSERT INTO JCTable ( CustomerName , CustomerNo ) VALUES ( 'Bill
Gates' , 1 )
UPDATE JCTable SET CustomerNo = 2 WHERE CustomerName = 'Jon Combe'
SELECT * FROM JCTable

When I run this SQL via the query analyser I get the errors:-

Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'CustomerNo'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'CustomerNo'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'CustomerNo'.

It appears the SQL Server is trying to "pre-parse" the query and
hasn't picked up on the ALTER TABLE line that adds this column and so
complains that it doesn't exist. However it doesn't end there.

If I then run this query a line at a time by highlighting each line in
the query analyser and running it all works (not unexpected). However
then dropping the table and then running the full SQL code once more
(I.E. not highlighting each line), it then works as expected. I assume
it was somehow remembering "state" in my session so closed and
re-started the Query Analyser with the same result that the code does
now work.

However changing the table name to something new brings back the
errors once more. Can anyone explain what is going on here? I am
dropping the table before re-running the code each time.

I'm using SQL Server 2000 if that makes a difference.

Thanks.
Jon.Not a bug. As you rightly suspected, SQL Server tries to validate your
code first by attempting to resolve any column or object references to
existing objects and columns. If the table doesn't exist at compile time
then the resolution of that table's columns is deferred until the
statement executes. However, if the table exists then you will receive
an error if the referenced columns don't also exist. A workaround is to
put a GO batch separator after the ALTER TABLE statement so that the
second batch will be compiled independently.

For this reason among others it is good practice always to separate DDL
(Data Definition Language, such as CREATE and ALTER table statements)
and DML (Data Manipulation Language, such as SELECT, UPDATE, INSERT,
DELETE statements). I would create separate scripts for your DDL and DML
statements.

--
David Portas
SQL Server MVP
--

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||David Portas wrote:

> Not a bug. As you rightly suspected, SQL Server tries to validate your
> code first by attempting to resolve any column or object references to
> existing objects and columns. If the table doesn't exist at compile time
> then the resolution of that table's columns is deferred until the
> statement executes.

David,

Thanks, this does make sense however the table in my statement did not exist
at compile time (otherwise the create table line would fail), so that is
why I cannot understand why I get that message.

Thanks.
Jon.|||Jon Combe (jcombe@.acxiom.co.uk) writes:
> Thanks, this does make sense however the table in my statement did not
> exist at compile time (otherwise the create table line would fail), so
> that is why I cannot understand why I get that message.

Your batch gets compiled several times. First you have:

CREATE TABLE JCTable ( CustomerName varchar(50) )
ALTER TABLE JCTable ADD CustomerNo int
INSERT INTO JCTable ( CustomerName , CustomerNo )
VALUES ( 'Jon Combe' , 1 )
INSERT INTO JCTable ( CustomerName , CustomerNo )
VALUES ( 'Bill Gates' , 1 )
UPDATE JCTable SET CustomerNo = 2 WHERE CustomerName = 'Jon Combe'
SELECT * FROM JCTable

On the first compile, all but the first statement is deferred. Once the
table has been created, SQL Server hits the ALTER TABLE, finds that the
statement is deferred, and recompiles the batch. This time, all statements
are scrutinized, since once all tables in a query exist, SQL Server per-
form full checks on the query.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:

> Your batch gets compiled several times. First you have:
> CREATE TABLE JCTable ( CustomerName varchar(50) )
> ALTER TABLE JCTable ADD CustomerNo int
> INSERT INTO JCTable ( CustomerName , CustomerNo )
> VALUES ( 'Jon Combe' , 1 )
> INSERT INTO JCTable ( CustomerName , CustomerNo )
> VALUES ( 'Bill Gates' , 1 )
> UPDATE JCTable SET CustomerNo = 2 WHERE CustomerName = 'Jon Combe'
> SELECT * FROM JCTable
> On the first compile, all but the first statement is deferred. Once the
> table has been created, SQL Server hits the ALTER TABLE, finds that the
> statement is deferred, and recompiles the batch. This time, all statements
> are scrutinized, since once all tables in a query exist, SQL Server per-
> form full checks on the query.

Thanks Erland,

Why doesn't it spot that I've added a column and then either defer the last
three statements again, or recognise that the column I'm using does now
exist? I'd expect that sort of behaviour from it although I take the point
made earlier that it's best to split the statements with a GO between them.

Also given the way it is compiling the code it doesn't explain why if I run
these statements one line at a time, then drop the table, reload Query
Analyser and re-run this full batch of code it works yet running the full
batch before the table has ever existed generates an error. That just seems
weird, so if anyone can explain why I'd be interested to hear it! The
database should be in the same state in both cases, but as the behaviour is
different it can't be.

Jon.|||Jon Combe wrote:
> Erland Sommarskog wrote:
>
>>Your batch gets compiled several times. First you have:
>>
>> CREATE TABLE JCTable ( CustomerName varchar(50) )
>> ALTER TABLE JCTable ADD CustomerNo int
>> INSERT INTO JCTable ( CustomerName , CustomerNo )
>> VALUES ( 'Jon Combe' , 1 )
>> INSERT INTO JCTable ( CustomerName , CustomerNo )
>> VALUES ( 'Bill Gates' , 1 )
>> UPDATE JCTable SET CustomerNo = 2 WHERE CustomerName = 'Jon Combe'
>> SELECT * FROM JCTable
>>
>>On the first compile, all but the first statement is deferred. Once the
>>table has been created, SQL Server hits the ALTER TABLE, finds that the
>>statement is deferred, and recompiles the batch. This time, all statements
>>are scrutinized, since once all tables in a query exist, SQL Server per-
>>form full checks on the query.
>
> Thanks Erland,
> Why doesn't it spot that I've added a column and then either defer the last
> three statements again, or recognise that the column I'm using does now
> exist? I'd expect that sort of behaviour from it although I take the point
> made earlier that it's best to split the statements with a GO between them.
> Also given the way it is compiling the code it doesn't explain why if I run
> these statements one line at a time, then drop the table, reload Query
> Analyser and re-run this full batch of code it works yet running the full
> batch before the table has ever existed generates an error. That just seems
> weird, so if anyone can explain why I'd be interested to hear it! The
> database should be in the same state in both cases, but as the behaviour is
> different it can't be.
> Jon.

I have to confess that this behaviour, if it is "normal", is surprising
to me. Is this the way the product is expected to behave?

Thanks.
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu
(replace 'x' with 'u' to respond)|||Jon Combe (jcombe@.acxiom.co.uk) writes:
> Why doesn't it spot that I've added a column

No, you haven't added a column. You get the error the table has been
created, but the ALTER TABLE statement has not been executed. Since the
ALTER TABLE statement was deferred, SQL Server recompiles the batch,
and it recompiles the batch, because that the lowest granularity for
compilation in SQL 2000. And since at this point the columns does not
exist, the compilation fails.

> and then either defer the last three statements again,

SQL Server could defer compilation because of unknown columns too, but it
has quite some ramifications, and I am very happy that unknown columns is
reason for deferral. It is bad as it is. To wit, when you create a stored
procedure, you want to be alerted if you have misspelled a table name of a
column name. Due to deferred name resolution, you don't get alerts for
misspelled table names, but since SQL Server checks the query once all
tables are there, you do at least sometimes get alerts about misspelling
column names. (And in our in-house load tool, I scan the code for table
references to find the missing tables, and also perform some tricks to
get SQL Server check queries with temp tables too.

But, there is light at the end of the tunnel. Your script runs as you
expected in SQL 2005. This is because SQL 2005 is able to recompile a
single statement in a batch, so the entire batch is not recompiled at
once.

> Also given the way it is compiling the code it doesn't explain why if I
> run these statements one line at a time, then drop the table, reload
> Query Analyser and re-run this full batch of code it works yet running
> the full batch before the table has ever existed generates an error.
> That just seems weird, so if anyone can explain why I'd be interested to
> hear it! The database should be in the same state in both cases, but as
> the behaviour is different it can't be.

This has to do with cached plans. The plans are in the cache, even if
the table is dropped. (This makes sense with temp tables.) Yes, it's
certainly a bit confusing, but for the situations for which the behaviour
is designed, it gives the best result.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:

>> Also given the way it is compiling the code it doesn't explain why if I
>> run these statements one line at a time, then drop the table, reload
>> Query Analyser and re-run this full batch of code it works yet running
>> the full batch before the table has ever existed generates an error.
>> That just seems weird, so if anyone can explain why I'd be interested to
>> hear it! The database should be in the same state in both cases, but as
>> the behaviour is different it can't be.
> This has to do with cached plans. The plans are in the cache, even if
> the table is dropped. (This makes sense with temp tables.) Yes, it's
> certainly a bit confusing, but for the situations for which the behaviour
> is designed, it gives the best result.

So basically with the query I have, whether I get an error or not (when the
database is in the same state) depends entirely on what has been run
previously? That doesn't seem very satisfactory!

Jon.

Alter table script

Hello,
I want to insert a new column in a data table using code (from VB.NET)
Is there a stored procedure or does any example script exists to do this.
I need to take in account any existing keys, indexes, constraints etc which
exist on the table.
Thanks
TimDo you want to:
1) Add a column to an SQL table through .NET? or
2) Add a column to a DataTable in .NET? or
3) Add a column through a DataTable in .NET and have the change persist into
the source table in SQL?
Christian Smith
"Tim Marsden" <TM@.UK.COM> wrote in message
news:eDM073W8DHA.1428@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I want to insert a new column in a data table using code (from VB.NET)
> Is there a stored procedure or does any example script exists to do this.
> I need to take in account any existing keys, indexes, constraints etc
which
> exist on the table.
> Thanks
> Tim
>
>|||Thanks for reply
I want to add a column to a SQL server table.
If I have to do it through a .NET datatable so be it.
Thanks
"Christian Smith" <csmith@.digex.com> wrote in message
news:uB3hKMX8DHA.3200@.TK2MSFTNGP09.phx.gbl...
> Do you want to:
> 1) Add a column to an SQL table through .NET? or
> 2) Add a column to a DataTable in .NET? or
> 3) Add a column through a DataTable in .NET and have the change persist
into
> the source table in SQL?
> Christian Smith
> "Tim Marsden" <TM@.UK.COM> wrote in message
> news:eDM073W8DHA.1428@.TK2MSFTNGP12.phx.gbl...
this.
> which
>|||You should be able to use the standard SQL DDL throught the SqlCommand
class. Adding a column should not affect any of the indexes or constraints
since they could not have possibly been defined to include a column that
does not yet exist. That might be an issue for deleting a column but I
can't imagine that it would affect an addition.
Alternately, I think that there is a way to modify the table structure of a
DataTable in .NET and have it push the change up to a SQL database if they
are linked. I tried to get it to work once before but couldn't get it and
did what I needed a different way.
Sorry that is all I got.
Christian Smith
"Tim Marsden" <TM@.UK.COM> wrote in message
news:OvTUBbX8DHA.3880@.tk2msftngp13.phx.gbl...
> Thanks for reply
> I want to add a column to a SQL server table.
> If I have to do it through a .NET datatable so be it.
> Thanks
>
> "Christian Smith" <csmith@.digex.com> wrote in message
> news:uB3hKMX8DHA.3200@.TK2MSFTNGP09.phx.gbl...
> into
> this.
>|||Hi Tim,
I am reviewing you post and since I have not heard from you for some time,
I wonder whether you have solved you problem or you still have any
questions about that. I agree with our community member's suggestion, that
is to use the DDL of T-SQL as the following example:
use pubs
go
CREATE TABLE dbo.testaddcol1
(
id int NULL
) ON [PRIMARY]
go
ALTER TABLE dbo.testaddcol1 ADD col1 varchar(25) NULL
go
Hope this helps and I am waiting on your replay. Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Thanks for all the suggestions.
I have discovered there is no easy way to alter a table in code, the
complexity of indexes, permissions etc is to great.
The simple functions can be performed easily with ALTER TABLE and a few
stored procedures, he complex stuff, I have left alone.
Tim
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:N994H%23b9DHA.704@.cpmsftngxa07.phx.gbl...
> Hi Tim,
> I am reviewing you post and since I have not heard from you for some time,
> I wonder whether you have solved you problem or you still have any
> questions about that. I agree with our community member's suggestion, that
> is to use the DDL of T-SQL as the following example:
> use pubs
> go
> CREATE TABLE dbo.testaddcol1
> (
> id int NULL
> ) ON [PRIMARY]
> go
> ALTER TABLE dbo.testaddcol1 ADD col1 varchar(25) NULL
> go
> Hope this helps and I am waiting on your replay. Thanks.
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||Hello Tim,
As Christian has stated, you can use standard SQL DDL/DML thru SQL
SqlCommand Class.
You code should pretty much look like this..(modify the table/col
names etc as per your need )
Imports System
Imports System.Data
Imports System.Data.SqlClient
Try
Dim myConnString As String ="User ID=myUID;password=myPWD;Initial
Catalog=pubs;Data Source=mySQLServer"
Dim myAlterQuery As String = "ALTER TABLE dbo.testaddcol1 ADD col1
varchar(25) NULL"
Dim myConnection As New SqlConnection(myConnString)
Dim myCommand As New SqlCommand(myAlterQuery, myConnection)
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
Catch ex As Exception
MessageBox.Show(ex.ToString())
End Try
Thanks for using MSDN Newsgroup.
Vikrant Dalwale
Microsoft SQL Server Support Professional
Microsoft highly recommends to all of our customers that they visit
the http://www.microsoft.com/protect site and perform the three
straightforward steps listed to improve your computers security.
This posting is provided "AS IS" with no warranties, and confers no
rights.
--
>From: "Tim Marsden" <TM@.UK.COM>
>References: <eDM073W8DHA.1428@.TK2MSFTNGP12.phx.gbl>
<uB3hKMX8DHA.3200@.TK2MSFTNGP09.phx.gbl>
>Subject: Re: Alter table script
>Date: Thu, 12 Feb 2004 14:44:42 -0000
>Lines: 40
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
>Message-ID: <OvTUBbX8DHA.3880@.tk2msftngp13.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: host213-122-124-46.in-addr.btopenworld.com
213.122.124.46
>Path:
cpmsftngxa07.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msft
ngp13.phx.gbl
>Xref: cpmsftngxa07.phx.gbl microsoft.public.sqlserver.server:328897
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Thanks for reply
>I want to add a column to a SQL server table.
>If I have to do it through a .NET datatable so be it.
>Thanks
>
>"Christian Smith" <csmith@.digex.com> wrote in message
>news:uB3hKMX8DHA.3200@.TK2MSFTNGP09.phx.gbl...
persist
>into
VB.NET)
do
>this.
constraints etc
>
>

Alter table script

Hello,
I want to insert a new column in a data table using code (from VB.NET)
Is there a stored procedure or does any example script exists to do this.
I need to take in account any existing keys, indexes, constraints etc which
exist on the table.
Thanks
TimDo you want to:
1) Add a column to an SQL table through .NET? or
2) Add a column to a DataTable in .NET? or
3) Add a column through a DataTable in .NET and have the change persist into
the source table in SQL?
Christian Smith
"Tim Marsden" <TM@.UK.COM> wrote in message
news:eDM073W8DHA.1428@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I want to insert a new column in a data table using code (from VB.NET)
> Is there a stored procedure or does any example script exists to do this.
> I need to take in account any existing keys, indexes, constraints etc
which
> exist on the table.
> Thanks
> Tim
>
>|||Thanks for reply
I want to add a column to a SQL server table.
If I have to do it through a .NET datatable so be it.
Thanks
"Christian Smith" <csmith@.digex.com> wrote in message
news:uB3hKMX8DHA.3200@.TK2MSFTNGP09.phx.gbl...
> Do you want to:
> 1) Add a column to an SQL table through .NET? or
> 2) Add a column to a DataTable in .NET? or
> 3) Add a column through a DataTable in .NET and have the change persist
into
> the source table in SQL?
> Christian Smith
> "Tim Marsden" <TM@.UK.COM> wrote in message
> news:eDM073W8DHA.1428@.TK2MSFTNGP12.phx.gbl...
> > Hello,
> >
> > I want to insert a new column in a data table using code (from VB.NET)
> > Is there a stored procedure or does any example script exists to do
this.
> >
> > I need to take in account any existing keys, indexes, constraints etc
> which
> > exist on the table.
> >
> > Thanks
> > Tim
> >
> >
> >
>|||You should be able to use the standard SQL DDL throught the SqlCommand
class. Adding a column should not affect any of the indexes or constraints
since they could not have possibly been defined to include a column that
does not yet exist. That might be an issue for deleting a column but I
can't imagine that it would affect an addition.
Alternately, I think that there is a way to modify the table structure of a
DataTable in .NET and have it push the change up to a SQL database if they
are linked. I tried to get it to work once before but couldn't get it and
did what I needed a different way.
Sorry that is all I got. :)
Christian Smith
"Tim Marsden" <TM@.UK.COM> wrote in message
news:OvTUBbX8DHA.3880@.tk2msftngp13.phx.gbl...
> Thanks for reply
> I want to add a column to a SQL server table.
> If I have to do it through a .NET datatable so be it.
> Thanks
>
> "Christian Smith" <csmith@.digex.com> wrote in message
> news:uB3hKMX8DHA.3200@.TK2MSFTNGP09.phx.gbl...
> > Do you want to:
> >
> > 1) Add a column to an SQL table through .NET? or
> > 2) Add a column to a DataTable in .NET? or
> > 3) Add a column through a DataTable in .NET and have the change persist
> into
> > the source table in SQL?
> >
> > Christian Smith
> >
> > "Tim Marsden" <TM@.UK.COM> wrote in message
> > news:eDM073W8DHA.1428@.TK2MSFTNGP12.phx.gbl...
> > > Hello,
> > >
> > > I want to insert a new column in a data table using code (from VB.NET)
> > > Is there a stored procedure or does any example script exists to do
> this.
> > >
> > > I need to take in account any existing keys, indexes, constraints etc
> > which
> > > exist on the table.
> > >
> > > Thanks
> > > Tim
> > >
> > >
> > >
> >
> >
>|||Hi Tim,
I am reviewing you post and since I have not heard from you for some time,
I wonder whether you have solved you problem or you still have any
questions about that. I agree with our community member's suggestion, that
is to use the DDL of T-SQL as the following example:
use pubs
go
CREATE TABLE dbo.testaddcol1
(
id int NULL
) ON [PRIMARY]
go
ALTER TABLE dbo.testaddcol1 ADD col1 varchar(25) NULL
go
Hope this helps and I am waiting on your replay. Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Thanks for all the suggestions.
I have discovered there is no easy way to alter a table in code, the
complexity of indexes, permissions etc is to great.
The simple functions can be performed easily with ALTER TABLE and a few
stored procedures, he complex stuff, I have left alone.
Tim
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:N994H%23b9DHA.704@.cpmsftngxa07.phx.gbl...
> Hi Tim,
> I am reviewing you post and since I have not heard from you for some time,
> I wonder whether you have solved you problem or you still have any
> questions about that. I agree with our community member's suggestion, that
> is to use the DDL of T-SQL as the following example:
> use pubs
> go
> CREATE TABLE dbo.testaddcol1
> (
> id int NULL
> ) ON [PRIMARY]
> go
> ALTER TABLE dbo.testaddcol1 ADD col1 varchar(25) NULL
> go
> Hope this helps and I am waiting on your replay. Thanks.
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||Hello Tim,
As Christian has stated, you can use standard SQL DDL/DML thru SQL
SqlCommand Class.
You code should pretty much look like this..(modify the table/col
names etc as per your need )
Imports System
Imports System.Data
Imports System.Data.SqlClient
Try
Dim myConnString As String ="User ID=myUID;password=myPWD;Initial
Catalog=pubs;Data Source=mySQLServer"
Dim myAlterQuery As String = "ALTER TABLE dbo.testaddcol1 ADD col1
varchar(25) NULL"
Dim myConnection As New SqlConnection(myConnString)
Dim myCommand As New SqlCommand(myAlterQuery, myConnection)
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
Catch ex As Exception
MessageBox.Show(ex.ToString())
End Try
Thanks for using MSDN Newsgroup.
Vikrant Dalwale
Microsoft SQL Server Support Professional
Microsoft highly recommends to all of our customers that they visit
the http://www.microsoft.com/protect site and perform the three
straightforward steps listed to improve your computer?s security.
This posting is provided "AS IS" with no warranties, and confers no
rights.
>From: "Tim Marsden" <TM@.UK.COM>
>References: <eDM073W8DHA.1428@.TK2MSFTNGP12.phx.gbl>
<uB3hKMX8DHA.3200@.TK2MSFTNGP09.phx.gbl>
>Subject: Re: Alter table script
>Date: Thu, 12 Feb 2004 14:44:42 -0000
>Lines: 40
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
>Message-ID: <OvTUBbX8DHA.3880@.tk2msftngp13.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: host213-122-124-46.in-addr.btopenworld.com
213.122.124.46
>Path:
cpmsftngxa07.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msft
ngp13.phx.gbl
>Xref: cpmsftngxa07.phx.gbl microsoft.public.sqlserver.server:328897
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Thanks for reply
>I want to add a column to a SQL server table.
>If I have to do it through a .NET datatable so be it.
>Thanks
>
>"Christian Smith" <csmith@.digex.com> wrote in message
>news:uB3hKMX8DHA.3200@.TK2MSFTNGP09.phx.gbl...
>> Do you want to:
>> 1) Add a column to an SQL table through .NET? or
>> 2) Add a column to a DataTable in .NET? or
>> 3) Add a column through a DataTable in .NET and have the change
persist
>into
>> the source table in SQL?
>> Christian Smith
>> "Tim Marsden" <TM@.UK.COM> wrote in message
>> news:eDM073W8DHA.1428@.TK2MSFTNGP12.phx.gbl...
>> > Hello,
>> >
>> > I want to insert a new column in a data table using code (from
VB.NET)
>> > Is there a stored procedure or does any example script exists to
do
>this.
>> >
>> > I need to take in account any existing keys, indexes,
constraints etc
>> which
>> > exist on the table.
>> >
>> > Thanks
>> > Tim
>> >
>> >
>> >
>>
>
>

ALTER TABLE MODIFY

hi!

i encountered problems when running this code in SQL Query

ALTER TABLE [dbo].[amsSchedule]
MODIFY(CutOff1 datetime NULL,
[FileName] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL)

my aim is to modify the two fields to change its data type. BUt when im trying to run this command in the query analyzer, itsays "incorrect syntax error '(' "

What do i have to do? please help me...thanks

Hi,

ALTER TABLE (...) when modifying a column only supports one change at a time.

HTH, Jens Suessmeyer.

|||

Hi Jens!

Thanks a lot for the tip...now i know what to do since u told me that alter table only supports one change at a time...

thanks a lot!

ALTER TABLE CHANGE question ...

I need to be able to change a table column name from within my C# code. The system in this part of the application is intended to be highly configurable and column names on the table being operated upon can change. The code adds square brackets to the column name (because the user might set up a column name with one or more spaces) but I'm not sure if I'm doing it right because I'm getting an error.

The code that builds the SQL command is:

SqlCommand =new SqlCommand("ALTER TABLE wto_facilities CHANGE [" +oldAccomType +"][" + accomType.Text +"] varchar(20)", conn);

 On the first test run, the code produces the following: ALTER TABLE wto_facilities CHANGE [Hotel] [Hotels] varchar(20)

However, it's giving me the following error: Incorrect syntax near 'Hotels'

It looks fine to me, but it's obviously not.

To change a column name you need to use sp_Rename

EXEC sp_rename 'table.column', 'newcolumnname', 'column'

Alter Table

Hi,
I would like to add a column in an existing table with this code.
ALTER TABLE tCheckImport ADD COLUMN bStdOpt BOOL
But it doesn' t work, what' s wrong with it ?
RegardsJust to add something, I want to set the default value to this column as
'False' too.
Thanks
"JuliaC" wrote:

> Hi,
> I would like to add a column in an existing table with this code.
> ALTER TABLE tCheckImport ADD COLUMN bStdOpt BOOL
> But it doesn' t work, what' s wrong with it ?
> Regards
>|||SQL Server doesnt know about BOOL, its bit there.
ALTER TABLE tCheckImport ADD bStdOpt BIT
HTH, jens Suessmeyer.|||Just to add that too:
ALTER TABLE tCheckImport ADD bStdOpt BIT DEFAULT 0
HTH, Jens Suessmeyer.|||That's what I am looking for
Thanks
"Jens" wrote:

> SQL Server doesnt know about BOOL, its bit there.
> ALTER TABLE tCheckImport ADD bStdOpt BIT
> HTH, jens Suessmeyer.
>|||Note, though, that bit is *not* a Boolean datatype. It is a numeric datatype
restricted to null, 1
and 0. It is up to us who uses the code to interpret whether 1 would corresp
ond to true or false.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"JuliaC" <JuliaC@.discussions.microsoft.com> wrote in message
news:BB388399-2AA3-4ED5-9D88-5E6CC857E85E@.microsoft.com...
> That's what I am looking for
> Thanks
> "Jens" wrote:
>

Alter Table

I am new to SQL and struggling with some basic code!
How do I use the ALTER TABLE command to add a foreign key constraint?
I have the correct code for creating the tables and adding the constraints
when creating, but can't figure out how to modify an existing table and add
a FK.
Thanks
Hi,
Sample code:-
create table t90(i int primary key)
go
create table t91(i int)
go
alter table t91 add constraint fk_t1 foreign key(i) references t90(i)
Thanks
Hari
MCDBA
"Keith" <@..> wrote in message news:uQUuKStTEHA.4048@.TK2MSFTNGP12.phx.gbl...
> I am new to SQL and struggling with some basic code!
> How do I use the ALTER TABLE command to add a foreign key constraint?
> I have the correct code for creating the tables and adding the constraints
> when creating, but can't figure out how to modify an existing table and
add
> a FK.
> Thanks
>

Alter Table

I am new to SQL and struggling with some basic code!
How do I use the ALTER TABLE command to add a foreign key constraint?
I have the correct code for creating the tables and adding the constraints
when creating, but can't figure out how to modify an existing table and add
a FK.
ThanksHi,
Sample code:-
create table t90(i int primary key)
go
create table t91(i int)
go
alter table t91 add constraint fk_t1 foreign key(i) references t90(i)
--
Thanks
Hari
MCDBA
"Keith" <@..> wrote in message news:uQUuKStTEHA.4048@.TK2MSFTNGP12.phx.gbl...
> I am new to SQL and struggling with some basic code!
> How do I use the ALTER TABLE command to add a foreign key constraint?
> I have the correct code for creating the tables and adding the constraints
> when creating, but can't figure out how to modify an existing table and
add
> a FK.
> Thanks
>|||This should do:
ALTER TABLE dbo.[Tablex] ADD CONSTRAINT
FK_Tablex_Tabley FOREIGN KEY
(
id
) REFERENCES dbo.Table1
(
id
)
GO
BTW,
if you require syntax like this, you can get EM to create
it for you. Just add the FK in table design and the "Save
Change Script" button becomes available.
HTH,
Paul Ibison

Alter Table

I am new to SQL and struggling with some basic code!
How do I use the ALTER TABLE command to add a foreign key constraint?
I have the correct code for creating the tables and adding the constraints
when creating, but can't figure out how to modify an existing table and add
a FK.
ThanksHi,
Sample code:-
create table t90(i int primary key)
go
create table t91(i int)
go
alter table t91 add constraint fk_t1 foreign key(i) references t90(i)
Thanks
Hari
MCDBA
"Keith" <@..> wrote in message news:uQUuKStTEHA.4048@.TK2MSFTNGP12.phx.gbl...
> I am new to SQL and struggling with some basic code!
> How do I use the ALTER TABLE command to add a foreign key constraint?
> I have the correct code for creating the tables and adding the constraints
> when creating, but can't figure out how to modify an existing table and
add
> a FK.
> Thanks
>

Alter Stored Procedure with asp.net code

I have looked all around and I am having no luck trying to figure out how to alter a stored procedure within an asp.net application.

Here is a short snippet of my code, but it keeps erroring out on me.

Try
myCommand.CommandText = "Using " & DatabaseName & vbNewLine & Me.txtStoredProcedures.Text
myCommand.ExecuteNonQuery()
myTran.Commit()
Catch ex As Exception
myTran.Rollback()
Response.Write(ex.ToString())
End Try

The reason for this is because I have to propagate stored procedures across many databases and was hoping to write an application for it.

Basically the database name is coming from a loop statement and I just want to keep on going through all the databases that I have chosen and have the stored procedure updated (altered) automatically

So i thought the code above was close, but it keeps catching on me. Anybody's help would be greatly appreciated!!!

This is one of the things that make stored procedures a maintenance nightmare.

It should be "USE", not "USING". It may be an idea to use separate connections or at least to execute the USE statement separately.

|||

Well, I changed it to Use (I should have seen that already) and still got nothing. I did try to do one stored proc at a time, but it kept catching on me. Any other ideas?

|||

Why not just run the stored procedure create/update within Query Analyser / SQL Server Management Studio?

alter identity property of a column to NOT FOR REPLICATION

i need to alter all foreign keys in my database and uncheck the
"Enforce relationship for replication" check box. Using the EM, I
extracted the code snippet below. unfortunately, when i run this test
from query analyzer, then go back into the EM, the box is still
checked.

can anyone tell me what i am missing? any advice on unsetting this
attribute globally would be appreciated!

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.CustomerCustomerDemo
DROP CONSTRAINT FK_CustomerCustomerDemo_Customers
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.CustomerCustomerDemo WITH NOCHECK ADD CONSTRAINT
FK_CustomerCustomerDemo_Customers FOREIGN KEY
(
CustomerID
) REFERENCES dbo.Customers
(
CustomerID
) NOT FOR REPLICATION

GO
COMMIT

thanks!!dayong (reedmb89@.yahoo.com) writes:
> i need to alter all foreign keys in my database and uncheck the
> "Enforce relationship for replication" check box. Using the EM, I
> extracted the code snippet below. unfortunately, when i run this test
> from query analyzer, then go back into the EM, the box is still
> checked.

It's not simply a refresh issue? I was not able to reproduce this, of
the simple reason that I was not able find where you poke with FKs in
Enterprise Manager. I prefer to work exclusively with SQL statements
for DDL statements.

You can use "sp_helpconstraint" in Query Analyzer to verify the status
of the constraint.

> can anyone tell me what i am missing? any advice on unsetting this
> attribute globally would be appreciated!

As long as you know which the foreign keys are, going like the code you
included should not be a problem.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||
first, obviously, i'm new to sql server. thanks for your advice so far.
you are correct, it was a refresh issue. unfortunately, i cannot find a
simple way to find the foreign keys that are set for replication. i
looked at the stored procedure you advised (sp_helpconstraint). it
appears to create a temp table and then query and join info and
eventually has a boolean value where if true is_for_replication and
false not_for_replication.

this code is greek to me in my early stages of sql server
administration. is there a simpler way to locate the keys and columns
that are set is_for_replication?

thanks in advance for any advice!!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Michael Reed (anonymous@.anonymous.com) writes:
> first, obviously, i'm new to sql server.

If you find out how get the commands that EM runs, and run them
in Query Analyzer, you have come a long way compared to many other
SQL Server newbies!

> this code is greek to me in my early stages of sql server
> administration. is there a simpler way to locate the keys and columns
> that are set is_for_replication?

This SELECT lists all foreign key constraints that are set for replication,
and the parent table:

select tbl = object_name(parent_obj), fk_name = name
from sysobjects
where xtype = 'F' and objectproperty(id, 'CnstIsNotRepl') = 0
order by tbl, fk_name

I don't know how many constraints you have. If you have only a handful,
you might be able to the rest manually. If you have hundreds of table,
you probably want a list of the columns in each FK. Since I'm lazy, and
I don't have a query ready for that right now, I don't include one. :-)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||
Erland

please re-post your last response. i can only see the summary. when i
click on the link, your post is nowhere to be found.

please re-post.

thanks!!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Alter Font of Data

Hello!

I have a matrix. Inside "Data", I have the follow code:

Fields!Name.Value & Chr(13) & Chr(10) & Fields!Group.Value

Is it possible place Font Bold only at Fields!Name.Value? How?

Thanks

Reporting Services does not support rich text or multiple formats inside text boxes. It is on our wishlist for a future release.

However, you could easily put another textbox in the cell with the Name.Value in a bold font and the other fields in another textbox. You could also add another column.

Alter Columns for Replication

I want to programmatically change the IDENTITY cols in my subscriber tables to NOT FOR REPLICATION. I want to use SQL code for this since there are many tables and cols and I want to be able to reverse the process.
I can't get the ALTER TABLE ALTER COLUMN syntax correct - it seems that I can't use this command to change the IDENTITY props, only field type, width, etc. Similarly I don't see any stored procs that do this. Can anyone provide the correct syntax or anoth
er solution?
TIA, P
Perry,
unfortunately this is not possible. EM makes it see that it is possible, but
behind the scenes it copies the contents of the table into a temporary table
with the required attribute then renames the table.
Regards,
Paul Ibison
|||Drat. I thought that might be the case.
Thanks for the help.
Perry
-- Paul Ibison wrote: --
Perry,
unfortunately this is not possible. EM makes it see that it is possible, but
behind the scenes it copies the contents of the table into a temporary table
with the required attribute then renames the table.
Regards,
Paul Ibison
|||Perry,
I've just thought of something - I seem to remember that Hilary has posted
up a backdoor method of doing this by editing the system table directly. I
can't vouch for this as I haven't used it, but you'll find it in one of
Hilary's posts. If you can't find it then please post up a message directly
for Hilary.
HTH,
Paul Ibison
|||try this. this is for a single table
http://groups.google.com/groups?hl=e...TNGP12.phx.gbl
this is for all tables
http://groups.google.com/groups?hl=e... NGP12.phx.gbl
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Perry Graham" <anonymous@.discussions.microsoft.com> wrote in message
news:4419796F-4968-4BAB-8B0E-12C104BDAE72@.microsoft.com...
> I want to programmatically change the IDENTITY cols in my subscriber
tables to NOT FOR REPLICATION. I want to use SQL code for this since there
are many tables and cols and I want to be able to reverse the process.
> I can't get the ALTER TABLE ALTER COLUMN syntax correct - it seems that I
can't use this command to change the IDENTITY props, only field type, width,
etc. Similarly I don't see any stored procs that do this. Can anyone provide
the correct syntax or another solution?
> TIA, P

alter column with PK / index

I have code that builds up a list of all tables requiring a column size
change and then executes the alter table command in dynamic sql via a cursor
.
problem is that sql server will not allow column to grow in size (char
datatype) if there are any PK or indexes on that column.
what is the best method of deploying my change?
do I need to check for all possible indexes upfront, drop, and then alter
table or is there a way to get around this issue. don't really want to have
to drop and recreate indexes due to time involved in rebuilding.
if I have to drop dependencies, can you assist with some code to detect and
build up the indexes again as I will have to do this afterwards.
many thanks.Hi
If this was in source code control your task would be a lot simpler!
Assuming that your PKs/FKs/Indexes are always the same then you could script
them and drop/apply then en-mass or tailor the scripts to do less work. As
you know, this may prolong the process and it is less likely to cope with an
y
anonomalies that may occur.
If you want to do less work start by looking at the sysindexkeys table
and/or INFORMATION_SCHEMA.TABLE_CONSTRAINTS
INFORMATION_SCHEMA.KEY_COLUMN_USAGE views
John
"sysbox27" wrote:

> I have code that builds up a list of all tables requiring a column size
> change and then executes the alter table command in dynamic sql via a curs
or.
> problem is that sql server will not allow column to grow in size (char
> datatype) if there are any PK or indexes on that column.
> what is the best method of deploying my change?
> do I need to check for all possible indexes upfront, drop, and then alter
> table or is there a way to get around this issue. don't really want to hav
e
> to drop and recreate indexes due to time involved in rebuilding.
> if I have to drop dependencies, can you assist with some code to detect an
d
> build up the indexes again as I will have to do this afterwards.
> many thanks.|||maybe you want to look at some true database change management...
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"sysbox27" wrote:

> I have code that builds up a list of all tables requiring a column size
> change and then executes the alter table command in dynamic sql via a curs
or.
> problem is that sql server will not allow column to grow in size (char
> datatype) if there are any PK or indexes on that column.
> what is the best method of deploying my change?
> do I need to check for all possible indexes upfront, drop, and then alter
> table or is there a way to get around this issue. don't really want to hav
e
> to drop and recreate indexes due to time involved in rebuilding.
> if I have to drop dependencies, can you assist with some code to detect an
d
> build up the indexes again as I will have to do this afterwards.
> many thanks.

alter column that has pk/index

Hi,
I have code that builds up a list of all tables requiring a column size
change and then executes the alter table command in dynamic sql via a cursor.
problem is that sql server will not allow column to grow in size (char
datatype) if there are any PK or indexes on that column.
what is the best method of deploying my change?
do I need to check for all possible indexes upfront, drop, and then alter
table or is there a way to get around this issue. don't really want to have
to drop and recreate indexes due to time involved in rebuilding.
many thanks.
maybe you want to look at some true database change management...
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"sysbox27" wrote:

> Hi,
> I have code that builds up a list of all tables requiring a column size
> change and then executes the alter table command in dynamic sql via a cursor.
> problem is that sql server will not allow column to grow in size (char
> datatype) if there are any PK or indexes on that column.
> what is the best method of deploying my change?
> do I need to check for all possible indexes upfront, drop, and then alter
> table or is there a way to get around this issue. don't really want to have
> to drop and recreate indexes due to time involved in rebuilding.
> many thanks.

alter column that has pk/index

Hi,
I have code that builds up a list of all tables requiring a column size
change and then executes the alter table command in dynamic sql via a cursor.
problem is that sql server will not allow column to grow in size (char
datatype) if there are any PK or indexes on that column.
what is the best method of deploying my change?
do I need to check for all possible indexes upfront, drop, and then alter
table or is there a way to get around this issue. don't really want to have
to drop and recreate indexes due to time involved in rebuilding.
many thanks.maybe you want to look at some true database change management...
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"sysbox27" wrote:
> Hi,
> I have code that builds up a list of all tables requiring a column size
> change and then executes the alter table command in dynamic sql via a cursor.
> problem is that sql server will not allow column to grow in size (char
> datatype) if there are any PK or indexes on that column.
> what is the best method of deploying my change?
> do I need to check for all possible indexes upfront, drop, and then alter
> table or is there a way to get around this issue. don't really want to have
> to drop and recreate indexes due to time involved in rebuilding.
> many thanks.

alter column that has pk/index

Hi,
I have code that builds up a list of all tables requiring a column size
change and then executes the alter table command in dynamic sql via a cursor
.
problem is that sql server will not allow column to grow in size (char
datatype) if there are any PK or indexes on that column.
what is the best method of deploying my change?
do I need to check for all possible indexes upfront, drop, and then alter
table or is there a way to get around this issue. don't really want to have
to drop and recreate indexes due to time involved in rebuilding.
many thanks.maybe you want to look at some true database change management...
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"sysbox27" wrote:

> Hi,
> I have code that builds up a list of all tables requiring a column size
> change and then executes the alter table command in dynamic sql via a curs
or.
> problem is that sql server will not allow column to grow in size (char
> datatype) if there are any PK or indexes on that column.
> what is the best method of deploying my change?
> do I need to check for all possible indexes upfront, drop, and then alter
> table or is there a way to get around this issue. don't really want to hav
e
> to drop and recreate indexes due to time involved in rebuilding.
> many thanks.

Alter column name from SQL?

How can you rename a column in a table (from c# code, preferrably from a SQL script command) without deleting the column and re-creating it with a new name?

Hello,

SQL Server contains a system stored procedure called SP_RENAME which can be used to rename user created object (tables, column, sp's, ...)

For more information, look at the BOL documentation on the subject http://msdn2.microsoft.com/en-us/library/ms188351.aspx (this is the 2005 version, but the system stored procedure also exist in previous version of SQL server)

Hope this helps,

|||Unfortunately this is not implemented in SQL Everywhere - you can only access the renaming APIs via OLE DB. You can always write a wrapper for .NET as I did.|||Guess I reacted to hasty. Thanks for the correction.|||

As of now we don't have direct method (e.g. SP support) for renaming column in SQL Server Everywhere Edition.

Thanks
Sachin

Alter Column as Identity

Hi All,
Is there a way to Alter an existing column and set it as an identity where t
he table contains data? TIA
A small sample code would be nice.No, you cannot alter an existing column to have the identity property. Your
options are limited to recreating the table or create another column as an
identity column.
Anith|||It's funny that you can manually set the column as an identity, but you can'
t programmatically change it.|||Actually, when you do it using EM (manually), a series of steps happen
behind the scenes : a new table is created with the identity column, the
data is copied, old one is dropped & the new table is renamed. You can see
the series of operations, by clicking on the save change script button on
the design table interface.
Anith