Showing posts with label working. Show all posts
Showing posts with label working. Show all posts

Tuesday, March 27, 2012

Dimension won't add to cube?

Hi, I built a new dimension with the dimension wizard while working with a cube that has dimensions pre-exisiting. The relationship is defined and I don't see anything different about it when I look at the other dimensions.

However, when I look at the cube editor, all of the other related dimension tables are blue and the fact is yellow, but this table is white. Then I process the cube and it's doesn't show up in the cube browser as a dimension. It's like it doesn't exist!!! How can I add a new dimension so that it's visible AND usable?

Also, how do I create shared dimensions or make private dimensions shared?

THanks in advance.

Mike

mreese@.satorigroupinc

which version of SSAS r u using?|||

I figured it out. When you add a new dimension, you still have to manually add it to the dimension usage screen. Pointless and non-intuitive, but it worked.

I am using SSAS 2005. Thanks for your response.

|||

The UI will automatically set the appropriate dimension usage so that a dimension is included in appropriate measure groups when it can be determined. However, as you've discovered, there are situations where the UI is unable to figure out the correct way to join a dimension to a measure group in which case you must manually specify the relationship in the dimension usage tab as you have done.

To answer you're other question, Analysis Services 2005 no longer contains the notion of private dimensions. You can get this effect if desired by simply prefixing your dimensions with the cube name and then renaming them in the cube. However, it is generally desirable to re-use dimensions for consistency whenever possible

sql

Sunday, March 25, 2012

Dimension Relationship problems

Hi, I am facing a unique problem regarding dimension relationship, i am working on referenced relationship but its not the straight one where 1 table reference a fact table with regular relation and another table reference that table to get the values.

I have a table 'A' which reference to fact table 'FACT'. Table A contains a FK named 'ID'. i want this ID field to be referenced by two other tables X and Y. when i make a single reference the CUBE works but when i 2nd time reference it with table Y. the cube fails.

what i feels that we can only reference a table with another single table at one time and can not reference a field of the table twice with 2 different table. the data looks like

ID , Name

333 , For table 1

444, ForTable 2

Unfortunately, the data ID is in row wise values and not on column., cube always fails to process

You can create a named query in the DSV or a view in the relational source which will either join the referenced table into a single dimesion table with all the members or will split the fact table into two seperate tables each containing members from one of the dimensions. Another option is to set the UnknowMember = Visible on the two dimensions and set the NullProcessing = UnknownMember in the Advanced dialog from the Define Relationships dialog.

Thursday, March 22, 2012

difficulty in getting result.

hi all
i am working on sql reporting 2005.
i have 3 reports say A, B ,C
i want to display sum of values in column of A & sum of values in column of B in report C
How can i do this?
plz help me.
report c would have to have the same data as in reports A and B. Then you can sum them.

Wednesday, March 21, 2012

Difficult Insert where clause.

