Large Report Dilemma...Need Suggestions

We have a database that has GUIDs that we need to use for looking up records
in a database and displaying a report. Problem is that the user can select
many records to be displayed in the report and we cannot use URL access as
the querystring will be too long. We tried web services, but the maximum
length for variable declaration is 4000 for a varchar variable in a stored
procedure and this can be exceeded also. So question is how can I get around
this and run very large reports? The GUIDs have to stay...I've already lost
that battle. How to get around this and any alternatives will be greatly
appreciated.Just to understand your problem.
You have GUID as one of the column, you use multiselect from RS to select
multiple GUIDs, once click on view report it should display some report. is
this what you are looking for ?
If yes, then 4096 is the limitation for multi-select option so you cannot
select more than that.
I have used a small query to do this it worked perfectly ok ofcourse through
RM. But you said URL, why you require URL when you can run from RM,
Is your requirement is through URL ?
Ofcourse you can use web services, using arrays and pass the parameters
loaded with GUID to the query / proc etc..
one more thing is if you are using SQL SERVER 2005 there is a new varchar
called
nvarchar(max) it can go upto 2 GB.
So just let know is my understanding is correct ? so that i can suggest
something.
Amarnath
"Wannabe" wrote:
> We have a database that has GUIDs that we need to use for looking up records
> in a database and displaying a report. Problem is that the user can select
> many records to be displayed in the report and we cannot use URL access as
> the querystring will be too long. We tried web services, but the maximum
> length for variable declaration is 4000 for a varchar variable in a stored
> procedure and this can be exceeded also. So question is how can I get around
> this and run very large reports? The GUIDs have to stay...I've already lost
> that battle. How to get around this and any alternatives will be greatly
> appreciated.|||Thanks for the reply...
The user will not be using RM to run the report. They will use our web
application, which has a datagrid, where they will select however many
records they want a report on (could be hundreds.) Then we will pass the GUID
of each record selected to RS for displaying. Also, we are currently using
SQL 2000, but do have plans to upgrade in about three months.
When I tried web services, in SQL 2000, the maximum value for a varchar is
4000 characters, so my parameter list got truncated when I built the where
clause using an IN statement.
"Amarnath" wrote:
> Just to understand your problem.
> You have GUID as one of the column, you use multiselect from RS to select
> multiple GUIDs, once click on view report it should display some report. is
> this what you are looking for ?
> If yes, then 4096 is the limitation for multi-select option so you cannot
> select more than that.
> I have used a small query to do this it worked perfectly ok ofcourse through
> RM. But you said URL, why you require URL when you can run from RM,
> Is your requirement is through URL ?
> Ofcourse you can use web services, using arrays and pass the parameters
> loaded with GUID to the query / proc etc..
> one more thing is if you are using SQL SERVER 2005 there is a new varchar
> called
> nvarchar(max) it can go upto 2 GB.
> So just let know is my understanding is correct ? so that i can suggest
> something.
> Amarnath
>
> "Wannabe" wrote:
> > We have a database that has GUIDs that we need to use for looking up records
> > in a database and displaying a report. Problem is that the user can select
> > many records to be displayed in the report and we cannot use URL access as
> > the querystring will be too long. We tried web services, but the maximum
> > length for variable declaration is 4000 for a varchar variable in a stored
> > procedure and this can be exceeded also. So question is how can I get around
> > this and run very large reports? The GUIDs have to stay...I've already lost
> > that battle. How to get around this and any alternatives will be greatly
> > appreciated.|||If you are writing custom code using asp.net then it will be a tricky way,
what you can do is take the input to a temp table and in your query use
subquery to execte it.
some thing like select ... from ... where xyz in (select guid from #temp)
Amarnath
"Wannabe" wrote:
> Thanks for the reply...
> The user will not be using RM to run the report. They will use our web
> application, which has a datagrid, where they will select however many
> records they want a report on (could be hundreds.) Then we will pass the GUID
> of each record selected to RS for displaying. Also, we are currently using
> SQL 2000, but do have plans to upgrade in about three months.
> When I tried web services, in SQL 2000, the maximum value for a varchar is
> 4000 characters, so my parameter list got truncated when I built the where
> clause using an IN statement.
> "Amarnath" wrote:
> > Just to understand your problem.
> > You have GUID as one of the column, you use multiselect from RS to select
> > multiple GUIDs, once click on view report it should display some report. is
> > this what you are looking for ?
> >
> > If yes, then 4096 is the limitation for multi-select option so you cannot
> > select more than that.
> > I have used a small query to do this it worked perfectly ok ofcourse through
> > RM. But you said URL, why you require URL when you can run from RM,
> > Is your requirement is through URL ?
> >
> > Ofcourse you can use web services, using arrays and pass the parameters
> > loaded with GUID to the query / proc etc..
> >
> > one more thing is if you are using SQL SERVER 2005 there is a new varchar
> > called
> > nvarchar(max) it can go upto 2 GB.
> >
> > So just let know is my understanding is correct ? so that i can suggest
> > something.
> >
> > Amarnath
> >
> >
> > "Wannabe" wrote:
> >
> > > We have a database that has GUIDs that we need to use for looking up records
> > > in a database and displaying a report. Problem is that the user can select
> > > many records to be displayed in the report and we cannot use URL access as
> > > the querystring will be too long. We tried web services, but the maximum
> > > length for variable declaration is 4000 for a varchar variable in a stored
> > > procedure and this can be exceeded also. So question is how can I get around
> > > this and run very large reports? The GUIDs have to stay...I've already lost
> > > that battle. How to get around this and any alternatives will be greatly
> > > appreciated.|||Thanks...I'll try that. I believe that will help.
"Amarnath" wrote:
> If you are writing custom code using asp.net then it will be a tricky way,
> what you can do is take the input to a temp table and in your query use
> subquery to execte it.
> some thing like select ... from ... where xyz in (select guid from #temp)
> Amarnath
>
> "Wannabe" wrote:
> > Thanks for the reply...
> >
> > The user will not be using RM to run the report. They will use our web
> > application, which has a datagrid, where they will select however many
> > records they want a report on (could be hundreds.) Then we will pass the GUID
> > of each record selected to RS for displaying. Also, we are currently using
> > SQL 2000, but do have plans to upgrade in about three months.
> >
> > When I tried web services, in SQL 2000, the maximum value for a varchar is
> > 4000 characters, so my parameter list got truncated when I built the where
> > clause using an IN statement.
> >
> > "Amarnath" wrote:
> >
> > > Just to understand your problem.
> > > You have GUID as one of the column, you use multiselect from RS to select
> > > multiple GUIDs, once click on view report it should display some report. is
> > > this what you are looking for ?
> > >
> > > If yes, then 4096 is the limitation for multi-select option so you cannot
> > > select more than that.
> > > I have used a small query to do this it worked perfectly ok ofcourse through
> > > RM. But you said URL, why you require URL when you can run from RM,
> > > Is your requirement is through URL ?
> > >
> > > Ofcourse you can use web services, using arrays and pass the parameters
> > > loaded with GUID to the query / proc etc..
> > >
> > > one more thing is if you are using SQL SERVER 2005 there is a new varchar
> > > called
> > > nvarchar(max) it can go upto 2 GB.
> > >
> > > So just let know is my understanding is correct ? so that i can suggest
> > > something.
> > >
> > > Amarnath
> > >
> > >
> > > "Wannabe" wrote:
> > >
> > > > We have a database that has GUIDs that we need to use for looking up records
> > > > in a database and displaying a report. Problem is that the user can select
> > > > many records to be displayed in the report and we cannot use URL access as
> > > > the querystring will be too long. We tried web services, but the maximum
> > > > length for variable declaration is 4000 for a varchar variable in a stored
> > > > procedure and this can be exceeded also. So question is how can I get around
> > > > this and run very large reports? The GUIDs have to stay...I've already lost
> > > > that battle. How to get around this and any alternatives will be greatly
> > > > appreciated.