June 24, 2009 at 3:10 am
Does anyone have a solution to our problem? We are using a 64bit version of SQL server and need data from Sybase. We bought an OLE DB and a ODBC 64 bit driver for Sybase from OpenLinc, but it is VERY expensive due to the fact that it is limited to a few simultaneous connections. ( I dont want to use SSIS and a 32 bit driver for Sybase )
/Håkan Winther
/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
June 30, 2009 at 10:52 am
It aint pretty but you could seperate the process up some and move some of it to a 32-bit SQL box that could access Sybase.
Otherwise you are stuck using 64-bit drivers because that is all 64-bit SQL will take, are there any other providers of OLEDB drivers for Sybase? I admit I haven't looked but there are usually a couple of providers..
CEWII
July 1, 2009 at 3:29 am
Elliott (6/30/2009)
It aint pretty but you could seperate the process up some and move some of it to a 32-bit SQL box that could access Sybase.Otherwise you are stuck using 64-bit drivers because that is all 64-bit SQL will take, are there any other providers of OLEDB drivers for Sybase? I admit I haven't looked but there are usually a couple of providers..
CEWII
We do not want to administrate another SQL server, so we are stuck with the OpenLink driver. There are at least two 64-bit drivers on the market, but they are really expensive (and we need 16 licences for 20 user sessions per server, one per CPU core). :crying:
Thanks anyway!
/Håkan Winther
/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
July 1, 2009 at 7:19 am
Yikes!
I don't blame you for not wanting to administer another server, but like many things its a question of cost, is the maint/admin costs of the box acceptable in light of the cost of the alternatives. Sometimes yes, and sometimes no.. You could also look at a virtualized box..
CEWII
July 1, 2009 at 9:18 am
I tried to add a linked server on SQL Server 2008 SP1 64-bit on Windows 2008 standard server, using ASEOLEDB provider to connect to a Sybase database. Did register it running 'regsvr32 sybdrvoledb.dll'. Does anyone know why ASEOLEDB is not shown in the list of Providers under SQL Management Studio - Server Objects/Linked Servers/Providers tab?
July 2, 2009 at 5:16 am
calgeorge (7/1/2009)
I tried to add a linked server on SQL Server 2008 SP1 64-bit on Windows 2008 standard server, using ASEOLEDB provider to connect to a Sybase database. Did register it running 'regsvr32 sybdrvoledb.dll'. Does anyone know why ASEOLEDB is not shown in the list of Providers under SQL Management Studio - Server Objects/Linked Servers/Providers tab?
As you mention in your post, you register the 32 bit driver and it cannot be used by the 64 bit SQL server. That is exactly my issue, the only 64 bit driver I found is very expensive. In my environment the driver will cost at least 100 000 USD, and my client thinks it is too expensive.
/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
July 2, 2009 at 8:50 am
Wonder if there is any 64-bit ODBC driver for Sybase that can be used instead of ASEOLEDB provider.
July 2, 2009 at 9:05 am
There are at least two drivers on the market, DataDirect or OpenLink. Remember that you need OLE for ODBC and the ODBC driver from OpenLink, and it is not cheap. You pay one licence per processor core, and the licence you buy is for 5, 10 or 25 concurrent connections.
/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
August 5, 2009 at 9:44 am
OK I installed the 64bit MDASQL provider, SQL Server 2K8 SP1. The provider shows. I am able to create the linked server, but when I try to access Sybase, I get the following:
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "FACETS". (.Net SqlClient Data Provider)
Microsoft SQL Server, Error: 7303)
Any Ideas?
August 5, 2009 at 4:06 pm
The name of that provider sounds like the MS SQL Provider.. Are you using the Sybase provider (I know stupid question, but I gotta ask)..
CEWII
September 27, 2009 at 8:49 am
I have a 64-bit Windows 2003 server with SQL 2005 Standard (64 bit). trying to delete and insert rows into a Sybase ASE 15 database.
I have successfully done the following:
Installed the 64 bit Sybase ASE 15 PCClient. Then set up a System DSN:
Use the Start > Programs > Sybase > DataAccess(64-bit) > OdbcDataSourceAdministrator to set up an ODBC System DSN
Click on System DSN tab
Click on Add to add a new DSN.
Choose the Adaptive Server Enterprise Version 15.00.00.350 from Sybase (SYBDRVODBC64.DLL dated 04 May 2009)
Click on Finish
Screen pops up to configure it.
Fill in
Data Source Name : whatever you want to name it - I used SyBaseData
SERVERName (ASE Host Name): Your ServerName
Server Port: 5000
Database Name: Test
Login ID: MyUserName
Back End Type: ASE
Click on test connection – fill in password for user MyUserName
Click OK
I Installed the 64-bit MSDASQL software downloaded from http://www.Microsoft.com/downloads/details.aspx?FamilyID=000364db-5e8b-44a8-b9be-ca44d18b059b&displaylang=en
Go to SSMS and run the following script:
/****** Object: LinkedServer [SyBase2Write] Script Date: 08/26/2009 11:00:24 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'SyBase2Write', @srvproduct=N'Sybase', @provider=N'MSDASQL', @datasrc=N'SyBaseData' --The System DSN you set up in ODBC above
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SyBase2Write',@useself=N'False',@locallogin=NULL,@rmtuser=N'MyUserName,@rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption @server=N'SyBase2Write', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SyBase2Write', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SyBase2Write', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SyBase2Write', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SyBase2Write', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SyBase2Write', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SyBase2Write', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SyBase2Write', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'SyBase2Write', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'SyBase2Write', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SyBase2Write', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'SyBase2Write', @optname=N'use remote collation', @optvalue=N'true'
I was then able to Insert rows using
insert OPENQUERY(SyBase2Write,'SELECT * FROM YourSybaseDbName.dbo.YourTableName') SELECT Field1, Field2, Field3 ... FROM SqlDbName.dbo.SqlTableName Where ....
Order By ...
I, however battled to delete the rows for the current month, I needed to. I eventually set up the Deletion as a Stored Proc in Sybase and executed it by using
SET ROWCOUNT 10
EXEC ('YourSybaseDbName.dbo.sp_delete_MySyBaseRows') at SyBase2Write
SET ROWCOUNT 0
I found that I needed the Set Rowcount 10 otherwise sybase seemed to hang.
This solution took approx 3 weeks of on-off research, as no-one seemed to have a complete answer.
I trust that solves your issue.
Regards
Harold Joubert
Snr BI Developer
October 1, 2009 at 12:51 pm
After reading tips in the article from sybase at this link ( http://www.sybase.com/detail?id=1056225&EMC=notificationmysybase&attr=1056225 ) I was able to install the 64-bit ODBC driver and subsequently create a linked server on sql server 2008 to sybase.
October 13, 2009 at 5:39 am
Thank you!! This solved my issue and you saved us a lot of money.
Harold Joubert (9/27/2009)
I have a 64-bit Windows 2003 server with SQL 2005 Standard (64 bit). trying to delete and insert rows into a Sybase ASE 15 database.I have successfully done the following:
Installed the 64 bit Sybase ASE 15 PCClient. Then set up a System DSN:
Use the Start > Programs > Sybase > DataAccess(64-bit) > OdbcDataSourceAdministrator to set up an ODBC System DSN
Click on System DSN tab
Click on Add to add a new DSN.
Choose the Adaptive Server Enterprise Version 15.00.00.350 from Sybase (SYBDRVODBC64.DLL dated 04 May 2009)
Click on Finish
Screen pops up to configure it.
Fill in
Data Source Name : whatever you want to name it - I used SyBaseData
SERVERName (ASE Host Name): Your ServerName
Server Port: 5000
Database Name: Test
Login ID: MyUserName
Back End Type: ASE
Click on test connection – fill in password for user MyUserName
Click OK
I Installed the 64-bit MSDASQL software downloaded from http://www.Microsoft.com/downloads/details.aspx?FamilyID=000364db-5e8b-44a8-b9be-ca44d18b059b&displaylang=en
Go to SSMS and run the following script:
/****** Object: LinkedServer [SyBase2Write] Script Date: 08/26/2009 11:00:24 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'SyBase2Write', @srvproduct=N'Sybase', @provider=N'MSDASQL', @datasrc=N'SyBaseData' --The System DSN you set up in ODBC above
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SyBase2Write',@useself=N'False',@locallogin=NULL,@rmtuser=N'MyUserName,@rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption @server=N'SyBase2Write', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SyBase2Write', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SyBase2Write', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SyBase2Write', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SyBase2Write', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SyBase2Write', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SyBase2Write', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SyBase2Write', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'SyBase2Write', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'SyBase2Write', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SyBase2Write', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'SyBase2Write', @optname=N'use remote collation', @optvalue=N'true'
I was then able to Insert rows using
insert OPENQUERY(SyBase2Write,'SELECT * FROM YourSybaseDbName.dbo.YourTableName') SELECT Field1, Field2, Field3 ... FROM SqlDbName.dbo.SqlTableName Where ....
Order By ...
I, however battled to delete the rows for the current month, I needed to. I eventually set up the Deletion as a Stored Proc in Sybase and executed it by using
SET ROWCOUNT 10EXEC ('YourSybaseDbName.dbo.sp_delete_MySyBaseRows') at SyBase2Write
SET ROWCOUNT 0
I found that I needed the Set Rowcount 10 otherwise sybase seemed to hang.
This solution took approx 3 weeks of on-off research, as no-one seemed to have a complete answer.
I trust that solves your issue.
Regards
Harold Joubert
Snr BI Developer
/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
July 5, 2011 at 3:16 pm
Assuming you have a 64 bit server running SQL SVR 2008 64 bit edition and you want to use the 32 bit Sybase ODBC driver, then the free workround is to install an additional 32 bit SQL instance on the same server. Create a linked server between SQL SVR 2008 32 bit instance and sybase using the 32-bit ODBC driver. Then set-up a linked server between the 64 bit instance adn the 32 bit instance. You can now run SSIS packages and other queries on the 64 bit instance. Performance is just fine.
Its been working for one of our customers for over a year but stopped working when the customer virtualized the server. Re-installing the ODBC driver may or may not resolve the problem. We have not tried that yet so I can't yet assure you that the solution works on virtualized servers.
Remember that Sybase needs to be configured to accept ODBC Queried from specified IP addresses. One reason for your ODBC query to fail is that your IP Address has not been added to the list.
July 6, 2011 at 12:51 am
Apologies on this as it might not be relivant, my experience with Sybase is SQL Anywhere (ASA) not ASE.
We have a 64bit data warehouse which uses the 64bit ODBC ASA provider which comes with the ASA media for both SSIS packages and Linked Servers and it works a treat.
We went through the process, create a 64bit System DSN with the ASA driver and test it connects. Create the linked server and ensure that it can query Sybase using openquery. Use the ODBC provider for SSIS and develop the packages that way.
While I appreciate that the two products are different, they have come together quite a lot over recent versions, so unsure if there is something in ASE which is causing your issue.
On a side note there is also the option to run SSIS packages in 32 bit mode once you deploy them and schedule them in a job should you have a working version of a 32bit ASE driver, but you would need to create the relivant 32bit DSN on the 64bit machine.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply