no case sensitive

Hi,
Does anybody know how to let the database treat my data as no case
sensitive?
For example: I have a customer type field, when I retrieve it from the
mainframe, it has "retail" or "RETAIL" as differently typed by users. But
when I run a report for customer type "Retail", I want to them to be showing
as one type on the report.
How do I do this?
Thanks,
SarahUse the lower function to convert the column to lowercase.
i.e.
Select *
from Orders
where lower(OrderType) = 'retail'
"Sarah G." <sguo@.coopervision.com> wrote in message
news:uMv8Wv#GEHA.1076@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Does anybody know how to let the database treat my data as no case
> sensitive?
> For example: I have a customer type field, when I retrieve it from the
> mainframe, it has "retail" or "RETAIL" as differently typed by users. But
> when I run a report for customer type "Retail", I want to them to be
showing
> as one type on the report.
> How do I do this?
> Thanks,
> Sarah
>|||Are you using SQL Server 2000? If so, look up the COLLATE keyword in BOL.
Here's a very simple example:
create table #a(mychar varchar(20) collate sql_latin1_general_cp1_cs_as)
go
insert #a values('aBc')
insert #a values('abC')
go
select distinct mychar from #a
go
select distinct mychar collate sql_latin1_general_cp1_ci_as from #a
go
"Sarah G." <sguo@.coopervision.com> wrote in message
news:uMv8Wv#GEHA.1076@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Does anybody know how to let the database treat my data as no case
> sensitive?
> For example: I have a customer type field, when I retrieve it from the
> mainframe, it has "retail" or "RETAIL" as differently typed by users. But
> when I run a report for customer type "Retail", I want to them to be
showing
> as one type on the report.
> How do I do this?
> Thanks,
> Sarah
>sql

no case sensitive

Hi,
Does anybody know how to let the database treat my data as no case
sensitive?
For example: I have a customer type field, when I retrieve it from the
mainframe, it has "retail" or "RETAIL" as differently typed by users. But
when I run a report for customer type "Retail", I want to them to be showing
as one type on the report.
How do I do this?
Thanks,
Sarah
Use the lower function to convert the column to lowercase.
i.e.
Select *
from Orders
where lower(OrderType) = 'retail'
"Sarah G." <sguo@.coopervision.com> wrote in message
news:uMv8Wv#GEHA.1076@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Does anybody know how to let the database treat my data as no case
> sensitive?
> For example: I have a customer type field, when I retrieve it from the
> mainframe, it has "retail" or "RETAIL" as differently typed by users. But
> when I run a report for customer type "Retail", I want to them to be
showing
> as one type on the report.
> How do I do this?
> Thanks,
> Sarah
>

No ADO type command for Data Flow?

I'm probably not looking in the right place, but all I could find when creating a data flow task was OLE DB Commands. I was trying to utilize a dataaccesslayer piece of code that we use every where in our projects, but because it uses ADO and not OLE DB, it caused an issue between the column data types.

Is there an ADO command object available? Or are we forced to use the OLE DB command object? All I was looking to do was to Execute a SQL command. There's an object on the Control Flow level to do that, but not on the Data Flow levelnot sure why that is.

Thanks,

Jeff Tolman
E&M Electric

Its because executing a stand-alone SQL statement isn't relevant for a data-flow. As you have observed you can do that in the Execute SQL Task.

What exactly is it that you want to do? What is your SQL statement?

-Jamie

|||

You would only use the data flow task if you wanted to process many rows of data - either aggregating them to pass them to your command, or issuing the command for every row that passes.

If you have existing code, you can likely acheive that using the Script Component - but be sure your scenario is appropriate first.

Can you describe some more about what you are trying to acheive?

Donald

|||

It's not necessarily a particular command. It's using ADO vs. OLE DB. Our DataAccessLayer code uses ADO, which I was able to import into the VSA scripting editor and that seemed to work just fine. However the output of the Script Component feeds records to separate SQL commands, but the only thing that I see that's available is the OLE DB Command. I know I could probably do this within the Script component, but it just seems to be more logically designed and layed out this way.

Thanks,

Jeff

|||

I have a set of records coming in to a Script Component and based on the data and lookups into the destination table I'm determining if data should be deleted, inserted or updated in the destination table. It's basically a table copy routine with a little intelligence for deletes.

Jeff

|||

JazzGeek wrote:

It's not necessarily a particular command. It's using ADO vs. OLE DB. Our DataAccessLayer code uses ADO, which I was able to import into the VSA scripting editor and that seemed to work just fine. However the output of the Script Component feeds records to separate SQL commands, but the only thing that I see that's available is the OLE DB Command. I know I could probably do this within the Script component, but it just seems to be more logically designed and layed out this way.

Thanks,

Jeff

Sorry, I'm really really confused. In SSIS you don't access data using the OLE DB Command component. You do it with a source adapter. There are many source adapters including one for OLE DB and one for ADO.

Are you saying that you want apply modifications using ADO rather than OLE DB?

-Jamie

|||

Hey Jamie,

I was just hoping to use our DataAccessLayer code library (which utilized ADO) to perform DB commands against our databases. I realize that with the OLE DB Command object you can execute a SQL command based on any of the input column data. Since I couldn't find an ADO Command object, I thought I'd use our DataAccessLayer library within a Script component to do it.

Thanks for the comments!

Jeff

NHibernate mapping

I'm using NHibernate with SQL Server Express Edition and I've been problems with the data type to be defined in the "type" attribute on the "property" tag in the XML file mapping.

Has someone worked with that?

I need to match the appropriate NHibernate type to the following SQL Server Types:

uniqueidentifier;bit;ntext;

I have been getting "type mismatch" error when the code is trying access the data.

Thanks a lot!

Hi oalexandrino,

Could you please post your detailed mapping schema here so we can better help you? Seems your problem is caused by uniqueidentifier. Anyway, I have read an article and it says you can configure your mapping file like this:

To map to a SQL Server instance, here is what your configuration settings might look like:

<configuration> <configSections><section name="nhibernate" type="System.Configuration.NameValueSectionHandler, System,Version=1.0.5000.0,Culture=neutral,PublicKeyToken=b77a5c561934e089" /> </configSections> <nhibernate><add key="hibernate.connection.provider" value="NHibernate.Connection.DriverConnectionProvider"/><add key="hibernate.dialect" value="NHibernate.Dialect.MsSql2000Dialect"/><add key="hibernate.connection.driver_class" value="NHibernate.Driver.SqlClientDriver" /><add key="hibernate.connection.connection_string" value="Server=localhost;initial catalog=nhibernate;UserID=someuser;Password=somepwd;Min Pool Size=2"/></nhibernate></configuration>

There are also some other steps you need to follow, see:http://www.theserverside.net/tt/articles/showarticle.tss?id=NHibernate It should help you solve your prolbem.Thanks

This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

|||

helloChen!

Firstly thanks by your answer.

So, in order to solve my problem I looked for something around internet to extract automatically this XML according to database schema.

Then I found thisSmartCode

http://www.codeproject.com/useritems/SmartCode-Code_Generation.asp

It is a useful tool because reading the tables in database and creates for each one an NHibernate mapping with all .NET types according with SQL types in the database.

That gave me what problem I was getting!

thanks!