after a little more digging , it seems that the problem is not directly
related to accessing virtual tables but rather with the SQL data type for
the @ASSOC_ROW column NUMERIC,9
everything is peachy if i change my select to not include the @ASSOC_ROW
column as : > SELECT * FROM OPENQUERY( [UVCON] , 'SELECT @ID,M1,M2
FROM TEST.TAB_MV')
a little testing shows that this turns out to be true for any column defined
as NUMERIC in universe.
changing the SQL data type to DECIMAL or INTEGER fixes the problem.
however this solution doesn't help with accessing virtual tables though as
we have no control over the data type for the @ASSOC_ROW column - or do we ?
does anyone know what we could possibly have set up wrong in either universe
or sql server to cause this behaviour ?
alternatively , are there settings in universe that will allow us to :
- use the SQL NUMERIC data type without locking up
- define the data type for @ASSOC_ROW as INTEGER or DECIMAL
gerry
----- Original Message -----
From: "gerry simpson" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, March 08, 2004 1:37 PM
Subject: SQL Server , UniOLEDB & subtables
> we have set up universe 10 in SQL Server 2K as a linked database via
> UniOLEDB.
>
> we have a test file set up that consists of 2 single valued fields & 2
multi
> valued fields
>
> at the universe command prompt we can access both the table and the
virtual
> table for the multi values :
> SELECT * FROM TEST.TAB ;
> @ID S1 S2 M1 M2
> AAA A B C D
> G H
> BBB I J K L
> M N
> O P
>
> SELECT * FROM TEST.TAB_MV ;
> @ID M1 M2 @ASSOC_ROW
> AAA C D 1
> G H 2
> BBB K L 1
> M N 2
> O P 3
>
>
> in SQL Server the following works just fine - we get the expected 2 row
> table with the columns @ID , S1 & S2 :
> SELECT * FROM OPENQUERY( [UVCON] , 'SELECT * FROM TEST.TAB')
>
> however any attempt to access the sub table :
> SELECT * FROM OPENQUERY( [UVCON] , 'SELECT * FROM TEST.TAB_MV')
> pretty much causes the pc to come grinding to a halt - cpu usage goes to
> 100%, connection activity stops and we have to restart SQL Server to abort
> the select.
> I have not been able to locate any type of error info either in uv or on
the
> pc.
>
> I tried double quoting the table name assuming this was a problem with the
_
> character - no effect
> In the linked database properties I set a query timeout of 30 seconds and
a
> connection timeout of 30 seconds but I have let this query run for 10
> minutes without getting any timeout errors.
>
> any ideas ?
>
> does universe keep any type of log for oleDB access - i can't find one to
> see if the request is even getting to universe.
>
> gerry
>
--
u2-users mailing list
[EMAIL PROTECTED]
http://www.oliver.com/mailman/listinfo/u2-users
