Post reply

Add Link Server Code to Procedure

  • 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'

  • 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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply