Connect to SQL Server Spatial

[ezfisher]

Anyone understand the service name when attempting to connect to sql server spatial

I've tried about every permutation as far a servername\instance\

I can get it to connect through a DSN/ODBC connection, but want to use the above...

Any examples would allow me to determine if I have the wrong syntax, or, there is a more fundamental issue to deal with

Thanks

[Murph_Map]

With my sqlserver express this is what I use. 

ComputerName\SQL_NAME/Data Table 

yes the \ first then / . Also this is with SQL Express on a laptop in 2014. 

[norman.yuan]

More than one SQL Server instance can be installed in the same computer. Depending on how SQL Server is installed on a compuer, its name can either be the same as the computer name (when installed as default instance), or named instance (e.g its name is like [ComputerName]\[InstaneceName]). Only one default instance per computer, of course.

After you correctly know the SQL Server name, then connecting to SQL Server is straightforward, well, if you know a bit how permission to connection to Sql server is granted.

In the "Data Connection" palette, after selecting "Add SqlServer spatial Connection" on the left,

1. In the "Connection Name" text box, enter whatever name you'd like to call this connection

2. In the "Service name" text box, enter the SQL Server name (that is, either a ComputerName, or ComputerName\SQLServerInstanceName) that is hosted either in a computer in the network where your computer has access to, or the same computer your AutoCAD Map runs.

3. Click button "Login". You either use Windows Authentication, or use SQL Server login (user name/password). To use Windows Authentication, the SQL Server must enable Windows security (If you use SQL Server Express locally installed, Wndows security is enabled by default). If you use SQL Server Login, you must have proper SQL Server login created in the SQL Server (thus you know, or the SQL Server administrtor would tell you, the SQL server login's user name/password).

4. Once you successfully get over the SQL Server login stage, in the "Data store" dropdown list, you can either choose an existing spatial database, or choose to create a new one.

[ezfisher]

I appreciate the responses!  Unfortunately, none of the advice is working for me.

I've done a thousand sql connections using many products (including a using a udl w/ Acad map)...

As mentioned, I can attach via ODBC, but not through the SQL Server Spatial so I am wondering if it an issue with the database itsself??

All of my tables have a GEOM field.  I noticed that whne I do an ODBC that it only recognizes WGS84 (lat lon) fields (a few have LL) and not the GEOM.

I thought I saw somewhere that this connection may need a "geography" field?  Is that why it doesn't connect?  I have 2008 R2

Much appreciated.

FYI - we are trying to create\edit landbase in ACAD Map that resides in SQL Server that other "GIS" applications can access.

[norman.yuan]

Then what is exactly the problem you are facing? With the "Data Connection" palette to connect to data with SQL Server Spatial provider ("Add SqlServer Spatial Connection"), can you actually get over the "Login" statge (so that you get a dropdown list in "Data store", which should have at least one option "Add new data store..."?

If you cannot get passed the "login" stage, what I adviced in my previous reply should apply.

If you did pass the "Login" stage (meaning AutoCAD Map deos connect to the SQL Server, and you have issue to connect an existing data store (a database with the connected SQL Server), listed in the "Data store" dropdown list, then the data store you tried to connectto may not be a valid SQL Server spatial FDO data store. Remember, in earlier AutoCAD version, there is SQL Server FDO provider and SQL Server Spatial provider. They are different in terms of their data schema/database table structure. If the existing data store you are trying to connect to with SQL Server Spatial provider was ccreated previously with SQL Server provider, or even with ODBC provider, then the data store probably cannot be connected to with SQL Server Spatial provifder. Rememner, SQL Server Spatial FDO provider was added to AutoCAD MAP much later than ODBC and SqlServer provider. When it was first time available, both SQL Server Provider and SQL Server Spatial Provider was available in the Data connection palette (since AutoCAD Map 2009?), and Autodesk removed SQL Server provider in later versions. SQL Server Spatial privider being available is because of SQL Server 2008's spatial capability.

So, if you do get passed "Login" with SQL Server Spatial" and want to connect to existing data store based on other FDO providers (ODBC, or SQL Server,even the backend data is in SQL Server), you probebly need to create new SQL Server Spatial data store and migrate data into new data store.

(Actually, I am not 100% sure on this, because I never used ODBC/SQL Server FDO provider with AutoCAD Map, only used SQL Server Spatial provider).