topic Re: No Records revealed after Database Connection Made in Data Management Questions

No Records revealed after Database Connection Made

JesseJoe — Fri, 13 Sep 2013 19:19:56 GMT

Hi everybody,

I am having issues with accessing records after making a database connection in ArcCatalog. I go and try to preview the table but no records are there.

help!

Jesse
GIS Technician
Town of Sidney, BC, Canada

Re: No Records revealed after Database Connection Made

MichaelVolz — Fri, 13 Sep 2013 19:21:33 GMT

Has your database account at least been assigned viewer roles to see data in the SDE database?

Re: No Records revealed after Database Connection Made

JesseJoe — Fri, 13 Sep 2013 19:28:07 GMT

My desired database does show up in the Database Connection dialog, after specifying:

DATABASE PLATFORM, INSTANCE, AUTHENTICATION TYPE

What follows when I open the database is a list of tables, everything looks good but when I go and preview the table by right clicking, no records are found.

Yeah, that's my situation....

Re: No Records revealed after Database Connection Made

MichaelVolz — Fri, 13 Sep 2013 19:30:56 GMT

What type of database are you connecting to (e.g. Oracle, SQL Server, etc.)?

Re: No Records revealed after Database Connection Made

JesseJoe — Fri, 13 Sep 2013 19:33:59 GMT

SQL Srver 2005 database, on Windows Server 2003 R2, with SQL Native Client 2003 R2

Re: No Records revealed after Database Connection Made

WilliamCraft — Fri, 13 Sep 2013 20:38:30 GMT

Is it possible that your tables are registered as versioned within ArcSDE? If so, which transactional version are you connecting to with the ArcSDE connection file from ArcCatalog? I'm wondering if all of your records might exist in a different version that hasn't yet been reconciled and posted. For example, you might be connecting to SDE.DEFAULT to view the data in ArcCatalog but perhaps you've inserted your records into a child version called SDE.DEFAULT_CHILD. As another test if this turns out to not be the issue, can you log into SQL Server Studio Manager and see the records that way? If you cannot, then your tables might be empty to begin with.

Re: No Records revealed after Database Connection Made

JesseJoe — Fri, 13 Sep 2013 21:37:12 GMT

Michael Volz,

I think I am misunderstanding your question about assigned viewer roles due to my inexperience. Can you explain that a little further for me?

thanks

Re: No Records revealed after Database Connection Made

JesseJoe — Fri, 13 Sep 2013 21:38:55 GMT

Hi William, I think I am good there regarding versioning. I did a check also whether records exists at the sources, they do. Thanks for the heads-up.

Re: No Records revealed after Database Connection Made

WilliamCraft — Fri, 13 Sep 2013 22:25:38 GMT

I think his suggestion might be to ensure that the user you are connecting with in ArcCatalog has been assigned to something like the db_datareader role within the database. Technically, a user account can exist and connect but have no permissions to see any records. So, you have a user login set up at the SQL Server instance level and there should be a corresponding user created within the database for that particular login. In SQL Server Studio Manager, if you can log in as an administrator or the 'sa' account then you can expand the database properties to see the Security section. Under the user in question, right click to see the properties and then ensure that it has been assigned to at least one role.

Re: No Records revealed after Database Connection Made

AsrujitSengupta — Fri, 13 Sep 2013 23:14:15 GMT

You are able to see the table...and I guess, the Schema(Fields) as well..

I think the issue could be with one of the Field names......check for Reserved Keywords for that particular SQL Server version..

Re: No Records revealed after Database Connection Made

AsrujitSengupta — Fri, 13 Sep 2013 23:18:46 GMT

on a side note...please mention the version of ArcGIS being used for a better understanding (my guess, it's either 10.1 or 10.2....SQL Server 2005 is not supported with either).

Also, its preferable to have the SQL Server and the Native Client at the same version (2005 in your case)....

Re: No Records revealed after Database Connection Made

JesseJoe — Mon, 16 Sep 2013 16:09:03 GMT

I think his suggestion might be to ensure that the user you are connecting with in ArcCatalog has been assigned to something like the db_datareader role within the database. Technically, a user account can exist and connect but have no permissions to see any records. So, you have a user login set up at the SQL Server instance level and there should be a corresponding user created within the database for that particular login. In SQL Server Studio Manager, if you can log in as an administrator or the 'sa' account then you can expand the database properties to see the Security section. Under the user in question, right click to see the properties and then ensure that it has been assigned to at least one role.

Hi William regarding Michael's post:

Appreciate the heads-up William. I should be good regarding setting up of the User Log-in. I created it in the security folder and had it map to the appropriate database. As for roles, I gave it reader privileges, so it should be good here.

Re: No Records revealed after Database Connection Made

JesseJoe — Tue, 17 Sep 2013 17:38:13 GMT

on a side note...please mention the version of ArcGIS being used for a better understanding (my guess, it's either 10.1 or 10.2....SQL Server 2005 is not supported with either).

Also, its preferable to have the SQL Server and the Native Client at the same version (2005 in your case)....

I am running ArcGIS for Desktop v10.1 and trying to connect to a SQL Server 2005 database. Due to the incompatibility, this would explain for no records showing up.

I have spent the last two days doing the upgrade of the database from 2005 to 2008. I've come across a bunch of issues with this as well, I think my new-be colors are really shining now! If interested here is the thread:

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/a22f8480-9f65-4c2c-99fe-e14edf31d31e/cannot-access-mdf-files-for-database-attachement#a22f8480-9f65-4c2c-99fe-e14edf31d31e

Appreciate all that contributed, just learnt how to give points for helpful tips. Be more on-top of that for next time. I will post results after I have the 2008 upgrade successful and can access tables in ArcGIS 10.1

Re: No Records revealed after Database Connection Made

JesseJoe — Wed, 18 Sep 2013 22:21:31 GMT

Well...I've upgraded the database from SQL SERVER 2005 to 2008. I then proceeded to do the database link, Tables remain unpopulated!!! It took be quite some time troubleshooting to resolve a database attachment issue, disheartening to see that at the end, the database connection error still remains, out of steam, out of luck - anyhow, any more leads in the matter?

Jesse

Re: No Records revealed after Database Connection Made

AsrujitSengupta — Thu, 19 Sep 2013 15:29:22 GMT

the database connection error still remains

Are you observing connection issues now?

** Did you check for Reserved Keywords in that table, as I had indicated in one of my previous posts?

Re: No Records revealed after Database Connection Made

JesseJoe — Thu, 19 Sep 2013 19:59:03 GMT

For some reason, ArcGIS is able to populate records of a VIEW table created with no problems! I created the VIEW table in SQL Server 2008 and then previewed it after bringing it into ArcGIS by creating a database connection to it.

I was also able to successfully join it to a GIS layer using a common attribute key field.

Perhaps ArcGIS is especially designed to access VIEWS? So a subset table...maybe my original table was too big for ArcGIS to handle, perhaps too many fields? Not sure at all but with VIEWS, I'm in the race again.

Thanks for all the input everybody. Being a new-be, you're a great encouragement.

Jesse