Problem with Sybase and DTS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Problem with Sybase and DTS

Author

Topic

san
Starting Member

26 Posts

Posted - 2002-08-20 : 13:58:20

Folks..

I am trying to import data from Sybase machine (Adaptive enterprise
12.5) to a
MSSQL 7.0 (Win2000) servicepack 3 machine using DTS.
I am using ODBC to connect to Sybase and OLEDB to connect to MSSQL.
(when I sued OLEDB to connect to Sybase, it is saying "null accessors
are not supported".)
I could succesfully do mass pumping from a Sybase table to a MSSQL
table.
But When I tried to filter the source data using a stored procedure
(Preview works fine), nothing is happening. IOW, when i say execute,
it is going on for ever and ever..I had to kill the process.

Ok thats my story with one machine. When I tried the same with a MSSQL
7.0 (servicepack 3 ) installed on a WinNT box everything works fine.
ie, I could pump from table to table with out any filters..ALSO, I
could filter the source using an sp and pump it to the destination
table.

It seems like things wrok fine in WinNt but not with Win2000. Folks
any of you have this issue before...Please share your thoughts.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-08-20 : 14:04:53

I'd hazard a guess that the Win2K machine has different MDAC components installed than the NT machine does, and the features of the Sybase ODBC driver are not compatible with it. You can get the version numbers for both machines in the ODBC Control Panel under the Drivers tab.

In case the NT machine has newer drivers (higher version number), then you can upgrade the Win2K machine by downloading the MDAC components here:

http://www.microsoft.com/data/download.htm

However, in all likelihood the MDAC components on the NT box are older, and if you upgrade them you'll get the same behavior as the Win2K box.

san
Starting Member

26 Posts

Posted - 2002-08-20 : 15:12:55

Thanks rob.
I could see that both the winNt and Win2000 box have Sybase ODBC provider version 3.7 on it. But how can I see what is the MDAC version running on two boxes ?
Also, SQL server driver is showing a version of "2000.71.7713" in Win2000 box . Does that mean MDAC is 2.71.7713 ?

San.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-08-20 : 16:53:45

Here's a little snippet of VB code borrowed from the ADO help file:

Public Sub VersionX()

   Dim cnn1 As ADODB.Connection
   Dim strCnn As String
   Dim strVersionInfo As String

   ' Open connection.
   Set cnn1 = New ADODB.Connection
   strCnn = "driver={SQL Server};server=srv;" & _
      "user id=sa;password=;database=Pubs;"
   cnn1.Open strCnn
   
   strVersionInfo = "ADO Version: " & cnn1.Version & vbCr & _
   "DBMS Name: " & cnn1.Properties("DBMS Name") & vbCr & _
   "DBMS Version: " & cnn1.Properties("DBMS Version") & vbCr & _
   "OLE DB Version: " & cnn1.Properties("OLE DB Version") & vbCr & _
   "Provider Name: " & cnn1.Properties("Provider Name") & vbCr & _
   "Provider Version: " & cnn1.Properties("Provider Version") & vbCr & _
   "Driver Name: " & cnn1.Properties("Driver Name") & vbCr & _
   "Driver Version: " & cnn1.Properties("Driver Version") & vbCr & _
   "Driver ODBC Version: " & cnn1.Properties("Driver ODBC Version")

   MsgBox strVersionInfo

   cnn1.Close

End Sub

You'd have to make a VB/Access/Excel module out of it, because MsgBox() doesn't work in ASP. If you change the connection string to match your Sybase settings you might get some more info on what that driver supports as well.

san
Starting Member

26 Posts

Posted - 2002-08-22 : 10:22:53

Guys..

It seems like MDAC 2.7 is the culprit here. first i thought it was Win 2000. I have MDAC 2.5 on another win2000 machine and there everything worked great...

San.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources