Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Proxy Query to SQL Server 2005 Fails

5 posts in General Discussion Last posting was on 2010-02-24 23:05:35.0Z
Glenn Barber Posted on 2010-02-24 00:44:10.0Z
Sender: 66db.4b847185.1804289383@sybase.com
From: Glenn Barber
Newsgroups: ianywhere.public.general
Subject: Proxy Query to SQL Server 2005 Fails
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4b84765a.677c.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 23 Feb 2010 16:44:10 -0800
X-Trace: forums-1-dub 1266972250 10.22.241.41 (23 Feb 2010 16:44:10 -0800)
X-Original-Trace: 23 Feb 2010 16:44:10 -0800, 10.22.241.41
Lines: 40
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7935
Article PK: 6962

I have a SQL Server Table with the following columns
licrightid (PK, numeric(18,0), not null)
licno (FK, varchar(40), not null)
territorycd (varchar(20), not null)
rightcd (varchar(18), not null)
licstartdate (datetime, null)
licenddate (datetime, null)
datechanged (datetime, null)
status (char(1), null)

For which I have created a proxy table in SA 11.0.1 using
SQL Central

The Proxy looks like
licrightid numeric(18,2)
licno varchar(20)
propno varchar(40)
territorycd varchar(20)
rightcd varchar(18)
licstartdate timestamp
licenddate timestamp
dateadded timestamp
datechanged timestamp
status char(10)

When I query this table in SQL Central I get about 990 rows
(out of 1301) and an "error reading results of SQL
statement"

using the SQL Central if I select the proxy table and look
at the data tab - if I scroll down I get an error after
about 990 rows "Error reading row" "Cursor not in valid
state"

This does not happen on other similar tables, but I notice
that the values I am seeing for the datetimes do not
correspond to valid values as seen in SQL Server.

Since I have done everything in SQL Central I am in a
mystery as to what is wrong here.


Glenn Barber Posted on 2010-02-24 19:29:57.0Z
Sender: 66db.4b847185.1804289383@sybase.com
From: Glenn Barber
Newsgroups: ianywhere.public.general
Subject: Re: Proxy Query to SQL Server 2005 Fails
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4b857e35.124a.1681692777@sybase.com>
References: <4b84765a.677c.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 24 Feb 2010 11:29:57 -0800
X-Trace: forums-1-dub 1267039797 10.22.241.41 (24 Feb 2010 11:29:57 -0800)
X-Original-Trace: 24 Feb 2010 11:29:57 -0800, 10.22.241.41
Lines: 10
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7943
Article PK: 6969

Reverting to SA Version 9 and retrying the proxies I got
some better information as to why the interactive query
fails

on the licrightid (18,0) column the query stops when the
value reaches 1000 with a message "can't convert 1,000.00 to
a decimal". Looks like the interface is converting to
decimal format then failing when trying to convert back. I
can confirm that the data in SQL Server is pure numeric
without formatting.


Glenn Barber Posted on 2010-02-24 19:14:15.0Z
Sender: 66db.4b847185.1804289383@sybase.com
From: Glenn Barber
Newsgroups: ianywhere.public.general
Subject: Re: Proxy Query to SQL Server 2005 Fails
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4b857a87.1130.1681692777@sybase.com>
References: <4b84765a.677c.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 24 Feb 2010 11:14:15 -0800
X-Trace: forums-1-dub 1267038855 10.22.241.41 (24 Feb 2010 11:14:15 -0800)
X-Original-Trace: 24 Feb 2010 11:14:15 -0800, 10.22.241.41
Lines: 4
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7942
Article PK: 6970

I note that using the same odbc datasource, that I can
properly read the datetime columns from within a
powerbuilder datawindow - so this confirms that SQL Anywhere
is mishandling the datetime > timestamp translation.


Glenn Barber Posted on 2010-02-24 19:03:54.0Z
Sender: 66db.4b847185.1804289383@sybase.com
From: Glenn Barber
Newsgroups: ianywhere.public.general
Subject: Re: Proxy Query to SQL Server 2005 Fails
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4b85781a.1094.1681692777@sybase.com>
References: <4b84765a.677c.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 24 Feb 2010 11:03:54 -0800
X-Trace: forums-1-dub 1267038234 10.22.241.41 (24 Feb 2010 11:03:54 -0800)
X-Original-Trace: 24 Feb 2010 11:03:54 -0800, 10.22.241.41
Lines: 76
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7941
Article PK: 6973

I updated to 11.0.1.2376 and still have the same
mis-behaviors

SQL Central Interactive Query Fails with an incomplete set
of rows, and timestamps are not being properly converted.

Has anybody gotten timestamps to convert properly with SQL
Server 2005.

I noticed some typos in my prior post.

On SQL Server it is

licrightid (PK, numeric(18,0), not null)
licno (FK, varchar(40), not null)
territorycd (varchar(20), not null)
rightcd (varchar(18), not null)
licstartdate (datetime, null)
licenddate (datetime, null)
dateadded (datetime, null)
datechanged (datetime, null)
status (char(1), null)

The Proxy Created by SQL Central looks like
licrightid numeric(18,0)
licno varchar(20)
propno varchar(40)
territorycd varchar(20)
rightcd varchar(18)
licstartdate timestamp
licenddate timestamp
dateadded timestamp
datechanged timestamp
status char(10)

> I have a SQL Server Table with the following columns
> licrightid (PK, numeric(18,0), not null)
> licno (FK, varchar(40), not null)
> territorycd (varchar(20), not null)
> rightcd (varchar(18), not null)
> licstartdate (datetime, null)
> licenddate (datetime, null)
> datechanged (datetime, null)
> status (char(1), null)
>
> For which I have created a proxy table in SA 11.0.1 using
> SQL Central
>
> The Proxy looks like
> licrightid numeric(18,2)
> licno varchar(20)
> propno varchar(40)
> territorycd varchar(20)
> rightcd varchar(18)
> licstartdate timestamp
> licenddate timestamp
> dateadded timestamp
> datechanged timestamp
> status char(10)
>
> When I query this table in SQL Central I get about 990
> rows (out of 1301) and an "error reading results of SQL
> statement"
>
> using the SQL Central if I select the proxy table and look
> at the data tab - if I scroll down I get an error after
> about 990 rows "Error reading row" "Cursor not in valid
> state"
>
> This does not happen on other similar tables, but I notice
> that the values I am seeing for the datetimes do not
> correspond to valid values as seen in SQL Server.
>
> Since I have done everything in SQL Central I am in a
> mystery as to what is wrong here.


Glenn Barber Posted on 2010-02-24 23:05:35.0Z
Sender: 66db.4b847185.1804289383@sybase.com
From: Glenn Barber
Newsgroups: ianywhere.public.general
Subject: Re: Proxy Query to SQL Server 2005 Fails - Solved
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4b85b0bf.1a1a.1681692777@sybase.com>
References: <4b84765a.677c.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 24 Feb 2010 15:05:35 -0800
X-Trace: forums-1-dub 1267052735 10.22.241.41 (24 Feb 2010 15:05:35 -0800)
X-Original-Trace: 24 Feb 2010 15:05:35 -0800, 10.22.241.41
Lines: 6
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7949
Article PK: 6977

I noticed that the version of SQL Server 2005 was out of
date - so I installed Service Pack 3 and recreated the ODBC
Source.

After that the Problems with numerics and timestamps
disappeared.