Cursor or While Loop or neither?

Hi,
I am looking for a way to pick specific records from a view I have
created. The data from my view looks like this:
machineid serverdate kioskdate kiosktime
ITG70000011 2004-09-30 01:59:52.000 2004-09-28 00:00:00.000 1900-01-01
02:02:28.000
ITG70000011 2004-09-30 01:59:51.000 2004-09-28 00:00:00.000 1900-01-01
02:02:24.000
ITG70000011 2004-09-30 01:59:51.000 2004-09-28 00:00:00.000 1900-01-01
02:02:24.000
ITG70000010 2004-09-30 01:59:50.000 2004-09-28 00:00:00.000 1900-01-01
02:02:01.000
ITG70000010 2004-09-30 01:59:50.000 2004-09-28 00:00:00.000 1900-01-01
02:02:23.000
ITG70000010 2004-09-30 01:59:50.000 2004-09-28 00:00:00.000 1900-01-01
02:02:23.000
ITG70000009 2004-09-30 01:59:49.000 2004-09-28 00:00:00.000 1900-01-01
02:02:00.000
ITG70000009 2004-09-30 01:59:48.000 2004-09-27 00:00:00.000 1900-01-01
02:02:29.000
ITG70000009 2004-09-30 01:59:48.000 2004-09-27 00:00:00.000 1900-01-01
02:02:30.000
ITG70000008 2004-09-30 01:59:47.000 2004-09-27 00:00:00.000 1900-01-01
02:02:29.000
ITG70000008 2004-09-30 01:59:47.000 2004-09-27 00:00:00.000 1900-01-01
02:02:29.000
ITG70000008 2004-09-30 01:59:46.000 2004-09-27 00:00:00.000 1900-01-01
02:02:28.000
I would like to perform a select which displays every distinct
machineid ordered by serverdate, so the above resultset would look
like this:
ITG70000011 2004-09-30 01:59:52.000 2004-09-28 00:00:00.000 1900-01-01
02:02:28.000
ITG70000010 2004-09-30 01:59:50.000 2004-09-28 00:00:00.000 1900-01-01
02:02:01.000
ITG70000009 2004-09-30 01:59:49.000 2004-09-28 00:00:00.000 1900-01-01
02:02:00.000
ITG70000008 2004-09-30 01:59:47.000 2004-09-27 00:00:00.000 1900-01-01
02:02:29.000
I have tried using select distinct machine, serverdate, kioskdate from
V_MyView however this still returns every single result. Do I need to
use a Cursor or a while..loop?
Thanks
ScottThis is just a guess, but perhaps you are loooking for something like this?
SELECT machineid, MAX(serverdate) as serverdate,
MAX(kioskdate) AS kioskdate, MAX(kiosktime) AS kiosktime
FROM V_MyView
By the way, why do you store kiosk 'date' and 'time' in two different
columns?
--
Keith
"scott" <quackhandle1975@.yahoo.co.uk> wrote in message
news:3af9ea3d.0409300540.37b38151@.posting.google.com...
> Hi,
> I am looking for a way to pick specific records from a view I have
> created. The data from my view looks like this:
> machineid serverdate kioskdate kiosktime
> ITG70000011 2004-09-30 01:59:52.000 2004-09-28 00:00:00.000 1900-01-01
> 02:02:28.000
> ITG70000011 2004-09-30 01:59:51.000 2004-09-28 00:00:00.000 1900-01-01
> 02:02:24.000
> ITG70000011 2004-09-30 01:59:51.000 2004-09-28 00:00:00.000 1900-01-01
> 02:02:24.000
> ITG70000010 2004-09-30 01:59:50.000 2004-09-28 00:00:00.000 1900-01-01
> 02:02:01.000
> ITG70000010 2004-09-30 01:59:50.000 2004-09-28 00:00:00.000 1900-01-01
> 02:02:23.000
> ITG70000010 2004-09-30 01:59:50.000 2004-09-28 00:00:00.000 1900-01-01
> 02:02:23.000
> ITG70000009 2004-09-30 01:59:49.000 2004-09-28 00:00:00.000 1900-01-01
> 02:02:00.000
> ITG70000009 2004-09-30 01:59:48.000 2004-09-27 00:00:00.000 1900-01-01
> 02:02:29.000
> ITG70000009 2004-09-30 01:59:48.000 2004-09-27 00:00:00.000 1900-01-01
> 02:02:30.000
> ITG70000008 2004-09-30 01:59:47.000 2004-09-27 00:00:00.000 1900-01-01
> 02:02:29.000
> ITG70000008 2004-09-30 01:59:47.000 2004-09-27 00:00:00.000 1900-01-01
> 02:02:29.000
> ITG70000008 2004-09-30 01:59:46.000 2004-09-27 00:00:00.000 1900-01-01
> 02:02:28.000
> I would like to perform a select which displays every distinct
> machineid ordered by serverdate, so the above resultset would look
> like this:
> ITG70000011 2004-09-30 01:59:52.000 2004-09-28 00:00:00.000 1900-01-01
> 02:02:28.000
> ITG70000010 2004-09-30 01:59:50.000 2004-09-28 00:00:00.000 1900-01-01
> 02:02:01.000
> ITG70000009 2004-09-30 01:59:49.000 2004-09-28 00:00:00.000 1900-01-01
> 02:02:00.000
> ITG70000008 2004-09-30 01:59:47.000 2004-09-27 00:00:00.000 1900-01-01
> 02:02:29.000
> I have tried using select distinct machine, serverdate, kioskdate from
> V_MyView however this still returns every single result. Do I need to
> use a Cursor or a while..loop?
> Thanks
>
> Scott|||I think he will also need a GROUP BY clause.
SELECT machineid, MAX(serverdate) as serverdate,
MAX(kioskdate) AS kioskdate, MAX(kiosktime) AS kiosktime
FROM V_MyView
GROUP BY machineid
"Keith Kratochvil" wrote:
> This is just a guess, but perhaps you are loooking for something like this?
> SELECT machineid, MAX(serverdate) as serverdate,
> MAX(kioskdate) AS kioskdate, MAX(kiosktime) AS kiosktime
> FROM V_MyView
> By the way, why do you store kiosk 'date' and 'time' in two different
> columns?
> --
> Keith
>
> "scott" <quackhandle1975@.yahoo.co.uk> wrote in message
> news:3af9ea3d.0409300540.37b38151@.posting.google.com...
> > Hi,
> >
> > I am looking for a way to pick specific records from a view I have
> > created. The data from my view looks like this:
> >
> > machineid serverdate kioskdate kiosktime
> >
> > ITG70000011 2004-09-30 01:59:52.000 2004-09-28 00:00:00.000 1900-01-01
> > 02:02:28.000
> > ITG70000011 2004-09-30 01:59:51.000 2004-09-28 00:00:00.000 1900-01-01
> > 02:02:24.000
> > ITG70000011 2004-09-30 01:59:51.000 2004-09-28 00:00:00.000 1900-01-01
> > 02:02:24.000
> > ITG70000010 2004-09-30 01:59:50.000 2004-09-28 00:00:00.000 1900-01-01
> > 02:02:01.000
> > ITG70000010 2004-09-30 01:59:50.000 2004-09-28 00:00:00.000 1900-01-01
> > 02:02:23.000
> > ITG70000010 2004-09-30 01:59:50.000 2004-09-28 00:00:00.000 1900-01-01
> > 02:02:23.000
> > ITG70000009 2004-09-30 01:59:49.000 2004-09-28 00:00:00.000 1900-01-01
> > 02:02:00.000
> > ITG70000009 2004-09-30 01:59:48.000 2004-09-27 00:00:00.000 1900-01-01
> > 02:02:29.000
> > ITG70000009 2004-09-30 01:59:48.000 2004-09-27 00:00:00.000 1900-01-01
> > 02:02:30.000
> > ITG70000008 2004-09-30 01:59:47.000 2004-09-27 00:00:00.000 1900-01-01
> > 02:02:29.000
> > ITG70000008 2004-09-30 01:59:47.000 2004-09-27 00:00:00.000 1900-01-01
> > 02:02:29.000
> > ITG70000008 2004-09-30 01:59:46.000 2004-09-27 00:00:00.000 1900-01-01
> > 02:02:28.000
> >
> > I would like to perform a select which displays every distinct
> > machineid ordered by serverdate, so the above resultset would look
> > like this:
> >
> > ITG70000011 2004-09-30 01:59:52.000 2004-09-28 00:00:00.000 1900-01-01
> > 02:02:28.000
> > ITG70000010 2004-09-30 01:59:50.000 2004-09-28 00:00:00.000 1900-01-01
> > 02:02:01.000
> > ITG70000009 2004-09-30 01:59:49.000 2004-09-28 00:00:00.000 1900-01-01
> > 02:02:00.000
> > ITG70000008 2004-09-30 01:59:47.000 2004-09-27 00:00:00.000 1900-01-01
> > 02:02:29.000
> >
> > I have tried using select distinct machine, serverdate, kioskdate from
> > V_MyView however this still returns every single result. Do I need to
> > use a Cursor or a while..loop?
> >
> > Thanks
> >
> >
> >
> > Scott
>|||Yep, missed that one. I hit Send too quickly.
--
Keith
"mthomason" <mthomason@.discussions.microsoft.com> wrote in message
news:A25A8079-9D5E-4DAB-A56B-E4BA779DF386@.microsoft.com...
> I think he will also need a GROUP BY clause.
> SELECT machineid, MAX(serverdate) as serverdate,
> MAX(kioskdate) AS kioskdate, MAX(kiosktime) AS kiosktime
> FROM V_MyView
> GROUP BY machineid
> "Keith Kratochvil" wrote:
> > This is just a guess, but perhaps you are loooking for something like
this?
> >
> > SELECT machineid, MAX(serverdate) as serverdate,
> > MAX(kioskdate) AS kioskdate, MAX(kiosktime) AS kiosktime
> > FROM V_MyView
> >
> > By the way, why do you store kiosk 'date' and 'time' in two different
> > columns?
> >
> > --
> > Keith
> >
> >
> > "scott" <quackhandle1975@.yahoo.co.uk> wrote in message
> > news:3af9ea3d.0409300540.37b38151@.posting.google.com...
> > > Hi,
> > >
> > > I am looking for a way to pick specific records from a view I have
> > > created. The data from my view looks like this:
> > >
> > > machineid serverdate kioskdate kiosktime
> > >
> > > ITG70000011 2004-09-30 01:59:52.000 2004-09-28 00:00:00.000 1900-01-01
> > > 02:02:28.000
> > > ITG70000011 2004-09-30 01:59:51.000 2004-09-28 00:00:00.000 1900-01-01
> > > 02:02:24.000
> > > ITG70000011 2004-09-30 01:59:51.000 2004-09-28 00:00:00.000 1900-01-01
> > > 02:02:24.000
> > > ITG70000010 2004-09-30 01:59:50.000 2004-09-28 00:00:00.000 1900-01-01
> > > 02:02:01.000
> > > ITG70000010 2004-09-30 01:59:50.000 2004-09-28 00:00:00.000 1900-01-01
> > > 02:02:23.000
> > > ITG70000010 2004-09-30 01:59:50.000 2004-09-28 00:00:00.000 1900-01-01
> > > 02:02:23.000
> > > ITG70000009 2004-09-30 01:59:49.000 2004-09-28 00:00:00.000 1900-01-01
> > > 02:02:00.000
> > > ITG70000009 2004-09-30 01:59:48.000 2004-09-27 00:00:00.000 1900-01-01
> > > 02:02:29.000
> > > ITG70000009 2004-09-30 01:59:48.000 2004-09-27 00:00:00.000 1900-01-01
> > > 02:02:30.000
> > > ITG70000008 2004-09-30 01:59:47.000 2004-09-27 00:00:00.000 1900-01-01
> > > 02:02:29.000
> > > ITG70000008 2004-09-30 01:59:47.000 2004-09-27 00:00:00.000 1900-01-01
> > > 02:02:29.000
> > > ITG70000008 2004-09-30 01:59:46.000 2004-09-27 00:00:00.000 1900-01-01
> > > 02:02:28.000
> > >
> > > I would like to perform a select which displays every distinct
> > > machineid ordered by serverdate, so the above resultset would look
> > > like this:
> > >
> > > ITG70000011 2004-09-30 01:59:52.000 2004-09-28 00:00:00.000 1900-01-01
> > > 02:02:28.000
> > > ITG70000010 2004-09-30 01:59:50.000 2004-09-28 00:00:00.000 1900-01-01
> > > 02:02:01.000
> > > ITG70000009 2004-09-30 01:59:49.000 2004-09-28 00:00:00.000 1900-01-01
> > > 02:02:00.000
> > > ITG70000008 2004-09-30 01:59:47.000 2004-09-27 00:00:00.000 1900-01-01
> > > 02:02:29.000
> > >
> > > I have tried using select distinct machine, serverdate, kioskdate from
> > > V_MyView however this still returns every single result. Do I need to
> > > use a Cursor or a while..loop?
> > >
> > > Thanks
> > >
> > >
> > >
> > > Scott
> >
> >|||On 30 Sep 2004 06:40:27 -0700, scott wrote:
>Hi,
>I am looking for a way to pick specific records from a view I have
>created. The data from my view looks like this:
(snip)
>I would like to perform a select which displays every distinct
>machineid ordered by serverdate, so the above resultset would look
>like this:
(snip)
Hi Scott,
Looking at your expected results, it looks as if you want to find the row
with the highest serverdate for each machineid, then display all columns
of that row. Correct?
>I have tried using select distinct machine, serverdate, kioskdate from
>V_MyView however this still returns every single result. Do I need to
>use a Cursor or a while..loop?
Neither. Try the following SQL:
SELECT machineid, serverdate, kioskdate, kiosktime
FROM YourView AS a
WHERE NOT EXISTS
(SELECT *
FROM YourView AS b
WHERE b.machineid = a.machineid
AND b.serverdate > a.serverdate)
Or try the following alternative:
SELECT a.machineid, a.serverdate, a.kioskdate, a.kiosktime
FROM YourView AS a
INNER JOIN (SELECT machineid, MAX(serverdate)
FROM YourView
GROUP BY machineid) AS b(machineid, maxserverdate)
ON a.machineid = b.machineid
AND a.serverdate = b.maxserverdate
(Both queries are untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks for all your replies, Keith is the winner, can't beleive I
forgot about using "max"!
Rgds,
Scott
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message news:<#LLMqlwpEHA.592@.TK2MSFTNGP11.phx.gbl>...
> Yep, missed that one. I hit Send too quickly.
> --
> Keith
>
> "mthomason" <mthomason@.discussions.microsoft.com> wrote in message
> news:A25A8079-9D5E-4DAB-A56B-E4BA779DF386@.microsoft.com...
> > I think he will also need a GROUP BY clause.
> >
> > SELECT machineid, MAX(serverdate) as serverdate,
> > MAX(kioskdate) AS kioskdate, MAX(kiosktime) AS kiosktime
> > FROM V_MyView
> > GROUP BY machineid
> >
> > "Keith Kratochvil" wrote:
> >
> > > This is just a guess, but perhaps you are loooking for something like
> this?
> > >
> > > SELECT machineid, MAX(serverdate) as serverdate,
> > > MAX(kioskdate) AS kioskdate, MAX(kiosktime) AS kiosktime
> > > FROM V_MyView
> > >
> > > By the way, why do you store kiosk 'date' and 'time' in two different
> > > columns?
> > >
> > > --
> > > Keith
> > >
> > >
> > > "scott" <quackhandle1975@.yahoo.co.uk> wrote in message
> > > news:3af9ea3d.0409300540.37b38151@.posting.google.com...
> > > > Hi,
> > > >
> > > > I am looking for a way to pick specific records from a view I have
> > > > created. The data from my view looks like this:
> > > >
> > > > machineid serverdate kioskdate kiosktime
> > > >
> > > > ITG70000011 2004-09-30 01:59:52.000 2004-09-28 00:00:00.000 1900-01-01
> > > > 02:02:28.000
> > > > ITG70000011 2004-09-30 01:59:51.000 2004-09-28 00:00:00.000 1900-01-01
> > > > 02:02:24.000
> > > > ITG70000011 2004-09-30 01:59:51.000 2004-09-28 00:00:00.000 1900-01-01
> > > > 02:02:24.000
> > > > ITG70000010 2004-09-30 01:59:50.000 2004-09-28 00:00:00.000 1900-01-01
> > > > 02:02:01.000
> > > > ITG70000010 2004-09-30 01:59:50.000 2004-09-28 00:00:00.000 1900-01-01
> > > > 02:02:23.000
> > > > ITG70000010 2004-09-30 01:59:50.000 2004-09-28 00:00:00.000 1900-01-01
> > > > 02:02:23.000
> > > > ITG70000009 2004-09-30 01:59:49.000 2004-09-28 00:00:00.000 1900-01-01
> > > > 02:02:00.000
> > > > ITG70000009 2004-09-30 01:59:48.000 2004-09-27 00:00:00.000 1900-01-01
> > > > 02:02:29.000
> > > > ITG70000009 2004-09-30 01:59:48.000 2004-09-27 00:00:00.000 1900-01-01
> > > > 02:02:30.000
> > > > ITG70000008 2004-09-30 01:59:47.000 2004-09-27 00:00:00.000 1900-01-01
> > > > 02:02:29.000
> > > > ITG70000008 2004-09-30 01:59:47.000 2004-09-27 00:00:00.000 1900-01-01
> > > > 02:02:29.000
> > > > ITG70000008 2004-09-30 01:59:46.000 2004-09-27 00:00:00.000 1900-01-01
> > > > 02:02:28.000
> > > >
> > > > I would like to perform a select which displays every distinct
> > > > machineid ordered by serverdate, so the above resultset would look
> > > > like this:
> > > >
> > > > ITG70000011 2004-09-30 01:59:52.000 2004-09-28 00:00:00.000 1900-01-01
> > > > 02:02:28.000
> > > > ITG70000010 2004-09-30 01:59:50.000 2004-09-28 00:00:00.000 1900-01-01
> > > > 02:02:01.000
> > > > ITG70000009 2004-09-30 01:59:49.000 2004-09-28 00:00:00.000 1900-01-01
> > > > 02:02:00.000
> > > > ITG70000008 2004-09-30 01:59:47.000 2004-09-27 00:00:00.000 1900-01-01
> > > > 02:02:29.000
> > > >
> > > > I have tried using select distinct machine, serverdate, kioskdate from
> > > > V_MyView however this still returns every single result. Do I need to
> > > > use a Cursor or a while..loop?
> > > >
> > > > Thanks
> > > >
> > > >
> > > >
> > > > Scott
> > >
> > >

Cursor loop

Hello,

I've created a stored procedure that loops through a cursor, with the
following example code:

DECLARE curPeriod CURSOR LOCAL for SELECT * FROM tblPeriods
DECLARE @.intYear smallint
DECLARE @.intPeriod smallint
DECLARE @.strTekst varchar(50)

OPEN curPeriod

WHILE @.@.FETCH_STATUS=0

BEGIN

FETCH NEXT FROM curPeriod INTO @.intYear, @.intPeriod

SET @.strTekst = CONVERT(varchar, @.intPeriod)

PRINT @.strTekst

END

CLOSE curPeriod
DEALLOCATE curPeriod

The problem is that this loop only executes one time, when I call the
stored procedure a second or third time, nothing happens. It seems that
the Cursor stays at the last record or that @.@.Fetch_status isn't 0. But
I Deallocate the cursor. I have to restart the SQL Server before the
stored procedure can be used again.

Does anyone know why the loop can execute only 1 time?

Greetings,
Chris

*** Sent via Developersdex http://www.developersdex.com ***Chris Zopers wrote:

Quote:

Originally Posted by

Hello,
>
I've created a stored procedure that loops through a cursor, with the
following example code:
>
DECLARE curPeriod CURSOR LOCAL for SELECT * FROM tblPeriods
DECLARE @.intYear smallint
DECLARE @.intPeriod smallint
DECLARE @.strTekst varchar(50)
>
OPEN curPeriod
>
WHILE @.@.FETCH_STATUS=0
>
BEGIN
>
FETCH NEXT FROM curPeriod INTO @.intYear, @.intPeriod
>
SET @.strTekst = CONVERT(varchar, @.intPeriod)
>
PRINT @.strTekst
>
END
>
CLOSE curPeriod
DEALLOCATE curPeriod
>
The problem is that this loop only executes one time, when I call the
stored procedure a second or third time, nothing happens. It seems that
the Cursor stays at the last record or that @.@.Fetch_status isn't 0. But
I Deallocate the cursor. I have to restart the SQL Server before the
stored procedure can be used again.
>
Does anyone know why the loop can execute only 1 time?
>
Greetings,
Chris

Hi Chris,

When you say you have to restart SQL Server before it can be used
again, do you mean the server or just Query Analyser?

I suspect the issue you're having is when you next enter the stored
procedure, the FETCH_STATUS is still as it was at the end of the last
time through the loop - non-zero, and so the loop isn't executed.

I've never seen a good pattern for doing cursors that doesn't look
messy (Since most practicioners tend to try to avoid them in the first
place, no-one spends much time tidying them up).

Normal pattern for me is:

declare cursor x for select ...
declare <variables to hold the columns>

open x

fetch next from x into <list of variables>
while @.@.FETCH_STATUS = 0
begin
--Do stuff

fetch next from x into <list of variables>
end

close x
deallocate x

in short, I've never found a way to do it which doesn't have to have
the same fetch statement in two places.

Damien

PS - Usual recommendation would be to have a list of columns, rather
than select * from... However, there is disagreement over this
particular recommendation, I'd suggest you search the archives for some
lively debate on the matter.|||Chris Zopers (test123test12@.12move.nl) writes:

Quote:

Originally Posted by

I've created a stored procedure that loops through a cursor, with the
following example code:
>
DECLARE curPeriod CURSOR LOCAL for SELECT * FROM tblPeriods
DECLARE @.intYear smallint
DECLARE @.intPeriod smallint
DECLARE @.strTekst varchar(50)
>
OPEN curPeriod
>
WHILE @.@.FETCH_STATUS=0
BEGIN
FETCH NEXT FROM curPeriod INTO @.intYear, @.intPeriod
SET @.strTekst = CONVERT(varchar, @.intPeriod)
PRINT @.strTekst
END
>
CLOSE curPeriod
DEALLOCATE curPeriod
>
The problem is that this loop only executes one time, when I call the
stored procedure a second or third time, nothing happens.

This is because you check @.@.fetch_status before you fetch. This is how
you should write cursor loop:

DECLARE cur INSENSITIVE CURSOR FOR
SELECT col1, col2 FROM tbl

OPEN cur

WHILE 1 = 1
BEGIN
FETCH cur INTO @.par1, @.par2
IF @.@.fetch_status <0
BREAK

-- Do stuff
END

DEALLOCATE cur

Beyond the structure of the cursor loop, please notice:

1) Never use SELECT * with cursor declarations. Add a column to the
table, and your code breaks. That's bad.

2) The cursor must be declared as INSENSITIVE or STATIC (the latter
can be combined with LOCAL, the first cannot). With no specification
you get a dynamic cursor, which is rarely what you want. But dynamic
cursors can have bad impact on both performance and funcion.

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

Cursor isn''t being created.

ElementTypeDep_Cursor seems is not being created in this stored procedure:

Code Snippet

ALTER procedure spCopyTemplateElementTypesToIssues

@.TemplateRecno integer,

@.ProjRecNo integer,

@.IssueRecNo integer

as

declare @.ElementTypeRecno integer

declare @.ElementTypeDepRecno integer

declare @.ProjTypeRecno integer

declare @.PreElementRecNo integer

declare @.PostElementRecno integer

declare @.Count integer

DECLARE element_Cursor CURSOR FOR

SELECT ElementTypeRecNo

FROM dbo.tblTemplateElementType

where TemplateRecno = @.TemplateRecNo

OPEN element_cursor

FETCH NEXT FROM Element_Cursor into @.ElementTypeRecno

--delete from tblElementCPO

WHILE @.@.FETCH_STATUS = 0

BEGIN

select @.count = count (*)

from tblElementCPO

where ProjRecno = @.ProjRecNo

and IssueRecno = @.IssueRecno

and TemplateRecno = @.TemplateRecno

