number range in the IN statement

Can anyone tell me what is wrong with this?
Month({Command.Date_Received}) IN [({?ParamMValue})]
?ParamMValue could have... (2,3,4,5) or (2) or (1,2,3,4,5,6,7,8,9)
Why is it telling me that I need a number range in the IN statement?
What should I do instead?Make sure your parameter is a number data type and not a date or string.
GJ|||your final formula should be in format like this..

month({field}) in [ parameter ] no need to you [(2,3,5)]..


Hope this helps yousql

NullReferenceException when using data-driven subscription with custom data extension

Hello,
I am trying to set up a data-driven subscription, where the command
that returns the list of recipients is run against a custom data
provider extension. However, when I validate the query (on step 3 of
the wizard), I always get the error rsCannotPrepareQuery, with the
message "Object reference not set to an instance of an object." When I
step into the code, the exception is being thrown inside the following
stack trace:
Microsoft.ReportingServices.Library.SubscriptionManager.PrepareQuery()
Microsoft.ReportingServices.Library.RSService.PrepareQuery()
Microsoft.ReportingServices.WebServer.ReportingService.PrepareQuery()
System.Web.Services.Protocols.LogicalMethodInfo.Invoke()
...
The exception is thrown right after IDbConnection.Open() returns, which
is immediately before IDbConnection.CreateCommand() is called.
Everywhere else, my custom data provider extension works great. And it
doesn't matter what query or credentials I type it - the problem always
exists.
I am running MS RS 2000 SP1.
Thanks in advance.Which interfaces do you implement: RS.IDbConnection, etc or
System.Data.IDbConnection?
--
Alex Mineev
Software Design Engineer. Report expressions; Code Access Security; Xml;
SQE.
This posting is provided "AS IS" with no warranties, and confers no rights
<bigredgum1@.excite.com> wrote in message
news:1107886911.665093.180630@.c13g2000cwb.googlegroups.com...
> Hello,
> I am trying to set up a data-driven subscription, where the command
> that returns the list of recipients is run against a custom data
> provider extension. However, when I validate the query (on step 3 of
> the wizard), I always get the error rsCannotPrepareQuery, with the
> message "Object reference not set to an instance of an object." When I
> step into the code, the exception is being thrown inside the following
> stack trace:
> Microsoft.ReportingServices.Library.SubscriptionManager.PrepareQuery()
> Microsoft.ReportingServices.Library.RSService.PrepareQuery()
> Microsoft.ReportingServices.WebServer.ReportingService.PrepareQuery()
> System.Web.Services.Protocols.LogicalMethodInfo.Invoke()
> ...
> The exception is thrown right after IDbConnection.Open() returns, which
> is immediately before IDbConnection.CreateCommand() is called.
> Everywhere else, my custom data provider extension works great. And it
> doesn't matter what query or credentials I type it - the problem always
> exists.
> I am running MS RS 2000 SP1.
> Thanks in advance.
>|||My connection class implements
Microsoft.ReportingServices.DataProcessing.IDbConnection.
I am basically just using the code provided in the Microsoft article
"Using an ADO.NET DataSet as a Reporting Services Data Source."
Thanks for your help.|||I need more info. Could you please repro it once again and send me recent rs
logs?
thanks
--
Alex Mineev
Software Design Engineer. Report expressions; Code Access Security; Xml;
SQE.
This posting is provided "AS IS" with no warranties, and confers no rights
<bigredgum1@.excite.com> wrote in message
news:1108048910.737444.302770@.f14g2000cwb.googlegroups.com...
> My connection class implements
> Microsoft.ReportingServices.DataProcessing.IDbConnection.
> I am basically just using the code provided in the Microsoft article
> "Using an ADO.NET DataSet as a Reporting Services Data Source."
> Thanks for your help.
>|||Sure. To reproduce, follow these steps:
- Set up the custom data extension as described in the article "Using
an ADO.NET DataSet as a Reporting Services Data Source".
- Go to the Report Manager web site and view any report.
- Click on the Subscriptions tab.
- Click the New Data-driven Subscription button.
- On page 1, enter any description and delivery provider. Select
"Specify for this subscription only". Click Next.
- On page 2, select your custom data extension. Enter any connection
string. Select "Credentials stored securely in the report server" and
enter any user name and password. Click Next.
- On page 3, enter any text and click Validate. The error
rsCannotPrepareQuery and text "Object reference not set to an instance
of an object" is displayed.
(Incidentally, if you choose "Credentials are not required" on page 2,
then on page 3 when you attempt to validate your query, you will get
error rsInvalidDataSourceCredentialSetting. This appears to be another
bug or design mistake. If my custom data extension does not require
credentials (e.g. it obtains them from a configuration file), then
there is no reason I should have to enter them in the UI. This just
ends up being confusing for end users to enter meaningless
credentials.)
When I click the Validate button on step 3 of the data-driven
subscription wizard, the following is appended to the ReportServer log
(I cranked up logging to Verbose):
aspnet_wp!runningrequests!15c4!02/10/2005-14:42:53:: v VERBOSE: User
map'<Users><User><Name>Person
1</Name><Paths><Path>http://mymachine/ReportServer/reportservice.asmx</Path><NrReq>1</NrReq></Paths></User></Users>'
aspnet_wp!runningrequests!15c4!02/10/2005-14:42:53:: v VERBOSE:
SoapAction:
"http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices/GetSystemProperties"
aspnet_wp!library!15c4!02/10/2005-14:42:53:: v VERBOSE: Call to
GetSystemProperties()
aspnet_wp!library!15c4!02/10/2005-14:42:53:: v VERBOSE: Call to
GetSystemProperties completed.
aspnet_wp!runningrequests!17fc!02/10/2005-14:45:07:: v VERBOSE: User
map'<Users><User><Name>Person
1</Name><Paths><Path>http://mymachine/ReportServer/reportservice.asmx</Path><NrReq>1</NrReq></Paths></User></Users>'
aspnet_wp!runningrequests!17fc!02/10/2005-14:45:07:: v VERBOSE:
SoapAction:
"http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices/ListSecureMethods"
aspnet_wp!runningrequests!17fc!02/10/2005-14:45:07:: v VERBOSE: User
map'<Users><User><Name>Person
1</Name><Paths><Path>http://mymachine/ReportServer/reportservice.asmx</Path><NrReq>1</NrReq></Paths></User></Users>'
aspnet_wp!runningrequests!17fc!02/10/2005-14:45:07:: v VERBOSE:
SoapAction:
"http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices/GetSystemPermissions"
aspnet_wp!library!17fc!02/10/2005-14:45:07:: i INFO: Call to
GetSystemPermissions
aspnet_wp!runningrequests!17fc!02/10/2005-14:45:07:: v VERBOSE: User
map'<Users><User><Name>Person
1</Name><Paths><Path>http://mymachine/ReportServer/reportservice.asmx</Path><NrReq>1</NrReq></Paths></User></Users>'
aspnet_wp!runningrequests!17fc!02/10/2005-14:45:07:: v VERBOSE:
SoapAction:
"http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices/ListChildren"
aspnet_wp!library!17fc!02/10/2005-14:45:07:: v VERBOSE: Call to
ListChildren( '/', False )
aspnet_wp!library!17fc!02/10/2005-14:45:07:: v VERBOSE: Call to
ListChildren completed
aspnet_wp!runningrequests!17fc!02/10/2005-14:45:07:: v VERBOSE: User
map'<Users><User><Name>Person
1</Name><Paths><Path>http://mymachine/ReportServer/reportservice.asmx</Path><NrReq>1</NrReq></Paths></User></Users>'
aspnet_wp!runningrequests!17fc!02/10/2005-14:45:07:: v VERBOSE:
SoapAction:
"http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices/ListExtensions"
aspnet_wp!library!17fc!02/10/2005-14:45:07:: v VERBOSE: Call to
ListProviders: type (Data).
aspnet_wp!library!17fc!02/10/2005-14:45:07:: v VERBOSE: Call to
ListProviders completed.
aspnet_wp!runningrequests!17fc!02/10/2005-14:45:07:: v VERBOSE: User
map'<Users><User><Name>Person
1</Name><Paths><Path>http://mymachine/ReportServer/reportservice.asmx</Path><NrReq>1</NrReq></Paths></User></Users>'
aspnet_wp!runningrequests!17fc!02/10/2005-14:45:07:: v VERBOSE:
SoapAction:
"http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices/PrepareQuery"
aspnet_wp!library!17fc!02/10/2005-14:45:07:: v VERBOSE: Call to
PrepareQuery: dataSource
(<DataSource><DataSourceDefinition><Extension>My
DataSet</Extension><ConnectString>asdf</ConnectString><CredentialRetrieval>Store</CredentialRetrieval><WindowsCredentials>False</WindowsCredentials><ImpersonateUser>False</ImpersonateUser><UserName>user</UserName><Password>test</Password></DataSourceDefinition></DataSource>),
dataSet(<DataSet><Query><CommandType>Text</CommandType><CommandText>asdfasdf</CommandText></Query></DataSet>).
aspnet_wp!crypto!17fc!02/10/2005-14:45:07:: v VERBOSE: Starting crypto
operation DBProtectString
aspnet_wp!crypto!17fc!02/10/2005-14:45:07:: v VERBOSE: Completed crypto
operation DBProtectString
aspnet_wp!crypto!17fc!02/10/2005-14:45:07:: v VERBOSE: Starting crypto
operation DBProtectString
aspnet_wp!crypto!17fc!02/10/2005-14:45:07:: v VERBOSE: Completed crypto
operation DBProtectString
aspnet_wp!crypto!17fc!02/10/2005-14:45:07:: v VERBOSE: Starting crypto
operation DBProtectString
aspnet_wp!crypto!17fc!02/10/2005-14:45:07:: v VERBOSE: Completed crypto
operation DBProtectString
aspnet_wp!crypto!17fc!02/10/2005-14:45:07:: v VERBOSE: Starting crypto
operation DBUnProtectString
aspnet_wp!crypto!17fc!02/10/2005-14:45:07:: v VERBOSE: Completed crypto
operation DBUnProtectString
aspnet_wp!crypto!17fc!02/10/2005-14:45:07:: v VERBOSE: Starting crypto
operation DBUnProtectString
aspnet_wp!crypto!17fc!02/10/2005-14:45:07:: v VERBOSE: Completed crypto
operation DBUnProtectString
aspnet_wp!crypto!17fc!02/10/2005-14:45:07:: v VERBOSE: Starting crypto
operation DBUnProtectString
aspnet_wp!crypto!17fc!02/10/2005-14:45:07:: v VERBOSE: Completed crypto
operation DBUnProtectString
aspnet_wp!processing!17fc!02/10/2005-14:45:07:: v VERBOSE: A connection
object for the My DataSet data source has been created.
aspnet_wp!crypto!17fc!02/10/2005-14:45:07:: v VERBOSE: Starting crypto
operation DBUnProtectString
aspnet_wp!crypto!17fc!02/10/2005-14:45:07:: v VERBOSE: Completed crypto
operation DBUnProtectString
aspnet_wp!library!17fc!02/10/2005-14:45:07:: 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.NullReferenceException: Object reference not set to an instance
of an object.
at
Microsoft.ReportingServices.Library.SubscriptionManager.PrepareQuery(DataSource
dataSource, DataSetDefinition dataSet, Boolean& changed)
-- End of inner exception stack trace --
aspnet_wp!runningrequests!17fc!02/10/2005-14:45:07:: v VERBOSE: User
map'<Users><User><Name>Person
1</Name><Paths><Path>http://mymachine/ReportServer/reportservice.asmx</Path><NrReq>1</NrReq></Paths></User></Users>'
aspnet_wp!runningrequests!17fc!02/10/2005-14:45:07:: v VERBOSE:
SoapAction:
"http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices/GetSystemProperties"
aspnet_wp!library!17fc!02/10/2005-14:45:07:: v VERBOSE: Call to
GetSystemProperties()
aspnet_wp!library!17fc!02/10/2005-14:45:07:: v VERBOSE: Call to
GetSystemProperties completed.
No changes are made to the ReportServerService or ReportServerWebApp
logs. No other errors are listed in any of these logs.
For reference, here is my connection class:
using System;
using System.Data;
using System.Configuration;
using System.Xml;
using Microsoft.ReportingServices.DataProcessing;
namespace My.Samples.DataSetProcessingExtension
{
public class DSXConnection
: Microsoft.ReportingServices.DataProcessing.IDbConnection
{
private string _connString;
private int _connTimeout = 15;
private ConnectionState _state = ConnectionState.Closed;
public DSXConnection() {}
public DSXConnection(string connString) { _connString = connString; }
public string ConnectionString { get { return _connString; } set {
_connString = value; } }
public int ConnectionTimeout { get { return _connTimeout; } }
public ConnectionState State { get { return _state; } }
public Microsoft.ReportingServices.DataProcessing.IDbTransaction
BeginTransaction()
{
return null;
}
public void Open()
{
_state = ConnectionState.Open;
}
public void Close()
{
this.Dispose();
_state = ConnectionState.Closed;
}
// Implemented.
public Microsoft.ReportingServices.DataProcessing.IDbCommand
CreateCommand()
{
return new DSXCommand(this);
}
public string LocalizedName
{
get { return "DataSet Data Processing Extension"; }
}
public void SetConfiguration(string configuration) {}
public void Dispose() {}
}
}
I also have forms authentication and a custom security extension set
up, which works fine as far as I can tell. I don't know if that would
have any bearing on this issue, but I thought I'd mention it anyways.
Hope that helps. If you need more information feel free to post here or
e-mail me.

Null Values in Destination

Hi to all!

I created a simple package – data flow task, containing OLE DB Source, with Data Access Mode – SQL command and OLE DB Destination.

My SQL command is

update support_incident

set date_closed = rn_edit_date

where status_text = 'Closed' and date_closed is null

DECLARE @.CMonth as datetime

DECLARE @.LMonth as datetime

SET @.CMonth = '10/1/2006'

SET @.LMonth = DateAdd (m, -1, @.CMonth)

select

@.LMonth as Reporting_Date,

(select count(support_incident_id) from support_Incident where rn_create_date >= @.LMonth and rn_create_date < @.CMonth) as Opened,

(select count(support_incident_id) from support_Incident where Date_Closed >= @.LMonth and Date_Closed < @.CMonth) as Closed,

(select count(support_incident_id) from support_Incident where rn_create_date < @.CMonth and (Date_Closed >= @.CMonth or Date_Closed is null)) as Backlog,

(select count(support_incident_id) from support_Incident where priority in (0,1) and (rn_create_date < Dateadd(d,-3,@.CMonth)) and support_incident_id in (select support_incident_id from support_Incident where rn_create_date < @.CMonth and (Date_Closed >= @.CMonth or Date_Closed is null))) as Critical_Backlog,

((select count(support_incident_id) from support_Incident where rn_create_date < @.CMonth and (Date_Closed >= @.CMonth or Date_Closed is null)) - (select count(support_incident_id) from support_Incident where priority in (0,1) and (rn_create_date < Dateadd(d,-3,@.CMonth)) and support_incident_id in (select support_incident_id from support_Incident where rn_create_date < @.CMonth and (Date_Closed >= @.CMonth or Date_Closed is null)))) as Operational_Backlog

