ODBC Error with PartTran table SysDate and SysTime

Yahoo Archive
1
Oops, just caught myself. Be sure and use the SQL server 2005 native client. Change the connection string provider property to say "SQLNCLI" not "SQLNCLI10"

That is unless you like to live dangerously and you're breaking all sorts of rules like we are and someone shoe-horned Vantage onto SQL Server 2008.
Jared

--- In vantage@yahoogroups.com, "k99ja04" <jallmond@...> wrote:
>
> I'm a few days behind reading the daily digest so excuse if this has been answered. An interesting theory on "wide" tables Vic. I do find it odd that Excel chokes, but Access does not, as they should be using the same ODBC driver. This is a just for kicks suggestion, but what about trying the SQL server native client instead of the OLE DB provider? The client is not usually installed by default on most machines, but it's on your SQL server CD or you can find it at download.microsoft.com. To specify native client your Excel VBA connection string goes something like
>
> Dim conn As ADODB.Connection
> Set conn = New ADODB.Connection
> With conn
> .Provider = "SQLNCLI10"
> .ConnectionString = "Server=MyServerName;Database=MfgSys803;Trusted_Connection=Yes;"
> .Open
> End With
>
> You can get around reserved key words by enclosing fields in [] backets. Also, it appears there is a trailing underscore on sysdate_ field (perhaps the problem?). I'm on 8.03.408B SQL. So my SQL select statement would go something like
>
> SELECT [company]
> ,[sysdate_]
> ,[systime]
> ,[trannum]
> ,[partnum]
> ,[etc. etc.]
> FROM [Mfgsys803].[dbo].[parttran]
>
> Jared
> _______________________
> Jared Allmond
> IT Systems Engineer
> Wright Coating Technologies
> jallmond@...
> voice: 269.344.8195
> direct: 269.341.4353
> fax: 269.344.3007
>
> --- In vantage@yahoogroups.com, "Linda Lowney" <llowney@> wrote:
> >
> > Hi Vic,
> >
> >
> >
> > I think SysDate and SysTime might be reserved words in SQL. I think
> > Type is another one that I have problems with that may also be a
> > reserved word.
> >
> >
> >
> > Regards,
> >
> > Linda
>
2
Progress db... V8.03x

When I attempt to use ODBC via Excel (MS Query) or ASP to query the PartTran I get an error only when I include the PartTran.SysDate or PartTran.SysTime in the query:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
ODBC driver does not support the requested properties.

The query works in MS Access, but not ASP or Excel. It's a simple query that works fine in Excel and ASP without those fields.

Thanks in advance,

Randy Weber
3
Randy,

I've seen this many times. PartTran is typically your largest table.
Excel & ASP via ODBC will usually error out; yet, like you stated,
Access can bring it in fine.

I've seen this on very "wide" tables, too (with many columns).

I know it sounds odd, but that's the way it's always been for me.

If you must use Excel for some reason, consider writing a BAQ and
dumping it at your desired time intervals to CSV somewhere via the BAQ
Export Process. Then open the CSV with Excel.

Vic


-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of randyweb
Sent: Monday, February 08, 2010 11:35 AM
To: vantage@yahoogroups.com
Subject: [Vantage] ODBC Error with PartTran table SysDate and SysTime

Progress db... V8.03x

When I attempt to use ODBC via Excel (MS Query) or ASP to query the
PartTran I get an error only when I include the PartTran.SysDate or
PartTran.SysTime in the query:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
ODBC driver does not support the requested properties.

The query works in MS Access, but not ASP or Excel. It's a simple query
that works fine in Excel and ASP without those fields.

Thanks in advance,

Randy Weber



------------------------------------

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/linksYahoo! Groups Links
4
Vic,



Yes, but I find it odd that it only happens when I add the SysDate or
SysTime fields. A simple query selecting only PartNumand TranDate works
fine. If I add SysDate to the same query, I get the error.



Randy Weber



From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Vic Drecchio
Sent: Monday, February 08, 2010 10:45 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] ODBC Error with PartTran table SysDate and
SysTime





Randy,

I've seen this many times. PartTran is typically your largest table.
Excel & ASP via ODBC will usually error out; yet, like you stated,
Access can bring it in fine.

I've seen this on very "wide" tables, too (with many columns).

I know it sounds odd, but that's the way it's always been for me.

If you must use Excel for some reason, consider writing a BAQ and
dumping it at your desired time intervals to CSV somewhere via the BAQ
Export Process. Then open the CSV with Excel.

Vic

-----Original Message-----
From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf
Of randyweb
Sent: Monday, February 08, 2010 11:35 AM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Subject: [Vantage] ODBC Error with PartTran table SysDate and SysTime

Progress db... V8.03x

When I attempt to use ODBC via Excel (MS Query) or ASP to query the
PartTran I get an error only when I include the PartTran.SysDate or
PartTran.SysTime in the query:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
ODBC driver does not support the requested properties.

The query works in MS Access, but not ASP or Excel. It's a simple query
that works fine in Excel and ASP without those fields.

Thanks in advance,

Randy Weber

------------------------------------

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/.
<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/linksYahoo! Groups Links





[Non-text portions of this message have been removed]
5
Hi Vic,



I think SysDate and SysTime might be reserved words in SQL. I think
Type is another one that I have problems with that may also be a
reserved word.



Regards,

Linda











From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Randy Weber
Sent: Monday, February 08, 2010 4:05 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] ODBC Error with PartTran table SysDate and
SysTime





Vic,

Yes, but I find it odd that it only happens when I add the SysDate or
SysTime fields. A simple query selecting only PartNumand TranDate works
fine. If I add SysDate to the same query, I get the error.

