OpenQuery Problems with Date Function Different MS-SQL 7 / MS-SQL

Hi
It is a change in the Microsoft Oracle driver to support the newer versions
of Oracle. In effect, it is a change required by Oracle that Microsoft had t
o
comply with so that the driver would continue to work. AFAIK, the change was
introduced in MDAC v2.5
Regards
Mike
"Klaus" wrote:

> hi,
>
> Currently we move a couple of SQL Server 7.0 to SQL Server 2000 and right
> now we habe big problems with a OPENQUERY Statement
> The SQL Server 7.0 and Windows 2000 is a german version, the MS-SQL Server
> 2000 and Windows 2000 is in english
>
> The Query on the MS-SQL Server 7 works fine
> SELECT * FROM OPENQUERY (ABKG002,
> 'SELECT DISTINCT NODP.AU_AUD_BENUTZER AS BA_AUD_ERST_VON FROM
> V3FADM.AUODP_ORDERPOS_OTAB NODP WHERE NODP.AU_AUD_ZEITPUNKT >
> ''01.03.2005''')
>
> With the same Query under MS-SQL 2000 is the result
> Server: Msg 7330, Level 16, State 2, Line 1
> Could not fetch a row from OLE DB provider 'MSDAORA'.
> [OLE/DB provider returned message: ORA-01843: not a valid month]
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IRowset::GetNextRows returne
d
> 0x80040e07].
>
>
> if we change the Query like below, the OPENQUERY is fine. IS THIS REALLY
> only the way the OPENQUERY works fine? That means for us we have to recrea
te
> a lot of VB Applications
> SELECT * FROM OPENQUERY (ABKG002, 'SELECT DISTINCT NODP.AU_AUD_BENUTZER AS
> BA_AUD_ERST_VON FROM V3FADM.AUODP_ORDERPOS_OTAB NODP WHERE
> NODP.AU_AUD_ZEITPUNKT > to_date(''22.03.2005'',''dd.mm.yyyy'')')
>
>
> Has anybody an IDEA?
>
>
> thx
> klaus
>
>
>Hi Mike
thanks for the very fast and good answer !
Klaus
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> schrieb im Newsbeitrag
news:5C2EC99E-CB18-4B1D-8DCA-6A04ED22255C@.microsoft.com...
> Hi
> It is a change in the Microsoft Oracle driver to support the newer
> versions
> of Oracle. In effect, it is a change required by Oracle that Microsoft had
> to
> comply with so that the driver would continue to work. AFAIK, the change
> was
> introduced in MDAC v2.5
> Regards
> Mike
> "Klaus" wrote:
>

open_tran = 1

We use i-net sprinta jdbc driver and use pooled connections. What I've
noticed is that when connections are not being used and are in sleeping
state Open_Tran in the sysprocesses table is 1 and not 0. Anyone know
why? We commit our transactions.
Doesn't matter if you COMMIT or not, if you continuely open another one,
then you'll see it open.
Here's what I've seen when our developer's have used JDBC drivers. They
continually SELECT 1 to keep their pooled connections open. They use SET
IMPLICIT TRANSACTIONS ON, which automatically starts transactions without
having to explicitly use the BEGIN TRANSACTION statement. Problem is that
they start on most T-SQL statements, including SELECT.
Sincerely,
Anthony Thomas

"Ken" <kshapley@.sbcglobal.net> wrote in message
news:1102791403.549396.160930@.z14g2000cwz.googlegr oups.com...
We use i-net sprinta jdbc driver and use pooled connections. What I've
noticed is that when connections are not being used and are in sleeping
state Open_Tran in the sysprocesses table is 1 and not 0. Anyone know
why? We commit our transactions.
|||Anthony,
You are right. Now my question is does this have any negative
consequences to performance. We are an asp with 100s of databases on
our sql 2000 servers, each with there own pooled connections. I have on
average over 3000 - 4000 of these connections always in an open
transaction state?
|||Yep, but I would be worried about that many concurrent connections, open
transactions or not. Are you running the 32-bit or 64-bit installation?
I ask because 32-bit has a serious memory drawback. Although the higher end
stuff can create a very nice Buffer Pool with AWE, all of the other memory
objects must be resident in the 2 GB--or 3 GB, depending on versions,
editions, and parameters--USER MODE address space. Moreover, the conection
structures are part of what lives in the MEM TO LEAVE region; so, you are
even more confined.
Now, you add object lock structures and multiple concurrent open
transactions and you are inviting disaster. I would seriously look at going
multi-instanced or the 64-bit installation, with a TON OF MEMORY, or course.
Sincerely,
Anthony Thomas

"Ken" <kshapley@.sbcglobal.net> wrote in message
news:1102990516.435766.91180@.c13g2000cwb.googlegro ups.com...
Anthony,
You are right. Now my question is does this have any negative
consequences to performance. We are an asp with 100s of databases on
our sql 2000 servers, each with there own pooled connections. I have on
average over 3000 - 4000 of these connections always in an open
transaction state?

open_tran = 1

We use i-net sprinta jdbc driver and use pooled connections. What I've
noticed is that when connections are not being used and are in sleeping
state Open_Tran in the sysprocesses table is 1 and not 0. Anyone know
why? We commit our transactions.Doesn't matter if you COMMIT or not, if you continuely open another one,
then you'll see it open.
Here's what I've seen when our developer's have used JDBC drivers. They
continually SELECT 1 to keep their pooled connections open. They use SET
IMPLICIT TRANSACTIONS ON, which automatically starts transactions without
having to explicitly use the BEGIN TRANSACTION statement. Problem is that
they start on most T-SQL statements, including SELECT.
Sincerely,
Anthony Thomas
"Ken" <kshapley@.sbcglobal.net> wrote in message
news:1102791403.549396.160930@.z14g2000cwz.googlegroups.com...
We use i-net sprinta jdbc driver and use pooled connections. What I've
noticed is that when connections are not being used and are in sleeping
state Open_Tran in the sysprocesses table is 1 and not 0. Anyone know
why? We commit our transactions.|||Anthony,
You are right. Now my question is does this have any negative
consequences to performance. We are an asp with 100s of databases on
our sql 2000 servers, each with there own pooled connections. I have on
average over 3000 - 4000 of these connections always in an open
transaction state?|||Yep, but I would be worried about that many concurrent connections, open
transactions or not. Are you running the 32-bit or 64-bit installation?
I ask because 32-bit has a serious memory drawback. Although the higher end
stuff can create a very nice Buffer Pool with AWE, all of the other memory
objects must be resident in the 2 GB--or 3 GB, depending on versions,
editions, and parameters--USER MODE address space. Moreover, the conection
structures are part of what lives in the MEM TO LEAVE region; so, you are
even more confined.
Now, you add object lock structures and multiple concurrent open
transactions and you are inviting disaster. I would seriously look at going
multi-instanced or the 64-bit installation, with a TON OF MEMORY, or course.
Sincerely,
Anthony Thomas
"Ken" <kshapley@.sbcglobal.net> wrote in message
news:1102990516.435766.91180@.c13g2000cwb.googlegroups.com...
Anthony,
You are right. Now my question is does this have any negative
consequences to performance. We are an asp with 100s of databases on
our sql 2000 servers, each with there own pooled connections. I have on
average over 3000 - 4000 of these connections always in an open
transaction state?

only Japanese got error from WebSphere to SQL 2000

Dear all:
Please help.
I have the java code to insert multi-language data from WebSphere in
Unix to MS SQL 2000 by using MS JDBC driver Service Pack 1 Version
2.2.0029.
All other language are ok but not Japanese.
I am sure that I input the correct Japanese in the statement before I
send the insert statement because I put the string into log file to
double check.
Does any one have a clue?
Thanks in advance
dennis_chen_canada@.hotmail.com
Dear all:
It is too bad.
I download and installed the MS SQL JDBC driver SP2.
The result is the same.
The traditional Chinese, simple Chinese, German, Franch are OK.
Only some Japanese will be garbage in the table. (some of the Charactor is ok, strange?)
more info:
- I log the sql statement before calling JDBC and it is correct Japanese letter
- I copy theis sql statement into a cold fusion (which use odbc to connection to the same MS SQL)
then run the cold fusion page, the result is OK in table
more question:
- How can I get the help from MS because the document said that this JDBC driver will be supported by MS?
It is kind of urgent.
Any help is welcome
|||dennis_chen_canada@.hotmail.com wrote:

> Dear all:
> It is too bad.
> I download and installed the MS SQL JDBC driver SP2.
> The result is the same.
> The traditional Chinese, simple Chinese, German, Franch are OK.
> Only some Japanese will be garbage in the table. (some of the Charactor is ok, strange?)
> more info:
> - I log the sql statement before calling JDBC and it is correct Japanese letter
> - I copy theis sql statement into a cold fusion (which use odbc to connection to the same MS SQL)
> then run the cold fusion page, the result is OK in table
> more question:
> - How can I get the help from MS because the document said that this JDBC driver will be supported by MS?
> It is kind of urgent.
> Any help is welcome
>
Hi. You would only get support from MS by paying for support. They rarely
answer posts here unless they can identify the poster as a paying support
customer... I suggest you try a commercial driver. Try the DataDirect driver,
because that is likely to indicate how quickly MS will provide a fixed
driver. DataDiarect made MS's free driver and will likely make the next version.
Their commercial driver is their most advanced product, so if that works, it will
show that they know the problem already. If you do buy a commercial driver you
will likely get faster support.
Joe
|||From: Dennis Chen <dennis_chen_canada@.hotmail.com>
To: joeNOSPAM@.bea.com
Subject: Re: only Japanese got error from WebSphere to SQL 2000
Dear Joe:
Thanks for your email.
I will try to get it later on.
Currrently, we are facing the urgent issue that comes from customers.
Do you have any other suggestion.
rgds,
Dennis Chen
Techinical Manager, E-Commerce Div.
www.ulead.com
|||dennis_chen_canada wrote:

> From: Dennis Chen <dennis_chen_canada@.hotmail.com>
> To: joeNOSPAM@.bea.com
> Subject: Re: only Japanese got error from WebSphere to SQL 2000
> Dear Joe:
> Thanks for your email.
> I will try to get it later on.
> Currrently, we are facing the urgent issue that comes from customers.
> Do you have any other suggestion.
> rgds,
> Dennis Chen
> Techinical Manager, E-Commerce Div.
> www.ulead.com
Call Microsoft technical support and pay them to take your case,
but don't expect rapid response because typical MS support only
has a vague idea of what Java/JDBC is, so they would eventually
pass the case on to the folks in MS who talk to the external company
that makes the driver...
I would create a standalone jdbc program that demonstrates the problem
if possible. I would suggest also trying to get support from IBM
Websphere folks. They should have some MS/JDBC experts. If you had
been using BEA's Weblogic, I would have been able to do more, and
would have solved your problem...
Joe Weinstein at BEA

>