Results 1 to 6 of 6
  1. #1
    RayMilhon is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,066

    Linked tables

    I have a report that requires me to pull data from a linked server. I've done this before never a problem but this time the Server Name is giving me an issue. The Server name includes the IP Address xxx.xxx.xxx.xxx/servername. I'm creating a view in our local SQL Server and a daily report will go out based on that view. The issue is that using the IP in the Query for the view generates an error everytime I try to save the view. I put the [] around it but that doesn't help anyone know a workaround?

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    What is the error number & description?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    RayMilhon is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,066
    Quote Originally Posted by isladogs View Post
    What is the error number & description?
    Msg 102, Level 15, State 1, Line 35
    Incorrect syntax near '\'.

  4. #4
    RayMilhon is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,066
    Here's the From Clause

    FROM
    [172.24.6.20\hvvmg]\hvvmgrpt.dbo.lab_nor ln
    INNER JOIN [172.24.6.20\hvvmg]\hvvmgrpt.dbo.[patient_encounter pe WITH(NOLOCK) on ln.enc_id = pe.enc_id
    INNER JOIN [172.24.6.20\hvvmg]\hvvmgrpt.dbo.person p on pe.person_id = p.person_id
    INNER JOIN [172.24.6.20\hvvmg]\hvvmgrpt.dbo.location_mstr lo on lo.location_id = pe.location_id
    INNER JOIN [172.24.6.20\hvvmg]\hvvmgrpt.dbo.provider_mstr pm on pm.provider_id = ln.ordering_provider
    left outer JOIN [172.24.6.20\hvvmg]\hvvmgrpt.dbo.lab_results_obr_p lrp on ln.order_num = lrp.ngn_order_num
    left outer JOIN [172.24.6.20\hvvmg]\hvvmgrpt.dbo.lab_results_obx obx on obx.unique_obr_num = lrp.unique_obr_num
    inner join [172.24.6.20\hvvmg]\hvvmgrpt.dbo.lab_order_diag d on d.order_num = ln.order_num
    inner join diagnosis_code_mstr dm on dm.diagnosis_code_id = d.diagnosis_code_id

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Unfortunately that is a typically unhelpful SQL server error message.
    I'm certainly not a SQL server expert but should it be a forward slash before servername as mentioned in post #1?
    I also think the [] brackets should be removed.
    Also try replacing the single backslashes with doubles i.e. \\.

    Hopefully someone who specialises in SS will chip in to assist.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    The syntax is servername.databasename.schema.tablename, without any \.

    Assuming your database name is hvvmgrpt, try:

    FROM
    [172.24.6.20\hvvmg].hvvmgrpt.dbo.lab_nor IN
    INNER JOIN [172.24.6.20\hvvmg].hvvmgrpt.dbo.[patient_encounter] PE

    ect




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

Similar Threads

  1. Replies: 6
    Last Post: 04-18-2018, 06:56 AM
  2. Replies: 17
    Last Post: 04-09-2018, 04:39 PM
  3. Replies: 1
    Last Post: 02-07-2016, 02:41 PM
  4. Replies: 4
    Last Post: 11-22-2013, 11:20 AM
  5. Replies: 5
    Last Post: 02-02-2012, 06:42 PM

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