changing the types of particular columns in particular table in the database

hi,
i have more than 300 tables in the database. Out of that tables some table has column whose type is "char".I want to change only those tables' column to the type "nvarchar".
Is it possible to make the modification all at a once?
since i am changing each and every table , this takes a lots of time.

thanks in advance,
by
singam

Hi,

first of all there is no built in function for this. YOu have to do that one by one in the script, there is sure a chance to do this automagically but I am no fan of a full automatic procedure to modify the schema of a bunch of tables.

You can (as I always do ) let the SQL Server create the script for you and execute this on your own in bacthes for better error handling (as you didn′t point out if error handling is applicable for you like in SQL Server 2005)

SELECT 'ALTER TABLE ' + C.TABLE_NAME +' ALTER COLUMN ' + C.COLUMN_NAME + ' VARCHAR(50)' --New data type and length
FROM INFORMATION_SCHEMA.Columns C
INNER JOIN INFORMATION_SCHEMA.Tables T
ON
T.TABLE_CATALOG = C.TABLE_CATALOG AND
T.TABLE_SCHEMA = C.TABLE_SCHEMA AND
T.TABLE_NAME = C.TABLE_NAME
WHERE
DATA_TYPE = 'NVARCHAR' AND --Old Type
CHARACTER_MAXIMUM_LENGTH = 50 AND --Old lenght
TABLE_TYPE = 'BASE TABLE'

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Changing the DataType of a Coluumn in an In-Memory Dataset

Here is the issue. I have ReadOnly Access to a database. All of the Columns are set to NVARCHAR(1000) by default. I cannot change them. I want to load the DataSet into memory and change the DataType of the columns from NVARCHAR(1000) to INT(4). The data is in integer (i.e. 4,5,123) format (but stored as a string), but is coming across as strings. The charting software I am using won't implicitly convert these Strings to Int or Double. How can I change an entire column to Int?

You need ANSI SQL ALTER COLUMN, I am not sure if you can do that for read only. Try the url below for your options. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_aa-az_3ied.asp

Kind regards,

Gift Peddie

|||Or, you could cast in in the SELECT statement. Like so: SELECT someColumn, otherColumn, CAST(thisColumn AS INT) AS thisColumnFROM yourTable...
But really, if you have an NVARCHAR(1000) column that's nothing butintegers, you really *should* change the underlying column in the tableto be the proper type.
|||I know, but this DB is the result of a portal tool, which I have no control over. I will try Casting the column to int on the select statement. Thanks.

Changing the datatype of a column which is part of a PK

Hi All
I have a table made up of 2 columns as the PK. I am trying to write a script
that changes the datatype of one of the columns which is part of the primary
key. This is what i have tried but i seem not to be getting anywhere:
ALTER TABLE TB1 DROP CONSTRAINT PK_TB1
ALTER COLUMN ACCOUNT VARCHAR(20) NOT NULL
Is there a way to do this? I tried to drop the PKs and then do the changes
and then recreate the PK again. All this gave me errors. Thank you in
advance.> Is there a way to do this? I tried to drop the PKs and then do the changes
> and then recreate the PK again. All this gave me errors.
What does "tried" mean? What errors?
This is the way you do it. You drop the constraints (both primary *and*
foreign key), you change the column, you re-apply the constraints.|||Thank you. It worked.
"Aaron Bertrand [SQL Server MVP]" wrote:

> What does "tried" mean? What errors?
> This is the way you do it. You drop the constraints (both primary *and*
> foreign key), you change the column, you re-apply the constraints.
>
>

Changing the columns of a SELECT statement.

I have been "noramlizing" some of the tables that we have been using and I
ran into a problem in one of our stored procedures.
It used to be (with the old table) that you could do something like:
SELECT A, B FROM OldTable
The stored procedure essentially does this.
Now with the new table(s) it is something like:
SELECT *
FROM Table
LEFT OUTER JOIN Attributes ON Attributes.AttributesId = Table.AttributesId
There is in the Attributes table an AttibutesTypeId and an AttributesValue.
So I get 'A' only when AttributesTypeId = 1 and 'B' when AttributesTypeId =
2. The question is how do I return A and B from the stored procedure given
this new table structure? I am tempted to create a temporary table and use a
cursor to move row by row through the table, but there must be a more
efficient way.
Thank you.Kevin
I need a bit more clarification, if you don't mind
You used to have One table called "Big Daddy" now there are 3 tables I
presume.
Table, Attributes and Attributetype
if that assumption is correct, let us know how the columns(old table) have
been distributed in the normalized structure(have the static columns A and B
been converted to AttributeType) , there is probably a way to do it without
the cursor, but right now it is very hard to understand your problem.
Also can you give me the query again that you are trying to run from the OLD
table.
In the query you have specified , it's confusing when you are say the
Attribute has AttributeTypeId but you join it to Table using AttributesId
column, so seems like there is a 3rd table called AttributesType.
it might be just simpler to post the DDL , you don't mind doing it.
Rakesh Ajwani
MCSD, MCSD.NET
"Kevin Burton" wrote:

> I have been "noramlizing" some of the tables that we have been using and I
> ran into a problem in one of our stored procedures.
> It used to be (with the old table) that you could do something like:
> SELECT A, B FROM OldTable
> The stored procedure essentially does this.
> Now with the new table(s) it is something like:
> SELECT *
> FROM Table
> LEFT OUTER JOIN Attributes ON Attributes.AttributesId = Table.AttributesId
> There is in the Attributes table an AttibutesTypeId and an AttributesValue
.
> So I get 'A' only when AttributesTypeId = 1 and 'B' when AttributesTypeId
=
> 2. The question is how do I return A and B from the stored procedure given
> this new table structure? I am tempted to create a temporary table and use
a
> cursor to move row by row through the table, but there must be a more
> efficient way.
> Thank you.
>|||Find the moron that did this to you and kill him. This is called a EAV
design and it is totally wrong in an RDBMS. It confuses data and meta
data and makes your most basic queries run several orders of magnitude
slower. But the lack of speed does not matter; you will have no data
integrity so you cannot trust the answers anyway.
You might also want to get any book on data modeling. Then you will
know that there cannot be such a creature is a "type_id" -- the data
element is either a type with a known code or it is an identifier of
some kind of entity.
I am on a borrowed computer in Brazil, so I cannot cut & paste the
painful details; you can Google old postings on EAV, OTLT and MUCK as
bad design decisions frequently made by non-SQL programmers.|||Kevin Burton wrote:
> I have been "noramlizing" some of the tables that we have been using and I
> ran into a problem in one of our stored procedures.
> It used to be (with the old table) that you could do something like:
> SELECT A, B FROM OldTable
> The stored procedure essentially does this.
> Now with the new table(s) it is something like:
> SELECT *
> FROM Table
> LEFT OUTER JOIN Attributes ON Attributes.AttributesId = Table.AttributesId
> There is in the Attributes table an AttibutesTypeId and an AttributesValue
.
> So I get 'A' only when AttributesTypeId = 1 and 'B' when AttributesTypeId
=
> 2. The question is how do I return A and B from the stored procedure given
> this new table structure? I am tempted to create a temporary table and use
a
> cursor to move row by row through the table, but there must be a more
> efficient way.
> Thank you.
That is what you call "normalization"?!! Go lookup normalization in a
good book is my advice. This is a design problem of your own making I'm
afraid.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thank you for your reply. In an effort to make things simpler by removing
detail I probably confused things.
The OldTable had columns that looked like:
Id, A, B, C, D
A, B, C, D are types of attributes for the node of Id. We are adding new
types and in the future even more will be added. So rather than keep adding
columns to the OldTable a new table was thought up like:
Id, AttributeId
The AttributeId column is an index to the attributes of Id. It indexes
another new table where the attributes are stored called Attributes. It has
columns like:
AttributeId, Type, Value
So a human will be able to tell what 'Type' is there is a third table
AttributeTypes that has columns like:
Type, Description
So the OldTable would have a row like:
1000, 'A Attribute', 'B Attribute', 'C Attribute', 'D Attribute'
This would translate to the NewTable having content like:
1000, 10
1000, 20
1000, 30
1000, 40
The Attributes table would have content like:
10, 1, 'A Attribute'
20, 2, 'B Attribute'
30, 3, 'C Attribute'
40, 4, 'D Attribute'
The contents of the "lookup" table (AttributeTypes) would look like:
1, 'A'
2, 'B'
3, 'C'
4, 'D'
So to make the query return the same result set as before I am trying:
SELECT Id,
CASE Attributes,Type
WHEN 1 THEN Attributes.Value
ELSE NULL
END AS A,
CASE Attributes,Type
WHEN 2 THEN Attributes.Value
ELSE NULL
END AS B,
CASE Attributes,Type
WHEN 3 THEN Attributes.Value
ELSE NULL
END AS C,
CASE Attributes,Type
WHEN 4 THEN Attributes.Value
ELSE NULL
END AS D
FROM NewTable
INNER JOIN Attributes ON Attributes.AttributeId = NewTable.AttributeId
ORDER BY Id
The problem is that this returns
1000, 'A Attribute', NULL, NULL, NULL
1000, NULL, 'B Attribute', NULL, NULL
1000, NULL, NULL, 'C Attribute',NULL
1000, NULL, NULL, NULL, 'D Attribute'
So I either need to merge the above (preferred) so that it returns just one
row as before:
1000, 'A Attribute', 'B Attribute', 'C Attribute', 'D Attribute'
Or I need to be prepared to add a column any time a new attribute is
speicfied.
Suggestions?
Thank you.
Kevin
"Rakesh Ajwani" wrote:
[vbcol=seagreen]
> Kevin
> I need a bit more clarification, if you don't mind
> You used to have One table called "Big Daddy" now there are 3 tables I
> presume.
> Table, Attributes and Attributetype
> if that assumption is correct, let us know how the columns(old table) have
> been distributed in the normalized structure(have the static columns A and
B
> been converted to AttributeType) , there is probably a way to do it withou
t
> the cursor, but right now it is very hard to understand your problem.
> Also can you give me the query again that you are trying to run from the O
LD
> table.
> In the query you have specified , it's confusing when you are say the
> Attribute has AttributeTypeId but you join it to Table using AttributesId
> column, so seems like there is a 3rd table called AttributesType.
> it might be just simpler to post the DDL , you don't mind doing it.
> --
> Rakesh Ajwani
> MCSD, MCSD.NET
>
> "Kevin Burton" wrote:
>

