Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Linked server failed

Answered Linked server failed

  • Friday, January 18, 2013 8:13 AM
     
     
    Sign In to Vote
    0

    Hi All,

    Please suggest...

    GSKR

     

All Replies

  • Friday, January 18, 2013 8:22 AM
     
     
    Sign In to Vote
    0

    Are you created LL for two server ?

    paste the error

    no need for snap

    -- Bhavesh Thakkar

    Sr.Database Administrator
    My Blog
    Linked In Profile


     
  • Friday, January 18, 2013 8:29 AM
     
     
    Sign In to Vote
    0

    You didn't answered my question in your other post with the same issue: linked server failed

    Are you using a 64 Bit SQL Server? JET provider is not available on 64 Bit systems.

    Olaf Helper

    Blog Xing

     
  • Friday, January 18, 2013 8:33 AM
     
     
    Sign In to Vote
    0

    Hi Olaf,

    No am using 32bit SQL Server...

    GSKR

     
  • Friday, January 18, 2013 9:56 AM
     
     
    Sign In to Vote
    0

    Hi Olaf,

    Waiting for reply.....

    GSKR

     
  • Friday, January 18, 2013 10:05 AM
     
     
    Sign In to Vote
    0

    Sometime I have to do my own work, so it can take some time till I can reply ...

    How did you defined the linked server?
    Where is the Excel file located?
    Do the SQL Server servoce account do have permissions to access the Excel file?

    Olaf Helper

    Blog Xing

     
  • Friday, January 18, 2013 11:02 AM
     
     
    Sign In to Vote
    0

    Hi Olaf..

    i defined like this

    excel located in E drive..

    GSKR

     
  • Friday, January 18, 2013 12:10 PM
     
     
    Sign In to Vote
    0

    I added a linked server with the same settings, also with a XLSX file and for me it works pretts fine.

    Your drive E:, is this a local drive or is it a mapped network share? If it's a network share, copy the XLXS file to a local drive and try it with that location.

    Olaf Helper

    Blog Xing

     
  • Friday, January 18, 2013 12:20 PM
     
     
    Sign In to Vote
    0

    Hi Olaf,

    Thanks for your reply.. My excel file located in local drive only. I think am missing the third point service account settings. could please explain me how to set permissions to access the excel file from loca drive.

    GSKR

     
  • Friday, January 18, 2013 12:35 PM
     
     
    Sign In to Vote
    0

    Before you change some permission, do an other test to see if it's really a permission issue.

    Copy the XLSX file to the DATA subfolder of your SQL Server, where also your database files are located; there the SQL Server service account do have full read/write permissions.

    Olaf Helper

    Blog Xing

     
  • Friday, January 18, 2013 12:40 PM
     
     
    Sign In to Vote
    0

    Hi Olaf,

    How do i give the permissions to the SQL Server service account.

    GSKR

     
  • Friday, January 18, 2013 1:19 PM
     
     
    Sign In to Vote
    0
    I think he means ServiceAccount = sa account = admin account for SQL are you an admin on that box?  If not engage the admin for help.

    • Edited by CountryStyle Friday, January 18, 2013 1:20 PM
    •  
     
  • Friday, January 18, 2013 1:35 PM
     
     
    Sign In to Vote
    0

    Have you tested it as I suggested to see, if it's a permission issue?

    And no, I don't mean the SysAdmin account "sa", I mean the account, under the SQL Server service is running.

    Olaf Helper

    Blog Xing

     
  • Saturday, January 19, 2013 5:59 AM
     
     
    Sign In to Vote
    0

    Hi Olaf,

    My system is running under admin and i have given full access to my account. still its not working.

    GSKR

     
  • Saturday, January 19, 2013 6:07 AM
     
     
    Sign In to Vote
    0

    Olaf Helper JET provider is available on 64 Bit systems

    you can look at this old blog i wrote long time ago (it is in hebraw but you can use google to translate): http://ariely.info/Blog/tabid/83/EntryId/35/MS-Access-with-64-bit.aspx

    signature

     
  • Saturday, January 19, 2013 6:19 AM
     
      Has Code
    Sign In to Vote
    0

    hi

    1. please try to use Microsoft.ACE.OLEDB.12.0 provider
    if need install this: http://www.microsoft.com/en-us/download/details.aspx?id=13255

    2. first try to use OPENROWSETand just after the connection is working and you have all the connection info my Suggests is to go and try linked server since it is more complex and there are other factors to the problem



    signature

     
  • Saturday, January 19, 2013 7:16 AM
     
     
    Sign In to Vote
    0

    GSKR.  Log into your machine as the same account that the SQL Service is running as.

    If I'm correct, the linked server is trying to create a temp file inside the profile folder of the account running as the SQL Service.

    I can't remember where the folder location is in the profile, but I've run into this same problem before.

     
  • Sunday, January 20, 2013 8:33 AM
     
     
    Sign In to Vote
    0

    Olaf Helper JET provider is available on 64 Bit systems


    No, It's not. What you point to is ACE, the new data access component, replacing the old JET.

    Olaf Helper

    Blog Xing

     
  • Sunday, January 20, 2013 8:34 AM
     
     Proposed
    Sign In to Vote
    0

    My system is running under admin and i have given full access to my account. still its not working.


    Your permissions doesn't matter in any way, it's the SQL Server going to access the file and it's service account requires permission. Have you testet it with the DATA folder?

    Olaf Helper

    Blog Xing

     
  • Monday, January 21, 2013 5:27 AM
     
     Answered
    Sign In to Vote
    0

    Hi Olaf,

    Thanks for your help.. Problem got resolved by disabling "allow inprocess" in linked server proivders "Microsoft.Jet.OLEDB.4.0" or "Microsoft.ACE.OLEDB.12.0".

    GSKR

    • Marked As Answer by GSKR Monday, January 21, 2013 5:27 AM
    •