[6.1] [Semi-OT] Birds of a feather: SQL and/or ODBC USERS

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
>
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]
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:



BM__sql_odbc_data_sourceFrom 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.



See also:
<http://msdn.microsoft.com/library/en-us/glosssql/Basics/gloss.asp?FRAME=tru
e#sql:data_source#sql:data_source> data source



BM__sql_data_sourcedata source



In ADO and OLE DB, the location of a source of data exposed by an OLE DB
provider.



See also:
<http://msdn.microsoft.com/library/en-us/glosssql/Basics/gloss.asp?FRAME=tru
e#sql:odbc_data_source#sql:odbc_data_source> ODBC data source





From
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/htm
l/sql_oledbconn.asp?frame=true
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/ht
ml/sql_oledbconn.asp?frame=true>



sql_oledbconn_topic2Overview 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]
OK, that didn't work well. Let me try this again, hopefully resulting in a
shorter message...



Regards,
Scott



-----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
ml/sql_oledbconn.asp?frame=true>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/htm
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]
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: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of
Lepley, Scott A.
Sent: Friday, March 03, 2006 8:45 PM
To: 'vantage@yahoogroups.com'
Subject: RE: [Vantage] [6.1] [Semi-OT] Birds of a feather: SQL and/or ODBC
USERS


OK, that didn't work well. Let me try this again, hopefully resulting in a
shorter message...



Regards,
Scott



-----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
ml/sql_oledbconn.asp?frame=true>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/htm
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]



Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links
Yahoo! Groups Links
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: Lepley, Scott A. <sal@...>
To: 'vantage@yahoogroups.com' <vantage@yahoogroups.com>
Sent: Fri, 3 Mar 2006 21:45:18 -0500
Subject: RE: [Vantage] [6.1] [Semi-OT] Birds of a feather: SQL and/or ODBC USERS


OK, that didn't work well. Let me try this again, hopefully resulting in a
shorter message...



Regards,
Scott



-----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
ml/sql_oledbconn.asp?frame=true>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/htm
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]



Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/.

(2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links
Yahoo! Groups Links






[Non-text portions of this message have been removed]