Randy Weber

From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf
Of Vic Drecchio
Sent: Monday, February 08, 2010 10:45 AM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Subject: RE: [Vantage] ODBC Error with PartTran table SysDate and
SysTime

Randy,

I've seen this many times. PartTran is typically your largest table.
Excel & ASP via ODBC will usually error out; yet, like you stated,
Access can bring it in fine.

I've seen this on very "wide" tables, too (with many columns).

I know it sounds odd, but that's the way it's always been for me.

If you must use Excel for some reason, consider writing a BAQ and
dumping it at your desired time intervals to CSV somewhere via the BAQ
Export Process. Then open the CSV with Excel.

Vic

-----Original Message-----
From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> ] On
Behalf
Of randyweb
Sent: Monday, February 08, 2010 11:35 AM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
Subject: [Vantage] ODBC Error with PartTran table SysDate and SysTime

Progress db... V8.03x

When I attempt to use ODBC via Excel (MS Query) or ASP to query the
PartTran I get an error only when I include the PartTran.SysDate or
PartTran.SysTime in the query:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
ODBC driver does not support the requested properties.

The query works in MS Access, but not ASP or Excel. It's a simple query
that works fine in Excel and ASP without those fields.

Thanks in advance,

Randy Weber

------------------------------------

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/.
<http://groups.yahoo.com/group/vantage/files/>
<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/linksYahoo! Groups Links

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





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

I've run into this and I had this in my notes:

To get the SYS fields in the PartTran Table:
'strSQL &= "PUB.PartTran.SysDate, " ' Can't get these because they start
with SYS
strSQL &= "PUB.PartTran." & Chr(34) & "SysDate" & Chr(34) & ", " ' This
Works

This is for building a query string in VB but it's basically putting double
quotes around the field name.

Like this: PUB.PartTran."SysDate"

HTH

Bob


Vista 8.00.811b
Progress 10.0b
Windows XP SP3
Windows Server 2003



----- Original Message -----
From: "randyweb" <rweber@...>
To: <vantage@yahoogroups.com>
Sent: Monday, February 08, 2010 11:34 AM
Subject: [Vantage] ODBC Error with PartTran table SysDate and SysTime


Progress db... V8.03x

When I attempt to use ODBC via Excel (MS Query) or ASP to query the PartTran
I get an error only when I include the PartTran.SysDate or PartTran.SysTime
in the query:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
ODBC driver does not support the requested properties.

The query works in MS Access, but not ASP or Excel. It's a simple query that
works fine in Excel and ASP without those fields.

Thanks in advance,

Randy Weber
7
Thanks Bob that worked!



Randy



From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of BobT
Sent: Tuesday, February 09, 2010 7:51 AM
To: vantage@yahoogroups.com
Subject: Re: [Vantage] ODBC Error with PartTran table SysDate and SysTime






Randy,

I've run into this and I had this in my notes:

To get the SYS fields in the PartTran Table:
'strSQL &= "PUB.PartTran.SysDate, " ' Can't get these because they start
with SYS
strSQL &= "PUB.PartTran." & Chr(34) & "SysDate" & Chr(34) & ", " ' This
Works

This is for building a query string in VB but it's basically putting double
quotes around the field name.

Like this: PUB.PartTran."SysDate"

HTH

Bob

Vista 8.00.811b
Progress 10.0b
Windows XP SP3
Windows Server 2003

----- Original Message -----
From: "randyweb" <rweber@... <mailto:rweber%40tlcelectronics.com> >
To: <vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> >
Sent: Monday, February 08, 2010 11:34 AM
Subject: [Vantage] ODBC Error with PartTran table SysDate and SysTime

Progress db... V8.03x

When I attempt to use ODBC via Excel (MS Query) or ASP to query the PartTran
I get an error only when I include the PartTran.SysDate or PartTran.SysTime
in the query:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
ODBC driver does not support the requested properties.

The query works in MS Access, but not ASP or Excel. It's a simple query that
works fine in Excel and ASP without those fields.

Thanks in advance,

Randy Weber





[Non-text portions of this message have been removed]
8
I'm a few days behind reading the daily digest so excuse if this has been answered. An interesting theory on "wide" tables Vic. I do find it odd that Excel chokes, but Access does not, as they should be using the same ODBC driver. This is a just for kicks suggestion, but what about trying the SQL server native client instead of the OLE DB provider? The client is not usually installed by default on most machines, but it's on your SQL server CD or you can find it at download.microsoft.com. To specify native client your Excel VBA connection string goes something like

Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
With conn
.Provider = "SQLNCLI10"
.ConnectionString = "Server=MyServerName;Database=MfgSys803;Trusted_Connection=Yes;"
.Open
End With

You can get around reserved key words by enclosing fields in [] backets. Also, it appears there is a trailing underscore on sysdate_ field (perhaps the problem?). I'm on 8.03.408B SQL. So my SQL select statement would go something like

SELECT [company]
,[sysdate_]
,[systime]
,[trannum]
,[partnum]
,[etc. etc.]
FROM [Mfgsys803].[dbo].[parttran]

Jared
_______________________
Jared Allmond
IT Systems Engineer
Wright Coating Technologies
jallmond@...
voice: 269.344.8195
direct: 269.341.4353
fax: 269.344.3007

--- In vantage@yahoogroups.com, "Linda Lowney" <llowney@...> wrote:
>
> Hi Vic,
>
>
>
> I think SysDate and SysTime might be reserved words in SQL. I think
> Type is another one that I have problems with that may also be a
> reserved word.
>
>
>
> Regards,
>
> Linda