Changing the columns of a SELECT statement.

I have been "noramlizing" some of the tables that we have been using and I
ran into a problem in one of our stored procedures.
It used to be (with the old table) that you could do something like:
SELECT A, B FROM OldTable
The stored procedure essentially does this.
Now with the new table(s) it is something like:
SELECT *
FROM Table
LEFT OUTER JOIN Attributes ON Attributes.AttributesId = Table.AttributesId
There is in the Attributes table an AttibutesTypeId and an AttributesValue.
So I get 'A' only when AttributesTypeId = 1 and 'B' when AttributesTypeId = 2. The question is how do I return A and B from the stored procedure given
this new table structure? I am tempted to create a temporary table and use a
cursor to move row by row through the table, but there must be a more
efficient way.
Thank you.Kevin
I need a bit more clarification, if you don't mind
You used to have One table called "Big Daddy" now there are 3 tables I
presume.
Table, Attributes and Attributetype
if that assumption is correct, let us know how the columns(old table) have
been distributed in the normalized structure(have the static columns A and B
been converted to AttributeType) , there is probably a way to do it without
the cursor, but right now it is very hard to understand your problem.
Also can you give me the query again that you are trying to run from the OLD
table.
In the query you have specified , it's confusing when you are say the
Attribute has AttributeTypeId but you join it to Table using AttributesId
column, so seems like there is a 3rd table called AttributesType.
it might be just simpler to post the DDL , you don't mind doing it.
--
Rakesh Ajwani
MCSD, MCSD.NET
"Kevin Burton" wrote:
> I have been "noramlizing" some of the tables that we have been using and I
> ran into a problem in one of our stored procedures.
> It used to be (with the old table) that you could do something like:
> SELECT A, B FROM OldTable
> The stored procedure essentially does this.
> Now with the new table(s) it is something like:
> SELECT *
> FROM Table
> LEFT OUTER JOIN Attributes ON Attributes.AttributesId = Table.AttributesId
> There is in the Attributes table an AttibutesTypeId and an AttributesValue.
> So I get 'A' only when AttributesTypeId = 1 and 'B' when AttributesTypeId => 2. The question is how do I return A and B from the stored procedure given
> this new table structure? I am tempted to create a temporary table and use a
> cursor to move row by row through the table, but there must be a more
> efficient way.
> Thank you.
>|||Find the moron that did this to you and kill him. This is called a EAV
design and it is totally wrong in an RDBMS. It confuses data and meta
data and makes your most basic queries run several orders of magnitude
slower. But the lack of speed does not matter; you will have no data
integrity so you cannot trust the answers anyway.
You might also want to get any book on data modeling. Then you will
know that there cannot be such a creature is a "type_id" -- the data
element is either a type with a known code or it is an identifier of
some kind of entity.
I am on a borrowed computer in Brazil, so I cannot cut & paste the
painful details; you can Google old postings on EAV, OTLT and MUCK as
bad design decisions frequently made by non-SQL programmers.|||Kevin Burton wrote:
> I have been "noramlizing" some of the tables that we have been using and I
> ran into a problem in one of our stored procedures.
> It used to be (with the old table) that you could do something like:
> SELECT A, B FROM OldTable
> The stored procedure essentially does this.
> Now with the new table(s) it is something like:
> SELECT *
> FROM Table
> LEFT OUTER JOIN Attributes ON Attributes.AttributesId = Table.AttributesId
> There is in the Attributes table an AttibutesTypeId and an AttributesValue.
> So I get 'A' only when AttributesTypeId = 1 and 'B' when AttributesTypeId => 2. The question is how do I return A and B from the stored procedure given
> this new table structure? I am tempted to create a temporary table and use a
> cursor to move row by row through the table, but there must be a more
> efficient way.
> Thank you.
That is what you call "normalization"?!! Go lookup normalization in a
good book is my advice. This is a design problem of your own making I'm
afraid.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thank you for your reply. In an effort to make things simpler by removing
detail I probably confused things.
The OldTable had columns that looked like:
Id, A, B, C, D
A, B, C, D are types of attributes for the node of Id. We are adding new
types and in the future even more will be added. So rather than keep adding
columns to the OldTable a new table was thought up like:
Id, AttributeId
The AttributeId column is an index to the attributes of Id. It indexes
another new table where the attributes are stored called Attributes. It has
columns like:
AttributeId, Type, Value
So a human will be able to tell what 'Type' is there is a third table
AttributeTypes that has columns like:
Type, Description
So the OldTable would have a row like:
1000, 'A Attribute', 'B Attribute', 'C Attribute', 'D Attribute'
This would translate to the NewTable having content like:
1000, 10
1000, 20
1000, 30
1000, 40
The Attributes table would have content like:
10, 1, 'A Attribute'
20, 2, 'B Attribute'
30, 3, 'C Attribute'
40, 4, 'D Attribute'
The contents of the "lookup" table (AttributeTypes) would look like:
1, 'A'
2, 'B'
3, 'C'
4, 'D'
So to make the query return the same result set as before I am trying:
SELECT Id,
CASE Attributes,Type
WHEN 1 THEN Attributes.Value
ELSE NULL
END AS A,
CASE Attributes,Type
WHEN 2 THEN Attributes.Value
ELSE NULL
END AS B,
CASE Attributes,Type
WHEN 3 THEN Attributes.Value
ELSE NULL
END AS C,
CASE Attributes,Type
WHEN 4 THEN Attributes.Value
ELSE NULL
END AS D
FROM NewTable
INNER JOIN Attributes ON Attributes.AttributeId = NewTable.AttributeId
ORDER BY Id
The problem is that this returns
1000, 'A Attribute', NULL, NULL, NULL
1000, NULL, 'B Attribute', NULL, NULL
1000, NULL, NULL, 'C Attribute',NULL
1000, NULL, NULL, NULL, 'D Attribute'
So I either need to merge the above (preferred) so that it returns just one
row as before:
1000, 'A Attribute', 'B Attribute', 'C Attribute', 'D Attribute'
Or I need to be prepared to add a column any time a new attribute is
speicfied.
Suggestions?
Thank you.
Kevin
"Rakesh Ajwani" wrote:
> Kevin
> I need a bit more clarification, if you don't mind
> You used to have One table called "Big Daddy" now there are 3 tables I
> presume.
> Table, Attributes and Attributetype
> if that assumption is correct, let us know how the columns(old table) have
> been distributed in the normalized structure(have the static columns A and B
> been converted to AttributeType) , there is probably a way to do it without
> the cursor, but right now it is very hard to understand your problem.
> Also can you give me the query again that you are trying to run from the OLD
> table.
> In the query you have specified , it's confusing when you are say the
> Attribute has AttributeTypeId but you join it to Table using AttributesId
> column, so seems like there is a 3rd table called AttributesType.
> it might be just simpler to post the DDL , you don't mind doing it.
> --
> Rakesh Ajwani
> MCSD, MCSD.NET
>
> "Kevin Burton" wrote:
> > I have been "noramlizing" some of the tables that we have been using and I
> > ran into a problem in one of our stored procedures.
> >
> > It used to be (with the old table) that you could do something like:
> >
> > SELECT A, B FROM OldTable
> >
> > The stored procedure essentially does this.
> >
> > Now with the new table(s) it is something like:
> >
> > SELECT *
> > FROM Table
> > LEFT OUTER JOIN Attributes ON Attributes.AttributesId = Table.AttributesId
> >
> > There is in the Attributes table an AttibutesTypeId and an AttributesValue.
> > So I get 'A' only when AttributesTypeId = 1 and 'B' when AttributesTypeId => > 2. The question is how do I return A and B from the stored procedure given
> > this new table structure? I am tempted to create a temporary table and use a
> > cursor to move row by row through the table, but there must be a more
> > efficient way.
> >
> > Thank you.
> >

Changing the column color depending on a dynamic date

Hi, I have a series of columns which I need to be able to change the
background color of dynamically depending on a date which is computed
dynamically when the report is created.
I a using a textbox to display the dynamically calculated date
(e.g. =DateAdd(DateInterval.Day,5,Today).ToString("dd-MMM") )
But I cannot seem to reference this textbox object at runtime.
I also need to know how to get the actual dayname given the calculated
date above.
Any ideas about how I can do this'
Thanks
MarkusOn Apr 11, 7:19 pm, Markus...@.gmail.com wrote:
> Hi, I have a series of columns which I need to be able to change the
> background color of dynamically depending on a date which is computed
> dynamically when the report is created.
> I a using a textbox to display the dynamically calculated date
> (e.g. =DateAdd(DateInterval.Day,5,Today).ToString("dd-MMM") )
> But I cannot seem to reference this textbox object at runtime.
> I also need to know how to get the actual dayname given the calculated
> date above.
> Any ideas about how I can do this'
> Thanks
> Markus
You should use something like this in a new dataset that populates a
hidden report parameter:
SELECT DATENAME(DW, GETDATE())
Then reference the hidden parameter as part of the table's column
background property. Something like this should work:
=iif(Parameters!HiddenParameterName.Value = "Monday", "Red", "White")
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||On Apr 12, 1:07 pm, "EMartinez" <emartinez...@.gmail.com> wrote:
> On Apr 11, 7:19 pm, Markus...@.gmail.com wrote:
>
>
> > Hi, I have a series of columns which I need to be able to change the
> > background color of dynamically depending on a date which is computed
> > dynamically when the report is created.
> > I a using a textbox to display the dynamically calculated date
> > (e.g. =DateAdd(DateInterval.Day,5,Today).ToString("dd-MMM") )
> > But I cannot seem to reference this textbox object at runtime.
> > I also need to know how to get the actual dayname given the calculated
> > date above.
> > Any ideas about how I can do this'
> > Thanks
> > Markus
> You should use something like this in a new dataset that populates a
> hidden report parameter:
> SELECT DATENAME(DW, GETDATE())
> Then reference the hidden parameter as part of the table's column
> background property. Something like this should work:
> =iif(Parameters!HiddenParameterName.Value = "Monday", "Red", "White")
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant- Hide quoted text -
> - Show quoted text -
Thanks Enrique, I will give that a shot
Cheers
Markus|||On Apr 12, 5:00 pm, Markus...@.gmail.com wrote:
> On Apr 12, 1:07 pm, "EMartinez" <emartinez...@.gmail.com> wrote:
>
> > On Apr 11, 7:19 pm, Markus...@.gmail.com wrote:
> > > Hi, I have a series of columns which I need to be able to change the
> > > background color of dynamically depending on a date which is computed
> > > dynamically when the report is created.
> > > I a using a textbox to display the dynamically calculated date
> > > (e.g. =DateAdd(DateInterval.Day,5,Today).ToString("dd-MMM") )
> > > But I cannot seem to reference this textbox object at runtime.
> > > I also need to know how to get the actual dayname given the calculated
> > > date above.
> > > Any ideas about how I can do this'
> > > Thanks
> > > Markus
> > You should use something like this in a new dataset that populates a
> > hidden report parameter:
> > SELECT DATENAME(DW, GETDATE())
> > Then reference the hidden parameter as part of the table's column
> > background property. Something like this should work:
> > =iif(Parameters!HiddenParameterName.Value = "Monday", "Red", "White")
> > Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant- Hide quoted text -
> > - Show quoted text -
> Thanks Enrique, I will give that a shot
> Cheers
> Markus
You're welcome. Let me know if you need further assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

Changing Order of columns in a table

Is there any way to change the order (position) of columns of a table ,
without the need of dropping and recreating the table itself !?
I found the 'colid' field of the 'syscolumn' system table: by changing the
colid of each column I got the desired result, unfortunately if I have to
create indexes ih the above columns later on I get the error
Location: record.cpp: 759
Expression: pbind-> fcheckfornull ()
spID: errore 56 o 61
process ID 2976 o 2452
ID code -2147467259
as a side effect.
Moreover , I saw that in the 'syscolumns' system table , the change in the
'colid' field was an insert rather than an update...
Thanks,
Massimo.If you monkey around with the data in the sys... tables you can screw up
your database permanently.
I think you should really drop the table and re-create it.
You could also create a view with the columns in the required order.
"news" <massimo.facchi@.getronics.com> wrote in message
news:eqacvBkjDHA.2656@.TK2MSFTNGP10.phx.gbl...
> Is there any way to change the order (position) of columns of a table ,
> without the need of dropping and recreating the table itself !?
> I found the 'colid' field of the 'syscolumn' system table: by changing the
> colid of each column I got the desired result, unfortunately if I have to
> create indexes ih the above columns later on I get the error
> Location: record.cpp: 759
> Expression: pbind-> fcheckfornull ()
> spID: errore 56 o 61
> process ID 2976 o 2452
> ID code -2147467259
> as a side effect.
>
> Moreover , I saw that in the 'syscolumns' system table , the change in the
> 'colid' field was an insert rather than an update...
> Thanks,
>
> Massimo.
>
>
>|||I used to worry about the order of columns when I first started using sql,
but I quickly found I was wasting my time... As the previous poster said,
messing with system tables is not a good way to go... The order of the
columns physically in the record is different than the order you put in the
create table...
Unless you have some huge, overriding reason don't worry about column
order... Otherwise, drop and re-create the table.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"news" <massimo.facchi@.getronics.com> wrote in message
news:eqacvBkjDHA.2656@.TK2MSFTNGP10.phx.gbl...
> Is there any way to change the order (position) of columns of a table ,
> without the need of dropping and recreating the table itself !?
> I found the 'colid' field of the 'syscolumn' system table: by changing the
> colid of each column I got the desired result, unfortunately if I have to
> create indexes ih the above columns later on I get the error
> Location: record.cpp: 759
> Expression: pbind-> fcheckfornull ()
> spID: errore 56 o 61
> process ID 2976 o 2452
> ID code -2147467259
> as a side effect.
>
> Moreover , I saw that in the 'syscolumns' system table , the change in the
> 'colid' field was an insert rather than an update...
> Thanks,
>
> Massimo.
>
>
>

Changing order of columns at run time

Here's the scenario:
I want to be able to create a report (typically these reports are
"table like" reports ... in other words, several columns with a list
of data underneath) and I want to be able to re-order the columns (not
the rows) in these reports at runtime.
So, for instance, if the "report design" is as follows:
COLUMNA COLUMNB COLUMNC
... data goes here ...
... I'd like to be able to pass a parameter in to the report that may
be, for instance, "COLUMNC,COLUMNB,COLUMNA" ... and report columns
would re-order, like follows:
COLUMNC COLUMNB COLUMNA
... data goes here ...
... I don't really care what report designer object is used (Table,
Matrix, etc) ... but I don't yet see a good way to do this. It seems
like the Matrix object might be able to do this via the ColumnGroups
"Sorting" tab ... but I don't seem to be able to come up with an
expression that can make this occur.
Any help is greatly appreciated.
Thanks,
RichardOn Apr 27, 9:39 am, Richard.G...@.Pa-Tech.Com wrote:
> Here's the scenario:
> I want to be able to create a report (typically these reports are
> "table like" reports ... in other words, several columns with a list
> of data underneath) and I want to be able to re-order the columns (not
> the rows) in these reports at runtime.
> So, for instance, if the "report design" is as follows:
> COLUMNA COLUMNB COLUMNC
> ... data goes here ...
> ... I'd like to be able to pass a parameter in to the report that may
> be, for instance, "COLUMNC,COLUMNB,COLUMNA" ... and report columns
> would re-order, like follows:
> COLUMNC COLUMNB COLUMNA
> ... data goes here ...
> ... I don't really care what report designer object is used (Table,
> Matrix, etc) ... but I don't yet see a good way to do this. It seems
> like the Matrix object might be able to do this via the ColumnGroups
> "Sorting" tab ... but I don't seem to be able to come up with an
> expression that can make this occur.
> Any help is greatly appreciated.
> Thanks,
> Richard
If you are using a matrix report, you can control the column layout
(assuming you know the column names prior to runtime) by adding spaces
in front of the names of the columns (like values in the pivot column)
and ordering/sorting by ascending order. If you are using a table
control, you can allow the user to select a sort order (asc/desc) in
the report and then set the columns in the returned resultset
accordingly. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Just Like the report was built by Report Builder..
when click title of column ,the order will be change,right?
how to do it in vs2005?
"EMartinez" <emartinez.pr1@.gmail.com>
':1177733247.239100.15830@.n59g2000hsh.googlegroups.com...
> On Apr 27, 9:39 am, Richard.G...@.Pa-Tech.Com wrote:
>> Here's the scenario:
>> I want to be able to create a report (typically these reports are
>> "table like" reports ... in other words, several columns with a list
>> of data underneath) and I want to be able to re-order the columns (not
>> the rows) in these reports at runtime.
>> So, for instance, if the "report design" is as follows:
>> COLUMNA COLUMNB COLUMNC
>> ... data goes here ...
>> ... I'd like to be able to pass a parameter in to the report that may
>> be, for instance, "COLUMNC,COLUMNB,COLUMNA" ... and report columns
>> would re-order, like follows:
>> COLUMNC COLUMNB COLUMNA
>> ... data goes here ...
>> ... I don't really care what report designer object is used (Table,
>> Matrix, etc) ... but I don't yet see a good way to do this. It seems
>> like the Matrix object might be able to do this via the ColumnGroups
>> "Sorting" tab ... but I don't seem to be able to come up with an
>> expression that can make this occur.
>> Any help is greatly appreciated.
>> Thanks,
>> Richard
>
> If you are using a matrix report, you can control the column layout
> (assuming you know the column names prior to runtime) by adding spaces
> in front of the names of the columns (like values in the pivot column)
> and ordering/sorting by ascending order. If you are using a table
> control, you can allow the user to select a sort order (asc/desc) in
> the report and then set the columns in the returned resultset
> accordingly. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||That's called interactive sort. click on the textbox or table textbox
properties and select the interactive sort tab and set your values.
Amarnath
"renhanyue" wrote:
> Just Like the report was built by Report Builder..
> when click title of column ,the order will be change,right?
> how to do it in vs2005?
> "EMartinez" <emartinez.pr1@.gmail.com>
> ':1177733247.239100.15830@.n59g2000hsh.googlegroups.com...
> > On Apr 27, 9:39 am, Richard.G...@.Pa-Tech.Com wrote:
> >> Here's the scenario:
> >>
> >> I want to be able to create a report (typically these reports are
> >> "table like" reports ... in other words, several columns with a list
> >> of data underneath) and I want to be able to re-order the columns (not
> >> the rows) in these reports at runtime.
> >>
> >> So, for instance, if the "report design" is as follows:
> >>
> >> COLUMNA COLUMNB COLUMNC
> >> ... data goes here ...
> >>
> >> ... I'd like to be able to pass a parameter in to the report that may
> >> be, for instance, "COLUMNC,COLUMNB,COLUMNA" ... and report columns
> >> would re-order, like follows:
> >>
> >> COLUMNC COLUMNB COLUMNA
> >> ... data goes here ...
> >>
> >> ... I don't really care what report designer object is used (Table,
> >> Matrix, etc) ... but I don't yet see a good way to do this. It seems
> >> like the Matrix object might be able to do this via the ColumnGroups
> >> "Sorting" tab ... but I don't seem to be able to come up with an
> >> expression that can make this occur.
> >>
> >> Any help is greatly appreciated.
> >>
> >> Thanks,
> >> Richard
> >
> >
> > If you are using a matrix report, you can control the column layout
> > (assuming you know the column names prior to runtime) by adding spaces
> > in front of the names of the columns (like values in the pivot column)
> > and ordering/sorting by ascending order. If you are using a table
> > control, you can allow the user to select a sort order (asc/desc) in
> > the report and then set the columns in the returned resultset
> > accordingly. Hope this helps.
> >
> > Regards,
> >
> > Enrique Martinez
> > Sr. Software Consultant
> >
>
>|||The issue is that I'm trying to change is the order of the columns
themselves (left to right) ... not the data "within" the column (which
would be the "top to bottom").
Normally, these reports would be simple "table" reports ... the
"extra" feature that I need to give to my users is the ability specify
the order of the columns (left to right)
To further clarify, if the report where a simple SQL statement, the
report designer might specify:
SELECT A, B, C FROM SOMETABLE
... but I want to give the report "user" the ability to say
SELECT C, B, A FROM SOMETABLE

Changing Object Explorer Details Window

is it possible to change the columns that display in the Object Explorer
Details Window? I'd like to be able see some Extended Properties in this
window.
TIA
Dean
Nope...sorry but that's not an option.
-Sue
On Fri, 10 Aug 2007 09:51:26 -0400, "Dean"
<deanl144@.hotmail.com.nospam> wrote:

>is it possible to change the columns that display in the Object Explorer
>Details Window? I'd like to be able see some Extended Properties in this
>window.
>TIA
>Dean
>

Changing NULL to zero

Greetings folks,
I have a dilemma that I thought was going to be a slam dunk. I have a
table with several integer and money columns that contain about 50%
null values. I need to sum them, and have the nulls treated as zero.
Secondly, I need to insert all of the values into another table. If
there happens to be a null in the source columns, it needs to become a
zero in the destination. (I know someone is going to fuss about the
storing of calculated values. Not my choice, I don't get a say-so).
The tables have millions of records, and there are roughly 40 int and
money columns. So I'm looking for a solution that will perform well,
and will not require me to update those 40 columns individually. Any
suggestions?
thanks,
CL--isnull will return the column if it is not null, and the second value (in
this case 0) if it is null
select isnull(Column1, 0) from table
or
--Coalesce returns the first non-null value in the list. I think if you
have only one value you want to use isnull, but here is is anyway...
select coalesce(column1,0) from table
"CL" <clhawkins74@.yahoo.com> wrote in message
news:1137790173.259283.202260@.g49g2000cwa.googlegroups.com...
> Greetings folks,
> I have a dilemma that I thought was going to be a slam dunk. I have a
> table with several integer and money columns that contain about 50%
> null values. I need to sum them, and have the nulls treated as zero.
> Secondly, I need to insert all of the values into another table. If
> there happens to be a null in the source columns, it needs to become a
> zero in the destination. (I know someone is going to fuss about the
> storing of calculated values. Not my choice, I don't get a say-so).
> The tables have millions of records, and there are roughly 40 int and
> money columns. So I'm looking for a solution that will perform well,
> and will not require me to update those 40 columns individually. Any
> suggestions?
> thanks,
> CL
>

changing datetime to string?

As in the database, i made a few columns in the forum table.
date(datetime) 15/09/2004 3.35PM
author(char) John

Select datetime + '<br>' + author from forum

it claimed there is an error on this datetime.

By right, the result should be

15/09/2004 3.35PM
John

can anyone help me how i could get the result out without having to change date's properties in the sql database?

Will be greatly appreciated if help gets ard.It looks like that select statement is trying to perform a math function. You should select the fields individually, then format them appropriately in your vb/cs code.

select datetime, author from forum

In your code, you will now have two fields exposed, and you can concatenate them if you wish.|||The TSQL CONVERT function can do that for you or Google for using string.format and use a date format code to convert it to the type of string you want it to be from within your VB code.