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

5731
17
04-27-2012 10:09 AM
ScottChang
New Contributor II
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.
0 Kudos
17 Replies
DeminHu
New Contributor
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.
0 Kudos
KimPeter
Esri Contributor
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_d...

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_Desk...


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
0 Kudos
KimPeter
Esri Contributor
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/00s5000000370...
0 Kudos
ScottChang
New Contributor II
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
0 Kudos
VinceAngelo
Esri Esteemed Contributor
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
0 Kudos
KimPeter
Esri Contributor
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
0 Kudos
ScottChang
New Contributor II
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?
0 Kudos
KimPeter
Esri Contributor
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
0 Kudos
ScottChang
New Contributor II
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
0 Kudos