How can I change a column with datatype "text" to "int"?

I made a mistake when I first created the column and just found out when I
tried to calculate. I tried using the design feature on Enterprise Manager,
but got an error message that this couldn't be done. Any help is deeply
appreciate.Don't use Enterprise Manager for this. Open Query Analyzer and connect to
the correct database.
-- add a new column to the table;
ALTER TABLE tablename ADD temp_column INT;
-- copy the data from the text column;
UPDATE tablename SET temp_column = text_column WHERE ISNUMERIC(text_column)
= 1;
-- drop the text column;
ALTER TABLE tablename DROP COLUMN text_column;
-- rename the new column;
EXEC sp_rename 'tablename.temp_column', 'real_column', 'COLUMN';
Of course, you'll want to put in the correct names for tablename,
text_column, real_column, etc.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Karolus" <Karolus@.discussions.microsoft.com> wrote in message
news:3B1473BD-7C92-4D18-80FF-F0DEE26C408F@.microsoft.com...
>I made a mistake when I first created the column and just found out when I
> tried to calculate. I tried using the design feature on Enterprise
> Manager,
> but got an error message that this couldn't be done. Any help is deeply
> appreciate.|||Thank you, Aaaron. I will use what you provided and make the change. Thanks
mucho. karolus
"Aaron Bertrand [SQL Server MVP]" wrote:
> Don't use Enterprise Manager for this. Open Query Analyzer and connect to
> the correct database.
> -- add a new column to the table;
> ALTER TABLE tablename ADD temp_column INT;
> -- copy the data from the text column;
> UPDATE tablename SET temp_column = text_column WHERE ISNUMERIC(text_column)
> = 1;
> -- drop the text column;
> ALTER TABLE tablename DROP COLUMN text_column;
> -- rename the new column;
> EXEC sp_rename 'tablename.temp_column', 'real_column', 'COLUMN';
> Of course, you'll want to put in the correct names for tablename,
> text_column, real_column, etc.
>
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
> "Karolus" <Karolus@.discussions.microsoft.com> wrote in message
> news:3B1473BD-7C92-4D18-80FF-F0DEE26C408F@.microsoft.com...
> >I made a mistake when I first created the column and just found out when I
> > tried to calculate. I tried using the design feature on Enterprise
> > Manager,
> > but got an error message that this couldn't be done. Any help is deeply
> > appreciate.
>
>

How can I change a column with datatype "text" to "int"?

I made a mistake when I first created the column and just found out when I
tried to calculate. I tried using the design feature on Enterprise Manager,
but got an error message that this couldn't be done. Any help is deeply
appreciate.
Don't use Enterprise Manager for this. Open Query Analyzer and connect to
the correct database.
-- add a new column to the table;
ALTER TABLE tablename ADD temp_column INT;
-- copy the data from the text column;
UPDATE tablename SET temp_column = text_column WHERE ISNUMERIC(text_column)
= 1;
-- drop the text column;
ALTER TABLE tablename DROP COLUMN text_column;
-- rename the new column;
EXEC sp_rename 'tablename.temp_column', 'real_column', 'COLUMN';
Of course, you'll want to put in the correct names for tablename,
text_column, real_column, etc.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Karolus" <Karolus@.discussions.microsoft.com> wrote in message
news:3B1473BD-7C92-4D18-80FF-F0DEE26C408F@.microsoft.com...
>I made a mistake when I first created the column and just found out when I
> tried to calculate. I tried using the design feature on Enterprise
> Manager,
> but got an error message that this couldn't be done. Any help is deeply
> appreciate.
|||Thank you, Aaaron. I will use what you provided and make the change. Thanks
mucho. karolus
"Aaron Bertrand [SQL Server MVP]" wrote:

> Don't use Enterprise Manager for this. Open Query Analyzer and connect to
> the correct database.
> -- add a new column to the table;
> ALTER TABLE tablename ADD temp_column INT;
> -- copy the data from the text column;
> UPDATE tablename SET temp_column = text_column WHERE ISNUMERIC(text_column)
> = 1;
> -- drop the text column;
> ALTER TABLE tablename DROP COLUMN text_column;
> -- rename the new column;
> EXEC sp_rename 'tablename.temp_column', 'real_column', 'COLUMN';
> Of course, you'll want to put in the correct names for tablename,
> text_column, real_column, etc.
>
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
> "Karolus" <Karolus@.discussions.microsoft.com> wrote in message
> news:3B1473BD-7C92-4D18-80FF-F0DEE26C408F@.microsoft.com...
>
>

How can I change a column with datatype "text" to "int"?

