AutoCAD Map 3D Forum
Welcome to Autodesk’s AutoCAD Map 3D Forums. Share your knowledge, ask questions, and explore popular AutoCAD Map 3D topics.
cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

SQL Server troubles...

18 REPLIES 18
SOLVED
Reply
Message 1 of 19
iam_doss
2428 Views, 18 Replies

SQL Server troubles...

Hi, I'm not a database expert but I'm trying tu use SQL Server with AutoCAD Map. I'm running Map 2010-64 bits over Windows 7 Ultimate-64 bits. It seems there is a lot of connectivity problems using Jet and ODBC on 64 bits platforms because there is not drivers from Microsoft for 64 bits programs. Well here is that I can do and that I can't do at this moment:

1. Installed SQL Server Express 2008 (I'm an absolutely newbie using and understanding SQL Server, but it seems it works fine)
2. I can create databases in SQL and Import from Access and Excel, using SQL Server Management Studio (many terms of SQL are new for me, but I can edit imported Tables, make Querys, and so on)
3. I can use SQL data links in Map, define link paths, attach records to objects, and so on, like using Jet or ODBC with Access or Excel files (except SQL Server complexity, no problems here)
4. Most problems begins using FDO
4.1 Do I need use SQL or SQL Server Spatial connection? I can connect SQL only using "sa" as user but no using Windows Authentication. I can connect SQL Spatial using also Windows Aut., but in both cases, I can't see my databases, only Create a new database
4.2 As a sample: I've an Access table with Hydrants data. Two columns of the table includes LAT and LONG of Hydrants. Is there some simple method to connect to imported table in SQL like thorugh ODBC FDO using ODBC?

Any help here using SQL Server with Map will be appreciated

Thank you in advance
18 REPLIES 18
Message 2 of 19
tjmapper
in reply to: iam_doss

I am no SQL professional myself, but i do work with it in conjunction with AutoCAD Map on a daily basis.

You will need to Use SQL Server Spatial Connection if you plan on storing your geometry in SQL.

And to be honest, the only way i have ever gotten AutoCAD to see my SQL tables is create a SHP file using the data and add a data store in autoCAD then bulk copy the SHP file into That connection. That is the only way i have ever gotten it to recognize my tables and information.

Because Autodesk has a partnership with oracle i doubt we will ever see anything formal on how to properly set up a Table in SQL so it can be viewed through our FDO connections. Maybe someone has seen some documentation on it, but I never have.
Message 3 of 19
Anonymous
in reply to: iam_doss


The main difference between the SQL Server and the
SQL Server Spatial is the spatial server connection can get the geometry of
objects and render them in Map3D. Here's a blog posting that may help you
understand a little bit of it.
href="http://map3d.wordpress.com/2007/04/24/whats-a-spatial-data-base/">http://map3d.wordpress.com/...
.

 

To get used to it and try a few things here is what
I suggest. Make a connection to a few sample SDF or shp files and bring those
into a blank dwg. Then make a connection to the SQL Spatial server and create a
new datastore name it MyTest or something. Set the coordinate system the same as
used for the SDF samples you have. Now use the bulk copy and copy the SDF
features to the SQL datastore you created. You may want to bulk copy a couple
different types of features to the SQL datastore (points,polygon & lines).
Now close out and start a new map/dwg connect to SQL Spatial server and add the
new features you copied from SDF. You can also try using the SQL Management
Studio tools to view edit and whatever to those new features.

 

I'm just now getting my Win 7 64X  workstation
set up so I can't tell you much more on it other than in XP 32x it's fun playing
around with it if you're a geek and DB nerd. 🙂


 

 


style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
Hi,
I'm not a database expert but I'm trying tu use SQL Server with AutoCAD Map.
I'm running Map 2010-64 bits over Windows 7 Ultimate-64 bits. It seems there
is a lot of connectivity problems using Jet and ODBC on 64 bits platforms
because there is not drivers from Microsoft for 64 bits programs. Well here is
that I can do and that I can't do at this moment:

1. Installed SQL
Server Express 2008 (I'm an absolutely newbie using and understanding SQL
Server, but it seems it works fine)
2. I can create databases in SQL and
Import from Access and Excel, using SQL Server Management Studio (many terms
of SQL are new for me, but I can edit imported Tables, make Querys, and so
on)
3. I can use SQL data links in Map, define link paths, attach records
to objects, and so on, like using Jet or ODBC with Access or Excel files
(except SQL Server complexity, no problems here)
4. Most problems begins
using FDO
4.1 Do I need use SQL or SQL Server Spatial connection? I can
connect SQL only using "sa" as user but no using Windows Authentication. I can
connect SQL Spatial using also Windows Aut., but in both cases, I can't see my
databases, only Create a new database
4.2 As a sample: I've an Access table
with Hydrants data. Two columns of the table includes LAT and LONG of
Hydrants. Is there some simple method to connect to imported table in SQL like
thorugh ODBC FDO using ODBC?

Any help here using SQL Server with Map
will be appreciated

Thank you in advance
Message 4 of 19
iam_doss
in reply to: iam_doss

Thank you TJmapper for your answer, I'll give a try using Bulk Copy

"And to be honest, the only way i have ever gotten AutoCAD to see my SQL tables is create a SHP file using the data and add a data store in autoCAD then bulk copy the SHP file into That connection. That is the only way i have ever gotten it to recognize my tables and information"

First, I think I need some external tool to create the SHP file from the Access file (or can I do it in Map 2010 - 64 bits?)

I use a lot connect to Access or Excel (no geographic data) via ODBC FDO to Join data to another Layers. Is it true that Autodesk can't provide us with these tools or subsitutes to work with FDO like Map 2009 32 bits ??? I can't believe it
Message 5 of 19
iam_doss
in reply to: iam_doss

Thank you Murph for your answer. Nice article. Please review my reply to TJmapper
Message 6 of 19
tjmapper
in reply to: iam_doss

You can export data to SHP files using AutoCAD map. Murph Suggests SDF but i have not been able to get those to Bulk Copy to SQL. Probably mistakes on my end.

What kind of data do you have now? SDF? Object Data?
Message 7 of 19
iam_doss
in reply to: iam_doss

Thanks TJmapper. I know I can export to SHP from objects in drawing.

In my sample, I have only a table in an Access file (mdb) and two columns of the table contents LAT & LONG of the hydrants. In Map 2009 load these hydrants, and their data, using ODBC FDO (to use as graphic FDO Layer or to Join data to another FDO Layer)
Message 8 of 19
tjmapper
in reply to: iam_doss

Well unless you still have a copy of Map 2009 on a different computer i am not sure exactly you are going to get that data to Map 2010.

Maybe a Comma Delineated file? It is hard to say not ever having to do it myself.

I vote for firing up the old PC and saving your layers to SDF and go from there.
Message 9 of 19
Anonymous
in reply to: iam_doss

Thanks, I read the replies to TJ and I'll reply with a few idea after I try
a few things.

--

Murph
http://map3d.wordpress.com/


"iam_doss" wrote in message news:6349590@discussion.autodesk.com...
Thank you Murph for your answer. Nice article. Please review my reply to
TJmapper
Message 10 of 19
iam_doss
in reply to: iam_doss

Some test after (sorry for screen copies in spanish Windows and Map):

- I see there are two ODBC Administrator, one for 32 bits applications (Windows\SysWOW64) one for 64 bits applications (Windows\System32); bizarre folder locations
- Using second one I can configure ODBC sources from SQL (see ODBC AD.JPG)
- Data connect in Map can access to these sources and read numeric columns as X and Y values (see Map data Connect.JPG, in the next post)
- I can add a Layer as always from that connection (see Map Layer.JPG, in the next post)

Some extra steps importing Access o Excel in SQL Server, but it works
Message 11 of 19
iam_doss
in reply to: iam_doss

(image 2)
Message 12 of 19
iam_doss
in reply to: iam_doss

(image 3)
Message 13 of 19
Anonymous
in reply to: iam_doss

Yes, just found the other ODBC for 32 bits, however my odcb connection to a
mdb didn't work in 2010 but does on the 2009. Win 7 64X ?

--

Murph
http://map3d.wordpress.com/


"iam_doss" wrote in message news:6349731@discussion.autodesk.com...
Some test after (sorry for screen copies in spanish Windows and Map):

- I see there are two ODBC Administrator, one for 32 bits applications
(Windows\SysWOW64) one for 64 bits applications (Windows\System32); bizarre
folder locations
- Using second one I can configure ODBC sources from SQL (see ODBC AD.JPG)
- Data connect in Map can access to these sources and read numeric columns
as X and Y values (see Map data Connect.JPG, in the next post)
- I can add a Layer as always from that connection (see Map Layer.JPG, in
the next post)

Some extra steps importing Access o Excel in SQL Server, but it works
Message 14 of 19
iam_doss
in reply to: iam_doss

I think that works this way:

- Using any ODBC Administrator (32 or 64 bits) you can see the same ODBC user sources
- Using ODBCAD 32 bits you can define ODBC sources from Access, Excel, ..., and these can be used ONLY in 32 bits applications (like Map 2009)
- Using ODBCAD 64 bits you can define ODBC sources ONLY from SQL Server, and these can be used in 64 bits native applications (like Map 2010-64)

Working, of course, a 64 bits system (XP, Vista or W7)
Message 15 of 19

Murph:

I just found out last week that there is a beta version for an MS Access ODBC driver from Win7 64 Bit - search for AccessDatabaseEngine.exe

When installing, you might have to uninstall MS Office 2007 (if it's 32 bit) first and install the DB engine and then reinstall office.

Office 2010 was released today, I haven't checked to see what it does for ODBC drivers yet.....
Message 16 of 19
Anonymous
in reply to: iam_doss

Yes there is, and a copy of it has been on my laptop for over a month now.
Just like Map3D betas I don't post anything about them in public. ; )


--

Murph
http://map3d.wordpress.com/


"toddhepworth4253" wrote in message news:6391289@discussion.autodesk.com...
Murph:

I just found out last week that there is a beta version for an MS Access
ODBC driver from Win7 64 Bit - search for AccessDatabaseEngine.exe

When installing, you might have to uninstall MS Office 2007 (if it's 32 bit)
first and install the DB engine and then reinstall office.

Office 2010 was released today, I haven't checked to see what it does for
ODBC drivers yet.....
Message 17 of 19
zalant
in reply to: Anonymous

 

To follow up on the above post about the (formerly beta) 64-bit drivers, here is a video which demonstrates just how to use them.
It may be fairly obvious, but I should point out something that I failed to mention in the video.  When creating a DSN for Excel files, choose the Excel driver, instead of the Access driver.
 
Lastly, I should also point out that this solution doesn't work for users of 64-bit Windows XP.  The 64-bit edition of Windows XP unfortunately is the one 64-bit Windows operating system for which the Microsoft OLE DB Provider for ODBC Drivers is not available. (It is included with 32-bit editions of Windows XP.)
 
(Thanks to my colleague Roy for those last two sentences!)
In case anyone needs it, here is a link to Microsoft's page for downloading the 2010 drivers:


Zac Travis
Message 18 of 19
Hirowa
in reply to: zalant

Thank you very much.

I spent nearly 10 hours to search on the help and on the net how to import the Excel database 32 bits in a AutoCAD Map 2011 64 bits (Win 7 64 bits) with ODBC, or anything else (but keeping the link between). I tried a lot of things.

Now it works. Thanks a lot, you all guys. I hope that you know than you are very usefull for some "autodidactes" users like us.

 

 

Message 19 of 19
adanbakri1997
in reply to: iam_doss

adan

Can't find what you're looking for? Ask the community or share your knowledge.

Post to forums  

Autodesk Design & Make Report

”Boost