Jeremy and Chris,
Thanks for your help; I will pass the information to our SQL/Access
developers.
Regards,
Scott
-----Original Message-----
From: jeremyrleonard@... [mailto:jeremyrleonard@...]
Sent: Saturday, March 04, 2006 9:07 AM
To: vantage@yahoogroups.com
Subject: Re: [Vantage] [6.1] [Semi-OT] Birds of a feather: SQL and/or ODBC
USERS
To setup the connection.
1) Enter the ODBC DSN on the first tab.
2) In the security section choose "Be Made in the Security Context" and
enter your username and password. The default username and password is
either sysprogress or odbcuser.
Your select statement will look like this.
Select * from OpenQuery([LINKEDSERVERNAME],'Select PartNum, Description from
pub.part where company =''YOURCOMPANYCODE''')
LINKEDSERVERNAME= The name you gave your linked server in MS SQL Server.
YOURCOMPANYCODE= The code representing your company in the progress
database.
Notes:
1) All strings and dates must be inclosed in two single quotes. Not double
quotes.
2) Doing more then three or four joins can bomb your query. For the
company links I will often include "where
pub.part.company=''YOURCOMPANYCODE" and
pub.partdtl.company=''YOURCOMPANYCODE" in the where clause of the statement
to speed up the query. This can have mixed results when doing a group by
query.
3) When creating a view in SQL Server, it does not like the OpenQuery
statement. It will run ok but when you try to save it, sql server will give
you an error. To get around this, create a view based on a table that
exists in your database and save it. Then create another view in SQL
Server to get the data you want. Copy the SQL Statement and then open the
other View by right mouse clicking on it and choosing PROPERTIES. Then
paste your statement in the Properties window. Save it and your good to go.
One example I have looks like this.
CREATE VIEW dbo.vw_Customers
AS
SELECT *
FROM OPENQUERY(MFC_Vantage, 'Select * from pub.customer where
company=''MFC''') Rowset_1
Hope that helps,
Jeremy
-----Original Message-----
From: Gitzlaff, Christopher [mailto:cgitzlaff@...]
Sent: Friday, March 03, 2006 11:42 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] [6.1] [Semi-OT] Birds of a feather: SQL and/or ODBC
USERS
This was a stumbling block for me when I implemented ODBC with Progress 4
years ago. I'm not sure if this is related to your problem or not. These
are methods I use to make ODBC calls for the internal web pages my company
serves.
All of the tables for Vantage are stored in the 'PUB' schema. All of your
calls should reflect that. You have two options. Include the PUB schema in
the SQL statement or make an initial statement to change to that schema.
Example 1 - Change to the PUB schema, then make a normal SQL call:
dbsVantage.Execute "SET SCHEMA 'PUB'"
sql = "SELECT * FROM Customer WHERE CustID = 'mycustid'"
Example 2 - Implement the PUB schema in the sql call:
sql = "SELECT * FROM PUB.Customer WHERE CustID = 'mycustid'"
Hope this helps,
Chris
Christopher Gitzlaff
Manager - Information Systems & Technology
Major Industries, Inc.
Phone: 715.842.4616 ext. 323
Email: cgitzlaff@...
-----Original Message-----
From: Lepley, Scott A. [mailto:sal@...]
Sent: Friday, March 03, 2006 9:26 PM
To: 'vantage@yahoogroups.com'
Subject: [Vantage] [6.1] [Semi-OT] Birds of a feather: SQL and/or ODBC USERS
We are attempting to connect Microsoft SQL Server to the mfgsys database for
the purpose of retrieving and presenting Vantage data in a SQL view that
would be used by a compiled MS Access application. I have contacted Vantage
Support, which didn't have very much to offer regarding this, so now I'm
turning to the network (Can you hear me now?).
Following the instructions in Epicor's "Implementing ODBC for Mfgsys"
document, I have already created a new ODBC data source on the SQL Server
machine by installing and configuring the Merant/DataDirect 3.60 Progress
SQL92 ODBC driver. When I click Test Connect and enter the password, I
receive the "connection established" message that apparently confirms that
the configuration is acceptable.
However, in SQL Server, our developers are attempting to use Microsoft OLE
DB Provider for ODBC functionality to connect to the ODBC data and it is
failing. This issue lays WAY outside my experience and (at least currently)
WAY beyond my grasp, but I'm trying to learn the basics and I'm hoping an
SQL/ODBC guru on this list might be able to help. I found the following
information on the MSDN website:
From the SQL Server 2000 glossary:
ODBC data source
The location of a set of data that can be accessed using an ODBC driver.
Also, a stored definition that contains all of the connection information an
ODBC application requires to connect to the data source.
data source
In ADO and OLE DB, the location of a source of data exposed by an OLE DB
provider.
From
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/ht
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/ht
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/htm
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/ht
m>
l/sql_oledbconn.asp?frame=true
Overview and Terminology
In Microsoft SQL Server 2000, distributed queries enable SQL Server users to
access data outside a SQL Server-based server, either within other servers
running SQL Server or other data sources that expose an OLE DB interface.
OLE DB provides a way to uniformly access tabular data from heterogeneous
data sources.
A distributed query, for the purpose of this document, is any SELECT,
INSERT, UPDATE, or DELETE statement that references tables and rowsets from
one or more external OLE DB data source.
A remote table is a table that is stored in an OLE DB data source and is
external to the server running SQL Server executing the query. A distributed
query accesses one or more remote tables.
OLE DB Provider Categories
The following is a categorization of OLE DB providers based on their
capabilities from a SQL Server distributed querying standpoint. As defined,
these are not mutually exclusive; a given provider may belong to more than
one of the following categories:
SQL Command Providers
Index Providers
Simple Table Providers
Non-SQL Command Providers
Connection Establishment and Property Retrieval
SQL Server supports two remote data object naming conventions: linked
server-based four-part names and ad hoc names using the OPENROWSET function.
Linked server-based names
A linked server serves as an abstraction to an OLE DB data source. A linked
server-based name is a four-part name of the form <linked-server>.<catalog>.
<schema>.<object>, where <linked-server> is the name of the linked server.
SQL Server interprets <linked-server> to derive the OLE DB provider and the
connection attributes that identify the data source to the provider. The
other three name parts are interpreted by the OLE DB data source to identify
the specific remote table.
Ad hoc names
An ad hoc name is a name based on the OPENROWSET or OPENDATASOURCE function.
It includes all the connection information (that is, the OLE DB provider to
use, the attributes needed to identify the data source, the user ID and
password) every time the remote table is referenced in a distributed query.
Using ad hoc names is not allowed by default except for members of the
sysadmin role. In order to use ad hoc names against an OLE DB provider, the
provider option DisallowAdhocAccess should be set to 0.
If a linked server name is used, SQL Server extracts from the linked server
definition the OLE DB provider name and the initialization properties for
the provider. If an ad hoc name is used, SQL Server extracts the same
information from the arguments of the OPENROWSET function.
Based on all of this, I'm suspecting that, even if the Merant driver is
functioning properly (which I believe it is) as a data provider of the
mfgsys data source, it simply cannot provide the data in the precise manner
which SQL Server expects and requires, simply because SQL92 ODBC is
different than OLE DB. In other words, I'm suspecting that this may simply
be a case of incompatible standards. Can anyone either confirm or refute my
suspicion? If you can confirm it, can you please also tell me what I need
to do to make the data accessible? If you can refute it, can you please
tell me what our developers need to specify
Regards,
Scott Lepley (EUG Vantage Liaison)
Systems Administrator
Mauell Corporation
Phone: 717-432-8686, ext. 14
Fax: 717-432-8688
Email: sal@...
Vantage version: 6.10.532
[Non-text portions of this message have been removed]
Thanks for your help; I will pass the information to our SQL/Access
developers.
Regards,
Scott
-----Original Message-----
From: jeremyrleonard@... [mailto:jeremyrleonard@...]
Sent: Saturday, March 04, 2006 9:07 AM
To: vantage@yahoogroups.com
Subject: Re: [Vantage] [6.1] [Semi-OT] Birds of a feather: SQL and/or ODBC
USERS
To setup the connection.
1) Enter the ODBC DSN on the first tab.
2) In the security section choose "Be Made in the Security Context" and
enter your username and password. The default username and password is
either sysprogress or odbcuser.
Your select statement will look like this.
Select * from OpenQuery([LINKEDSERVERNAME],'Select PartNum, Description from
pub.part where company =''YOURCOMPANYCODE''')
LINKEDSERVERNAME= The name you gave your linked server in MS SQL Server.
YOURCOMPANYCODE= The code representing your company in the progress
database.
Notes:
1) All strings and dates must be inclosed in two single quotes. Not double
quotes.
2) Doing more then three or four joins can bomb your query. For the
company links I will often include "where
pub.part.company=''YOURCOMPANYCODE" and
pub.partdtl.company=''YOURCOMPANYCODE" in the where clause of the statement
to speed up the query. This can have mixed results when doing a group by
query.
3) When creating a view in SQL Server, it does not like the OpenQuery
statement. It will run ok but when you try to save it, sql server will give
you an error. To get around this, create a view based on a table that
exists in your database and save it. Then create another view in SQL
Server to get the data you want. Copy the SQL Statement and then open the
other View by right mouse clicking on it and choosing PROPERTIES. Then
paste your statement in the Properties window. Save it and your good to go.
One example I have looks like this.
CREATE VIEW dbo.vw_Customers
AS
SELECT *
FROM OPENQUERY(MFC_Vantage, 'Select * from pub.customer where
company=''MFC''') Rowset_1
Hope that helps,
Jeremy
-----Original Message-----
From: Gitzlaff, Christopher [mailto:cgitzlaff@...]
Sent: Friday, March 03, 2006 11:42 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] [6.1] [Semi-OT] Birds of a feather: SQL and/or ODBC
USERS
This was a stumbling block for me when I implemented ODBC with Progress 4
years ago. I'm not sure if this is related to your problem or not. These
are methods I use to make ODBC calls for the internal web pages my company
serves.
All of the tables for Vantage are stored in the 'PUB' schema. All of your
calls should reflect that. You have two options. Include the PUB schema in
the SQL statement or make an initial statement to change to that schema.
Example 1 - Change to the PUB schema, then make a normal SQL call:
dbsVantage.Execute "SET SCHEMA 'PUB'"
sql = "SELECT * FROM Customer WHERE CustID = 'mycustid'"
Example 2 - Implement the PUB schema in the sql call:
sql = "SELECT * FROM PUB.Customer WHERE CustID = 'mycustid'"
Hope this helps,
Chris
Christopher Gitzlaff
Manager - Information Systems & Technology
Major Industries, Inc.
Phone: 715.842.4616 ext. 323
Email: cgitzlaff@...
-----Original Message-----
From: Lepley, Scott A. [mailto:sal@...]
Sent: Friday, March 03, 2006 9:26 PM
To: 'vantage@yahoogroups.com'
Subject: [Vantage] [6.1] [Semi-OT] Birds of a feather: SQL and/or ODBC USERS
We are attempting to connect Microsoft SQL Server to the mfgsys database for
the purpose of retrieving and presenting Vantage data in a SQL view that
would be used by a compiled MS Access application. I have contacted Vantage
Support, which didn't have very much to offer regarding this, so now I'm
turning to the network (Can you hear me now?).
Following the instructions in Epicor's "Implementing ODBC for Mfgsys"
document, I have already created a new ODBC data source on the SQL Server
machine by installing and configuring the Merant/DataDirect 3.60 Progress
SQL92 ODBC driver. When I click Test Connect and enter the password, I
receive the "connection established" message that apparently confirms that
the configuration is acceptable.
However, in SQL Server, our developers are attempting to use Microsoft OLE
DB Provider for ODBC functionality to connect to the ODBC data and it is
failing. This issue lays WAY outside my experience and (at least currently)
WAY beyond my grasp, but I'm trying to learn the basics and I'm hoping an
SQL/ODBC guru on this list might be able to help. I found the following
information on the MSDN website:
From the SQL Server 2000 glossary:
ODBC data source
The location of a set of data that can be accessed using an ODBC driver.
Also, a stored definition that contains all of the connection information an
ODBC application requires to connect to the data source.
data source
In ADO and OLE DB, the location of a source of data exposed by an OLE DB
provider.
From
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/ht
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/ht
>ml/sql_oledbconn.asp?frame=true>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/htm
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/ht
m>
l/sql_oledbconn.asp?frame=true
Overview and Terminology
In Microsoft SQL Server 2000, distributed queries enable SQL Server users to
access data outside a SQL Server-based server, either within other servers
running SQL Server or other data sources that expose an OLE DB interface.
OLE DB provides a way to uniformly access tabular data from heterogeneous
data sources.
A distributed query, for the purpose of this document, is any SELECT,
INSERT, UPDATE, or DELETE statement that references tables and rowsets from
one or more external OLE DB data source.
A remote table is a table that is stored in an OLE DB data source and is
external to the server running SQL Server executing the query. A distributed
query accesses one or more remote tables.
OLE DB Provider Categories
The following is a categorization of OLE DB providers based on their
capabilities from a SQL Server distributed querying standpoint. As defined,
these are not mutually exclusive; a given provider may belong to more than
one of the following categories:
SQL Command Providers
Index Providers
Simple Table Providers
Non-SQL Command Providers
Connection Establishment and Property Retrieval
SQL Server supports two remote data object naming conventions: linked
server-based four-part names and ad hoc names using the OPENROWSET function.
Linked server-based names
A linked server serves as an abstraction to an OLE DB data source. A linked
server-based name is a four-part name of the form <linked-server>.<catalog>.
<schema>.<object>, where <linked-server> is the name of the linked server.
SQL Server interprets <linked-server> to derive the OLE DB provider and the
connection attributes that identify the data source to the provider. The
other three name parts are interpreted by the OLE DB data source to identify
the specific remote table.
Ad hoc names
An ad hoc name is a name based on the OPENROWSET or OPENDATASOURCE function.
It includes all the connection information (that is, the OLE DB provider to
use, the attributes needed to identify the data source, the user ID and
password) every time the remote table is referenced in a distributed query.
Using ad hoc names is not allowed by default except for members of the
sysadmin role. In order to use ad hoc names against an OLE DB provider, the
provider option DisallowAdhocAccess should be set to 0.
If a linked server name is used, SQL Server extracts from the linked server
definition the OLE DB provider name and the initialization properties for
the provider. If an ad hoc name is used, SQL Server extracts the same
information from the arguments of the OPENROWSET function.
Based on all of this, I'm suspecting that, even if the Merant driver is
functioning properly (which I believe it is) as a data provider of the
mfgsys data source, it simply cannot provide the data in the precise manner
which SQL Server expects and requires, simply because SQL92 ODBC is
different than OLE DB. In other words, I'm suspecting that this may simply
be a case of incompatible standards. Can anyone either confirm or refute my
suspicion? If you can confirm it, can you please also tell me what I need
to do to make the data accessible? If you can refute it, can you please
tell me what our developers need to specify
Regards,
Scott Lepley (EUG Vantage Liaison)
Systems Administrator
Mauell Corporation
Phone: 717-432-8686, ext. 14
Fax: 717-432-8688
Email: sal@...
Vantage version: 6.10.532
[Non-text portions of this message have been removed]