from support_incident

where support_incident_code = '1000'

The SQL statement is successfully parsed and I can see the columns and numbers in Preview Query Results. The package is executed without errors and warnings with success. In Destination server, the table is created with columns according to query, but the problem is no numbers are written in rows, all values are nulls.

I can’t figure out what is the reason. Can anybody to help me?

Thank you.

First avenue of investigation would be to use a data viewer to see if any data enters the pipeline (and what that data is).

-Jamie

|||

Thank you Jamie for your respond. Created data viewers as you suggested, and saw that at OLE DB Source Output Data Viewer appears : Total rows: 0, buffers:0.

So, although OLE DB Source is "successful'', I need to search another approach of sending the data. May be the reason in SQL statement?

Anybody had such problems?

Thank you.

|||Have you tried wrapping this script code in a stored procedure? I suspect that the nested select statement return columns could be confusing SSIS. It's possible that SSIS could detect the return columns differently when the code is in a stored procedure.

I don't know if it will fix the problem or not. However, I have a lot of code like this in stored procedures that I'm calling from SSIS without any problems.

Thanks,
Greg Van Mullem|||Here is another idea. Try putting this command at the top of your script. This command fixes a lot of odd problems in SQL 2000 DTS. Definetly worth a try here too.

SET NOCOUNT ON;

