Subscribe to this thread
Home - General / All posts - Cannot make a Manifold drawing from a SQL Server View that has joined tables - gardening is my relief
HeyGL12 post(s)
#03-Jun-24 02:00

Greetings.

So many times I hit this problem and have never solved it - I just hang up the phone and go do some gardening or rip open QGIS and hack something.

I have two joined tables as a view in SQL Server eg 'dbo.vNewView' - one has spatial data, the other is a lookup. The join includes all fields from the spatial table and a code field from the 'lookup' table.

I open this view and see all the data.

I now attempt to create a drawing from this SQL Server view. I create a drawing from the SQL Server view using the right-click Create New Drawing and a new drawing file appears.

When I open this new drawing I see nothing.

What am I missing? What am I missing in the help file that would point out my misunderstandings?

I don't want to use a Manifold Join because it's writing a new field to the target table -- this should be unnecessary, especially if I'm in a read-only database.

Why can I not create a drawing from a SQL Server view on joined tables ?

Cheers

GL

Dimitri


7,452 post(s)
#03-Jun-24 05:32

Need more detail on what you're doing. For example, you don't mention if you're working with Manifold Release 9 or Release 8. You also don't mention all the fields you have in your view, their data type, what the coordinate system is for the geometry, etc, etc.

For help topics, check out the SQL Server topic about connecting to SQL Server and making drawings. The usual reason for not seeing anything in drawings is some simple error, like not having a geometry field, specifying the wrong coordinate system, not refreshing it, etc.

HeyGL12 post(s)
#03-Jun-24 22:04

SOLVED

Thanks Dimitri

Your comment gave me confidence that what I'm trying to do is legitimate (this is always the first hurdle for the novice), and so the problem must lie deeper.

And indeed it did...

Forensics: when I compared my bad SQL view with a 'good' SQL view, I noted I was using a near-fully qualified table name - like this:

[<database name>].[<schema name>].[<table name>]

The use of [<database name>] in an external query (SQL Server view) looks to be problematic.

Perhaps this gotcha can be added to Manifold documentation somewhere - it's a case of not documenting what is required, but documenting what must be avoided (unless it's already there but I don't know how to search for it) ?

Cheers

Greg

Dimitri


7,452 post(s)
#04-Jun-24 06:06

Not exactly sure what you're doing client side or server side (need all details to know that), but it sounds like you might be running into an issue as to when you're executing Manifold SQL or SQL Server SQL, perhaps executing the wrong SQL for the context in which it is being executed. See topics like this one, and the topics it references.

HeyGL12 post(s)
#04-Jun-24 10:10

In my SQL Server I use a view to JOIN codes (eg. Code='BLUE') in a source table to their description (ie lookups) in a reference table (eg Description='Blue Train').

My view looks like this:

SELECT[Code]. [Description] ...

FROM [DatabaseName].[SchemaName].[TableName] INNER JOIN ...

;

Note I include the database name in the FROM clause - this is legitimate in SQL Server.

I then connect Manifold to this SQL Server database and this view appears in Manifold as a query (aka a SQL Server 'view').

However:

  1. Manifold will not display the content of the view because I've used the source database name in the SQL. and
  2. Manifold will create a drawing for this view but NO data appears.

If I remove the database name in the SQL Server view, like this:

SELECT[Code]. [Description] ...

FROM [SchemaName].[TableName] INNER JOIN ...

;

...then Manifold will create a drawing on this view and it WILL return a table of data AND display the drawing content.

This appears to be a subtle problem not easily covered in the documentation - it's hard to document all configurations that fail; nowhere does documentation say "don't use the database name in a SQL Server FROM clause".

Dimitri


7,452 post(s)
#04-Jun-24 12:32

nowhere does documentation say "don't use the database name in a SQL Server FROM clause".

Well, if I understand the situation in your project the documentation should not say that, because whether you should or should not use the database name in a FROM clause depends on which query engine you've told Manifold to use. If you've told Manifold to use Manifold's query engine then using alien syntax not used by Manifold's query engine is a mistake. In contrast, if you've told Manifold to use SQL Server's query engine, then it's not a mistake and you should use SQL Server specific syntax, such as using the database name if that's what SQL Server syntax is.

If you like, when working with queries involving external data sources you often have a choice of writing queries that use Manifold SQL or using the native SQL implemented within that data source. Manifold's documentation covers how to use either Manifold's or SQL Server's version of SQL depending on which one you want to use, as well as other important info such as the context within which a query is executing, etc. Start with the link I suggested to learn about all that.

When doing such things that involve a mix of server side and client side SQL you have to get all the nuances right. For example, note from Manifold's documentation that there's no such construction as [DatabaseName].[SchemaName].[TableName] in Manifold SQL. If your project is set up so a query executes within Manifold's query engine then you must use the syntax that Manifold's query engine uses.

It sounds like that's the case, that your project is set up so the query is executing within Manifold's query engine, so Manifold syntax for SQL must be used. If instead you want the query to be executed server side within SQL Server's query engine using Microsoft's syntax for SQL in SQL Server, the info in the topic I recommended should help.

HeyGL12 post(s)
#04-Jun-24 13:08

In a nutshell - brilliant, thanks.

I know I've been down this path before but not committed to memory the differences (engines) and when they apply. Time to re-read and make better notes for future reference (I call on Manifold only occasionally since most of the spatial work I do is stats in SQL)

Cheers

Greg

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.