March 2, 2018 at 5:00 pm
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
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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy