Changing column names in SSMS for SQL Server 2005

I changed the name of a column in the Summary pane of SSMS. I was a bit surprised (but pleased) to see that I could make the change there.

When I tried to use the new column name in a Query, I received the error message, "column not found."

When I used "modify" in the Object Explorer pane of SSMS I was able to see that "(int, not null)" had been appended to the column name in the table.

I don't know if this is a BUG, or an undocumented feature.

Have others here run into this? I am using SS2005, with SP1.

Dan

"int, not null" is not appended to a column name, it is part of the syntax for defining a column. If you can see the column in Object Explorer after you added and saved it you should be able to refer to it in a query.

Service Pack 2 is out ...

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/servicepacks/sp2.mspx

|||

lkh,

Yes, I am sorry to say that "(int, not null)" WAS appended to the column name.

When I went into "Modify" in Object Explorer, I found the column name as

[New_name (int, not null)]. The column characteristics were "(int, not null)".

The square brackets were placed there as a result of my editing the name in the Summary pane.

So yes, "(int, not null)" was part of the new name created when I edited the name in the Summary pane.

Dan

Changing column data type to Unicode data type

Use databases a bit, but new to SQL Server. We just want to change a column of existing SQL Server 2005 data from a string data type to one of the UNiCODE data types, such as DT_WSTR or DT.NTEXT (such as one can use for various data mining tasks, etc.). It seems to do this one needs to "the data conversion transformation editor". To use that one has to have a package and a project?

Does any one have a full script or set of steps to do the full set of steps for what should be a simple task? This would be a great example for BOL, but each atomistic bit of BOL refers to another, and one gets lost in the circle when a complete example is needed for fundamantal housekeeping tasks.

Yes you need a project and a package. To get started with SSIS projects and packages, you should run through the SSIS tutorial. See http://msdn2.microsoft.com/en-us/library/ms170419(SQL.90).aspx - the steps of the tutorial take you through the project and package creation and into working with data flow

When you need to convert data, this BOL entry gives the steps for using the Data Conversion Component. http://msdn2.microsoft.com/en-us/library/ms140321.aspx

Donald

|||

Fairly new to SQL Server 2005, so please excuse a more basic question. I very much appreciate any further hint or clarification that you can provide!

It is sometime at least appears rather unclear to quickly see the necessary "big picture" in SQL 2005! For example, when is it best to use "graphical tools" (with projects and packages), or, can one use simple Transact- SQL statements to perhaps best perform the same (relatively simple) operation? As here, for example, to change column data type, could I not use the SQL commands ALTER TABLE, and/or, say CAST and CONVERT? Do these transforms work into the Unicode data types, such as DT_WSTR?

I very much appreciate any further hint or thought!

|||

J. Lewis wrote:

Fairly new to SQL Server 2005, so please excuse a more basic question. I very much appreciate any further hint or clarification that you can provide!

It is sometime at least appears rather unclear to quickly see the necessary "big picture" in SQL 2005! For example, when is it best to use "graphical tools" (with projects and packages), or, can one use simple Transact- SQL statements to perhaps best perform the same (relatively simple) operation? As here, for example, to change column data type, could I not use the SQL commands ALTER TABLE, and/or, say CAST and CONVERT? Do these transforms work into the Unicode data types, such as DT_WSTR?

I very much appreciate any further hint or thought!

J,

There appears to be some confusion between SQL Server Database Engine and SQL Server Integration Services.

Tables are stored in SQL Server database engine and can be manipulated using ALTER TABLE.

CAST and CONVERT are T-SQL fuctions. T-SQL is a programming language used to manipulate the DATA that is stored in tables (note the distinction here between ALTER TABLE which only operates on the table itself).

DT_WSTR is a data type within SQL Server Integration Services. It is NOT a data type within SQL Server Database Engine. Hence, CAST and CONVERT will not work on columns of type DT_WSTR.

With all that in mind, can you explain again exactly what it is you require to be able to do?

-Jamie

|||

Thank you greatly -- your explanation is really clear and very helpful. One does not always see the "big picture", when just looking at individual BOL pages!

What trying to do is is set-up to use the Term Extraction Transformation which as we understand it requires use of the DT_WSTR or DT_NTEXT data types. This is a fairly limited, focused job we were trying to complete in SQL Server 200 5. It had sadly, frankly not fully hit us that there were different data types across various components of SQL Server.

As we have a bit "in /out" job to do here, we are now at least hoping to find a simple, but reasonably complete, example script to set up a project/package to read in an input file, convert a data type in a column, and then run a Term Extraction.

Thank you for your help.

|||

Term Extraction Transform is part of SQL Server Integration Services so you are in the right place.

It sounds like you are a beginner so I would recommend you first watch this webcast: http://msevents.microsoft.com/cui/WebCastEventDetails.aspx?EventID=1032289998&EventCategory=5&culture=en-US&CountryCode=US and this: http://msevents.microsoft.com/cui/WebCastEventDetails.aspx?EventID=1032273477&EventCategory=5&culture=en-US&CountryCode=US to introduce yourself to the product.

As for term extraction, I haven't seen much material although I vaguely recall a webcast that Donald Farmer (further up this thread) did in which it was mentioned. I can't find that webcast though. Hopefully Donald will reply and let you know.

-Jamie

sql

Changing column data type

I want to change a column's data type from bit to int. There are data in the table already. I'm wondering if it is save/correct way to issue the following command to change the data type for that column.

ALTER TABLE database_table
ALTER COLUMN my_bit_columnINT;

Thanks.

It should work. The data will change to 0 (if it was False) or 1 (if it was True) after you change your column data type.

Changing a Column datatype

Hello I am having a table
table1
col1 (bit)
and i want to changethe col1 type for smallint
col1 (smallint)
true will be = 1
and false = 0
how can i do it ??
thank youJust change it using Enterprise Manager. The existing values will be implicitly converted.|||i must change it from a script
but i found it

thank you|||Change it in Enterprise Manager, and then click the icon that scripts your change.

Changing a BIT to an INT where there's a CONSTRAINT and a DEFAULT

A few weeks ago a client asked me to add a column to a table so I
created this script:

ALTER TABLE dbo.tblIndividual ADD fldRenewalStatus BIT NOT NULL
CONSTRAINT fldRenewalStatus_Default DEFAULT 0

Now they want to change it from a BIT to an INT, to store an enum.
Fair enough. However, no matter how much I wrangle with a script, I
can't find a reliable way to alter the column. I've mixed and matched
the following and nothing seems to work:

EXEC sp_unbindefault 'tblIndividual.fldRenewalStatus'

DROP DEFAULT DF_tblIndividual_fldRenewalStatus

ALTER TABLE tblIndividual
DROP CONSTRAINT fldRenewalStatus_Default

ALTER TABLE tblIndividual
DROP COLUMN fldRenewalStatus
GO

ALTER TABLE tblIndividual
ADD fldRenewalStatus int NOT NULL
CONSTRAINT fldRenewalStatus_Default DEFAULT 0

Thoughts?

Thanks

EdwardALTER the table to add a new INT column. Set the value of that column
based on the data in the BIT column. Alter the table to drop the BIT
column. Rename the INT column.

Roy Harvey
Beacon Falls, CT

On 2 Jan 2007 09:19:00 -0800, teddysnips@.hotmail.com wrote:

Quote:

Originally Posted by

>A few weeks ago a client asked me to add a column to a table so I
>created this script:
>
>ALTER TABLE dbo.tblIndividual ADD fldRenewalStatus BIT NOT NULL
>CONSTRAINT fldRenewalStatus_Default DEFAULT 0
>
>Now they want to change it from a BIT to an INT, to store an enum.
>Fair enough. However, no matter how much I wrangle with a script, I
>can't find a reliable way to alter the column. I've mixed and matched
>the following and nothing seems to work:
>
>EXEC sp_unbindefault 'tblIndividual.fldRenewalStatus'
>
>DROP DEFAULT DF_tblIndividual_fldRenewalStatus
>
>ALTER TABLE tblIndividual
>DROP CONSTRAINT fldRenewalStatus_Default
>
>ALTER TABLE tblIndividual
>DROP COLUMN fldRenewalStatus
>GO
>
>ALTER TABLE tblIndividual
>ADD fldRenewalStatus int NOT NULL
>CONSTRAINT fldRenewalStatus_Default DEFAULT 0
>
>
>Thoughts?
>
>Thanks
>
>Edward

|||On 2 Jan 2007 09:19:00 -0800, teddysnips@.hotmail.com wrote:

Quote:

Originally Posted by

>A few weeks ago a client asked me to add a column to a table so I
>created this script:
>
>ALTER TABLE dbo.tblIndividual ADD fldRenewalStatus BIT NOT NULL
>CONSTRAINT fldRenewalStatus_Default DEFAULT 0
>
>Now they want to change it from a BIT to an INT, to store an enum.
>Fair enough. However, no matter how much I wrangle with a script, I
>can't find a reliable way to alter the column. I've mixed and matched
>the following and nothing seems to work:

Hi Edward,

You can do as Roy suggests, or you can run the following script:

ALTER TABLE dbo.tblIndividual
DROP CONSTRAINT fldRenewalStatus_Default;

ALTER TABLE dbo.tblIndividual
ALTER COLUMN fldRenewalStatus INT NOT NULL;

ALTER TABLE dbo.tblIndividual
ADD CONSTRAINT fldRenewalStatus_Default DEFAULT 0 FOR fldRenewalStatus;

Running the ALTER COLUMN might take long if you have lots of data!

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Wed, 03 Jan 2007 00:06:18 +0100, Hugo Kornelis
<hugo@.perFact.REMOVETHIS.info.INVALIDwrote:

Quote:

Originally Posted by

>You can do as Roy suggests, or you can run the following script:

Much simpler, of course. Somehow I had the idea that bit would not
convert to int. Thanks for the diplomatic correction. 8-)

Roy|||Hugo Kornelis wrote:

Quote:

Originally Posted by

On 2 Jan 2007 09:19:00 -0800, teddysnips@.hotmail.com wrote:
>

Quote:

Originally Posted by

A few weeks ago a client asked me to add a column to a table so I
created this script:

ALTER TABLE dbo.tblIndividual ADD fldRenewalStatus BIT NOT NULL
CONSTRAINT fldRenewalStatus_Default DEFAULT 0

Now they want to change it from a BIT to an INT, to store an enum.
Fair enough. However, no matter how much I wrangle with a script, I
can't find a reliable way to alter the column. I've mixed and matched
the following and nothing seems to work:

>
Hi Edward,
>
You can do as Roy suggests, or you can run the following script:
>
ALTER TABLE dbo.tblIndividual
DROP CONSTRAINT fldRenewalStatus_Default;
>
ALTER TABLE dbo.tblIndividual
ALTER COLUMN fldRenewalStatus INT NOT NULL;
>
ALTER TABLE dbo.tblIndividual
ADD CONSTRAINT fldRenewalStatus_Default DEFAULT 0 FOR fldRenewalStatus;
>
Running the ALTER COLUMN might take long if you have lots of data!

Thanks Hugo - that worked a treat!

Edward

Changes to Master/Target Server Configurations

I've installed SQL Server 2000 and applied SP3a. I see SP4 is available and fixes a lot of things. The only bit I am unsure about is section '5.3.2 Changes to Master/Target Server Configurations'

http://download.microsoft.com/download/1/b/d/1bdf5b78-584e-4de0-b36f-c44e06b0d2a3/ReadmeSql2k32sp4.htm#_5464_sql_server_agent_enhancements_705

"Before you apply SP4, you must complete several steps to upgrade your SQL Server 2000 master/target server configuration. The changes that are introduced with SP4 are not compatible with SQL Server 7.0 target servers, or with any servers not running SP3 or later. This is a change from the original SQL Server 2000 functionality. "

As I'm just starting out I don't even know what 'Master/Target Server' is. I'm only installing to my PC which is XP Pro SP2. Can I ignore this step and apply the patch or do I need to do as it states?

Thanks

SQL Server 2000 service pack 3 and 3a are twenty five .sql files Windows uses as needed, I have used them for all existing operating systems with minor problems with Windows 2000 standard server. I would say just run it and in SQL Server 2000 and below ignore most questions only answer those relevant to your situation and you will not run into problems with SQL Server. Hope this helps.

change value type from Int64 to Int32 ?

I have a query which calculates a number... but by default the number is represented as a 64 bit integer. I cannot remember the function name but it is only using SQL9.0 built in functions. Is there a way to cast the number?

This is not a trivial issue since I have found an easier way to do this which did not involve the number, although any input would be greatly appreciated.

Thank you :)

I don't quite understand your question. But you can cast a value to a different data type using CAST function. You may or may not get runtime errors depending on the data. See Books Online for more details. So in your query, you can do something like below:

select cast(<expr> as int)

from ...

change of value at the time of insertion

Hai,
Here is a query that i am creating a table

create table test10 (item varchar(10),state bit)

now at the time of insertion i will enter like this

insert into test10 values ('book' ,1)

but at the time of display in the table i want

item state
book true

from above i want to ask that when i am going to insert 1 i want to
get true and 0 as false.I never enter true or false in the insert
command. please help me in coding.
thankyou,
lucky.

Replyis this homework, or a real world question? if real world, come up
with better examples.

thx.|||Hai doug,
This is baba what i have askd is not for fun really i
want to know wether i am going to get 1 as true in the output table it
is a request from my side i am new to the SQL i know u r having valuble
time i dont want to waste it if my query is good please give me the
answer. thankyou,
baba.|||Hi Lucky

A column of type bit can hold the values 0 or 1. Depending on the client
side programming language it may interpret 0 as false and 1 as true. For
example in VB.Net you could bind this to a boolean (logical) field in a
ADO.Net datatable.

Within T-sql you could say:

select item, case when state = 0 then 'false' else 'true' end as state1
from test10

But in this case, from an application's (such as one written in VB.Net)
point of view the 'true' or 'false' returned are character strings not
boolean (logical) values.

--
-Dick Christoph
"lucky" <j.v.s.s.baba@.gmail.com> wrote in message
news:1142488231.981434.27360@.p10g2000cwp.googlegro ups.com...
> Hai,
> Here is a query that i am creating a table
> create table test10 (item varchar(10),state bit)
>
> now at the time of insertion i will enter like this
>
> insert into test10 values ('book' ,1)
>
> but at the time of display in the table i want
>
> item state
> book true
>
> from above i want to ask that when i am going to insert 1 i want to
> get true and 0 as false.I never enter true or false in the insert
> command. please help me in coding.
> thankyou,
> lucky.
>
> Reply

change of value at the time of insertion

Hai,
Here is a query that i am creating a table
create table test10 (item varchar(10),state bit)
now at the time of insertion i will enter like this
insert into test10 values ('book' ,1)
but at the time of display in the table i want
item state
book true
from above i want to ask that when i am going to insert 1 i want to
get true and 0 as false.I never enter true or false in the insert
command. please help me in coding.
thankyou,
lucky.Hi
create table test10 (item varchar(10),state bit)
insert into test10 values ('book' ,1)
select item,case when state=1 then 'true' else 'false' end as state
from test10
drop table test10
"lucky" <j.v.s.s.baba@.gmail.com> wrote in message
news:1142488029.691844.64920@.j52g2000cwj.googlegroups.com...
> Hai,
> Here is a query that i am creating a table
> create table test10 (item varchar(10),state bit)
> now at the time of insertion i will enter like this
> insert into test10 values ('book' ,1)
>
> but at the time of display in the table i want
> item state
> book true
> from above i want to ask that when i am going to insert 1 i want to
> get true and 0 as false.I never enter true or false in the insert
> command. please help me in coding.
> thankyou,
> lucky.
>

Change Look & Feel

Is there a way to change the look and feel of the reports in the bit where
the parameters selection is? And what about the top section?
Im thinking of changing background etc.
JonasChanging the style of HTML Viewer toolbar is not possible in the initial
release but it can be done in SP1. Details are in SP1 readme:
http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
news:%23S6IjXmWEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Is there a way to change the look and feel of the reports in the bit where
> the parameters selection is? And what about the top section?
> Im thinking of changing background etc.
> Jonas
>