Hello all,
I'm not sure if what I'm encountering is a bug, an intended feature, or user error...
I've got two tables set up to assist with some data driven reports we want to run. There are two tables listed below:
CREATE TABLE [dbo].[MS_REPORT_SUBSCRIPTION](
[ID] [int] NOT NULL,
[REPORT_NAME] [varchar](30) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,[REPORT_DESCRIPTION] [varchar](60) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL)
This table holds an instance of an intended data driven subscription report.
CREATE TABLE [dbo].[MS_REPORT_PARAMETERS](
[ID] [int] NOT NULL,
[REPORT_ID] [int] NOT NULL,
[REPORT_PARAMETERS] [ntext] COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL)
This table will hold the report parameters. The REPORT_PARAMETERS column holds an xml string (formatted the same as in the Reporting Services database. This will allow a single instance to have a variable number of parameters.
This is the query I use to get the information:
SELECT
convert(xml, MRP.REPORT_PARAMETERS).value('(/ParameterValues/ParameterValue/Value)[1]', 'int') SupervisorID,
'\\sxcorp1\temp\dmlenz\is_docs\' + convert(xml, MRP.REPORT_PARAMETERS).value('(/ParameterValues/ParameterValue/Value)[2]', 'varchar(255)') FilePath,
convert(xml, MRP.REPORT_PARAMETERS).value('(/ParameterValues/ParameterValue/Value)[3]', 'varchar(3)') Department
FROM DW_DIMENSION..MS_REPORT_SUBSCRIPTION MRS
JOIN DW_DIMENSION..MS_REPORT_PARAMETERS MRP ON MRS.ID = MRP.REPORT_ID
WHERE MRS.ID = 1
This works great when I run it within Management Studio. The problem happens when I try to use it for my data driven query. I get the error below. I guess it doesn't know how to parse it? I'm not sure why this happens since the database doesn't have a problem with it (or does it - see exception below). This is the error I get in Report Manager:
The dataset cannot be generated. An error occurred while connecting to a data source, or the query is not valid for the data source. (rsCannotPrepareQuery) Get Online Help
| For more information about this error navigate to the report server on the local server machine, or enable remote errors |
I looked in the logs and found the exception below. Does anyone have an idea as to why this is happening? I do see the 'ARITHABORT' part below, but am not sure if this is something that just comes back from the exception or if it is truly set incorrectly. If the latter is the case, then is this something that Management Studio sets correctly? Anyone have any idea what those settings are? I guess I'm trying to understand why Mgmt Studio would work but not Report Manager..
Thanks in advance for any insight you all might have.
Regards,
Dan
End of inner exception stack trace
w3wp!library!a!06/08/2006-10:17:57:: i INFO: Call to GetSystemPermissions
w3wp!library!d!06/08/2006-10:18:04:: i INFO: Call to GetSystemPermissions
w3wp!library!d!06/08/2006-10:18:04:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.CannotPrepareQueryException: The dataset cannot be generated. An error occurred while connecting to a data source, or the query is not valid for the data source., ;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.CannotPrepareQueryException: The dataset cannot be generated. An error occurred while connecting to a data source, or the query is not valid for the data source. > System.Data.SqlClient.SqlException: SELECT failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.ReportingServices.DataExtensions.SqlCommandWrapperExtension.ExecuteReader(CommandBehavior behavior)
at Microsoft.ReportingServices.Library.SubscriptionManager.PrepareQuery(DataSource dataSource, DataSetDefinition dataSet, ReportParameter[]& parameters, Boolean& changed)
End of inner exception stack trace
This link has some info:
http://msdn2.microsoft.com/en-us/library/ms188783.aspx
I found this bullet point interesting:
The SET option settings must be the same as those required for indexed views and computed column indexes. Specifically, the option ARITHABORT must be set to ON when an XML index is created and when inserting, deleting, or updating values in the
xml column. For more information, see
SET Options That Affect Results.|||
I've attempted many things up to this point. For some reason setting the options before the data driven query still doesn't get rid of the error message above.
I have no gone as far as to create a table valued function that will allow me to SELECT * FROM fn_function. This parses ok, but when I actually schedule & run the data driven subscription, I get the following error in the subscription status: Error: Cannot read the next data row for the data set . Looks like functions don't work quite right either.
Ryan: That article helped clear up a lot of information. Unfortunately I couldn't seem to get anything working yet. Thanks for the link.
If anyone else has any suggestions, I'd be willing to try anything at this point...
Regards,
Dan
|||
Alright. This is another response to my own question. The good news is, I've figured a way around the limitation.
First I tried this (using the set arithabort on option):
SET ARITHABORT on
SELECT convert(xml, MRP.REPORT_PARAMETERS).value('(/ParameterValues/ParameterValue/Value)[1]', 'int') SupervisorID,
'\\sxcorp1\temp\dmlenz\is_docs\' + convert(xml, MRP.REPORT_PARAMETERS).value('(/ParameterValues/ParameterValue/Value)[2]', 'varchar(255)') FilePath,
convert(xml, MRP.REPORT_PARAMETERS).value('(/ParameterValues/ParameterValue/Value)[3]', 'varchar(3)') Department
FROM DW_DIMENSION..MS_REPORT_SUBSCRIPTION MRS
JOIN DW_DIMENSION..MS_REPORT_PARAMETERS MRP ON MRS.ID = MRP.REPORT_ID
WHERE MRS.ID = 1
It didn't work. The next thing I tried is to create a proc that set the appropriate options. The Data Driven Subscription Engine didn't understand the proc. This was no longer an option.
I stated above that I couldn't get functions to work - this isn't completely true. The reason they didn't work for me was because, in the log files, I was getting the same exception as my original post. At this point, I figured it was a lost cause, however, I was able to set an option before the function sql. Not sure why this worked and the query above didn't.
This is the query that ultimately worked follows:
set ARITHABORT ON
SELECT SM.SupervisorID, '\\sxcorp1\temp\dmlenz\is_docs\zService Scorecards\' + SM.FilePath, SM.FName FileName, SM.Department FROM fn_DataDriven_IS_Service_Metrics(1) SM
I don't understand why setting the option before the xml query didn't work, but I suppose, as long as I got it to work...
I hope this will eventually help someone else.
Regards,
Dan
sql
