adjust colours of bars in bar charts at run time

Hi,
does anyone know if it is possible to adjust the colours of the bars in
bar charts at run time using custom assemblies or functions? I have
clicked on all the options I can find and I can see no expression
capability for the colours of the bars, only a selection of color
schemes...is this just not an option in 2000?
Thanks
Peter
www.peternolan.comYou will need to have at least SP1 of Reporting Services 2000 installed (on
the server and on the report designer). Please check the SP1/SP2 Readme for
more details:
http://download.microsoft.com/download/5/1/3/513534ae-a0e7-44e6-9a04-ba3c549a5f5f/sp2Readme_EN.htm#_chart_enhancements
You can also search the archives of this newsgroup and you should find many
related threads about setting datapoint colors in charts.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Peter Nolan" <peter@.peternolan.com> wrote in message
news:1127599073.728978.199980@.g44g2000cwa.googlegroups.com...
> Hi,
> does anyone know if it is possible to adjust the colours of the bars in
> bar charts at run time using custom assemblies or functions? I have
> clicked on all the options I can find and I can see no expression
> capability for the colours of the bars, only a selection of color
> schemes...is this just not an option in 2000?
> Thanks
> Peter
> www.peternolan.com
>|||Hi Robert,
I just installed RS...SP2.....yes, I understand I can change the
colour of the data points printed on the chart at run time.....but I
cannot see any way to change the colour of the bar itself....it only
appears that I can change the style of the report between things like
'excel', 'earthy', 'transparent' etc...and the colours of the bars are
rendered according to these definitions for all bars in the
series.....
No big deal as I will probably use another charting package on
2005...but I was wondering if I can do this in RS charts...
Thanks
Peter|||Peter,
I've done this in the following way:
In Chart Properties, Data, Values, Edit, Appearance, Series Style I use this
function:
= Code.GetColor(Fields!RightsHolder.Value)
And in the code for the report I have:
Public Function GetColor(ByVal RightsHolder As String) As String
if RightsHolder = "EMI" Then
Return "#CC3333" ' "Red"
end if
if RightsHolder = "EMI RW" Then
Return "Pink"
end if
if RightsHolder = "WMG" Then
Return "#3300CC" ' "Blue"
end if
if (RightsHolder <> "WMG") and (RightsHolder <> "EMI") and (RightsHolder
<> "EMI RW") Then
Return "Black"
end if
End Function
HTH,
Paul Ibison, SQL Server MVP|||Paul,
thanks for that...I will give it a go.
Peter|||You cannot define your own custom palettes directly. The closest you can get
is to use the approach discussed in the following blog posting (it also
provides a sample for download):
http://blogs.msdn.com/bwelcker/archive/2005/05/20/420349.aspx
The sample defines its own "color palette" by always overriding the colors
that come from the selected color palette.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Peter Nolan" <peter@.peternolan.com> wrote in message
news:1127656949.717291.17150@.z14g2000cwz.googlegroups.com...
> Hi Robert,
> I just installed RS...SP2.....yes, I understand I can change the
> colour of the data points printed on the chart at run time.....but I
> cannot see any way to change the colour of the bar itself....it only
> appears that I can change the style of the report between things like
> 'excel', 'earthy', 'transparent' etc...and the colours of the bars are
> rendered according to these definitions for all bars in the
> series.....
> No big deal as I will probably use another charting package on
> 2005...but I was wondering if I can do this in RS charts...
> Thanks
> Peter
>|||Robert,
apologies for not referencing/crediting your great blog example - knew I got
my code template from somewhere but couldn't remember where :)
Cheers,
Paul Ibison, SQL Server MVP|||That's fine. I'm glad people find my samples, tips, answers, etc. useful and
it helps them solving their issues.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:u3iZoK2wFHA.2728@.TK2MSFTNGP14.phx.gbl...
> Robert,
> apologies for not referencing/crediting your great blog example - knew I
> got my code template from somewhere but couldn't remember where :)
> Cheers,
> Paul Ibison, SQL Server MVP
>|||Paul,
you are brilliant!!! Thank You!!!!
When I read this I thought it related to the numbers that were printed
on the chart if they were printed...but when I tested I found it was
the bar and line colour that it was referring to...now I can pass data
from the row to a member of a custom assembly and get it to generate
the bars on the chart to be the colours I want!!
Thank you very much for this...this is a feature I really wanted on my
charts....
Best Regards
Peter
www.peternolan.com|||Hello,
I read the blog and for HTML and PDF it works great. However when i export a
report to excel, excel reinterprets the colors to what it can handle.
In my case i made a colorpalette of only oranges and browns. When i export
it to excel, the graph itself is exported ok. Only the colors i used in the
legend table are changed. eg. GoldenRod, Orange and DarkOrange are all
reverted to the same color.
Is there a way to have the table also exported as a picture or something to
get the right color across?
"Robert Bruckner [MSFT]" wrote:
> You cannot define your own custom palettes directly. The closest you can get
> is to use the approach discussed in the following blog posting (it also
> provides a sample for download):
> http://blogs.msdn.com/bwelcker/archive/2005/05/20/420349.aspx
> The sample defines its own "color palette" by always overriding the colors
> that come from the selected color palette.
>|||Hi,
I don't seem to have the 'Series Style" section showing on my 'Appearance'
tab.
Have installed SP2 for RS 2000 on the server, but surely this is a "client"
issue with
SQL Server Report Designer. I'm using Ver 8.00.743
Any ideas?
"Paul Ibison" wrote:
> Peter,
> I've done this in the following way:
> In Chart Properties, Data, Values, Edit, Appearance, Series Style I use this
> function:
> = Code.GetColor(Fields!RightsHolder.Value)
> And in the code for the report I have:
> Public Function GetColor(ByVal RightsHolder As String) As String
> if RightsHolder = "EMI" Then
> Return "#CC3333" ' "Red"
> end if
> if RightsHolder = "EMI RW" Then
> Return "Pink"
> end if
> if RightsHolder = "WMG" Then
> Return "#3300CC" ' "Blue"
> end if
> if (RightsHolder <> "WMG") and (RightsHolder <> "EMI") and (RightsHolder
> <> "EMI RW") Then
> Return "Black"
> end if
> End Function
> HTH,
> Paul Ibison, SQL Server MVP
>
>

Adding times together

Hi guys,

I have a field in my DB called EventDate as a DateTime field,
therefore it holds both the date and time together like this:
'2004-10-14 08:42:57.000'.

I need to add together all the times in this column for a particular
date range (BETWEEN).

Any suggestions will be great.

Thanks
Sunny:)Sunny K (sunstarwu@.yahoo.com) writes:
> I have a field in my DB called EventDate as a DateTime field,
> therefore it holds both the date and time together like this:
> '2004-10-14 08:42:57.000'.
> I need to add together all the times in this column for a particular
> date range (BETWEEN).

If I take you by the word, it sounds like the answer is:

SELECT SUM(datefiff(ss, convert(char(8), EventDate, 112), EventDate)
FROM tbl
WHERE EventDate BETWEEN ... AND ...

But it looks a little funny.

A common advice for this type of query is that you post

o CREATE TABLE statement for your table.
o INSERT statements with sample data.
o The desired result, given the sample data.

This make it easy to cut and paste and compose a tested solution.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||You certainly can use BETWEEN with the DATETIME datatype but if you are
querying values with times other than midnight it's often more convenient to
use use >= and < instead of BETWEEN. For example

This:

SELECT *
FROM YourTable
WHERE eventdate >= '20041014'
AND eventdate < '20041015'

Is equivalent to this:

SELECT *
FROM YourTable
WHERE eventdate
BETWEEN '2004-10-14T00:00:00.000'
AND '2004-10-14T23:59:59.997'

Hope that answers your question.

--
David Portas
SQL Server MVP
--|||> I need to add together all the times in this column

I missed that bit from my first post - maybe because I've no idea what it
means! Just what would you expect to be the result of, for example
'2004-10-14 08:42:57.000' + '2004-12-31 00:00:00.000'? Could you explain how
you want to add up a DATETIME?

--
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<xoudnd6FteLz-BTcRVn-1A@.giganews.com>...
> > I need to add together all the times in this column
> I missed that bit from my first post - maybe because I've no idea what it
> means! Just what would you expect to be the result of, for example
> '2004-10-14 08:42:57.000' + '2004-12-31 00:00:00.000'? Could you explain how
> you want to add up a DATETIME?

Hi Dave

Thats for the reply, and admitly i was very vague in what i meant to
say. From your above example the time result of the two times would
give me 08:42:57.000, as the time added was 00:00:00.000.

Maybe this will help explain what i mean a bit better. Here is a few
typical lines from my table:

Name EventDate EventID
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
CTWIGG-MOBL 2004-11-03 09:13:21.000 6006
CTWIGG-MOBL 2004-11-03 09:14:42.000 6005
CTWIGG-MOBL 2004-11-03 15:44:55.000 6006
CTWIGG-MOBL 2004-11-03 15:46:11.000 6005

My 'exact' requirements are to SUM all the 6005 EventID times together
and SUM all the 6006 EventID times together then find the difference
between the two times. The dates in the column are of no use.

Ive been banging my head over how to do this for a few days now. Any
suggestions?|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<xoudnd6FteLz-BTcRVn-1A@.giganews.com>...
> > I need to add together all the times in this column
> I missed that bit from my first post - maybe because I've no idea what it
> means! Just what would you expect to be the result of, for example
> '2004-10-14 08:42:57.000' + '2004-12-31 00:00:00.000'? Could you explain how
> you want to add up a DATETIME?

Hi Dave

Thats for the reply, and admitly i was very vague in what i meant to
say. From your above example the time result of the two times would
give me 08:42:57.000, as the time added was 00:00:00.000.

Maybe this will help explain what i mean a bit better. Here is a few
typical lines from my table:

Name EventDate EventID
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
CTWIGG-MOBL 2004-11-03 09:13:21.000 6006
CTWIGG-MOBL 2004-11-03 09:14:42.000 6005
CTWIGG-MOBL 2004-11-03 15:44:55.000 6006
CTWIGG-MOBL 2004-11-03 15:46:11.000 6005

My 'exact' requirements are to SUM all the 6005 EventID times together
and SUM all the 6006 EventID times together then find the difference
between the two times. The dates in the column are of no use.

Ive been banging my head over how to do this for a few days now. Any
suggestions?|||Sunny K (sunstarwu@.yahoo.com) writes:
> Thats for the reply, and admitly i was very vague in what i meant to
> say. From your above example the time result of the two times would
> give me 08:42:57.000, as the time added was 00:00:00.000.
> Maybe this will help explain what i mean a bit better. Here is a few
> typical lines from my table:
> Name EventDate EventID
> _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
> CTWIGG-MOBL 2004-11-03 09:13:21.000 6006
> CTWIGG-MOBL 2004-11-03 09:14:42.000 6005
> CTWIGG-MOBL 2004-11-03 15:44:55.000 6006
> CTWIGG-MOBL 2004-11-03 15:46:11.000 6005
>
> My 'exact' requirements are to SUM all the 6005 EventID times together
> and SUM all the 6006 EventID times together then find the difference
> between the two times. The dates in the column are of no use.
> Ive been banging my head over how to do this for a few days now. Any
> suggestions?

I repeat from my previous post:

A common advice for this type of query is that you post

o CREATE TABLE statement for your table.
o INSERT statements with sample data.
o The desired result, given the sample data.

This make it easy to cut and paste and compose a tested solution.

In this case, the part with the desired result is very important,
because I am not sure what result you are looking for, and I don't
feel like guessing.

--
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 <esquel@.sommarskog.se> wrote in message news:<Xns9597F073739C9Yazorman@.127.0.0.1>...
> Sunny K (sunstarwu@.yahoo.com) writes:
> > Thats for the reply, and admitly i was very vague in what i meant to
> > say. From your above example the time result of the two times would
> > give me 08:42:57.000, as the time added was 00:00:00.000.
> > Maybe this will help explain what i mean a bit better. Here is a few
> > typical lines from my table:
> > Name EventDate EventID
> > _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
> > CTWIGG-MOBL 2004-11-03 09:13:21.000 6006
> > CTWIGG-MOBL 2004-11-03 09:14:42.000 6005
> > CTWIGG-MOBL 2004-11-03 15:44:55.000 6006
> > CTWIGG-MOBL 2004-11-03 15:46:11.000 6005
> > My 'exact' requirements are to SUM all the 6005 EventID times together
> > and SUM all the 6006 EventID times together then find the difference
> > between the two times. The dates in the column are of no use.
> > Ive been banging my head over how to do this for a few days now. Any
> > suggestions?
> I repeat from my previous post:
> A common advice for this type of query is that you post
> o CREATE TABLE statement for your table.
> o INSERT statements with sample data.
> o The desired result, given the sample data.
> This make it easy to cut and paste and compose a tested solution.
> In this case, the part with the desired result is very important,
> because I am not sure what result you are looking for, and I don't
> feel like guessing.

Hi,

Here is the script to create the table with some sample data:

CREATE TABLE [dbo].[tbltemp23] (
[Machine_Name] [char] (17) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[EventDate] [datetime] NOT NULL ,
[EventID] [int] NOT NULL
) ON [PRIMARY]
GO

INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-11 09:10:54.000',6006)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-11 09:12:13.000',6005)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-14 08:41:42.000',6006)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-14 08:42:57.000',6005)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-18 16:16:45.000',6006)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-18 16:19:21.000',6005)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-02 16:32:56.000',6006)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-02 16:34:17.000',6005)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-03 09:13:21.000',6006)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-03 09:14:42.000',6005)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-03 15:44:55.000',6006)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-03 15:46:11.000',6005)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-04 17:51:43.000',6006)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-04 17:53:03.000',6005)

Now I need to work out the the total time of all the 6006 EventIDs
(the date is here is not needed) which should equal: 93:32:16 then the
the total time of all the 6005 EventIDs which equals: 93:42:44. Then
finally find the difference between the two times, which should equal:
00:10:28 in this case.

I hope this is enough information.

Thanks
Sunny|||Thanks for the DDL and data.

SQL Server doesn't have a timespan data type. The query below uses
1900-01-01 as the base date from which durations are calculated, ignoring
the date component of the table data. You can format the returned values
according to your reporting requirements.

SELECT
(SELECT
DATEADD(s,
SUM(DATEDIFF(s,
'19000101', CAST(CONVERT(varchar(12), EventDate, 114) AS datetime))),
'19000101')
FROM tbltemp23
WHERE EventId = 6006) AS EventId6006Duration,
(SELECT
DATEADD(s,
SUM(DATEDIFF(s,
'19000101', CAST(CONVERT(varchar(12), EventDate, 114) AS datetime))),
'19000101')
FROM tbltemp23
WHERE EventId = 6005) AS EventId6005Duration,
DATEADD(s,
DATEDIFF(s,
(SELECT
DATEADD(s,
SUM(DATEDIFF(s,
'19000101', CAST(CONVERT(varchar(12), EventDate, 114) AS datetime))),
'19000101')
FROM tbltemp23
WHERE EventId = 6006),
(SELECT
DATEADD(s,
SUM(DATEDIFF(s,
'19000101', CAST(CONVERT(varchar(12), EventDate, 114) AS datetime))),
'19000101')
FROM tbltemp23
WHERE EventId = 6005)),
'19000101'
) AS EventDurationDifference

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Sunny K" <sunstarwu@.yahoo.com> wrote in message
news:1ecdad8f.0411080132.4d6627fe@.posting.google.c om...
> Erland Sommarskog <esquel@.sommarskog.se> wrote in message
> news:<Xns9597F073739C9Yazorman@.127.0.0.1>...
>> Sunny K (sunstarwu@.yahoo.com) writes:
>> > Thats for the reply, and admitly i was very vague in what i meant to
>> > say. From your above example the time result of the two times would
>> > give me 08:42:57.000, as the time added was 00:00:00.000.
>>> > Maybe this will help explain what i mean a bit better. Here is a few
>> > typical lines from my table:
>>> > Name EventDate EventID
>> > _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
>> > CTWIGG-MOBL 2004-11-03 09:13:21.000 6006
>> > CTWIGG-MOBL 2004-11-03 09:14:42.000 6005
>> > CTWIGG-MOBL 2004-11-03 15:44:55.000 6006
>> > CTWIGG-MOBL 2004-11-03 15:46:11.000 6005
>>>> > My 'exact' requirements are to SUM all the 6005 EventID times together
>> > and SUM all the 6006 EventID times together then find the difference
>> > between the two times. The dates in the column are of no use.
>>> > Ive been banging my head over how to do this for a few days now. Any
>> > suggestions?
>>
>> I repeat from my previous post:
>>
>> A common advice for this type of query is that you post
>>
>> o CREATE TABLE statement for your table.
>> o INSERT statements with sample data.
>> o The desired result, given the sample data.
>>
>> This make it easy to cut and paste and compose a tested solution.
>>
>> In this case, the part with the desired result is very important,
>> because I am not sure what result you are looking for, and I don't
>> feel like guessing.
>
> Hi,
> Here is the script to create the table with some sample data:
>
> CREATE TABLE [dbo].[tbltemp23] (
> [Machine_Name] [char] (17) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [EventDate] [datetime] NOT NULL ,
> [EventID] [int] NOT NULL
> ) ON [PRIMARY]
> GO
>
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-11 09:10:54.000',6006)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-11 09:12:13.000',6005)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-14 08:41:42.000',6006)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-14 08:42:57.000',6005)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-18 16:16:45.000',6006)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-18 16:19:21.000',6005)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-02 16:32:56.000',6006)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-02 16:34:17.000',6005)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-03 09:13:21.000',6006)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-03 09:14:42.000',6005)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-03 15:44:55.000',6006)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-03 15:46:11.000',6005)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-04 17:51:43.000',6006)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-04 17:53:03.000',6005)
> Now I need to work out the the total time of all the 6006 EventIDs
> (the date is here is not needed) which should equal: 93:32:16 then the
> the total time of all the 6005 EventIDs which equals: 93:42:44. Then
> finally find the difference between the two times, which should equal:
> 00:10:28 in this case.
> I hope this is enough information.
> Thanks
> Sunny|||Sunny K (sunstarwu@.yahoo.com) writes:
> Now I need to work out the the total time of all the 6006 EventIDs
> (the date is here is not needed) which should equal: 93:32:16 then the
> the total time of all the 6005 EventIDs which equals: 93:42:44. Then
> finally find the difference between the two times, which should equal:
> 00:10:28 in this case.

To be honest, this still seem very strange to me. Sure, there is enough
information to write a solution, but somehow I wonder what is the real
problem.

Looking at your data, it seems that event 6006 means start and 6005
means end, and what you really are computing is the total duration of
all start-stop sequences. Given that, I wrote this query:

SELECT convert(char(8), dateadd(ss, SUM(diff), '19000101'), 108)
FROM (select diff = datediff(ss, a.EventDate,
(SELECT MIN(EventDate)
FROM tbltemp23 b
WHERE b.EventDate > a.EventDate
AND b.EventID = 6005))
FROM tbltemp23 a
WHERE a.EventID = 6006) AS c

Of course, this query breaks down if the 6006 and 6005 can come in
any order, but in that case I have no clue of what might be going on.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||What can I say you guys, you really know your stuff. Thanks for all
the help, its given me the exact results I've needed.

I will consider you guys when I face another problem:-P

Sunny

Adding Time to Rank

Using FoodMart, how can i add time to this:
WITH MEMBER [Measures].[Rank] AS 'Rank ( Product.CurrentMember,Order( {Product.CurrentMember.Parent.Children} ,[Profit], DESC) ) '
SELECT
{[Profit] , [Rank]} ON COLUMNS,
Drink.Children ON ROWS
FROM SalesWhat platform is this?|||Brett,

This is an MDX statement for OLAP - Hard as nails to understand ..

msenoelo - are you wanting to set this up as a calculated measure or do you just want to see if the MDX is valid

If you want check the MDX then you can open up the sample mdx application from

Programs > SQL Server > Anaysis Services > Sample MDX Application

I think??

You then get a kind IDE that can use to parse MDX.

Or you can goto your cube

New calculated meause > Insert your code

Not totally sure as I don't have anaylsis services installed on my computer and MDX is total bloody mystery to me. Where did you get the MDX from ?

Here is a like to a more relevant forum:

http://www.sql-server-performance.com/forum/forum.asp?FORUM_ID=18

Cheerssql

ADDING TIME IN SQL SERVER!

Greetings!
How do I add duration:
00:00:25
00:00:25
00:00:25
in a sql statement. I have been able to add just the
minutes or just the hours with the following statements
Here is the syntax to sum hours and minutes
Select sum(datepart(hour,convert(datetime,Duration))) from
reports.PhoneUsage /* sum hours*/
Select sum(datepart(minute,convert(datetime,Duration)))
from reports.PhoneUsage /* sum minutes */
but I would like to be able to produce a final result like
duration = 00:01:15 not just add one of the three hour,
minute, second
Thank you for your time and expertise!CREATE TABLE PhoneUsage (duration CHAR(8) PRIMARY KEY CHECK (duration LIKE
('[0-9][0-9]:[0-9][0-9]:[0-9][0-9]')))
INSERT INTO PhoneUsage VALUES ('00:00:25')
INSERT INTO PhoneUsage VALUES ('00:01:30')
SELECT SUM(DATEDIFF(SECOND,'19000101','1900-01-01T'+duration)) AS
total_seconds
FROM PhoneUsage
or:
SELECT
CONVERT(VARCHAR,DATEADD(SECOND,SUM(DATEDIFF(SECOND,'19000101','1900-01-01T'+
duration)),'19000101'),8) AS total_duration
FROM PhoneUsage
--
David Portas
--
Please reply only to the newsgroup
--|||Thank You for your help!
I really appreciate it. I am new to SQL Server and
still learning. I have gone through the steps you show
1. CREATE TABLE PhoneUsage (duration CHAR(8) PRIMARY KEY
CHECK (duration LIKE
>('[0-9][0-9]:[0-9][0-9]:[0-9][0-9]')))
>INSERT INTO PhoneUsage VALUES ('00:00:25')
>INSERT INTO PhoneUsage VALUES ('00:01:30')
2. SELECT SUM(DATEDIFF(SECOND,'19000101','1900-01-
01T'+duration)) AS
>total_seconds
> FROM PhoneUsage
or
SELECT
>CONVERT(VARCHAR,DATEADD(SECOND,SUM(DATEDIFF
(SECOND,'19000101','1900-01-01T'+
>duration)),'19000101'),8) AS total_duration
> FROM PhoneUsage
After creating the table, I now query and recieve a error
message telling me that I have a
Syntax error converting datetime from character string.
Would you might know what I have done wrong?
Thanks again for your help,
Mike

Adding Time In DateTime Field

Hi

I'm trying to add a time from a DateTime field to provide a total. Eg:

Field1

01/02/2007 01:00:00PM

01/03/2007 01:45:00PM

01/04/2007 03:00:00PM

I want to add the time so I get a total of 05:45. The total hours could go over 24. I know I can't Sum it. I've seen several examples of how to do this but can't make any of them work. Could someone please point me in the right direction?

Thanks

set @.d1='01/02/2007 01:00:00PM'

datepart(hh,@.d1) return : 1

DATEADD ( hour, datepart(hh,@.d1), YourDate ) add 1 hour to your data

|||

Please check your objective. All of the times you list are afternoon times. The sum of the time component for all of these is the 5:45 plus an additional 36 hours. If your answer is correct, there is more to it than just summing the time components.

Code Snippet

declare @.aTable table (field1 datetime)
insert into @.aTable
select '01/02/2007 01:00:00PM' union all
select '01/03/2007 01:45:00PM' union all
select '01/04/2007 03:00:00PM'

select datediff(day, 0, sumOfTime) as Days,
convert(varchar, sumOfTime, 114) as Time
from ( select cast(sum(cast(field1 as float)
-floor(cast(field1 as float)))as datetime)
as sumOfTime
from @.aTable
) x

/*
Days Time
--
1 17:44:59:997
*/

|||

From the nature of your question, and looking at your sample data, I assume that the sample data represents 'elapsed time' on a date. And that 01:45PM means 1 hour and 45 minutes elapsed time -NOT 13:45 o'clock.

To calculate the total 'elapsed time', it would have been so much easier if you were storing the StartDateTime and EndDateTime -then it would be relatively simple date arithematic.

If my assumptions are correct, AND you cannot re-engineer the data to collect Start/End datetime values, this will be a bit more effort.

Please confirm.

|||

Sorry, I should have been clearer. The time is just a time, the date is irrelevant. It's actually a travel time, so Arnie you're correct, it is an elapsed time. I'm purely interested in adding the hours together. So in the 3 lines of sample data they travelled for 1 hour, 1 hour 45 minutes and 3 hours. AM/PM is also irrelevant. Travel time will never go over 12 hours. So the total I want is 5:45.

I'm working with someone else's data and tables here, personally I wouldn't have used a datetime field for this data but that is what I have. I also agree it would be better to have a start and end time, but I don't.

One possible way could be to extract the time, convert it to minutes, add those minutes together and then convert it back to hours and minutes...possibly? Any ideas?

|||

YOu could use this function:

Code Snippet

CREATE FUNCTION dbo.TimeDiffInHoursAndMinutes
(
@.Firstdate DATETIME,
@.Seconddate DATETIME
)
/*
Function written by Jens K. Suessmeyer, 07/22/2007
http://www.sqlserver2005.de
*/
RETURNS VARCHAR(10)
AS
BEGIN

DECLARE @.FirstdateMinutes INT
DECLARE @.SeconddateMinutes INT

SELECT @.FirstdateMinutes = DATEPART (hh,@.Firstdate)*60 + DATEPART(mi,@.Firstdate)
SELECT @.SeconddateMinutes = DATEPART (hh,@.Seconddate)*60 + DATEPART(mi,@.Seconddate)

RETURN (
SELECT
CONVERT(VARCHAR(10), FLOOR(@.SeconddateMinutes-@.FirstdateMinutes) / 60) +
':' +
RIGHT('00' + CONVERT(VARCHAR(10), (@.SeconddateMinutes-@.FirstdateMinutes) - FLOOR((@.SeconddateMinutes-@.FirstdateMinutes) / 60)*60),2))
END;

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

This 'should' move you in the direction you want. (I've added another row to the sample data.)

Code Snippet

DECLARE @.MyTable table
( RowID int IDENTITY,
TravelTime smalldatetime
)

INSERT INTO @.MyTable VALUES ( '01/02/2007 01:00:00PM' )
INSERT INTO @.MyTable VALUES ( '01/03/2007 01:45:00PM' )
INSERT INTO @.MyTable VALUES ( '01/04/2007 03:00:00PM' )
INSERT INTO @.MyTable VALUES ( '01/04/2007 01:45:00PM' )

SELECT
Hours = sum( cast( parsename( replace( left( right( convert( varchar(20), TravelTime, 100 ), 7 ), 5 ), ':', '.' ), 2 ) AS int )) +
( sum( cast( parsename( replace( left( right( convert( varchar(20), TravelTime, 100 ), 7 ), 5 ), ':', '.' ), 1 ) AS int )) / 60 ) ,
Mins = ( sum( cast( parsename( replace( left( right( convert( varchar(20), TravelTime, 100 ), 7 ), 5 ), ':', '.' ), 1 ) AS int )) % 60 )
FROM @.MyTable

Hours Mins
-- --
7 30

|||Thank you so much Arnie that seems perfect. |||In addition and althrough already closed, I found something on my blog (didn′t know that I wrote such a thing yet :-) )

http://www.sqlserver2005.de/sqlserver2005/MyBlog/tabid/56/EntryID/31/Default.aspx

Jens K. Suessmeyer

http://www.sqlserver2005.de

Adding Time In DateTime Field

Hi

I'm trying to add a time from a DateTime field to provide a total. Eg:

Field1

01/02/2007 01:00:00PM

01/03/2007 01:45:00PM

01/04/2007 03:00:00PM

I want to add the time so I get a total of 05:45. The total hours could go over 24. I know I can't Sum it. I've seen several examples of how to do this but can't make any of them work. Could someone please point me in the right direction?

Thanks

set @.d1='01/02/2007 01:00:00PM'

datepart(hh,@.d1) return : 1

DATEADD ( hour, datepart(hh,@.d1), YourDate ) add 1 hour to your data

|||

Please check your objective. All of the times you list are afternoon times. The sum of the time component for all of these is the 5:45 plus an additional 36 hours. If your answer is correct, there is more to it than just summing the time components.

Code Snippet

declare @.aTable table (field1 datetime)
insert into @.aTable
select '01/02/2007 01:00:00PM' union all
select '01/03/2007 01:45:00PM' union all
select '01/04/2007 03:00:00PM'

select datediff(day, 0, sumOfTime) as Days,
convert(varchar, sumOfTime, 114) as Time
from ( select cast(sum(cast(field1 as float)
-floor(cast(field1 as float)))as datetime)
as sumOfTime
from @.aTable
) x

/*
Days Time
--
1 17:44:59:997
*/

|||

From the nature of your question, and looking at your sample data, I assume that the sample data represents 'elapsed time' on a date. And that 01:45PM means 1 hour and 45 minutes elapsed time -NOT 13:45 o'clock.

To calculate the total 'elapsed time', it would have been so much easier if you were storing the StartDateTime and EndDateTime -then it would be relatively simple date arithematic.

If my assumptions are correct, AND you cannot re-engineer the data to collect Start/End datetime values, this will be a bit more effort.

Please confirm.

|||

Sorry, I should have been clearer. The time is just a time, the date is irrelevant. It's actually a travel time, so Arnie you're correct, it is an elapsed time. I'm purely interested in adding the hours together. So in the 3 lines of sample data they travelled for 1 hour, 1 hour 45 minutes and 3 hours. AM/PM is also irrelevant. Travel time will never go over 12 hours. So the total I want is 5:45.

I'm working with someone else's data and tables here, personally I wouldn't have used a datetime field for this data but that is what I have. I also agree it would be better to have a start and end time, but I don't.

One possible way could be to extract the time, convert it to minutes, add those minutes together and then convert it back to hours and minutes...possibly? Any ideas?

|||

YOu could use this function:

Code Snippet

CREATE FUNCTION dbo.TimeDiffInHoursAndMinutes
(
@.Firstdate DATETIME,
@.Seconddate DATETIME
)
/*
Function written by Jens K. Suessmeyer, 07/22/2007
http://www.sqlserver2005.de
*/
RETURNS VARCHAR(10)
AS
BEGIN

DECLARE @.FirstdateMinutes INT
DECLARE @.SeconddateMinutes INT

SELECT @.FirstdateMinutes = DATEPART (hh,@.Firstdate)*60 + DATEPART(mi,@.Firstdate)
SELECT @.SeconddateMinutes = DATEPART (hh,@.Seconddate)*60 + DATEPART(mi,@.Seconddate)

RETURN (
SELECT
CONVERT(VARCHAR(10), FLOOR(@.SeconddateMinutes-@.FirstdateMinutes) / 60) +
':' +
RIGHT('00' + CONVERT(VARCHAR(10), (@.SeconddateMinutes-@.FirstdateMinutes) - FLOOR((@.SeconddateMinutes-@.FirstdateMinutes) / 60)*60),2))
END;

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

This 'should' move you in the direction you want. (I've added another row to the sample data.)

Code Snippet

DECLARE @.MyTable table
( RowID int IDENTITY,
TravelTime smalldatetime
)

INSERT INTO @.MyTable VALUES ( '01/02/2007 01:00:00PM' )
INSERT INTO @.MyTable VALUES ( '01/03/2007 01:45:00PM' )
INSERT INTO @.MyTable VALUES ( '01/04/2007 03:00:00PM' )
INSERT INTO @.MyTable VALUES ( '01/04/2007 01:45:00PM' )

SELECT
Hours = sum( cast( parsename( replace( left( right( convert( varchar(20), TravelTime, 100 ), 7 ), 5 ), ':', '.' ), 2 ) AS int )) +
( sum( cast( parsename( replace( left( right( convert( varchar(20), TravelTime, 100 ), 7 ), 5 ), ':', '.' ), 1 ) AS int )) / 60 ) ,
Mins = ( sum( cast( parsename( replace( left( right( convert( varchar(20), TravelTime, 100 ), 7 ), 5 ), ':', '.' ), 1 ) AS int )) % 60 )
FROM @.MyTable

Hours Mins
-- --
7 30

|||Thank you so much Arnie that seems perfect. |||In addition and althrough already closed, I found something on my blog (didn′t know that I wrote such a thing yet :-) )

http://www.sqlserver2005.de/sqlserver2005/MyBlog/tabid/56/EntryID/31/Default.aspx

Jens K. Suessmeyer

http://www.sqlserver2005.de

Adding time

Hi,
I have a table with process_id and process_started_at and
process_completed_at which are both datetime datatype.
I need to calculate the total time taken for a particular process for a
given date-range.
and calculate average time per day per process_id.
How can I do it I have MS SQL 2003.
If I add the 2 datetime variables it increments the day by 1 after
every 24 hrs but that is not what I want.
Example if I have
date1 = '2005-01-01 20:20:30'
date2 = '2005-01-01 20:25:20'
then I want the result as '40:45:50' I am not concerned with the date
part.
I tried creating a function which would add the two times but then how
do I get the average? Do I have to write another function which will
convert the total time into seconds and then devide by total number of
days and then convert back to hr:min:sec or is there is easier way to
do it?
Does MS SQL have any simple methode which will convert the time to
seconds and seconds to hr:min:sec?
Thanks for your time and expertise
Ashoo> Does MS SQL have any simple methode which will convert the time to
> seconds and seconds to hr:min:sec?
For the latter, see http://www.aspfaq.com/2271

Adding the Same Column to a Model More Than One Time

I know that I can add a column to a mining structure and/or model more than once using XMLA. However, I am having a problem doing the same using DMX. I now that DMX does not support data binding and that it relies on the INSERT INTO... construct to essentially map the structure columns to the incoming comluns from the data source. I tried to use "AS" like I would in SQL but it doesn't seem to like it.

Can anyone confirm that this can only be done using XMLA and not DMX? If I am mistaken, it would be greatly appreciated if you could provide an example on how to do this using DMX.

For background on why I would want to add the same column to a model more than once please read this post.

Thanks.

It should be fairly straightforward, is this not working?

CREATE MINING STRUCTURE Foo
(
CustID LONG KEY,
Age LONG CONTINUOUS,
AgeDisc LONG DISCRETIZED)

INSERT INTO Foo(CustID, Age, AgeDisc)
OPENQUERY(MyDataSource,"SELECT CustID, Age, Age as Age2 FROM MyTable")

|||Sorry Jamie. I was actually about to delete the post as I realized my obvious oversight.

Adding the Same Column to a Model More Than One Time

I know that I can add a column to a mining structure and/or model more than once using XMLA. However, I am having a problem doing the same using DMX. I now that DMX does not support data binding and that it relies on the INSERT INTO... construct to essentially map the structure columns to the incoming comluns from the data source. I tried to use "AS" like I would in SQL but it doesn't seem to like it.

Can anyone confirm that this can only be done using XMLA and not DMX? If I am mistaken, it would be greatly appreciated if you could provide an example on how to do this using DMX.

For background on why I would want to add the same column to a model more than once please read this post.

Thanks.

It should be fairly straightforward, is this not working?

CREATE MINING STRUCTURE Foo
(
CustID LONG KEY,
Age LONG CONTINUOUS,
AgeDisc LONG DISCRETIZED)

INSERT INTO Foo(CustID, Age, AgeDisc)
OPENQUERY(MyDataSource,"SELECT CustID, Age, Age as Age2 FROM MyTable")

|||Sorry Jamie. I was actually about to delete the post as I realized my obvious oversight.

adding sql server to a group,

hello, i am try to registering a server, in the first time, it let me register with wizard, but i happen check the box "From now on, i want to perform this task without using a wizard", when i add later, it won't show up the wizard, How can i change back to using wizard to register? Thanks.Highlight the group and locate a tool bar button up at the top that has a tool tip "Run a Wizard". Click on it and uncheck "From now on, i want to perform this task without using a wizard" check box.|||thanks, i got it.

adding sql db

hey i have a very simple form here:www.syscpupower.com

I would like to have the information from the form saved to a sql db with a time stamp.

here is the code i have for the page below:

"C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>"-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml" >"server"> "form1" runat="server">
  
"z-index: 108; left: 0px; position: absolute; top: 189px">  
"z-index: 109; left: 204px; width: 411px; position: absolute; top: 18px; height: 160px">"3" rowspan="3" style="text-align: center"> Please accept to view your detailed trip information.
 "Button1" runat="server" OnClick="Button1_Click" Style="z-index: 102; left: 322px; position: absolute; top: 261px" Text="Accept" Width="60px"> "Button2" runat="server" OnClick="Button2_Click" Style="z-index: 103; left: 392px; position: absolute; top: 261px" Text="Decline" Width="64px"> "Label1" runat="server" Style="z-index: 104; left: 254px; position: absolute; top: 198px" Text="Please Enter Your First and Last Name!" Width="273px"> "TextBox1" runat="server" Style="z-index: 105; left: 278px; position: absolute; top: 229px" Width="216px"> "RequiredFieldValidator1" runat="server" ControlToValidate="TextBox1" ErrorMessage="FirstName LastName" Font-Size="12px" Style="z-index: 106; left: 500px; position: absolute; top: 229px">* "ValidationSummary1" runat="server" Style="z-index: 110; left: 263px; position: absolute; top: 300px" Width="284px">

sorry, what exactly you want?

you want to know how to insert information to db?

or you want to know how to create db for it?

|||well i created the db, now i guess i need to findo out how to get the info from the text box into the database...|||

using (SqlConnection sc = new SqlConnection("Data Source=(local);Initial Catalog=AAAA;Integrated Security=SSPI;"))
{

string strSQL = "INSERT INTO tbXXXX(NAME) VALUES(TextBox1.text)
SqlCommand command = new SqlCommand(strSQL, sc);
command.Connection.Open();
command.ExecuteNonQuery()
}

|||

i have added the SQL database, THATS it...

I see the code that you have put here can you please explain the coding, how did you get this and where does it go in the coding. alsodi i need to create tables inside my db.

|||

Hey

You could double click button in design mode and add above code.

Above code follows those steps:

Create a connection -> build the sql -> create sql command ->open connection ->exec insert-> close command.

And to prevent Sql Inject you could choose sql parameter,it could be something like this:

SqlCommand cmd = new SqlCommand("select column1 from test where column1 = @.column1", conn);

cmd.Parameters.AddWithValue("@.column1", str_test);

If you are in trouble understanding the codeStarter Books might be helpful.

Adding sql datafile

My existing database size is 90GB. Due to this backup,restore and quering
take more time to execute. So I want to know have any chance to
split the existing database into two (or)
how to add one more datafile with existing datafile.
By adding datafile, is the performence will improve?Hi Durai,
You can try the following as the first step towards improving the database
performance...
(a) Place transaction logs on a drive different from datafiles, especially
if your database transaction includes lots of DML operations.
(b) Make use of FILEGROUP, and put files in different drive.
(c) If possible, Tempdb drive should be separated from datafile drive.
With filegroups in place, your backup management (especially backup time)
will be efficient. Try to read more on filegroups and how to create them.
I assume that you haven't placed your files in the RAID environment where a
physical disk is split into logical drive. Then place the files in different
logical drive will not give much of the performance boost.
--
- - - - - - - - -
Thanks
Yogish
"durai" wrote:
> My existing database size is 90GB. Due to this backup,restore and quering
> take more time to execute. So I want to know have any chance to
> split the existing database into two (or)
> how to add one more datafile with existing datafile.
> By adding datafile, is the performence will improve?
>|||Hi,
If you have 2 disk controllers, then placing new file in a different
controller will reduce the disk I/O and will increase the performance.
As well as when you backup you could in to multiple files... This will speed
up your backup timings.
BACKUP DATABASE xyz to DISK='F:\...', DISK = 'G:\...' etc.
Thanks
Hari
SQL Server MVP
"durai" <durai@.discussions.microsoft.com> wrote in message
news:55588715-1EE5-4593-A592-10E881A1A78A@.microsoft.com...
> My existing database size is 90GB. Due to this backup,restore and quering
> take more time to execute. So I want to know have any chance to
> split the existing database into two (or)
> how to add one more datafile with existing datafile.
> By adding datafile, is the performence will improve?
>

Adding sql datafile

My existing database size is 90GB. Due to this backup,restore and quering
take more time to execute. So I want to know have any chance to
split the existing database into two (or)
how to add one more datafile with existing datafile.
By adding datafile, is the performence will improve?Hi Durai,
You can try the following as the first step towards improving the database
performance...
(a) Place transaction logs on a drive different from datafiles, especially
if your database transaction includes lots of DML operations.
(b) Make use of FILEGROUP, and put files in different drive.
(c) If possible, Tempdb drive should be separated from datafile drive.
With filegroups in place, your backup management (especially backup time)
will be efficient. Try to read more on filegroups and how to create them.
I assume that you haven't placed your files in the RAID environment where a
physical disk is split into logical drive. Then place the files in different
logical drive will not give much of the performance boost.
- - - - - - - - -
Thanks
Yogish
"durai" wrote:

> My existing database size is 90GB. Due to this backup,restore and quering
> take more time to execute. So I want to know have any chance to
> split the existing database into two (or)
> how to add one more datafile with existing datafile.
> By adding datafile, is the performence will improve?
>|||Hi,
If you have 2 disk controllers, then placing new file in a different
controller will reduce the disk I/O and will increase the performance.
As well as when you backup you could in to multiple files... This will speed
up your backup timings.
BACKUP DATABASE xyz to DISK='F:\...', DISK = 'G:\...' etc.
Thanks
Hari
SQL Server MVP
"durai" <durai@.discussions.microsoft.com> wrote in message
news:55588715-1EE5-4593-A592-10E881A1A78A@.microsoft.com...
> My existing database size is 90GB. Due to this backup,restore and quering
> take more time to execute. So I want to know have any chance to
> split the existing database into two (or)
> how to add one more datafile with existing datafile.
> By adding datafile, is the performence will improve?
>

Adding sql datafile

My existing database size is 90GB. Due to this backup,restore and quering
take more time to execute. So I want to know have any chance to
split the existing database into two (or)
how to add one more datafile with existing datafile.
By adding datafile, is the performence will improve?
Hi Durai,
You can try the following as the first step towards improving the database
performance...
(a) Place transaction logs on a drive different from datafiles, especially
if your database transaction includes lots of DML operations.
(b) Make use of FILEGROUP, and put files in different drive.
(c) If possible, Tempdb drive should be separated from datafile drive.
With filegroups in place, your backup management (especially backup time)
will be efficient. Try to read more on filegroups and how to create them.
I assume that you haven't placed your files in the RAID environment where a
physical disk is split into logical drive. Then place the files in different
logical drive will not give much of the performance boost.
- - - - - - - - -
Thanks
Yogish
"durai" wrote:

> My existing database size is 90GB. Due to this backup,restore and quering
> take more time to execute. So I want to know have any chance to
> split the existing database into two (or)
> how to add one more datafile with existing datafile.
> By adding datafile, is the performence will improve?
>
|||Hi,
If you have 2 disk controllers, then placing new file in a different
controller will reduce the disk I/O and will increase the performance.
As well as when you backup you could in to multiple files... This will speed
up your backup timings.
BACKUP DATABASE xyz to DISK='F:\...', DISK = 'G:\...' etc.
Thanks
Hari
SQL Server MVP
"durai" <durai@.discussions.microsoft.com> wrote in message
news:55588715-1EE5-4593-A592-10E881A1A78A@.microsoft.com...
> My existing database size is 90GB. Due to this backup,restore and quering
> take more time to execute. So I want to know have any chance to
> split the existing database into two (or)
> how to add one more datafile with existing datafile.
> By adding datafile, is the performence will improve?
>

Adding Milliseconds to Time

Hi,
i am trying to add milliseconds to a time. For example if i have a time of 01:01:05:000 and i want to add 0.297 milliseconds to it i use the following simplified query

SELECT CONVERT(nvarchar(20), DATEADD(ms, 0.297, '00:01:05:000'), 14) AS Expr1

However instead of getting 01:01:05:0.297 i get 01:01:05:000. Can somebody please tell me what i am doing wrong.

Thanks in advance.Try:

SELECT CONVERT(nvarchar(20), DATEADD(ms, 297, '00:01:05:000'), 14) AS Expr1

Terri|||hi tmorton, thanks for the reply, however what if the vlaue to add in milliseconds is 0.297? How would i change the sql query?|||0.297 milliseconds, as in 0.000297 seconds? Sorry, SQL Server is not that granular. SQL Server is accurate only down to 3.33 millseconds. You can read up ondatetime and smalldatetime (Transact-SQL Reference (SQL Server)).

Terri|||ok got it working,

SELECT CONVERT(varchar(20), DATEADD(ms, Exec_Time * 1000, CONVERT(varchar(20), [date], 14)), 14)
 AS Expr1
FROM llserverlogs
WHERE (Server = 'llkwa001-ukbg')
ORDER BY [date]

thanks for the help

Adding many templates at once

I can’t find out how to put SQL 2000 templates that I’ve already created into SQL 2005, en masse. I don’t want to put them in one at a time. I’ve got 40 or 50 of them.

Can you please direct me to the location I can paste them in and have them show up.

I’ve tried this location, where the ones built-in exist:
C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\sqlworkbenchprojectitems\Sql

When I put my own folder in there with my custom templates, they don’t show up and there is no REFRESH button on the Template View.

When I saw that existing ones had the .sql extension, I renamed them from .tql to .sql, but that did not make any difference.

Thanks for any help.

I found that when I closed SSMS and then opened it, all my templates appeared as expected.

I suggest adding a refresh button to the templates.

Adding folders in the solution explorer?

Hi..
Isn′t it possible to create sub folders in the solution explorer?
I have it hard time to create a decent structure inside the SSIS Package folder, since I have like 30 packages in my project. Is it just me being silly or cannot this be done?
Have a nice day
/Erik

nope...

You can add folders to a solution but withing a project, everything is either in packages of misc.

|||ok, thanks for the input though

Adding entry to DB and getting unique ID at the same time

Hi all,

I'm writing a website with Cold Fusion and when a user submits a
request and it's stored in the MS SQL database, I want the unique ID
(Identity field in table) to be given to the user on screen plus
emailed to user.

Now can I store data to the database (where the ID is created) and
return this as a variable in the same statement? I've seen this done
on many websites, but I have no idea how to do it in one step.

Thanks,

Alex.Return to the user the value from @.@.identity (check this out in the BOL).
You can also have SQL trigger off the email if this fits within your
project's stated performance requirements. Send the email via
xp_smtp_sendmail(http://sqldev.net/xp/xpsmtp.htm) But basically your
requirement to do it in 1 step could all be handled with a stored procedure.

hth
Eric

"Alex" <alex@.totallynerd.com> wrote in message
news:2ba4b4eb.0401291136.6ec0ee16@.posting.google.c om...
> Hi all,
> I'm writing a website with Cold Fusion and when a user submits a
> request and it's stored in the MS SQL database, I want the unique ID
> (Identity field in table) to be given to the user on screen plus
> emailed to user.
> Now can I store data to the database (where the ID is created) and
> return this as a variable in the same statement? I've seen this done
> on many websites, but I have no idea how to do it in one step.
> Thanks,
> Alex.

Adding DEFAULT columns

Hi

I have a table that currently has 466 columns and about 700,000
records. Adding a new DEFAULT column to this table takes a long time.

It it a lot faster to recreate the table with the new columns and then
copy all of the data across.

As far as I am aware when you add a DEFAULT column the following
happens:

a) The column is added with a NULL property
b) Each row is updated to be set to the DEFAULT value
c) The column is changed to NOT NULL.

However, adding the column as NOT NULL with the DEFAULT seems to take a
lot longer than if I do steps a) - c) separately.

When I say a long time, adding just a single DEFAULT column takes
around 6 hours. Surely it should not take this long?

There is a trigger on this table but disabling this does not seem to
make much difference.

Can anybody give me any advice on the use of DEFAULT columns please?
When should they be used, benefits, disadvantages, alternatives etc.
Also should it really take as long as it is taking or is there a
problem with my setup?

If I am honest I can't see why DEFAULT columns should be used as the
values could always be inserted explicitly via the application
Thanks in Advance.

PaulI think you may be able to speed it up by using the NoCheck option, so it
doesn't look at the existing data.

I didn't know that specifying a default updates the null values in the
table - so you are either incorrect, or I have an incomplete understanding.

But I am pretty sure that having so many columns isn't helping you in the
least bit, and may be the real reason.

Splitting the tables apart and using a view to be backwards compatible (with
an 'instead of ' trigger for updates) - aught to make things go faster.
Especially if all or the most frequent searchable columns stay in one of the
new tables (a hub table as it were).

If the data values are ALWAYS explicitly added ALL the time, there is no
reason for a default.
You may need to beat up on some wayward programmer to guarantee that they
get populated each and every time with the correct values - But since I
don't like violence (nor the testing to find the problem), I add defaults
to guarantee that happens regardless. Also it is possible to do an insert
without a column list and specify defaults (so that all the columns get the
default values), which could be useful in some instances.

"Paul" <paulwragg2323@.hotmail.com> wrote in message
news:1103024817.227784.219090@.z14g2000cwz.googlegr oups.com...
> Hi
> I have a table that currently has 466 columns and about 700,000
> records. Adding a new DEFAULT column to this table takes a long time.
> It it a lot faster to recreate the table with the new columns and then
> copy all of the data across.
> As far as I am aware when you add a DEFAULT column the following
> happens:
> a) The column is added with a NULL property
> b) Each row is updated to be set to the DEFAULT value
> c) The column is changed to NOT NULL.
> However, adding the column as NOT NULL with the DEFAULT seems to take a
> lot longer than if I do steps a) - c) separately.
> When I say a long time, adding just a single DEFAULT column takes
> around 6 hours. Surely it should not take this long?
> There is a trigger on this table but disabling this does not seem to
> make much difference.
> Can anybody give me any advice on the use of DEFAULT columns please?
> When should they be used, benefits, disadvantages, alternatives etc.
> Also should it really take as long as it is taking or is there a
> problem with my setup?
> If I am honest I can't see why DEFAULT columns should be used as the
> values could always be inserted explicitly via the application
> Thanks in Advance.
> Paul|||Thanks for the response David.

I have suggested that we split this table up and I think this will be
eventually done (it's a case of having the time up front to do this).

I wasn't saying that adding a DEFAULT value to the column updates
existing data - rather that when a new DEFAULT column is added it
follows the steps a) - c) in order to add the new column.

As the table is so large I think I need to investigate the way the data
for this table is actually stored. At present we have no clustered
index on this table which is probably also contributing to the problem.
I don't know too much about the way the data is stored to be honest!|||OK, I get it now, you are adding a column and not just binding a new default
to an existing column.

Copy into the new table, drop the original, and do a sp_rename.
While you are at it break the table apart - If you can't find the time to do
it right, when will you find the time to do over and over incorrectly.

Get one of those MCSD prep books for the SQL Server Design Exam to find out
how stuff gets stored in a database. Their 1st or 2nd chapter normally goes
over devices, extents, pages and all that stuff.

I am sure there are a boat load of free sources on the Web on that as well.

"Paul" <paulwragg2323@.hotmail.com> wrote in message
news:1103033046.110249.69780@.f14g2000cwb.googlegro ups.com...
> Thanks for the response David.
> I have suggested that we split this table up and I think this will be
> eventually done (it's a case of having the time up front to do this).
> I wasn't saying that adding a DEFAULT value to the column updates
> existing data - rather that when a new DEFAULT column is added it
> follows the steps a) - c) in order to add the new column.
> As the table is so large I think I need to investigate the way the data
> for this table is actually stored. At present we have no clustered
> index on this table which is probably also contributing to the problem.
> I don't know too much about the way the data is stored to be honest!|||Paul (paulwragg2323@.hotmail.com) writes:
> It it a lot faster to recreate the table with the new columns and then
> copy all of the data across.

