Options for bringing data into a Microsoft SQL Server database

[sbosch]

Hello,

I am having issues bringing large tables in from the ServiceNow database into a Microsoft SQL Server database.   I used the information from the following article: Using ODBC Driver in SQL Server - ServiceNow Wiki.   However, I get the following error: OLE DB provider "MSDASQL" for linked server "SERVICENOW_PRAXPROD" returned message "[DataDirect][ODBC OpenAccess SDK driver][OpenAccess SDK SQL Engine]Disk Cache file size limit has reached[10215]."   I went into the ServiceNow ODBC Management Console and reset the ServiceSQLDiskCacheMaxSize property to the maximum, 1,048,575.   After doing this, I was still getting the same error.

Are there alternatives to using the linked server approach?   If not, is there a way to get around the maximum cache size issue?

Thanks in advance,

SWB

[tony_barratt]

Hi Stephen,

There is a published KB

ODBC Error Messages: Determining if you need to increase file size limit

ServiceNow Customer Service System

..

Cause

If you receive an error message indicating that the Disk Cache file size limit has been reached, this is likely caused by performing operations on a large number of columns.

Resolution

If you receive the following error message when running a query, [ODBC OpenAccess SDK driver][OpenAccess SDK SQL Engine]Disk Cache file size limit has reached., the disk cache file size limit has been reached. If it is necessary for you to complete the query or operations without reducing the file size, increase the file size limit of the property.

Using the Management Console application, navigate to Services > ServiceNow_ODBC > Service Settings > SQL Engine Parameters and increase the ServiceSQLDiskCacheMaxSize property.

I just checked my ODBC setting, default is 200 (MB)

1,048,575 = 1 TB so seems rather large.

Maybe try 500?

Are you bringing a large number of columns?

Could you reduce that?

What version is your ODBC Driver?

1.0.9 is the current version

Release Notes - ServiceNow Wiki

Best Regards

Tony

[tony_barratt]

Hi Stephen,

Any news? Did you find a solution? If so could you update this thread with the solution.

This will add value to Community Members reading this thread.

Consider marking the question as answered, or marking replies as helpful as appropriate.

Best Regards

Tony

[sbosch]

Hi Tony,

I found a solution. Also, I have the additional options to try from yourself and Ashutosh if I were to encounter the same problem in the future.

Thank you.

Stephen Bosch<http://www.linkedin.com/in/stephenwbosch> | Business Intelligence Developer

C: 315.529.4542

inergex.com<http://www.inergex.com/> | Twitter @inergex<https://twitter.com/inergex>

[Ashutosh Munot1]

HI Stephen,

Actually we have done this recently. First you need to have Proper ODBC service now drivers installed. It seems there is problem in Size of cache.

When we started doing this we actually reduced the columns which were transfered or mapped to Microsoft SQl.

Just try bringing slow amount of data from Service now to Microsoft. Also version matters alot as Tony said.

Important: Check your Microsoft SQL ODBC driver to and Cache and disk Size of that server also. it may have problem there , we have faced it.