sp_addlinkserver

• @DJ

  SSCommitted

  Points: 1989

  More actions

  December 22, 2010 at 3:42 am

  #388483

  Hi

  I have created a procedure that opens a connection to access.

  Looking something like this:

  **************

  CREATE PROCEDURE ACCESS_LINK

  AS

  -- Setup variables

  DECLARE

  @path nvarchar(254),

  @file nvarchar(30),

  @string nvarchar(254);

  -- Assign valid values to each variable

  set @path = (select [path] from [access_Test_Datamarts].[dbo].[TF_XD_config])

  set @file = (select [file] from [access_Test_Datamarts].[dbo].[TF_XD_config])

  -- Concatenate variables to complete string

  set @string = @path +''+ @file

  -- Link server information

  EXEC sp_addlinkedserver

  @server = 'Access01',

  @provider = 'Microsoft.Jet.OLEDB.4.0',

  @srvproduct = 'OLE DB Provider for Jet',

  @datasrc = @string

  -- Set up login mapping using current user's security context

  EXEC sp_addlinkedsrvlogin

  @rmtsrvname = 'Access01',

  @useself = 'False',

  @locallogin = NULL,

  @rmtuser = 'Admin',

  @rmtpassword = ''

  What I want to achive is to be able to run some scripts to change data in access based on some data from ms sql.

  This will run every day, or manually if needed.

  Question:

  Will this link last for ever?

  If I use it in a regular job from ms sql,will it then open a new connection each time I run it?

  Should we disconnect when not needed, and can this be done with sp_droppserver.(the description of this function scares me, it looks like it will dropp the entire server not just the link I have made)

  Thanks for your time

  Dan

• Marco V

  SSCrazy

  Points: 2939

  More actions

  December 22, 2010 at 6:47 am

  #1265485

  you only need to create the linked server once it will stay

  about connections it works the same as you query local databases but there will be a dormant spid on the destination server