Cursor Help Please

Hi all!

I just need some help with cursors, a topic I don't profess to be an expert in.

I've got two tables with a 1-many relationship between them. Let's say they're "tblCustomers" and "tblOrders".

tblCustomers data:

CustomerID Name
1 Fred
2 Charlie
3 Lucy

tblOrders data:

OrderID CustomerId Qty
1 1 10
2 1 5
3 1 20
4 2 8
5 3 20
6 3 6

I need to return a result set that puts all the "many" records into a single row, like:

Name Qty

Fred 10, 15, 20
Charlie 8
Lucy 20, 6

THANKS IN ADVANCE!!!!!!!!!!Create this function

CREATE Function ListOfQuantity (@.ID Varchar(100))
RETURNS Varchar(2000)
As
Begin
Declare @.List Varchar(2000)
set @.List=''
Select @.List = @.List+RTrim(Qte) + ',' From Orders Where CustomerID =RTrim(@.ID)
Return(Left(@.List,Len(@.List)-1))
End|||then you can do

select name,dbo.ListOfQuantity(id)
from customer|||What is magical is the

Select @.List = @.List + Col1 From Tbl

For a result that should be
1
2
3

it returns
123|||I'll try it Karolyn, THANKS!|||Many thanks!

Cursor Declaration

Hello All!
I am trying to declare a cursor and I keep on gettin the following error
when I try and debug: Incorrect syntax near the keyword 'declare'. I am
stumped and becuase I have even tried using the numerous exmaples
listed on the internet and modified the select clause.
PLEASE HELP!
Here is my stored PRoc code:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE FXSecondPart
AS
BEGIN
--SET NOCOUNT ON;
declare @.1m as float
declare @.2m as float
declare @.spot as float
declare @.@.ValDt as datetime
declare @.@.PayAmt as money
declare @.@.RcvAmt as money
declare @.BS as char(1)
declare @.CCY as char(3)
declare @.Sell
declare pubcrsr cursor
for select ISIN
from AllTrades
FOR READ ONLY
if i parse this I get a error!READ_ONLY not READ ONLY
declare pubcrsr cursor READ_ONLY
for select ISIN
from AllTrades
and also
declare @.Sell needs a type maybe int?
declare @.Sell int
http://sqlservercode.blogspot.com/|||umm.. what type exactly is the @.Sell variable?
:)
Peter
> declare @.CCY as char(3)
> declare @.Sell
> declare pubcrsr cursor
> for select ISIN
> from AllTrades
> FOR READ ONLY
>
> if i parse this I get a error!
>|||based on what you've posted
1)there should be a END associated with the BEGIN
2) add a datatype to declare @.Sell
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Chris Allison" <ChrisAllison@.discussions.microsoft.com> wrote in message
news:63674EDA-96BF-41B0-A30C-19F6D254A5C1@.microsoft.com...
> Hello All!
> I am trying to declare a cursor and I keep on gettin the following error
> when I try and debug: Incorrect syntax near the keyword 'declare'. I am
> stumped and becuase I have even tried using the numerous exmaples
> listed on the internet and modified the select clause.
> PLEASE HELP!
> Here is my stored PRoc code:
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE PROCEDURE FXSecondPart
> AS
> BEGIN
> --SET NOCOUNT ON;
> declare @.1m as float
> declare @.2m as float
> declare @.spot as float
> declare @.@.ValDt as datetime
> declare @.@.PayAmt as money
> declare @.@.RcvAmt as money
> declare @.BS as char(1)
> declare @.CCY as char(3)
> declare @.Sell
> declare pubcrsr cursor
> for select ISIN
> from AllTrades
> FOR READ ONLY
>
> if i parse this I get a error!
>|||Hi SQL
That doesnt work! If I parse this I get a incorrect syntax near declare, the
same error!!
It cant be a permissions problem as I have SA privilidges. coulld it be
another system setting? or perhaps im declaring it in the wrong 'section' of
my stored procedure. Im using SQL Express 2005
declare pubcrsr cursor READ_ONLY
for select ISIN
from AllTrades
OPEN pubcrsr
WHILE @.@.Fetch_Status <>-1
Begin
FETCH NEXT from pubcrsr
INTO @.@.ValDt, @.@.PayAmt, @.@.RcvAmt
--SELECT @.spot = (SELECT LastTrade FROM HistoricalFX WHERE (CCY = @.CCY) AND
--(Date = @.p1))
--SELECT @.1m = (SELECT LastTrade FROM HistoricalFXFwrd WHERE (CCY = @.CCY)
AND
--(Period = '1M') AND (Date = @.p1) )
--SELECT @.2m = (SELECT LastTrade FROM HistoricalFXFwrd WHERE (CCY = @.CCY)
AND
--(Period = '2M') AND (Date = @.p1) )
-- pass @.spot, @.1m, @.2m, @.valdt, to calculating procedure
-- with the returned value calculate the PnL and put this in a 'counter'
FETCH NEXT from pubcrsr
End
Close pubcrsr
Deallocate pubcrsr
"SQL" wrote:

> READ_ONLY not READ ONLY
> declare pubcrsr cursor READ_ONLY
> for select ISIN
> from AllTrades
>
> and also
> declare @.Sell needs a type maybe int?
> declare @.Sell int
>
> http://sqlservercode.blogspot.com/
>|||THATS IT!!! I know it was a simple problem Thanks Guys it was the @.sell
variable which ive deleted!!
Great Stuff!
"SQL" wrote:

> READ_ONLY not READ ONLY
> declare pubcrsr cursor READ_ONLY
> for select ISIN
> from AllTrades
>
> and also
> declare @.Sell needs a type maybe int?
> declare @.Sell int
>
> http://sqlservercode.blogspot.com/
>

Cube back up

dear all
i have a list of 7 cubes on the production server and i intend to bring the back up of 1 cube to my local system(w/o data,just the structure).I took the xmla file by scripting that respective cube and brought that xmla file to my local system. tell me wat i need to do to restore this xmla file to get the structure of my cube

Cheers
sid

If you just need the structure I would suggest scripting the whole database and then running the script on your local server and deleting any cubes that you don't need. This is because you need to have a database to script the cube into and you need to bring the dsv, data sources and database dimensions along so that the cube will work.

I've been playing with a Powershell script to generate the xmla script for a single cube and all the dependant objects, but it's not quite finished. For the time being I would say to go with scripting the whole database and deleting what you don't need later.

CrystalReportViewer check for guest login

Hi all

I have added crystalreportviewer using ASP .net (VB .net)

I was able to view the report when I run the web page on
my server. Everything seems fine on the server.

However, if I access the crystal report on a client machine,
it will prompt for guest login and password to the server machine.

I am using anonymous access for my IIS setting.
I can access the rest of the .net webpage without any problem.
However, when I enter the webpage with the crystalreportviewer,
it will prompt for guest password.

Even after I have keyed in the guest password, mutiple prompts appear again. The server event log show this error -
The server was unable to logon the Windows NT account 'IUSR_Machinename' due to the following error: Logon failure: unknown user name or bad password. The data is the error code.

After canceling the prompt a few times, it will show the page with
the report and its data but the sort tree and reportviewer
functions bar on top will show all weird marking. :(

For testing, I have tried adding just a crystalreportviewer object
on a empty webpage. I did not link the crystalreportviewer to
any .rpt file and the same prompt appear. Its seems that the
crystalreportviewer object need to be authenticated on the server
when accessing from the client machine.

I am using MS studio 2003 and IIS 5.1 on XP professional server.

Please help advice.

Thanks.
DerekHi all,

I have found the solution myself.

Give access rights to this folder
C:\Program Files\Microsoft Visual Studio .NET 2003\Crystal Reports

to the IUSR_<MACHINENAME> anonymous account.

This is because all the .net dll for crystal report is installed in
this folder. Hence the correct access should be provided to this
folder.

Regards,
Derek