January 14, 2010 at 8:54 am
Hey guys, long time no post. Been swamped here and haven't had time to even check the daily email from SQLServerCentral in a while.
Window 2003 64 bit
SQL 2005 64 bit
Anyway, my problem is that I am trying to setup a linked server using a 32bit ODBC connection. I do not have the 64bit drivers for the server I am trying to connect to and have been told that they will not be developed.
The thing is that if I set this up in a SSIS package and run the package in 32 bit mode it works (not using a linked server, but just connecting through odbc). What I want to do is setup a linked server on my 64bit box to connect to their 32bit server. The server is IEX and the driver is Simba. If anyone is familiar with IEX, then they will know my pain.
I have tried to download the latest MDAC, but it didn't help. Here's my code for setting up the linked server, but it can't find the datasource when I try to connect.
EXEC sp_addlinkedserver @server = 'TTVDB', @srvproduct='', @provider='MSDASQL', @datasrc='TTVodbcConnection'
My problem basically comes down to this:
How can I use a 32bit odbc datasource for a linked server in SQL 2005 64bit?
Solving this problem would make my life so much easier. Thanks in advance for any responses to this post.
January 14, 2010 at 1:25 pm
You cannot use a 32-bit driver from a linked server. The linked server process is running in x64 - which means you have to use x64 drivers.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 14, 2010 at 1:39 pm
What I've had to do before, when there were no x64 drivers, was to set up another server (x86), install SQL on there and use the x86 drivers. Not ideal, has licensing implications. But if there is not and will not be a x64 driver, is probably only way.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 14, 2010 at 2:27 pm
Jeffrey Williams-493691 (1/14/2010)
You cannot use a 32-bit driver from a linked server. The linked server process is running in x64 - which means you have to use x64 drivers.
I read that on the microsoft site and I was hoping that someone found a way around it. I was thinking that I was going to setup a linked table in Access and then SQL Server query the table, but I was hoping for a better solution.
January 14, 2010 at 2:29 pm
GilaMonster (1/14/2010)
What I've had to do before, when there were no x64 drivers, was to set up another server (x86), install SQL on there and use the x86 drivers. Not ideal, has licensing implications. But if there is not and will not be a x64 driver, is probably only way.
I've already have that setup. Between my 32bit SQL 2000 server and my x64 2005 Server. I also can use the 32bit option in SSIS, but neither option is too stable.
Again, hoping that someone has a better idea.
January 14, 2010 at 2:30 pm
Thank you both for your replies.
January 18, 2010 at 2:25 pm
So no one has any other work arounds for this issue? I'm really suprised that this isn't a bigger issue in the community.
January 19, 2010 at 1:57 am
It's the biggest problem when moving to 64 bit operating system and SQL. The thing is, there are no work arounds to make it work. 64 bit SQL Server cannot use a 32 bit driver. You need to install something else (32 bit) that can use the driver and then connect the 64 bit SQL to that. Hence my suggestion of a second SQL instance
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 19, 2010 at 5:40 am
I have also found that installing a 32 bit instance of SQL Server seems to be the best way. I have had to link to all sorts of legacy systems this way.
Not sure if this would work, but maybe a 32 bit install of SQL Server express somewhere. I have not tried it, but it would be the right price.
Mike
“I know that you believe you understand what you think I said, but I'm not sure you realize that what you heard is not what I meant.”...Robert McCloskey
___________________________________________________________________
January 19, 2010 at 8:56 am
This may or may not be the answer you're looking for, but here's some info I've collected during our move to 64-Bit SQL Server 2005.
http://www.amosfivesix.com/sql/34-64-bit-sql-server-ssis-packages-with-excel-and-access-data
http://www.amosfivesix.com/sql/35-more-sql-server-64-bit-issues
January 26, 2010 at 11:43 am
Well I already had the '2nd server as sql 2000' option in place.
I have done a ton of research on the problems with 32bit SSIS. Looks like the most reliable way that I can still use my main 64x server to pull from a 32bit source is to just create legacy DTS packages. I've been running them as my main source for about a week and they seem to be much more stable.
April 19, 2012 at 9:40 am
I've gotten this to work in our new SQL 2012 RTM 64-bit servers:
This retrives data from DBase, Advantage DB, Sybase .dbf files directly using the Microsoft ACE OLEDB 12.0 64-bit drivers.
Use MyDatabase
GO
sp_configure 'show advanced options', 1
reconfigure
GO
sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1;
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1;
--Must use all CAPS in the query parameter section, except the file name.
SELECT COLUMN1,
COLUMN2
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','dBASE IV;Database=\\RemotServerName\ShareName\Folder1\SubFolder1\','SELECT COLUMN1, COLUMN2 FROM DBASEFIL.dbf WHERE COLUMN3 = ''C''')
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 0;
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 0;
GO
sp_configure 'Ad Hoc Distributed Queries', 0
reconfigure
GO
sp_configure 'show advanced options', 0
reconfigure
GO
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply