Post reply

SQL 2005 64bit is like a MAC. Incompatible!!!

  • June 4, 2007 at 9:06 am

    #173963

    Ok, so maybe I'm being a bit over-dramatic. But there are some very strange nuances when it comes to getting SQL 2005 64-bit to work like SQL 2005 32-bit.

    I am trying to connect to a Progress database on another server using linked servers. I am able to create a connection using the 32 bit of ODBC administrator. But it appears that there is no 64 bit provider for ODBC servers installed on SQL 2005 64 bit! Is it possible to add a 32 bit DSN as a linked server in SQL 2006 64 bit?

    So can someone prove me wrong that SQL 2005 64bit is not like a MAC. Incompatible?

    Thanks!

    Mike

  • June 4, 2007 at 12:59 pm

    #710691

    too lazy to link, but i search the KB. i think there is a .sql file from SP4 you have to rerun on your sql 2000 server

  • June 4, 2007 at 8:11 pm

    #710759

    I don't have any SQL 2000 servers. Just SQL 2005 64bit. Note, the 32-bit works just fine as it can link and has the 32 bit MSDAQSL (?) provider for ODBC connections, but the 64 bit just doesn't see the SYSTEM DSNs, nor does it have any providers other than SQL SERVER.

     

     

  • June 5, 2007 at 7:29 am

    #710847

    Can you download and install the 32 bit ODBC drivers (MDAC 2.8+??) on your 64 bit server?  Not sure if this is viable or not.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • June 5, 2007 at 8:05 am

    #710853

    I ran MDAC Component Checker and it says I have MDAC 2.8 SP2 on Windows Server 2003 SP1

    Not sure if this helps...

     

     

  • June 5, 2007 at 8:26 am

    #710857

    1) Did you make your DSN a system one or user? See here in BOL: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/fc1e0ca2-457d-45f6-a5a4-38793eb7511c.htm

    2) This, and the links from it seem to imply to me that there is no distinction between 32/64 bit and that you should be able to connect to Progress assuming a valid driver is available and configured:  ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/76b4a5b3-2c4b-4a48-8d2a-91acefc3cb72.htm

    3) Did you try OPENROWSET or OPENDATASOURCE?

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • June 5, 2007 at 9:09 am

    #710860

    How do I access those links you posted above?

    From what I understand the problem to be is that in SQL 2005 64 bit there is no MSDASQL provider when you expand the linked severs node in SQL Server Management Studio 2005. I think if I got that provider to appear, it would work just fine.

    It now appears that the 32-bit version of SQL Server Mangement Studio is installed on the server. I wonder if there is a 64 bit I need to install and maybe that will show me an expanded list of providers?

     

  • June 5, 2007 at 9:25 am

    #710865

    1) Open BOL (SQL 2005 Books Online) and paste link in bar and hit enter

    2) I don't believe there is 64 bit SSMS. 

    3) Did you try using SQL Native Client to configure a connection to Progress?

    4) See this for sp_addlinkedserver.  Perhaps you can simply specify msdasql even though it isn't listed.

    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/fed3adb0-4c15-4a1a-8acd-1b184aff558f.htm

     

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • June 5, 2007 at 10:24 am

    #710888

    I'm not sure how I would go about creating a SQL Native client connection to a Progress Database system (they are quite different)

    When I run the command:

    select

    a.* from

    openrowset

    ('MSDASQL','SWR_RIMS';'RsiS92Cr';'yT778CoR','SELECT * FROM sysprogress.systables') AS a

    From the workstation where 'SWR_RIMS' is a linked server configured to use the SWR_RIMS system DSN (yes I know they are the same name) it works just fine. However the same command on the server configured the same way produces:

    Msg 7403, Level 16, State 1, Line 1

    The OLE DB provider "MSDASQL" has not been registered.

    I'm guessing MSDASQL isn't installed on SQL SERVER 64-bit? If that's the case, anyone running 64 Bit SQL 2005 will never be able to query a System DSN??

     

  • June 5, 2007 at 10:32 am

    #710892

    One last detail, the SQL server is a clustered server. But I am working on the active node of the cluster.

     

  • June 5, 2007 at 10:33 am

    #710893

    1) Just try using SQLNCLI as the provider, instead of msdasql.  Yeah, I know it SHOULDN'T work, and PROBABLY won't work, but I have had wierder things than that solve problems!

    2) Try downloading and installing the latest, full mdac version from microsoft's website.  I would be a bit leery of this if it is a production server obviously.  I think you can find it here:  http://www.microsoft.com/downloads/details.aspx?familyid=78CAC895-EFC2-4F8E-A9E0-3A1AFBD5922E&displaylang=en

    3) Other than that, I am fresh out of ideas.  Unless you want to write your own provider . . .

     

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • June 19, 2007 at 8:40 am

    #713862

    Hi Newbie, did you get solution for your question? I have the same issue.

    thanks

  • June 19, 2007 at 11:41 am

    #713918

    The only solution is to use SQL 2005 32bit. It has all the necessary drivers. Thats what I ended up using.

    -Mike

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply