HI, Please take a look at the post below (Sept 9 2013)

sagecity.na.sage.com/.../69119.aspx

The current version of the ODBC driver is 4.40.0004.

Make sure your "SAGE100SMI" is a System DSN and "Silent Mode"  is checked.  

Can you post the exact query(s) that is running when the crash occurs?

John Nichols

Sage

John,

Tested with Silent Mode. The result was the same. The two most relevant events from the

Event Log are below. The ensuing list of 3400 series events indicating all SQL DBs in either Recovery Mode or failing CheckPoint number in the hundreds and are not really relevant except to say that I don't want to test this frequently on a production server. The issue is highly replicatable and the simple OpenQuery statement that we used to trigger this issue is below as well. We have been using this function for several years now and I have never seen it react this way and it does not react this way on SQL Server 2008 64bit. The setups are identical with respect to the ODBC configs and the SQL Linker Server(s).

 SELECT *

 FROM OPENQUERY(SAGE100SMI,'SELECT * FROM PO_PurchaseOrderHeader')

Log Name:      Application

Source:        Application Error

Date:          12/9/2014 8:27:33 PM

Event ID:      1000

Task Category: (100)

Level:         Error

Keywords:      Classic

User:          N/A

Computer:      WSQL1.skilledmfg.com

Description:

Faulting application name: sqlservr.exe, version: 2011.110.3128.0, time stamp: 0x50deadad

Faulting module name: ntdll.dll, version: 6.2.9200.16579, time stamp: 0x51637f77

Exception code: 0xc0000374

Fault offset: 0x00000000000ebd59

Faulting process id: 0x5e4

Faulting application start time: 0x01cffc8c64998444

Faulting application path: C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\sqlservr.exe

Faulting module path: C:\Windows\SYSTEM32\ntdll.dll

Report Id: b6a7d9d9-800b-11e4-9406-00155d00cb00

Faulting package full name:

Faulting package-relative application ID:

Event Xml:

<Event xmlns="schemas.microsoft.com/.../event">

 <System>

   <Provider Name="Application Error" />

   <EventID Qualifiers="0">1000</EventID>

   <Level>2</Level>

   <Task>100</Task>

   <Keywords>0x80000000000000</Keywords>

   <TimeCreated SystemTime="2014-12-10T01:27:33.000000000Z" />

   <EventRecordID>501623</EventRecordID>

   <Channel>Application</Channel>

   <Computer>WSQL1.skilledmfg.com</Computer>

   <Security />

 </System>

 <EventData>

   <Data>sqlservr.exe</Data>

   <Data>2011.110.3128.0</Data>

   <Data>50deadad</Data>

   <Data>ntdll.dll</Data>

   <Data>6.2.9200.16579</Data>

   <Data>51637f77</Data>

   <Data>c0000374</Data>

   <Data>00000000000ebd59</Data>

   <Data>5e4</Data>

   <Data>01cffc8c64998444</Data>

   <Data>C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\sqlservr.exe</Data>

   <Data>C:\Windows\SYSTEM32\ntdll.dll</Data>

   <Data>b6a7d9d9-800b-11e4-9406-00155d00cb00</Data>

   <Data>

   </Data>

   <Data>

   </Data>

 </EventData>

</Event>

Log Name:      Application

Source:        Windows Error Reporting

Date:          12/9/2014 8:27:42 PM

Event ID:      1001

Task Category: None

Level:         Information

Keywords:      Classic

User:          N/A

Computer:      WSQL1.skilledmfg.com

Description:

Fault bucket , type 0

Event Name: APPCRASH

Response: Not available

Cab Id: 0

Problem signature:

P1: sqlservr.exe

P2: 2011.110.3128.0

P3: 50deadad

P4: StackHash_b147

P5: 6.2.9200.16579

P6: 51637f77

P7: c0000374

P8: PCH_82_FROM_ntdll+0x0000000000002C2A

P9:

P10:

Attached files:

C:\Users\serviceadmin\AppData\Local\Temp\WER824C.tmp.appcompat.txt

C:\Users\serviceadmin\AppData\Local\Temp\WER8356.tmp.WERInternalMetadata.xml

C:\Users\serviceadmin\AppData\Local\Temp\WER9876.tmp.dmp

C:\Users\serviceadmin\AppData\Local\Temp\WER9AC9.tmp.WERDataCollectionFailure.txt

These files may be available here:

C:\ProgramData\Microsoft\Windows\WER\ReportQueue\AppCrash_sqlservr.exe_2c1136bfd4b58044fa4be96a5c1fec391821d2a6_cab_91209ae4

Analysis symbol:

Rechecking for solution: 0

Report Id: b6a7d9d9-800b-11e4-9406-00155d00cb00

Report Status: 0

Hashed bucket:

Event Xml:

<Event xmlns="schemas.microsoft.com/.../event">

 <System>

   <Provider Name="Windows Error Reporting" />

   <EventID Qualifiers="0">1001</EventID>

   <Level>4</Level>

   <Task>0</Task>

   <Keywords>0x80000000000000</Keywords>

   <TimeCreated SystemTime="2014-12-10T01:27:42.000000000Z" />

   <EventRecordID>501627</EventRecordID>

   <Channel>Application</Channel>

   <Computer>WSQL1.skilledmfg.com</Computer>

   <Security />

 </System>

 <EventData>

   <Data>

   </Data>

   <Data>0</Data>

   <Data>APPCRASH</Data>

   <Data>Not available</Data>

   <Data>0</Data>

   <Data>sqlservr.exe</Data>

   <Data>2011.110.3128.0</Data>

   <Data>50deadad</Data>

   <Data>StackHash_b147</Data>

   <Data>6.2.9200.16579</Data>

   <Data>51637f77</Data>

   <Data>c0000374</Data>

   <Data>PCH_82_FROM_ntdll+0x0000000000002C2A</Data>

   <Data>

   </Data>

   <Data>

   </Data>

   <Data>

C:\Users\serviceadmin\AppData\Local\Temp\WER824C.tmp.appcompat.txt

C:\Users\serviceadmin\AppData\Local\Temp\WER8356.tmp.WERInternalMetadata.xml

C:\Users\serviceadmin\AppData\Local\Temp\WER9876.tmp.dmp

C:\Users\serviceadmin\AppData\Local\Temp\WER9AC9.tmp.WERDataCollectionFailure.txt</Data>

   <Data>C:\ProgramData\Microsoft\Windows\WER\ReportQueue\AppCrash_sqlservr.exe_2c1136bfd4b58044fa4be96a5c1fec391821d2a6_cab_91209ae4</Data>

   <Data>

   </Data>

   <Data>0</Data>

   <Data>b6a7d9d9-800b-11e4-9406-00155d00cb00</Data>

   <Data>0</Data>

   <Data>

   </Data>

 </EventData>

</Event>

Hi,

From your MS SQL Server 2012 "and" SQL Server 2008 please run the query below.  

SELECT @@VERSION AS 'SQL Server Version';

What version of Sage 100 are you running?  

Did you install the Sage 100 Client and the separate x64 ODBC driver on the Windows Server 2012 R1?

On the Windows Server 2012 R1

Run  c:\windows\system32\odbcad32.exe    (ODBC Data Source Administrator)

Go to the System Tab

Double Click your SAGE100SMI DSN

Go To Debug Tab

Test Connection - does it successfully return tables?

I cannot replicate the issue on my system.

Microsoft SQL Server 2012 - 11.0.5058.0 (X64)

May 14 2014 18:34:29

Copyright (c) Microsoft Corporation

Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Sage 100 2014

MAS 90 4.0 ODC Driver 4.40.04.00   x64

Based on the information I have, my assumption is that configuration differences exist between the MS SQL Server 2008 R2 and MS SQL Server 2012

John Nichols

Sage

Verify in linked server Providers - MSDASQL properties “Allow in Process” is checked,

No other properties should be set

John,

As I stated earlier in the post, Returning a table count works just fine in the debug function. However using your method of odbcad32.exe for creating a 32 but ODBC connection will return a table count but when creating a Linked Sever using that configuration on a 64 bit server results the following expected message:

Begin Message ****************************

TITLE: Microsoft SQL Server Management Studio

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

The linked server has been created but failed a connection test. Do you want to keep the linked server?

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

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

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

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "SAGE100SMI".

OLE DB provider "MSDASQL" for linked server "SAGE100SMI" returned message "[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application". (Microsoft SQL Server, Error: 7303)

For help, click: go.microsoft.com/fwlink

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

BUTTONS:

&Yes

&No

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

End Message ***************************

To my knowledge the configuration of the ODBC parameters has to occur in the 64 bit ODBC configuration utility for a Linked Server to work in a 64 bit SQL server. Also noteworthy regarding the Allow In Process

flag, in the 64 bit server when this flag is set on the provider the Linked Server will not enumerate the Sage table structure at all. Also I have used both the Sage 100 CD install procedure as well as a manual

install procedure both resulting in the same effect. I am unsure how your are using the 32 bit ODBC

configuration utility in order to create a viable Linked Server in a 64bit SQL Server. That also dos not work

on my 2008 SQL Server but the 64 bit ODBC works just fine on my DEV 2008 SQL Server using the 64bit

ODBC configuration utility.

Begin SQL Build Info ***********************

WDEV01 SQL Server Version

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)   Jun 17 2011 00:54:03   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

WSQL1 SQL Server Version

Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)   Dec 28 2012 20:23:12   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

End SQL Build Info ***********************

Sincerely

Brent

Hi Brent,

"The specified DSN contains an architecture mismatch between the Driver and Application". "  tells me the Sage 100 x64 ODBC driver is not configured/installed.

On a x64 system

c:\windows\system32\odbcad32.exe  "runs the x64 ODBC manager"

c:\windows\syswow64\odbcad32.exe  "runs the x86 ODBC manager"

If your DSN "SAGE100SMI"  does not return tables/success from c:\windows\system32\odbcad32.exe

then the x64 ODBC is either not installed or misconfigured.  

On your 2012 x64 system browse to c:\windows\system32

Do the files below exist?  

pvodbsec

pvxio.dll

pvxodbc.dll

pvxsec64.dll

If they do not reside in c:\windows\system32,  you'll need to install the X64 driver after you've installed a Sage 100 2013 or higher workstation on the 2012 Server.

Thanks

John

John

All these files do reside in the appropriate directory. The 64bit ODBC utility that I am using is located at

%windir%\system32\odbcad32.exe. I cant send you a screen shot in this forum. The 64 bit

ODBC utility does enumerate the Sage tables properly using debug and the Linked Server also enumerates the tables correctly expanding the tree in SQL. The issue is that an OpenQuery function when executed begins to return data and then crashes the SQL Server. Are you executing the sample query that we sent in an SQL query window ?.

"Are you executing the sample query that we sent in an SQL query window ?."  Yes,  no errors data set returned properly(screen shot attached) Only change is to DSN name.

Architectural mismatch means the driver is not installed or properly configured.  in your DSN set Options Tab set Null Date to Checked. Go to Debug Tab select Enable Debug. Save and rerun your query.  Send me the log when you can. Thanks John 

Brent,

Are you running the CS ODBC Driver as a service or executable on the Sage 100 Server?  

Open your DSN on the 2012 Server, check the Server Tab - Is there a Server Name or IP value - if Yes, remove the server name or IP address

Go to Basic Tab and enter the UNC path to MAS90 directory. i.e.  \\ServerName\SharePoint\MAS90 

Save.

Go to Debug Tab and Test Connection.

Rerun your SQL query.  (The CS ODBC Service must be the same version as the ODBC Driver)

Can you check the versions on Server 2012 box

C:\windows\system32\pvxodbc.dll

C:\windows\system32\pvxio.dll

Both should be version 4.40.000

Thanks John