topic Re: ArcGIS10:Add Spatial Database Connection to MS SQL Server 2008 Management Studio. in Data Management Questions

ArcGIS10:Add Spatial Database Connection to MS SQL Server 2008 Management Studio.How?

ScottChang — Fri, 27 Apr 2012 17:09:41 GMT

Hi all,

I am new in doing ArcGIS10: Add Spatial Database Connection to Microsoft SQL Server 2008 Management Studio (aka SQL Server 2008 Express). I did not see any technical documents or tutorials on doing ArcGIS10: Add Spatial Database Connection to Microsoft SQL Server 2008 Management Studio (aka SQL Server 2008 Express). Could the experts of Geodatabase & ArcSDE please kindly give me/new users of ArcGIS10: Add Spatial Database Connection to Microsoft SQL Server 2008 Management Studio (aka SQL Server 2008 Express) some instructions/steps or technical sources for doing this kind of projects?

Thanks,
Scott Chang

P. S. I have tried to do it. But I did not go too far - see the attached file. My SQL Server 2008 Express and ArcGIS 10 are installed in our Microsoft NT4 LAN System. I do not know what User Name and Password to connect my SQL Server 2008 Express and ArcGIS 10.

Re: ArcGIS10:Add Spatial Database Connection to MS SQL Server 2008 Management Studio.

DeminHu — Fri, 27 Apr 2012 19:05:22 GMT

For an ArcSDE geodatabase , you want to make a spatial connection, probably you need to use ArcObject commands, tools or applications,

You can connect to ArcSDE geodatabase with ODBC, JDBC, OLE DB etc. But you will not view as spatial data, you will view them as database tables.

Re: ArcGIS10:Add Spatial Database Connection to MS SQL Server 2008 Management Studio.

KimPeter — Sat, 28 Apr 2012 01:24:19 GMT

Hi,

You don't connect to Management Studio from the Spatial Database Connection; you connect to a geodatabase in one SQL Server database.

Did you run the wizard to use ArcSDE SQL Server Express licensed through ArcGIS Desktop (previously called ArcSDE personal)? Or ArcSDE SQL Server Express licensed through ArcGIS Server Workgroup (previously called ArcSDE workgroup)? If you did either of those two things, then you should add a database server connection from ArcGIS Desktop to the SQL Server Express instance. You would be connecting with your Windows log in since Windows Authentication is the only supported connection method with those.
http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#/A_quick_tour_of_setting_up_and_using_database_servers/003n00000099000000/

Or did you instead install ArcSDE for SQL Server provided with ArcGIS Server Enterprise? In that case, what user you connect with depends on what logins and users you or your dba have created in SQL Server. You would also need to provide either an ArcSDE service port or direct connection string (sde:sqlserver:<your_sqlserver_instance> in the Service field and the name of the specific database in the Database field.
http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#/Geodatabase_connections_in_ArcGIS_Desktop/002q00000031000000/

If you don't actually have a database server or ArcSDE geodatabase, you can connect to a SQL Server database through the OLE DB connection , but, as Demin mentioned, you won't be able to render spatial data in ArcMap; you will only see the tables and can view the attributes stored in the rows of the tables.

-Kim

Re: ArcGIS10:Add Spatial Database Connection to MS SQL Server 2008 Management Studio.

KimPeter — Sat, 28 Apr 2012 05:32:02 GMT

Sorry - I just realized you said you were using ArcGIS 10. (I was thinking you were on 9.3.1...) You can see spatial tables in SQL Server if you create a query layer in ArcMap.
http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#/Connecting_to_a_database/00s500000037000000/

Re: ArcGIS10:Add Spatial Database Connection to MS SQL Server 2008 Management Studio.

ScottChang — Mon, 30 Apr 2012 15:09:26 GMT

Hi Demin and Kim, Thanks for your nice responses.

1) I am not sure I undersatand your responses closely. Let me tell both of you what Microsoft SQL Servers I have and ask you where I should start doing "Add Spatial Database Connection" and "ArcDSE" I want: I have Microsoft SQL Server 2008 Management Studio that I used for a while and Microsoft SQL Server 2008 that I never used. I just completed learning "OLE DB Connection" to XY data of the accdb file of Microsoft Access 2007 in ArcGIS 10. Thus, logically I want to learn "Add Spatial Database Connection to MS SQL Server 2008 Management Studio' in ArcGIS 10. Should I start using ArcGIS 10 with SQL Server 2008 as a backend (i. e. doing "Add Data" => "Add Query Layer"...) or using "Add Spatial Database Connection" (in ArcCata;log 10) to MS SQL Server 2008 Management Studio directly? Please clarify this starting point for me first.
2) This morning, when I was doing my "How to use Microsoft SQL Server 2008 in ArcGIS 10" search in Google, I saw an article "Spatial Data Support Coming To Microsoft SQL Azure" (published by geo.geek.nz, March 22, 2010). Also I saw that Microsoft SQL Server 2012 Express/SQL Azure is free and can be downloaded from Microsoft website. It seems to me Microsoft SQL Server 2012 Express/SQL Azure is pretty good to use. Should I as an ArcGIS 10 novice to use the Microsoft SQL Azure to do Spatial Database Connection and ArcSDE and ArcSDE geodatabase and so forth? Please enlighten me in this matter for me.