Thanks,
Greg Van Mullem|||

Thank you Greg.

No, I did not try. I do not have much experience with stored procedures, only college projects practicing.

Now I am going to read MS SQL Server 2005 - how to create stored procedures - make this script to run monthly. Actually, I have to create and "automate" Crystal Reports using this monthly information. So, I need to change

SET @.CMonth = '10/1/2006'

in the procedure in such a way it would run monthly.

Thanks once again to all.

|||

Now, I received an Error Message and My OLE DB Source is red. I am afraid, I cannot use this statement. My Error message:

SSIS package "PivSupportmonthly.dtsx" starting.

Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.

Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.

Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.

Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.

Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.

Error: 0xC020901C at Data Flow Task, PivSupportIncident [1]: There was an error with output column "Reporting_Date" (625) on output "OLE DB Source Output" (11). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.".

Error: 0xC020902A at Data Flow Task, PivSupportIncident [1]: The "output column "Reporting_Date" (625)" failed because truncation occurred, and the truncation row disposition on "output column "Reporting_Date" (625)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: The PrimeOutput method on component "PivSupportIncident" (1) returned error code 0xC020902A.The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.

Error: 0xC0047039 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.

Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039.

Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.

Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.

Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "OLE DB Destination" (1657)" wrote 0 rows.

Task failed: Data Flow Task