and ElementTypeRecno = @.ElementTypeRecNo

if @.Count = 0

begin

insert into tblElementCPO(Ignore, ElementTypeRecno, IssueRecno,

ProjRecno, MaxAttemptNum, IsMileStone, ComponentOnly, Phase,

TaskHoursEst, TemplateRecNo, ChangeDate, ChangePerson)

values (0, @.ElementTypeRecno, @.IssueRecno,

@.ProjRecno, 5,0,6,99,

99,@.TemplateRecno, getdate(), current_user)

end

FETCH NEXT FROM element_Cursor into @.ElementTypeRecno

END

CLOSE element_Cursor

DEALLOCATE element_Cursor

select @.Count = count (*)

FROM dbo.tblElementTypeDep

where TemplateRecno = @.TemplateRecNo

if @.Count > 0 then

begin

DECLARE ElementTypeDep_Cursor CURSOR FOR

SELECT ElementTypeDepRecNo, PreElementTypeRecNo, PostElementTypeRecno

FROM dbo.tblElementTypeDep

where TemplateRecno = @.TemplateRecNo

OPEN ElementTypeDep_cursor

FETCH NEXT FROM ElementTypeDep_Cursor

into @.ElementTypeDepRecno, @.PreElementRecNo, @.PostElementRecno

WHILE @.@.FETCH_STATUS = 0

BEGIN

select @.Count = count (*)

from tblElementDepCPO

where ElementTypeDepRecno = @.ElementTypeDepRecno

and PreElementRecNo = @.PreElementRecNo

and PostElementRecno = @.PostElementRecno

if @.Count = 0

begin

insert tblElementDepCPO (ElementTypeDepRecno, PreElementRecNo,

PostElementRecno, ChangeDate, ChangePerson)

values (@.ElementTypeDepRecno, @.PreElementRecNo,

@.PostElementRecno, getdate(), current_user)

end

FETCH NEXT FROM ElementTypeDep_Cursor

into @.ElementTypeDepRecno, @.PreElementRecNo, @.PostElementRecno

END

CLOSE elementTypeDep_Cursor

DEALLOCATE elementTypeDep_Cursor

end

called by

Code Snippet

Dim cnQI02414 As New SqlConnection(My.Settings.csQI02414Dev)

Dim cmd As New SqlCommand

Dim reader As SqlDataReader

cmd.CommandText = "spCopyTemplateElementTypesToIssues"

cmd.CommandType = CommandType.StoredProcedure

Dim spmTemplateRecNo As SqlParameter = _

cmd.Parameters.Add("@.TemplateRecNo", SqlDbType.Int)

spmTemplateRecNo.Value = _

Me.cbTemplate.SelectedValue

Dim spmProjRecNo As SqlParameter = _

cmd.Parameters.Add("@.ProjRecNo", SqlDbType.Int)

spmProjRecNo.Value = _

Me.cbProject.SelectedValue

Dim spmIssueRecNo As SqlParameter = _

cmd.Parameters.Add("@.IssueRecNo", SqlDbType.Int)

spmIssueRecNo.Value = _

Me.cbIssue.SelectedValue

cmd.Connection = cnQI02414

cnQI02414.Open()

reader = cmd.ExecuteReader()

' Data is accessible through the DataReader object here.

cnQI02414.Close()

What should I be looking for?

You're not returning anything from the stored procedure. i.e. there is no "select" after you're done inserting. So, your reader will be empty.

To verify if your sproc actually runs, try returning all input and output parameters as the last "select" statement.

Cursor isn''t being created.

ElementTypeDep_Cursor seems is not being created in this stored procedure:

Code Snippet

ALTER procedure spCopyTemplateElementTypesToIssues

@.TemplateRecno integer,

@.ProjRecNo integer,

@.IssueRecNo integer

as

declare @.ElementTypeRecno integer

declare @.ElementTypeDepRecno integer

declare @.ProjTypeRecno integer

declare @.PreElementRecNo integer

declare @.PostElementRecno integer

declare @.Count integer

DECLARE element_Cursor CURSOR FOR

SELECT ElementTypeRecNo

FROM dbo.tblTemplateElementType

where TemplateRecno = @.TemplateRecNo

OPEN element_cursor

FETCH NEXT FROM Element_Cursor into @.ElementTypeRecno

--delete from tblElementCPO

WHILE @.@.FETCH_STATUS = 0

BEGIN

select @.count = count (*)

from tblElementCPO

where ProjRecno = @.ProjRecNo

and IssueRecno = @.IssueRecno

and TemplateRecno = @.TemplateRecno

and ElementTypeRecno = @.ElementTypeRecNo

if @.Count = 0

begin

insert into tblElementCPO(Ignore, ElementTypeRecno, IssueRecno,

ProjRecno, MaxAttemptNum, IsMileStone, ComponentOnly, Phase,

TaskHoursEst, TemplateRecNo, ChangeDate, ChangePerson)

values (0, @.ElementTypeRecno, @.IssueRecno,

@.ProjRecno, 5,0,6,99,

99,@.TemplateRecno, getdate(), current_user)

end

FETCH NEXT FROM element_Cursor into @.ElementTypeRecno

END

CLOSE element_Cursor

DEALLOCATE element_Cursor

select @.Count = count (*)

FROM dbo.tblElementTypeDep

where TemplateRecno = @.TemplateRecNo

if @.Count > 0 then

begin

DECLARE ElementTypeDep_Cursor CURSOR FOR

SELECT ElementTypeDepRecNo, PreElementTypeRecNo, PostElementTypeRecno

FROM dbo.tblElementTypeDep

where TemplateRecno = @.TemplateRecNo

OPEN ElementTypeDep_cursor

FETCH NEXT FROM ElementTypeDep_Cursor

into @.ElementTypeDepRecno, @.PreElementRecNo, @.PostElementRecno

WHILE @.@.FETCH_STATUS = 0

BEGIN

select @.Count = count (*)

from tblElementDepCPO

where ElementTypeDepRecno = @.ElementTypeDepRecno

and PreElementRecNo = @.PreElementRecNo

and PostElementRecno = @.PostElementRecno

if @.Count = 0

begin

insert tblElementDepCPO (ElementTypeDepRecno, PreElementRecNo,

PostElementRecno, ChangeDate, ChangePerson)

values (@.ElementTypeDepRecno, @.PreElementRecNo,

@.PostElementRecno, getdate(), current_user)

end

FETCH NEXT FROM ElementTypeDep_Cursor

into @.ElementTypeDepRecno, @.PreElementRecNo, @.PostElementRecno

END

CLOSE elementTypeDep_Cursor

DEALLOCATE elementTypeDep_Cursor

end

called by

Code Snippet

Dim cnQI02414 As New SqlConnection(My.Settings.csQI02414Dev)

Dim cmd As New SqlCommand

Dim reader As SqlDataReader

cmd.CommandText = "spCopyTemplateElementTypesToIssues"

cmd.CommandType = CommandType.StoredProcedure

Dim spmTemplateRecNo As SqlParameter = _

cmd.Parameters.Add("@.TemplateRecNo", SqlDbType.Int)

spmTemplateRecNo.Value = _

Me.cbTemplate.SelectedValue

Dim spmProjRecNo As SqlParameter = _

cmd.Parameters.Add("@.ProjRecNo", SqlDbType.Int)

spmProjRecNo.Value = _

Me.cbProject.SelectedValue

Dim spmIssueRecNo As SqlParameter = _

cmd.Parameters.Add("@.IssueRecNo", SqlDbType.Int)

spmIssueRecNo.Value = _

Me.cbIssue.SelectedValue

cmd.Connection = cnQI02414

cnQI02414.Open()

reader = cmd.ExecuteReader()

' Data is accessible through the DataReader object here.

cnQI02414.Close()

What should I be looking for?

You're not returning anything from the stored procedure. i.e. there is no "select" after you're done inserting. So, your reader will be empty.

To verify if your sproc actually runs, try returning all input and output parameters as the last "select" statement.

cursor + rounding?

Hi Guys,

I've created a cursor inside a function. When I break it down and execute the code piece by piece, no problem. However, try and parse it together and I get an error - 'Mixing old and new syntax is not allowed?' Something to do with the return statements? as I can alter the function to a procedure and it parses fine. Anyone come across this before?

Also, the values are being rounded when putting them into the cursor, even though I've declared the variables the cursor uses specifically as decimal? How can I get around this please?

Cheers,

Michelle

Michelle:

Can you describe what you are doing? This sounds rather vague.

|||

Blast, it didn't post. Umm, I've sorted the first problem, and I think I may have the answer to the second one. Will give it a go and let you know. Thanks for your help!

Cheers,

Michelle

|||

OK, both of those problems fixed. Should have been

DECLARE product_cursor CURSOR local SCROLL

NOT

DECLARE product_cursor SCROLL CURSOR

and Should have been:

decimal(8,2)

NOT

decimal.

Stupid mistakes! However, now I'm able to parse it fine, but am getting:

Msg 443, Level 16, State 15, Procedure CalculateFreight, Line 15

Invalid use of side-effecting or time-dependent operator in 'SELECT INTO' within a function.

Msg 443, Level 16, State 15, Procedure CalculateFreight, Line 25

Invalid use of side-effecting or time-dependent operator in 'UPDATE' within a function.

when I execute it!?

The SELECT INTO and UPDATE statements are fine when executed alone

Michelle

sql

Cursor

hey all,
i am relatively new to cursors and have created a SP that uses a cursor to populate a table. here is the code

CREATE PROCEDURE sppa_invvoid
(
@.invno int
)

AS

DECLARE @.glTranKey int
DECLARE @.AcctRefKey int
DECLARE @.BatchKey int
DECLARE @.CreateDate datetime
DECLARE @.CreateType smallint
DECLARE @.CurrExchRate float
DECLARE @.CurrID varchar (3)
DECLARE @.ExtCmnt varchar (255)
DECLARE @.FiscPer smallint
DECLARE @.FiscYear varchar (5)
DECLARE @.GLAcctKey int
DECLARE @.JrnlKey int
DECLARE @.JrnlNo int
DECLARE @.PostAmt decimal(15, 3)
DECLARE @.PostAmtHC decimal(15, 3)
DECLARE @.PostCmnt varchar (50)
DECLARE @.PostDate datetime
DECLARE @.PostQty decimal(16, 8)
DECLARE @.SourceModuleNo smallint
DECLARE @.TranDate datetime
DECLARE @.TranKey int
DECLARE @.TranNo varchar (10)
DECLARE @.TranType int
DECLARE @.Companyid varchar(3)
DECLARE @.Batchtype int
DECLARE @.Userid varchar (30)
DECLARE @.Moduleno int
DECLARE @.NextBatchNo int
DECLARE @._oRetVal int
DECLARE @.iTableName varchar(50)
DECLARE @.iCommitFlag int
DECLARE @.NextJrnlNo int

Set @.CompanyID= 'EMA'
Set @.JrnlNo = 139
Set @.iCommitFlag = 1
Set @.JrnlKey = 193
Set @.iTableName='tgltransaction'
Set @.batchtype = 501
Set @.moduleNo = 5
Set @.Userid = 'Admin'

EXECUTE spGetNextBatchNo @.CompanyID, @.BatchType, @.UserId, @.ModuleNo, @.BatchKey OUTPUT, @.NextBatchNo OUTPUT, @._oRetVal OUTPUT

Execute spglGiveNextJrnlNo @.CompanyID, @.JrnlKey, @.iCommitFlag, @.JrnlNo, @.NextJrnlNo OUTPUT


DECLARE cursor_tran CURSOR FOR

select glTranKey, AcctRefKey,CreateDate,CreateType,CurrExchRate,Curr ID,ExtCmnt,FiscPer,FiscYear,GLAcctKey,JrnlKey,Jrnl No,PostAmt,PostAmtHC,PostCmnt,PostDate,PostQty,Sou rceModuleNo,TranDate,TranKey,TranNo,TranType
from tgltransaction where tranno = @.invno

OPEN cursor_tran

FETCH NEXT FROM cursor_tran INTO

@.glTranKey,
@.AcctRefKey,
@.CreateDate,
@.CreateType,
@.CurrExchRate,
@.CurrID,
@.ExtCmnt,
@.FiscPer,
@.FiscYear,
@.GLAcctKey,
@.JrnlKey,
@.JrnlNo,
@.PostAmt,
@.PostAmtHC,
@.PostCmnt,
@.PostDate,
@.PostQty,
@.SourceModuleNo,
@.TranDate,
@.TranKey,
@.TranNo,
@.TranType

WHILE (@.@.FETCH_STATUS <> -1)

BEGIN

Execute spgetnextSurrogateKey @.iTablename , @.glTranKey OUTPUT
/*Execute aaaa*/




INSERT INTO tgltransaction
(glTranKey,
AcctRefKey,
BatchKey,
CreateDate,
CreateType,
CurrExchRate,
CurrID,
ExtCmnt,
FiscPer,
FiscYear,
GLAcctKey,
JrnlKey,
JrnlNo,
PostAmt,
PostAmtHC,
PostCmnt,
PostDate,
PostQty,
SourceModuleNo,
TranDate,
TranKey,
TranNo,
TranType)

Values

(@.glTrankey,
@.AcctRefKey,
@.BatchKey,
@.CreateDate,
@.CreateType,
@.CurrExchRate,
@.CurrID,
@.ExtCmnt,
@.FiscPer,
@.FiscYear,
@.GLAcctKey,
@.JrnlKey,
@.JrnlNo,
@.PostAmt,
@.PostAmtHC,
@.PostCmnt,
@.PostDate,
@.PostQty,
@.SourceModuleNo,
@.TranDate,
@.TranKey,
@.TranNo,
@.TranType)

FETCH NEXT FROM cursor_tran INTO

@.glTranKey,
@.AcctRefKey,
@.CreateDate,
@.CreateType,
@.CurrExchRate,
@.CurrID,
@.ExtCmnt,
@.FiscPer,
@.FiscYear,
@.GLAcctKey,
@.JrnlKey,
@.JrnlNo,
@.PostAmt,
@.PostAmtHC,
@.PostCmnt,
@.PostDate,
@.PostQty,
@.SourceModuleNo,
@.TranDate,
@.TranKey,
@.TranNo,
@.TranType

END
CLOSE cursor_tran
DEALLOCATE cursor_tran
GO

the issue that i am having is after i do the insert, the cursor picks up on the inserted row and it ultimately becomes an infinate loop. what can i do to prevent it from picking up the newly inserted rows. thanks alot

tibornevermind, just saw my problem. of course it will loop if youre using the same table for insert and cursor, lol.|||Glad to see you've figured out how to use cursors. Now do yourself a favor and forget about them, and use set-based processing instead.

Your spglGiveNextJrnlNo procedure should be converted to a user-defined function, (or better yet, dumped altogether), and then you can write your code as a much shorter and much more efficient INSERT statement.|||Hey, a wiseman once said:

Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

http://www.sqlteam.com/forums/pop_profile.asp?mode=display&id=1578

Current errorlog not showing

Hello everybody,
last week I had to move my master database en with it the location of
my SQL Server errorlogs. Since then new errorlogs are created in the
new location specified in the startup parameter, but in EM I can only
see the archived ones, not the current.
Turns out that xp_enumerrorlogs don't return 0, the number for the
current errorlog. Executing sp_readerrorlog returns the content of
current errorlog.
Any ideas how to fix this?
Tia Markus
From the EM, click on Server properties, General Tab & click startup
parameters. The location of the error log can be changed there.
Alternatively, you can change the location directly in the registry as well.
Anith
|||Have a look at startup parameters in EM. There is a -e entry for the
location of the errorlog, chaging it here should resolve. The value is being
saved under the following Registry Key
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ MSSQLServer\Parameters
Open EM / Right Click "Server" / Properties and choose Startup Parameters
HTH. Ryan
"MarkusB" <m.bohse@.quest-consultants.com> wrote in message
news:1139839000.665337.12560@.g44g2000cwa.googlegro ups.com...
> Hello everybody,
> last week I had to move my master database en with it the location of
> my SQL Server errorlogs. Since then new errorlogs are created in the
> new location specified in the startup parameter, but in EM I can only
> see the archived ones, not the current.
> Turns out that xp_enumerrorlogs don't return 0, the number for the
> current errorlog. Executing sp_readerrorlog returns the content of
> current errorlog.
> Any ideas how to fix this?
> Tia Markus
>
|||Thanks guys,
I found the problem in the mean time. I had checked the startup
parameter and the registry before, but missed the fact that there was a
trailing space. This had the annoying effect that archive logs had a
name like ERRORLOG .1 and thus appeared in EM. For the current log
Windows removes the trailing space and that's why xp_enumerrorlogs was
unable to find it.
Markus
sql

Current errorlog not showing

Hello everybody,
last week I had to move my master database en with it the location of
my SQL Server errorlogs. Since then new errorlogs are created in the
new location specified in the startup parameter, but in EM I can only
see the archived ones, not the current.
Turns out that xp_enumerrorlogs don't return 0, the number for the
current errorlog. Executing sp_readerrorlog returns the content of
current errorlog.
Any ideas how to fix this?
Tia MarkusFrom the EM, click on Server properties, General Tab & click startup
parameters. The location of the error log can be changed there.
Alternatively, you can change the location directly in the registry as well.
Anith|||Have a look at startup parameters in EM. There is a -e entry for the
location of the errorlog, chaging it here should resolve. The value is being
saved under the following Registry Key
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\MSSQLServer\Parameters
Open EM / Right Click "Server" / Properties and choose Startup Parameters
HTH. Ryan
"MarkusB" <m.bohse@.quest-consultants.com> wrote in message
news:1139839000.665337.12560@.g44g2000cwa.googlegroups.com...
> Hello everybody,
> last week I had to move my master database en with it the location of
> my SQL Server errorlogs. Since then new errorlogs are created in the
> new location specified in the startup parameter, but in EM I can only
> see the archived ones, not the current.
> Turns out that xp_enumerrorlogs don't return 0, the number for the
> current errorlog. Executing sp_readerrorlog returns the content of
> current errorlog.
> Any ideas how to fix this?
> Tia Markus
>|||Thanks guys,
I found the problem in the mean time. I had checked the startup
parameter and the registry before, but missed the fact that there was a
trailing space. This had the annoying effect that archive logs had a
name like ERRORLOG .1 and thus appeared in EM. For the current log
Windows removes the trailing space and that's why xp_enumerrorlogs was
unable to find it.
Markus

Current errorlog not showing

Hello everybody,
last week I had to move my master database en with it the location of
my SQL Server errorlogs. Since then new errorlogs are created in the
new location specified in the startup parameter, but in EM I can only
see the archived ones, not the current.
Turns out that xp_enumerrorlogs don't return 0, the number for the
current errorlog. Executing sp_readerrorlog returns the content of
current errorlog.
Any ideas how to fix this?
Tia MarkusFrom the EM, click on Server properties, General Tab & click startup
parameters. The location of the error log can be changed there.
Alternatively, you can change the location directly in the registry as well.
--
Anith|||Have a look at startup parameters in EM. There is a -e entry for the
location of the errorlog, chaging it here should resolve. The value is being
saved under the following Registry Key
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters
Open EM / Right Click "Server" / Properties and choose Startup Parameters
HTH. Ryan
"MarkusB" <m.bohse@.quest-consultants.com> wrote in message
news:1139839000.665337.12560@.g44g2000cwa.googlegroups.com...
> Hello everybody,
> last week I had to move my master database en with it the location of
> my SQL Server errorlogs. Since then new errorlogs are created in the
> new location specified in the startup parameter, but in EM I can only
> see the archived ones, not the current.
> Turns out that xp_enumerrorlogs don't return 0, the number for the
> current errorlog. Executing sp_readerrorlog returns the content of
> current errorlog.
> Any ideas how to fix this?
> Tia Markus
>|||Thanks guys,
I found the problem in the mean time. I had checked the startup
parameter and the registry before, but missed the fact that there was a
trailing space. This had the annoying effect that archive logs had a
name like ERRORLOG .1 and thus appeared in EM. For the current log
Windows removes the trailing space and that's why xp_enumerrorlogs was
unable to find it.
Markus

Currency Fromatting

Hi Everyone,
I am a bit of a newbie to SQL, I have created a table to hold supplier
invoice details which has three columns to hold currency values i.e.
Nett Value
VAT Value
Gross Value
The columns for VAT and gross calculate automatically from the Nett value,
however the values in the fields have more than 2 digits after the decimal
place i.e.
Nett Value = 275.00
VAT Value = 48.125
Gross Value = 323.125
How do I force the two calculating fields to format the contents to standard
currency format i.e. ￡xxx.xx with only 2 digits after the decimal point?
Hope that makes sense. Any help would be greatly appreciatedNote that in the UK (AFAIK same as the rest of the EU), accounting
regulations require that VAT be calculated on an invoice sub-total, not
on individual items (there may be a difference due to rounding) so what
you are doing will not represent the correct total for accounting
purposes.
Storing both Net and Gross columns in a table is poor design. This is
redundant data and most systems will store just the Net value at detail
level, plus a VAT code.
Formatting is probably best left to your client app or middle tier. If
you must do it in SQL then look up the style parameter of the CONVERT
function. Avoid using MONEY / SMALLMONEY columns in your tables though
as these have some problems with precision in division operations.
David Portas
SQL Server MVP
--|||Thanks for your reply David. I am aware that for official accounting it
would need to be line by line calculated, but my DB if purely for reporting
supplier turnover and not for accounting so I am not overly bothered about
the roundings, but thanks for the pointers I'll go and have aplay and see
what I get.
Cheers
Jonathan
"David Portas" wrote:

> Note that in the UK (AFAIK same as the rest of the EU), accounting
> regulations require that VAT be calculated on an invoice sub-total, not
> on individual items (there may be a difference due to rounding) so what
> you are doing will not represent the correct total for accounting
> purposes.
> Storing both Net and Gross columns in a table is poor design. This is
> redundant data and most systems will store just the Net value at detail
> level, plus a VAT code.
> Formatting is probably best left to your client app or middle tier. If
> you must do it in SQL then look up the style parameter of the CONVERT
> function. Avoid using MONEY / SMALLMONEY columns in your tables though
> as these have some problems with precision in division operations.
> --
> David Portas
> SQL Server MVP
> --
>

Currency format in reporting services

I have what is likely a simple question but I have had no luck in tracking
down an answer so far. I have a report that I've created in RS that is for an
English company and I want to display the output in pounds as opposed to $.
When I go to a given text-box and click the format/currency option all I get
is $. I realize that RS tries to default to the currency setting on the
location the report is run but since the report will be hosted in the states
that's not going to help. All I need to do is format a text box as pounds!
Anyone have any ideas?
Thanks in advance.I noticed that if you click on the text box and look at the properties for it
that there is a language property. Change it from default to English (United
Kingdom) and that will change the currency symbol to the other symbol. I had
something formatted as currency and tried this and it seemed to work.
"littleren" wrote:
> I have what is likely a simple question but I have had no luck in tracking
> down an answer so far. I have a report that I've created in RS that is for an
> English company and I want to display the output in pounds as opposed to $.
> When I go to a given text-box and click the format/currency option all I get
> is $. I realize that RS tries to default to the currency setting on the
> location the report is run but since the report will be hosted in the states
> that's not going to help. All I need to do is format a text box as pounds!
> Anyone have any ideas?
> Thanks in advance.|||Thank You Kim! It worked perfectly.
"KimB" wrote:
> I noticed that if you click on the text box and look at the properties for it
> that there is a language property. Change it from default to English (United
> Kingdom) and that will change the currency symbol to the other symbol. I had
> something formatted as currency and tried this and it seemed to work.
> "littleren" wrote:
> > I have what is likely a simple question but I have had no luck in tracking
> > down an answer so far. I have a report that I've created in RS that is for an
> > English company and I want to display the output in pounds as opposed to $.
> > When I go to a given text-box and click the format/currency option all I get
> > is $. I realize that RS tries to default to the currency setting on the
> > location the report is run but since the report will be hosted in the states
> > that's not going to help. All I need to do is format a text box as pounds!
> >
> > Anyone have any ideas?
> >
> > Thanks in advance.|||I'm glad it helped!
"littleren" wrote:
> Thank You Kim! It worked perfectly.
>
> "KimB" wrote:
> > I noticed that if you click on the text box and look at the properties for it
> > that there is a language property. Change it from default to English (United
> > Kingdom) and that will change the currency symbol to the other symbol. I had
> > something formatted as currency and tried this and it seemed to work.
> >
> > "littleren" wrote:
> >
> > > I have what is likely a simple question but I have had no luck in tracking
> > > down an answer so far. I have a report that I've created in RS that is for an
> > > English company and I want to display the output in pounds as opposed to $.
> > > When I go to a given text-box and click the format/currency option all I get
> > > is $. I realize that RS tries to default to the currency setting on the
> > > location the report is run but since the report will be hosted in the states
> > > that's not going to help. All I need to do is format a text box as pounds!
> > >
> > > Anyone have any ideas?
> > >
> > > Thanks in advance.|||Hi,
Is it possible to apply your machine's Regional Settings to the currency
symbol Meaning, when I change my Windows' Regional Settings to English/UK,
currency will display with a pound symbol, if South Africa, with an R, or if
US. with a $?
Regards,
PM
"KimB" wrote:
> I'm glad it helped!
> "littleren" wrote:
> > Thank You Kim! It worked perfectly.
> >
> >
> > "KimB" wrote:
> >
> > > I noticed that if you click on the text box and look at the properties for it
> > > that there is a language property. Change it from default to English (United
> > > Kingdom) and that will change the currency symbol to the other symbol. I had
> > > something formatted as currency and tried this and it seemed to work.
> > >
> > > "littleren" wrote:
> > >
> > > > I have what is likely a simple question but I have had no luck in tracking
> > > > down an answer so far. I have a report that I've created in RS that is for an
> > > > English company and I want to display the output in pounds as opposed to $.
> > > > When I go to a given text-box and click the format/currency option all I get
> > > > is $. I realize that RS tries to default to the currency setting on the
> > > > location the report is run but since the report will be hosted in the states
> > > > that's not going to help. All I need to do is format a text box as pounds!
> > > >
> > > > Anyone have any ideas?
> > > >
> > > > Thanks in advance.

Currency conversion wizard - performance

We have created a cube in which we want to do many-to-many currency conversion - i.e. we have a measure group with amounts measured in 4 different currencies, and we want to be able to display these amounts in any of these 4 currencies. The measure group is linked to a company dimension which in turn is linked to a currency dimension via a referenced relationship (there are 3-4 more dimensions in the cube). Running the currency conversion wizard yields the expected results and we can show our measures in any available currency using the "Reporting Currency" dimension... However - it is SLOW!

We realize that the calculations in the MDX script generated by the wizard scopes on the leaves on the time dimension, which causes a performance hit, but the query runs for 4-5 minutes when choosing a currency other than "Local". Choosing "Local" the query runs for 2-3 seconds. The measure group in question contains approximately 1.4 millions fact rows and we have exchange rates for a 10 year period (approximately 365 x 4 x 10 = 14.600 fact rows for the exchange rate measure group).

We are using the September CTP (Standard Edition). Is there any way to improve the response times? Has bugs that affect this been fixed for RTM?

Thanks...

We are really having problems with this one... Has anyone else had similar experiences? |||Hi Michael,

Although my experience is limited, I can think of some things it might be worth trying...

First of all, using measure expressions instead of pure MDX Script calculations should give you better performance. See the following entries on my blog for more information:
http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!260.entry
http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!299.entry

Secondly (and this is only an educated guess), if you make sure that any aggregations you build are only at the granuarlity attribute of the Time dimension, then with a bit of luck the AS engine will be able to use them for your queries. You can do this by making sure that only the granuarity attribute has its AttributeHierarchyOptimizedState property set to FullyOptimized (the others should have it set to NotOptimized); if you then redesign your aggregations, hopefully you'll still get some built and see some improvement. If you don't get any aggregations built then you might have to design some manually by hacking the XMLA.

Thirdly, I wonder if the fact that the Currency dimension has a referenced relationship with the main fact table could be a factor here? Can you redesign the main fact table so that you have a Currency key as well as a Company key?

Fourthly, and I'm not sure that this will have much impact but it's worth trying, partitioning your Exchange Rate measure group by Month might be a good idea.

Let me know if any of this works..

Chris|||Thanks Chris... I will try your suggestions and report back my findings. My options with regard to your suggestions are limited, however, since the project is being built on a Standard Edition (thus no measure expressions and/or partitioning).

Currency conversion wizard - performance

We have created a cube in which we want to do many-to-many currency conversion - i.e. we have a measure group with amounts measured in 4 different currencies, and we want to be able to display these amounts in any of these 4 currencies. The measure group is linked to a company dimension which in turn is linked to a currency dimension via a referenced relationship (there are 3-4 more dimensions in the cube). Running the currency conversion wizard yields the expected results and we can show our measures in any available currency using the "Reporting Currency" dimension... However - it is SLOW!

We realize that the calculations in the MDX script generated by the wizard scopes on the leaves on the time dimension, which causes a performance hit, but the query runs for 4-5 minutes when choosing a currency other than "Local". Choosing "Local" the query runs for 2-3 seconds. The measure group in question contains approximately 1.4 millions fact rows and we have exchange rates for a 10 year period (approximately 365 x 4 x 10 = 14.600 fact rows for the exchange rate measure group).

We are using the September CTP (Standard Edition). Is there any way to improve the response times? Has bugs that affect this been fixed for RTM?

Thanks...

We are really having problems with this one... Has anyone else had similar experiences? |||Hi Michael,

Although my experience is limited, I can think of some things it might be worth trying...

First of all, using measure expressions instead of pure MDX Script calculations should give you better performance. See the following entries on my blog for more information:
http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!260.entry
http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!299.entry

Secondly (and this is only an educated guess), if you make sure that any aggregations you build are only at the granuarlity attribute of the Time dimension, then with a bit of luck the AS engine will be able to use them for your queries. You can do this by making sure that only the granuarity attribute has its AttributeHierarchyOptimizedState property set to FullyOptimized (the others should have it set to NotOptimized); if you then redesign your aggregations, hopefully you'll still get some built and see some improvement. If you don't get any aggregations built then you might have to design some manually by hacking the XMLA.

Thirdly, I wonder if the fact that the Currency dimension has a referenced relationship with the main fact table could be a factor here? Can you redesign the main fact table so that you have a Currency key as well as a Company key?

Fourthly, and I'm not sure that this will have much impact but it's worth trying, partitioning your Exchange Rate measure group by Month might be a good idea.

Let me know if any of this works..

Chris|||Thanks Chris... I will try your suggestions and report back my findings. My options with regard to your suggestions are limited, however, since the project is being built on a Standard Edition (thus no measure expressions and/or partitioning).

Cumalative %

Hi there, i am having some problems with a Crystal Report. I have created a report showing the duration of calls within a HEAT System. The calls are grouped into the below areas:

Under 4 Hours
Under 8 Hours
Under 2 Days
Under 3 Days

What is required is the following:

Duration of Call(Group) Number of Calls Cumalative %

Under 4 Hours 300 30% Calc(300/1000)
Under 8 Hours 300 60% Calc(600/1000)
Under 2 Days 100 70% Calc(700/1000)
Under 3 Days 300 100% Calc(1000/1000)

Total Calls 1000

I don't know how to achieve this in a Grouped report in Crystal - what would i use in a formula to do this?

Any help would be greatly received.

Regards

TimI think you want the PercentOfCount function, e.g.

PercentOfSum (call, inner, outer)

So if your report were grouped on, say,
1) Call centre
2) Duration

You would want: PercentOfSum(call, duration, centre)

Anyway, read the help on the PercentOf... functions.|||Sorry, I kept writing PercentOfSum instead of PercentOfCount

Cubes

Hi,
I have two Data Sources from two different servers.. Using that i have
created two cubes namely HostCube and Applications Cube..Now i need to
link the two cubes based on host name.. Host name is present in Host
cube and Site name is present in Application cube.. I need to link
these both and get the keywords, referal page, number of applications
running and number of visits.

Am not able to link two cubes and get the result.. I tried using
Virtual Cubes, but it just takes dimensions from both the cubes and
gives the result.. Can anyone help me out in this please.
Thanks in advance..

I would guess you are trying to use AS 2000 to solve the problem.