Please respond again.

Thanks,
Scott Chang

Re: ArcGIS10:Add Spatial Database Connection to MS SQL Server 2008 Management Studio.

VinceAngelo — Mon, 30 Apr 2012 20:09:58 GMT

There's some fundamental issues here, and I don't see any way through them without doing some
basic review:

Microsoft SQL-Server is a database. Management Studio is an administrative client to SQL-Server.
Microsoft publishes a library so that other clients can connect to the database server as well. At no
time will any other client, like ArcGIS or ArcSDE, connect to Management Studio. Management Studio
is used to configure the database, so that users exist for clients to connect, but it has no role in the
clients' connections to the database server, and it is not possible to use it without a database server.

There are a number of different versions of SQL-Server. Microsoft has chosen to split the capability
of their core software into "Enterprise", "Standard", and "Express" classes. They also have the "old"
release (2008) and the "new" release (2012). While you can chose the database you want to use, you
can't control the ability of older software clients to work seamlessly with newer servers (sometimes
the new servers will permit old clients, but it's rare that there's any way for the old client to access
new capabilities). It would be unwise to assume that you could use 2012 Express with ArcGIS 10.

ArcSDE is a "middleware" component -- it acts as a server to clients at the same time it acts as a
client to a database server. The technology of the ArcSDE middleware has also been configured into
a DLL which can run inside a client, which allows a client to be it's own server, and talk directly to the
database (this is called Direct Connect).

Esri also exploited opportunity presented by the existance of SQL-Server Express to provide "Personal
ArcSDE" and "Workgroup ArcSDE" products. These technologies are closely coupled to ArcGIS, and
don't really have much to do with the capabilites of Enterprise ArcSDE -- the connection protocols are
different, as are the administrative tools.

ArcGIS 10 also added a capability for clients to connect to spatially-enabled databases directly, without
using ArcSDE (aka "Query Layers").

It is not, however, possible to mix-n-match the protocols so that you can use a 2008 Express-specific
client to talk to a newer release of the database with an ArcSDE query layer.

I suggest you choose one technology, and explore its capabilities and limitations before trying to integrate
any other similar technology into your environment. If you work chronologically, you'll have less trouble
(and by the time you reach it, the newest ArcGIS may be able to talk with the newest Express).

- V

Re: ArcGIS10:Add Spatial Database Connection to MS SQL Server 2008 Management Studio.

KimPeter — Mon, 30 Apr 2012 20:37:39 GMT

Hi Scott,

I'm not clear on what you were using Microsoft SQL Server 2008 Management Studio for if you weren't using SQL Server 2008. Normally, you use Management Studio to manage SQL Server instances and databases.

That aside, though, it would help to know what you're trying to accomplish. What data do you need to store? What do you need to do with it?

To answer your questions about query layers and spatial database connections...

-If you want to store simple spatial data in a SQL Server database, you can load or create the data outside of ArcGIS. Then you can create a query layer in ArcMap to connect to the spatial tables in your database. You'll be able to view and analyze that data in ArcMap.

-If you want to load or create data in your SQL Server database through ArcGIS, you need a geodatabase. What edition of SQL Server do you have? See this Microsoft article on how to determine this: http://support.microsoft.com/kb/321185 What ArcGIS products do you have? The answers to these questions could affect what type of geodatabase you use. Once you have a geodatabase, that's when you can create a spatial database connection to it.

SQL Azure is not free (as far as I know). Contact Microsoft about pricing for it. Either way, though, SQL Azure is not supported with ArcGIS 10. SQL Server 2012 is also not supported with ArcGIS 10. To use either of these, you'll need to move to ArcGIS 10.1 when it is released.

