Query very slow when executed using Reporting Services

Hello,
I have a dataset that uses a stored procedure as the data source. In
the query analyzer, it takes just over 2 seconds to execute. However,
in the execute query mode in the Visual Studio reports designer, the
query takes several minutes.
The report, when run from the server, also takes a very long time to
complete.
There are only 4 parameters, two varchar(2) and two datetime fields.
The particular query I am running returns 12 rows. There are no filters
applied to the report, not that would affect the query in the VS
reports designer.
Does anybody have any ideas on how to improve the performance?
Thank you,
Brian TakitaFirst the good news. You really did a good job with the information you
provided here. The bad news, this is very strange. The most common reasons
for slowness is lots of data returned (but you only have 12 rows), second is
having a filter. The issue with a filter isn't a filter per se but the fact
that all the data is brought over prior to the filter being applied. If you
execute the stored procedure in query analyzer and it returns 12 rows there
then something else is going on.
Do you have a large amount of fields being returned?
How is your data source credential setup? I am wondering if something is
going on with regards to your credentials that is causing the problem.
Do you have another report that uses the same data source? If so, how is its
performance. If not, then try some simple report that uses the same data
source so we can eliminate that as a possible problem.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Brian Takita" <briantakitaspam@.yahoo.com> wrote in message
news:1112301864.710648.22590@.z14g2000cwz.googlegroups.com...
> Hello,
> I have a dataset that uses a stored procedure as the data source. In
> the query analyzer, it takes just over 2 seconds to execute. However,
> in the execute query mode in the Visual Studio reports designer, the
> query takes several minutes.
> The report, when run from the server, also takes a very long time to
> complete.
> There are only 4 parameters, two varchar(2) and two datetime fields.
> The particular query I am running returns 12 rows. There are no filters
> applied to the report, not that would affect the query in the VS
> reports designer.
> Does anybody have any ideas on how to improve the performance?
> Thank you,
> Brian Takita
>|||Thank you for the prompt reply.
>Do you have a large amount of fields being returned?
Yes. There are 40 fields returned.
>How is your data source credential setup? I am wondering if something
is
>going on with regards to your credentials that is causing the problem.
I'm using integrated security.
>Do you have another report that uses the same data source? If so, how
is its
>performance. If not, then try some simple report that uses the same
data
>source so we can eliminate that as a possible problem.
I tried creating a report using the stored procedure and it is also
slow.|||You can try to pin point what's taking so long. Look in the ReportServer
database; the ExecutionLog
table. It shows what report ran, when, the amount of time for data
retrieval, processing time, and rendering time.
It also shows what render format, render status (fail or success), query
row count and byte size of
the rendered file.
Adrian M.
MCP
"Brian Takita" <briantakitaspam@.yahoo.com> wrote in message
news:1112303389.941233.54770@.z14g2000cwz.googlegroups.com...
> Thank you for the prompt reply.
>>Do you have a large amount of fields being returned?
> Yes. There are 40 fields returned.
>>How is your data source credential setup? I am wondering if something
> is
>>going on with regards to your credentials that is causing the problem.
> I'm using integrated security.
>>Do you have another report that uses the same data source? If so, how
> is its
>>performance. If not, then try some simple report that uses the same
> data
>>source so we can eliminate that as a possible problem.
> I tried creating a report using the stored procedure and it is also
> slow.
>|||Here are the result fields:
TimeDataRetrieval: 255840
TimeProcessing: 45
TimeRendering: 34
Status: rsSuccess
ByteCount: 54070
RowCount: 0
What is strange is when the report is executed on the server, the
RowCount is 0, but when executed from the VS report designer, I get the
correct number of rows, but it is also slow.|||When I try pulling a very similar report with the same parameters as a
pdf file I get the following results:
TimeDataRetrieval: 228716
TimeProcessing: 224082
TimeRendering: 45
Status: rsSuccess
ByteCount: 1872
RountCount: 0|||Is it possible to you to do a test where the stored procedure is returning
fewer fields (for the test just have it do 5 fields for instance) and then
create a report off of that. Let's see if the problem is the number of
fields.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Brian Takita" <briantakitaspam@.yahoo.com> wrote in message
news:1112303389.941233.54770@.z14g2000cwz.googlegroups.com...
> Thank you for the prompt reply.
> >Do you have a large amount of fields being returned?
> Yes. There are 40 fields returned.
> >How is your data source credential setup? I am wondering if something
> is
> >going on with regards to your credentials that is causing the problem.
> I'm using integrated security.
> >Do you have another report that uses the same data source? If so, how
> is its
> >performance. If not, then try some simple report that uses the same
> data
> >source so we can eliminate that as a possible problem.
> I tried creating a report using the stored procedure and it is also
> slow.
>|||Thank you for your help Bruce. Your heuristics to solve the problem
were invaluable.
I solved the issue but did not find the ultimate source of the problem.
To solve the issue I optimized the stored procedure. I removed an
insert statement that scanned a fairly large table.
My stored procedure is fairly complicated as it uses several table
variables.
It strange that the sp would work fine using the query analyzer but not
using Sql Server RS.
I also tried recreating the sp, making all of the functions called in
the sp inline. There was a test case in the sp that took a long time to
complete.
Maybe the sp caused the server to run into memory constraints?
Thanks again,
Brian Takita|||Problem solved. The ExecutionLog table is something I'll need remember.
Thank you Adrian.|||You might want to turn on SQL profiler and look at the statements that are
generated by Query Analyzer and Reporting Services (through sqlclient).
Sometimes, the acutal execution syntax will be slightly different even when
the results are the same. This is primarily in the way that sqlclient
prepares parameters and executes the stored procedure. This difference may
cause SQL Server not to use a compiled query plan.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Brian Takita" <briantakitaspam@.yahoo.com> wrote in message
news:1112313413.140441.295110@.f14g2000cwb.googlegroups.com...
> Thank you for your help Bruce. Your heuristics to solve the problem
> were invaluable.
> I solved the issue but did not find the ultimate source of the problem.
> To solve the issue I optimized the stored procedure. I removed an
> insert statement that scanned a fairly large table.
> My stored procedure is fairly complicated as it uses several table
> variables.
> It strange that the sp would work fine using the query analyzer but not
> using Sql Server RS.
> I also tried recreating the sp, making all of the functions called in
> the sp inline. There was a test case in the sp that took a long time to
> complete.
> Maybe the sp caused the server to run into memory constraints?
> Thanks again,
> Brian Takita
>

Query to Get and Set Image data type Value in SQL Query

Hi

I'm using Sharepoint Services in my application.My database is SQl server 2005 My Sharepoint site using one database there is one table called docs. In this table one column is called MetaInfo and its datatype is Image.
My Question is How to write a Query to Get and Set Image data type Value.I want to execute the Query in SQL Itself. If anyone knows the answer Please let me to know.
Thanks

Regards,
Vinoth

It is not clear what you want to do exactly. But you can just use regular insert/update statements to modify image data. You need to specify the image data as a binary string. Ex:

update t
set imagecol = 0xfff3030939393910

If you have the data in another table, then you can use UPDATE with the T-SQL syntax to copy value from one table to another. Ex:

update t
set imagecol = t1.imagecol
from t1
where t1.i = t.i

Also, you can have SPs with text/ntext/image data type as parameters so you can pass these values directly from the client side and use them in statements like above. Note that you cannot manipulate the variable directly in T-SQL.

For more control on the modifications, you can use UPDATETEXT. See also PATINDEX and SUBSTRING topics in Books Online.

Query that works in Management Studio, fails in Reporting Services

I can run the following query in Management Studio, but get the error listed below when I run it from the data tab in Reporting Services:

declare @.starttime as datetime
declare @.endtime as datetime
declare @.timezone as integer
declare @.date as datetime

set @.timezone = 1
set @.date = '5/1/2007'

set @.starttime = dateadd(hh, @.timezone, @.date)
set @.endtime = dateadd(d, 1, @.starttime)

select @.Starttime, @.endtime from site

Error Message:

TITLE: Microsoft Report Designer

An error occurred while executing the query.
The variable name '@.starttime' has already been declared. Variable names must be unique within a query batch or stored procedure.

ADDITIONAL INFORMATION:

The variable name '@.starttime' has already been declared. Variable names must be unique within a query batch or stored procedure. (Microsoft SQL Server, Error: 134)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.2039&EvtSrc=MSSQLServer&EvtID=134&LinkId=20476

BUTTONS:

OK

What I am trying to accomplish is the ability for users to select which time zone they want the data in the report to display in. To do this, I created a timezone parameter that has the offset from Central Time (which is how all data is stored in our database).

Any help would be greatly appreciated!

Add the word "my" as a prefix to your variables:

DECLARE @.myStartTime smalldate time

etc...

Adamus

|||

Hi,

Please note that expressions in SSRS are case sensitive. In the code above, the starttime is declared as @.starttime and while referring to it in the select clause, you have typed it as @.Starttime. This is causing the conflict. SSRS created a report parameter, Starttime. Since SQL is not case sensitive, while execution it got two declarations for @.starttime and hence the error that you are facing.

