RE: ODBC Error with Sybase from Win2K Server (but works from WinXP!)
- From: petery@xxxxxxxxxxxxxxxxxxxx (Peter Yang [MSFT])
- Date: Thu, 27 Oct 2005 05:53:12 GMT
Hello,
To narrow down the issue, I'd like to know if you create a DSN in odbc
configuration to test the Sybase odbc driver, does it work properly? Also,
does the issue occur if you run a simple query instead of this specific SP
in DTSrun?
If the issue occurs with a long-running query, it might be a performance
related issue. When configuring the DSN for the ODBC connection to Sybase,
change the selectMode property from the performance tab to 0-Cursor Mode .
When the DSN is set to use the direct mode, each statement uses a different
SQL Server connection. Select statements execute directly without using
database cursors. This could result in a performance issue. Changing to
Cursor mode 0 allows database cursors to be used. In some cases involving
large numbers of sequential Select statements, performance degradation may
result because of the overhead associated with creating database cursors.
Also, you may want to see if you can setup a linked server to sybase as per
the following KB article and use that to connect to Sybase in your DTS
packages and see if that gives you a better performance.
280102 How to set up a linked server to a Sybase database server and
http://support.microsoft.com/?id=280102
Fetch buffer size: Increasing the fetch buffer size should help in
increasing the performance in general. Some FAQs on this parameter:
What does the fetch buffer size parameter do?
The FetchBufferSize (FBS) Parameter in DTS is the number of records
fetched at one time from the OLE DB provider. Increasing this number
increases performance at the cost of memory on the host computer. In
general, the larger the FBS setting, the
better your performance will be.
Hope this is helpful. Since it is related third party odbc driver, it's
better you incoude their support in narrow down the issues.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/default.aspx?scid=%2finternational.aspx.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------
>Thread-Topic: ODBC Error with Sybase from Win2K Server (but works from
WinXP!)
>thread-index: AcXaHWSJeLPenDcgRritxmJcLG/2xw==
>X-WBNR-Posting-Host: 198.240.128.75
>From: "=?Utf-8?B?UGF0cmljaw==?=" <questions@xxxxxxxxxxxxxxxx>
>References: <76C2D463-C607-4770-918F-398C39B14CCF@xxxxxxxxxxxxx>
>Subject: RE: ODBC Error with Sybase from Win2K Server (but works from
WinXP!)
>Date: Wed, 26 Oct 2005 04:07:02 -0700
>Lines: 102
>Message-ID: <E7C596C3-5CDA-486B-AFEA-032DBE0E9DCC@xxxxxxxxxxxxx>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.odbc
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.odbc:2905
>X-Tomcat-NG: microsoft.public.sqlserver.odbc
>
>Note that in most cases, the error occured BEFORE the Sybase stored proc
>created the tempDB..tempTable in the SELECT INTO part of the stored
procedure.
>
>"Patrick" wrote:
>
>> Our company only allow the use of Merant 3.60 32-bit Sybase ODBC Driver
>> (Version) for connection to Sybase Sybase 12.5.3 DB on Sun OS 5.3)
>>
>> I have a stored procedure on Sybase 12.5 ASE which
>> a) Select 'dummy'
>> b) Select lots of stuff using joins and filters into tempDB.tempTable
>> using dirty read
>>
>> I tried
>> 1) execute stroed proc on using Sybase 12 client Windows isql.exe
(38,400
>> bytes) ------ this succeeded
>>
>> 2) Tried to execute same stored procedure from a Microsoft SQL Server
2000
>> DTS Package using
>> 2.1) SQL Server 2000's DTSRun.exe (Version 2000.80.194.0)
>> 2.2) Merant 3.60 32-bit Sybase ODBC Driver
>> 2.3) same Sybase 12 windows client (script installed as per 1 above)
>> 2.4.1) When run from Windows 2000 Server SP4
>> 2.4.1.1) Following sys.out is returned to DTS:
>> Step 'DTSStep_DTSExecuteSQLTask_6' failed
>> Step Error Source: Microsoft OLE DB Provider for ODBC Drivers
>> Step Error Description:[MERANT][ODBC Sybase
>> driver][Sybase]ct_connect(): user api layer: internal Client Library
>> error: Read from the server has timed out.
>> Step Error code: 80004005
>> Step Error Help File:
>> Step Error Help Context ID:0
>> Step Execution Started: 10/24/2005 3:12:05 PM
>> Step Execution Completed: 10/24/2005 3:12:30 PM
>> Total Step Execution Time: 25.062 seconds
>> Progress count in Step: 0
>>
>> 2.4.1.2) ODBC Trace file indicate the following:
>> fMYDTS_NAME a58-bf4 EXIT SQLDriverConnectW with return code -1
(SQL_ERROR)
>> HDBC 028118D8
>> HWND 00000000
>> WCHAR * 0x1F7B1D38 [ -3] "******\ 0"
>> SWORD -3
>> WCHAR * 0x1F7B1D38
>> SWORD 8
>> SWORD * 0x00000000
>> UWORD 0 <SQL_DRIVER_NOPROMPT>
>>
>> DIAG [S1T00] [MERANT][ODBC Sybase driver][Sybase]ct_connect(): user
api
>> layer: internal Client Library error: Read from the server has timed
out.
>> (63)
>>
>> fMYDTS_NAME a58-bf4 ENTER SQLGetDiagRecW
>> SQLSMALLINT 2
>> SQLHANDLE 028118D8
>> SQLSMALLINT 1
>> SQLWCHAR * 0x02C4F60C (NYI)
>> SQLINTEGER * 0x02C4F630
>> SQLWCHAR * 0x02C4F20C (NYI)
>> SQLSMALLINT 512
>> SQLSMALLINT * 0x02C4F634
>>
>> fMYDTS_NAME a58-bf4 EXIT SQLGetDiagRecW with return code 0
(SQL_SUCCESS)
>> SQLSMALLINT 2
>> SQLHANDLE 028118D8
>> SQLSMALLINT 1
>> SQLWCHAR * 0x02C4F60C (NYI)
>> SQLINTEGER * 0x02C4F630 (63)
>> SQLWCHAR * 0x02C4F20C (NYI)
>> SQLSMALLINT 512
>> SQLSMALLINT * 0x02C4F634 (132)
>>
>> fMYDTS_NAME a58-bf4 ENTER SQLGetDiagRecW
>> SQLSMALLINT 2
>> SQLHANDLE 028118D8
>> SQLSMALLINT 2
>> SQLWCHAR * 0x02C4F60C (NYI)
>> SQLINTEGER * 0x02C4F630
>> SQLWCHAR * 0x02C4F20C (NYI)
>> SQLSMALLINT 512
>> SQLSMALLINT * 0x02C4F634
>>
>> fMYDTS_NAME a58-bf4 EXIT SQLGetDiagRecW with return code 100
>> (SQL_NO_DATA_FOUND)
>> SQLSMALLINT 2
>> SQLHANDLE 028118D8
>> SQLSMALLINT 2
>> SQLWCHAR * 0x02C4F60C (NYI)
>> SQLINTEGER * 0x02C4F630
>> SQLWCHAR * 0x02C4F20C (NYI)
>> SQLSMALLINT 512
>> SQLSMALLINT * 0x02C4F634
>>
>> HOWEVER, exeute the same stored proc on my workstation (WinXP Pro SP1,
with
>> otherwise the same scripted install config), and the DTS step that
execute
>> the Stored proc succeedeed. Confused! How could I investigate? Same
logon
>> credentials used in both server and WinXP environments! Tracert from
>> workstation and server are OK (<10ms in each hop). Even using Sybase
ISQL to
>> execute stored proc on Sybase (using sybase open client instead of ODBC
works
>> from Dev/UAT Win2K server SP4!).
>>
>> SOS!
>
.
- References:
- Prev by Date: Re: relinking tables using MS Access
- Next by Date: RE: ODBC SQLINTEGER & (BADMEM) - Why?
- Previous by thread: RE: ODBC Error with Sybase from Win2K Server (but works from WinXP!)
- Next by thread: Re: SQL Server ODBC Problem - Help!!!
- Index(es):
Relevant Pages
|
