Microsoft.Jet.OLEDB.4.0 provider is not registered on the local machine

 

 

Recently I deployed an aspx web application that is running OK on a 64 bit Windows 7 system. The web page would open Ok but trying to run a part of application that would read a csv/Excel file and parse it, using win forms application I am getting the above error message.

The fix involved two things:

1. Rebuilding the application specifically for 32 bit system. To do this for a VB.NET project, open project properities, select Compile tab, click on ‘Advanced Settings’ then select x86 for the system (not "Any CPU").

or

In case of Web project

2. On IIS system on the server, on the application pool for this application, select properties and then tick on "Enable 32 bit applications".

 

 

Converting hex to varbinary and vice versa

 

HexString To Varbinary :

CREATE FUNCTION [dbo].[fnHexStrToVarbinary]
(
@inputText varchar(max)
)
returns varbinary(max)
as
begin

declare @binvalue varbinary(8000), @sqlstring Nvarchar(1000)

--X Query

select @binvalue = cast('' as xml).value('xs:hexBinary( substring(sql:variable("@inputText"), sql:column("t.pos")) )', 'varbinary(max)')
from (select case substring(@inputText, 1, 2) when '0x' then 3 else 0 end) as t(pos)

RETURN @binvalue

end

Testing :

declare @hexstring varchar(MAX)
select @hexstring = ‘0x001be96ccd14ef47acbf24583b972c250100000031071af34c3e9aac1346d62c8507f0ab9255c827adcf82fad7029c1ee844fcfd1d3b89cf6e89afcda812441be1c9a5b5’

select dbo.[fnHexStrToVarbinary](@hexstring)

 

Varbinary to Hex string :

declare @binary varbinary(MAX)
select @binary = 0x001be96ccd14ef47acbf24583b972c250100000031071af34c3e9aac1346d62c8507f0ab9255c827adcf82fad7029c1ee844fcfd1d3b89cf6e89afcda812441be1c9a5b5

select master.dbo.fn_varbintohexstr(binary) --Built in function

So altogether we can convert one from another like this..

declare @hexstring varchar(MAX)

declare @binary varbinary(MAX)
select @binary = 0x001BE96CCD14EF47ACBF24583B972C250100000031071AF34C3E9AAC1346D62C8507F0AB9255C827ADCF82FAD7029C1EE844FCFD1D3B89CF6E89AFCDA812441BE1C9A5B5 – must not include in ‘’ as it is a varbinary field.

select  @hexstring = master.dbo.fn_varbintohexstr(@binary)

print @hexstring
select dbo.[fnHexStrToVarbinary](@hexstring)

select case when dbo.[fnHexStrToVarbinary](@hexstring) = @binary then ‘Same Values’ else ‘Different Values’
end