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

[ScottChang]

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.

[JoeBorgione]

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.

[VinceAngelo]

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

[JoeBorgione]

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

[KimPeter]

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

[JoeBorgione]

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?

[VinceAngelo]

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

[KimPeter]

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

[ShannonShields]

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