Sorry for anyone who has seen this query and dataset before but this is a
seperate question/issue which I am working on.
I am struggling trying to get my insert statement not to insert record 5
because the ToURN has been used before in a previous record(1).
Basically I am trying to write some logic which says that if the ToURN in
one record already exists in the FromURN field of a previous record then do
not insert the record.
Does anyone know how I would write the sql to do this.
RecNO MergeFromURN MergeToURN MergeDateMerged
1 100 200 15/06/1982
2 200 300 15/06/1982
3 300 400 15/06/1982
4 500 600 15/06/1982
5 700 100 15/06/1982
6 100 100 15/06/1982
7 NULL 100 15/06/1982
8 700 0 15/06/1982
So far I have the following sql but need to go that step further to stop
record 5 being inserted because 100 already has been inserted as a from urn
in record 1.
INSERT INTO MOVE (MOVEFROMURN, MOVETOURN, MOVEDATEMERGED)
SELECT MergeFromURN, MergeToURN, MIN(MergeDateMerged)
from myTable
where MergeFromURN is not null and MergeToURN is not null
and MergeFromURN <> 0 and MergeToURN <> 0 and
MergeFromURN <> MergeToURN and
(MergeFromURN not in (select MoveFromURN from Move) and MergeToURN not in
(select MoveToURN from Move))
GROUP BY MergeFromURN, MergeToURN
Order by MergeFromURN
while @.@.ROWCOUNT > 0
begin
update A set MoveToURN = B.MoveToURN
from Move A
inner join Move B on A.MoveToURN=B.MoveFromURN
end
Can anyone help me with this.Stephen
You are posted this question a few times some time ago , so people gave you
solutuion (me include), so would you mind at least posting DDL+ sample data
+ expected result
CREATE TABLE #Test
(
RecNo INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
f INT ,
t INT ,
dt DATETIME NOT NULL
)
INSERT INTO #Test (f,t,dt) VALUES (100,200,'19820615')
INSERT INTO #Test (f,t,dt) VALUES (200,300,'19820615')
INSERT INTO #Test (f,t,dt) VALUES (300,400,'19820615')
INSERT INTO #Test (f,t,dt) VALUES (500,600,'19820615')
INSERT INTO #Test (f,t,dt) VALUES (700,100,'19820615')
INSERT INTO #Test (f,t,dt) VALUES (100,100,'19820615')
INSERT INTO #Test (f,t,dt) VALUES (NULL,100,'19820615')
INSERT INTO #Test (f,t,dt) VALUES (700,0,'19820615')
GO
INSERT INTO YourTable <column lists>
SELECT f, t , dt FROM #Test
WHERE RecNo=(SELECT MIN(RecNo) FROM #Test T WHERE T.f=#Test.f)
"Stephen" <Stephen@.discussions.microsoft.com> wrote in message
news:2ED15ACC-2E2C-4382-AF55-3E2F997D7C24@.microsoft.com...
> Sorry for anyone who has seen this query and dataset before but this is a
> seperate question/issue which I am working on.
> I am struggling trying to get my insert statement not to insert record 5
> because the ToURN has been used before in a previous record(1).
> Basically I am trying to write some logic which says that if the ToURN in
> one record already exists in the FromURN field of a previous record then
> do
> not insert the record.
> Does anyone know how I would write the sql to do this.
> RecNO MergeFromURN MergeToURN MergeDateMerged
> 1 100 200 15/06/1982
> 2 200 300 15/06/1982
> 3 300 400 15/06/1982
> 4 500 600 15/06/1982
> 5 700 100 15/06/1982
> 6 100 100 15/06/1982
> 7 NULL 100 15/06/1982
> 8 700 0 15/06/1982
> So far I have the following sql but need to go that step further to stop
> record 5 being inserted because 100 already has been inserted as a from
> urn
> in record 1.
> INSERT INTO MOVE (MOVEFROMURN, MOVETOURN, MOVEDATEMERGED)
> SELECT MergeFromURN, MergeToURN, MIN(MergeDateMerged)
> from myTable
> where MergeFromURN is not null and MergeToURN is not null
> and MergeFromURN <> 0 and MergeToURN <> 0 and
> MergeFromURN <> MergeToURN and
> (MergeFromURN not in (select MoveFromURN from Move) and MergeToURN not in
> (select MoveToURN from Move))
> GROUP BY MergeFromURN, MergeToURN
> Order by MergeFromURN
> while @.@.ROWCOUNT > 0
>
----
--

>
> Can anyone help me with this.
>|||Sorry but this doesn't work as I need.
It inserts 5 rows into the table including the row. 700, 100 (5th Record).
I want to write in further logic which wouldn'd allow this record to be
inserted on the basis that the ToURN value of 100 already exists in a prior
record in the FromURN column.
Its quite hard to explain and I'm sorry for the previous posts which may be
covering the same ground.
"Uri Dimant" wrote:

> Stephen
> You are posted this question a few times some time ago , so people gave yo
u
> solutuion (me include), so would you mind at least posting DDL+ sample dat
a
> + expected result
>
> CREATE TABLE #Test
> (
> RecNo INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
> f INT ,
> t INT ,
> dt DATETIME NOT NULL
> )
> INSERT INTO #Test (f,t,dt) VALUES (100,200,'19820615')
> INSERT INTO #Test (f,t,dt) VALUES (200,300,'19820615')
> INSERT INTO #Test (f,t,dt) VALUES (300,400,'19820615')
> INSERT INTO #Test (f,t,dt) VALUES (500,600,'19820615')
> INSERT INTO #Test (f,t,dt) VALUES (700,100,'19820615')
> INSERT INTO #Test (f,t,dt) VALUES (100,100,'19820615')
> INSERT INTO #Test (f,t,dt) VALUES (NULL,100,'19820615')
> INSERT INTO #Test (f,t,dt) VALUES (700,0,'19820615')
> GO
> INSERT INTO YourTable <column lists>
> SELECT f, t , dt FROM #Test
> WHERE RecNo=(SELECT MIN(RecNo) FROM #Test T WHERE T.f=#Test.f)
>
> "Stephen" <Stephen@.discussions.microsoft.com> wrote in message
> news:2ED15ACC-2E2C-4382-AF55-3E2F997D7C24@.microsoft.com...
>
> ----
--
>
>
>|||Hi
> It inserts 5 rows into the table including the row. 700, 100 (5th Record).
No it does not , look at all columns and check it out
"Stephen" <Stephen@.discussions.microsoft.com> wrote in message
news:79692AC3-9860-40BB-9409-9F0EC9C880A5@.microsoft.com...
> Sorry but this doesn't work as I need.
> It inserts 5 rows into the table including the row. 700, 100 (5th Record).
> I want to write in further logic which wouldn'd allow this record to be
> inserted on the basis that the ToURN value of 100 already exists in a
> prior
> record in the FromURN column.
> Its quite hard to explain and I'm sorry for the previous posts which may
> be
> covering the same ground.
> "Uri Dimant" wrote:
>|||Honestly I ran it there now and it inserts 5 rows even though I don;t want i
t
to insert 700,100. Was trying to get around things without using a cursor
but i can't seem to find a way of doing this without using a cursor.
"Uri Dimant" wrote:

> Hi
> No it does not , look at all columns and check it out
>
> "Stephen" <Stephen@.discussions.microsoft.com> wrote in message
> news:79692AC3-9860-40BB-9409-9F0EC9C880A5@.microsoft.com...
>
>|||Hi
When I ran it I also go trow 5 in the result set.
This may have something to do with the order in which records are processed.
As in the options on your SQL server setup may be different to the person
who provided the solution, thus when you are running the select to insert ro
w
5 row 1 isn't necessarily in yet?
Just a guess
--
Chan
Programmer
"Stephen" wrote:
> Honestly I ran it there now and it inserts 5 rows even though I don;t want
it
> to insert 700,100. Was trying to get around things without using a cursor
> but i can't seem to find a way of doing this without using a cursor.
> "Uri Dimant" wrote:
>|||I wrote this SQL Statement based on Uri's initial SQL Statement and DDL
select DISTINCT t1.f, t1.t, t1.dt
from #Test t1
inner join #Test t2 on t1.RecNo < t2.RecNo and
t1.f != t2.t
AND t2.RecNo=(SELECT MIN(RecNo) FROM #Test T WHERE T.f = t2.f)
It returns 4 rows
f t dt
100 200 2005-07-27 07:26:33.490
200 300 2005-07-27 07:26:40.897
300 400 2005-07-27 07:26:48.523
500 600 2005-07-27 07:26:54.523
Is this what you are looking for? You should avoid cursors whenever possible
.
"Stephen" wrote:
> Honestly I ran it there now and it inserts 5 rows even though I don;t want
it
> to insert 700,100. Was trying to get around things without using a cursor
> but i can't seem to find a way of doing this without using a cursor.
> "Uri Dimant" wrote:
>|||Legend tough guy now thats the kinda sql i'm talking about!! OH YEAH!!
SKIN that one up and smoke it!!
"frank chang" wrote:
> I wrote this SQL Statement based on Uri's initial SQL Statement and DDL
> select DISTINCT t1.f, t1.t, t1.dt
> from #Test t1
> inner join #Test t2 on t1.RecNo < t2.RecNo and
> t1.f != t2.t
> AND t2.RecNo=(SELECT MIN(RecNo) FROM #Test T WHERE T.f = t2.f)
>
> It returns 4 rows
> f t dt
> 100 200 2005-07-27 07:26:33.490
> 200 300 2005-07-27 07:26:40.897
> 300 400 2005-07-27 07:26:48.523
> 500 600 2005-07-27 07:26:54.523
> Is this what you are looking for? You should avoid cursors whenever possib
le.
>
> "Stephen" wrote:
>|||Stephen, This select statement is the most appropriate one:
INSERT INTO ......
SELECT m.f, m.t , m.dt FROM #Test m
WHERE m.RecNo=(SELECT MIN(RecNo) FROM #Test T WHERE T.f=m.f)
AND
NOT EXISTS -- like MINUS operator in ORACLE, subtract the ones you don't wa
nt
(select *
from #Test t1
inner join #Test t2 on t1.RecNo < t2.RecNo
and t1.f != t2.t AND t2.f != t1.t
AND t1.RecNo=(SELECT MIN(RecNo) FROM #Test T WHERE T.f = t2.f)
AND t1.RecNo = m.RecNo)
Sorry about that. I didn't drink coffee this morning.
"Stephen" wrote:
> Legend tough guy now thats the kinda sql i'm talking about!! OH YEAH!!
> SKIN that one up and smoke it!!
> "frank chang" wrote:
>|||Stephen, The previous SQL statment had a typo in it (I cut and pasted by
mistake)
This statement may help:
SELECT m.f, m.t , m.dt FROM #Test m
WHERE m.RecNo=(SELECT MIN(RecNo) FROM #Test T WHERE T.f=m.f)
AND
NOT EXISTS // subtract set where from-urn number is already used
(select *
from #Test t1
inner join #Test t2 on t1.RecNo > t2.RecNo
and t2.f = t1.t
AND t1.RecNo=(SELECT MIN(RecNo) FROM #Test T WHERE T.f = t1.f)
AND t1.RecNo = m.RecNo)
Thnak you for your help.
"Stephen" wrote:
> Legend tough guy now thats the kinda sql i'm talking about!! OH YEAH!!
> SKIN that one up and smoke it!!
> "frank chang" wrote:
>

Difficult Combining Rows queston...followup

Greetings,
I'm working to combine rows based on a time window and I am hoping to
be able to write a stored procedure to do this for me, rather than have
parse through all this data in my program. I'm not very well versed
with T-SQL syntax.. just enough to get by selecting using inner joins,
updating and inserting... thats about it. (Hence why I am here.)
The raw data I have below looks like this:
groupID, StartTime, EndTime, Min, Max, Points
1, 2005-10-05 06:00, 2005-10-05 06:14:59, 7, 32, 13
1, 2005-10-05 06:15, 2005-10-05 06:29:59, 5, 29, 6
1, 2005-10-05 06:30, 2005-10-05 06:44:59, 5, 28, 4
1, 2005-10-05 06:45, 2005-10-05 06:59:59, 5, 29, 16
1, 2005-10-05 07:00, 2005-10-05 07:14:59, 5, 23, 13
1, 2005-10-05 07:15, 2005-10-05 07:29:59, 5, 25, 18
1, 2005-10-05 07:30, 2005-10-05 07:44:59, 5, 34, 49
1, 2005-10-05 07:45, 2005-10-05 07:59:59, 5, 31, 49
Pretty straight forward; you can see each entry is a 15 minute time
interval. What I want to be able to do is to use a view or a stored
procedure to view this in grouped chunks, like below
30 minute chunks
groupID, StartTime, EndTime, Min, Max, Points
1, 2005-10-05 06:00, 2005-10-05 06:29:59, 5, 32, 19
1, 2005-10-05 06:30, 2005-10-05 06:59:59, 5, 29, 20
1, 2005-10-05 07:00, 2005-10-05 07:29:59, 5, 25, 36
1, 2005-10-05 07:30, 2005-10-05 07:59:59, 5, 34, 98
1 hour chunks
groupID, StartTime, EndTime, Min, Max, Points
1, 2005-10-05 06:00, 2005-10-05 06:59:59, 5, 32, 39
1, 2005-10-05 07:00, 2005-10-05 07:59:59, 5, 34, 129
2 hour chunks
groupID, StartTime, EndTime, Min, Max, Points
1, 2005-10-05 06:00, 2005-10-05 07:59:59, 5, 34, 168
Since originally posting my question, I have learned from John Bell
that I can use this to solve the 1 hour problem at hand.
SELECT GROUPID,
DATEADD(minute,-DATEPART(minute,Starttime),Starttime) AS StartTime,
DATEADD(millisecond,-3,DATEADD(hour,1,DATEADD(minute,-DATEPART(minute,Starttime),Starttime)))
AS EndTime,
Min([Min]), Max([Max]), SUM([Points])
FROM Readings
GROUP BY GroupId,
DATEADD(minute,-DATEPART(minute,Starttime),Starttime),
DATEADD(millisecond,-3,DATEADD(hour,1,DATEADD(minute,-DATEPART(minute,Starttime),Starttime)))
This makes sense for the hour instance... The hour instance also seems
to be the easiest one to solve. This query simply selects and groups
the start times to starttime-it's own minutes, end time to
starttime-it's own minutes + 1 hour. That works our very well for the
one hour case.
When I move to a two hour time window, we run into problems. Using that
exact query replacing "hour,1" for "hour, 2" will produce results that
look like this
groupID, StartTime, EndTime, Min, Max, Points
1, 2005-10-05 06:00, 2005-10-05 07:59:59, 5, 34, 168
1, 2005-10-05 07:00, 2005-10-05 08:59:59, 7, 32, 150
1, 2005-10-05 08:00, 2005-10-05 09:59:59, 6, 36, 172
This is where it gets confusing. The problem with that is that the
group by is still grouping in one hour chunks, because the 'adjusted'
start time for the second hour is not the same as the 'adjusted' start
time for the first hour. The start and end time might look ok, but the
rest of the data does not. Also, since this is a GROUP BY clause, a
working query should not produce overlapping results(in this case,
seemingly overlapping).
So for this 2 hour case (and upwards) I am looking for a solution to
get that start time query to go back
It's almost like i need to do something like IF statments in my
SELECT... not sure if that is possible or not.
Similar problems occur when you go to do half hour groupings. All four
15 minute chunks get floored and grouped together, even though you can
easily get the end time to report x:29:29.
What a mess, it seems like I am trying to do the impossible.
Any suggestions? Please ask me to clarify if necessary.
Jason
Got it. Well part of it. Here is the multiple hour verison. Yay for
integer division.
--SELECT in TWO HOUR INCREMENTS
SELECT groupID,
DATEADD(hour,-(DATEPART(hour,StartTime))+(DATEPART(hour,StartTim e)/2)*2,DATEADD(minute,-DATEPART(minute,StartTime),StartTime))
AS Startime,
DATEADD(millisecond,-3,DATEADD(hour,2,DATEADD(hour,-(DATEPART(hour,StartTime))+(DATEPART(hour,StartTim e)/2)*2,DATEADD(minute,-DATEPART(minute,StartTime),StartTime))))
AS Endtime,
MIN([Min]) as MinSpeed,
MAX([Max]) as MaxSpeed,
SUM([Points]) as Total
FROM TABLE_NAME
WHERE groupID='1'
GROUP BY groupID,
DATEADD(hour,-(DATEPART(hour,StartTime))+(DATEPART(hour,StartTim e)/2)*2,DATEADD(minute,-DATEPART(minute,StartTime),StartTime)),
DATEADD(millisecond,-3,DATEADD(hour,2,DATEADD(hour,-(DATEPART(hour,StartTime))+(DATEPART(hour,StartTim e)/2)*2,DATEADD(minute,-DATEPART(minute,StartTime),StartTime))))
This will work for 1,2,3,4, if you replace all instances of 2 with
1,2,3,4, etc. which can be done in code very easily.
Now to figure out the 30 minute version.
This query is so repetetive, I wish I could use variable names instead
of rewriting the whole thing... the EndTime calculation uses the whole
start time calculation. and the GROUP BY clauses are copies of what is
above. Using AS does not seem to work in these cases. Oh well.
Jason
|||On 10 Nov 2005 12:35:53 -0800, jasonsgeiger@.gmail.com wrote:

>Greetings,
>I'm working to combine rows based on a time window and I am hoping to
>be able to write a stored procedure to do this for me, rather than have
>parse through all this data in my program. I'm not very well versed
>with T-SQL syntax.. just enough to get by selecting using inner joins,
>updating and inserting... thats about it. (Hence why I am here.)
(snip)
Hi Jason,
I just posted a reply to your message in the original thread (in
microsoft.public.sqlserver.programming).
Please don't post multiple copies of the same question. I'd hate to see
someone else spend time to figure this out, becuase he or she is not
aware that I have already answered the question.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Difficult Combining Rows queston...followup

Greetings,
I'm working to combine rows based on a time window and I am hoping to
be able to write a stored procedure to do this for me, rather than have
parse through all this data in my program. I'm not very well versed
with T-SQL syntax.. just enough to get by selecting using inner joins,
updating and inserting... thats about it. (Hence why I am here.)
The raw data I have below looks like this:
groupID, StartTime, EndTime, Min, Max, Points
----
1, 2005-10-05 06:00, 2005-10-05 06:14:59, 7, 32, 13
1, 2005-10-05 06:15, 2005-10-05 06:29:59, 5, 29, 6
1, 2005-10-05 06:30, 2005-10-05 06:44:59, 5, 28, 4
1, 2005-10-05 06:45, 2005-10-05 06:59:59, 5, 29, 16
1, 2005-10-05 07:00, 2005-10-05 07:14:59, 5, 23, 13
1, 2005-10-05 07:15, 2005-10-05 07:29:59, 5, 25, 18
1, 2005-10-05 07:30, 2005-10-05 07:44:59, 5, 34, 49
1, 2005-10-05 07:45, 2005-10-05 07:59:59, 5, 31, 49
Pretty straight forward; you can see each entry is a 15 minute time
interval. What I want to be able to do is to use a view or a stored
procedure to view this in grouped chunks, like below
30 minute chunks
groupID, StartTime, EndTime, Min, Max, Points
----
1, 2005-10-05 06:00, 2005-10-05 06:29:59, 5, 32, 19
1, 2005-10-05 06:30, 2005-10-05 06:59:59, 5, 29, 20
1, 2005-10-05 07:00, 2005-10-05 07:29:59, 5, 25, 36
1, 2005-10-05 07:30, 2005-10-05 07:59:59, 5, 34, 98
1 hour chunks
groupID, StartTime, EndTime, Min, Max, Points
----
1, 2005-10-05 06:00, 2005-10-05 06:59:59, 5, 32, 39
1, 2005-10-05 07:00, 2005-10-05 07:59:59, 5, 34, 129
2 hour chunks
groupID, StartTime, EndTime, Min, Max, Points
----
1, 2005-10-05 06:00, 2005-10-05 07:59:59, 5, 34, 168
Since originally posting my question, I have learned from John Bell
that I can use this to solve the 1 hour problem at hand.
SELECT GROUPID,
DATEADD(minute,-DATEPART(minute,Starttime),Starttime) AS StartTime,
DATEADD(millisecond,-3,DATEADD(hour,1,DATEADD(minute,-DATEPART(minute,Startt
ime),Starttime)))
AS EndTime,
Min([Min]), Max([Max]), SUM([Points])
FROM Readings
GROUP BY GroupId,
DATEADD(minute,-DATEPART(minute,Starttime),Starttime),
DATEADD(millisecond,-3,DATEADD(hour,1,DATEADD(minute,-DATEPART(minute,Startt
ime),Starttime)))
This makes sense for the hour instance... The hour instance also seems
to be the easiest one to solve. This query simply selects and groups
the start times to starttime-it's own minutes, end time to
starttime-it's own minutes + 1 hour. That works our very well for the
one hour case.
When I move to a two hour time window, we run into problems. Using that
exact query replacing "hour,1" for "hour, 2" will produce results that
look like this
groupID, StartTime, EndTime, Min, Max, Points
----
1, 2005-10-05 06:00, 2005-10-05 07:59:59, 5, 34, 168
1, 2005-10-05 07:00, 2005-10-05 08:59:59, 7, 32, 150
1, 2005-10-05 08:00, 2005-10-05 09:59:59, 6, 36, 172
This is where it gets confusing. The problem with that is that the
group by is still grouping in one hour chunks, because the 'adjusted'
start time for the second hour is not the same as the 'adjusted' start
time for the first hour. The start and end time might look ok, but the
rest of the data does not. Also, since this is a GROUP BY clause, a
working query should not produce overlapping results(in this case,
seemingly overlapping).
So for this 2 hour case (and upwards) I am looking for a solution to
get that start time query to go back
It's almost like i need to do something like IF statments in my
SELECT... not sure if that is possible or not.
Similar problems occur when you go to do half hour groupings. All four
15 minute chunks get floored and grouped together, even though you can
easily get the end time to report x:29:29.
What a mess, it seems like I am trying to do the impossible.
Any suggestions? Please ask me to clarify if necessary.
JasonGot it. Well part of it. Here is the multiple hour verison. Yay for
integer division.
--SELECT in TWO HOUR INCREMENTS
SELECT groupID,
DATEADD(hour,- (DATEPART(hour,StartTime))+(DATEPART(hou
r,StartTime)/2)*2,DATE
ADD(minute,-DATEPART(minute,StartTime),StartTime))
AS Startime,
DATEADD(millisecond,-3,DATEADD(hour,2,DATEADD(hour,-(DATEPART(hour,StartTime
))+(DATEPART(hour,StartTime)/2)*2,DATEADD(minute,-DATEPART(minute,StartTime)
,StartTime))))
AS Endtime,
MIN([Min]) as MinSpeed,
MAX([Max]) as MaxSpeed,
SUM([Points]) as Total
FROM TABLE_NAME
WHERE groupID='1'
GROUP BY groupID,
DATEADD(hour,- (DATEPART(hour,StartTime))+(DATEPART(hou
r,StartTime)/2)*2,DATE
ADD(minute,-DATEPART(minute,StartTime),StartTime)),
DATEADD(millisecond,-3,DATEADD(hour,2,DATEADD(hour,-(DATEPART(hour,StartTime
))+(DATEPART(hour,StartTime)/2)*2,DATEADD(minute,-DATEPART(minute,StartTime)
,StartTime))))
This will work for 1,2,3,4, if you replace all instances of 2 with
1,2,3,4, etc. which can be done in code very easily.
Now to figure out the 30 minute version.
This query is so repetetive, I wish I could use variable names instead
of rewriting the whole thing... the EndTime calculation uses the whole
start time calculation. and the GROUP BY clauses are copies of what is
above. Using AS does not seem to work in these cases. Oh well.
Jason|||On 10 Nov 2005 12:35:53 -0800, jasonsgeiger@.gmail.com wrote:

>Greetings,
>I'm working to combine rows based on a time window and I am hoping to
>be able to write a stored procedure to do this for me, rather than have
>parse through all this data in my program. I'm not very well versed
>with T-SQL syntax.. just enough to get by selecting using inner joins,
>updating and inserting... thats about it. (Hence why I am here.)
(snip)
Hi Jason,
I just posted a reply to your message in the original thread (in
microsoft.public.sqlserver.programming).
Please don't post multiple copies of the same question. I'd hate to see
someone else spend time to figure this out, becuase he or she is not
aware that I have already answered the question.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Difficult Combining Rows queston...followup

Greetings,
I'm working to combine rows based on a time window and I am hoping to
be able to write a stored procedure to do this for me, rather than have
parse through all this data in my program. I'm not very well versed
with T-SQL syntax.. just enough to get by selecting using inner joins,
updating and inserting... thats about it. (Hence why I am here.)
The raw data I have below looks like this:
groupID, StartTime, EndTime, Min, Max, Points
----
1, 2005-10-05 06:00, 2005-10-05 06:14:59, 7, 32, 13
1, 2005-10-05 06:15, 2005-10-05 06:29:59, 5, 29, 6
1, 2005-10-05 06:30, 2005-10-05 06:44:59, 5, 28, 4
1, 2005-10-05 06:45, 2005-10-05 06:59:59, 5, 29, 16
1, 2005-10-05 07:00, 2005-10-05 07:14:59, 5, 23, 13
1, 2005-10-05 07:15, 2005-10-05 07:29:59, 5, 25, 18
1, 2005-10-05 07:30, 2005-10-05 07:44:59, 5, 34, 49
1, 2005-10-05 07:45, 2005-10-05 07:59:59, 5, 31, 49
Pretty straight forward; you can see each entry is a 15 minute time
interval. What I want to be able to do is to use a view or a stored
procedure to view this in grouped chunks, like below
30 minute chunks
groupID, StartTime, EndTime, Min, Max, Points
----
1, 2005-10-05 06:00, 2005-10-05 06:29:59, 5, 32, 19
1, 2005-10-05 06:30, 2005-10-05 06:59:59, 5, 29, 20
1, 2005-10-05 07:00, 2005-10-05 07:29:59, 5, 25, 36
1, 2005-10-05 07:30, 2005-10-05 07:59:59, 5, 34, 98
1 hour chunks
groupID, StartTime, EndTime, Min, Max, Points
----
1, 2005-10-05 06:00, 2005-10-05 06:59:59, 5, 32, 39
1, 2005-10-05 07:00, 2005-10-05 07:59:59, 5, 34, 129
2 hour chunks
groupID, StartTime, EndTime, Min, Max, Points
----
1, 2005-10-05 06:00, 2005-10-05 07:59:59, 5, 34, 168
Since originally posting my question, I have learned from John Bell
that I can use this to solve the 1 hour problem at hand.
SELECT GROUPID,
DATEADD(minute,-DATEPART(minute,Starttime),Starttime) AS StartTime,
DATEADD(millisecond,-3,DATEADD(hour,1,DATEADD(minute,-DATEPART(minute,Starttime),Starttime)))
AS EndTime,
Min([Min]), Max([Max]), SUM([Points])
FROM Readings
GROUP BY GroupId,
DATEADD(minute,-DATEPART(minute,Starttime),Starttime),
DATEADD(millisecond,-3,DATEADD(hour,1,DATEADD(minute,-DATEPART(minute,Starttime),Starttime)))
This makes sense for the hour instance... The hour instance also seems
to be the easiest one to solve. This query simply selects and groups
the start times to starttime-it's own minutes, end time to
starttime-it's own minutes + 1 hour. That works our very well for the
one hour case.
When I move to a two hour time window, we run into problems. Using that
exact query replacing "hour,1" for "hour, 2" will produce results that
look like this
groupID, StartTime, EndTime, Min, Max, Points
----
1, 2005-10-05 06:00, 2005-10-05 07:59:59, 5, 34, 168
1, 2005-10-05 07:00, 2005-10-05 08:59:59, 7, 32, 150
1, 2005-10-05 08:00, 2005-10-05 09:59:59, 6, 36, 172
This is where it gets confusing. The problem with that is that the
group by is still grouping in one hour chunks, because the 'adjusted'
start time for the second hour is not the same as the 'adjusted' start
time for the first hour. The start and end time might look ok, but the
rest of the data does not. Also, since this is a GROUP BY clause, a
working query should not produce overlapping results(in this case,
seemingly overlapping).
So for this 2 hour case (and upwards) I am looking for a solution to
get that start time query to go back
It's almost like i need to do something like IF statments in my
SELECT... not sure if that is possible or not.
Similar problems occur when you go to do half hour groupings. All four
15 minute chunks get floored and grouped together, even though you can
easily get the end time to report x:29:29.
What a mess, it seems like I am trying to do the impossible.
Any suggestions? Please ask me to clarify if necessary.
JasonGot it. Well part of it. Here is the multiple hour verison. Yay for
integer division.
--SELECT in TWO HOUR INCREMENTS
SELECT groupID,
DATEADD(hour,-(DATEPART(hour,StartTime))+(DATEPART(hour,StartTime)/2)*2,DATEADD(minute,-DATEPART(minute,StartTime),StartTime))
AS Startime,
DATEADD(millisecond,-3,DATEADD(hour,2,DATEADD(hour,-(DATEPART(hour,StartTime))+(DATEPART(hour,StartTime)/2)*2,DATEADD(minute,-DATEPART(minute,StartTime),StartTime))))
AS Endtime,
MIN([Min]) as MinSpeed,
MAX([Max]) as MaxSpeed,
SUM([Points]) as Total
FROM TABLE_NAME
WHERE groupID='1'
GROUP BY groupID,
DATEADD(hour,-(DATEPART(hour,StartTime))+(DATEPART(hour,StartTime)/2)*2,DATEADD(minute,-DATEPART(minute,StartTime),StartTime)),
DATEADD(millisecond,-3,DATEADD(hour,2,DATEADD(hour,-(DATEPART(hour,StartTime))+(DATEPART(hour,StartTime)/2)*2,DATEADD(minute,-DATEPART(minute,StartTime),StartTime))))
This will work for 1,2,3,4, if you replace all instances of 2 with
1,2,3,4, etc. which can be done in code very easily.
Now to figure out the 30 minute version.
This query is so repetetive, I wish I could use variable names instead
of rewriting the whole thing... the EndTime calculation uses the whole
start time calculation. and the GROUP BY clauses are copies of what is
above. Using AS does not seem to work in these cases. Oh well.
Jason|||On 10 Nov 2005 12:35:53 -0800, jasonsgeiger@.gmail.com wrote:
>Greetings,
>I'm working to combine rows based on a time window and I am hoping to
>be able to write a stored procedure to do this for me, rather than have
>parse through all this data in my program. I'm not very well versed
>with T-SQL syntax.. just enough to get by selecting using inner joins,
>updating and inserting... thats about it. (Hence why I am here.)
(snip)
Hi Jason,
I just posted a reply to your message in the original thread (in
microsoft.public.sqlserver.programming).
Please don't post multiple copies of the same question. I'd hate to see
someone else spend time to figure this out, becuase he or she is not
aware that I have already answered the question.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql

Sunday, March 11, 2012

Differential Backup did not work

Hi all,
I've been using this stored proc to do full and log backups for a while
and it's working perfectly. Now, the requirements have changed I've to
run full backup at 7:00PM and differential at 5:00AM. The full and log
backups are still working not differential. Something is wroing in my
codes on differentail backup task.
Thanks so much,
Silaphet,
Here is the error report from the job history:
Executed as user: NT AUTHORITY\SYSTEM. Incorrect syntax near the
keyword 'TO'. [SQLSTATE 42000] (Error 156). The step failed.
Below is my stored proc code:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
-- drop proc spOM_BackUpDB
--Exec spOM_BackUpDB 'FULL', 'CLI', 'ZION_PROD', 4
--Exec spOM_BackUpDB 'FULL', 'CLI', 'socConfig', 4
--Exec spOM_BackUpDB 'FULL', 'CLI', 'ObjectManager', 4
--Exec spOM_BackUpDB 'TRAN', 'CLI', 'ZION_PROD', 4
--Exec spOM_BackUpDB 'TRAN', 'CLI', 'socConfig', 4
--Exec spOM_BackUpDB 'TRAN', 'CLI', 'ObjectManager', 4
ALTER Proc spOM_BackUpDB_Test
@.Full_TranChar(4),--FULL or TRAN
@.ServerAliasVarChar(5),--CLI or FIN or OM or RPT
@.db_nameVarchar(150),--Name of Database
@.StripesInt--Number of stripes to dump it to
AS
Declare
@.cmd varchar(8000),
@.rcmd varchar (8000),
@.File Varchar(8000),
@.UNCPathVarchar(8000),
@.ServerNameVarchar(200),--Name of server
@.StripeNumint,
@.RuntimeDateTime,
@.CompressFileVarchar(8000)
Set @.File = ''
Set @.ServerName = @.@.servername
Set @.UNCPath = '\\Houdbs0101\K_Drive\' + @.ServerName +'\' + @.db_name +
'\'
Set @.StripeNum = 1
Set @.Runtime = GetDate()
SELECT @.FILE = @.uncpath + @.db_name + '_' + @.Full_Tran + '_' +
convert(varchar(24),@.Runtime,112) +
replace(convert(varchar(5),@.Runtime,114),':','') + '_' +
Convert(Varchar(3),@.StripeNum) + '.Bak'
Select @.CompressFile = @.uncpath + @.db_name + '_' + @.Full_Tran + '_' +
convert(varchar(24),@.Runtime,112) +
replace(convert(varchar(5),@.Runtime,114),':','') + '*.*'
Set @.StripeNum = @.StripeNum + 1
If @.Full_Tran = 'FULL'
Begin
set @.cmd = ' BACKUP DATABASE ' + @.db_name
End
DECLARE @.Full_TranChar(4)
IF @.Full_Tran = 'DIFF'
BEGIN
set @.cmd = ' BACKUP DATABASE ' + @.db_name + +
'WITH DIFFERENTIAL '
END
If @.Full_Tran = 'TRAN'
Begin
set @.cmd = ' BACKUP LOG ' + @.db_name
End
set @.cmd = @.cmd + ' TO DISK = ''''' + @.FILE + ''''''
While @.StripeNum <= @.Stripes
Begin
set @.cmd = @.cmd + ', DISK = ''''' + @.uncpath + @.db_name + '_' +
@.Full_Tran + '_' + convert(varchar(24),@.Runtime,112) +
replace(convert(varchar(5),@.Runtime,114),':','') + '_' +
Convert(Varchar(3),@.StripeNum) + '.Bak' + ''''''
Set @.StripeNum = @.StripeNum + 1
End
set @.Rcmd = '[' + @.ServerAlias + '].master.dbo.sp_sqlexec ''' + @.cmd +
''''
Select @.Rcmd
EXECUTE (@.Rcmd)
/*
Set @.cmd = '"\\Houdbs0101\L_Drive\WinRAR\RAR.exe" a -ep "' + @.uncpath +
@.db_name + '_' + @.Full_Tran + '_' + convert(varchar(24),@.Runtime,112) +
replace(convert(varchar(24),@.Runtime,114),':','') + '.rar' + '" "' +
@.CompressFile + '"'
Select @.cmd
set @.Rcmd = '[' + @.ServerAlias + '].master.dbo.xp_cmdshell ''' + @.cmd +
''''
Select @.Rcmd
EXECUTE (@.Rcmd)
*/
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Under the section "IF @.Full_Tran = 'DIFF'", it should be:
BEGIN
SET @.cmd = ' BACKUP DATABASE ' + @.db_name + ' WITH DIFFERENTIAL '
END
Remove the extra + sign and add a space before the word "WITH"
"Silaphet" wrote:

> Hi all,
> I've been using this stored proc to do full and log backups for a while
> and it's working perfectly. Now, the requirements have changed I've to
> run full backup at 7:00PM and differential at 5:00AM. The full and log
> backups are still working not differential. Something is wroing in my
> codes on differentail backup task.
> Thanks so much,
> Silaphet,
> Here is the error report from the job history:
> Executed as user: NT AUTHORITY\SYSTEM. Incorrect syntax near the
> keyword 'TO'. [SQLSTATE 42000] (Error 156). The step failed.
>
> Below is my stored proc code:
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> -- drop proc spOM_BackUpDB
> --Exec spOM_BackUpDB 'FULL', 'CLI', 'ZION_PROD', 4
> --Exec spOM_BackUpDB 'FULL', 'CLI', 'socConfig', 4
> --Exec spOM_BackUpDB 'FULL', 'CLI', 'ObjectManager', 4
> --Exec spOM_BackUpDB 'TRAN', 'CLI', 'ZION_PROD', 4
> --Exec spOM_BackUpDB 'TRAN', 'CLI', 'socConfig', 4
> --Exec spOM_BackUpDB 'TRAN', 'CLI', 'ObjectManager', 4
> ALTER Proc spOM_BackUpDB_Test
> @.Full_TranChar(4),--FULL or TRAN
> @.ServerAliasVarChar(5),--CLI or FIN or OM or RPT
> @.db_nameVarchar(150),--Name of Database
> @.StripesInt--Number of stripes to dump it to
> AS
>
> Declare
> @.cmd varchar(8000),
> @.rcmd varchar (8000),
> @.File Varchar(8000),
> @.UNCPathVarchar(8000),
> @.ServerNameVarchar(200),--Name of server
> @.StripeNumint,
> @.RuntimeDateTime,
> @.CompressFileVarchar(8000)
>
> Set @.File = ''
> Set @.ServerName = @.@.servername
> Set @.UNCPath = '\\Houdbs0101\K_Drive\' + @.ServerName +'\' + @.db_name +
> '\'
> Set @.StripeNum = 1
> Set @.Runtime = GetDate()
> SELECT @.FILE = @.uncpath + @.db_name + '_' + @.Full_Tran + '_' +
> convert(varchar(24),@.Runtime,112) +
> replace(convert(varchar(5),@.Runtime,114),':','') + '_' +
> Convert(Varchar(3),@.StripeNum) + '.Bak'
> Select @.CompressFile = @.uncpath + @.db_name + '_' + @.Full_Tran + '_' +
> convert(varchar(24),@.Runtime,112) +
> replace(convert(varchar(5),@.Runtime,114),':','') + '*.*'
> Set @.StripeNum = @.StripeNum + 1
> If @.Full_Tran = 'FULL'
> Begin
> set @.cmd = ' BACKUP DATABASE ' + @.db_name
> End
> DECLARE @.Full_TranChar(4)
> IF @.Full_Tran = 'DIFF'
> BEGIN
> set @.cmd = ' BACKUP DATABASE ' + @.db_name + +
> 'WITH DIFFERENTIAL '
> END
> If @.Full_Tran = 'TRAN'
> Begin
> set @.cmd = ' BACKUP LOG ' + @.db_name
> End
> set @.cmd = @.cmd + ' TO DISK = ''''' + @.FILE + ''''''
>
> While @.StripeNum <= @.Stripes
> Begin
> set @.cmd = @.cmd + ', DISK = ''''' + @.uncpath + @.db_name + '_' +
> @.Full_Tran + '_' + convert(varchar(24),@.Runtime,112) +
> replace(convert(varchar(5),@.Runtime,114),':','') + '_' +
> Convert(Varchar(3),@.StripeNum) + '.Bak' + ''''''
> Set @.StripeNum = @.StripeNum + 1
> End
> set @.Rcmd = '[' + @.ServerAlias + '].master.dbo.sp_sqlexec ''' + @.cmd +
> ''''
> Select @.Rcmd
> EXECUTE (@.Rcmd)
> /*
> Set @.cmd = '"\\Houdbs0101\L_Drive\WinRAR\RAR.exe" a -ep "' + @.uncpath +
> @.db_name + '_' + @.Full_Tran + '_' + convert(varchar(24),@.Runtime,112) +
> replace(convert(varchar(24),@.Runtime,114),':','') + '.rar' + '" "' +
> @.CompressFile + '"'
> Select @.cmd
> set @.Rcmd = '[' + @.ServerAlias + '].master.dbo.xp_cmdshell ''' + @.cmd +
> ''''
> Select @.Rcmd
> EXECUTE (@.Rcmd)
> */
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>

Friday, March 9, 2012

Differential Backup did not work

Hi all,
I've been using this stored proc to do full and log backups for a while
and it's working perfectly. Now, the requirements have changed I've to
run full backup at 7:00PM and differential at 5:00AM. The full and log
backups are still working not differential. Something is wroing in my
codes on differentail backup task.
Thanks so much,
Silaphet,
Here is the error report from the job history:
Executed as user: NT AUTHORITY\SYSTEM. Incorrect syntax near the
keyword 'TO'. [SQLSTATE 42000] (Error 156). The step failed.
Below is my stored proc code:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
-- drop proc spOM_BackUpDB
-- Exec spOM_BackUpDB 'FULL', 'CLI', 'ZION_PROD', 4
-- Exec spOM_BackUpDB 'FULL', 'CLI', 'socConfig', 4
-- Exec spOM_BackUpDB 'FULL', 'CLI', 'ObjectManager', 4
-- Exec spOM_BackUpDB 'TRAN', 'CLI', 'ZION_PROD', 4
-- Exec spOM_BackUpDB 'TRAN', 'CLI', 'socConfig', 4
-- Exec spOM_BackUpDB 'TRAN', 'CLI', 'ObjectManager', 4
ALTER Proc spOM_BackUpDB_Test
@.Full_Tran Char(4), --FULL or TRAN
@.ServerAlias VarChar(5), --CLI or FIN or OM or RPT
@.db_name Varchar(150), --Name of Database
@.Stripes Int --Number of stripes to dump it to
AS
Declare
@.cmd varchar(8000),
@.rcmd varchar (8000),
@.File Varchar(8000),
@.UNCPath Varchar(8000),
@.ServerName Varchar(200), --Name of server
@.StripeNum int,
@.Runtime DateTime,
@.CompressFile Varchar(8000)
Set @.File = ''
Set @.ServerName = @.@.servername
Set @.UNCPath = '\\Houdbs0101\K_Drive' + @.ServerName +'' + @.db_name +
''
Set @.StripeNum = 1
Set @.Runtime = GetDate()
SELECT @.FILE = @.uncpath + @.db_name + '_' + @.Full_Tran + '_' +
convert(varchar(24),@.Runtime,112) +
replace(convert(varchar(5),@.Runtime,114)
,':','') + '_' +
Convert(Varchar(3),@.StripeNum) + '.Bak'
Select @.CompressFile = @.uncpath + @.db_name + '_' + @.Full_Tran + '_' +
convert(varchar(24),@.Runtime,112) +
replace(convert(varchar(5),@.Runtime,114)
,':','') + '*.*'
Set @.StripeNum = @.StripeNum + 1
If @.Full_Tran = 'FULL'
Begin
set @.cmd = ' BACKUP DATABASE ' + @.db_name
End
DECLARE @.Full_Tran Char(4)
IF @.Full_Tran = 'DIFF'
BEGIN
set @.cmd = ' BACKUP DATABASE ' + @.db_name + +
'WITH DIFFERENTIAL '
END
If @.Full_Tran = 'TRAN'
Begin
set @.cmd = ' BACKUP LOG ' + @.db_name
End
set @.cmd = @.cmd + ' TO DISK = '' + @.FILE + '''
While @.StripeNum <= @.Stripes
Begin
set @.cmd = @.cmd + ', DISK = '' + @.uncpath + @.db_name + '_' +
@.Full_Tran + '_' + convert(varchar(24),@.Runtime,112) +
replace(convert(varchar(5),@.Runtime,114)
,':','') + '_' +
Convert(Varchar(3),@.StripeNum) + '.Bak' + '''
Set @.StripeNum = @.StripeNum + 1
End
set @.Rcmd = '[' + @.ServerAlias + '].master.dbo.sp_sqlexec ''' + @.cmd +
''''
Select @.Rcmd
EXECUTE (@.Rcmd)
/*
Set @.cmd = '"\\Houdbs0101\L_Drive\WinRAR\RAR.exe" a -ep "' + @.uncpath +
@.db_name + '_' + @.Full_Tran + '_' + convert(varchar(24),@.Runtime,112) +
replace(convert(varchar(24),@.Runtime,114
),':','') + '.rar' + '" "' +
@.CompressFile + '"'
Select @.cmd
set @.Rcmd = '[' + @.ServerAlias + '].master.dbo.xp_cmdshell ''' + @.cmd +
''''
Select @.Rcmd
EXECUTE (@.Rcmd)
*/
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOUnder the section "IF @.Full_Tran = 'DIFF'", it should be:
BEGIN
SET @.cmd = ' BACKUP DATABASE ' + @.db_name + ' WITH DIFFERENTIAL '
END
Remove the extra + sign and add a space before the word "WITH"
"Silaphet" wrote:

> Hi all,
> I've been using this stored proc to do full and log backups for a while
> and it's working perfectly. Now, the requirements have changed I've to
> run full backup at 7:00PM and differential at 5:00AM. The full and log
> backups are still working not differential. Something is wroing in my
> codes on differentail backup task.
> Thanks so much,
> Silaphet,
> Here is the error report from the job history:
> Executed as user: NT AUTHORITY\SYSTEM. Incorrect syntax near the
> keyword 'TO'. [SQLSTATE 42000] (Error 156). The step failed.
>
> Below is my stored proc code:
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> -- drop proc spOM_BackUpDB
> -- Exec spOM_BackUpDB 'FULL', 'CLI', 'ZION_PROD', 4
> -- Exec spOM_BackUpDB 'FULL', 'CLI', 'socConfig', 4
> -- Exec spOM_BackUpDB 'FULL', 'CLI', 'ObjectManager', 4
> -- Exec spOM_BackUpDB 'TRAN', 'CLI', 'ZION_PROD', 4
> -- Exec spOM_BackUpDB 'TRAN', 'CLI', 'socConfig', 4
> -- Exec spOM_BackUpDB 'TRAN', 'CLI', 'ObjectManager', 4
> ALTER Proc spOM_BackUpDB_Test
> @.Full_Tran Char(4), --FULL or TRAN
> @.ServerAlias VarChar(5), --CLI or FIN or OM or RPT
> @.db_name Varchar(150), --Name of Database
> @.Stripes Int --Number of stripes to dump it to
> AS
>
> Declare
> @.cmd varchar(8000),
> @.rcmd varchar (8000),
> @.File Varchar(8000),
> @.UNCPath Varchar(8000),
> @.ServerName Varchar(200), --Name of server
> @.StripeNum int,
> @.Runtime DateTime,
> @.CompressFile Varchar(8000)
>
> Set @.File = ''
> Set @.ServerName = @.@.servername
> Set @.UNCPath = '\\Houdbs0101\K_Drive' + @.ServerName +'' + @.db_name +
> ''
> Set @.StripeNum = 1
> Set @.Runtime = GetDate()
> SELECT @.FILE = @.uncpath + @.db_name + '_' + @.Full_Tran + '_' +
> convert(varchar(24),@.Runtime,112) +
> replace(convert(varchar(5),@.Runtime,114)
,':','') + '_' +
> Convert(Varchar(3),@.StripeNum) + '.Bak'
> Select @.CompressFile = @.uncpath + @.db_name + '_' + @.Full_Tran + '_' +
> convert(varchar(24),@.Runtime,112) +
> replace(convert(varchar(5),@.Runtime,114)
,':','') + '*.*'
> Set @.StripeNum = @.StripeNum + 1
> If @.Full_Tran = 'FULL'
> Begin
> set @.cmd = ' BACKUP DATABASE ' + @.db_name
> End
> DECLARE @.Full_Tran Char(4)
> IF @.Full_Tran = 'DIFF'
> BEGIN
> set @.cmd = ' BACKUP DATABASE ' + @.db_name + +
> 'WITH DIFFERENTIAL '
> END
> If @.Full_Tran = 'TRAN'
> Begin
> set @.cmd = ' BACKUP LOG ' + @.db_name
> End
> set @.cmd = @.cmd + ' TO DISK = '' + @.FILE + '''
>
> While @.StripeNum <= @.Stripes
> Begin
> set @.cmd = @.cmd + ', DISK = '' + @.uncpath + @.db_name + '_' +
> @.Full_Tran + '_' + convert(varchar(24),@.Runtime,112) +
> replace(convert(varchar(5),@.Runtime,114)
,':','') + '_' +
> Convert(Varchar(3),@.StripeNum) + '.Bak' + '''
> Set @.StripeNum = @.StripeNum + 1
> End
> set @.Rcmd = '[' + @.ServerAlias + '].master.dbo.sp_sqlexec ''' + @.cmd +
> ''''
> Select @.Rcmd
> EXECUTE (@.Rcmd)
> /*
> Set @.cmd = '"\\Houdbs0101\L_Drive\WinRAR\RAR.exe" a -ep "' + @.uncpath +
> @.db_name + '_' + @.Full_Tran + '_' + convert(varchar(24),@.Runtime,112) +
> replace(convert(varchar(24),@.Runtime,114
),':','') + '.rar' + '" "' +
> @.CompressFile + '"'
> Select @.cmd
> set @.Rcmd = '[' + @.ServerAlias + '].master.dbo.xp_cmdshell ''' + @.cmd
+
> ''''
> Select @.Rcmd
> EXECUTE (@.Rcmd)
> */
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>

Differential Backup did not work

Hi all,
I've been using this stored proc to do full and log backups for a while
and it's working perfectly. Now, the requirements have changed I've to
run full backup at 7:00PM and differential at 5:00AM. The full and log
backups are still working not differential. Something is wroing in my
codes on differentail backup task.
Thanks so much,
Silaphet,
Here is the error report from the job history:
Executed as user: NT AUTHORITY\SYSTEM. Incorrect syntax near the
keyword 'TO'. [SQLSTATE 42000] (Error 156). The step failed.
Below is my stored proc code:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
-- drop proc spOM_BackUpDB
-- Exec spOM_BackUpDB 'FULL', 'CLI', 'ZION_PROD', 4
-- Exec spOM_BackUpDB 'FULL', 'CLI', 'socConfig', 4
-- Exec spOM_BackUpDB 'FULL', 'CLI', 'ObjectManager', 4
-- Exec spOM_BackUpDB 'TRAN', 'CLI', 'ZION_PROD', 4
-- Exec spOM_BackUpDB 'TRAN', 'CLI', 'socConfig', 4
-- Exec spOM_BackUpDB 'TRAN', 'CLI', 'ObjectManager', 4
ALTER Proc spOM_BackUpDB_Test
@.Full_Tran Char(4), --FULL or TRAN
@.ServerAlias VarChar(5), --CLI or FIN or OM or RPT
@.db_name Varchar(150), --Name of Database
@.Stripes Int --Number of stripes to dump it to
AS
Declare
@.cmd varchar(8000),
@.rcmd varchar (8000),
@.File Varchar(8000),
@.UNCPath Varchar(8000),
@.ServerName Varchar(200), --Name of server
@.StripeNum int,
@.Runtime DateTime,
@.CompressFile Varchar(8000)
Set @.File = ''
Set @.ServerName = @.@.servername
Set @.UNCPath = '\\Houdbs0101\K_Drive\' + @.ServerName +'\' + @.db_name +
'\'
Set @.StripeNum = 1
Set @.Runtime = GetDate()
SELECT @.FILE = @.uncpath + @.db_name + '_' + @.Full_Tran + '_' +
convert(varchar(24),@.Runtime,112) +
replace(convert(varchar(5),@.Runtime,114),':','') + '_' +
Convert(Varchar(3),@.StripeNum) + '.Bak'
Select @.CompressFile = @.uncpath + @.db_name + '_' + @.Full_Tran + '_' +
convert(varchar(24),@.Runtime,112) +
replace(convert(varchar(5),@.Runtime,114),':','') + '*.*'
Set @.StripeNum = @.StripeNum + 1
If @.Full_Tran = 'FULL'
Begin
set @.cmd = ' BACKUP DATABASE ' + @.db_name
End
DECLARE @.Full_Tran Char(4)
IF @.Full_Tran = 'DIFF'
BEGIN
set @.cmd = ' BACKUP DATABASE ' + @.db_name + +
'WITH DIFFERENTIAL '
END
If @.Full_Tran = 'TRAN'
Begin
set @.cmd = ' BACKUP LOG ' + @.db_name
End
set @.cmd = @.cmd + ' TO DISK = '' + @.FILE + '''
While @.StripeNum <= @.Stripes
Begin
set @.cmd = @.cmd + ', DISK = '' + @.uncpath + @.db_name + '_' +
@.Full_Tran + '_' + convert(varchar(24),@.Runtime,112) +
replace(convert(varchar(5),@.Runtime,114),':','') + '_' +
Convert(Varchar(3),@.StripeNum) + '.Bak' + '''
Set @.StripeNum = @.StripeNum + 1
End
set @.Rcmd = '[' + @.ServerAlias + '].master.dbo.sp_sqlexec ''' + @.cmd +
''''
Select @.Rcmd
EXECUTE (@.Rcmd)
/*
Set @.cmd = '"\\Houdbs0101\L_Drive\WinRAR\RAR.exe" a -ep "' + @.uncpath +
@.db_name + '_' + @.Full_Tran + '_' + convert(varchar(24),@.Runtime,112) +
replace(convert(varchar(24),@.Runtime,114),':','') + '.rar' + '" "' +
@.CompressFile + '"'
Select @.cmd
set @.Rcmd = '[' + @.ServerAlias + '].master.dbo.xp_cmdshell ''' + @.cmd +
''''
Select @.Rcmd
EXECUTE (@.Rcmd)
*/
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOUnder the section "IF @.Full_Tran = 'DIFF'", it should be:
BEGIN
SET @.cmd = ' BACKUP DATABASE ' + @.db_name + ' WITH DIFFERENTIAL '
END
Remove the extra + sign and add a space before the word "WITH"
"Silaphet" wrote:
> Hi all,
> I've been using this stored proc to do full and log backups for a while
> and it's working perfectly. Now, the requirements have changed I've to
> run full backup at 7:00PM and differential at 5:00AM. The full and log
> backups are still working not differential. Something is wroing in my
> codes on differentail backup task.
> Thanks so much,
> Silaphet,
> Here is the error report from the job history:
> Executed as user: NT AUTHORITY\SYSTEM. Incorrect syntax near the
> keyword 'TO'. [SQLSTATE 42000] (Error 156). The step failed.
>
> Below is my stored proc code:
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> -- drop proc spOM_BackUpDB
> -- Exec spOM_BackUpDB 'FULL', 'CLI', 'ZION_PROD', 4
> -- Exec spOM_BackUpDB 'FULL', 'CLI', 'socConfig', 4
> -- Exec spOM_BackUpDB 'FULL', 'CLI', 'ObjectManager', 4
> -- Exec spOM_BackUpDB 'TRAN', 'CLI', 'ZION_PROD', 4
> -- Exec spOM_BackUpDB 'TRAN', 'CLI', 'socConfig', 4
> -- Exec spOM_BackUpDB 'TRAN', 'CLI', 'ObjectManager', 4
> ALTER Proc spOM_BackUpDB_Test
> @.Full_Tran Char(4), --FULL or TRAN
> @.ServerAlias VarChar(5), --CLI or FIN or OM or RPT
> @.db_name Varchar(150), --Name of Database
> @.Stripes Int --Number of stripes to dump it to
> AS
>
> Declare
> @.cmd varchar(8000),
> @.rcmd varchar (8000),
> @.File Varchar(8000),
> @.UNCPath Varchar(8000),
> @.ServerName Varchar(200), --Name of server
> @.StripeNum int,
> @.Runtime DateTime,
> @.CompressFile Varchar(8000)
>
> Set @.File = ''
> Set @.ServerName = @.@.servername
> Set @.UNCPath = '\\Houdbs0101\K_Drive\' + @.ServerName +'\' + @.db_name +
> '\'
> Set @.StripeNum = 1
> Set @.Runtime = GetDate()
> SELECT @.FILE = @.uncpath + @.db_name + '_' + @.Full_Tran + '_' +
> convert(varchar(24),@.Runtime,112) +
> replace(convert(varchar(5),@.Runtime,114),':','') + '_' +
> Convert(Varchar(3),@.StripeNum) + '.Bak'
> Select @.CompressFile = @.uncpath + @.db_name + '_' + @.Full_Tran + '_' +
> convert(varchar(24),@.Runtime,112) +
> replace(convert(varchar(5),@.Runtime,114),':','') + '*.*'
> Set @.StripeNum = @.StripeNum + 1
> If @.Full_Tran = 'FULL'
> Begin
> set @.cmd = ' BACKUP DATABASE ' + @.db_name
> End
> DECLARE @.Full_Tran Char(4)
> IF @.Full_Tran = 'DIFF'
> BEGIN
> set @.cmd = ' BACKUP DATABASE ' + @.db_name + +
> 'WITH DIFFERENTIAL '
> END
> If @.Full_Tran = 'TRAN'
> Begin
> set @.cmd = ' BACKUP LOG ' + @.db_name
> End
> set @.cmd = @.cmd + ' TO DISK = '' + @.FILE + '''
>
> While @.StripeNum <= @.Stripes
> Begin
> set @.cmd = @.cmd + ', DISK = '' + @.uncpath + @.db_name + '_' +
> @.Full_Tran + '_' + convert(varchar(24),@.Runtime,112) +
> replace(convert(varchar(5),@.Runtime,114),':','') + '_' +
> Convert(Varchar(3),@.StripeNum) + '.Bak' + '''
> Set @.StripeNum = @.StripeNum + 1
> End
> set @.Rcmd = '[' + @.ServerAlias + '].master.dbo.sp_sqlexec ''' + @.cmd +
> ''''
> Select @.Rcmd
> EXECUTE (@.Rcmd)
> /*
> Set @.cmd = '"\\Houdbs0101\L_Drive\WinRAR\RAR.exe" a -ep "' + @.uncpath +
> @.db_name + '_' + @.Full_Tran + '_' + convert(varchar(24),@.Runtime,112) +
> replace(convert(varchar(24),@.Runtime,114),':','') + '.rar' + '" "' +
> @.CompressFile + '"'
> Select @.cmd
> set @.Rcmd = '[' + @.ServerAlias + '].master.dbo.xp_cmdshell ''' + @.cmd +
> ''''
> Select @.Rcmd
> EXECUTE (@.Rcmd)
> */
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>

Different versions of SSMS?

Hi.
Does it exist different versions of SSMS?
I'm now working with SSMS Express and SQL Server 2005 Express. How can I
make a scheduled backup from SSMS?
___________________________
Best regards - Anders
Sql Server Express does not include a SQL Agent, so you will have to
use an external solution for scheduling your backups. My suggestion
would be to run a script with SQLCMD on a scheduled basis (e.g. with
the at command).
Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Or use below two:
http://sqldbatips.com/showarticle.asp?ID=27
http://sqldbatips.com/showarticle.asp?ID=29
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1180341388.388646.126450@.u30g2000hsc.googlegr oups.com...
> Sql Server Express does not include a SQL Agent, so you will have to
> use an external solution for scheduling your backups. My suggestion
> would be to run a script with SQLCMD on a scheduled basis (e.g. with
> the at command).
> Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>

different values between Relational and MOLAP in a sum measure... BUG?

Hello,
I'm working in a Windows 2003 Server, SQL Server 2000 and Analysis Services 2000.
I have created two models in Analysis Services (MOLAP):
- MODEL 1 gets info directly from the relational FACT table: "FCT_SALES"
- MODEL 2 gets info from a view with: "SELECT * FROM FCT_SALES"
FCT_SALES has a measure (sum and double - the column in the relational table is float).
This measure has 10 rows in the FCT_SALES.
Problem:
When we agregate the measure rows in Query Analyzer, MODEL 1 and MODEL 2 return 20. --> IT'S OK!
When I use Analysis Services (and ProClarity), MODEL 1 returns 20. --> IT'S OK!
When I use Analysis Services (and ProClarity), MODEL 2 returns 17. -->WRONG!!
WHY?
When I make Drill Through (Drill to Detail) in Analysis Services (or ProClarity) to see the rows that compose the value, and export them to Excel, I get the 20. --> IT'S OK!
So, WHY does Analysis Services (and ProClarity) give me 17 ?!?
If the SUM(rows) give me 20, WHY does Analysis Services give me 17 ?!?
Thanks.
Hi guys,
Yesterday nigth, I found the problem: something that shouldn't happen in the source info, happened! Murphy's Law )))
Analysis Services wasn't causing the info inconsistent.
Thanks anyway.

different values between Relational and MOLAP in a sum measure... BUG?

Hello,
I'm working in a Windows 2003 Server, SQL Server 2000 and Analysis Services
2000.
I have created two models in Analysis Services (MOLAP):
- MODEL 1 gets info directly from the relational FACT table: "FCT_SALES"
- MODEL 2 gets info from a view with: "SELECT * FROM FCT_SALES"
FCT_SALES has a measure (sum and double - the column in the relational table
is float).
This measure has 10 rows in the FCT_SALES.
Problem:
When we agregate the measure rows in Query Analyzer, MODEL 1 and MODEL 2 ret
urn 20. --> IT'S OK!
When I use Analysis Services (and ProClarity), MODEL 1 returns 20. --> IT'S
OK!
When I use Analysis Services (and ProClarity), MODEL 2 returns 17. -->WRONG!
!
WHY?
When I make Drill Through (Drill to Detail) in Analysis Services (or ProClar
ity) to see the rows that compose the value, and export them to Excel, I get
the 20. --> IT'S OK!
So, WHY does Analysis Services (and ProClarity) give me 17 ?!?
If the SUM(rows) give me 20, WHY does Analysis Services give me 17 ?!?
Thanks.Hi guys,
Yesterday nigth, I found the problem: something that shouldn't happen in the
source info, happened! Murphy's Law )))
Analysis Services wasn't causing the info inconsistent.
Thanks anyway.

Sunday, February 19, 2012

Different data types

I have successfully made a couple of SSIS packages to read data from a legacy AS400 to SQL Server 2005. These are working fine. I am now working on one that is trying to pull some financial data and I keep getting the following error for the decimal columns:

"The component ciew is unavailable. Make sure the component view has been created. The input column "input column AMT1 (2870) cannot be mapped to "external metadata column AMT1(2852) because they have different data types."

Now I made sure that the data sitting on the AS400 is of type decimal and the SQL server column is the same. What else do I need to look for?

Thanks for the information.

Go to the advanced editor of the source component and compare the datatypes of the input and output column...that may give you a clue. Also remeber that there is a data conversion transform available in data flow.|||

how did you do that, may I ask?

I suggest you change the name of the "output column" in the

datasource this is done by

1. right clicking the datasource

2. show advance editor

3. click input or output properties

4. expand the datareader output if its a datareader

5. click on output columns.

6. look for the columns you want to change and click it,

7. Add an "x" in the column "name" property say "xuser_id" for the orginal column "user_id"

8. click ok

9. check the mappings column mapping by following steps 1 and 2

10 add a derived column transform.

12. transform the column to the desired format

13. in the derived column transform make sure that the "derived Column" property is set to "new column"

and in the derived column name remove the "x" we add in step no. 7 of course you need to set the right format

wheewww. this is long... hahaha

Different collations on a single server

I have 2 sql server databases on a single server and each database working with a web site written with VBScript. One database with a collation of SQL_Latin1_General_CP1254_CI_AS and another databse with a collation of SQL_Latin1_General_CP850_CI_AS. Because of the server's windows character setting which is set to "English" the web site working with "CP850" displays NVARCHAR characters correctly, but the web site with "CP1254" displays some of the special turkish NVARCHAR characters not correctly. How can I solve this problem? Note : Query Analyzer displays the characters correctly on the database with a collation of "1254"Did you set Session.CodePage or the CodePage server-side directive to
tell IIS to encode outgoing strings as Unicode, and set the META CHARSET
property to tell the client browser to decode the page as Unicode?
Here's some simple ASP code that demonstrates how to round-trip Unicode
data from arbitrary languages from web browser->IIS->ASP->SQL and back
out to the browser:
http://groups.google.com/groups?q=bartd+asp&hl=en&lr=&ie=UTF-8&selm=JrIV8D
ZDDHA.2496%40cpmsftngxa06.phx.gbl&rnum=1
If this line wraps, try this shorter URL:
http://tinyurl.com/2zygn
Try this out -- it should work if you're using IIS 5 (Windows 2000) or
later. The only thing you should need to change is the connection string
(strCnn) in SQLNLS_UTF8.ASP to point to your SQL Server and provide a
valid login/password.
HTH,
Bart
--
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no
rights.
Thread-Topic: Different collations on a single server
thread-index: AcPww2+N7ky8j5FoSwCPaUe5Q6LzGg==X-Tomcat-NG: microsoft.public.sqlserver.server
From: "=?Utf-8?B?bWFocnV0aQ==?=" <anonymous@.discussions.microsoft.com>
Subject: Different collations on a single server
Date: Wed, 11 Feb 2004 09:21:07 -0800
Lines: 1
Message-ID: <FCBF0A2A-02A5-447E-890F-5DD52D4A550D@.microsoft.com>
MIME-Version: 1.0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
Newsgroups: microsoft.public.sqlserver.server
Path: cpmsftngxa07.phx.gbl
Xref: cpmsftngxa07.phx.gbl microsoft.public.sqlserver.server:328749
NNTP-Posting-Host: tk2msftcmty1.phx.gbl 10.40.1.180
X-Tomcat-NG: microsoft.public.sqlserver.server
I have 2 sql server databases on a single server and each database
working with a web site written with VBScript. One database with a
collation of SQL_Latin1_General_CP1254_CI_AS and another databse with a
collation of SQL_Latin1_General_CP850_CI_AS. Because of the server's
windows character setting which is set to "English" the web site working
with "CP850" displays NVARCHAR characters correctly, but the web site
with "CP1254" displays some of the special turkish NVARCHAR characters
not correctly. How can I solve this problem? Note : Query Analyzer
displays the characters correctly on the database with a collation of
"1254"

Different collations on a single server

I have 2 sql server databases on a single server and each database working w
ith a web site written with VBScript. One database with a collation of SQL_L
atin1_General_CP1254_CI_AS and another databse with a collation of SQL_Latin
1_General_CP850_CI_AS. Beca
use of the server's windows character setting which is set to "English" the
web site working with "CP850" displays NVARCHAR characters correctly, but th
e web site with "CP1254" displays some of the special turkish NVARCHAR chara
cters not correctly. How ca
n I solve this problem? Note : Query Analyzer displays the characters correc
tly on the database with a collation of "1254"Did you set Session.CodePage or the CodePage server-side directive to
tell IIS to encode outgoing strings as Unicode, and set the META CHARSET
property to tell the client browser to decode the page as Unicode?
Here's some simple ASP code that demonstrates how to round-trip Unicode
data from arbitrary languages from web browser->IIS->ASP->SQL and back
out to the browser:
http://groups.google.com/groups?q=b...F-8&selm=JrIV8D
ZDDHA.2496%40cpmsftngxa06.phx.gbl&rnum=1
If this line wraps, try this shorter URL:
http://tinyurl.com/2zygn
Try this out -- it should work if you're using IIS 5 (Windows 2000) or
later. The only thing you should need to change is the connection string
(strCnn) in SQLNLS_UTF8.ASP to point to your SQL Server and provide a
valid login/password.
HTH,
Bart
--
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no
rights.
Thread-Topic: Different collations on a single server
thread-index: AcPww2+N7ky8j5FoSwCPaUe5Q6LzGg==
X-Tomcat-NG: microsoft.public.sqlserver.server
From: "examnotes" <anonymous@.discussions.microsoft.com>
Subject: Different collations on a single server
Date: Wed, 11 Feb 2004 09:21:07 -0800
Lines: 1
Message-ID: <FCBF0A2A-02A5-447E-890F-5DD52D4A550D@.microsoft.com>
MIME-Version: 1.0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
Newsgroups: microsoft.public.sqlserver.server
Path: cpmsftngxa07.phx.gbl
Xref: cpmsftngxa07.phx.gbl microsoft.public.sqlserver.server:328749
NNTP-Posting-Host: tk2msftcmty1.phx.gbl 10.40.1.180
X-Tomcat-NG: microsoft.public.sqlserver.server
I have 2 sql server databases on a single server and each database
working with a web site written with VBScript. One database with a
collation of SQL_Latin1_General_CP1254_CI_AS and another databse with a
collation of SQL_Latin1_General_CP850_CI_AS. Because of the server's
windows character setting which is set to "English" the web site working
with "CP850" displays NVARCHAR characters correctly, but the web site
with "CP1254" displays some of the special turkish NVARCHAR characters
not correctly. How can I solve this problem? Note : Query Analyzer
displays the characters correctly on the database with a collation of
"1254"

Tuesday, February 14, 2012

Differences betwee 2000 and 2005 SQL

Hi,

I have a simple sql statement that used to work in SQL 2000 that isn't working in SQL 2005. The order by clause doesn't seem to have any effect on the result set. The sql statement is:

ALTER VIEW dbo.SELECT_PP_END
AS
SELECT TOP 100 PERCENT

PP_PERIOD_ID,

CONVERT(VARCHAR, PP_END_DATE, 101) AS PP
FROM dbo.PP_PERIODS
ORDER BY PP_END_DATE DESC

The period end date is appearing in ascinding order on sql server 2005 and in the correct order in sql 2000. Any idea? Thank you for your help

- T.A.

How about this..

Code Snippet


ALTER VIEW dbo.SELECT_PP_END
AS
SELECT TOP 100 PERCENT
PP_PERIOD_ID,
CONVERT(VARCHAR, PP_END_DATE, 101) AS PP,
rank() over (order by PP_END_DATE desc) as Rank
FROM dbo.PP_PERIODS
ORDER BY PP_END_DATE DESC


|||

This has been answered to your almost identical post in the 'Setup and Upgrade' forum, here.

It is best to not 'multi-post' like this. Post in one forum and wait for a response. That shows respect for the folks here since they don't have to spend time answering a question that was answered in another forum.

|||

Sorry about the multi-post Sankar Reddy answer is what i was looking for.

Thanks

T.A.

|||

You're selecting another work-around that you 'may' have to fix in the future.

It is 'smarter' to use 'best practices' from the beginning, or as soon as you become aware of the need to follow better and more robust methods.

|||Use this forum to learn things to do the right way instead of getting quick answers. Listen to the masters when they give advice to you. I showed you how to get that result and it may not be the right way of doing things when you consider bigger picture. It shouldn't be a big deal to add order by clause to views and I am not sure why it adds more work to you.
|||

Thank you guys,

I had to do a massive change on my application using "select" statements rather than using the view name.

I assume views do not output the order by due to execution cost.

that is the only reasonable conclusion i could think of.

Thanks Again

T.A.