link data fails because of extra quote mark (odbc ole db)

[Dave xxiii]

I am attempting to link data to shape, and it fails this this error:

Error 80004005: ERROR: relation "public"."assets" does not exist;
Error while executing the query

Turning on tracing in the odbc driver and examing it reveals that the query
sent to the database actually looks like:

"SELECT * FROM "public"".""assets""

The period itself seems to be quoted, which seems to be causing the failure.

Reverse engineering the database in visio using the same driver works fine,
as does importing data from it with excel 2003. But I cannot link shapes to
data.

 

[Al Edlund]

since the version isn't mentioned, I have to assume that this is v2007. You
didn't mention the database you are running against (or it's version), how
you are attempting to link, and a few other issues that really are
important.
al

 

[Dave xxiii]

Yes, its Visio 2007, sorry about not mentioning that (link data is also a new
feature in 2007)
postgresql 8.1.1
data->link data to shape
select Other OLEDB or ODBC data source
select ODBC DSN
select (preexisting) DSN connection from list of ODBC data sources
uncheck "Connect to a specific table" (although the problem still occurs if
a specific table is selected)
save the data connection file.
this file then appears pre-selected in "What connectino do you want to use?"
dialog, hit next.
get a select table dialog, where I selec the table I want, hit OK.
get "Microsoft Office Visio cannot connect to this data source."
hit "More Info"
get Error 80004005: ERROR: relation "public"."assets" does not exist;
Error while executing the query

Turning on full tracing in the odbc driver (psqlodbc) reveals queries of the
form
public"."assets and "public"".""assets" being sent to the database.

I have taken the issue up with the psqlodbc people, and they point to the
quote marks around the . as the problem.

Also, it appears that Visio 2007 uses a OLE DB for ODBC driver to talk to
odbc instead of talking to odbc directly.

Reverse engineer in visio 2007 using the same driver (psqlodbc) works (I
don't know if this function goes through OLE DB or directly to ODBC however).

Importing data from the same table via Excel 2003 works (also unknown if
that is using the odbc driver directly, or going through ole db).

Also, this driver (psqlodbc) is provided in an ANSI and a UNICODE version, I
have tried both, with the same results.

 

[Al Edlund]

I suspect you got caught by a data validation rule with your table name.
Visio2007 has a couple of gotcha's like that. Your observation of v2007
calling odbc via oledb directly may be true since there is an ado type of
feel to the api's. What I did in a current applicaion was to read the table
in as a datatable and then use the sdk .net ado conversion to get an xml
stream. There is an xml load function in datarecordsets.

Here's an example of the name checking in access, although sql server will
allow it.

'
A set of specific rules for naming Microsoft Access objects. In Microsoft
Access, names can be up to 64 characters long and can include any
combination of letters, numbers, spaces, and special characters except a
period (.), an exclamation point (!), an accent grave (`), and brackets
([ ]). Note that you also can't use leading spaces or control characters
(ASCII values 0 to 31). For information on Visual Basic naming conventions,
search the Help index for "naming conventions

al

 

[Dave xxiii]

I'm not sure how this would apply here. The table is just named (in this case):

assets

All lower case letters, no special characters.

I also created a minimal test database:
create database test;
\c test
create table atable(
id integer;
goo varchar;
)

insert into atable (id,goo) values (1,"one");
insert into atable (id,goo) values (2,"two");
insert into atable (id,goo) values (3,"three");

and then created an odbc datasource for it, and then tried to access it with
visio, and it also failed in the same way.

 

[Al Edlund]

dave,
sorry, I thought the table name was "private.assets". A couple of things I'd
do for isolating the fault would be
a.) try to link to the exernal data via odbc through access (to see if I can
link to the data and that it isn't an odbc failure)
b.) can you share the odbc setup, and how you are calling it in your code?
I'd like to see if it fails the same way against a different platform.

al

 

[Dave xxiii]

Yes it works via access 2003 (I don't have access 2007).
It also works in excel 2003

And, i'm not calling it in any code, i'm just trying to link data to shape
in visio.

I have also just tried reverse engineer database in visio, which works fine
when I go through the odbc driver, but when I go through the ole db for odbc
driver, visio crashes:

AppName: visio.exe AppVer: 12.0.4518.1014 AppStamp:454281f4
ModName: ixoledb.pdl ModVer: 12.0.4518.1014 ModStamp:45419839
fDebug: 0 Offset: 000094db

The exception code is 0xc000005

Unfortunatly, the link data to shape feature does not offer the option to go
directly through odbc, you have to go through ole db for odbc.

 

[Dave xxiii]

I have now tried the microsoft odbc text driver, which shows a similar
symptom. I'm pretty sure at this point that its a problem in either visio, or
the ole db provider for odbc.

details:
c:\odbctest.txt:
id,name
1,"wendy"
2,"tom"
3,"fred"

User Data Sources:
text test Microsoft Text Driver (*.txt; *.csv):
Data Source Name: text test
Directory: C:\
Other options left at defaults.

In Visio 2007:
NEW select detailed network diagram
Data->link data to shapes
select Other OLEDB or ODBC data source
click next
select ODBC DSN
click next
select text test
click next
select odbctest.txt
click next
click finish (let it save connection with default generated name,
odbctest.txt.odc)
this is now selected in what connection do you want to use dialog, click next
Visio generates:
Microsoft Office Visio cannot connect to this data source.
click More Info
Error 80040e14: [Microsoft][ODBC Text Driver] Syntax error in FROM clause.

I turned on ODBC global tracing before doing this and the relevent lines
from the log read thusly:

WCHAR * 0x049B3920 [ 36] "SELECT * FROM
`C:\``\``odbctest.txt`"
SDWORD 36

DIAG [37000] [Microsoft][ODBC Text Driver] Syntax error in FROM clause.
(-3506)

Note that it individually quoted the \ character, similar to when it
individually quotes the . character between the schema name and the table
name in my earlier example.

This now shows the problem with two totally separate odbc drivers, in the
second case one provided by microsoft.

Question: do they monitor these groups, or is there anywhere better to
report these sorts of things without using up a support incident?

 

[Al Edlund]

they do occasionally monitor the forums, but this is still an 'informal'
support mechanism. Formal / tracked support would be via an incident.
al

 

[Al Edlund]

Dave,

the problem appears to be in the data connection wizard. Even though the
ODBC DSN is being selected, when it creates the odc connect file it is
putting OLEDB into the file. Here's an example of the failing xml


<html xmlns="urn:schemas-microsoft-comfficeffice"
xmlns="http://www.w3.org/TR/REC-html40">

<head>
<meta http-equiv=Content-Type content="text/x-ms-odc; charset=utf-8">
<meta name=ProgId content=ODC.Table>
<meta name=SourceType content=OLEDB>
<meta name=Catalog content="C:\TEMP">
<meta name=Table content=odbctest.txt>
<title>test.odbctest.txt</title>
<xml id=docprops><oocumentProperties
xmlns="urn:schemas-microsoft-comfficeffice"
xmlns="http://www.w3.org/TR/REC-html40">
<oescription>test connect to odbc source</oescription>
<o:Name>test.odbctest.txt</o:Name>
</oocumentProperties>
</xml><xml id=msodc><odc:OfficeDataConnection
xmlnsdc="urn:schemas-microsoft-comfficedc"
xmlns="http://www.w3.org/TR/REC-html40">
<odc:Connection odc:Type="OLEDB">
<odc:ConnectionString>Provider=MSDASQL.1;Persist Security
Info=True;Extended
Properties=&quot;DSN=odbctest;DefaultDir=C:\TEMP;DriverId=27;FIL=text;MaxBufferSize=2048;PageTimeout=5;&quot;;Initial
Catalog=C:\TEMP</odc:ConnectionString>
<odc:CommandType>Table</odc:CommandType>
<odc:CommandText>`C:\TEMP`\`odbctest.txt`</odc:CommandText>
</odc:Connection>
<odc:Connection odc:Type="ODBC">
<odc:ConnectionString>DSN=odbctest;DefaultDir=C:\TEMP;DriverId=27;FIL=text;MaxBufferSize=2048;PageTimeout=5;</odc:ConnectionString>
<odc:CommandText>SELECT * FROM `C:\TEMP`\`odbctest.txt`</odc:CommandText>
</odc:Connection>
</odc:OfficeDataConnection>
</xml>


when I change the OLEDB references to ODBC the import works correctly. Here
are the corrections

<html xmlns="urn:schemas-microsoft-comfficeffice"
xmlns="http://www.w3.org/TR/REC-html40">

<head>
<meta http-equiv=Content-Type content="text/x-ms-odc; charset=utf-8">
<meta name=ProgId content=ODC.Table>
<meta name=SourceType content=ODBC>
<meta name=Catalog content="C:\TEMP">
<meta name=Table content=odbctest.txt>
<title>test.odbctest.txt</title>
<xml id=docprops><oocumentProperties
xmlns="urn:schemas-microsoft-comfficeffice"
xmlns="http://www.w3.org/TR/REC-html40">
<oescription>test connect to odbc source</oescription>
<o:Name>test.odbctest.txt</o:Name>
</oocumentProperties>
</xml><xml id=msodc><odc:OfficeDataConnection
xmlnsdc="urn:schemas-microsoft-comfficedc"
xmlns="http://www.w3.org/TR/REC-html40">
<odc:Connection odc:Type="ODBC">
<odc:ConnectionString>Provider=MSDASQL.1;Persist Security
Info=True;Extended
Properties=&quot;DSN=odbctest;DefaultDir=C:\TEMP;DriverId=27;FIL=text;MaxBufferSize=2048;PageTimeout=5;&quot;;Initial
Catalog=C:\TEMP</odc:ConnectionString>
<odc:CommandType>Table</odc:CommandType>
<odc:CommandText>`C:\TEMP`\`odbctest.txt`</odc:CommandText>
</odc:Connection>
<odc:Connection odc:Type="ODBC">
<odc:ConnectionString>DSN=odbctest;DefaultDir=C:\TEMP;DriverId=27;FIL=text;MaxBufferSize=2048;PageTimeout=5;</odc:ConnectionString>
<odc:CommandText>SELECT * FROM `C:\TEMP`\`odbctest.txt`</odc:CommandText>
</odc:Connection>
</odc:OfficeDataConnection>
</xml>


Al

I have now tried the microsoft odbc text driver, which shows a similar
symptom. I'm pretty sure at this point that its a problem in either visio,
or
the ole db provider for odbc.

details:
c:\odbctest.txt:
id,name
1,"wendy"
2,"tom"
3,"fred"

User Data Sources:
text test Microsoft Text Driver (*.txt; *.csv):
Data Source Name: text test
Directory: C:\
Other options left at defaults.

In Visio 2007:
NEW select detailed network diagram
Data->link data to shapes
select Other OLEDB or ODBC data source
click next
select ODBC DSN
click next
select text test
click next
select odbctest.txt
click next
click finish (let it save connection with default generated name,
odbctest.txt.odc)
this is now selected in what connection do you want to use dialog, click
next
Visio generates:
Microsoft Office Visio cannot connect to this data source.
click More Info
Error 80040e14: [Microsoft][ODBC Text Driver] Syntax error in FROM clause.

I turned on ODBC global tracing before doing this and the relevent lines
from the log read thusly:

WCHAR * 0x049B3920 [ 36] "SELECT * FROM
`C:\``\``odbctest.txt`"
SDWORD 36

DIAG [37000] [Microsoft][ODBC Text Driver] Syntax error in FROM clause.
(-3506)

Note that it individually quoted the \ character, similar to when it
individually quotes the . character between the schema name and the table
name in my earlier example.

This now shows the problem with two totally separate odbc drivers, in the
second case one provided by microsoft.

Question: do they monitor these groups, or is there anywhere better to
report these sorts of things without using up a support incident?

 

[Jamie Colpean]

I am evaluating Vision 2007. I created an external data link to an ODBC DSN
that is setup on my XP laptop. I can use Excel 2003 and perform an import
using this DSN. However, when I link to this DSN using Visio 2007 I receive
an error:

Error 80040e14: [Lotus][ODBC Lotus Notes]Table reference has to be a table
name or an outer join escape clause in a FROM clause

When I configure this data link...and .odc file is created with the settings.

Following the advice of changing the OLEDB references to ODBC...This
actually allowed the data connection to be established...and the rows of data
from the DSN source were present in VISIO 2007 External Data
Windows...HOWEVER...NONE of the rows were present.

Jamie

Dave,

the problem appears to be in the data connection wizard. Even though the
ODBC DSN is being selected, when it creates the odc connect file it is
putting OLEDB into the file. Here's an example of the failing xml


<html xmlns="urn:schemas-microsoft-comfficeffice"
xmlns="http://www.w3.org/TR/REC-html40">

<head>
<meta http-equiv=Content-Type content="text/x-ms-odc; charset=utf-8">
<meta name=ProgId content=ODC.Table>
<meta name=SourceType content=OLEDB>
<meta name=Catalog content="C:\TEMP">
<meta name=Table content=odbctest.txt>
<title>test.odbctest.txt</title>
<xml id=docprops><oocumentProperties
xmlns="urn:schemas-microsoft-comfficeffice"
xmlns="http://www.w3.org/TR/REC-html40">
<oescription>test connect to odbc source</oescription>
<o:Name>test.odbctest.txt</o:Name>
</oocumentProperties>
</xml><xml id=msodc><odc:OfficeDataConnection
xmlnsdc="urn:schemas-microsoft-comfficedc"
xmlns="http://www.w3.org/TR/REC-html40">
<odc:Connection odc:Type="OLEDB">
<odc:ConnectionString>Provider=MSDASQL.1;Persist Security
Info=True;Extended
Properties="DSN=odbctest;DefaultDir=C:\TEMP;DriverId=27;FIL=text;MaxBufferSize=2048;PageTimeout=5;";Initial
Catalog=C:\TEMP</odc:ConnectionString>
<odc:CommandType>Table</odc:CommandType>
<odc:CommandText>`C:\TEMP`\`odbctest.txt`</odc:CommandText>
</odc:Connection>
<odc:Connection odc:Type="ODBC">
<odc:ConnectionString>DSN=odbctest;DefaultDir=C:\TEMP;DriverId=27;FIL=text;MaxBufferSize=2048;PageTimeout=5;</odc:ConnectionString>
<odc:CommandText>SELECT * FROM `C:\TEMP`\`odbctest.txt`</odc:CommandText>
</odc:Connection>
</odc:OfficeDataConnection>
</xml>


when I change the OLEDB references to ODBC the import works correctly. Here
are the corrections

<html xmlns="urn:schemas-microsoft-comfficeffice"
xmlns="http://www.w3.org/TR/REC-html40">

<head>
<meta http-equiv=Content-Type content="text/x-ms-odc; charset=utf-8">
<meta name=ProgId content=ODC.Table>
<meta name=SourceType content=ODBC>
<meta name=Catalog content="C:\TEMP">
<meta name=Table content=odbctest.txt>
<title>test.odbctest.txt</title>
<xml id=docprops><oocumentProperties
xmlns="urn:schemas-microsoft-comfficeffice"
xmlns="http://www.w3.org/TR/REC-html40">
<oescription>test connect to odbc source</oescription>
<o:Name>test.odbctest.txt</o:Name>
</oocumentProperties>
</xml><xml id=msodc><odc:OfficeDataConnection
xmlnsdc="urn:schemas-microsoft-comfficedc"
xmlns="http://www.w3.org/TR/REC-html40">
<odc:Connection odc:Type="ODBC">
<odc:ConnectionString>Provider=MSDASQL.1;Persist Security
Info=True;Extended
Properties="DSN=odbctest;DefaultDir=C:\TEMP;DriverId=27;FIL=text;MaxBufferSize=2048;PageTimeout=5;";Initial
Catalog=C:\TEMP</odc:ConnectionString>
<odc:CommandType>Table</odc:CommandType>
<odc:CommandText>`C:\TEMP`\`odbctest.txt`</odc:CommandText>
</odc:Connection>
<odc:Connection odc:Type="ODBC">
<odc:ConnectionString>DSN=odbctest;DefaultDir=C:\TEMP;DriverId=27;FIL=text;MaxBufferSize=2048;PageTimeout=5;</odc:ConnectionString>
<odc:CommandText>SELECT * FROM `C:\TEMP`\`odbctest.txt`</odc:CommandText>
</odc:Connection>
</odc:OfficeDataConnection>
</xml>


Al

I have now tried the microsoft odbc text driver, which shows a similar
symptom. I'm pretty sure at this point that its a problem in either visio,
or
the ole db provider for odbc.

details:
c:\odbctest.txt:
id,name
1,"wendy"
2,"tom"
3,"fred"

User Data Sources:
text test Microsoft Text Driver (*.txt; *.csv):
Data Source Name: text test
Directory: C:\
Other options left at defaults.

In Visio 2007:
NEW select detailed network diagram
Data->link data to shapes
select Other OLEDB or ODBC data source
click next
select ODBC DSN
click next
select text test
click next
select odbctest.txt
click next
click finish (let it save connection with default generated name,
odbctest.txt.odc)
this is now selected in what connection do you want to use dialog, click
next
Visio generates:
Microsoft Office Visio cannot connect to this data source.
click More Info
Error 80040e14: [Microsoft][ODBC Text Driver] Syntax error in FROM clause.

I turned on ODBC global tracing before doing this and the relevent lines
from the log read thusly:

WCHAR * 0x049B3920 [ 36] "SELECT * FROM
`C:\``\``odbctest.txt`"
SDWORD 36

DIAG [37000] [Microsoft][ODBC Text Driver] Syntax error in FROM clause.
(-3506)

Note that it individually quoted the \ character, similar to when it
individually quotes the . character between the schema name and the table
name in my earlier example.

This now shows the problem with two totally separate odbc drivers, in the
second case one provided by microsoft.

Question: do they monitor these groups, or is there anywhere better to
report these sorts of things without using up a support incident?