Add Link Server Code to Procedure

• texpic

  SSCertifiable

  Points: 5887

  More actions

  March 2, 2018 at 5:00 pm

  #336431

  I'm trying to put this into a procedure.  I get this error.  I'm attaching the test xlsx file also.  Any ideas, thanks.

  Here is the error:  Msg 7202, Level 11, State 2, Line 19
  Could not find server 'MyLinkServ' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

  
CREATE PROCEDURE EraseMe1 as

  DECLARE @FileSource varchar(100) = 'TestFile.xlsx'

  DECLARE @SQLStatement nvarchar(MAX)
SELECT @SQLStatement = 'EXEC sp_addlinkedserver
@server = N''MyLinkServ'', 
@srvproduct=N''ExcelData'',
@provider=N''Microsoft.ACE.OLEDB.12.0'', 
@datasrc=N''C:\ImportData\' + @FileSource + ''',
@provstr=N''EXCEL 12.0''; '
EXEC (@SQLStatement)
--PRINT (@SQLStatement)
GO

  IF OBJECT_ID('Test123..StdFile') IS NOT NULL DROP TABLE Test123..StdFile
SELECT * INTO Test123..StdFile FROM OPENQUERY (MyLinkServ, 'Select * from [a$]')

  SELECT COUNT(*) FROM Test123..StdFile

  GO
--cleanup
IF EXISTS(SELECT * FROM sys.servers WHERE name = N'MyLinkServ')
EXEC sp_dropserver
@server = N'MyLinkServ',
@droplogins = 'droplogins'


• texpic

  SSCertifiable

  Points: 5887

  More actions

  March 2, 2018 at 7:04 pm

  #1981834

  Think I figured it out:

  STEP 1: Create the link server

  DECLARE @FileSource varchar(100) = 'TestFile.xlsx'

  DECLARE @SQLStatement nvarchar(MAX)
  SELECT @SQLStatement = 'EXEC sp_addlinkedserver
  @server = N''MyLinkServ'', 
  @srvproduct=N''ExcelData'',
  @provider=N''Microsoft.ACE.OLEDB.12.0'', 
  @datasrc=N''C:\ImportData\' + @FileSource + ''',
  @provstr=N''EXCEL 12.0''; '
  EXEC (@SQLStatement)
  --PRINT (@SQLStatement)

  STEP 2: Create the procedure

  STEP 3: Delete the link server

  IF EXISTS(SELECT * FROM sys.servers WHERE name = N'MyLinkServ')
  EXEC sp_dropserver
  @server = N'MyLinkServ',
  @droplogins = 'droplogins'

  STEP 4:

  Procedure is ready