Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL 2008 - Geography Data Type Missing

Posted on 2011-02-21
10
Medium Priority
?
821 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 143

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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

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 43

Expert Comment

by:Eugene Z
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

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

578 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