Previously, a SQL Server 2005 query retrieved data from a Pervasive SQL 10 table using ODBC. All this took place in the Windows SBS 2003 enironment.
Recently this server has been replaced by a new server running Windows Server 2008 R2 and SQL Server 2008 R2. I have installed the same version of Pervasive SQL and have migrated all PSQL databases successfully. All the right tables are there and are accessible. I have made these databases available through ODBC using ODBCAD32.exe (PSQL databasename linked to the database location via Pervasive ODBC Engine Interface; everything else default).
In SQL Server, I link to such a PSQL database (execute sp_addlinkedserver <odbc link name>). Subsequently, I try to create a view using the following statement:
create view [dbo].[vw_dagboek] as
select * from openquery
' select rtrim(DBKCODE) as dbkcode,
rtrim(BENAMING) as benaming,
rtrim(FINREKNR) as finreknr,
rtrim(VALUTA) as valuta,
rtrim(BANKNR) as banknr,
rtrim(BANKNAAM) as banknaam
where SOORTDBK = ''F'' and
rtrim(BANKNR) <> ''''' )
This results in the following error message:
OLE DB provider "MSDASQL" for linked server "BOWANBVBA" returned message "[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application".
Msg 7303, Level 16, State 1, Procedure vw_dagboek, Line 5
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "BOWANBVBA".
So far, I have ruled out the accessibility of the database itself, collation issues and case sensitivity issues. Although I have tried hard, I cannot imagine what else could cause this architecture mismatch. I am grateful for any hint that helps me to solve this problem.