Re: SQL Server , UniOLEDB & subtables
Thanks again david I am assuming that uci needs to be restarted for this to take effect and am waiting for the client to do so. In case anyone is at all interested, it seems that this problem goes away if the NUMERIC column doesn't contain any data values - ie all values are NULL I have asked that the client try to execute the SQL Server command that is hanging me up locally on one of their SQL Server machines to determine if this is something that is particular to my SQL Server machine or to their universe system. No word on this yet. gerry - Original Message - From: "Hona, David S" <[EMAIL PROTECTED]> To: "'U2 Users Discussion List'" <[EMAIL PROTECTED]> Sent: Wednesday, March 10, 2004 2:08 AM Subject: RE: SQL Server , UniOLEDB & subtables > > Gerry, > > Enter the values stated, separated by spaces, I believe. > > We're not having problems with our UCI connections, so have never needed to > debug them. It's a new feature, so perhaps it doesn't actually work as > documented! > > Regards > David > > > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On > Behalf Of gerry simpson > Sent: Tuesday, March 09, 2004 10:52 PM > To: U2 Users Discussion List > Subject: Re: SQL Server , UniOLEDB & subtables > > > Thanks David > > the docs 'explain' how to enable server side uci logging via the > UVHOME/serverdebug file this file doesn not currently exist and no format > beyond 'column' values is given. do you know what the format of this file is > ? what delimits a column , space , comma , tab , colon ? i've tried all 4 > but have yet to see a debug log file appear. do we have to restart uci/uv to > have changes take effect ? > > gerry > > > > > - Original Message - > > From: "Hona, David S" <[EMAIL PROTECTED]> > > To: "'U2 Users Discussion List'" <[EMAIL PROTECTED]> > > Sent: Tuesday, March 09, 2004 2:26 AM > > Subject: RE: SQL Server , UniOLEDB & subtables > > > > > > > > > > I suggest you read-up on how to enable the UCI logging feature. As > > UniOLEDB > > > uses UCI to communicate with UV. > > > > > > See 'Administrative Supplement for Client APIs' pages 3-21 and 3-22. > > > > > > Of course, this will just help with issues at the UV end. > > > > > > Regards > > > David > > > > > > > > > -Original Message- > > > From: [EMAIL PROTECTED] > > > [mailto:[EMAIL PROTECTED] > On > > > Behalf Of gerry simpson > > > Sent: Tuesday, March 09, 2004 5:38 AM > > > To: [EMAIL PROTECTED] > > > 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 ; [snip] > > > > > > 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 -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users
RE: SQL Server , UniOLEDB & subtables
Gerry, Enter the values stated, separated by spaces, I believe. We're not having problems with our UCI connections, so have never needed to debug them. It's a new feature, so perhaps it doesn't actually work as documented! Regards David -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of gerry simpson Sent: Tuesday, March 09, 2004 10:52 PM To: U2 Users Discussion List Subject: Re: SQL Server , UniOLEDB & subtables Thanks David the docs 'explain' how to enable server side uci logging via the UVHOME/serverdebug file this file doesn not currently exist and no format beyond 'column' values is given. do you know what the format of this file is ? what delimits a column , space , comma , tab , colon ? i've tried all 4 but have yet to see a debug log file appear. do we have to restart uci/uv to have changes take effect ? gerry > - Original Message - > From: "Hona, David S" <[EMAIL PROTECTED]> > To: "'U2 Users Discussion List'" <[EMAIL PROTECTED]> > Sent: Tuesday, March 09, 2004 2:26 AM > Subject: RE: SQL Server , UniOLEDB & subtables > > > > > > I suggest you read-up on how to enable the UCI logging feature. As > UniOLEDB > > uses UCI to communicate with UV. > > > > See 'Administrative Supplement for Client APIs' pages 3-21 and 3-22. > > > > Of course, this will just help with issues at the UV end. > > > > Regards > > David > > > > > > -Original Message- > > From: [EMAIL PROTECTED] > > [mailto:[EMAIL PROTECTED] On > > Behalf Of gerry simpson > > Sent: Tuesday, March 09, 2004 5:38 AM > > To: [EMAIL PROTECTED] > > 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 ; [snip] > > > > 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
Re: SQL Server , UniOLEDB & subtables
Thanks David the docs 'explain' how to enable server side uci logging via the UVHOME/serverdebug file this file doesn not currently exist and no format beyond 'column' values is given. do you know what the format of this file is ? what delimits a column , space , comma , tab , colon ? i've tried all 4 but have yet to see a debug log file appear. do we have to restart uci/uv to have changes take effect ? gerry > - Original Message - > From: "Hona, David S" <[EMAIL PROTECTED]> > To: "'U2 Users Discussion List'" <[EMAIL PROTECTED]> > Sent: Tuesday, March 09, 2004 2:26 AM > Subject: RE: SQL Server , UniOLEDB & subtables > > > > > > I suggest you read-up on how to enable the UCI logging feature. As > UniOLEDB > > uses UCI to communicate with UV. > > > > See 'Administrative Supplement for Client APIs' pages 3-21 and 3-22. > > > > Of course, this will just help with issues at the UV end. > > > > Regards > > David > > > > > > -Original Message----- > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On > > Behalf Of gerry simpson > > Sent: Tuesday, March 09, 2004 5:38 AM > > To: [EMAIL PROTECTED] > > 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 ; > > [snip] > > > > 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 > > > > -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users
RE: SQL Server , UniOLEDB & subtables
I suggest you read-up on how to enable the UCI logging feature. As UniOLEDB uses UCI to communicate with UV. See 'Administrative Supplement for Client APIs' pages 3-21 and 3-22. Of course, this will just help with issues at the UV end. Regards David -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of gerry simpson Sent: Tuesday, March 09, 2004 5:38 AM To: [EMAIL PROTECTED] 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 ; [snip] 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
Re: SQL Server , UniOLEDB & subtables
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 ; > @IDS1 S2M1 M2 > AAA A B CD > G H > BBB I J K L > M N > O P > > SELECT * FROM TEST.TAB_MV ; > @IDM1 M2 @ASSOC_ROW > AAA CD 1 > G H 2 > BBBK L 1 > MN2 > O P3 > > > 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
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 ; @IDS1 S2M1 M2 AAA A B CD G H BBB I J K L M N O P SELECT * FROM TEST.TAB_MV ; @IDM1 M2 @ASSOC_ROW AAA CD 1 G H 2 BBBK L 1 MN2 O P3 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