sp_addlinkedserver

[yetanotherjo]

I've made a stored procedure to link to an Access database and drop some data into it's tables, then drop the link. If I run the steps of it separately they all work ok, but running the whole thing I get an error that "Could not find server 'AccessDatabase' in sysservers. Execute sp_addlinkedserver to add the server to sysservers."

I want to use this a. as a scheduled job and b. for users to run whenever they need, probably using a pass-through from Access. Any ideas what the problem is?

-- Link the target Access database to SQL Server
EXEC sp_addlinkedserver 
   @server = 'ExportToAccess', 
   @provider = 'Microsoft.Jet.OLEDB.4.0', 
   @srvproduct = 'OLE DB Provider for Jet',
   @datasrc = N'D:\SurpacExportMaster.mdb'

-- Clean out collar table
DELETE ExportToAccess...dh_collar

-- Insert new data into collar table
INSERT INTO ExportToAccess...dh_collar
	(DataSet, Hole_ID, Local_East, Local_North, Local_RL, Local_Grid_ID, MGA_East, MGA_North, 
	MGA_RL, MGA_Grid_ID, Hole_Type, Max_Depth, Prospect, Tenement, BOCO_Depth, CBI_Depth, 
	TOFR_Depth, Water_Depth, Company)
SELECT DataSet, Hole_ID, Local_East, Local_North, Local_RL, Local_Grid_ID, MGA_East, MGA_North, 
	MGA_RL, MGA_Grid_ID, Hole_Type, Max_Depth, Prospect, Tenement, BOCO_Depth, CBI_Depth, 
	TOFR_Depth, Water_Depth, Company
FROM tblVW_SurpacDHCollar
WHERE tblVW_SurpacDHCollar.Local_East >= @mineast AND tblVW_SurpacDHCollar.Local_East <= @maxeast 
	AND tblVW_SurpacDHCollar.Local_North >= @minnorth AND tblVW_SurpacDHCollar.Local_North <= @maxnorth
	AND tblVW_SurpacDHCollar.Local_Grid_ID = @gridid

-- drop linked access database
EXEC sp_dropserver 'ExportToAccess'

 

[unclerico]

maybe try adding GO between statements

EXEC sp_addlinkedserver 
   @server = 'ExportToAccess', 
   @provider = 'Microsoft.Jet.OLEDB.4.0', 
   @srvproduct = 'OLE DB Provider for Jet',
   @datasrc = N'D:\SurpacExportMaster.mdb'

[b][red]GO[/red][/b]

-- Clean out collar table
DELETE ExportToAccess...dh_collar

-- Insert new data into collar table
INSERT INTO ExportToAccess...dh_collar
    (DataSet, Hole_ID, Local_East, Local_North, Local_RL, Local_Grid_ID, MGA_East, MGA_North, 
    MGA_RL, MGA_Grid_ID, Hole_Type, Max_Depth, Prospect, Tenement, BOCO_Depth, CBI_Depth, 
    TOFR_Depth, Water_Depth, Company)
SELECT DataSet, Hole_ID, Local_East, Local_North, Local_RL, Local_Grid_ID, MGA_East, MGA_North, 
    MGA_RL, MGA_Grid_ID, Hole_Type, Max_Depth, Prospect, Tenement, BOCO_Depth, CBI_Depth, 
    TOFR_Depth, Water_Depth, Company
FROM tblVW_SurpacDHCollar
WHERE tblVW_SurpacDHCollar.Local_East >= @mineast AND tblVW_SurpacDHCollar.Local_East <= @maxeast 
    AND tblVW_SurpacDHCollar.Local_North >= @minnorth AND tblVW_SurpacDHCollar.Local_North <= @maxnorth
    AND tblVW_SurpacDHCollar.Local_Grid_ID = @gridid

[b][red]GO[/red][b]

-- drop linked access database
EXEC sp_dropserver 'ExportToAccess'

 

[yetanotherjo]

Thanks for the response unclerico. I tried that and my parameter declaration fell over...just can't win some days!!!

I think I've solved it by running it as 3 separate stored procedures, which is probably better anyway as it gives me more flexibility...well it worked in Management Studio, next challenge is to get it to work as a pass-through from Access....