Microsoft OLE DB Provider for SQL Server Object was open
Wayne
I am trying to upgrade a PB 6.5 app to 10.2.1. I assume this app used to
work just fine under PB 6.5 and MS SQL Server 6.5. Now I am connecting to
SQL Server 2000 and get the following error message:
Microsoft OLE DB Provider for SQL Server Object was open.
This happens at an INSERT statement after FETCHing some data from a declared
cursor. It seems like the cursors fetch is happening on the open DB
connection and not allowing the insert statement to also use the connection
while holding the cursor. Has anybody else come across this, or am I just
not understanding what is happening. Any insight is appreciated.
Thanks!
Wayne
M. Searer
others have found regarding this.
Basically the OLEDB interface (MS) doesn't support this. I think the SQL 2005
'native client' will, but I'm not 100% sure.
"Wayne" <wayne...@eandm.com> wrote in message news:4463b3b7$1@forums-2-dub...
M. Searer
"M. Searer" <nos...@nospam.com> wrote in message news:4464ab99@forums-1-dub...
Wayne
Wayne
"M. Searer" <nos...@nospam.com> wrote in message
news:4464c0d7$1@forums-2-dub...
![Jim O'Neil [Sybase]'s profile photo](https://lh3.googleusercontent.com/a/default-user=s40-c)
Jim O'Neil [Sybase]
Result Sets (MARS)... a future version of PB (> 10 and < 12, wink)
will have this support through SQL Native Client.
To my understanding the support CANNOT be provided through OLE-DB or
ODBC because of limitation of MDAC (the Microsoft DataAccess
Components).
Jon Brabham
Title is SQLOLEDB - DBPROP_MULTIPLECONNECTIONS.
I think if we can change this setting we can get it work, but I can not find
a good example of how to change it. I have only found ADO verisons.
Jon Brabham
"Jim O'Neil [Sybase]" <joneil_@_sybase_dot_com> wrote in message
news:cap962h3nbb9sjook...@4ax.com...
Jon Brabham
This seems to only be an issue with PowerScript. If we put the same cursor
and its logic into a stored procedure and execute it via PowerScript the
code is successful. We will probably end up working around the issue in
this manner.
Hope this helps,
Jon Brabham
"Jon Brabham" <jbra...@hsesystems.com> wrote in message
news:4469e607$1@forums-1-dub...
Waleed Seada
Regards
Waleed Seada
"M. Searer" <nos...@nospam.com> wrote in message
news:4464ab99@forums-1-dub...
M. Searer
That would be using OLEDB with SQLNCLI as the provider
"Waleed Seada" <waleed...@yahoo.com> wrote in message
news:45ed4bcf@forums-1-dub...
Chris Pollach
NO... you will have to upgrade to PB 11. SNC is a new supported driver.
Basically, SNC is "fastpath" through OLE-DB.
However, here are my latest performance benchmarks ....
Scenario#1: 114,196 rows selected from a table. Each is set-up identically
in ASE 15 and SS2005. No where, ordered by or grouped by clauses - a
standard SQL projection. I am using PB 10.2.1, 10.5.1 and 11 Beta 2 - build
6056 compiles for my test application using a standard DW Retrieve ( ) =>
ASE 15
Native - 78ms
SS2005
ADO.net - 12,031ms
ODBC - 1,765ms
OLE-DB - 3,875ms
SNC - 1,984ms
The test surprised me in two ways - 1) how slow ADO.Net was compared to
any other connectivity options; and 2) even though SNC was better than
OLE-DB (because it is a "fast path" option through OLE-DB) it still did not
beat ODBC!
So in summary compared to ASE 15 (baseline on a native driver):
ADO.Net - ~154.2 times slower
ODBC - ~22.6 times slower
OLE-DB - ~49.7 times slower
ODBC - ~25.4 times slower
Regards ... Chris
PS: Have you "hugged" your DataWindow today! -:)
PPS: I love my ASE 15!
"Waleed Seada" <waleed...@yahoo.com> wrote in message
news:45ed4bcf@forums-1-dub...
M. Searer
possible.
What was the packet size (server and client) - I would think that could make a
big difference in bringing back 100,000 rows, although less than 1/10 of second
still doesn't seem possible even if you have your packet size set huge.
"Chris Pollach" <cpol...@travel-net.dot.com> wrote in message
news:45eecbb4$1@forums-1-dub...
Chris Pollach
Observation #4: SS does NOT trim trailing spaced from CHAR columns. ASE
does! Soooo .. if you are transmitting allot of Char data and many rows, ASE
can pack more rows per packet than SS!!!! However, I see this is a
configuration option in SS2005. I will turn this ON for trimming (default is
OFF) and run the tests again and let you know the outcome.
"Chris Pollach" <cpol...@travel-net.dot.com> wrote in message
news:45f00db4@forums-1-dub...
>
> Packsize: 8K
>
> Observation #1: I have one possible behavioral difference and it is also
> appearing now on other tests today. When ASE builds a RS and it has enough
> rows to fill a packet it sends it right away (PB connectivity setting
> Async=1). Each data packet requires an acknowledgment packet - but because
> ASE has shipped the 1st packet sooner, the acknowledgement sequence starts
> right away. It seems that SS2005 waits for the entire RS before sending
the
> 1st data packet - you snooze you loose.
>
> Observation #2: PB keeps the SQL in ANSI form. For ODBC for example, it
> converts the ANSI SQL into ODBC SQL. ODBC then converts this to SS SQL. On
> the SS side ... SS builds an ANSI RS and ships this to ODBC. ODBC
rebuilds
> the RS into an ODBC RS. PB's ODBC driver converts the ODBC RS into an ANSI
> RS. I suspect that OLE-DB is similar. For the old native MS db driver
(like
> ASE 15's Open Client) - both PB and the db driver are one. PB sends T-SQL
> and process the ASE RS in binary form. That is what the old MS driver used
> to do!!!!!!
>
> Observation #3: I have changed the SQL in the DW for sorting, different
> projection scenarios, etc and the ADO.Net timing on 144K rows is +/-100ms.
> So I assume that most of the over-head in ADO.Net is on the client side
(aka
> .Net overhead or bad RS handling design).
>
> Last but not least - if you were here I would show you the PB test window.
> There is nothing like seeing the test screen speed for yourself ...OK here
> they are (see attached)
>
> Oh God, some more tweaking on the ASE side and I have it down to 39ms! I
> better stop before I wet my pants on how slow SS really is - ROFL!
>
>
>
> "M. Searer" <nos...@nospam.com> wrote in message
> news:45ef5fb2$1@forums-1-dub...
M. Searer
Try scrolling (via script) to the last row after the retrieve statement. I
would be interested to know if you are seeing the first N rows immediately, or
if you are in fact getting ALL of the rows. Even if it is just the first N
rows, that is still great since the perception of speed is what the users are
interested in. I'm surprised that none of the vendors have implemented any kind
of compression in the transmission (or maybe some have?).
I *believe* oracle does something similar to this too (waiting for the rs), that
plus the non-blocking feature of its redo logs that enables reads while a write
is occurring. Although this is a great feature for concurrency, the trade off
is that IME oracle is a LOT slower on retrieves than either Sybase or MS.
Also, your mileage may vary: My tests show Sybase ASE 15 a lot slower on some
things than MS & Oracle. I have an application that creates my tables,
procedures, and populates with test data. Sybase is at least 4 times as slow as
MS; and about 2 twice as slow than Oracle. I also have some sql selects that
take several minutes compared to < 2 seconds for MS/Oracle.
"Chris Pollach" <cpol...@travel-net.dot.com> wrote in message
news:45f00db4@forums-1-dub...
>
> Packsize: 8K
>
> Observation #1: I have one possible behavioral difference and it is also
> appearing now on other tests today. When ASE builds a RS and it has enough
> rows to fill a packet it sends it right away (PB connectivity setting
> Async=1). Each data packet requires an acknowledgment packet - but because
> ASE has shipped the 1st packet sooner, the acknowledgement sequence starts
> right away. It seems that SS2005 waits for the entire RS before sending the
> 1st data packet - you snooze you loose.
>
> Observation #2: PB keeps the SQL in ANSI form. For ODBC for example, it
> converts the ANSI SQL into ODBC SQL. ODBC then converts this to SS SQL. On
> the SS side ... SS builds an ANSI RS and ships this to ODBC. ODBC rebuilds
> the RS into an ODBC RS. PB's ODBC driver converts the ODBC RS into an ANSI
> RS. I suspect that OLE-DB is similar. For the old native MS db driver (like
> ASE 15's Open Client) - both PB and the db driver are one. PB sends T-SQL
> and process the ASE RS in binary form. That is what the old MS driver used
> to do!!!!!!
>
> Observation #3: I have changed the SQL in the DW for sorting, different
> projection scenarios, etc and the ADO.Net timing on 144K rows is +/-100ms.
> So I assume that most of the over-head in ADO.Net is on the client side (aka
> .Net overhead or bad RS handling design).
>
> Last but not least - if you were here I would show you the PB test window.
> There is nothing like seeing the test screen speed for yourself ...OK here
> they are (see attached)
>
> Oh God, some more tweaking on the ASE side and I have it down to 39ms! I
> better stop before I wet my pants on how slow SS really is - ROFL!
>
>
>
> "M. Searer" <nos...@nospam.com> wrote in message
> news:45ef5fb2$1@forums-1-dub...
Chris Pollach
I am using the DW Control RetrieveEnd event - so this means the last row in
the last packet, regardless of the DBMS!!!!!!!
"M. Searer" <nos...@nospam.com> wrote in message
news:45f03115@forums-1-dub...
M. Searer
If your avg row size is 250 bytes, and you have 114,000 rows, then your total
size is 28.5 megabytes (plus any overhead added in)
On a 100 megabit network it should take over 2 seconds to copy a file of 28.5 MB
in size.
2.28 seconds for:
28.5/(100,000,000/8) - not counting any overhead for the actual transmission.
if it is 100 bytes wide, then it is about .912 seconds.
10 bytes then it is .09 seconds, which is 91 ms and still is beat by your 78 ms.
Network transmission overhead should add 20-30% to the time.
I am missing something on my napkin math here?
Are you not going through a network, but running these tests on the server to
remove the network time? If that is the case, are you using the 'in-memory'
transmission for ASE and not the others? IE - for MS SQL, if you use a period
(.) for the server name, it won't go through the network layers and so you get
much better response time.
"Chris Pollach" <cpol...@travel-net.dot.com> wrote in message
news:45f03c7a$1@forums-1-dub...
Alex Bibiano González
is SS padding CHAR columns?
"Chris Pollach" <cpol...@travel-net.dot.com> escribió en el mensaje
news:45f690e1@forums-1-dub...
>
> No, this is not a server test. The same standard workstation is being used
> across the internal network to SS and ASE. You can see the data being
> retrieved as I have attached a copy of the DW column specifications. One
> of
> the big items I have noticed are the large CHAR columns. In ASE it does
> NOT
> pad these out but SS pads the data in the packets out with trailing
> spaces.
> ASE on the other hand treats the data as VARCHAR in the packets!
>
>
>
> "M. Searer" <nos...@nospam.com> wrote in message
> news:45f1c053@forums-1-dub...
Chris Pollach
That only trims at the client side and does not make the packet
transmissions efficient!
This only masks the symptom and does not cure the problem which is SS <bg>.
"Alex Bibiano González" <abib...@nikel.es> wrote in message
news:45f7bff2@forums-1-dub...
Terry Dykstra
does not pad spaces at the end.
--
Terry Dykstra (TeamSybase)
http://powerbuilder.codeXchange.sybase.com/
http://www.pb9books.com
product enhancement requests:
http://my.isug.com/cgi-bin/1/c/submit_enhancement
"Chris Pollach" <cpol...@travel-net.dot.com> wrote in message
news:45f7fbfb$1@forums-1-dub...
Chris Pollach
That is why Oracle is even slower <lol>!!!!
"Terry Dykstra" <tddy...@forestoil.ca> wrote in message
news:45f80146$1@forums-1-dub...