Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2006
    Posts
    14

    Unanswered: linked server SQL to dBASE(DB4)

    Please anybody help me.
    Can't get oledb for odbc work.Need an explanation what to put in each field of linked server properties General tab to get connected. Have ODBC server DSN working fine with Crystal reports.
    Did this:
    exec sp_addlinkedserver @server = 'ODBC_TEST' ,
    @srvproduct = 'dBase',
    @provider = 'MSDASQL',
    @datasrc = 'DB4_GBS'
    GO
    -- Adding linked server login:

    EXEC sp_addlinkedsrvlogin 'ODBC_TEST', 'false', 'sqlLogin', 'sqlpassword', NULL
    Getting error : datasource name not found and no default driver specified.

    I also attempt to create OLEDB ODBCless link and can't do it because after that I have to change path in TEMP and TMP variables and restart db, but I cann ot restart the server.

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    Here's a sample of one that I know that works; but it is an AS/400 and not dbase. I don't have an example of a dbase server around, and have no particular experience with linking SQL to dbase.


    sp_addlinkedserver @server = 'LK_REMOTE'
    , @srvproduct = ''
    , @provider = 'MSDASQL'
    , @datasrc = 'REMOTE_DSN'
    GO

    sp_addlinkedsrvlogin @rmtsrvname = 'LK_REMOTE'
    , @rmtuser = 'username'
    , @rmtpassword = 'password'
    , @useself = 'FALSE'
    GO

    It looks an awful lot like yours; so I'm not sure it helps much. Are you sure that 'DB4_GBS' exists as a DSN (a system DSN) and that the SQL account under which your server is running has access to it?

    Regards,

    hmscott
    Have you hugged your backup today?

  3. #3
    Join Date
    Mar 2006
    Posts
    14
    Quote Originally Posted by hmscott
    Are you sure that 'DB4_GBS' exists as a DSN (a system DSN) and that the SQL account under which your server is running has access to it?
    How do I check that? I only know that I can create Crystal report or get data into excel file using that DSN.

    Thank you so much for your respond.

    Ann2

  4. #4
    Join Date
    Aug 2005
    Posts
    9
    Ann:

    My guess is that either your DSN really does not exist or you have something like a typo error.

  5. #5
    Join Date
    Mar 2006
    Posts
    14
    Quote Originally Posted by DatabaseCorner
    Ann:

    My guess is that either your DSN really does not exist or you have something like a typo error.
    It exists (look at the picture, I use DB4_GBN) and I checked all possible typos.
    Ann
    Attached Thumbnails Attached Thumbnails ODBC.jpg  

  6. #6
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by Ann2
    It exists (look at the picture, I use DB4_GBN) and I checked all possible typos.
    Ann
    Forgive me if this sounds too basic a question: on which machine is that DSN located? Is it your local machine (like maybe a Windows XP you are using to connect to the SQL Server)? Or is it the server itself?

    The DSN connection must be created on the machine on which SQL Server is running in order for SQL Server to "see" it.

    Again, my apologies if this is too obvious; I certainly do not mean to insult you.

    Regards,

    hmscott
    Have you hugged your backup today?

  7. #7
    Join Date
    Mar 2006
    Posts
    14
    Oh thank you. I am not an expert at all and that too basic for you is woods for me.
    No. DSN I created on my machine. SQL Server located on another machine.
    I have all sql tools on my machine, like query analyser and enterprise manager and I can do whatever queries on that sql database, but we have a sql server separate machine. So I have to phisically go to the server machine and create the DSN there? It will be the same DSN connection to db4 as I have on my machine, but it will be created on the sql server machine. Am I correct?
    Thank you so much
    Ann2

  8. #8
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by Ann2
    Am I correct?

    Ann2
    That's it; the linked server must be created (in the exact same fashion as yours) on the SQL Server machine itself.

    Regards,

    hmscott
    Have you hugged your backup today?

  9. #9
    Join Date
    Mar 2006
    Posts
    14
    I did mapped the drives to a letter on the server machine and created system DSN. I also created linked server on the server machine.
    But when I try to click on the tables of the linked server from my machine or run query like this: select * from openquery(GBS,'select * from ASTU5003')
    I get this error message:
    OLE DB provider 'MSDASQL' reported an error.
    [OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed]
    [OLE/DB provider returned message: [Microsoft][ODBC dBase Driver] '(unknown)' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.]
    OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ].
    What is wrong now?
    Ann2

  10. #10
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by Ann2
    I did mapped the drives to a letter on the server machine and created system DSN.
    Ann2
    This doesn't sound quite right to me. Where is the file for the dbase data? Is it on your machine, or on the SQL Server machine?

    Ugh, this could be ugly.

    SQL Server is a service; it won't recognize a mapped drive that you create while logged in as you. You will have to place the dbase data file(s) on a machine that is always on (eg, a server) and then reference the location using a Universal Naming Convention path (UNC). This would look like: \\MyServer\MyShare\DBaseFolder\DBasedFile.dbf.

    Like I said, I have never created a DSN for a dbase file; I don't know what the actual entries would be. I have created one for Access and it does work with UNC.

    One other thing to check would be to ensure that SQL Server Service account has permissions (read and execute I would think) on the file/folder where the DBF file(s) reside.

    I apologize if none of this makes sense. I am knee-deep in Oracle right now and I am not testing this against files in my own environment.

    Regards,

    hmscott
    Have you hugged your backup today?

  11. #11
    Join Date
    Mar 2006
    Posts
    14
    I was not working for couple days. If you can look to this please.
    dBase located on another server.
    First, when creating system DSN the GUI does not let put in UNC, it forces you to select a driver and map it. The only place to change the value is in regedit, but it did not help.
    Second. According to our servers person SQL Server Service account does has permissions on the machine/file/folder where dbase resides. This part I do not understand completely. Is it a windows admin account for sql server machine or SQL server account like for instance sa? Then how to give permissions to that account?

    Third.
    I trIed just to run a query using
    OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
    'Data Source= "\\DIST\.GB_DATA.F.GBS.GB\APPS\SA\DATAFILE\tabname .dbf
    ";Extended Properties=dBASE IV')...X
    and getting the same error whether I use UNC or just simple S:/...

    Thank you so much for helping me.
    Ann

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •