Oracle.DataAccess.dll version mismatch...

In a recent project I was faced with this problem:
I had compiled a local asp.net mvc project pointed to my installed version of the Oracle.ataaccess.dll (from the GAC). Instead of a complete project deployment, I was supposed to just replace the main project dll in the server.

Upon deployment, I found that the version of the Oracle.Dataaccess.dll was not the same and i was getting a "Version Mismatch error", whenever there was a call from the site to the Oracle DB. The Ora Client version in my dev box was 12.1.1.0.
To add to it, the environments of the Dev and the Web server were also different. My local dev box was a 32bit Win 7 whereas the webserver was a 64bit Win Server 2008 R2.

So the first challenge was to know what is the installed version of the Oraclient in Server.
I could not locate the Oracle Client in the usual place on the server, i.e., C://Oraclient/Products/.
1. So I opened the Environment Variables of the server and from the Path Variable found the path to the Oraclient.
From there I got the Oraclient version.
2.  I opened the GAC of the machine to confirm the version
3. I downloaded the exsiting dll of the mvc project and dissembled it (using ILSpy) to see the referenced Oracle version.

I found that the used version on the server was 11.2.3.0
----------------------
Now that I know of the 2 versions, the next challenge was to fix the mismatch of the versions.
The first thing which came to my mind was to uninstall the local version, download and install the one on the server, recompile and the replace the dll.
But that was a cumbersome process, and I couldn't find the exact oraclient version on the net too.
So, I investigated aa little more... From the experience of a previous project, i knew that in the web.config we can instrument the assembly which we want to be loaded.

Then I found this website with the steps: it was not an exact same scenario, but quite close.
(http://tiredblogger.wordpress.com/2008/11/06/getting-oracledataaccess-working-on-x64/)

taking a cue from the website I added the following section to my web.config

<dependentAssembly>
       <assemblyIdentity name="Oracle.DataAccess" publicKeyToken="89b483f429c47342"/>
       <bindingRedirect oldVersion="0.0.0.0-4.121.1.0" newVersion="4.112.3.0"/>
</dependentAssembly>

and voila, after the deployment of the new dll, it was working fine....

ohh and BTW, I got the public key token of the installed oraclient version from the dissembled dll.

How to loop in Sql Server

Scenario: We have a Table : TableA

Structure of TableA:

Id                Name

-- ---------------------------

1           Pirate

2            Monkey

3            Ninja

4            Spaghetti

-------------------------------

 

Requirement: Iterate through the Names (“Name” column) of TableA

Solution:

We all know that the most convenient, easy and widely used solution is by using a Cursor.

DECLARE cursor1 CURSOR

         FOR SELECT Name FROM TableA

OPEN cursor1

       FETCH NEXT FROM cursor1

This works, but we should be aware of the disadvantages of using a Cursor…

Cursor implementation in application, helps data manipulation easy and even they are very effective but due to some major disadvantage of Cursor normally they are not preferred.

Disadvantages of cursors

• Uses more resources because Each time you fetch a row from the cursor, it results in a network roundtrip
• There are restrictions on the SELECT statements that can be used.
• Because of the round trips, performance and speed is slow.
• As we know cursor doing round trip it will make network line busy and also make time consuming methods. First of all select queries generate output and after that cursor goes one by one so round trip happen.
• Another disadvantage of cursor are there are too costly because they require lot of resources and temporary storage so network is quite busy.

Apart from these I would like to point out some great advantages of cursor if the entire result set must be transferred to the client for processing and display.

• Client-side memory : For large results, holding the entire result set on the client can lead to demanding memory requirements on client side system.
• Response time : Cursors can provide the first few rows before the whole result set is assembled. If you do not use cursors, the entire result set must be delivered before any rows are displayed by your application.
• Concurrency control :It's a general problem with current applications, If you make updates to your data and do not using cursors in your application, you must send separate SQL statements to the database server to apply the changes. This raises the possibility of concurrency problems if the result set has changed since it was queried by the client. In turn, this raises the possibility of lost updates.But Cursors act as pointers to the underlying data, and so impose proper concurrency constraints on any changes you make.

Some Alternatives to using a Cursor:

• Use WHILE LOOPS
• Use temp tables
• Use derived tables
• Use correlated sub-queries
• Use the CASE statement
• Perform multiple queries

It has been generally observed that looping without using a cursor is faster than looping using a cursor.

Some solutions:

1.  Add the recordset to a new Temp Table and also introduce a new column to the temp table.

*****************************************************
set rowcount 0
select NULL mykey, * into #mytemp from TableA

set rowcount 1
update #mytemp set mykey = 1

while @@rowcount > 0
begin
set rowcount 0
select * from #mytemp where mykey = 1
delete #mytemp where mykey = 1
set rowcount 1
update #mytemp set mykey = 1
end
set rowcount 0

*****************************************************************

2.  The following solution assumes that there is a unique indexed int column named id.

declare @id char( 11 )

select @id = min( id ) from TableA

while @id is not null
begin
    select * from TableA where id = @id
    select @id = min( id ) from TableA where id > @id
end

****************************************************************

3.  Here to the temp table we are adding a new column (RowID ) which is a identity column

DECLARE @RowsToProcess  int
DECLARE @CurrentRow     int
DECLARE @SelectCol1     int

DECLARE @table1 TABLE (RowID int not null primary key identity(1,1), Name varchar(50)) 
INSERT into @table1 (Name ) SELECT Name FROM tableA
SET @RowsToProcess=@@ROWCOUNT

SET @CurrentRow=0
WHILE @CurrentRow<@RowsToProcess
BEGIN
    SET @CurrentRow=@CurrentRow+1
    SELECT
        @SelectCol1=Name
        FROM @table1
        WHERE RowID=@CurrentRow

    --do your thing here--

END

 

******************************************************************

4.

DECLARE @table1 TABLE (
    idx int identity(1,1),
    col1 int )

DECLARE @counter int

SET @counter = 1

WHILE(@counter < SELECT MAX(idx) FROM @table1)
BEGIN
    DECLARE @colVar INT

    SELECT @colVar = col1 FROM @table1 WHERE idx = @counter

    -- Do your work here

    SET @counter = @counter + 1
END

 

Believe it or not, this is actually more efficient and performant than using a cursor.

*********************************************************************

5.

DECLARE
  @LoopId  int
,@MyData  varchar(100)

DECLARE @CheckThese TABLE
(
   LoopId  int  not null  identity(1,1)
  ,MyData  varchar(100)  not null
)

INSERT @CheckThese (YourData)
select MyData from MyTable
order by DoesItMatter

SET @LoopId = @@rowcount

WHILE @LoopId > 0
BEGIN
    SELECT @MyData = MyData
     from @CheckThese
     where LoopId = @LoopId

    --  Do whatever

    SET @LoopId = @LoopId - 1
END

*********************************************************************

6.

*********************************************************************

While fetching we should always remember that SQL Server Queries are SET Based operations and work best in circumstances dealing with SET Based operations.

You can loop through the table variable or you can cursor through it. This is what we usually call a RBAR - pronounced Reebar and means Row-By-Agonizing-Row.

So, we should always strive to find a SET-BASED answer and move away from RBARs as much as possible.

Set based queries are (usually) faster because:

1. They have more information for the query optimizer to optimize
2. They can batch reads from disk
3. There's less logging involved for rollbacks, transaction logs, etc.
4. Less locks are taken, which decreases overhead
5. Set based logic is the focus of RDBMSs, so they've been heavily optimized for it (often, at the expense of procedural performance)

Configure Full Text Search in Sql Server 2008 R2/ Express

SQL SERVER - 2008 - Creating Full Text Catalog and Full Text Search

(Source:http://www.codeproject.com/Articles/29237/SQL-SERVER-2008-Creating-Full-Text-Catalog-and-Ful)

Full Text Index helps to perform complex queries against character data. These queries can include word or phrase searching. We can create a full-text index on a table or indexed view in a database. Only one full-text index is allowed per table or indexed view. The index can contain up to 1024 columns. Software developer Monica who helped with screenshots also informed that this feature works with RTM (Ready to Manufacture) version of SQL Server 2008 and does not work on CTP (Community Technology Preview) versions.

To create an Index, follow the steps:

1. Create a Full-Text Catalog
2. Create a Full-Text Index
3. Populate the Index

1) Create a Full-Text Catalog

< !--[if gte vml 1]> <![endif]-->

Full – Text can also be created while creating a Full-Text Index in its Wizard.

2) Create a Full-Text Index

<!--[if gte vml 1]> <![endif]-->

3) Populate the Index

As the Index Is created and populated, you can write the query and use in searching records on that table which provides better performance.

For Example,

We will find the Employee Records who has “Marking “in their Job Title.

FREETEXT( ) Is predicate used to search columns containing character-based data types. It will not match the exact word, but the meaning of the words in the search condition. When FREETEXT is used, the full-text query engine internally performs the following actions on the freetext_string, assigns each term a weight, and then finds the matches.

• Separates the string into individual words based on word boundaries (word-breaking).
• Generates inflectional forms of the words (stemming).
• Identifies a list of expansions or replacements for the terms based on matches in the thesaurus.

CONTAINS( ) is similar to the Freetext but with the difference that it takes one keyword to match with the records, and if we want to combine other words as well in the search then we need to provide the “and” or “or” in search else it will throw an error.

USE AdventureWorks2008
GO

SELECT BusinessEntityID, JobTitle
FROM HumanResources.Employee
WHERE FREETEXT(*, 'Marketing Assistant');

SELECT BusinessEntityID,JobTitle
FROM HumanResources.Employee
WHERE CONTAINS(JobTitle, 'Marketing OR Assistant');

SELECT BusinessEntityID,JobTitle
FROM HumanResources.Employee
WHERE CONTAINS(JobTitle, 'Marketing AND Assistant');
GO

Conclusion

Full text indexing is a great feature that solves a database problem, the searching of textual data columns for specific words and phrases in SQL Server databases. Full Text Index can be used to search words, phrases and multiple forms of word or phrase using FREETEXT() and CANTAINS() with “and” or “or” operators.