Release source file after operation is done (LinkedServer)

• Ronny_1

  SSC Enthusiast

  Points: 187

  More actions

  August 27, 2019 at 6:02 pm

  #3674804

  Hi. I´m somwhat new to SQL.

  I haven´t been able to find a solution, maybe its a trivial question, but here we go.

  Basically I have to get the data from an Excel file to a table in a data base, for this I have created a series of stored procedures and a macro to do it automatically.

  It works like this:

  Select the sheets/tables,

  copy then into a temp excel file,

  save and close this temp file,

  then I create a Linked server pointing to this temp file,

  copy the data from the excel temp file to a new table on the DB,

  then I drop the Linked Server,

  and finally delete the temp excel file.

  The problem I got is with the last step, I can´t inmediately delete the temp excel file because the sqlservr.exe process has a lock on it, I'm pretty sure that no connection is left open. After 1 minute (more or less) the file is released and finally I can delete it but I want to do this as soon as I drop the linked server.

  The procedures:

  CREATE PROCEDURE CreateTables @name nvarchar(15)
  AS  
  DECLARE @OPENQUERY nvarchar(100), @TSQL nvarchar(100), @LinkedServer nvarchar(40)
  
  SET @TSQL = 'DROP TABLE IF EXISTS ' + @name + ''
  EXEC (@TSQL)
  
  SET @LinkedServer = 'ExcelServer'
  SET @OPENQUERY = 'SELECT * INTO ' +@name + ' FROM OPENQUERY(' +@LinkedServer+ ','''
  SET @TSQL = 'SELECT * FROM ['+@name +']'')'
  EXEC (@OPENQUERY+@TSQL)
  
  SET @TSQL = 'ALTER TABLE ' + @name + ' ADD PRIMARY KEY (ID)'
  EXEC (@TSQL)
  GO

  CREATE PROCEDURE CreateLinkedServer
  AS
  EXEC sp_addlinkedserver @server=N'ExcelServer',
  @srvproduct=N'',
  @provider=N'MSDASQL',
  @provstr=N'Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=\\XXX.XXX.XXX.XXX\shared\TempFile.xlsb;'
  
  EXEC sp_serveroption @SERVER='ServidorExcel', @OPTNAME='rpc', @OPTVALUE='true'
  EXEC sp_serveroption @SERVER='ServidorExcel', @OPTNAME='rpc out', @OPTVALUE='true'
  GO
  

  CREATE PROCEDURE DropLinkedServer
  AS
  EXEC sp_dropserver 'ExcelServer', 'droplogins'
  GO

  Attached is an image of process explorer.

  Is what I´m asking feasible?

  Thank you in advance.

  Attachments:

  You must be logged in to view attached files.

• Site Owners

  SSC Guru

  Points: 80381

  More actions

  August 28, 2019 at 6:10 pm

  #3675180

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.