After changing the Select clause, delete the parameter from Reports --> Parameters menu option and also from the dataset --> parameters Tab and your query should work.

Also, there is no need for the from clause.

HTH.

Regards,

Ashish

|||They are in fact case-sensitive and I've corrected it and the query now runs is RS. Thank you so much!!!

Query string space limitation

Other posts indicate that Reporting Services has no limit to the size of the
query string defining the Data Set in Visual Studio. I am querying MySQL
(lots of calculation and conversion statements) and after building and
testing the query with another tool, I paste it into the Report Designer data
set query string box. I have successfully pasted large blocks of sql text
into the box (20,000+ characters/35,000+ with white spaces), but the latest
query got cut off during the copy/paste routine (23,405 characters/35,936
with white spaces). Not until I remove text in the MySQL statement does it
paste entirely into the query string box.
Since the larger query works fine against the database in my build tool that
rules out possible limitations with MySQL. So, is there a limitation in how
much text can be entered in the query string box -- character limit? line
limit?
Thanks.I could be having a memory problem, but I thought that the entire RDL must
be < 4000 characters or it can not be deployed.
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"bhc" <bhc@.discussions.microsoft.com> wrote in message
news:1BB42C13-89EB-42C1-8E81-478EC783429F@.microsoft.com...
> Other posts indicate that Reporting Services has no limit to the size of
> the
> query string defining the Data Set in Visual Studio. I am querying MySQL
> (lots of calculation and conversion statements) and after building and
> testing the query with another tool, I paste it into the Report Designer
> data
> set query string box. I have successfully pasted large blocks of sql text
> into the box (20,000+ characters/35,000+ with white spaces), but the
> latest
> query got cut off during the copy/paste routine (23,405 characters/35,936
> with white spaces). Not until I remove text in the MySQL statement does
> it
> paste entirely into the query string box.
> Since the larger query works fine against the database in my build tool
> that
> rules out possible limitations with MySQL. So, is there a limitation in
> how
> much text can be entered in the query string box -- character limit? line
> limit?
> Thanks.|||There is no size limit on RDL files.
Note: if RS is installed on Windows 2003 with IIS 6 you may run into the
default security restriction of a 4 MB file upload/download limit (which can
be changed).
Regarding the large query command text in report designer - are you using
the text-based generic query designer (with 2 panes)?
BTW: you could start with a smaller query that returns all fields, design
the report and as last step replace the smaller query with the huge
commandtext directly in the RDL file.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%233AlNICpFHA.764@.TK2MSFTNGP14.phx.gbl...
>I could be having a memory problem, but I thought that the entire RDL must
>be < 4000 characters or it can not be deployed.
>
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "bhc" <bhc@.discussions.microsoft.com> wrote in message
> news:1BB42C13-89EB-42C1-8E81-478EC783429F@.microsoft.com...
>> Other posts indicate that Reporting Services has no limit to the size of
>> the
>> query string defining the Data Set in Visual Studio. I am querying MySQL
>> (lots of calculation and conversion statements) and after building and
>> testing the query with another tool, I paste it into the Report Designer
>> data
>> set query string box. I have successfully pasted large blocks of sql
>> text
>> into the box (20,000+ characters/35,000+ with white spaces), but the
>> latest
>> query got cut off during the copy/paste routine (23,405 characters/35,936
>> with white spaces). Not until I remove text in the MySQL statement does
>> it
>> paste entirely into the query string box.
>> Since the larger query works fine against the database in my build tool
>> that
>> rules out possible limitations with MySQL. So, is there a limitation in
>> how
>> much text can be entered in the query string box -- character limit? line
>> limit?
>> Thanks.
>|||My problem seems to be limited to the query string on the dataset. I have
already built the report and was modifying (i.e., adding limiters) to my
existing query when it wouldn't paste the query contents in the Query string
field. I used the ellipses next to my existing Dataset name to pull up the
Dataset properties. Then, I cut out the existing Query string, copied and
pasted the new query string (using Command Type: Text) and that's where I
found the problem. The only way to then get a valid dataset that I could use
in my report was to pare down the query length until I could get everything
in the Query String box.
I do not add query strings to Report Designer any other way. I've had
Visual Studio crash on my frequently enough when accessing more than two
tables in a query that I long since have been building queries outside Report
Designer and only then pasting in the final query when I'm ready to build the
reports. This is the first instance where the query did not paste completely
into the box.
I run two different reports off this query. One report is a few hundred K;
the other is about 1MB when exported to Excel. Once I paste in a valid query
into the Dataset properties, all the reports run fine.
Anything else that might illuminate a remedy?
Thanks.
"Robert Bruckner [MSFT]" wrote:
> There is no size limit on RDL files.
> Note: if RS is installed on Windows 2003 with IIS 6 you may run into the
> default security restriction of a 4 MB file upload/download limit (which can
> be changed).
> Regarding the large query command text in report designer - are you using
> the text-based generic query designer (with 2 panes)?
> BTW: you could start with a smaller query that returns all fields, design
> the report and as last step replace the smaller query with the huge
> commandtext directly in the RDL file.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:%233AlNICpFHA.764@.TK2MSFTNGP14.phx.gbl...
> >I could be having a memory problem, but I thought that the entire RDL must
> >be < 4000 characters or it can not be deployed.
> >
> >
> >
> > --
> > Wayne Snyder MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> > (Please respond only to the newsgroup.)
> >
> > I support the Professional Association for SQL Server ( PASS) and it's
> > community of SQL Professionals.
> > "bhc" <bhc@.discussions.microsoft.com> wrote in message
> > news:1BB42C13-89EB-42C1-8E81-478EC783429F@.microsoft.com...
> >> Other posts indicate that Reporting Services has no limit to the size of
> >> the
> >> query string defining the Data Set in Visual Studio. I am querying MySQL
> >> (lots of calculation and conversion statements) and after building and
> >> testing the query with another tool, I paste it into the Report Designer
> >> data
> >> set query string box. I have successfully pasted large blocks of sql
> >> text
> >> into the box (20,000+ characters/35,000+ with white spaces), but the
> >> latest
> >> query got cut off during the copy/paste routine (23,405 characters/35,936
> >> with white spaces). Not until I remove text in the MySQL statement does
> >> it
> >> paste entirely into the query string box.
> >>
> >> Since the larger query works fine against the database in my build tool
> >> that
> >> rules out possible limitations with MySQL. So, is there a limitation in
> >> how
> >> much text can be entered in the query string box -- character limit? line
> >> limit?
> >>
> >> Thanks.
> >
> >
>
>|||You can still do this cut and paste but don't do it the way you are doing
it. Use the generic query designer (2 pane) rather than the graphical (4
pane) designer. The button to switch to the generic query designer is to the
right of the ...
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"bhc" <bhc@.discussions.microsoft.com> wrote in message
news:C6481152-C839-4A9B-BA6F-79A5D6626883@.microsoft.com...
> My problem seems to be limited to the query string on the dataset. I have
> already built the report and was modifying (i.e., adding limiters) to my
> existing query when it wouldn't paste the query contents in the Query
> string
> field. I used the ellipses next to my existing Dataset name to pull up
> the
> Dataset properties. Then, I cut out the existing Query string, copied and
> pasted the new query string (using Command Type: Text) and that's where I
> found the problem. The only way to then get a valid dataset that I could
> use
> in my report was to pare down the query length until I could get
> everything
> in the Query String box.
> I do not add query strings to Report Designer any other way. I've had
> Visual Studio crash on my frequently enough when accessing more than two
> tables in a query that I long since have been building queries outside
> Report
> Designer and only then pasting in the final query when I'm ready to build
> the
> reports. This is the first instance where the query did not paste
> completely
> into the box.
> I run two different reports off this query. One report is a few hundred
> K;
> the other is about 1MB when exported to Excel. Once I paste in a valid
> query
> into the Dataset properties, all the reports run fine.
> Anything else that might illuminate a remedy?
> Thanks.
> "Robert Bruckner [MSFT]" wrote:
>> There is no size limit on RDL files.
>> Note: if RS is installed on Windows 2003 with IIS 6 you may run into the
>> default security restriction of a 4 MB file upload/download limit (which
>> can
>> be changed).
>> Regarding the large query command text in report designer - are you using
>> the text-based generic query designer (with 2 panes)?
>> BTW: you could start with a smaller query that returns all fields, design
>> the report and as last step replace the smaller query with the huge
>> commandtext directly in the RDL file.
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
>> news:%233AlNICpFHA.764@.TK2MSFTNGP14.phx.gbl...
>> >I could be having a memory problem, but I thought that the entire RDL
>> >must
>> >be < 4000 characters or it can not be deployed.
>> >
>> >
>> >
>> > --
>> > Wayne Snyder MCDBA, SQL Server MVP
>> > Mariner, Charlotte, NC
>> > (Please respond only to the newsgroup.)
>> >
>> > I support the Professional Association for SQL Server ( PASS) and it's
>> > community of SQL Professionals.
>> > "bhc" <bhc@.discussions.microsoft.com> wrote in message
>> > news:1BB42C13-89EB-42C1-8E81-478EC783429F@.microsoft.com...
>> >> Other posts indicate that Reporting Services has no limit to the size
>> >> of
>> >> the
>> >> query string defining the Data Set in Visual Studio. I am querying
>> >> MySQL
>> >> (lots of calculation and conversion statements) and after building and
>> >> testing the query with another tool, I paste it into the Report
>> >> Designer
>> >> data
>> >> set query string box. I have successfully pasted large blocks of sql
>> >> text
>> >> into the box (20,000+ characters/35,000+ with white spaces), but the
>> >> latest
>> >> query got cut off during the copy/paste routine (23,405
>> >> characters/35,936
>> >> with white spaces). Not until I remove text in the MySQL statement
>> >> does
>> >> it
>> >> paste entirely into the query string box.
>> >>
>> >> Since the larger query works fine against the database in my build
>> >> tool
>> >> that
>> >> rules out possible limitations with MySQL. So, is there a limitation
>> >> in
>> >> how
>> >> much text can be entered in the query string box -- character limit?
>> >> line
>> >> limit?
>> >>
>> >> Thanks.
>> >
>> >
>>|||I typically don't even use the graphical designer in Reporting Services; I
have them all toggled off and paste the query in the pop-up window. However,
I did try the 2-pane window as you suggest and the query still cuts off.
Pasting the cut-off query into Word gives me a count of 20,812 characters;
31,955 with white spaces. The only way I can get this to paste in its
entirety is to remove one of my fields (luckily, I don't need that).
However, I foresee adding more limiters in the WHERE clause which might bulk
up the query again.
Any other ideas on what is preventing my query from pasting into the designer?
Thanks.
"Bruce L-C [MVP]" wrote:
> You can still do this cut and paste but don't do it the way you are doing
> it. Use the generic query designer (2 pane) rather than the graphical (4
> pane) designer. The button to switch to the generic query designer is to the
> right of the ...
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "bhc" <bhc@.discussions.microsoft.com> wrote in message
> news:C6481152-C839-4A9B-BA6F-79A5D6626883@.microsoft.com...
> > My problem seems to be limited to the query string on the dataset. I have
> > already built the report and was modifying (i.e., adding limiters) to my
> > existing query when it wouldn't paste the query contents in the Query
> > string
> > field. I used the ellipses next to my existing Dataset name to pull up
> > the
> > Dataset properties. Then, I cut out the existing Query string, copied and
> > pasted the new query string (using Command Type: Text) and that's where I
> > found the problem. The only way to then get a valid dataset that I could
> > use
> > in my report was to pare down the query length until I could get
> > everything
> > in the Query String box.
> >
> > I do not add query strings to Report Designer any other way. I've had
> > Visual Studio crash on my frequently enough when accessing more than two
> > tables in a query that I long since have been building queries outside
> > Report
> > Designer and only then pasting in the final query when I'm ready to build
> > the
> > reports. This is the first instance where the query did not paste
> > completely
> > into the box.
> >
> > I run two different reports off this query. One report is a few hundred
> > K;
> > the other is about 1MB when exported to Excel. Once I paste in a valid
> > query
> > into the Dataset properties, all the reports run fine.
> >
> > Anything else that might illuminate a remedy?
> > Thanks.
> >
> > "Robert Bruckner [MSFT]" wrote:
> >
> >> There is no size limit on RDL files.
> >> Note: if RS is installed on Windows 2003 with IIS 6 you may run into the
> >> default security restriction of a 4 MB file upload/download limit (which
> >> can
> >> be changed).
> >>
> >> Regarding the large query command text in report designer - are you using
> >> the text-based generic query designer (with 2 panes)?
> >> BTW: you could start with a smaller query that returns all fields, design
> >> the report and as last step replace the smaller query with the huge
> >> commandtext directly in the RDL file.
> >>
> >> -- Robert
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >>
> >> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> >> news:%233AlNICpFHA.764@.TK2MSFTNGP14.phx.gbl...
> >> >I could be having a memory problem, but I thought that the entire RDL
> >> >must
> >> >be < 4000 characters or it can not be deployed.
> >> >
> >> >
> >> >
> >> > --
> >> > Wayne Snyder MCDBA, SQL Server MVP
> >> > Mariner, Charlotte, NC
> >> > (Please respond only to the newsgroup.)
> >> >
> >> > I support the Professional Association for SQL Server ( PASS) and it's
> >> > community of SQL Professionals.
> >> > "bhc" <bhc@.discussions.microsoft.com> wrote in message
> >> > news:1BB42C13-89EB-42C1-8E81-478EC783429F@.microsoft.com...
> >> >> Other posts indicate that Reporting Services has no limit to the size
> >> >> of
> >> >> the
> >> >> query string defining the Data Set in Visual Studio. I am querying
> >> >> MySQL
> >> >> (lots of calculation and conversion statements) and after building and
> >> >> testing the query with another tool, I paste it into the Report
> >> >> Designer
> >> >> data
> >> >> set query string box. I have successfully pasted large blocks of sql
> >> >> text
> >> >> into the box (20,000+ characters/35,000+ with white spaces), but the
> >> >> latest
> >> >> query got cut off during the copy/paste routine (23,405
> >> >> characters/35,936
> >> >> with white spaces). Not until I remove text in the MySQL statement
> >> >> does
> >> >> it
> >> >> paste entirely into the query string box.
> >> >>
> >> >> Since the larger query works fine against the database in my build
> >> >> tool
> >> >> that
> >> >> rules out possible limitations with MySQL. So, is there a limitation
> >> >> in
> >> >> how
> >> >> much text can be entered in the query string box -- character limit?
> >> >> line
> >> >> limit?
> >> >>
> >> >> Thanks.
> >> >
> >> >
> >>
> >>
> >>
>
>|||This must just be an internal tool issue. This designer came from VS so it
is the same as VS (i.e. it is not specific to RS). My suggestion is to
create this as a stored procedure instead. Better for performance with
something this complicated so SQL Server already has created the query plan.
By the way are you aliasing your table names?
select a.somefield, b.someotherfield from table1 a inner join table2 b on
a.joinfield = b.joinfield
If not that should get your number of characters to be a lot less.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"bhc" <bhc@.discussions.microsoft.com> wrote in message
news:EB7775A7-EDAB-44DD-9C4F-D693699A8268@.microsoft.com...
>I typically don't even use the graphical designer in Reporting Services; I
> have them all toggled off and paste the query in the pop-up window.
> However,
> I did try the 2-pane window as you suggest and the query still cuts off.
> Pasting the cut-off query into Word gives me a count of 20,812 characters;
> 31,955 with white spaces. The only way I can get this to paste in its
> entirety is to remove one of my fields (luckily, I don't need that).
> However, I foresee adding more limiters in the WHERE clause which might
> bulk
> up the query again.
> Any other ideas on what is preventing my query from pasting into the
> designer?
> Thanks.
> "Bruce L-C [MVP]" wrote:
>> You can still do this cut and paste but don't do it the way you are doing
>> it. Use the generic query designer (2 pane) rather than the graphical (4
>> pane) designer. The button to switch to the generic query designer is to
>> the
>> right of the ...
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "bhc" <bhc@.discussions.microsoft.com> wrote in message
>> news:C6481152-C839-4A9B-BA6F-79A5D6626883@.microsoft.com...
>> > My problem seems to be limited to the query string on the dataset. I
>> > have
>> > already built the report and was modifying (i.e., adding limiters) to
>> > my
>> > existing query when it wouldn't paste the query contents in the Query
>> > string
>> > field. I used the ellipses next to my existing Dataset name to pull up
>> > the
>> > Dataset properties. Then, I cut out the existing Query string, copied
>> > and
>> > pasted the new query string (using Command Type: Text) and that's where
>> > I
>> > found the problem. The only way to then get a valid dataset that I
>> > could
>> > use
>> > in my report was to pare down the query length until I could get
>> > everything
>> > in the Query String box.
>> >
>> > I do not add query strings to Report Designer any other way. I've had
>> > Visual Studio crash on my frequently enough when accessing more than
>> > two
>> > tables in a query that I long since have been building queries outside
>> > Report
>> > Designer and only then pasting in the final query when I'm ready to
>> > build
>> > the
>> > reports. This is the first instance where the query did not paste
>> > completely
>> > into the box.
>> >
>> > I run two different reports off this query. One report is a few
>> > hundred
>> > K;
>> > the other is about 1MB when exported to Excel. Once I paste in a valid
>> > query
>> > into the Dataset properties, all the reports run fine.
>> >
>> > Anything else that might illuminate a remedy?
>> > Thanks.
>> >
>> > "Robert Bruckner [MSFT]" wrote:
>> >
>> >> There is no size limit on RDL files.
>> >> Note: if RS is installed on Windows 2003 with IIS 6 you may run into
>> >> the
>> >> default security restriction of a 4 MB file upload/download limit
>> >> (which
>> >> can
>> >> be changed).
>> >>
>> >> Regarding the large query command text in report designer - are you
>> >> using
>> >> the text-based generic query designer (with 2 panes)?
>> >> BTW: you could start with a smaller query that returns all fields,
>> >> design
>> >> the report and as last step replace the smaller query with the huge
>> >> commandtext directly in the RDL file.
>> >>
>> >> -- Robert
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> rights.
>> >>
>> >>
>> >>
>> >> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
>> >> news:%233AlNICpFHA.764@.TK2MSFTNGP14.phx.gbl...
>> >> >I could be having a memory problem, but I thought that the entire RDL
>> >> >must
>> >> >be < 4000 characters or it can not be deployed.
>> >> >
>> >> >
>> >> >
>> >> > --
>> >> > Wayne Snyder MCDBA, SQL Server MVP
>> >> > Mariner, Charlotte, NC
>> >> > (Please respond only to the newsgroup.)
>> >> >
>> >> > I support the Professional Association for SQL Server ( PASS) and
>> >> > it's
>> >> > community of SQL Professionals.
>> >> > "bhc" <bhc@.discussions.microsoft.com> wrote in message
>> >> > news:1BB42C13-89EB-42C1-8E81-478EC783429F@.microsoft.com...
>> >> >> Other posts indicate that Reporting Services has no limit to the
>> >> >> size
>> >> >> of
>> >> >> the
>> >> >> query string defining the Data Set in Visual Studio. I am querying
>> >> >> MySQL
>> >> >> (lots of calculation and conversion statements) and after building
>> >> >> and
>> >> >> testing the query with another tool, I paste it into the Report
>> >> >> Designer
>> >> >> data
>> >> >> set query string box. I have successfully pasted large blocks of
>> >> >> sql
>> >> >> text
>> >> >> into the box (20,000+ characters/35,000+ with white spaces), but
>> >> >> the
>> >> >> latest
>> >> >> query got cut off during the copy/paste routine (23,405
>> >> >> characters/35,936
>> >> >> with white spaces). Not until I remove text in the MySQL statement
>> >> >> does
>> >> >> it
>> >> >> paste entirely into the query string box.
>> >> >>
>> >> >> Since the larger query works fine against the database in my build
>> >> >> tool
>> >> >> that
>> >> >> rules out possible limitations with MySQL. So, is there a
>> >> >> limitation
>> >> >> in
>> >> >> how
>> >> >> much text can be entered in the query string box -- character
>> >> >> limit?
>> >> >> line
>> >> >> limit?
>> >> >>
>> >> >> Thanks.
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>|||The database platform I'm querying is MySQL, not a native SQL Server
database. The database platform itself is version 4.0 and the most recent
MyODBC driver freely available is 3.51. I have not tried so I can't really
speak of the ability to create the query in 4.0 on the native platform and
successfully call it using a 3.51 driver.
And, yes (for sanity, not space reasons) I'm aliasing my tables with single
character references. The white space is predominantly indents (again for
sanity in reviewing the queries).
Since the query tool I'm using processes the large query string just fine I
know it's valid SQL and that it does pull the desired data from the 4.0 MySQL
database. I am puzzeld by what is causing the cut-offs in VS/RD. I'd like
to know what it is so I can find a better workaround.
Thanks.
"Bruce L-C [MVP]" wrote:
> This must just be an internal tool issue. This designer came from VS so it
> is the same as VS (i.e. it is not specific to RS). My suggestion is to
> create this as a stored procedure instead. Better for performance with
> something this complicated so SQL Server already has created the query plan.
> By the way are you aliasing your table names?
> select a.somefield, b.someotherfield from table1 a inner join table2 b on
> a.joinfield = b.joinfield
> If not that should get your number of characters to be a lot less.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "bhc" <bhc@.discussions.microsoft.com> wrote in message
> news:EB7775A7-EDAB-44DD-9C4F-D693699A8268@.microsoft.com...
> >I typically don't even use the graphical designer in Reporting Services; I
> > have them all toggled off and paste the query in the pop-up window.
> > However,
> > I did try the 2-pane window as you suggest and the query still cuts off.
> > Pasting the cut-off query into Word gives me a count of 20,812 characters;
> > 31,955 with white spaces. The only way I can get this to paste in its
> > entirety is to remove one of my fields (luckily, I don't need that).
> > However, I foresee adding more limiters in the WHERE clause which might
> > bulk
> > up the query again.
> >
> > Any other ideas on what is preventing my query from pasting into the
> > designer?
> >
> > Thanks.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> You can still do this cut and paste but don't do it the way you are doing
> >> it. Use the generic query designer (2 pane) rather than the graphical (4
> >> pane) designer. The button to switch to the generic query designer is to
> >> the
> >> right of the ...
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "bhc" <bhc@.discussions.microsoft.com> wrote in message
> >> news:C6481152-C839-4A9B-BA6F-79A5D6626883@.microsoft.com...
> >> > My problem seems to be limited to the query string on the dataset. I
> >> > have
> >> > already built the report and was modifying (i.e., adding limiters) to
> >> > my
> >> > existing query when it wouldn't paste the query contents in the Query
> >> > string
> >> > field. I used the ellipses next to my existing Dataset name to pull up
> >> > the
> >> > Dataset properties. Then, I cut out the existing Query string, copied
> >> > and
> >> > pasted the new query string (using Command Type: Text) and that's where
> >> > I
> >> > found the problem. The only way to then get a valid dataset that I
> >> > could
> >> > use
> >> > in my report was to pare down the query length until I could get
> >> > everything
> >> > in the Query String box.
> >> >
> >> > I do not add query strings to Report Designer any other way. I've had
> >> > Visual Studio crash on my frequently enough when accessing more than
> >> > two
> >> > tables in a query that I long since have been building queries outside
> >> > Report
> >> > Designer and only then pasting in the final query when I'm ready to
> >> > build
> >> > the
> >> > reports. This is the first instance where the query did not paste
> >> > completely
> >> > into the box.
> >> >
> >> > I run two different reports off this query. One report is a few
> >> > hundred
> >> > K;
> >> > the other is about 1MB when exported to Excel. Once I paste in a valid
> >> > query
> >> > into the Dataset properties, all the reports run fine.
> >> >
> >> > Anything else that might illuminate a remedy?
> >> > Thanks.
> >> >
> >> > "Robert Bruckner [MSFT]" wrote:
> >> >
> >> >> There is no size limit on RDL files.
> >> >> Note: if RS is installed on Windows 2003 with IIS 6 you may run into
> >> >> the
> >> >> default security restriction of a 4 MB file upload/download limit
> >> >> (which
> >> >> can
> >> >> be changed).
> >> >>
> >> >> Regarding the large query command text in report designer - are you
> >> >> using
> >> >> the text-based generic query designer (with 2 panes)?
> >> >> BTW: you could start with a smaller query that returns all fields,
> >> >> design
> >> >> the report and as last step replace the smaller query with the huge
> >> >> commandtext directly in the RDL file.
> >> >>
> >> >> -- Robert
> >> >> This posting is provided "AS IS" with no warranties, and confers no
> >> >> rights.
> >> >>
> >> >>
> >> >>
> >> >> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> >> >> news:%233AlNICpFHA.764@.TK2MSFTNGP14.phx.gbl...
> >> >> >I could be having a memory problem, but I thought that the entire RDL
> >> >> >must
> >> >> >be < 4000 characters or it can not be deployed.
> >> >> >
> >> >> >
> >> >> >
> >> >> > --
> >> >> > Wayne Snyder MCDBA, SQL Server MVP
> >> >> > Mariner, Charlotte, NC
> >> >> > (Please respond only to the newsgroup.)
> >> >> >
> >> >> > I support the Professional Association for SQL Server ( PASS) and
> >> >> > it's
> >> >> > community of SQL Professionals.
> >> >> > "bhc" <bhc@.discussions.microsoft.com> wrote in message
> >> >> > news:1BB42C13-89EB-42C1-8E81-478EC783429F@.microsoft.com...
> >> >> >> Other posts indicate that Reporting Services has no limit to the
> >> >> >> size
> >> >> >> of
> >> >> >> the
> >> >> >> query string defining the Data Set in Visual Studio. I am querying
> >> >> >> MySQL
> >> >> >> (lots of calculation and conversion statements) and after building
> >> >> >> and
> >> >> >> testing the query with another tool, I paste it into the Report
> >> >> >> Designer
> >> >> >> data
> >> >> >> set query string box. I have successfully pasted large blocks of
> >> >> >> sql
> >> >> >> text
> >> >> >> into the box (20,000+ characters/35,000+ with white spaces), but
> >> >> >> the
> >> >> >> latest
> >> >> >> query got cut off during the copy/paste routine (23,405
> >> >> >> characters/35,936
> >> >> >> with white spaces). Not until I remove text in the MySQL statement
> >> >> >> does
> >> >> >> it
> >> >> >> paste entirely into the query string box.
> >> >> >>
> >> >> >> Since the larger query works fine against the database in my build
> >> >> >> tool
> >> >> >> that
> >> >> >> rules out possible limitations with MySQL. So, is there a
> >> >> >> limitation
> >> >> >> in
> >> >> >> how
> >> >> >> much text can be entered in the query string box -- character
> >> >> >> limit?
> >> >> >> line
> >> >> >> limit?
> >> >> >>
> >> >> >> Thanks.
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>