-Kim

Re: ArcGIS10:Add Spatial Database Connection to MS SQL Server 2008 Management Studio.

ScottChang — Tue, 01 May 2012 15:08:30 GMT

Hi Vince and Kim,
Thanks for your nice, valuable responses that cleared up the confusions I had in starting Add Spatial Database Connection to my NAB-WK-02554356. Now I know what I should do first, before I start clicking Add Spatial Database Connection to my NAB-WK-02554356 in ArcCatalog 10!!!
1) I have learned to add (i) XY-Coordinates of the Point Feature Class from the MS Excel 2007 .xlsx file (in File Holder), and (ii) XY-Coordinates of the Point Feature Class from the MS Access 2007 .accdb file (from Database Connections-Add OLEDB Connection in ArcCatalg 10 directly) successfully. I did Personal and File Geodatabases before, but I did not do ArcSDE Geodatabase before. I should start creating ArcSDE geodatabase first, then use Add Spatial Database Connection to my NAB-WK-02554356 in ArcCatalog 10. Am I right in the last statement? See the 2nd attached file that I have XY-Coordinates for 8 Point Feature Class in my NAB-WK-02554356 of my SQL Server 2008 Express.
2) I can do the Query Layer in my ArcGIS 10 Desktop, I clicked on File => Add Data => Add Query Layer.... I can't fill out the User Name & Password of Database Authentication in the Database Connection box - see the 1st attached file for details.
Please kindly help and comment on 1) and 2).
Thanks,
Scott Chang

P.S.
To Kim: I read the article (How to determine the version and edition of SQL Server and its components) you cited and I saw SQL Server 2008 R2:
Component Name                                                Versions
Microsoft SQL Server Management Studio                10.50.1600.1
This "R2" Management Studio with 10.50.1600.1 is quite different from my "not-R2" Management Studio with 10.0.1600 Version. Should I ask my Computer Team to upgradw my "not-R2" Management Studio with 10.0.1600 Version to the "R2" Management Studio with 10.50.1600.1?

Re: ArcGIS10:Add Spatial Database Connection to MS SQL Server 2008 Management Studio.

KimPeter — Thu, 03 May 2012 01:26:17 GMT

I should start creating ArcSDE geodatabase first, then use Add Spatial Database Connection to my NAB-WK-02554356 in ArcCatalog 10.

Yes, that is correct; you must create an ArcSDE geodatabase before you can connect to it from the Spatial Database Connection dialog box in ArcCatalog 10.

I can't fill out the User Name & Password of Database Authentication in the Database Connection box

Do you mean the user name and password fields are inactive/you can't physically type in them? Are you attempting to connect to the same SQL Server instance and database that you show in your second attachment? If so, the information you typed in the Database Connection dialog box does not match the datasource and database. The datasource in this case is your SQL Server instance name. That should match what you provided for the "server name" when you logged in through Management Studio. The database is the name of the specific database to which you want to connect. In your second graphic, the database you are connected to in Management Studio appears to be DotSpatialGISdatabase.

Additionally, does the SQL Server instance you want to connect to allow for database authentication? By default, SQL Server allows Windows-authenticated logins only. To use database authentication, you must configure the instance for mixed-mode authentication, then add the logins, database users, and schemas you require, and grant permissions.

Should I ask my Computer Team to upgrade...

You don't have to upgrade Management Studio or your SQL Server instance to 2008 R2; SQL Server 2008 is supported with ArcGIS 10. http://resources.arcgis.com/content/arcsde/10.0/sql-server-system-requirements

-Kim

Re: ArcGIS10:Add Spatial Database Connection to MS SQL Server 2008 Management Studio.

ScottChang — Tue, 08 May 2012 17:31:46 GMT

Hi Kim, Thanks for your nice response.

I am still struggling with my problem of Add Spatial Database Connection to MS SQL Server 2008 Management Studio. My PC has Microsoft Windows XP Pro that is linked to Microsoft NT4 LAN System. My MS SQL Server 2008 Management Studio (SQL Server 2008 Express) is installed in my PC. I think that Personal ArcSDE can be done in Windows 7, not in Microsoft Windows XP Pro - Am I right in this guessing?

I do not want ArcGIS Server Workgroup in my ArcSDE support for my SQL Server 2008 Express. Is it possible to make a direct connection to a geodatabase in my MS SQL Server 2008 Management Studio (SQL Server 2008 Express) from my ArcGIS Catalog 10? If it is possible, please tell me how to make such direct connection.

Please help and respond.

Thanks,
Scott Chang

Re: ArcGIS10:Add Spatial Database Connection to MS SQL Server 2008 Management Studio.

JoeBorgione — Tue, 08 May 2012 19:03:13 GMT

Hi Kim, Thanks for your nice response.

I am still struggling with my problem of Add Spatial Database Connection to MS SQL Server 2008 Management Studio. My PC has Microsoft Windows XP Pro that is linked to Microsoft NT4 LAN System. My MS SQL Server 2008 Management Studio (SQL Server 2008 Express) is installed in my PC. I think that Personal ArcSDE can be done in Windows 7, not in Microsoft Windows XP Pro - Am I right in this guessing?

I do not want ArcGIS Server Workgroup in my ArcSDE support for my SQL Server 2008 Express. Is it possible to make a direct connection to a geodatabase in my MS SQL Server 2008 Management Studio (SQL Server 2008 Express) from my ArcGIS Catalog 10? If it is possible, please tell me how to make such direct connection.

Please help and respond.

Thanks,
Scott Chang

I've been following this thread from the sidelines and I'm more and more confused as it goes. Selfishly, I'd like to add to the discussion just to see if I can clarify in my mind what your objective is.

First; are you trying to connect to a SQl Server database through ArcSDE or are you trying to connect to a SQL Express database with Personal SDE?

Second; Where/when/why does SQL Server Management Studio come into play with respect to ArcGIS? The only thing I use it for is to actually manage the sql data base; back ups, restores, user permissions, triggers, schemas, etc. In some cases I'll run an update query or a make table query to create tabular data, but as n interface, ArcMap really shines for that. Perhaps I'm missing some cool functionality and if so, please enlightn me.

Third; is there a specific reason you need to create a direct connection to a SQL Server Express database? My SQL Server Express/Personal SDE data base is installed on my local machine; as I read through this passage on line, I don't see where a direct connection would be advantageous in the Personal SDE environment. Now, if your Personal SDE/SQL Server Express database is installed on something other than your local machine, and the server machine does not have the power to run it, then I guess a direct connection might be warranted. I've always thought that Personal SDE can run just fine on a Personal Computer.

Fourth; as I type this, the machine I am using is a windows XP pro machine with Personal SDE and Sql Server Express installed and running without any problems. In fact I use database replication from a SDE/Oracle parent database to this one, so no operating system issues that I'm aware of.

The two esri folks responding to this thread have bailed me out more than once with SDE/SQL issues. I consider them the de facto go-to forum members for these kinds of issues. I'm sure we can work through a solution for you.

Re: ArcGIS10:Add Spatial Database Connection to MS SQL Server 2008 Management Studio.

VinceAngelo — Tue, 08 May 2012 20:04:45 GMT

Joe -

Personal ArcSDE and Workgroup ArcSDE are *only* capable of Direct Connect (they are
bound to Express and will not ever run an application service). It's not supported, but
actually possible to use Enterprise ArcSDE command-line tools with Personal/Workgroup
instances, but only via Direct Connect command-line switches (and/or variables).
That said, Personal/Workgroup instances should always be administered from Catalog,
and Direct Connect will only work when:
a) Catalog was used to create the Express instance, or
b) Enterprise ArcSDE has been post-installed in the instance.

It is perhaps unfortunate that "ArcSDE" is associated with non-Enterprise geodatabases,
but "personal geodatabase" refers to the Access/Jet data file, and they do use a significant
part of the ArcSDE technology codebase (just not the network protocol), so it's not wrong,
just confusing.

- V

Re: ArcGIS10:Add Spatial Database Connection to MS SQL Server 2008 Management Studio.

JoeBorgione — Wed, 09 May 2012 01:33:50 GMT

V- I guess I never thought of add database server in ArcCatalog as a direct connection. But it makes sense.

I'm still scratching my head with the whole management studio direction in the original post; but I've already learned something new today, so...

Re: ArcGIS10:Add Spatial Database Connection to MS SQL Server 2008 Management Studio.

KimPeter — Wed, 16 May 2012 03:19:01 GMT

I do not want ArcGIS Server Workgroup in my ArcSDE support for my SQL Server 2008 Express. Is it possible to make a direct connection to a geodatabase in my MS SQL Server 2008 Management Studio (SQL Server 2008 Express) from my ArcGIS Catalog 10? If it is possible, please tell me how to make such direct connection.

