Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2008
    Posts
    3

    Unanswered: How to query remote Access table in SQL Server Stored Procedure

    Hello esteemed experts.

    I have no training in writing SQL but I've picked up a bit from Google and trial and error.

    I'm trying to write a stored procedure to query data stored on our SQL Server database. However, I really need to reference some information that is only stored in a table in a separate Access database.

    I have read around this for a day or so, and it looks like I need to link the Access database into our server using the sp_addlinkedserver system procedure. So I have written the following to try to make this work:

    Code:
    CREATE PROCEDURE usp_vd_temp_test_ALS
    
    AS
    
    exec sp_addlinkedserver 
    	@server='ProjCostRep',
    	@provider='Microsoft.Jet.OLEDB.4.0',
    	@srvproduct='OLE DB Provider for Jet',
    	@datasrc='\\shul003\reprts\Project Cost Report.mdb'
    
    GO
    
    exec sp_addlinkedsrvlogin 
    	@rmtsrvname='ProjCostRep', 
    	@useself='false', 
    	@rmtpassword=''
    
    GO
    
    exec sp_tables_ex ProjCostRep
    
    GO
    
    select * from ProjCostRep...[Completed jobs on report]
    The problem is this generates a string of three "Incorrect syntax near 'GO'. (#170) errors.

    So my questions are:
    1. Is this really the best way to link to a table in Access?
    2. What's wrong with my syntax?


    PS
    I'm afraid I don't know what version of SQL Server we are running, though it was installed new in late 2003/early 2004. Can I write a query that will tell me?

  2. #2
    Join Date
    Jun 2008
    Posts
    3
    As an experiment I tried commenting out the GOs. Now I get:

    "[SQL Server]Could not find server 'ProjCostRep' in sysservers. Execute sp_addlinkedserver to add the server to sysservers (#7202)."

    I also tried splitting the sp_addlinkedserver part off, executing it first then going with the query, but I get the same result, so there seems to be a problem with my sp_addlinkedserver syntax. Maybe I'm missing something?

  3. #3
    Join Date
    Jul 2008
    Posts
    2

    Re: How to query remote Access table in SQL Server Stored Procedure

    Stored Procedure do not accept go statement in its body. Hope this will work
    Jalil

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    The script below is for SQL2K5/SQL2K8, but very little will need to be changed to make it work in SQL2K. The script is written as a template, so after pasting it into QA press Ctrl+Shift+M and replace the values with actual references:
    Code:
    /*
       ******************************************************************************
       This procedure creates a linked server to MS Access 2000 database (xxx.mdb)
       To create a new linked server, execute the following statement:
       
          exec __admin.sp__AddLinkedServerForAccess2000 N'c:\temp\test.mdb'
          
       To see the contents of the linked server database, you may either expand SSMS,
       Server Objects, Linked Servers, or execute the following statement:
       
          exec sp_tables_ex 'Guidata031908'
       ******************************************************************************
    */
       if exists (select * from sys.sysservers where srvname = N'<linkedserver,sysname,test>')
          exec master.dbo.sp_dropserver @server = N'<linkedserver,sysname,test>', @droplogins = 'droplogins'
       exec sp_addlinkedserver N'<linkedserver,sysname,test>', 'Access', 'Microsoft.Jet.OLEDB.4.0', N'<linkedserverpath,sysname,c:\temp\test.mdb>'
       exec sp_addlinkedsrvlogin N'<linkedserver,sysname,test>', false, 'sa', 'Admin', NULL
    go
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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