I made a mistake when I first created the column and just found out when I
tried to calculate. I tried using the design feature on Enterprise Manager,
but got an error message that this couldn't be done. Any help is deeply
appreciate.Don't use Enterprise Manager for this. Open Query Analyzer and connect to
the correct database.
-- add a new column to the table;
ALTER TABLE tablename ADD temp_column INT;
-- copy the data from the text column;
UPDATE tablename SET temp_column = text_column WHERE ISNUMERIC(text_column)
= 1;
-- drop the text column;
ALTER TABLE tablename DROP COLUMN text_column;
-- rename the new column;
EXEC sp_rename 'tablename.temp_column', 'real_column', 'COLUMN';
Of course, you'll want to put in the correct names for tablename,
text_column, real_column, etc.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Karolus" <Karolus@.discussions.microsoft.com> wrote in message
news:3B1473BD-7C92-4D18-80FF-F0DEE26C408F@.microsoft.com...
>I made a mistake when I first created the column and just found out when I
> tried to calculate. I tried using the design feature on Enterprise
> Manager,
> but got an error message that this couldn't be done. Any help is deeply
> appreciate.|||Thank you, Aaaron. I will use what you provided and make the change. Thank
s
mucho. karolus
"Aaron Bertrand [SQL Server MVP]" wrote:

> Don't use Enterprise Manager for this. Open Query Analyzer and connect to
> the correct database.
> -- add a new column to the table;
> ALTER TABLE tablename ADD temp_column INT;
> -- copy the data from the text column;
> UPDATE tablename SET temp_column = text_column WHERE ISNUMERIC(text_column
)
> = 1;
> -- drop the text column;
> ALTER TABLE tablename DROP COLUMN text_column;
> -- rename the new column;
> EXEC sp_rename 'tablename.temp_column', 'real_column', 'COLUMN';
> Of course, you'll want to put in the correct names for tablename,
> text_column, real_column, etc.
>
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
> "Karolus" <Karolus@.discussions.microsoft.com> wrote in message
> news:3B1473BD-7C92-4D18-80FF-F0DEE26C408F@.microsoft.com...
>
>

How can I calculate the disk space which some records used in SQL 2005 database?

I execute the following SQL, and return 3 records, I want to know how many disk space the 3 records use, how can I do? thanks!

select * from myTable where username='Paul'

I'd like to use a temp table:

select * INTO tbl_tmp from myTable where username='Paul'

exec sp_spaceused tbl_tmp

drop table tbl_tmp

|||Many thanks! but is there a better way?|||

Or you have to sum date length for all fields:

SELECT [TotalDataSize(Bytes)]=SUM(DATALENGTH(col1)+DATALENGTH(col2)+DATALENGTH(username))
FROM myTable where username='Paul'

|||Sorry, I'm a beginner, what does '[TotalDataSize(Bytes)]' mean ?|||

And more, ifcol1 is null in some records, doesSELECT [TotalDataSize(Bytes)]=SUM(DATALENGTH(col1)+DATALENGTH(col2)+DATALENGTH(username))
FROM myTable where username='Paul' cause error?

Many thanks!

|||BTW, the datalength method is not very accurate. It will give you a kind of estimate, but it isn't even close to correct in many cases.

how can i calculate "sum" in crystal report?

there is 2 field one is Branch code and second is transfer ammount.

if the branch code is same then the sum of transfer ammount is display

for eg

branch code transfer amount

0101 1000

0101 4000

--

5000

how can i do this?

Hi,

this forum is about reporting services only. If you want to do that in reporting service you would do a grouping per branch_code and do a sum in the footer of the group.

HTH, Jens Suessmeyer.

--
http://www.sqlserver2005.de
--

|||To be honest that's pretty much exactly what you'd also do in Crystal

How calculate space that occupied?

I want to know following memory space related question.

1 . How can i get a Database Size by means of a SQL query?

2. How can i Get a field size (not allocated space) .. ie. i am
storing some textual data to a field..
after a insertion i want to know how much space has been occupied by
that particular field .

ex sno product_name Description
1 sample1 dfkjsdkfj kldsjfkdjk
sdkjdfskdjk vcmvxcvmcvnksdjfkdsn m

Here i want know the space occupied by the field name "description"
for the product id=1.

Thanks in adavance

Regards
Visu.On Jun 12, 12:35 pm, visu <k.vis...@.gmail.comwrote:

Quote:

Originally Posted by

I want to know following memory space related question.
>
1 . How can i get a Database Size by means of a SQL query?
>
2. How can i Get a field size (not allocated space) .. ie. i am
storing some textual data to a field..
after a insertion i want to know how much space has been occupied by
that particular field .
>
ex sno product_name Description
1 sample1 dfkjsdkfj kldsjfkdjk
sdkjdfskdjk vcmvxcvmcvnksdjfkdsn m
>
Here i want know the space occupied by the field name "description"
for the product id=1.
>
Thanks in adavance
>
Regards
Visu.

1. How can i get a Database Size by means of a SQL query

sp_helpdb 'dbname'

2. Here i want know the space occupied by the field name "description"
for the product id=1.

SELECT SUM(DATALENGTH(description)) FROM table
WHERE product_id=1.|||On Jun 12, 10:47 am, M A Srinivas <masri...@.gmail.comwrote:

Quote:

Originally Posted by

On Jun 12, 12:35 pm, visu <k.vis...@.gmail.comwrote:
>
>
>
>
>

Quote:

Originally Posted by

I want to know following memory space related question.

>

Quote:

Originally Posted by

1 . How can i get a Database Size by means of a SQL query?

>

Quote:

Originally Posted by

2. How can i Get a field size (not allocated space) .. ie. i am
storing some textual data to a field..
after a insertion i want to know how much space has been occupied by
that particular field .

>

Quote:

Originally Posted by

ex sno product_name Description
1 sample1 dfkjsdkfj kldsjfkdjk
sdkjdfskdjk vcmvxcvmcvnksdjfkdsn m

>

Quote:

Originally Posted by

Here i want know the space occupied by the field name "description"
for the product id=1.

>

Quote:

Originally Posted by

Thanks in adavance

>

Quote:

Originally Posted by

Regards
Visu.

>
1. How can i get a Database Size by means of a SQL query
>
sp_helpdb 'dbname'
>
2. Here i want know the space occupied by the field name "description"
for the product id=1.
>
SELECT SUM(DATALENGTH(description)) FROM table
WHERE product_id=1.- Hide quoted text -
>
- Show quoted text -

Use the length function

SELECT LENGTH(description) FROM tablename WHERE id = 1

This will return the length of the description field for the row
identified by id = 1.|||undercups (dwang@.woodace.co.uk) writes:

Quote:

Originally Posted by

Use the length function
>
SELECT LENGTH(description) FROM tablename WHERE id = 1
>
This will return the length of the description field for the row
identified by id = 1.

There is no LENGTH function in SQL Server.

You may be thinking of len(), but since Visu asked for the space
consumption, it does not fit the bill for two reasons:

1) It ignores trailing spaces.
2) It counts characters, not bytes, which makes a difference for nvarchar.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||M A Srinivas (masri999@.gmail.com) writes:

Quote:

Originally Posted by

2. Here i want know the space occupied by the field name "description"
for the product id=1.
>
SELECT SUM(DATALENGTH(description)) FROM table
WHERE product_id=1.

Should be:

SELECT SUM(2 + DATALENGTH(description)) FROM table
WHERE product_id=1.

Add 2 for the length stored for the field.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

How calculate SMO.Transfer progress?

I have work code who transfer database using SMO.Transfer object.

This code start my transfer and subscribe my event handlers. I have such problem: DiscoveryProgress - does not approach. Maybe need use additional options?

m_tr.ScriptingError += new ScriptingErrorEventHandler(m_tr_ScriptingError);
m_tr.ScriptingProgress += new ProgressReportEventHandler(m_tr_ScriptingProgress);

m_tr.DiscoveryProgress += new ProgressReportEventHandler(m_tr_DiscoveryProgress);
m_tr.DataTransferEvent += new DataTransferEventHandler(m_tr_DataTransferEvent);

m_script = m_tr.ScriptTransfer();
m_tr.TransferData();

Sergey,

You can define your event handlers like this:

public static void DataTransferHandler(object sender, DataTransferEventArgs e)
{
Console.WriteLine(e.DataTransferEventType.ToString() + " : " + e.Message);
}

public static void ProgressEventHandler(object sender, ProgressReportEventArgs args)
{
Console.Write("Progress {0} of {1} . . . {2} \n", args.TotalCount, args.Total, args.Current);
}

public static void ScriptingProgressEventHandler(object sender, ProgressReportEventArgs args)
{
Console.Write("Progress {0} of {1} . . . {2} \n", args.TotalCount, args.Total, args.Current);
}

Then set up your events like this:

trans.DiscoveryProgress += new ProgressReportEventHandler(ProgressEventHandler);

trans.ScriptingProgress += new ProgressReportEventHandler(ScriptingProgressEventHandler);

trans.DataTransferEvent += new DataTransferEventHandler(DataTransferHandler);

By the way, you don't need to call ScriptTransfer before calling TransferData. ScriptTransfer returns TSQL script in a StringCollection, which you are not using.

Artur Laksberg

SQL Server Team

|||Hi Artur,
I did this as you wrote. I only use the scripting progress and the discover progress for my app. The scripting event is raised, and my handler gets it, but the totals and subtotals all remain at value of 1. The discovery event is either never raised or something is wrong on my side as my handler is never called.
Do you know what else I can try? Is this a known issue?
Regards
Japie Teunissen
|||

Japie,

You are right: we seem to have a few problems here. Total shows right values for Scripting, but Transfer uses Scripting to transfer objects one at a time, hence "1 out of 1" you see in the event handler. Sort of makes sense but not useful at all.

Discover event is only raised when you call Script with a list of URNs, which most people don't. Again, confusing and not very useful.

I'll file a bug to clean this up.

Thanks to you and Sergey for pointing this out.

Artur Laksberg
SQL Server Team
Microsoft

How calculate SMO.Transfer progress?

I have work code who transfer database using SMO.Transfer object.

This code start my transfer and subscribe my event handlers. I have such problem: DiscoveryProgress - does not approach. Maybe need use additional options?

m_tr.ScriptingError += new ScriptingErrorEventHandler(m_tr_ScriptingError);
m_tr.ScriptingProgress += new ProgressReportEventHandler(m_tr_ScriptingProgress);

m_tr.DiscoveryProgress += new ProgressReportEventHandler(m_tr_DiscoveryProgress);
m_tr.DataTransferEvent += new DataTransferEventHandler(m_tr_DataTransferEvent);

m_script = m_tr.ScriptTransfer();
m_tr.TransferData();

Sergey,

You can define your event handlers like this:

public static void DataTransferHandler(object sender, DataTransferEventArgs e)
{
Console.WriteLine(e.DataTransferEventType.ToString() + " : " + e.Message);
}

public static void ProgressEventHandler(object sender, ProgressReportEventArgs args)
{
Console.Write("Progress {0} of {1} . . . {2} \n", args.TotalCount, args.Total, args.Current);
}

public static void ScriptingProgressEventHandler(object sender, ProgressReportEventArgs args)
{
Console.Write("Progress {0} of {1} . . . {2} \n", args.TotalCount, args.Total, args.Current);
}

Then set up your events like this:

trans.DiscoveryProgress += new ProgressReportEventHandler(ProgressEventHandler);

trans.ScriptingProgress += new ProgressReportEventHandler(ScriptingProgressEventHandler);

trans.DataTransferEvent += new DataTransferEventHandler(DataTransferHandler);

By the way, you don't need to call ScriptTransfer before calling TransferData. ScriptTransfer returns TSQL script in a StringCollection, which you are not using.

Artur Laksberg

SQL Server Team

|||Hi Artur,
I did this as you wrote. I only use the scripting progress and the discover progress for my app. The scripting event is raised, and my handler gets it, but the totals and subtotals all remain at value of 1. The discovery event is either never raised or something is wrong on my side as my handler is never called.
Do you know what else I can try? Is this a known issue?
Regards
Japie Teunissen
|||

Japie,

You are right: we seem to have a few problems here. Total shows right values for Scripting, but Transfer uses Scripting to transfer objects one at a time, hence "1 out of 1" you see in the event handler. Sort of makes sense but not useful at all.

Discover event is only raised when you call Script with a list of URNs, which most people don't. Again, confusing and not very useful.

I'll file a bug to clean this up.

Thanks to you and Sergey for pointing this out.

Artur Laksberg
SQL Server Team
Microsoft

hours cal

Dear frinends
I need to substract the hours. I need to calculate the hours worked after 6:00pm.
input is strat time like 30:june:03:30 PM until 8:00pm in night
I need to calculate the hours he worked after 6:00pm.
please help
elamsomething like this?

declare @.d datetime
set @.d = dateadd(hh,-12,getdate())
select @.d
, dateadd(hh,18,convert(varchar(10),@.d,120))
, datediff(hh,dateadd(hh,18,convert(varchar(10),@.d,1 20)),@.d)|||This calculates minutes worked after 6 pm. You could change it to calculate hours if you don't want the extra accuracy.

select (datediff(mi, convert(varchar(10), @.d, 120) + ' 18:00', @.d) + abs(datediff(mi, convert(varchar(10), @.d, 120) + ' 18:00', @.d)))/2

The absolute value conversion is used to return 0 instead of negative hours.