Scott,

Going back to the screen shots you provided, it does not look like you have a geodatabase. What you appear to have is a SQL Server database (DotSpatialGISDatabase) that contains a table with x,y coordinates in it (FtMcNair8pts).

To connect to a table in a database, create a query layer in ArcMap. That will add the table to the ArcMap table of contents. I think your previous attempt to create a query layer failed because you had the wrong information in the Database Connection dialog box. Based on your screen shots, the Data Source is NAB-WK-02554356\SQLSCOTT and your Database is DotSpatialGISDatabase. Then, if you want to use Database Authentication to connect, you must provide the user name and password for a database user who has permission to connect to the database and see your dbo.FtMcNair8pts table. If you are unable to make the query layer database connection, it is possible that your SQL Server instance is not configured to use Database Authentication. Try choosing OS Authentication instead. If you are still unable to make a query layer connection, please contact support so they can help walk through your steps and configuration.

Once you have created the query layer, though, and the table is listed in the ArcMap table of contents, you can right-click that table and click Display XY Data. In the Display XY Data dialog box, specify which field contains your x coordinates and which contains your y coordinates and indicate which coordinate system to use. Then your points will render on the map.

In regards to Management Studio, I think you're using that name when you really mean SQL Server Express. The two are not the same thing - SQL Server Express is an edition of the Microsoft SQL Server RDBMS; whereas Management Studio is the application Microsoft provides for you to manage your SQL Server instances. You do not connect to Management Studio from ArcGIS.

I hope this helps.

-Kim

Re: Add Spatial Database Connection to MS SQL Server 2008 Management Studio.

JoeBorgione — Mon, 09 Jul 2012 16:06:49 GMT

Hi, I want to ask something about turned to me crazy. I have a sptial database (XYZ) on SQLServer 2008 R2. I can connect to it (XYZ) using arccatalog, but when I created different database (KLM) and tried to connect it (KLM) via ArcCatalog it connects to older (XYZ) database :S I didn't figure out why it doesnt't connect my new database (KLM). Does anyone know anything about that problem?

Did you establish a new connection in arc catalog to the database KLM?

[ATTACH=CONFIG]15871[/ATTACH]

Do you have permissions to at least read KLM?

Re: ArcGIS10:Add Spatial Database Connection to MS SQL Server 2008 Management Studio.

VinceAngelo — Mon, 09 Jul 2012 16:50:43 GMT

In the single database model (which everyone should be using at this late date),
each database in which ArcSDE is post-installed receives its own port. If you
connect via an application server to a port, the database is ignored. If you
connect via Direct Connect, then the database is required (and must have had
ArcSDE previously installed).

It sounds like you're trying to use a port dedicated to a specific database to
connect a different database (which hasn't had ArcSDE post-installation) --
this will not work. Instead, you'll need to run post-install on the new database,
then assign it a port (via 'sdeservice') or use Direct Connect.

- V

Re: Add Spatial Database Connection to MS SQL Server 2008 Management Studio.

KimPeter — Tue, 17 Jul 2012 00:04:11 GMT

Hi, I want to ask something about turned to me crazy. I have a sptial database (XYZ) on SQLServer 2008 R2. I can connect to it (XYZ) using arccatalog, but when I created different database (KLM) and tried to connect it (KLM) via ArcCatalog it connects to older (XYZ) database :S I didn't figure out why it doesnt't connect my new database (KLM). Does anyone know anything about that problem?

This is a known issue; ArcCatalog is hanging on to the old connection information. If you close ArcCatalog and re-open it, you should be able to make a connection to the new geodatabase. I'll try to dig up the bug number.

(By the way, this should go into a different post since it isn't exactly related to the original post.)

Re: Add Spatial Database Connection to MS SQL Server 2008 Management Studio.

ShannonShields — Tue, 17 Jul 2012 02:58:17 GMT

This is a known issue; ArcCatalog is hanging on to the old connection information. If you close ArcCatalog and re-open it, you should be able to make a connection to the new geodatabase. I'll try to dig up the bug number.

The bug is NIM080776 Changing the database of an existing connection in ArcCatalog does not change subsequent connections without a refresh of database connections node.

This only appears to affect connections made with Windows Authentication. Connections made using SQL Server-managed logins behave correctly. Refreshing the database connections node will update the connection to the new database without having to restart ArcCatalog.

-Shannon