Query Execution failed for dataset error / the syntax for " " is incorrect

Hi,

Am totally new to MDX Queries.

I got this error while previewing a report that we had made. It goes like this --

Query execution failed for dataset 'some_dataset'.

Parser: the syntax for 'some_character' is incorrect.

What was figured out was this error shows up only for those values in a particular dropdown list which are multiword. Eg : My Company Technology and not for those values which a re single word. Eg : Company

what could be the possible problem and the solution to that.

Try to use [My Company Technology] as parameter value.|||

I dont wish to hard code the values.

It should be dynamic

|||

Hi Madu,

here you can find the rules for the identifiers in MDX:

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

Query Execution Failed for Dataset (Beginner)

I got this Error, Query Execution for Dataset 'Source'.

When i try to run a report that is actually a drillthrough from another report. It runs fine in Report Designer, and when i deploy to my Local server. But when i deploy it to my virtual Report Server, I get this error messege. Why is it doing this? and where can i see errors for this type of stuff, so i can figure this out. This uses the same Datasource as the report linked from it. That report works fine, why wont this one? any ideas?

Look for the report server log at this location C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\LogFiles if installed to the default location.|||A collegue of mine helped me figure this one out. My stored procedure was not set to public in the properties section. Thats why it worked on my local computer and not on the report server.

Query execution failed for data set <name> For more information about this error navigate

I have created and deployed my first report. It renders fine for me and the other database admin. When others attempt to view it, we get the error

Query execution failed for data set 'periods'. (rsErrorExecutingCommand), For more information about this error navigate to the report server on the local server machine, or enable remote errors

Initially, We created a local group on the machine that hosts both the database and webserver and added the individuals to that group. Then, within SRS Report manager, we added that group to the Browswer role of the report.

The error message was slightly different, in that it couldn't even open the Datasource.

We then added an individual to the database as dbreader, and got the above message. It apprently is starting to render, and when it encounters the first query (dataset "periods", which populates a drop down list for a parameter), it chokes. BTW, the Periods dataset executes a stored procedure dbo.Period_List that has no parameters. It returns a list of reporting periods.

I could not figure out how to "enable remote errors" or find an error log on the server. The C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\LogFiles Log files did not appear to record any errors.

Please advise!

Have you given execute permissions on the stored procedure? You can try troubleshooting with SQL Server Profiler (http://msdn2.microsoft.com/en-us/library/ms187929.aspx), but that might be like hitting a gnat with a sledge hammer.

Larry

|||

I want to add some information. We recently enabled remote errors, and found that EXECUTE permission on the stored procedure was denied.

Does this mean that we have to grant permissions on every stored procedure to User Groups?

I love SRS, but this is turning into an Admin nightmare.

|||

Ok, let's back up a second and take another run at this. As long as you don't require the data on your reports to change based on who is viewing them, use a shared DataSource with stored credentials (http://technet.microsoft.com/en-us/library/ms178308.aspx). That way, you admin a single user and everyone (including you during development) sees the same report.

To continue to use Windows Authentication, yes, you would need to grant permissions for every stored procedure that you want SSRS to use to any user that will run a report using that stored procedure.

Larry

|||

thank you!

Ok, the first option sounds like the way to go for the moment, but something you said intrigued me...

"As long as you don't require the data on your reports to change based on who is viewing them..."

Actually, we would like to restrict what the users see based upon who they are, and haven't figured out how to do that yet. For example, if a person is from business unit A, we would like them only to see their business units data. All business units are in the same table however, so we would have to use a parameter or a filter, based upon the longin User ID. Not sure how to do this, any ideas?

|||

Well, you could implement row level security to filter the results returned based on the user. Here is a tutorial from Books On Line (BOL) (http://msdn2.microsoft.com/en-us/library/ms365305.aspx) and here is a white paper discussing it (http://www.microsoft.com/technet/prodtechnol/sql/2005/multisec.mspx). It is a bit complex to get setup, but the results are well worth the effort.

Larry

Query execution failed

Hi,

I have a group of reports using a shared datasource. Going to the preview of the report works fine in the report designer, but when I try and view it from a browser (deployed on a website), it gives the error:

"An error has occurred during report processing.

Query execution failed for data set 'DataSet1_ticketInfo'.

Failed to parse SQL.[long sql query here]"

If there's a problem with it, I don't get why it works in preview mode. I'm using SQL server 2005.

Thanks.
Hi,

Can you provide some more details about your Report

1) 'DataSet1_ticketInfo' - Is this your main DataSet ie; the result of this DS is used on the Report body OR
2) Are you using 'DataSet1_ticketInfo' DS for populating Report Parameters OR
3) Your DS contains some script (query or Stored Proc) that requires special permissions to be executed through your asp.net web-site

I think you may be missing some params used by above mentioned DS, which may be causing the error.

Regards,
abhi_viking

|||That dataset is the main dataset for the report. One of these reports (all are having the same problem) does take parameters, but I have a default value set, which actually shows up in the datepicker correctly. This particular dataset doesn't take any parameters. The actual report doesn't load however (but does in preview)

|||

Hi,

Are you using some code that requires permission to be executed thro' your asp.net website?

If possible, do paste ur DS code here, that might help us solve your issue.

Regards,

abhi_viking

|||How would I get the actual code of the DS? Just to be clear, this is a server report, not a client one. I've verified that the conn. string is correct and does connect ok.
|||After talking to MS support, it seems that if you use a column name alias (ie "as") in the select statement, the report viewer refuses to parse the SQL. It seems to be some sort of bug. We were able to get around it by having the report run a stored procedure, which allowed the column names. Hope this helps anyone else that runs into this issue.

Query execution failed

Hi,

I have a group of reports using a shared datasource. Going to the preview of the report works fine in the report designer, but when I try and view it from a browser (deployed on a website), it gives the error:

"An error has occurred during report processing.

Query execution failed for data set 'DataSet1_ticketInfo'.

Failed to parse SQL.[long sql query here]"

If there's a problem with it, I don't get why it works in preview mode. I'm using SQL server 2005.

Thanks.
Hi,

Can you provide some more details about your Report

1) 'DataSet1_ticketInfo' - Is this your main DataSet ie; the result of this DS is used on the Report body OR
2) Are you using 'DataSet1_ticketInfo' DS for populating Report Parameters OR
3) Your DS contains some script (query or Stored Proc) that requires special permissions to be executed through your asp.net web-site

I think you may be missing some params used by above mentioned DS, which may be causing the error.

Regards,
abhi_viking

|||That dataset is the main dataset for the report. One of these reports (all are having the same problem) does take parameters, but I have a default value set, which actually shows up in the datepicker correctly. This particular dataset doesn't take any parameters. The actual report doesn't load however (but does in preview)

|||

Hi,

Are you using some code that requires permission to be executed thro' your asp.net website?

If possible, do paste ur DS code here, that might help us solve your issue.

Regards,

abhi_viking

|||How would I get the actual code of the DS? Just to be clear, this is a server report, not a client one. I've verified that the conn. string is correct and does connect ok.
|||After talking to MS support, it seems that if you use a column name alias (ie "as") in the select statement, the report viewer refuses to parse the SQL. It seems to be some sort of bug. We were able to get around it by having the report run a stored procedure, which allowed the column names. Hope this helps anyone else that runs into this issue.

Query execution failed

Hi,

I have a group of reports using a shared datasource. Going to the preview of the report works fine in the report designer, but when I try and view it from a browser (deployed on a website), it gives the error:

"An error has occurred during report processing.

Query execution failed for data set 'DataSet1_ticketInfo'.

Failed to parse SQL.[long sql query here]"

If there's a problem with it, I don't get why it works in preview mode. I'm using SQL server 2005.

Thanks.
Hi,

Can you provide some more details about your Report

1) 'DataSet1_ticketInfo' - Is this your main DataSet ie; the result of this DS is used on the Report body OR
2) Are you using 'DataSet1_ticketInfo' DS for populating Report Parameters OR
3) Your DS contains some script (query or Stored Proc) that requires special permissions to be executed through your asp.net web-site

I think you may be missing some params used by above mentioned DS, which may be causing the error.

Regards,
abhi_viking

|||That dataset is the main dataset for the report. One of these reports (all are having the same problem) does take parameters, but I have a default value set, which actually shows up in the datepicker correctly. This particular dataset doesn't take any parameters. The actual report doesn't load however (but does in preview)

|||

Hi,

Are you using some code that requires permission to be executed thro' your asp.net website?

If possible, do paste ur DS code here, that might help us solve your issue.

Regards,

abhi_viking

|||How would I get the actual code of the DS? Just to be clear, this is a server report, not a client one. I've verified that the conn. string is correct and does connect ok.
|||After talking to MS support, it seems that if you use a column name alias (ie "as") in the select statement, the report viewer refuses to parse the SQL. It seems to be some sort of bug. We were able to get around it by having the report run a stored procedure, which allowed the column names. Hope this helps anyone else that runs into this issue.

Query Engine Error?

I have a report which displays some customer information and their transactions. The data is coming from an MS Access Database. The user must specify a customer either by customer Number or by their Last Name. I have C#.NET code which does this. Unfortunatley the data doesn't display on the report if the customer doesn't have any transactions. I asked another developer and he said to change the joins to right joins. The closest option i have is right outer join so i selected that. When i do this and run the report i get "Query Engine Error". It also displays the filepath to the rpt file. The database has been built up over a number of years and is not well normalized. Therefore i have more tables in the report then I would like. The database is quite large and would take alot of time to normalize that database and the program using the database would have to be altered as well. As you may have guessed, that code has also been developed over a number of years and again would take alot of time to change that. Is there an easy solution to my problem. I am not too experienced with Crystal Reports and any help would be greatly appreciated.Run the right join query in Access and see if it works

Query Engine Error again

I have crystal reports in my VS2005 project. We have wrapped dll for report printing, but using version 9.2.3300.0. So I got Error "Query Engine Error" when printing. If I use Crystal Reports 10.2.3600.0, It works fine.
How can I print report (10.2.3600.0) by wrapped dll (9.2.3300.0)?Did you miss any dlls?
Do very database and check

query engine error

"query engine error" in ...4trerf-fr454-5453-ffftre4.rpt"
Usually what is the problem when i get this message error,when is loading a report into a reportviewer?
thanksThis may be due to the improper link between the tables used. Check for the link between the tables properly.

Ashok|||I am have 7 reports in my app and none of my reports work on the deployment machine. Could there be anything else wrong with it? It works fine on my dev machine.

Sue

Query Engine Error

Hi All,

I am trying to build an application in VS.Net which uses Crystal Report 11 to build the rpt file. I have an XML and XSD file as my datasources. I want to view the report in PDF format. Now my source code looks like:

ReportDocument doc = new ReportDocument();
string fileName = Server.MapPath("MyReport.rpt");
doc.Load(fileName);

DataSet ds = new DataSet();
ds.ReadXml(Server.MapPath("MyXML.xml"));

doc.SetDataSource(ds);

ExportOptions exportOpts = doc.ExportOptions;
exportOpts.ExportFormatType = ExportFormatType.PortableDocFormat;
exportOpts.ExportDestinationType = ExportDestinationType.DiskFile;
exportOpts.DestinationOptions = new DiskFileDestinationOptions();

DiskFileDestinationOptions diskOpts = new DiskFileDestinationOptions();
((DiskFileDestinationOptions)doc.ExportOptions.DestinationOptions).DiskFileName = Server.MapPath("MyPDF.pdf");

doc.Export();

But it gives me the error
"Query Engine Error: 'c:\inetpub\wwwroot\MyWebsite\MyReport.rpt'"

I tried out all the forums but could not find any solution. Someone told me that it is due to xsd file error. But in Crystal Report it is running perfectly OK. Can anyone help.did you figure out what happened? I am getting this error during my deployment, but on my local PC it works fine.

bev

Query designer toggle button not present

When I create a Report Server Project Using Visual Studio 2005 with SQL Server 2005 I can create a data source with no problem and the test shows it is good good.

When I next create a report and go to the query builder using that same data source and click on the query builder button I see the Query Builder screen, but there is no toggle button in the top left of the screen so I cannot go into the graphical mode to see the tables.

I have uninstalled and reinstalled both Visual Studio and SQL Server but I still have the same problem. What should I do to get the button visible on the screen?

Can anyone help?

Terry,

Are you setting this report up from a shared data source or from making a new datasource. You may want to set up a shared datasource first then make your report from the shared datasource and see if that works. It works that way for me.

|||

It makes no difference either way, I can have new data source or a shared data source.

I have a server and my computer both of which appear to have a nearly identical setup of Studio and SQL Server but I can perform identical steps on both and on the server I get the toggle button but on my computer I get nothing. This is what makes be think it may be a bad installation but I have reinstalled both Studio and SQL Server on my machine and I still get the same thing!

Thanks for responding though.

Terry

|||

Terry,

You may have already figured it out, but you may just want to use stored procedures. When we create a new report we just go through the wizard, pick our datasource, and then tell it what stored proc we want it to use. I believe the syntax is

exec rptsp_MyStoredProcedure @.MyParameter1,@.MyParameter2

Do you have a lot of experience with SQL queries and stored procs? If not maybe I can help.

Query Designer for AS2000

Hello,
I've installed new CTP June of SQL2005. And I'd like to create new report in
reporting services with connection to Analysis Services 2000.
So, I've created datasource, looks fine, test connection succeeded. When I'm
starting to create report, after selecting datasource, when I click on
"Query Designer"
I get following message:
"The query designer could not be loaded.
Verify your connection string and query string or enter a valid query to
continue."
Where is the problem?
Best regards
Piotr TrêbaczMake sure you add ConnectTo=8.0 to the connection string
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Piotr Trêbacz" <ptrebacz@.kruk-inkaso.com.pl> wrote in message
news:uJ8n5WvdFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I've installed new CTP June of SQL2005. And I'd like to create new report
> in
> reporting services with connection to Analysis Services 2000.
> So, I've created datasource, looks fine, test connection succeeded. When
> I'm
> starting to create report, after selecting datasource, when I click on
> "Query Designer"
> I get following message:
> "The query designer could not be loaded.
> Verify your connection string and query string or enter a valid query to
> continue."
> Where is the problem?
> Best regards
> Piotr Trêbacz
>|||> Make sure you add ConnectTo=8.0 to the connection string
It works, great, big thanks. I wonder why MS people didn't mention this when
I ask few times:)
Thanks again
Reagards
Piotr

query defining parameter dropdowns is run twice!

I built a very simple report which uses a query to define the options in the parameter’s dropdown. I used that same dataset to define the default for that parameter (meaning that it will just pick the first row from the dataset and use that as the default). When I run the report watching a Profiler trace on the SQL database, it runs that query twice. (Presumably, that’s once to fill the dropdown list and once to figure out the default.) That seems silly to me since it is the same query that is the same dataset in Reporting Services. Is there any way around this? My parameter bar is rendering twice as slowly as it should be.

I've tested against SSRS 2005 SP1 and the CTP of SP2.

Can you confirm your test by removing the default and re-testing to make sure it gets run once. Then adding the default again to confirm it goes back to 2 executions?|||Yes, it only runs it once if I don't specify that the parameter should have a default from a query.

Query calculated OLAP members

I'm trying to create a report using an OLAP cube as my data source, but I can
retrieve only the dimensions and measures, not the calculated members.
My query is:
select * from MasterCube
Is there any way to extract them?Below is a sample MDX query (used in the sample Foodmart reporting services
report) which includes a calculated measure, Store Cost.
SELECT { [Measures].[Store Sales], [Measures].[Store Cost] } ON COLUMNS,
{ Descendants([Product].[All Products], [Product].[Brand Name], LEAVES) }
ON ROWS,
{ Time.[1997].[Q1],Time.[1997].[Q2],Time.[1997].[Q3],Time.[1997].[Q4] } ON
PAGES
FROM Sales
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Cesar Meza" <CesarMeza@.discussions.microsoft.com> wrote in message
news:0A29D2FD-F3CF-4772-A063-E585C6B1DDA3@.microsoft.com...
> I'm trying to create a report using an OLAP cube as my data source, but I
> can
> retrieve only the dimensions and measures, not the calculated members.
> My query is:
> select * from MasterCube
> Is there any way to extract them?|||The real issue this: I have a cube that is too big for Excel's Pivot table to
handle it. So I'm trying to export it as PDF report using RS.
So I have several formulas that rely on the Analysis Services functionalliy
(aggregates, ranking, etc.) that are already in the cube.
I know I can write the MDX statement to recreate them, but this will mean to
re-create all the calculated members once more in the statement.
Any suggestions?.
Thanks a lot for your response.

Query based parameter value changes back to default value

I have several parameters on my report, some have non-queried lists, and
some have queried lists. All of them have default values. When I ran
reports on the Report Manager, I changed the value of the parameters from
the drop-down list to produce different reports. My problem is whenever I
changed the value of a parameter(pA), the value of those queried-based
parameters that were listed after pA would switch back to their respective
default values. This didn't happen when I previewed reports on Visual
Studio .Net Development Environment. Is this a bug, limitation, or am I
missing something?
Any help would be appreciated.
Paul
--
Message posted via http://www.sqlmonster.comPaul,
It sounds like your default values are calculated values (begin with a '=').
The way that RS computes these is that whenever you change a parameter, it
recalculates all the calculated parameters after it. This behaviour is to
allow cascading parameters. As far as I know, there is no way to turn this
calculation off. However, I worked around it by creating another dataset
which had a column for each default value (I do a lot of default dates). You
can then set the default values for the parameters to the dataset value in
the parameters window. I have to use SQL to do the calculations, but for
most things, this should be an easy workaround. It gets rid of the changing
values and also the refresh that takes place when you change calculated
parameters
"Paul via SQLMonster.com" wrote:
> I have several parameters on my report, some have non-queried lists, and
> some have queried lists. All of them have default values. When I ran
> reports on the Report Manager, I changed the value of the parameters from
> the drop-down list to produce different reports. My problem is whenever I
> changed the value of a parameter(pA), the value of those queried-based
> parameters that were listed after pA would switch back to their respective
> default values. This didn't happen when I previewed reports on Visual
> Studio .Net Development Environment. Is this a bug, limitation, or am I
> missing something?
> Any help would be appreciated.
> Paul
> --
> Message posted via http://www.sqlmonster.com
>|||Hi David, that works great. Thank you very much!!
Paul
--
Message posted via http://www.sqlmonster.com

Query based parameter and web service call

has anyone seen this before?
I have a developer that has built a report that has a query based parameter.
Since they want to be able to run the report both interactively and through
a .net batch job. They are attempting to do a soap call to the report
passing a string value in as the query based parameters value. They are
getting a soap exception error .SoapException: Default value or value
provided for the report parameter 'PROD_DESC' is not a valid value. -->
Has anyone seen this before or can verify that you can pass string values
into query based parameters?
Thank YOu!!!We figured out the issue. The developer had a trailing space in his code so
the values in the string were not matching the values returned by the query.
"Mark" wrote:
> has anyone seen this before?
> I have a developer that has built a report that has a query based parameter.
> Since they want to be able to run the report both interactively and through
> a .net batch job. They are attempting to do a soap call to the report
> passing a string value in as the query based parameters value. They are
> getting a soap exception error .SoapException: Default value or value
> provided for the report parameter 'PROD_DESC' is not a valid value. -->
> Has anyone seen this before or can verify that you can pass string values
> into query based parameters?
> Thank YOu!!!

query at runtime

Hi all,
Is there way in reporting service programming to give the sql select query
for a report during runtime.
Thanks
rsuserYou can have your query be an expression. Be in the generic query designer
(two panes). The button to switch to this is to the right of the ...
Put in an expression.
="select somefield, someotherfield from mytable where startdate>= '" &
parameters!ParamName & "' order by " & parameters!AnotherParam
Note that you have to put your own single quotes where appropriate. What I
do is first have a report with just a textbox with the expression so I can
see it and see if I have created the query appropriately. Then I assign it
to a dataset.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"RSUser" <RSUser@.discussions.microsoft.com> wrote in message
news:347143A5-6B67-4CE3-BFBD-2E90F8E53193@.microsoft.com...
> Hi all,
> Is there way in reporting service programming to give the sql select query
> for a report during runtime.
> Thanks
> rsuser|||Just adding to Bruce's description:
Once you are using an expression-based command-text, you can no longer
execute the query in the report designer data view. Also you can no longer
refresh the fields list. That's why it is important to first finish the
report design based on a constant command text and use Bruce's suggested
approach of a textbox to look at the expression results - before you change
the command text to be expression-based.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:O%23r4y5%23rFHA.304@.TK2MSFTNGP11.phx.gbl...
> You can have your query be an expression. Be in the generic query designer
> (two panes). The button to switch to this is to the right of the ...
> Put in an expression.
> ="select somefield, someotherfield from mytable where startdate>= '" &
> parameters!ParamName & "' order by " & parameters!AnotherParam
> Note that you have to put your own single quotes where appropriate. What I
> do is first have a report with just a textbox with the expression so I can
> see it and see if I have created the query appropriately. Then I assign it
> to a dataset.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "RSUser" <RSUser@.discussions.microsoft.com> wrote in message
> news:347143A5-6B67-4CE3-BFBD-2E90F8E53193@.microsoft.com...
>> Hi all,
>> Is there way in reporting service programming to give the sql select
>> query
>> for a report during runtime.
>> Thanks
>> rsuser
>|||thanks!! I got it working. I have other issues. Please help me on these
1)Is there a way to supress the image when the report is
displayed in browser and then make them available for printing on paper
alone? is there a way to use any javascript or any other way to do this?
2)Say i have a dot net application which will display reports using
webservices and reports are using custom assembly which requires read
permission. Then while installing the dot net application in the
end users system, how the policy files will be configured. is there a way to
set up client's report server policy files through dot net code?
Thanks,
rsuser
"Robert Bruckner [MSFT]" wrote:
> Just adding to Bruce's description:
> Once you are using an expression-based command-text, you can no longer
> execute the query in the report designer data view. Also you can no longer
> refresh the fields list. That's why it is important to first finish the
> report design based on a constant command text and use Bruce's suggested
> approach of a textbox to look at the expression results - before you change
> the command text to be expression-based.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:O%23r4y5%23rFHA.304@.TK2MSFTNGP11.phx.gbl...
> > You can have your query be an expression. Be in the generic query designer
> > (two panes). The button to switch to this is to the right of the ...
> >
> > Put in an expression.
> >
> > ="select somefield, someotherfield from mytable where startdate>= '" &
> > parameters!ParamName & "' order by " & parameters!AnotherParam
> >
> > Note that you have to put your own single quotes where appropriate. What I
> > do is first have a report with just a textbox with the expression so I can
> > see it and see if I have created the query appropriately. Then I assign it
> > to a dataset.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "RSUser" <RSUser@.discussions.microsoft.com> wrote in message
> > news:347143A5-6B67-4CE3-BFBD-2E90F8E53193@.microsoft.com...
> >> Hi all,
> >>
> >> Is there way in reporting service programming to give the sql select
> >> query
> >> for a report during runtime.
> >>
> >> Thanks
> >> rsuser
> >
> >
>
>