Warning: 0x80019002 at PivSupportmonthly: The Execution method succeeded, but the number of errors raised (6) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "PivSupportmonthly.dtsx" finished: Failure.

Thanks Greg for your following.

|||

"Text was truncated or "

That's your error, right there. Something is getting truncated somewhere. You are trying to push a string value into somthing that isn't big enough for it.

-Jamie

|||

You are right, Jamie. I changed the size of the default VARCHAR and it is successful. But there are no inserting data in the table:

SSIS package "PivSupportmonthly.dtsx" starting.

Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.

Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.

Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.

Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.

Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.

Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.

Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.

Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "OLE DB Destination" (1657)" wrote 0 rows.

SSIS package "PivSupportmonthly.dtsx" finished: Success.

Thanks once again.

|||I think the issue might be with your query - although it may execute fine in Query Analyser - in SSIS source query - first statement should be SELECT (even if you have Stored Proc) - and in your case its UPDATE & then SET & finally SELECT|||So try a simple SELECT statement in the query to see if data passes through|||

Thanks to all your suggestions, I could run successfully my package. I changed default type VARCHAR to DATETIME and used SET NOCOUNT ON. Thanks once again.

Now, I have to make the package would run monthly. Where can I find information about how to schedule?

Thank you very much.

|||

Corres wrote:

Now, I have to make the package would run monthly. Where can I find information about how to schedule?

Use SQL Server's built-in scheduler (aka SQL Server Agent)

-Jamie

|||Thanks Jamie.