One Date field with different dates

I want to search on a date field with user input. How can I make it to where
it will give user a startdate and an enddate box? Remember I have only one
date field.
SELECT COURSE_NBR, ATTEND, COMPL_DATE
FROM EH_MEMINFO_1
WHERE (COURSE_NBR LIKE @.COURSE_NBR) or
(COMPL_DATE LIKE @.COMPL_DATE)
Thanks fo ryour help.Use this:
SELECT COURSE_NBR, ATTEND, COMPL_DATE
FROM EH_MEMINFO_1
WHERE (COURSE_NBR LIKE @.COURSE_NBR) or
(COMPL_DATE BETWEEN @.StartDate AND @.EndDate)
"Shan" wrote:
> I want to search on a date field with user input. How can I make it to where
> it will give user a startdate and an enddate box? Remember I have only one
> date field.
> SELECT COURSE_NBR, ATTEND, COMPL_DATE
> FROM EH_MEMINFO_1
> WHERE (COURSE_NBR LIKE @.COURSE_NBR) or
> (COMPL_DATE LIKE @.COMPL_DATE)
> Thanks fo ryour help.|||O Thank you soo much...that solved lots of problems.
Great Daw!
"daw" wrote:
> Use this:
> SELECT COURSE_NBR, ATTEND, COMPL_DATE
> FROM EH_MEMINFO_1
> WHERE (COURSE_NBR LIKE @.COURSE_NBR) or
> (COMPL_DATE BETWEEN @.StartDate AND @.EndDate)
>
> "Shan" wrote:
> > I want to search on a date field with user input. How can I make it to where
> > it will give user a startdate and an enddate box? Remember I have only one
> > date field.
> >
> > SELECT COURSE_NBR, ATTEND, COMPL_DATE
> > FROM EH_MEMINFO_1
> > WHERE (COURSE_NBR LIKE @.COURSE_NBR) or
> > (COMPL_DATE LIKE @.COMPL_DATE)
> >
> > Thanks fo ryour help.

OLE DB Source Error Output

In the Input and Output Properties tab under Advance Editor for OLE DB Source, I cannot remove columns. I copied this Source from a standard template and have made the normal changes to make it work. However I keep getting this error...

Error: 0xC020837B at Load Server Security, OLE DB Source [1]: The output column "DBName" (1632) on the error output has no corresponding output column on the non-error output.

Error: 0xC004706B at Load Server Security, DTS.Pipeline: "component "OLE DB Source" (1)" failed validation and returned validation status "VS_ISBROKEN".

DBName of course is one of the columns that no longer exist, but I can't remove. Whenever I try to remove one of the columns, I get this error...

Error at Load Server Security [OLE DB Source[1]]: The column cannot be deleted. The component does not allow columns to be deleted from this input or output. Is there anything that I can do to remove the columns? Is there just a simple setting that I can change to make this work?
If you just double click on the OLE DB source, you can choose "Columns" on the left hand side and unselect the columns you do not want in the data flow.|||There is a problem with that though because I can't make any changes within the source. I always get the error...

Invalid object name 'tempdb.dbo.Server_Roles. This is because the table isn't created yet. It gets created when the connection manager connects to each server. It is a temporary table. Thus, it won't let me make any changes that I can't make in advance editor.

-Kyle
|||

Why don't you create the table somewhere else, get it configured properly, then change the table name afterward?

|||

That doesn't work. Currently I created a table in the temp directory and pointed the connection manager towards that DB and server. I still can't click on column and I can't change anything unless I hit cancel. My guess is that it may not be connecting to the server and DB correctly. I don't get any explanation for the error, its just one of the H errors.

-Kyle

|||I guess I don't understand what you are trying to do. Please explain further with more detail.

Ole DB Multi value named parameters problem.

I'm trying to use cascading report parameters using the mult-value select option. The first parameter is used as input for second parameter.

The first dataset called Employee,
select employeeid from humanresources.employee

Using SQL Server you can use multi named parameters to create your second dataset. For example,
select employeeid, loginid from humanresources.employee where employeeid
in (@.Employee)

However, I'm sadly not using sql server. I'm trying to do the same thing using an OleDB (non SQL Server) interface which doesn't support named parameters. So I used an expression. For example,

="select employeeid, loginid from humanresources.employee where employeeid
in (" & Parameters!Employee.value &")"
This works in Reporting Services if you do not have multi-value select option checked. Once you check the multi-value select option I get an error creating the second data set.
"Cannot set the command text, Error during process of command text of expression"
How do I do multi-value expressions.

