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: 

Connecting to SQL Server 2008 in Map3d 2009

22 REPLIES 22
Reply
Message 1 of 23
dvmorris
684 Views, 22 Replies

Connecting to SQL Server 2008 in Map3d 2009

I am trying to connect to a local instance of SQL Server 2008, and I can't seem to figure out what "service name" is actually referring to in the connect dialog in Map3d 2009.

Is this the IP address? should i use "localhost"? Should I use the name of the windows service (MSSQLSERVER)? There is no documentation that I can find anywhere that says what this actually means, and I can't seem to find anyone on the internet who has actually gotten this to work. I saw a video on channel9.msdn.com where the Autodesk guy did it, but the screen was so blurry I couldn't tell what he was typing in.

Also, does this allow me to use Windows Authentication to connect? Or do I have to create a SQLServer user account? The documentation seems to be specific to ORACLE, and generic for everything else. It's extremely confusing, fyi for any Autodesk technical writers reading this. I'm not a total idiot when it comes to connecting to databases etc... and I am completely stuck.

Anyways, I'm desperate now, this thing is driving me crazy. Also, the bulk copy command doesn't seem to work on sql server. I was able to connect to the SQL Server instance by creating a system DSN and using the ODBC driver, but it doesn't seem to recognize my geography data in my database. It only recognizes a table where I have a field called latitude and one called longitude.

Any help would be greatly appreciated. This is a Windows Server 2003 box with SQL Server 2008, Visual Studio 2008, Autodesk Map 3d 2009 with sp1, etc...
22 REPLIES 22
Message 2 of 23
Anonymous
in reply to: dvmorris

The service used in the demo was "Localhost". Make sure that you have the
provider for SQLServer 2008 installed, IIRC the provider for SQL Server
shipped with R2009 is not the one for the 2008 server.

--
Murph
www.map3d.wordpress.com


"dvmorris" wrote in message news:5970323@discussion.autodesk.com...
I am trying to connect to a local instance of SQL Server 2008, and I can't
seem to figure out what "service name" is actually referring to in the
connect dialog in Map3d 2009.

Is this the IP address? should i use "localhost"? Should I use the name of
the windows service (MSSQLSERVER)? There is no documentation that I can find
anywhere that says what this actually means, and I can't seem to find anyone
on the internet who has actually gotten this to work. I saw a video on
channel9.msdn.com where the Autodesk guy did it, but the screen was so
blurry I couldn't tell what he was typing in.

Also, does this allow me to use Windows Authentication to connect? Or do I
have to create a SQLServer user account? The documentation seems to be
specific to ORACLE, and generic for everything else. It's extremely
confusing, fyi for any Autodesk technical writers reading this. I'm not a
total idiot when it comes to connecting to databases etc... and I am
completely stuck.

Anyways, I'm desperate now, this thing is driving me crazy. Also, the bulk
copy command doesn't seem to work on sql server. I was able to connect to
the SQL Server instance by creating a system DSN and using the ODBC driver,
but it doesn't seem to recognize my geography data in my database. It only
recognizes a table where I have a field called latitude and one called
longitude.

Any help would be greatly appreciated. This is a Windows Server 2003 box
with SQL Server 2008, Visual Studio 2008, Autodesk Map 3d 2009 with sp1,
etc...
Message 3 of 23
dvmorris
in reply to: dvmorris

I installed the newest version of the provider for sqlserver 2008, but I still am unable to connect to my database. The connection string is typed in all asterisks so it's difficult to troubleshoot that right in the interface.

Here is what my connection string looks like:

Provider=SQLNCLI.1;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=SpatialTest

I would like to use windows authentication, but I went ahead and made a sql server account called dave with a password, and it also doesn't seem to work. I attached a jpg of the error message...

Any ideas? Thank you for your help,

Dave
Message 4 of 23
JJuniewich
in reply to: dvmorris

Hope you made a copy of your provider.xml as you need to edit the original section of the SQLServer section. Take a look at the attached doc. It uses the new provider with the same FDO UI. This lets you create datastores from within Map. Hopes this helps you out.
Message 5 of 23
chrisgountanis
in reply to: dvmorris

Where do you get these Spatial DLL files? I was assuming that you can get them from OSGEO in the latest FDO SDK. Is this correct?
Message 6 of 23
dvmorris
in reply to: dvmorris

I don't think I made a copy of my providers.xml file. Could you paste in the entire xml entry for the original SQL Server Provider ? I only see the new SQL Spatial provider in my providers.xml file.
Message 7 of 23
chrisgountanis
in reply to: dvmorris

Nice passing over my simple question but I will still help you.


Autodesk.SqlServer.3.3
Autodesk FDO Provider for SQL Server
Read/write access to feature data in a MS SQL Server-based data store. Supports geospatial and nongeospatial data and schema R/W access.
False
3.3.0.0
3.3.0.0
SqlServerProvider.dll
Message 8 of 23
chrisgountanis
in reply to: dvmorris

Original Map 2009 Providers.xml file attached.
Message 9 of 23
dvmorris
in reply to: dvmorris

I'm getting an error when i try to login as sa. see attached image.
Message 10 of 23
chrisgountanis
in reply to: dvmorris

MY guess is you don't have mixed mode on (requires sql server restart) or bad password. You also want to check your service address. For example you have to include the instance name.

Here is mine: COMPUTERNAMEHERE\SQL2008RC0
Message 11 of 23
dvmorris
in reply to: dvmorris

I am ready to give up with this product. It's making me feel like a complete idiot that I can't connect to the server. It's on the same machine, the service name is MSSQLSERVER, the password for the sa account is correct, I don't know what else to try. Does it really need to be this difficult?
Message 12 of 23
JJuniewich
in reply to: dvmorris

You will have to uninstall and re-install SQL Server 2008. You want to install using the SQL Server and Windows Authentication (Mixed) Mode. Remember the new instance name and your password. Attached is a MS doc explaining the authentication. Make sure you have the Spatial dll's from OSGeo for SQL Server Spatial 2008.
Message 13 of 23
chrisgountanis
in reply to: dvmorris

Try your computer name the the service name if you installed sql server 2008 as the default instance. Otherwise it will be computername\instancename
Message 14 of 23
chrisgountanis
in reply to: dvmorris

You can change the Mode using SQL Server Studio Manager if you can find Studio 2008 on your install disk.

Attached is mine working but I cant get bulk copy wo work still. (another post)
Message 15 of 23
JJuniewich
in reply to: dvmorris

Take a look at the attached jpg. It shows Map 09 with the new SQL Server 2008 Spatial data type (geometry). All vector data in the jpg is stored in SQL Server 2008.
Message 16 of 23
chrisgountanis
in reply to: dvmorris

Where do you get the new DLL files for SQL Server 2008? Also, how did you push the data to SQL Server? Message was edited by: cgountanis
Message 17 of 23
dvmorris
in reply to: dvmorris

It looks like the issue was the service name. If anyone out there works in the UI or User Experience department, I would seriously consider documenting this and changing the field names in the UI. This concept of "Service Name" is really confusing, especially when it's on the local machine, you are supposed to use the computer name instead. Is this documented anywhere? I hope this post gets indexed by google, because it really shouldn't take a week to figure this out.

Also, the concept of a Data Store vs. a database? What is the difference? I have databases with spatial data in them, and Map 3d can't see them, it will only allow me to create new "data stores".
Message 18 of 23
JJuniewich
in reply to: dvmorris

Make sure your using the right dll's the SqlServerProvider.dll
will not work on spatial data types. You need the SqlServerSpatialProvider.dll. Copy the dll's into the bin folder and the rename the SqlServerProvider.dll in the providers.xml file to SqlServerSpatialProvider.dll. It will use the same UI in the data connect but use the new spatial provider.


l
Message 19 of 23
chrisgountanis
in reply to: dvmorris

How do you import data into SQL Server from existing data? Bulk copy does not work? Did you have a complete spatial database to test with? I really need to research batch copy for clients.

Thanks!
Message 20 of 23
dvmorris
in reply to: dvmorris

I would also like to get a sample database to learn how to get bulk copy to work. I would really like to use the technology at a client, and I'm running into too many road blocks so far.

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

Post to forums  

Autodesk Design & Make Report

”Boost