Solved

SQL 2008 - Geography Data Type Missing

Posted on 2011-02-21
10
785 Views
Last Modified: 2012-05-11
I installed the compact edition on Windows 7 and am having trouble getting the following script to work:
IF OBJECT_ID ( 'dbo.SpatialTable', 'U' ) IS NOT NULL 
    DROP TABLE dbo.SpatialTable;
GO

CREATE TABLE SpatialTable 
    ( id int IDENTITY (1,1),
    GeogCol1 geography, 
    GeogCol2 AS GeogCol1.STAsText() );
GO

INSERT INTO SpatialTable (GeogCol1)
VALUES (geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656)', 4326));

INSERT INTO SpatialTable (GeogCol1)
VALUES (geography::STGeomFromText('POLYGON((-122.358 47.653, -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))', 4326));
GO

Open in new window


This is right from the MSDN site.  Here is what happens when I try to run the script:
Major Error 0x80040E14, Minor Error 25501
> IF OBJECT_ID ( 'dbo.SpatialTable', 'U' ) IS NOT NULL 
    DROP TABLE dbo.SpatialTable
There was an error parsing the query. [ Token line number = 1,Token line offset = 1,Token in error = IF ]
Major Error 0x80040E14, Minor Error 26302
> CREATE TABLE SpatialTable 
    ( id int IDENTITY (1,1),
    GeogCol1 geography, 
    GeogCol2 AS GeogCol1.STAsText() )
The specified data type is not valid. [ Data type (if known) = geography ]
Major Error 0x80040E14, Minor Error 25501
> INSERT INTO SpatialTable (GeogCol1)
VALUES (geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656)', 4326))
There was an error parsing the query. [ Token line number = 2,Token line offset = 9,Token in error = geography ]

Open in new window


I thought the geometry and geography types came with all versions of SQL 2008.  Do I need to re-install?

Thanks in advance...

0
Comment
Question by:gandalf97
10 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34943916
your error message does not read like it would be ms sql server, actually.
can you please run this query and show it's output:
 select @@version 

Open in new window

0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 34943953
well, reading the docs, you need Compact Edition 3.5 SP1 or higher:
http://msdn.microsoft.com/en-us/library/ms172417.aspx
0
 
LVL 2

Author Comment

by:gandalf97
ID: 34944484
I did the select and it indicated that I wasn't connected to the engine I thought I was connecting to.  I need to fix that and come back to the problem.
0
 
LVL 2

Author Comment

by:gandalf97
ID: 34944968
OK.  I found that I was unable to connect to SQLEXPRESS.  I tried uninstalling, rebooting and reinstalling.  SQL Server Management Studio can't see the instance.  I checked to see what was running and the SQLEXPRESS service was running but SQL Agent was not and there was no way to start agent.

Suggestions?


TITLE: Connect to Server
------------------------------

Cannot connect to (local).

------------------------------
ADDITIONAL INFORMATION:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=2&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

Open in new window

0
 
LVL 2

Author Comment

by:gandalf97
ID: 34944984
Additional info:

There was an error message on the install that it couldn't access my user.config file.  I am trying to duplicate that error so I can paste it here.

0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 2

Author Comment

by:gandalf97
ID: 34945240
OK...  

Here is the error I get when trying to install:

Please help.  Thanks!
See the end of this message for details on invoking 
just-in-time (JIT) debugging instead of this dialog box.

************** Exception Text **************
System.Configuration.ConfigurationErrorsException: An error occurred creating the configuration section handler for userSettings/Microsoft.SqlServer.Configuration.LandingPage.Properties.Settings: Could not load file or assembly 'System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies. The system cannot find the file specified. (C:\Users\ESchilling\AppData\Local\Microsoft_Corporation\LandingPage.exe_StrongName_ryspccglaxmt4nhllj5z3thycltsvyyx\10.0.0.0\user.config line 5) ---> System.IO.FileNotFoundException: Could not load file or assembly 'System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies. The system cannot find the file specified.
File name: 'System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'
   at System.Configuration.TypeUtil.GetTypeWithReflectionPermission(IInternalConfigHost host, String typeString, Boolean throwOnError)
   at System.Configuration.RuntimeConfigurationRecord.RuntimeConfigurationFactory.Init(RuntimeConfigurationRecord configRecord, FactoryRecord factoryRecord)
   at System.Configuration.RuntimeConfigurationRecord.RuntimeConfigurationFactory.InitWithRestrictedPermissions(RuntimeConfigurationRecord configRecord, FactoryRecord factoryRecord)
   at System.Configuration.RuntimeConfigurationRecord.CreateSectionFactory(FactoryRecord factoryRecord)
   at System.Configuration.BaseConfigurationRecord.FindAndEnsureFactoryRecord(String configKey, Boolean& isRootDeclaredHere)

WRN: Assembly binding logging is turned OFF.
To enable assembly bind failure logging, set the registry value [HKLM\Software\Microsoft\Fusion!EnableLog] (DWORD) to 1.
Note: There is some performance penalty associated with assembly bind failure logging.
To turn this feature off, remove the registry value [HKLM\Software\Microsoft\Fusion!EnableLog].

   --- End of inner exception stack trace ---
   at System.Configuration.BaseConfigurationRecord.FindAndEnsureFactoryRecord(String configKey, Boolean& isRootDeclaredHere)
   at System.Configuration.BaseConfigurationRecord.GetSectionRecursive(String configKey, Boolean getLkg, Boolean checkPermission, Boolean getRuntimeObject, Boolean requestIsHere, Object& result, Object& resultRuntimeObject)
   at System.Configuration.BaseConfigurationRecord.GetSection(String configKey)
   at System.Configuration.ClientConfigurationSystem.System.Configuration.Internal.IInternalConfigSystem.GetSection(String sectionName)
   at System.Configuration.ConfigurationManager.GetSection(String sectionName)
   at System.Configuration.ClientSettingsStore.ReadSettings(String sectionName, Boolean isUserScoped)
   at System.Configuration.LocalFileSettingsProvider.GetPropertyValues(SettingsContext context, SettingsPropertyCollection properties)
   at System.Configuration.SettingsBase.GetPropertiesFromProvider(SettingsProvider provider)
   at System.Configuration.SettingsBase.GetPropertyValueByName(String propertyName)
   at System.Configuration.SettingsBase.get_Item(String propertyName)
   at System.Configuration.ApplicationSettingsBase.GetPropertyValue(String propertyName)
   at System.Configuration.ApplicationSettingsBase.get_Item(String propertyName)
   at Microsoft.SqlServer.Configuration.LandingPage.LandingPageForm.OnLoad(EventArgs e)
   at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
   at System.Windows.Forms.Control.CreateControl()
   at System.Windows.Forms.Control.WmShowWindow(Message& m)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)


************** Loaded Assemblies **************
mscorlib
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.4952 (win7RTMGDR.050727-4900)
    CodeBase: file:///C:/Windows/Microsoft.NET/Framework64/v2.0.50727/mscorlib.dll
----------------------------------------
LandingPage
    Assembly Version: 10.0.0.0
    Win32 Version: 10.50.1600.1 ((KJ_RTM).100402-1539 )
    CodeBase: file:///c:/57e225472b1390f543b952b93bc89615/x64/LandingPage.exe
----------------------------------------
System.Windows.Forms
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.4927 (NetFXspW7.050727-4900)
    CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System.Windows.Forms/2.0.0.0__b77a5c561934e089/System.Windows.Forms.dll
----------------------------------------
System
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.4927 (NetFXspW7.050727-4900)
    CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System/2.0.0.0__b77a5c561934e089/System.dll
----------------------------------------
System.Drawing
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.4927 (NetFXspW7.050727-4900)
    CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System.Drawing/2.0.0.0__b03f5f7f11d50a3a/System.Drawing.dll
----------------------------------------
Microsoft.SqlServer.Configuration.Sco
    Assembly Version: 10.0.0.0
    Win32 Version: 10.50.1600.1 ((KJ_RTM).100402-1539 )
    CodeBase: file:///c:/57e225472b1390f543b952b93bc89615/x64/Microsoft.SqlServer.Configuration.Sco.DLL
----------------------------------------
Microsoft.SqlServer.Chainer.Infrastructure
    Assembly Version: 10.0.0.0
    Win32 Version: 10.50.1600.1 ((KJ_RTM).100402-1539 )
    CodeBase: file:///c:/57e225472b1390f543b952b93bc89615/x64/Microsoft.SqlServer.Chainer.Infrastructure.DLL
----------------------------------------
System.Xml
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.4927 (NetFXspW7.050727-4900)
    CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System.Xml/2.0.0.0__b77a5c561934e089/System.Xml.dll
----------------------------------------
Accessibility
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.4927 (NetFXspW7.050727-4900)
    CodeBase: file:///C:/Windows/assembly/GAC_MSIL/Accessibility/2.0.0.0__b03f5f7f11d50a3a/Accessibility.dll
----------------------------------------
Microsoft.SqlServer.Management.Controls
    Assembly Version: 10.0.0.0
    Win32 Version: 10.50.1600.1 ((KJ_RTM).100402-1539 )
    CodeBase: file:///c:/57e225472b1390f543b952b93bc89615/x64/Microsoft.SqlServer.Management.Controls.DLL
----------------------------------------
System.Configuration
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.4927 (NetFXspW7.050727-4900)
    CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System.Configuration/2.0.0.0__b03f5f7f11d50a3a/System.Configuration.dll
----------------------------------------

************** JIT Debugging **************
To enable just-in-time (JIT) debugging, the .config file for this
application or computer (machine.config) must have the
jitDebugging value set in the system.windows.forms section.
The application must also be compiled with debugging
enabled.

For example:

<configuration>
    <system.windows.forms jitDebugging="true" />
</configuration>

When JIT debugging is enabled, any unhandled exception
will be sent to the JIT debugger registered on the computer
rather than be handled by this dialog box.

Open in new window

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34945285
well, that starts out to get completely different issue :)
to connect to an instance, I had written this article to start troubleshooting:
http://www.experts-exchange.com/A_1881.html
0
 
LVL 2

Author Comment

by:gandalf97
ID: 34945434
Yes...  it sure does.  Isn't it funny how that happens sometimes?  I have never had this much trouble in other versions.  I will check out the link and get to the troubleshooting.  Hopefully it won't open too many more cans of worms.
0
 
LVL 15

Expert Comment

by:jorge_toriz
ID: 34948308
The error message that you posted indicates that you must install Framework 4.0 before continue...
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 34959740
make sure that you installed sql server express 2008 - not 2005 edition (and if it is not sql R2 - do not forget for at least sp1..)
SQL Server 2008 R2Express
http://www.microsoft.com/express/Database/

www.microsoft.com/sqlserver/2008/en/us/express.aspx

--
you may need to check this one
How to uninstall an instance of SQL Server 2005 manually
http://support.microsoft.com/kb/909967
---

BTW: Sql express 2008     supports the geometry and geography data types for storing spatial data
http://www.microsoft.com/sqlserver/2008/en/us/editions-compare.aspx
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now