Data providers do not support multi value parameters natively. Consequently, we have chosen to implement query statement rewrite for the managed SQL and the managed Oracle provider. Multi value parameters are also supported for AS 2005 data sources.

The OleDB standard in particular only allows single value parameters. It is not possible to write a "generic" multi value parameter query rewrite algorithm that works with any database server behind an arbitrary OleDB provider.

If you need multi value parameter functionality for a specific OleDB provider, you could write a custom RS data extensions which implements in particular the Microsoft.ReportingServices.DataProcessing.IDataMultiValueParameter interface. Then you could implement the query rewrite yourself in a way that is understood by your target database server.

Hope this helps,
Robert

|||Thanks for the education on Ole DB Providers. The testing I did validates what you have told me.

I'm tyring to do a work around. Is it possible to create a data set expression when multi-value parameters is selected. So I could do an expression query similar to this where I concatanate everything into the in portion of the query?
="select employeeid, loginid from humanresources.employee where employeeid
in (" & Parameters!Employee.value &")"

I believe Reporting Services would construct the query and would therefor the OleDB provider would not have to support multiple parameters. Thus a work around.

Todd|||If you change a report parameter to be multi value, the .Value property will return an object[] rather than an object. Hence you cannot just use regular string concatenation.

In your particular case, you may want to try the following expression:
="select employeeid, loginid from humanresources.employee where employeeid in (" & Join(Parameters!Employee.value, ", ") & ")"

Below is more information about using multi value parameters in reports. To access individual values of a multi value parameter you can use expressions like this:
=Parameters!MVP1.IsMultiValue
boolean flag - tells if a parameter is defined as multi value
=Parameters!MVP1.Count
returns the number of values in the array
=Parameters!MVP1.Value(0)
returns the first selected value
=Join(Parameters!MVP1.Value)
creates a space separated list of values
=Join(Parameters!MVP1.Value, ", ")
creates a comma separated list of values
=Split("a b c", " ")
to create a multi value object array from a string (this can be used e.g. for drillthrough parameters, subreports, or query parameters)

See also MSDN:
* http://msdn.microsoft.com/library/en-us/vblr7/html/vafctjoin.asp
* http://msdn.microsoft.com/library/en-us/vbenlr98/html/vafctsplit.asp

-- Robert

OLe DB Command and Using the value of an input column more than once

Hi there,

I have an OLE DB Command which updates a table. However, the command needs to use the value of an input column more than once.

For example I want to update TableA only if either ColumnA or ColumnB have actually changed: -

update tableA

set columnA = ?,

column B = ?

where columnC = ?

AND (columnA != ? OR columnB != ?)

I can't map the Input column to more than one parameter so I've been forced to create a copy of columnA and ColumnB as input columns so I can map to the extra paramters that the Command shape expects.

I also attempted to modify the command syntax so it set up variables for the 3 values required and then set the values to parameters - but I get a very unhelpful syntax error message: -

declare @.ValueA varchar(50),

@.ValueB varchar(50),

@.ValueC varchar(50)

select @.ValueA = ?,

@.ValueB = ?,

@.ValueC = ?,

update tableA

set columnA = @.ValueA ,

column B = @.ValueB

where columnC = @.ValueC

AND (columnA != @.ValueA OR columnB != @.ValueB)

Any suggestions?

anydobbo wrote:

Hi there,

I have an OLE DB Command which updates a table. However, the command needs to use the value of an input column more than once.

For example I want to update TableA only if either ColumnA or ColumnB have actually changed: -

update tableA

set columnA = ?,

column B = ?

where columnC = ?

AND (columnA != ? OR columnB != ?)

I can't map the Input column to more than one parameter so I've been forced to create a copy of columnA and ColumnB as input columns so I can map to the extra paramters that the Command shape expects.

Yeah, that's what you have to do!

anydobbo wrote:

I also attempted to modify the command syntax so it set up variables for the 3 values required and then set the values to parameters - but I get a very unhelpful syntax error message: -

declare @.ValueA varchar(50),

@.ValueB varchar(50),

@.ValueC varchar(50)

select @.ValueA = ?,

@.ValueB = ?,

@.ValueC = ?,

update tableA

set columnA = @.ValueA ,

column B = @.ValueB

where columnC = @.ValueC

AND (columnA != @.ValueA OR columnB != @.ValueB)

Any suggestions?

I may be wrong but I don't think you can do that. The OLE DB Command accepts a DML statement but not a statement block (as far as I know).

-Jamie

|||Thanks Jamie