Is that a question or a statement? Which "It" is a typo for "is"?

> When I say a long time, adding just a single DEFAULT column takes
> around 6 hours. Surely it should not take this long?

Just because "ALTER TABLE tbl ADD col DEFAULT 0" is easy to type, that
does not mean that it executes equally fast. There is a lot of work to
be done - since all rows expand, basically all pages have to be written.

In our shop we do all table changes the long way - rename, create new,
insert over, move foreign keys, drop old. We have a build that generates
a skeleton for this manoeuvre. One reason we do this is that ALTER TABLE
only can handle some changes, and you can not insert columns in the
middle with. (And our scheme was established in 6.5 when you could do
even less with ALTER TABLE.)

Generally, I would not expect reload of a 700000 rows table, not even
that wide to take six hours. Also, when moving over, you can do that
in chunks.

> There is a trigger on this table but disabling this does not seem to
> make much difference.

The trigger is not fired when you to ALTER TABLE. Note that if you do
the long way, you will need to recreate the trigger. Whether you do
that before or after you reload the data depends on whether you want
the checks in the trigger to be performed (I usually want to). But for
performance, it's best to recreate the trigger after the data move.

> Can anybody give me any advice on the use of DEFAULT columns please?
> When should they be used, benefits, disadvantages, alternatives etc.
> Also should it really take as long as it is taking or is there a
> problem with my setup?

If you need to add to existing column to a database, and you don't want
NULL values in the column, the a default value is a good way to go, to
avoid problems with existing software that writes to this table. And,
even if existing software is rewritten - it may after all be a single
GUI form - existing data needs to be handled.

Sometimes NULL values can be feasible, but for instance a bit column
is typically NOT NULL. I think the choice should be made from the
anticpated use in the future, and not what is the most convenient
right now.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||A technique that I sometimes use with large tables is SELECT ... INTO
followed by a drop and a rename. This is minimally logged in the SIMPLE or
BULK_LOGGED recovery model.

Whether or not this is faster depends on the particulars of the changes made
and the indexes that need to be rebuilt.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Paul" <paulwragg2323@.hotmail.com> wrote in message
news:1103024817.227784.219090@.z14g2000cwz.googlegr oups.com...
> Hi
> I have a table that currently has 466 columns and about 700,000
> records. Adding a new DEFAULT column to this table takes a long time.
> It it a lot faster to recreate the table with the new columns and then
> copy all of the data across.
> As far as I am aware when you add a DEFAULT column the following
> happens:
> a) The column is added with a NULL property
> b) Each row is updated to be set to the DEFAULT value
> c) The column is changed to NOT NULL.
> However, adding the column as NOT NULL with the DEFAULT seems to take a
> lot longer than if I do steps a) - c) separately.
> When I say a long time, adding just a single DEFAULT column takes
> around 6 hours. Surely it should not take this long?
> There is a trigger on this table but disabling this does not seem to
> make much difference.
> Can anybody give me any advice on the use of DEFAULT columns please?
> When should they be used, benefits, disadvantages, alternatives etc.
> Also should it really take as long as it is taking or is there a
> problem with my setup?
> If I am honest I can't see why DEFAULT columns should be used as the
> values could always be inserted explicitly via the application
> Thanks in Advance.
> Paul

Adding days and setting time

Hi,
Can someone please help me with a SQL Server 2005 issue with date and
time.
I want to take the current date and time and add 2 days to it, but the
time must be set to 5pm.
If the current date/time is past 5pm it will go to the next day at 5pm.
So if the date was:
2006-06-23 15:55:46.337 then the new date should say 2006-06-25
17:00:46.337
If the current time was past 5pm then it should be as follows:
2006-06-23 19:55:46.337 then the new date should say 2006-06-26
17:00:46.337
Notice the day is an extra day because 5pm has already gone by hence it
has to go to the next 5pm, which is the next day.
Thanks.
SimonTry this:
SELECT DATEADD(DAY, (CASE
WHEN DATEPART(HOUR, CURRENT_TIMESTAMP) < 17 THEN 2
ELSE 3
END), DATEADD(HOUR, 17, CAST(CONVERT(char(8), CURRENT_TIMESTAMP, 112) AS
DATETIME)));
HTH
Vern Rabe
"simon_s_li@.hotmail.com" wrote:

> Hi,
> Can someone please help me with a SQL Server 2005 issue with date and
> time.
> I want to take the current date and time and add 2 days to it, but the
> time must be set to 5pm.
> If the current date/time is past 5pm it will go to the next day at 5pm.
> So if the date was:
> 2006-06-23 15:55:46.337 then the new date should say 2006-06-25
> 17:00:46.337
> If the current time was past 5pm then it should be as follows:
> 2006-06-23 19:55:46.337 then the new date should say 2006-06-26
> 17:00:46.337
> Notice the day is an extra day because 5pm has already gone by hence it
> has to go to the next 5pm, which is the next day.
> Thanks.
> Simon
>|||Something like:
DECLARE @.Date1 DATETIME;
SELECT @.Date1 = GETDATE();
PRINT @.Date1;
SET @.Date1 = CASE WHEN DATEPART(HH, @.date1) > 17 THEN DATEADD(DAY, 1,
@.Date1) ELSE @.Date1 END;
SET @.Date1 = DATEADD(HH, (17-DATEPART(HH, @.Date1)), @.Date1);
PRINT @.Date1;|||I just realized that you apparently want to retain the current seconds and
milliseconds, to be added to 5:00 PM. Seems strange, but to do that, this
should work:
SELECT DATEADD(DAY, (CASE
WHEN DATEPART(HOUR, CURRENT_TIMESTAMP) < 17 THEN 2
ELSE 3
END), DATEADD(HOUR, 17, CAST(CONVERT(varchar(10), CURRENT_TIMESTAMP, 110) +
' 00:00' + RIGHT(CONVERT(varchar(24), CURRENT_TIMESTAMP, 13), 7) AS
DATETIME)));
HTH
Vern Rabe
"simon_s_li@.hotmail.com" wrote:

> Hi,
> Can someone please help me with a SQL Server 2005 issue with date and
> time.
> I want to take the current date and time and add 2 days to it, but the
> time must be set to 5pm.
> If the current date/time is past 5pm it will go to the next day at 5pm.
> So if the date was:
> 2006-06-23 15:55:46.337 then the new date should say 2006-06-25
> 17:00:46.337
> If the current time was past 5pm then it should be as follows:
> 2006-06-23 19:55:46.337 then the new date should say 2006-06-26
> 17:00:46.337
> Notice the day is an extra day because 5pm has already gone by hence it
> has to go to the next 5pm, which is the next day.
> Thanks.
> Simon
>