Line 1: Incorrect syntax near getlistofspclass.

what am i doing wrong.. :(

CREATE PROCEDURE getlistofspclass
(
@.date datetime,
@.s varchar(8000) output
)

AS

select @.s = coalesce(@.s + ' & ', '') + '"' + convert(varchar(10),dbo.calader.timestart)+ ' ' + dbo.classT.classname + '<p>'
FROM dbo.calader INNER JOIN dbo.classT ON dbo.calader.classid = dbo.classT.classcode
WHERE dbo.calader.caledardatedates = @.date
exec( @.s)
GO

The procedure's parameters should not be enclosed in parentheses. Remove the parentheses that surround the parameters and you should have better luck.|||

my god!! i spent 4 hours on that and $60 on a Que sql server programming book. :(

but now it works. i can hang myself now

|||

hello can you help me again. ...my asp.net code is not working.. it will not send the date to the pramater.

can you make a sameple code in asp.net vb of how you will send a date to that stored procuder and how you would get the returned value

please. thank you verymuch

|||

All I have is a C# version I made the other day, sorry. But it's close enough to VB you should be able to figure out how to port it.

1public int InsertNewThread(Thread ThreadToInsert,out int InternalID)2{3using(SqlConnection sqlConnection =new SqlConnection(GetConnectionString()))4{5SqlCommand sqlCommand =new SqlCommand("Thread_Insert", sqlConnection);6sqlCommand.CommandType = CommandType.StoredProcedure;7sqlCommand.Parameters.Add("@.threadSummary", SqlDbType.NVarChar, 75).Value = ThreadToInsert.ThreadSummary;8sqlCommand.Parameters.Add("@.boardID", SqlDbType.Int).Value = ThreadToInsert.BoardID;9sqlCommand.Parameters.Add("@.internalID", SqlDbType.Int).Direction = ParameterDirection.Output;10sqlConnection.Open();11int RowsEffected = sqlCommand.ExecuteNonQuery();12sqlConnection.Close();13InternalID = (int) sqlCommand.Parameters["@.internalID"].Value;14return RowsEffected;15}16}

12/****** Object: Stored Procedure dbo.Thread_Insert Script Date: 7/28/2006 7:24:19 PM ******/3CREATE PROCEDURE Thread_Insert4@.threadSummaryNVARCHAR( 75 ),5@.internalIDINT OUTPUT6AS7INSERT INTO8 threads( threadSummary )910VALUES11 ( @.threadSummary )1213SELECT14@.internalID = SCOPE_IDENTITY ( )1516GO

|||

thanks again. you know the speed of getting the return value was just the same as putting the sql statement on the webpage itself...people say storedprocedures are faster..oh well

thanks everything worked

|||

Stored procedures are faster. If you coded one page without stored procedures, and another with stored procedures, and timed them with your watch, then you won't notice much.

Take a web server (or multiple web servers) using complex queries and time the difference when they are getting hit a few hundred requests per second, and you will notice a difference, especially in older versions of SQL Server. The newer ones have added a few little things that reduce the difference dramatically in most cases (Cached query plans, even for dynamic queries). They basically turn even dynamic queries into a kind of "temporary" stored procedure, getting most of the sp's benefits, a very nice addition to SQL Server.

Limits in Reporting Services

I read a post from one of the MVPs that stated "If you mean that the result
set has 90,000 records or 180,000 records then you have the wrong product. RS
is not designed to generate reports that are 1500+ pages."
Is there documentation available about the limits of RS (i.e. max number of
records, max number of pages, etc.)? I realize that it depends on the amount
of memory available, but I'd like to get the limits for the 2 GB process
maximum (numbers for 3GB with the /3GB option would be nice also).
Additionally, how is this affected by multiple users? Is it a sum total of
all requests or are the requests queued?
I looked in the docs first for this info and wasn't able to find it.
Thanks for your help."brett" wrote:
> I read a post from one of the MVPs that stated "If you mean that the result
> set has 90,000 records or 180,000 records then you have the wrong product. RS
> is not designed to generate reports that are 1500+ pages."
> Is there documentation available about the limits of RS (i.e. max number of
> records, max number of pages, etc.)? I realize that it depends on the amount
> of memory available, but I'd like to get the limits for the 2 GB process
> maximum (numbers for 3GB with the /3GB option would be nice also).
> Additionally, how is this affected by multiple users? Is it a sum total of
> all requests or are the requests queued?
> I looked in the docs first for this info and wasn't able to find it.
> Thanks for your help.
Hi Brett,
Are you outputting to pdf files. I have recently experienced a similar
problem where the reports timeout and don't work if there are over 4000
pages. We tried throttling the memory allocated to SQL on the box as well as
changing memory limits in the config file within Reporting services but that
has not seemed to do the trick.|||I am not aware of any document like that. PDF and Excel rendering take a lot
of processing power. Rendering to CSV is much much faster (I had a case
where Excel took 5 minutes and the same data rendered as CSV in about 15
seconds). That is a possible work around for you for large datasets.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"brett" <brett@.discussions.microsoft.com> wrote in message
news:5318C3D8-3051-45C4-AC1B-2D206C7AC2E8@.microsoft.com...
> I read a post from one of the MVPs that stated "If you mean that the
result
> set has 90,000 records or 180,000 records then you have the wrong product.
RS
> is not designed to generate reports that are 1500+ pages."
> Is there documentation available about the limits of RS (i.e. max number
of
> records, max number of pages, etc.)? I realize that it depends on the
amount
> of memory available, but I'd like to get the limits for the 2 GB process
> maximum (numbers for 3GB with the /3GB option would be nice also).
> Additionally, how is this affected by multiple users? Is it a sum total
of
> all requests or are the requests queued?
> I looked in the docs first for this info and wasn't able to find it.
> Thanks for your help.|||Rob,
Our application renders to the gui first, so that's where we are
encountering our problem. It's clearly a memory issue. I'm able to render
over 7K+ pages in the GUI, but then we get the OutOfMemory exception when we
go above that.
When I up the process memory limit to 3GB, I'm able to render more pages so
it's clearly a memory issue as the exception states.
I'm trying to get documentation so we can understand what the limits are so
we can tell our users what to expect. Telling them "don't run big reports"
isn't acceptable:)
"rob" wrote:
>
> "brett" wrote:
> > I read a post from one of the MVPs that stated "If you mean that the result
> > set has 90,000 records or 180,000 records then you have the wrong product. RS
> > is not designed to generate reports that are 1500+ pages."
> >
> > Is there documentation available about the limits of RS (i.e. max number of
> > records, max number of pages, etc.)? I realize that it depends on the amount
> > of memory available, but I'd like to get the limits for the 2 GB process
> > maximum (numbers for 3GB with the /3GB option would be nice also).
> >
> > Additionally, how is this affected by multiple users? Is it a sum total of
> > all requests or are the requests queued?
> >
> > I looked in the docs first for this info and wasn't able to find it.
> >
> > Thanks for your help.
>
> Hi Brett,
> Are you outputting to pdf files. I have recently experienced a similar
> problem where the reports timeout and don't work if there are over 4000
> pages. We tried throttling the memory allocated to SQL on the box as well as
> changing memory limits in the config file within Reporting services but that
> has not seemed to do the trick.|||Thanks for the feedback Bruce.
What we are wrestling with is there is a max amount of data the RS can
render and it's tied to memory. When our customers ask how big of a machine
they need to buy, we need to be able to give them some guidance based on
their data sets. We also need to be able to tell them (or prevent them in
code) what size of reports they can run.
If it's not documented, we'll have to test different memory configurations
and datasets...not a great use of resources.
"Bruce L-C [MVP]" wrote:
> I am not aware of any document like that. PDF and Excel rendering take a lot
> of processing power. Rendering to CSV is much much faster (I had a case
> where Excel took 5 minutes and the same data rendered as CSV in about 15
> seconds). That is a possible work around for you for large datasets.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "brett" <brett@.discussions.microsoft.com> wrote in message
> news:5318C3D8-3051-45C4-AC1B-2D206C7AC2E8@.microsoft.com...
> > I read a post from one of the MVPs that stated "If you mean that the
> result
> > set has 90,000 records or 180,000 records then you have the wrong product.
> RS
> > is not designed to generate reports that are 1500+ pages."
> >
> > Is there documentation available about the limits of RS (i.e. max number
> of
> > records, max number of pages, etc.)? I realize that it depends on the
> amount
> > of memory available, but I'd like to get the limits for the 2 GB process
> > maximum (numbers for 3GB with the /3GB option would be nice also).
> >
> > Additionally, how is this affected by multiple users? Is it a sum total
> of
> > all requests or are the requests queued?
> >
> > I looked in the docs first for this info and wasn't able to find it.
> >
> > Thanks for your help.
>
>|||One potential for large Excel data is to render it in CSV and specify ANSII
format (rather than the default of Unicode which Report Manager uses). Excel
doesn't handle Unicode CSV. If rendered in ANSII CSV then Excel will
appropriate put it in the correct columns. Might be a good solution for
people that want a data dump to perform their own analysis on.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"brett" <brett@.discussions.microsoft.com> wrote in message
news:290F0D39-1C39-436E-9597-75208873EEA3@.microsoft.com...
> Thanks for the feedback Bruce.
> What we are wrestling with is there is a max amount of data the RS can
> render and it's tied to memory. When our customers ask how big of a
machine
> they need to buy, we need to be able to give them some guidance based on
> their data sets. We also need to be able to tell them (or prevent them in
> code) what size of reports they can run.
> If it's not documented, we'll have to test different memory configurations
> and datasets...not a great use of resources.
> "Bruce L-C [MVP]" wrote:
> > I am not aware of any document like that. PDF and Excel rendering take a
lot
> > of processing power. Rendering to CSV is much much faster (I had a case
> > where Excel took 5 minutes and the same data rendered as CSV in about 15
> > seconds). That is a possible work around for you for large datasets.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "brett" <brett@.discussions.microsoft.com> wrote in message
> > news:5318C3D8-3051-45C4-AC1B-2D206C7AC2E8@.microsoft.com...
> > > I read a post from one of the MVPs that stated "If you mean that the
> > result
> > > set has 90,000 records or 180,000 records then you have the wrong
product.
> > RS
> > > is not designed to generate reports that are 1500+ pages."
> > >
> > > Is there documentation available about the limits of RS (i.e. max
number
> > of
> > > records, max number of pages, etc.)? I realize that it depends on the
> > amount
> > > of memory available, but I'd like to get the limits for the 2 GB
process
> > > maximum (numbers for 3GB with the /3GB option would be nice also).
> > >
> > > Additionally, how is this affected by multiple users? Is it a sum
total
> > of
> > > all requests or are the requests queued?
> > >
> > > I looked in the docs first for this info and wasn't able to find it.
> > >
> > > Thanks for your help.
> >
> >
> >|||We've actually had certain reports bring a server down because it was
taking so much memory and CPU time. Interestingly, the PDF format of
the same report didn't take very long nor did it bring the machine down.
Bruce L-C [MVP] wrote:
> I am not aware of any document like that. PDF and Excel rendering take a lot
> of processing power. Rendering to CSV is much much faster (I had a case
> where Excel took 5 minutes and the same data rendered as CSV in about 15
> seconds). That is a possible work around for you for large datasets.
>|||Our customer requirement is for the reports to be rendered in PDF format so
the CSV is not an option. There is also a logo graphic that is on each page
that adds to the problem as well.
"Bruce L-C [MVP]" wrote:
> One potential for large Excel data is to render it in CSV and specify ANSII
> format (rather than the default of Unicode which Report Manager uses). Excel
> doesn't handle Unicode CSV. If rendered in ANSII CSV then Excel will
> appropriate put it in the correct columns. Might be a good solution for
> people that want a data dump to perform their own analysis on.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "brett" <brett@.discussions.microsoft.com> wrote in message
> news:290F0D39-1C39-436E-9597-75208873EEA3@.microsoft.com...
> > Thanks for the feedback Bruce.
> >
> > What we are wrestling with is there is a max amount of data the RS can
> > render and it's tied to memory. When our customers ask how big of a
> machine
> > they need to buy, we need to be able to give them some guidance based on
> > their data sets. We also need to be able to tell them (or prevent them in
> > code) what size of reports they can run.
> >
> > If it's not documented, we'll have to test different memory configurations
> > and datasets...not a great use of resources.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > I am not aware of any document like that. PDF and Excel rendering take a
> lot
> > > of processing power. Rendering to CSV is much much faster (I had a case
> > > where Excel took 5 minutes and the same data rendered as CSV in about 15
> > > seconds). That is a possible work around for you for large datasets.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "brett" <brett@.discussions.microsoft.com> wrote in message
> > > news:5318C3D8-3051-45C4-AC1B-2D206C7AC2E8@.microsoft.com...
> > > > I read a post from one of the MVPs that stated "If you mean that the
> > > result
> > > > set has 90,000 records or 180,000 records then you have the wrong
> product.
> > > RS
> > > > is not designed to generate reports that are 1500+ pages."
> > > >
> > > > Is there documentation available about the limits of RS (i.e. max
> number
> > > of
> > > > records, max number of pages, etc.)? I realize that it depends on the
> > > amount
> > > > of memory available, but I'd like to get the limits for the 2 GB
> process
> > > > maximum (numbers for 3GB with the /3GB option would be nice also).
> > > >
> > > > Additionally, how is this affected by multiple users? Is it a sum
> total
> > > of
> > > > all requests or are the requests queued?
> > > >
> > > > I looked in the docs first for this info and wasn't able to find it.
> > > >
> > > > Thanks for your help.
> > >
> > >
> > >
>
>|||I agree with the MVP - a 4000+ page report is really just a fancy
data-feed.
I often have customers request such reports, and my typical response is
that Sql Reporting Services is not the right tool for the job. Just
because you "Can" do something doesnt mean that you should.
Also, assuming you do get this working, if you legitimately have a
4000+ page PDF report, then I have sympathy for the end-user who tries
to open it in Adobe Acrobat. I have seen Adobe Acrobat opening
sub-500 page PDF documents bring a high-end PC to its knees, so I am
fearful of what a 4000 page doc would be like.
There are plenty of alternatives...split the report into 40 PDF
documents @. 100 pages, extract the data to file, then use a 3rd party
PDF renderer to create the file, convince the users to accept an
ETL-generated CSV file, etc.
TIP: If you are the admin of a Sql Reporting Services instance, set a
report development standard for report developers to limit the number
of rows in a DataSet and the number of Pages rendered.
Typically my max threshold for data in a report DataSet is 5000 rows @.
5 columns (or equivilent). Also, my guideline for report-length is to
restrict report output to < 100 pages.
I hope this helps.
Lance Hunt
http://weblogs.asp.net/lhunt/|||"There are more reporting requirements in heaven and on earth, Lance,
than are dreamt of in your reporting architecture."|||We are working on supporting larger report sizes but yes, if you want to do
a pure data dump, Reporting Services is not the best tool for the job.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lance" <lancehunt@.gmail.com> wrote in message
news:1116022906.085455.213970@.f14g2000cwb.googlegroups.com...
>I agree with the MVP - a 4000+ page report is really just a fancy
> data-feed.
> I often have customers request such reports, and my typical response is
> that Sql Reporting Services is not the right tool for the job. Just
> because you "Can" do something doesnt mean that you should.
> Also, assuming you do get this working, if you legitimately have a
> 4000+ page PDF report, then I have sympathy for the end-user who tries
> to open it in Adobe Acrobat. I have seen Adobe Acrobat opening
> sub-500 page PDF documents bring a high-end PC to its knees, so I am
> fearful of what a 4000 page doc would be like.
> There are plenty of alternatives...split the report into 40 PDF
> documents @. 100 pages, extract the data to file, then use a 3rd party
> PDF renderer to create the file, convince the users to accept an
> ETL-generated CSV file, etc.
> TIP: If you are the admin of a Sql Reporting Services instance, set a
> report development standard for report developers to limit the number
> of rows in a DataSet and the number of Pages rendered.
> Typically my max threshold for data in a report DataSet is 5000 rows @.
> 5 columns (or equivilent). Also, my guideline for report-length is to
> restrict report output to < 100 pages.
> I hope this helps.
> Lance Hunt
> http://weblogs.asp.net/lhunt/
>|||What other third party PDF rendering tool you would suggest? How do I split
current PDF report into many pages thru Reporting Services? I got the same
report that genearats more than 2500 pages and RS threw out 'OutofMemory'
execption. THanks.
-Chuck
"Lance" wrote:
> I agree with the MVP - a 4000+ page report is really just a fancy
> data-feed.
> I often have customers request such reports, and my typical response is
> that Sql Reporting Services is not the right tool for the job. Just
> because you "Can" do something doesnt mean that you should.
> Also, assuming you do get this working, if you legitimately have a
> 4000+ page PDF report, then I have sympathy for the end-user who tries
> to open it in Adobe Acrobat. I have seen Adobe Acrobat opening
> sub-500 page PDF documents bring a high-end PC to its knees, so I am
> fearful of what a 4000 page doc would be like.
> There are plenty of alternatives...split the report into 40 PDF
> documents @. 100 pages, extract the data to file, then use a 3rd party
> PDF renderer to create the file, convince the users to accept an
> ETL-generated CSV file, etc.
> TIP: If you are the admin of a Sql Reporting Services instance, set a
> report development standard for report developers to limit the number
> of rows in a DataSet and the number of Pages rendered.
> Typically my max threshold for data in a report DataSet is 5000 rows @.
> 5 columns (or equivilent). Also, my guideline for report-length is to
> restrict report output to < 100 pages.
> I hope this helps.
> Lance Hunt
> http://weblogs.asp.net/lhunt/
>|||Hi,
Since there's a conclusion here that reporting services is not the right
tool to work around for generating reports that have like more than 150 K
records, is there any solution to this? I mean even if we were to use excel,
excel files only support 65K plus records right?
Thanks
"brett" wrote:
> I read a post from one of the MVPs that stated "If you mean that the result
> set has 90,000 records or 180,000 records then you have the wrong product. RS
> is not designed to generate reports that are 1500+ pages."
> Is there documentation available about the limits of RS (i.e. max number of
> records, max number of pages, etc.)? I realize that it depends on the amount
> of memory available, but I'd like to get the limits for the 2 GB process
> maximum (numbers for 3GB with the /3GB option would be nice also).
> Additionally, how is this affected by multiple users? Is it a sum total of
> all requests or are the requests queued?
> I looked in the docs first for this info and wasn't able to find it.
> Thanks for your help.|||There are at least a few different ways to do this, but I recommend
starting at the end-user and working backwards.
If this report ultimately only needs to be machine-readable, then
choose a data-format such as XML, CSV, MDB (access). Then, work
backwards to figure-out the best technology to deliver this file(s).
If your end-users require this to be a human-readable report, then what
format do they want it in? HTML, Excel, XML, PDF, plain-text, etc.
Also, its unlikely that any person would need to read the entire report
top-to-bottom in one session, so figure-out how they plan to use the
report and break it up accordingly. Possibly this report could be
broken-down into several hundred HTML pages with a table-of-contents to
enable navigation. You may be able to use something such as XML-FO to
help automate this, or a simple XSL and a VBS script to chunk the data.
Hope this helps...
Lance Hunt
http://www.lance-hunt.net/|||It would be fascinating to learn the motivation behind this rather odd
requirement.|||That was me. RS does everything in memory (RAM). This makes sense from a
performance viewpoint but when you get to large amounts of data then that
really makes a difference. The other issue is just efficiency of rendering.
Rendering to PDF and Excel is much slower than HTML or CSV. They have
emphasized fidelity rather than performance. If doing large amounts to Excel
then the following will be very very fast. You need to have a link to do
this because Report Manager defaults to unicode which Excel puts all in one
column. In RS 2005 you will be able to configure Report Manager to use ASCII
CSV export.
Here is an example of a Jump to URL link I use. This causes Excel to come up
with the data in a separate window:
="javascript:void(window.open('" & Globals!ReportServerUrl &
"?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
"&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
Very nice and very fast.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"et_ck" <etck@.discussions.microsoft.com> wrote in message
news:09627F24-3E35-4ABC-A353-DED4A8865DEF@.microsoft.com...
> Hi,
> Since there's a conclusion here that reporting services is not the right
> tool to work around for generating reports that have like more than 150 K
> records, is there any solution to this? I mean even if we were to use
> excel,
> excel files only support 65K plus records right?
> Thanks
> "brett" wrote:
>> I read a post from one of the MVPs that stated "If you mean that the
>> result
>> set has 90,000 records or 180,000 records then you have the wrong
>> product. RS
>> is not designed to generate reports that are 1500+ pages."
>> Is there documentation available about the limits of RS (i.e. max number
>> of
>> records, max number of pages, etc.)? I realize that it depends on the
>> amount
>> of memory available, but I'd like to get the limits for the 2 GB process
>> maximum (numbers for 3GB with the /3GB option would be nice also).
>> Additionally, how is this affected by multiple users? Is it a sum total
>> of
>> all requests or are the requests queued?
>> I looked in the docs first for this info and wasn't able to find it.
>> Thanks for your help.|||I'm with the masses here... what the hell would you do with a 400
page printed report? It's pointless, no-one will read through it. Yo
need to find out what your customer wants to do with it
Personally I'm a fan of exception reports where you actually buil
logic into your report rather than just spewing data from
database
Likewise, with reporting services you could build a summary repor
where the user can interactively drill to more detail which would b
a subset of your 4000 pages|||This is probably some braindead legal requirement, even the most
hideous of paper wasters would not ask for something like this.