topic OLE DB connection to MS SQL 2008 database table in ArcGIS Enterprise Questions

OLE DB connection to MS SQL 2008 database table

AdrianGomez — Wed, 11 Jul 2012 21:09:31 GMT

Dear Sir / Madam,

I am trying to connect ArcCatalog 10.0 via OLE DB Connection.

I can preview all tables in the database except for one stating "The selection cannot be previewed"
This table contains x/y coordinates and if I try to create a feature clase it causes the error below:

There was an error trying to process this table.
An underlying database error occured.
An underlying database error occured.[dbo.xxx.tablename]
Multiple-step OLE DB operation generated errors. Check each OLE DB status
value, if available. No work was done.

Is there a log file that would provide more detail as to what ArcCatalog does not like about this table?
Are there any particular MS SQL 2008 column data types that should be avoided?
(table currently has 63 columns of data types int, varchar, datetime, tinyint, bit, numeric, nvarchar and xml)

Any suggestions would be greatfully appreciated.

Kind regards, Adrian

Re: OLE DB connection to MS SQL 2008 database table

KenGalliher1 — Tue, 24 Jul 2012 00:25:27 GMT

Hi Adrian,

You can find the Sql Server logs in the DATA folder typically found in:
C:\Program Files\Microsoft Sql Server\MSSQL10_50.MACHINE\MSSQL\DATA.

They won't tell you much in direct relation to ArcGIS but they will let you know what Sql Server is complaining about.

Can you query the table in Sql Server Management Studio? This is often a datatype problem. Make sure the fields in the table are acceptable and do not contain special characters or reserved words. Make sure you are passing valid datatypes to whatever is going on in the database.

Let me know about the query in Sql Server.

Ken

Re: OLE DB connection to MS SQL 2008 database table

AdrianGomez — Tue, 24 Jul 2012 08:57:47 GMT

Hi Ken,

Appreciate the reply. I can successfully query the table in SQL Server Management Studio.

By way of process of elimination a View of the Table was created introducing one field at a time until it failed to preview in ArcCatalog. The offending field is called strNatureOfCall. Originally the Supplier had the datatype for strNatureOfCall as TEXT. Following some research I recommended that datatype VARCHAR be used to keep field content in unicode characters. This has now been implemented by our Supplier. However they implemented as varchar(MAX) which did not resolved the issue with preview in ArcCatalog. ArcCatalog saw field datatype varchar(MAX) as datatype BLOB.

After your reply gave me a steer I did some more research and found this article (albeit for an older version) http://webhelp.esri.com/arcgisserver/9.3/dotnet/index.htm#geodatabases/sql_server_data_types.htm

After reading I understood that ArcGIS will convert varchar above 8000 to datatype blob. Went back to Supplier go them to change the datatype from varchar(max) to varchar(4000) and that enabled ArcCatalog to maintain the datatype as a text datatype rather than convert it to a blob datatype.

Thanks again for the great steer which helped me on the way to resolve the issue.

all the best,
Adrian

Re: OLE DB connection to MS SQL 2008 database table

KenGalliher1 — Thu, 26 Jul 2012 18:11:05 GMT

Hi Adrian,

Good catch on the BLOB conversion. I'm glad I could help but it looks like you did all the heavy lifting.

- Ken