QGIS » QGIS Application

Tested on version 1.9.90-Alpha

The error message should have of course been:

dbname='CMSI' host=JAMESP-THINK srid=27700 type=POLYGON table="dbo"."vSitesGeom" (Geom) sql= is an invalid layer and cannot be loaded.

(I was trying a variety of views - some of which worked - some didn't - but I can't (yet) see a pattern. I pasted a mismatch error with the view - apologies)

A bit more information - hope this helps:

OK - I set up a really simple view - I have a table SitesGeom that is displaying correctly - so I don't think its a data issue

I created a view on this table that simply dropped a number of columns and called it SitesTest
(SELECT SiteCode, Geom AS MyGeom FROM dbo.SitesGeom)

I made sure all entries were in geometry_columns table (same settings for base table and view)

When I tried to add table - I watched in profiler and got:

select f_geometry_column, coord_dimension, srid, geometry_type from geometry_columns where f_table_schema = 'dbo' and f_table_name = 'SitesTest'
exec sp_columns N'SitesTest', NULL, NULL, NULL, NULL
select [MyGeom] from [dbo].[SitesTest]

I didn't get this SQL (which I do for the base table)
select srtext from spatial_ref_sys where srid = 27700

So presumably problem is between those 2 statements. All SQL in profiler seemed valid.

Hope this helps

James Perrins wrote:

A bit more information - hope this helps:

OK - I set up a really simple view - I have a table SitesGeom that is displaying correctly - so I don't think its a data issue

I created a view on this table that simply dropped a number of columns and called it SitesTest
(SELECT SiteCode, Geom AS MyGeom FROM dbo.SitesGeom)

I made sure all entries were in geometry_columns table (same settings for base table and view)

When I tried to add table - I watched in profiler and got:

select f_geometry_column, coord_dimension, srid, geometry_type from geometry_columns where f_table_schema = 'dbo' and f_table_name = 'SitesTest'
exec sp_columns N'SitesTest', NULL, NULL, NULL, NULL
select [MyGeom] from [dbo].[SitesTest]

I didn't get this SQL (which I do for the base table)
select srtext from spatial_ref_sys where srid = 27700

So presumably problem is between those 2 statements. All SQL in profiler seemed valid.

Hope this helps

Could you test the same thing using https://github.com/szekerest/Quantum-GIS ?
There have been a couple of changes recently which may affect this issue

I can try - is there a Windows build in there somewhere - or is it just source ?

In which case presumably I need to set up a build environment - which I don't have at the moment (but was planning on trying to get going in the next couple of weeks ).

I assume its not quite as straight forward as simply downloading git repository and hitting build in VS 2008 ?

Thanks

James Perrins wrote:

I can try - is there a Windows build in there somewhere - or is it just source ?

In which case presumably I need to set up a build environment - which I don't have at the moment (but was planning on trying to get going in the next couple of weeks ).

I assume its not quite as straight forward as simply downloading git repository and hitting build in VS 2008 ?

Thanks

Uploaded a Windows build here: http://www.gisinternals.com/tests/qgis1.9.90.zip

Same error with your latest build I'm afraid

James Perrins wrote:

Same error with your latest build I'm afraid

Do you have an integer column with unique values in the view?

Yes - unique integer field is present.

Same view works well in your mapserver sql2008 plugin

CREATE TABLE [dbo].[SitesGeom](
[guid] [uniqueidentifier] NOT NULL,
[SiteCode] [nvarchar](10) NOT NULL,
[geom] [geometry] NULL,
[VersionNumber] [timestamp] NOT NULL,
[MI_PRINX] [int] IDENTITY NOT NULL,
[Modified] [datetime] NOT NULL,
[ModifiedBy] [nvarchar](255) NOT NULL,
[Created] [datetime] NOT NULL,
[CreatedBy] [nvarchar](255) NOT NULL,
[xgLockedBy] [nvarchar](50) NULL,
[xgGeometryType] [int] NULL,
[GISType] [int] NOT NULL,
CONSTRAINT [PK_SitesGeom] PRIMARY KEY CLUSTERED
(
[guid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_SitesGeom] UNIQUE NONCLUSTERED
(
[SiteCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

James Perrins wrote:

Yes - unique integer field is present.

Same view works well in your mapserver sql2008 plugin

CREATE TABLE [dbo].[SitesGeom](
[guid] [uniqueidentifier] NOT NULL,
[SiteCode] [nvarchar](10) NOT NULL,
[geom] [geometry] NULL,
[VersionNumber] [timestamp] NOT NULL,
[MI_PRINX] [int] IDENTITY NOT NULL,
[Modified] [datetime] NOT NULL,
[ModifiedBy] [nvarchar](255) NOT NULL,
[Created] [datetime] NOT NULL,
[CreatedBy] [nvarchar](255) NOT NULL,
[xgLockedBy] [nvarchar](50) NULL,
[xgGeometryType] [int] NULL,
[GISType] [int] NOT NULL,
CONSTRAINT [PK_SitesGeom] PRIMARY KEY CLUSTERED
(
[guid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_SitesGeom] UNIQUE NONCLUSTERED
(
[SiteCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

How is your view definition looking like?

OK - a bit more...

This view works:

SELECT SiteCode, Geom AS MyGeom, MI_PRINX
FROM dbo.SitesGeom

In my attempt to simplify I had removed the unique integer field by mistake - stupid - sorry...)

This is the real view that I was trying to use (and still fails). It works fine in mapserver. It appears OK and correctly identifies SRID and that it contains polygons - but trying to add to map gives error above:

CREATE VIEW [dbo].[vSitesGeom]
AS
SELECT dbo.Sites.SiteCode, dbo.Sites.SiteName, dbo.SitesGeom.Geom, dbo.SitesGeom.MI_PRINX, dbo.SiteTree.SiteTreeName
FROM dbo.Sites INNER JOIN
dbo.SitesGeom ON dbo.Sites.SiteCode = dbo.SitesGeom.SiteCode LEFT OUTER JOIN
dbo.SiteTree ON dbo.Sites.SiteTreeCodeParent = dbo.SiteTree.SiteTreeCode

In case its useful the SiteTree table that is joined in view above is:

CREATE TABLE [dbo].[SiteTree](
[SiteTreeCode] [nvarchar](10) NOT NULL,
[ParentSiteTreeCode] [nvarchar](10) NULL,
[SiteTreeName] [nvarchar](100) NULL,
[SiteTreeOrder] [int] NULL,
[VersionNumber] [timestamp] NOT NULL,
[Modified] [datetime] NOT NULL,
[ModifiedBy] [nvarchar](255) NOT NULL,
[Created] [datetime] NOT NULL,
[CreatedBy] [nvarchar](255) NOT NULL,
[guid] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_SiteTree] PRIMARY KEY NONCLUSTERED
(
[guid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [LogicalKey_SiteTree] UNIQUE CLUSTERED
(
[SiteTreeCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[SiteTree] WITH CHECK ADD CONSTRAINT [FK_SiteTree_SiteTree] FOREIGN KEY
REFERENCES [dbo].[SiteTree] ([SiteTreeCode])
GO

ALTER TABLE [dbo].[SiteTree] CHECK CONSTRAINT [FK_SiteTree_SiteTree]
GO

ALTER TABLE [dbo].[SiteTree] ADD CONSTRAINT [DF__SiteTree__Modifi__29EC2402] DEFAULT (getdate()) FOR [Modified]
GO

ALTER TABLE [dbo].[SiteTree] ADD CONSTRAINT [DF__SiteTree__Modifi__2AE0483B] DEFAULT (user_name()) FOR [ModifiedBy]
GO

ALTER TABLE [dbo].[SiteTree] ADD CONSTRAINT [DF__SiteTree__Create__2BD46C74] DEFAULT (getdate()) FOR [Created]
GO

ALTER TABLE [dbo].[SiteTree] ADD CONSTRAINT [DF__SiteTree__Create__2CC890AD] DEFAULT (user_name()) FOR [CreatedBy]
GO

ALTER TABLE [dbo].[SiteTree] ADD CONSTRAINT [DF__SiteTree__guid__61074EC2] DEFAULT (newsequentialid()) FOR [guid]
GO

Are you sure the left outer join on the view definition doesn't affect the uniqueness of the MI_PRINX column?
If you could provide some test data, that would be helpful to reproduce this behaviour.

James Perrins wrote:

I don't think the join makes it non unique - as I say - it definitely works in map server - wouldn't that have the same issue ?

SQL script that will create tables and data in your db attached

Many Thanks

Tested with your data and works for me. Are you sure you use the latest version?

This appears to now work in 1.9-0 Master
QGIS code revision
829f672

Thanks