You should be way better off using AS 2005 that allows you to use data from 2 different datasources in a single cube.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi Edward,

Thanks alot for your reply..

Yes am using AS 2000.. Now i have copied table and all the tables are in one database..

Here I have a basic question... Can we use dimensions with conditions in Analysis service.. I have three dimensions and i need output for particular condition..
For example: I have Site Dimension, Host Dimension and Tracking Details Dimensions.. Here From Host Dimension I need to take Host Name and compare it with SiteName in Site Dimension and from this i need to take site type and siteid and comapre this site id with siteid in Tracking Details Dimension and get keywords and referer excluding for status=180..

Am totally lost.. Can you help me out in this.. Thanks in advance..

|||

Looks to me the problem you are trying to solve should be solved on the client side. Try installing eval version of ProClarity and see if cross drill ( or drill across) functionality gets you you what you need.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Cube Processing with IS packages

Hi,

Just have a quick question regarding the cube processing.

Currently, I've created IS package for the cube processing for better controls.

Before processing the cube, if I have to update the dimension first (since dimension updates everyday and it gives an error if I process the cube itself without dimension updates) then I have to create data flow 'dimension processiong' inside the AS processiong control task?

If so, I need to link the dimension table and connect columns into the dimension in cubes and what else I need to think about before creating this?

I appreciate if anyone can give a suggestion.

Thanks.

Hello! In the starschema in the realtional source you should have foreign keys relations from the dimension tables to the fact table.

In the data source view, in the BIDS cube project, you will need to relate these tables once again, if this is not done automatically.

Your deployed cube is only dependent on the data source when you process the cube, if it is a MOLAP cube.

After you have updated your relational tables(dimensions and fact table) you will only need to process the cube dimensions first and the cube(or measure group) after that.

HTH

Thomas Ivarsson

|||

HI,

Thanks for your reply.

I've already made the relationship between Facts and dimensions in DSV and also checked Dimension usage in each cubes which are molap.

When I process the cube in SSAS, In object list, I need to check dimension update and cube process and it does not give an error but if not, it gives an error ( like attribute can not be found since the dimensions are not updated). My question is that in SSIS I have to check dimension when I run a package but is there anyway to process automatically instead of checking dimension whenever I process the cube?

I appreciate if you can give me any comments.

Thanks.

|||

Hello! I assume that you have also included the processing of dimensions in your SSIS package and before the cube?

In the process cube form in BIDS you have a buttom att the bottom(advanced?) and there you have a setting like process affected objects that you can activate.

HTH

Thomas Ivarsson

Cube Refresh

Hi,
I hope someone can clarify this to me.
I have DW Database which is the for a OLAP Database I
created in AS. I have created cube with the necessary fact
tables and the dimensions. my question is whenever the
Data in the DW is updated, do u need to manually refresh
the cubes so the cube would have data? is this automatic
or else can this be automated?
Thanks
Regards
ImranYes and yes, you need to "re-process" the cube when you add data to the
underlying tables. You can automate this by using the process cube task in
DTS.
HTH
Ray Higdon MCSE, MCDBA, CCNA
--
"Imran" <anonymous@.discussions.microsoft.com> wrote in message
news:b70e01c40d94$5e25e840$a001280a@.phx.gbl...
> Hi,
> I hope someone can clarify this to me.
> I have DW Database which is the for a OLAP Database I
> created in AS. I have created cube with the necessary fact
> tables and the dimensions. my question is whenever the
> Data in the DW is updated, do u need to manually refresh
> the cubes so the cube would have data? is this automatic
> or else can this be automated?
> Thanks
> Regards
> Imran|||Our team uses DTS to populate a special datawarehouse db and then trigger an
alysis server to reprocess it's cubes. We have this automated to run every
15 minutes. In our DTS package, we just needed to drage the SQL Server OLED
B provider, an execute SQL
task that kicks off our datawarehouse data import stored procedures, and the
n an analysis services processing task. The processing task would then just
be set up with a work flow to execute after the data import stored procedur
e. This has worked splendi
dly for us.

Cube Processing with IS packages

Hi,

Just have a quick question regarding the cube processing.

Currently, I've created IS package for the cube processing for better controls.

Before processing the cube, if I have to update the dimension first (since dimension updates everyday and it gives an error if I process the cube itself without dimension updates) then I have to create data flow 'dimension processiong' inside the AS processiong control task?

If so, I need to link the dimension table and connect columns into the dimension in cubes and what else I need to think about before creating this?

I appreciate if anyone can give a suggestion.

Thanks.

Hello! In the starschema in the realtional source you should have foreign keys relations from the dimension tables to the fact table.

In the data source view, in the BIDS cube project, you will need to relate these tables once again, if this is not done automatically.

Your deployed cube is only dependent on the data source when you process the cube, if it is a MOLAP cube.

After you have updated your relational tables(dimensions and fact table) you will only need to process the cube dimensions first and the cube(or measure group) after that.

HTH

Thomas Ivarsson

|||

HI,

Thanks for your reply.

I've already made the relationship between Facts and dimensions in DSV and also checked Dimension usage in each cubes which are molap.

When I process the cube in SSAS, In object list, I need to check dimension update and cube process and it does not give an error but if not, it gives an error ( like attribute can not be found since the dimensions are not updated). My question is that in SSIS I have to check dimension when I run a package but is there anyway to process automatically instead of checking dimension whenever I process the cube?

I appreciate if you can give me any comments.

Thanks.

|||

Hello! I assume that you have also included the processing of dimensions in your SSIS package and before the cube?

In the process cube form in BIDS you have a buttom att the bottom(advanced?) and there you have a setting like process affected objects that you can activate.

HTH

Thomas Ivarsson

Cube Processing with IS packages

Hi,

Just have a quick question regarding the cube processing.

Currently, I've created IS package for the cube processing for better controls.

Before processing the cube, if I have to update the dimension first (since dimension updates everyday and it gives an error if I process the cube itself without dimension updates) then I have to create data flow 'dimension processiong' inside the AS processiong control task?

If so, I need to link the dimension table and connect columns into the dimension in cubes and what else I need to think about before creating this?

I appreciate if anyone can give a suggestion.

Thanks.

Hello! In the starschema in the realtional source you should have foreign keys relations from the dimension tables to the fact table.

In the data source view, in the BIDS cube project, you will need to relate these tables once again, if this is not done automatically.

Your deployed cube is only dependent on the data source when you process the cube, if it is a MOLAP cube.

After you have updated your relational tables(dimensions and fact table) you will only need to process the cube dimensions first and the cube(or measure group) after that.

HTH

Thomas Ivarsson

|||

HI,

Thanks for your reply.

I've already made the relationship between Facts and dimensions in DSV and also checked Dimension usage in each cubes which are molap.

When I process the cube in SSAS, In object list, I need to check dimension update and cube process and it does not give an error but if not, it gives an error ( like attribute can not be found since the dimensions are not updated). My question is that in SSIS I have to check dimension when I run a package but is there anyway to process automatically instead of checking dimension whenever I process the cube?

I appreciate if you can give me any comments.

Thanks.

|||

Hello! I assume that you have also included the processing of dimensions in your SSIS package and before the cube?

In the process cube form in BIDS you have a buttom att the bottom(advanced?) and there you have a setting like process affected objects that you can activate.

HTH

Thomas Ivarsson

Cube processing error: "The end of the input was reached"

I created a cube with AMO and examined it in BI Studio by importing. Everything looks fine, but I get the following error when processing the cube:

"Parser: The end of the input was reached".

The processing of dimensions appears to be successful. SSAS processes the cube immediately after finishing dimensions. That's where the error occurs. What is odd is that the process progress dialog box shows everything was completely successfully. I am quite sure that the processing of the cube was not completed because I cannot browse it and I know it takes much more than a duratio of 0 seconds to process the cube.

Could anyone offer some hint?

Thanks,

hz

It looks like the script you have for your cube contains syntax error. At least it should have "CALCULATE;" statement.
|||

I did not write the script. I wrote code in C# using AMO to create the cube. I do not know how to look at the script, but scripted the cube to a query editor window and did not see any CALCULATE statement. I also scripted a cube that is working perfectly and did not see CALCULATE in its script either.

At this point of time, I am temporarily giving up using AMO to create cubes and resorting to other means.

hz