Anyone producing a large number of reports per day?

I'm evaluting Reporting Services for my client. They need to produce 5000+
reports per day, mainly output in PDF format. The PDF's are 6-8 pages long.
Does anyone have any experience of getting this kind of performance out of
SQL-RS?
They have an 8-processor 2.8Ghz server running SQL Server that will be the
Reporting Services server. It has 4gig of memory. Will it be up to the job?
Thanks,
Andy.I am able to render about 70reports (PDF format) in less than 10mins. Each
report has about 200 pages.
"Andy Smith" wrote:
> I'm evaluting Reporting Services for my client. They need to produce 5000+
> reports per day, mainly output in PDF format. The PDF's are 6-8 pages long.
> Does anyone have any experience of getting this kind of performance out of
> SQL-RS?
> They have an 8-processor 2.8Ghz server running SQL Server that will be the
> Reporting Services server. It has 4gig of memory. Will it be up to the job?
> Thanks,
> Andy.|||Don't forget to set boot.ini to use /3GB switch in order to take advantage
of the memory.
To verify RS can handle this load, consider using a test tool like
Application Center Test (ACT), part of Visual Studio, to run a Proof Of
Concept. Below is sample ACT script you might use to run such a test:
' --
CONSTANTS --
const ENABLE_DELAYS = True
const REQUESTBUFFERSIZE = 15000
const REPORTSERVERNAME = "localhost"
' --
' -- Think time variables - examples shows between 2-3 seconds
' --
const MIN_SLEEP_MSEC = 2000
const MAX_SLEEP_MSEC = 3000
' --
' -- Think time logic if desired
' --
Function RandomSleep()
if (NOT ENABLE_DELAYS) then
RandomSleep = 0
return
end if
Dim lMinSleep, lMaxSleep, lSleep
lMaxSleep = MAX_SLEEP_MSEC
lMinSleep = MIN_SLEEP_MSEC
' create a random int within our range
Call Randomize()
lSleep = Int((lMaxSleep - lMinSleep + 1) * Rnd(1) + lMinSleep)
' Test.Trace "Sleeping: " + Cstr(lSleep)
Call Test.Sleep(lSleep)
' return the delay time
RandomSleep = lSleep
End Function
Function SendGetRequest(reportServerUrl)
Dim oConnection, oRequest, oResponse, oHeaders, statusCode
Set oConnection = Test.CreateConnection(REPORTSERVERNAME, 80, false)
If (oConnection is Nothing) Then
Test.Trace "Error: Unable to create connection to server"
Else
' TODO: uncomment this after debugging
' Test.Trace(reportServerUrl)
Set oRequest = Test.CreateRequest
oRequest.ResponseBufferSize = REQUESTBUFFERSIZE
oRequest.Path = reportServerUrl
oRequest.Verb = "GET"
oRequest.HTTPVersion = "HTTP/1.1"
set oHeaders = oRequest.Headers
oHeaders.RemoveAll
oHeaders.Add "Accept", "image/gif, image/x-xbitmap, image/jpeg,
image/pjpeg, application/vnd.ms-excel, application/vnd.ms-powerpoint,
application/msword, */*"
oHeaders.Add "Accept-Language", "en-us"
oHeaders.Add "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0;
Windows NT 5.1; .NET CLR 1.0.3512; .NET CLR 1.1.4322)"
oHeaders.Add "Host", "(automatic)"
oHeaders.Add "Cookie", "(automatic)"
Set oResponse = oConnection.Send(oRequest)
If (oResponse is Nothing) Then
Test.Trace "Error: Failed to receive response for URL to " +
reportServerUrl
Else
statusCode = oResponse.ResultCode
'DEBUG
Test.Trace "Received: " + CStr(statusCode) + " for: " + REPORTSERVERNAME
+ reportServerUrl
' delay in case of errors - to avoid the snowball effect
' Test.Trace "Response code of: " + CStr(statusCode) + "
recieved for " + reportServerUrl
if (statusCode = 503) Then
Test.Trace "Server too busy error: " + CStr(statusCode) + "
recieved for " + reportServerUrl + " - Process Put To Sleep"
End If
End If
oConnection.Close
End If
End Function
Sub Main()
Dim Url
'--
'-- URL of report to call for test. This one calls report named "Simple"
located in dir /Benchmark
'-- passing in a parm of &RowNumber '--
Url ="/ReportServer?/Benchmark/Simple&RowNumber=20&rs:Command=Render&rc:Toolbar=false&rs:Format=PDF"
SendGetRequest(Url)
RandomSleep()
End Sub
Main
--
-- "This posting is provided 'AS IS' with no warranties, and confers no
rights."
jhmiller@.online.microsoft.com
"Raj Chandra" <RajChandra@.discussions.microsoft.com> wrote in message
news:C23DE604-F4E0-4F3E-BD7B-1C4516C1987C@.microsoft.com...
>I am able to render about 70reports (PDF format) in less than 10mins. Each
> report has about 200 pages.
> "Andy Smith" wrote:
>> I'm evaluting Reporting Services for my client. They need to produce
>> 5000+
>> reports per day, mainly output in PDF format. The PDF's are 6-8 pages
>> long.
>> Does anyone have any experience of getting this kind of performance out
>> of
>> SQL-RS?
>> They have an 8-processor 2.8Ghz server running SQL Server that will be
>> the
>> Reporting Services server. It has 4gig of memory. Will it be up to the
>> job?
>> Thanks,
>> Andy.sql

Anyone know table limits in multi-schema environment?

My product is growing rapidly and currently I have a db for each client with identical schema. Of course maintenance is pretty hard. I was thinking of using a shared db but having a schema for each client (sql 2005) - I have almost 100 tables in the schema which means with just 10 clients the db would pass 1000 tables. My gut is telling me this ain't going to fly!

any ideas? and if it does work ... any thoughts on updating the internal schemas for each client?

thanks

-c

You definately can get over 1000 tables, since certain complex ERP systems and such have that just on their own, and that was even in older version of SQL Server.

According to :http://msdn2.microsoft.com/en-us/library/aa933149(SQL.80).aspx

The amount of tables is actualy only limited by the amount of objects (which means all triggers, tables, stored procedures, etc count toward this limit). The limit is... 2,147,483,647

Good luck busting that. Oh, and thats SQL Server 2000, was too lazy to find the 2005 specs :)|||

Sounds responable to me. I was going off this article from MS that suggested no more than 100 tables per client schema in a single db but they don't specify why :)

http://msdn2.microsoft.com/en-us/library/aa479086.aspx

Any idea how to do updates to the schema? my current thinking is to get my app to login as each schema owner and execute the update script.

thanks

-c

Anyone know if lineheight property works?

A client I'm producing several reports for wants the spacing between the
lines of text to be less and I haven't any ideas other than this property to
get it to happen.
Any suggestions would be AWESOME!
Thanks,
DiaDia
I use tables alot and the height property works very well in a table. You
can also adjust the distance between rows with the padding property.
"Dia" wrote:
> A client I'm producing several reports for wants the spacing between the
> lines of text to be less and I haven't any ideas other than this property to
> get it to happen.
> Any suggestions would be AWESOME!
> Thanks,
> Dia

Any way to get processing status when executing a large batch process job via AMO?

When making an ExecuteCaptureLog() AMO call, is there any way the client can poll for processing status from the SSAS insance?

I think the only way you could do this is to capture the Trace events

This thread has some samples showing you how to create and use Trace events from code.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=482459&SiteID=1

Any way to force collation/sort-order as part of the ODBC connection?

In the situation where the ODBC client app assumes a specific collation or, more specifically, a specific sort-order (case-sensitive vs. case-insensitive) but the collation/sort-order of the SQL Server Database and/or Table is unknown, is there a way to coerce the ODBC connection to always use a case-sensitive/case-insensitive sort order?

I believe case-sensitivity is a characteristic of the database you're connecting to. I don't know of a client setting for this.|||Collation is specified during server installation or db/table creation time. I don't believe you can change it on the fly via ODBC API.|||

Thanks Warren and Ricky. I suspected as much, just wasn't sure.

Any way to force collation/sort-order as part of the ODBC connection?

In the situation where the ODBC client app assumes a specific collation or, more specifically, a specific sort-order (case-sensitive vs. case-insensitive) but the collation/sort-order of the SQL Server Database and/or Table is unknown, is there a way to coerce the ODBC connection to always use a case-sensitive/case-insensitive sort order?

I believe case-sensitivity is a characteristic of the database you're connecting to. I don't know of a client setting for this.|||Collation is specified during server installation or db/table creation time. I don't believe you can change it on the fly via ODBC API.|||

Thanks Warren and Ricky. I suspected as much, just wasn't sure.

Any way to easily add this computed column (divide by zero problem)

I have this query that I would rather not turn into a stored proc.
because the client really has no budget. I was hoping there would be a
way to add this statement inline to the following sql with some kind of
IF statement without writing it all out. The problem is either total
cost or conversions will have some zeros in the table.

SUM([total cost]/[cost per conversion])

Any help would be appreciated - Happy New Year

SELECT [Search Term], SUM([total cost]/[cost per conversion]) as calcw,
SUM([impressions]) AS impress, SUM([Total Cost]) AS totalcost,
SUM([Total Clicks]) AS totalclicks, SUM(Conversions) AS totalconv,
SUM([Cost Per Conversion]) AS costconv FROM csv where [start date]
>='01/01/04' and [end date] <='12/31/04' GROUP BY [Search Term] ORDER
BY [Search Term] ASCDo you just want to ignore the row in the sum if either value is zero? If
so:

SUM([total cost]/NULLIF([cost per conversion],0))

--
David Portas
SQL Server MVP
--|||I would rather the row be included as just returned as zero

Thanks!|||Also I tried that statement and it works well except I think the
calculation comes out incorrect - It needs to be something like the
SUM of total cost divived by the SUM of Conversions.

Thanks|||COALESCE( SUM([total cost]) / NULLIF(SUM([cost per conversion]),0) ,0)

--
David Portas
SQL Server MVP
--|||Thank you very much David - That seems to have done it!
Thanks again...

Any suggesstions please

I am passing a XML string from client, In the back end I
am verifying the data with constraints of the columns.
Exec sp_xml_preparedocument @.hdoc OUTPUT,@.xml
select * into #tempApps
from openxml(@.hdoc, N'//application',2)
with AP_Applications --This is the physical table
EXEC sp_xml_removedocument @.hdoc
The system will create an ApplicationId at the time of
inserting a new record. Before inserting a record I am
checking the data.
When I run the above code I am getting the following
error.
Unexpected NULL value returned for
column '[OpenXML].AppId' from the OLE DB
provider 'OpenXML'. This column cannot be NULL.
OLE DB error trace [Non-interface error: Unexpected NULL
value returned for the column: ProviderName='OpenXML',
TableName='[OpenXML]', ColumnName='AppId'].
I will appreciate you can give some suggestions.
Thanks in Advance
You cannot use a table name with an identity column in the with clause. You
have to give the with clause (without the Identity column) explicit.
Best regards
Michael
<anonymous@.discussions.microsoft.com> wrote in message
news:784a01c43121$a8d09420$a501280a@.phx.gbl...
>I am passing a XML string from client, In the back end I
> am verifying the data with constraints of the columns.
> Exec sp_xml_preparedocument @.hdoc OUTPUT,@.xml
> select * into #tempApps
> from openxml(@.hdoc, N'//application',2)
> with AP_Applications --This is the physical table
>
>
> EXEC sp_xml_removedocument @.hdoc
>
> The system will create an ApplicationId at the time of
> inserting a new record. Before inserting a record I am
> checking the data.
> When I run the above code I am getting the following
> error.
> Unexpected NULL value returned for
> column '[OpenXML].AppId' from the OLE DB
> provider 'OpenXML'. This column cannot be NULL.
> OLE DB error trace [Non-interface error: Unexpected NULL
> value returned for the column: ProviderName='OpenXML',
> TableName='[OpenXML]', ColumnName='AppId'].
> I will appreciate you can give some suggestions.
>
> Thanks in Advance
>

any good sql client tool to connect database

any good sql client tool to connect database
thx
How about Query Analyzer? What exactly are you looking for?
David Portas
SQL Server MVP
|||Hi
This will depend on what your requirements are! If you are writing your own
front end then all you need to install is MDAC, if you want an
administration tool then you could use the Microsoft Web Data Administrator
or others listed at http://www.aspfaq.com/show.asp?id=2442
John
"inamori" <test@.test.com> wrote in message
news:cnqcvo$l191@.imsp212.netvigator.com...
> any good sql client tool to connect database
>
> thx
>
|||TOAD for SQL Server is nice. Even better if you are working with Oracle as
well as SQL Server.
www.toadsoft.com
I normally just use Query Analyzer however, or Visual Studio.
Rick Sawtell
MCT, MCSD, MCDBA
"inamori" <test@.test.com> wrote in message
news:cnqcvo$l191@.imsp212.netvigator.com...
> any good sql client tool to connect database
>
> thx
>

any good sql client tool to connect database

any good sql client tool to connect database
thxHow about Query Analyzer? What exactly are you looking for?
David Portas
SQL Server MVP
--|||Hi
This will depend on what your requirements are! If you are writing your own
front end then all you need to install is MDAC, if you want an
administration tool then you could use the Microsoft Web Data Administrator
or others listed at http://www.aspfaq.com/show.asp?id=2442
John
"inamori" <test@.test.com> wrote in message
news:cnqcvo$l191@.imsp212.netvigator.com...
> any good sql client tool to connect database
>
> thx
>|||TOAD for SQL Server is nice. Even better if you are working with Oracle as
well as SQL Server.
www.toadsoft.com
I normally just use Query Analyzer however, or Visual Studio.
Rick Sawtell
MCT, MCSD, MCDBA
"inamori" <test@.test.com> wrote in message
news:cnqcvo$l191@.imsp212.netvigator.com...
> any good sql client tool to connect database
>
> thx
>

any good sql client tool to connect database

any good sql client tool to connect database
thxHow about Query Analyzer? What exactly are you looking for?
--
David Portas
SQL Server MVP
--|||Hi
This will depend on what your requirements are! If you are writing your own
front end then all you need to install is MDAC, if you want an
administration tool then you could use the Microsoft Web Data Administrator
or others listed at http://www.aspfaq.com/show.asp?id=2442
John
"inamori" <test@.test.com> wrote in message
news:cnqcvo$l191@.imsp212.netvigator.com...
> any good sql client tool to connect database
>
> thx
>|||TOAD for SQL Server is nice. Even better if you are working with Oracle as
well as SQL Server.
www.toadsoft.com
I normally just use Query Analyzer however, or Visual Studio.
Rick Sawtell
MCT, MCSD, MCDBA
"inamori" <test@.test.com> wrote in message
news:cnqcvo$l191@.imsp212.netvigator.com...
> any good sql client tool to connect database
>
> thx
>