Results 1 to 4 of 4
  1. #1
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065

    Linked Server


    Not exactly sure how to word this but Here goes:

    Current Setup: We have a Corporate SQL Server in another part of the state. We also have a local SQL Server. There is a Commercial database (EZ-Cap) that we use. Currently twice a week we take a backup of the Corporate SQL Server and restore it to our local server for our reporting purposes. Over the last few years I've built about 300 Views that are kept in another database on our local Server. The views point to the local copy of the Database. Our Data Line between Corporate and us has been expanded and Corporate want's to discontinue the backup and restore. So I'm trying to create a linked Server to the Corporate database so I can re-point those views to the Corporate Database instead of the local copy. The issue I have with that is that the Data is a Commercial Database so unless I use the UserID and Password to that Database I can't see the data. If I do use the UserID and Password to that particular Database the Link Fails because I don't have the rights on that server to link to that database. If I use the sa password and login I can connect to the database just fine but the primary tables I use for my reports are all blank because the sa username and password don't have access to the commercial data. So does anyone have an idea of how I can make this work. SQL Server Versions are Corporate (12). Local Version 8 R2. Anybody have an Idea on how I can make this work?

  2. #2
    turbofish is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    25
    Are both the local server and the server located in another part of the state on the same domain? If so, you could use merge replication that will keep the two servers in sync with each other. I used this method to keep a copy of the data from 140 remote locations on a server at corporate. I would suggest merge replication instead of transactional replication because merge will handle disconnected data better.

    If that is out of the question, you are going to have to talk to the DBA to give an account enough rights to create a linked server but there will be some more questions to make that work. For example, does the tables have XML columns? If it does, you will have to use views for everything since even if you aren't querying the XML column, it will fail due to the fact that you can't use openquery in distributed queries.

    And still yet another idea would be to create an SSIS package that would retrieve the data from remote location and put it in the local database.

    How much data are we talking about here?

  3. #3
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    Kept looking at the Server I'm trying to get the data from and found that My userid in the database I need to access and my userid on that particular SQL Server are disconnected. It's the only user that exists Where the USERID Exists in the Database and the USERID Exists in SQL Server but I get the following Message when I look at that USERID in Security in the Database Under User Type is the Message: SQL user without Login

    If I look at Security for the Server the USERID Exists but the database I'm trying to connect to is not checked and If I try to check it, it gives an error saying the USER Already Exists.

    the full Database is about 210 GB. The Log is about 100 MB.

  4. #4
    NoellaG's Avatar
    NoellaG is offline VIP
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi, possible solutions:
    First look if your security is set to Windows authentication or mixed sql server/windows authentication
    1/ if you get user exists it means the user exists in the database but the Id doesn't match anymore with the login ID. fastest solution: drop DB user and recreate it on the database using the server login.
    2/ (ask to) create a windows service account that has permissions on both servers , and use this account for setting op the linked server or replication agent if you go for replication instead of linked servers.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Access SQL Linked Server from DSN
    By AME in forum SQL Server
    Replies: 5
    Last Post: 07-26-2016, 09:31 AM
  2. Replies: 3
    Last Post: 12-17-2015, 07:01 AM
  3. Replies: 0
    Last Post: 09-25-2012, 06:50 AM
  4. Linked SQL Server Table Problems
    By jalton in forum Import/Export Data
    Replies: 3
    Last Post: 02-13-2010, 12:52 AM
  5. Linked Table To SQL Server?
    By snkscore in forum Import/Export Data
    Replies: 3
    Last Post: 12-09-2009, 06:36 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums